查询服务中的SQL语法

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

选择查询

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

[ 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 ]

where 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 子句可与表或表别名一起使用，但不能在子查询或视图的顶部使用。 A SNAPSHOT 子句在a SELECT 可以对表应用查询。

此外，您还可以使用 HEAD 和 TAIL 作为快照子句的特殊偏移值。使用 HEAD 是指第一个快照之前的偏移，而 TAIL 指上次快照后的偏移。

注意

如果您在两个快照ID之间进行查询，并且启动快照已过期，则可能会出现以下两种情况，具体取决于可选的回退行为标记(resolve_fallback_snapshot_on_failure):

如果设置了可选的回退行为标志，查询服务将选择最早的可用快照，将其设置为开始快照，并返回最早可用快照和指定的结束快照之间的数据。此数据为包含最早可用快照。
如果未设置可选的回退行为标记，则将返回错误。

WHERE子句

默认情况下，由 WHERE 子句 SELECT 查询区分大小写。如果希望匹配项不区分大小写，则可以使用关键字 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”开头的客户。

加入

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:A 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:A 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:如果指定了此值，则在表执行此操作时不会引发异常 not 存在。

删除数据库

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

DROP DATABASE [IF EXISTS] db_name

参数

IF EXISTS:如果指定了此值，则在数据库执行 not 存在。

删除架构

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

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

参数

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

创建视图

以下语法定义 CREATE VIEW 查询：

CREATE VIEW view_name AS select_query

参数

view_name:要创建的视图的名称。
select_query:A 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:如果指定了此值，则在视图为 not 存在。
view_name:要删除的视图名称。

示例

DROP VIEW v1
DROP VIEW IF EXISTS v1

匿名块

匿名块包含两个部分：可执行和异常处理部分。在匿名块中，可执行文件部分是强制性的。但是，异常处理部分是可选的。

以下示例显示如何使用一个或多个语句创建块以一起执行：

BEGIN
  statementList
[EXCEPTION exceptionHandler]
END

exceptionHandler:
      WHEN OTHER
      THEN statementList

statementList:
    : (statement (';')) +

以下示例使用匿名块。

$$
BEGIN
   SET @v_snapshot_from = select parent_id  from (select history_meta('email_tracking_experience_event_dataset') ) tab where is_current;
   SET @v_snapshot_to = select snapshot_id from (select history_meta('email_tracking_experience_event_dataset') ) tab where is_current;
   SET @v_log_id = select now();
   CREATE TABLE tracking_email_id_incrementally
     AS SELECT _id AS id FROM email_tracking_experience_event_dataset SNAPSHOT BETWEEN @v_snapshot_from AND @v_snapshot_to;

EXCEPTION
  WHEN OTHER THEN
    DROP TABLE IF EXISTS tracking_email_id_incrementally;
    SELECT 'ERROR';
END$$;

Spark SQL命令

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

设置

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

SET property_key = property_value

参数

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

要返回任何设置的值，请使用 SET [property key] 没有 property_value.

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语句。如果未明确取消分配预准备语句，则会在会话结束时取消分配该语句。有关准备语句的更多信息，请参阅准备命令中。

DEALLOCATE name
DEALLOCATE ALL

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

声明

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

DECLARE name CURSOR FOR query

参数

name:要创建的游标的名称。
query:A 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 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