查询服务中的SQL语法

Adobe Experience Platform查询服务提供了对SELECT语句和其他有限命令使用标准ANSI SQL的功能。本文档介绍Query Service支持的SQL语法。

选择查询

以下语法定义了Query Service支持的SELECT查询：

[ WITH with_query [, ...] ]
SELECT [ ALL | DISTINCT [( expression [, ...] ) ] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ]
    [ FROM from_item [, ...] ]
    [ SNAPSHOT { SINCE start_snapshot_id | AS OF end_snapshot_id | BETWEEN start_snapshot_id AND end_snapshot_id } ]
    [ WHERE condition ]
    [ GROUP BY grouping_element [, ...] ]
    [ HAVING condition [, ...] ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT | MINUS } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start ]

其中from_item可以是以下选项之一：

table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]

[ LATERAL ] ( select ) [ AS ] alias [ ( column_alias [, ...] ) ]

with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]

from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]

和grouping_element可以是以下选项之一：

( )

expression

( expression [, ...] )

ROLLUP ( { expression | ( expression [, ...] ) } [, ...] )

CUBE ( { expression | ( expression [, ...] ) } [, ...] )

GROUPING SETS ( grouping_element [, ...] )

with_query为：

 with_query_name [ ( column_name [, ...] ) ] AS ( select | values )

以下子部分提供了有关可在查询中使用的附加条款的详细信息，前提是这些条款遵循上述格式。

SNAPSHOT子句

此子句可用于基于快照ID增量读取表上的数据。快照ID是由长类型数字表示的检查点标记，每次向其写入数据时，该长类型数字都会应用于数据湖表。 SNAPSHOT子句将自身附加到它旁边使用的表关系。

    [ SNAPSHOT { SINCE start_snapshot_id | AS OF end_snapshot_id | BETWEEN start_snapshot_id AND end_snapshot_id } ]

示例

SELECT * FROM Customers SNAPSHOT SINCE 123;

SELECT * FROM Customers SNAPSHOT AS OF 345;

SELECT * FROM Customers SNAPSHOT BETWEEN 123 AND 345;

SELECT * FROM Customers SNAPSHOT BETWEEN HEAD AND 123;

SELECT * FROM Customers SNAPSHOT BETWEEN 345 AND TAIL;

SELECT * FROM (SELECT id FROM CUSTOMERS BETWEEN 123 AND 345) C 

SELECT * FROM Customers SNAPSHOT SINCE 123 INNER JOIN Inventory AS OF 789 ON Customers.id = Inventory.id;

请注意，SNAPSHOT子句可与表或表别名一起使用，但不能在子查询或视图上使用。 SNAPSHOT子句可以在任何可以应用表SELECT查询的位置使用。

此外，还可以将HEAD和TAIL用作快照子句的特殊偏移值。使用HEAD表示第一个快照之前的偏移，而使用TAIL表示最后一个快照之后的偏移。

WHERE子句

默认情况下，由SELECT查询上的WHERE子句生成的匹配区分大小写。如果希望匹配项不区分大小写，则可以使用关键字ILIKE而不是LIKE。

    [ WHERE condition { LIKE | ILIKE | NOT LIKE | NOT ILIKE } pattern ]

下表说明了LIKE和ILIKE子句的逻辑：

子句	运算符
`WHERE condition LIKE pattern`	`~~`
`WHERE condition NOT LIKE pattern`	`!~~`
`WHERE condition ILIKE pattern`	`~~*`
`WHERE condition NOT ILIKE pattern`	`!~~*`

示例

SELECT * FROM Customers
WHERE CustomerName ILIKE 'a%';

此查询会返回名称以“A”或“a”开头的客户。

加入

使用连接的SELECT查询具有以下语法：

SELECT statement
FROM statement
[JOIN | INNER JOIN | LEFT JOIN | LEFT OUTER JOIN | RIGHT JOIN | RIGHT OUTER JOIN | FULL JOIN | FULL OUTER JOIN]
ON join condition

并集、交叉和除外

UNION、INTERSECT和EXCEPT子句用于组合或排除两个或多个表中类似的行：

SELECT statement 1
[UNION | UNION ALL | UNION DISTINCT | INTERSECT | EXCEPT | MINUS]
SELECT statement 2

创建选定表

以下语法定义了CREATE TABLE AS SELECT(CTAS)查询：

CREATE TABLE table_name [ WITH (schema='target_schema_title', rowvalidation='false') ] AS (select_query)

参数

schema:XDM架构的标题。仅当您希望对由CTAS查询创建的新数据集使用现有XDM架构时，才使用此子句。
rowvalidation:（可选）指定用户是否希望对为新创建数据集摄取的每个新批次进行行级别验证。默认值为 true。
select_query:一个 SELECT 声明。SELECT查询的语法可在SELECT查询部分中找到。

示例

CREATE TABLE Chairs AS (SELECT color, count(*) AS no_of_chairs FROM Inventory i WHERE i.type=="chair" GROUP BY i.color)

CREATE TABLE Chairs WITH (schema='target schema title') AS (SELECT color, count(*) AS no_of_chairs FROM Inventory i WHERE i.type=="chair" GROUP BY i.color)

CREATE TABLE Chairs AS (SELECT color FROM Inventory SNAPSHOT SINCE 123)

注意

SELECT语句必须具有聚合函数的别名，如COUNT、SUM、MIN等。此外，SELECT语句可以带有或不带括号()。您可以提供SNAPSHOT子句，以将增量增量增量读取到目标表中。

插入到

INSERT INTO命令的定义如下：

INSERT INTO table_name select_query

参数

table_name:要将查询插入到的表的名称。
select_query:一个 SELECT 声明。SELECT查询的语法可在SELECT查询部分中找到。

示例

INSERT INTO Customers SELECT SupplierName, City, Country FROM OnlineCustomers;

INSERT INTO Customers AS (SELECT * from OnlineCustomers SNAPSHOT AS OF 345)

注意

SELECT语句不得包含在括号()中。此外，SELECT语句结果的模式必须符合INSERT INTO语句中定义的表的模式。您可以提供SNAPSHOT子句，以将增量增量增量读取到目标表中。

拖放表

DROP TABLE命令会删除一个现有表，如果该表不是外部表，则会从文件系统中删除与该表关联的目录。如果表不存在，则会发生异常。

DROP TABLE [IF EXISTS] [db_name.]table_name

参数

IF EXISTS:如果指定了此值，则如果表没有文本列表，则不会引发任何异常。

删除数据库

DROP DATABASE命令会删除现有数据库。

DROP DATABASE [IF EXISTS] db_name

参数

IF EXISTS:如果指定了此值，则如果数据库没有文本列表，则不会引发任何异常。

删除架构

DROP SCHEMA命令会删除现有架构。

DROP SCHEMA [IF EXISTS] db_name.schema_name [ RESTRICT | CASCADE]

参数

IF EXISTS:如果指定了此设置，则如果架构没有文本列表，则不会引发任何异常。
RESTRICT:模式的默认值。如果指定了此值，则仅当不包含任何表时，才会删除架构。
CASCADE:如果指定了此值，则将删除架构以及架构中存在的所有表。

创建视图

以下语法定义CREATE VIEW查询：

CREATE VIEW view_name AS select_query

参数

view_name:要创建的视图的名称。
select_query:一个 SELECT 声明。SELECT查询的语法可在SELECT查询部分中找到。

示例

CREATE VIEW V1 AS SELECT color, type FROM Inventory

CREATE OR REPLACE VIEW V1 AS SELECT model, version FROM Inventory

下拉视图

以下语法定义DROP VIEW查询：

DROP VIEW [IF EXISTS] view_name

参数

IF EXISTS:如果指定了此值，则如果视图没有文本列表，则不会引发异常。
view_name:要删除的视图名称。

示例

DROP VIEW v1
DROP VIEW IF EXISTS v1

Spark SQL命令

以下子部分涵盖查询服务支持的Spark SQL命令。

设置

SET命令可设置属性，并返回现有属性的值或列出所有现有属性。如果为现有属性键值提供了，则会覆盖旧值。

SET property_key = property_value

参数

property_key:要列出或更改的属性的名称。
property_value:您希望将属性设置为的值。

要返回任何设置的值，请使用不带property_value的SET [property key]。

PostgreSQL命令

以下子部分涵盖查询服务支持的PostgreSQL命令。

开始

BEGIN命令或BEGIN WORK或BEGIN TRANSACTION命令会启动事务块。在开始命令之后输入的任何语句将在单个事务中执行，直到给出明确的COMMIT或ROLLBACK命令。此命令与START TRANSACTION相同。

BEGIN
BEGIN WORK
BEGIN TRANSACTION

关闭

CLOSE命令释放与打开游标相关的资源。光标关闭后，不允许对其执行后续操作。当不再需要游标时，应该将其关闭。

CLOSE name
CLOSE ALL

如果使用CLOSE name，则name表示需要关闭的打开游标的名称。如果使用CLOSE ALL，则所有打开的游标都将关闭。

取消分配

使用DEALLOCATE命令可以取消分配以前准备的SQL语句。如果未明确取消分配预准备语句，则会在会话结束时取消分配该语句。有关准备语句的更多信息，请参见PREPARE命令一节。

DEALLOCATE name
DEALLOCATE ALL

如果使用DEALLOCATE name，则name表示需要取消分配的准备语句的名称。如果使用DEALLOCATE ALL，则所有准备的语句都将被取消分配。

声明

DECLARE命令允许用户创建游标，该游标可用于从较大的查询中检索少量行。创建游标后，使用FETCH从游标中获取行。

DECLARE name CURSOR FOR query

参数

name:要创建的游标的名称。
query:提 SELECT 供游 VALUES 标要返回的行的或命令。

执行

EXECUTE命令用于执行先前准备的语句。由于准备语句仅在会话期间存在，因此准备语句必须由在当前会话之前执行的PREPARE语句创建。有关使用预准备语句的更多信息，请参阅PREPARE命令部分。

如果创建该语句的PREPARE语句指定了一些参数，则必须将一组兼容的参数传递到EXECUTE语句中。如果未传递这些参数，则会引发错误。

EXECUTE name [ ( parameter ) ]

参数

name:要执行的准备语句的名称。
parameter:准备语句的参数的实际值。这必须是一个表达式，其中生成的值与此参数的数据类型兼容，这取决于创建预准备语句时所确定的值。如果准备语句有多个参数，则它们之间用逗号分隔。

解释

EXPLAIN命令显示所提供语句的执行计划。执行计划显示如何扫描语句引用的表。如果引用了多个表，则将显示用于将每个输入表中的所需行汇总在一起的联接算法。

EXPLAIN option statement

其中option可以是以下任一值：

ANALYZE
FORMAT { TEXT | JSON }

参数

ANALYZE:如果包 option 含 ANALYZE，则会显示运行时间和其他统计信息。
FORMAT:如果包 option 含 FORMAT，则会指定输出格式，格式可以 TEXT 为或 JSON。非文本输出包含与文本输出格式相同的信息，但便于程序解析。此参数默认为TEXT。
statement:要查看 SELECT其执行计划的任 INSERT何、 UPDATE、 DELETE、 VALUES EXECUTE、 DECLARE CREATE TABLE AS或 CREATE MATERIALIZED VIEW AS 语句。

重要

请记住，当使用ANALYZE选项时，实际会执行该语句。尽管EXPLAIN会丢弃SELECT返回的任何输出，但语句的其他副作用会照常发生。

示例

以下示例显示了在具有单个integer列和10000行的表上执行简单查询的计划：

EXPLAIN SELECT * FROM foo;

                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on foo  (cost=0.00..155.00 rows=10000 width=4)
(1 row)

获取

FETCH命令使用先前创建的游标检索行。

FETCH num_of_rows [ IN | FROM ] cursor_name

参数

num_of_rows:要获取的行数。
cursor_name:从中检索信息的游标的名称。

准备

使用PREPARE命令可以创建准备语句。准备语句是服务器端对象，可用于模板类似的SQL语句。

预准备语句可以采用参数，这些参数是执行语句时替换到语句中的值。在使用准备的语句时，参数按位置（使用$1、$2等）引用。

或者，您也可以指定参数数据类型列表。如果未列出参数的数据类型，则可以从上下文推断该类型。

PREPARE name [ ( data_type [, ...] ) ] AS SELECT

参数

name:准备语句的名称。
data_type:准备语句参数的数据类型。如果未列出参数的数据类型，则可以从上下文推断该类型。如果需要添加多个数据类型，可以将其添加到逗号分隔列表中。

回滚

ROLLBACK命令将取消当前事务，并丢弃该事务所做的所有更新。

ROLLBACK
ROLLBACK WORK

选择到

SELECT INTO命令会创建一个新表，并使用查询计算的数据填充该表。数据不会返回给客户端，因为它使用普通的SELECT命令。新表的列具有与SELECT命令的输出列关联的名称和数据类型。

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    * | expression [ [ AS ] output_name ] [, ...]
    INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ] new_table
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY expression [, ...] ]
    [ HAVING condition [, ...] ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
    [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]

参数

有关标准SELECT查询参数的更多信息，请参见SELECT查询节。此部分将仅列出SELECT INTO命令专用的参数。

TEMPORARY 或： TEMP可选参数。如果指定，则所创建的表将是临时表。
UNLOGGED:可选参数。如果指定，则创建为的表将是一个未记录的表。有关未记录表的详细信息，请参阅PostgreSQL文档。
new_table:要创建的表的名称。

示例

以下查询将创建一个新表films_recent，该表仅包含表films中的最近条目：

SELECT * INTO films_recent FROM films WHERE date_prod >= '2002-01-01';

显示

SHOW命令显示运行时参数的当前设置。这些变量可以使用SET语句进行设置，方法是：编辑postgresql.conf配置文件，通过PGOPTIONS环境变量（使用libpq或基于libpq的应用程序时）进行设置，或者在启动Postgres服务器时通过命令行标记进行设置。

SHOW name
SHOW ALL

参数

name:要了解相关信息的运行时参数的名称。运行时参数的可能值包括以下值：
- SERVER_VERSION:此参数显示服务器的版本号。
- SERVER_ENCODING:此参数显示服务器端字符集编码。
- LC_COLLATE:此参数显示数据库的归类区域设置（文本排序）。
- LC_CTYPE:此参数显示字符分类的数据库区域设置。
  
  IS_SUPERUSER:此参数显示当前角色是否具有超级用户权限。
ALL:显示所有配置参数的值及说明。

示例

以下查询显示参数DateStyle的当前设置。

SHOW DateStyle;

 DateStyle
-----------
 ISO, MDY
(1 row)

复制

COPY命令将任何SELECT查询的输出转储到指定位置。用户必须有权访问此位置，此命令才能成功。

COPY query
    TO '%scratch_space%/folder_location'
    [  WITH FORMAT 'format_name']

参数

query:要复制的查询。
format_name:要在中复制查询的格式。format_name可以是parquet、csv或json之一。默认情况下，值为parquet。

注意

完整的输出路径将为adl://<ADLS_URI>/users/<USER_ID>/acp_foundation_queryService/folder_location/<QUERY_ID>

ALTER TABLE

使用ALTER TABLE命令可以添加或删除主键或外键约束，以及向表中添加列。

添加或删除约束

以下SQL查询显示了向表添加或删除约束的示例。

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY ( column_name )

ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY ( column_name ) REFERENCES referenced_table_name ( primary_column_name )

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY column_name NAMESPACE namespace

ALTER TABLE table_name DROP CONSTRAINT constraint_name PRIMARY KEY ( column_name )

ALTER TABLE table_name DROP CONSTRAINT constraint_name FOREIGN KEY ( column_name )

参数

table_name:要编辑的表的名称。
constraint_name:要添加或删除的约束的名称。
column_name:要向其添加约束的列的名称。
referenced_table_name:外键引用的表的名称。
primary_column_name:外键引用的列的名称。

注意

表架构应是唯一的，且不会在多个表之间共享。此外，对于主键约束，命名空间是必填的。

添加列

以下SQL查询显示了向表添加列的示例。

ALTER TABLE table_name ADD COLUMN column_name data_type

ALTER TABLE table_name ADD COLUMN column_name_1 data_type1, column_name_2 data_type2

参数

table_name:要编辑的表的名称。
column_name:要添加的列的名称。
data_type:要添加的列的数据类型。支持的数据类型包括：bigint， char， string， date， datetime， double， double precision，整数， smallint， tinyint， varchar。

显示主键

SHOW PRIMARY KEYS命令列出了给定数据库的所有主键约束。

SHOW PRIMARY KEYS

    tableName | columnName    | datatype | namespace
------------------+----------------------+----------+-----------
 table_name_1 | column_name1  | text     | "ECID"
 table_name_2 | column_name2  | text     | "AAID"

显示外键

SHOW FOREIGN KEYS命令列出给定数据库的所有外键约束。

SHOW FOREIGN KEYS

    tableName   |     columnName      | datatype | referencedTableName | referencedColumnName | namespace 
------------------+---------------------+----------+---------------------+----------------------+-----------
 table_name_1   | column_name1        | text     | table_name_3        | column_name3         |  "ECID"
 table_name_2   | column_name2        | text     | table_name_4        | column_name4         |  "AAID"

adobe.com 上的资源

Personalized Experiences Headless Cms Second Party Data Contribution Analysis Mobile Analytics Target Market Segmentation