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 ]
位置 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 )
以下小节提供了可在查询中使用的附加子句的详细信息,前提是它们遵循上述格式。
此子句可用于基于快照ID增量读取表中的数据。 快照ID是由Long-type数字表示的检查点标记,每次将数据写入到快照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
子句 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', label='PROFILE') ] AS (select_query)
参数 | 描述 |
---|---|
schema |
XDM架构的标题。 仅当您希望为CTAS查询创建的新数据集使用现有XDM架构时,才使用此子句。 |
rowvalidation |
(可选)指定用户是否希望对新创建的数据集摄取的每个新批次进行行级验证。 默认值为 true 。 |
label |
使用CTAS查询创建数据集时,请使用此标签和值 profile 将您的数据集标记为已为配置文件启用。 这意味着您的数据集会在创建时自动标记为用户档案。 有关使用的更多信息,请参阅派生的属性扩展文档 label . |
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', label='PROFILE') 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
子句将增量增量增量读入目标表。
在根级别未找到实际XDM架构中的大多数字段,并且SQL不允许使用点表示法。 要使用嵌套字段获得逼真的结果,您必须映射 INSERT INTO
路径。
至 INSERT INTO
嵌套路径,请使用以下语法:
INSERT INTO [dataset]
SELECT struct([source field1] as [target field in schema],
[source field2] as [target field in schema],
[source field3] as [target field in schema]) [tenant name]
FROM [dataset]
示例
INSERT INTO Customers SELECT struct(SupplierName as Supplier, City as SupplierCity, Country as SupplierCountry) _Adobe FROM OnlineCustomers;
此 DROP TABLE
命令删除现有的表,如果它不是外部表,则从文件系统删除与该表关联的目录。 如果该表不存在,则会发生异常。
DROP TABLE [IF EXISTS] [db_name.]table_name
参数 | 描述 |
---|---|
IF EXISTS |
如果指定此项,则不会引发异常(如果表确实如此) 非 存在。 |
此 CREATE DATABASE
命令创建Azure Data Lake Storage (ADLS)数据库。
CREATE DATABASE [IF NOT EXISTS] db_name
此 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
查询数据集。 此数据集可以是ADLS或加速存储数据集。
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
以下语法定义 CREATE VIEW
在数据库和模式的上下文中创建视图的查询。
示例
CREATE VIEW db_name.schema_name.view_name AS select_query
CREATE OR REPLACE VIEW db_name.schema_name.view_name AS select_query
参数 | 描述 |
---|---|
db_name |
数据库的名称。 |
schema_name |
架构的名称。 |
view_name |
要创建的视图的名称。 |
select_query |
A SELECT 语句。 的语法 SELECT 查询位于 选择查询部分. |
示例
CREATE VIEW <dbV1 AS SELECT color, type FROM Inventory;
CREATE OR REPLACE VIEW V1 AS SELECT model, version FROM Inventory;
以下查询显示视图列表。
SHOW VIEWS;
Db Name | Schema Name | Name | Id | Dataset Dependencies | Views Dependencies | TYPE
----------------------------------------------------------------------------------------------
qsaccel | profile_agg | view1 | view_id1 | dwh_dataset1 | | DWH
| | view2 | view_id2 | adls_dataset | adls_views | ADLS
(2 rows)
以下语法定义 DROP VIEW
查询:
DROP VIEW [IF EXISTS] view_name
参数 | 描述 |
---|---|
IF EXISTS |
如果指定此项,则当视图指定此项时,不会引发异常 非 存在。 |
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;
当条件被评估为TRUE时,IF-THEN-ELSE控制结构允许有条件地执行语句列表。 此控制结构仅适用于匿名块。 如果此结构用作独立命令,则会导致语法错误(“匿名块外部的命令无效”)。
下面的代码段演示了匿名块中IF-THEN-ELSE条件语句的正确格式。
IF booleanExpression THEN
List of statements;
ELSEIF booleanExpression THEN
List of statements;
ELSEIF booleanExpression THEN
List of statements;
ELSE
List of statements;
END IF
示例
以下示例执行 SELECT 200;
.
$$BEGIN
SET @V = SELECT 2;
SELECT @V;
IF @V = 1 THEN
SELECT 100;
ELSEIF @V = 2 THEN
SELECT 200;
ELSEIF @V = 3 THEN
SELECT 300;
ELSE
SELECT 'DEFAULT';
END IF;
END$$;
此结构可以与 raise_error();
以返回自定义错误消息。 下面所示的代码块使用“自定义错误消息”终止匿名块。
示例
$$BEGIN
SET @V = SELECT 5;
SELECT @V;
IF @V = 1 THEN
SELECT 100;
ELSEIF @V = 2 THEN
SELECT 200;
ELSEIF @V = 3 THEN
SELECT 300;
ELSE
SELECT raise_error('custom error message');
END IF;
END$$;
匿名块中支持嵌套的IF语句。
示例
$$BEGIN
SET @V = SELECT 1;
IF @V = 1 THEN
SELECT 100;
IF @V > 0 THEN
SELECT 1000;
END IF;
END IF;
END$$;
匿名块中支持异常块。
示例
$$BEGIN
SET @V = SELECT 2;
IF @V = 1 THEN
SELECT 100;
ELSEIF @V = 2 THEN
SELECT raise_error(concat('custom-error for v= ', '@V' ));
ELSEIF @V = 3 THEN
SELECT 300;
ELSE
SELECT 'DEFAULT';
END IF;
EXCEPTION WHEN OTHER THEN
SELECT 'THERE WAS AN ERROR';
END$$;
查询服务支持可选会话级别设置,以便从JSON字符串形式的交互式SELECT查询返回顶级复杂字段。 此 auto_to_json
通过设置,可以将复杂字段中的数据作为JSON返回,然后使用标准库解析为JSON对象。
设置功能标志 auto_to_json
设置为true,然后再执行包含复杂字段的SELECT查询。
set auto_to_json=true;
auto_to_json
标志下表提供了一个示例查询结果,它位于 auto_to_json
设置。 这两种方案都使用同一个SELECT查询(如下所示),该查询用于定向具有复杂字段的表。
SELECT * FROM TABLE_WITH_COMPLEX_FIELDS LIMIT 2;
结果如下:
_id | _experience | application | commerce | dataSource | device | endUserIDs | environment | identityMap | placeContext | receivedTimestamp | timestamp | userActivityRegion | web | _adcstageforpqs
-----------------------------------+----------------------------------------------------------------------------+--------------+----------------------------------------------+------------+--------------------------------------------------------------------+--------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------+-------------------------------------------------------------------------+-----------------------+-----------------------+--------------------+--------------------------------------------------------------------------------------+-----------------
31892EE15DE00000-401D52664FF48A52 | ("("("(1,1)","(1,1)")","(-209479095,4085488201,-2105158467,2189808829)")") | (background) | (NULL,"(USD,NULL)",NULL,NULL,NULL,NULL,NULL) | (475341) | (32,768,1024,205202,https://ns.adobe.com/xdm/external/deviceatlas) | ("("(31892EE080007B35-E6CE00000000000,"(AAID)",t)")") | ("(en-US,f,f,t,1.6,"Mozilla/5.0 (iPhone; U; CPU iPhone OS 4_1 like Mac OS X; ja-jp) AppleWebKit/532.9 (KHTML, like Gecko) Version/4.0.5 Mobile/8B117 Safari/6531.22.7",490,1125)",xo.net,64.3.235.13) | [AAID -> "{(31892EE080007B35-E6CE00000000000,t)}"] | ("("(34.01,-84.0)",lawrenceville,US,524,30043,ga)",600) | 2022-09-02 19:47:14.0 | 2022-09-02 19:47:14.0 | (UT1) | ("(f,Search Results,"(1.0)")","(http://www.google.com/search?ie=UTF-8&q=,internal)") |
31892EE15DE00000-401B92664FF48AE8 | ("("("(1,1)","(1,1)")","(-209479095,4085488201,-2105158467,2189808829)")") | (background) | (NULL,"(USD,NULL)",NULL,NULL,NULL,NULL,NULL) | (475341) | (32,768,1024,205202,https://ns.adobe.com/xdm/external/deviceatlas) | ("("(31892EE100007BF3-215FE00000000001,"(AAID)",t)")") | ("(en-US,f,f,t,1.5,"Mozilla/5.0 (iPhone; U; CPU iPhone OS 4_1 like Mac OS X; ja-jp) AppleWebKit/532.9 (KHTML, like Gecko) Version/4.0.5 Mobile/8B117 Safari/6531.22.7",768,556)",ntt.net,219.165.108.145) | [AAID -> "{(31892EE100007BF3-215FE00000000001,t)}"] | ("("(34.989999999999995,138.42)",shizuoka,JP,392005,420-0812,22)",-240) | 2022-09-02 19:47:14.0 | 2022-09-02 19:47:14.0 | (UT1) | ("(f,Home - JJEsquire,"(1.0)")","(NULL,typed_bookmarked)") |
(2 rows)
auto_to_json
标志下表显示了结果中的差异, auto_to_json
设置对生成的数据集有。 这两种方案都使用了相同的SELECT查询。
_id | receivedTimestamp | timestamp | _experience | application | commerce | dataSource | device | endUserIDs | environment | identityMap | placeContext | userActivityRegion | web | _adcstageforpqs
-----------------------------------+-----------------------+-----------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+----------------------------------+------------------+-------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------
31892EE15DE00000-401D52664FF48A52 | 2022-09-02 19:47:14.0 | 2022-09-02 19:47:14.0 | {"analytics":{"customDimensions":{"eVars":{"eVar1":"1","eVar2":"1"},"props":{"prop1":"1","prop2":"1"}},"environment":{"browserID":-209479095,"browserIDStr":"4085488201","operatingSystemID":-2105158467,"operatingSystemIDStr":"2189808829"}}} | {"userPerspective":"background"} | {"order":{"currencyCode":"USD"}} | {"_id":"475341"} | {"colorDepth":32,"screenHeight":768,"screenWidth":1024,"typeID":"205202","typeIDService":"https://ns.adobe.com/xdm/external/deviceatlas"} | {"_experience":{"aaid":{"id":"31892EE080007B35-E6CE00000000000","namespace":{"code":"AAID"},"primary":true}}} | {"browserDetails":{"acceptLanguage":"en-US","cookiesEnabled":false,"javaEnabled":false,"javaScriptEnabled":true,"javaScriptVersion":"1.6","userAgent":"Mozilla/5.0 (iPhone; U; CPU iPhone OS 4_1 like Mac OS X; ja-jp) AppleWebKit/532.9 (KHTML, like Gecko) Version/4.0.5 Mobile/8B117 Safari/6531.22.7","viewportHeight":490,"viewportWidth":1125},"domain":"xo.net","ipV4":"64.3.235.13"} | {"AAID":[{"id":"31892EE080007B35-E6CE00000000000","primary":true}]} | {"geo":{"_schema":{"latitude":34.01,"longitude":-84.0},"city":"lawrenceville","countryCode":"US","dmaID":524,"postalCode":"30043","stateProvince":"ga"},"localTimezoneOffset":600} | {"dataCenterLocation":"UT1"} | {"webPageDetails":{"isHomePage":false,"name":"Search Results","pageViews":{"value":1.0}},"webReferrer":{"URL":"http://www.google.com/search?ie=UTF-8&q=","type":"internal"}} |
31892EE15DE00000-401B92664FF48AE8 | 2022-09-02 19:47:14.0 | 2022-09-02 19:47:14.0 | {"analytics":{"customDimensions":{"eVars":{"eVar1":"1","eVar2":"1"},"props":{"prop1":"1","prop2":"1"}},"environment":{"browserID":-209479095,"browserIDStr":"4085488201","operatingSystemID":-2105158467,"operatingSystemIDStr":"2189808829"}}} | {"userPerspective":"background"} | {"order":{"currencyCode":"USD"}} | {"_id":"475341"} | {"colorDepth":32,"screenHeight":768,"screenWidth":1024,"typeID":"205202","typeIDService":"https://ns.adobe.com/xdm/external/deviceatlas"} | {"_experience":{"aaid":{"id":"31892EE100007BF3-215FE00000000001","namespace":{"code":"AAID"},"primary":true}}} | {"browserDetails":{"acceptLanguage":"en-US","cookiesEnabled":false,"javaEnabled":false,"javaScriptEnabled":true,"javaScriptVersion":"1.5","userAgent":"Mozilla/5.0 (iPhone; U; CPU iPhone OS 4_1 like Mac OS X; ja-jp) AppleWebKit/532.9 (KHTML, like Gecko) Version/4.0.5 Mobile/8B117 Safari/6531.22.7","viewportHeight":768,"viewportWidth":556},"domain":"ntt.net","ipV4":"219.165.108.145"} | {"AAID":[{"id":"31892EE100007BF3-215FE00000000001","primary":true}]} | {"geo":{"_schema":{"latitude":34.989999999999995,"longitude":138.42},"city":"shizuoka","countryCode":"JP","dmaID":392005,"postalCode":"420-0812","stateProvince":"22"},"localTimezoneOffset":-240} | {"dataCenterLocation":"UT1"} | {"webPageDetails":{"isHomePage":false,"name":"Home - JJEsquire","pageViews":{"value":1.0}},"webReferrer":{"type":"typed_bookmarked"}} |
(2 rows)
此 resolve_fallback_snapshot_on_failure
选项用于解决快照ID过期的问题。 快照元数据在两天后过期,过期的快照可能会使脚本的逻辑失效。 当使用匿名块时,可能会出现问题。
设置 resolve_fallback_snapshot_on_failure
选项为true时,会使用以前的快照ID覆盖快照。
SET resolve_fallback_snapshot_on_failure=true;
以下代码行将覆盖 @from_snapshot_id
包含最早可用的 snapshot_id
来自元数据。
$$ BEGIN
SET resolve_fallback_snapshot_on_failure=true;
SET @from_snapshot_id = SELECT coalesce(last_snapshot_id, 'HEAD') FROM checkpoint_log a JOIN
(SELECT MAX(process_timestamp)process_timestamp FROM checkpoint_log
WHERE process_name = 'DIM_TABLE_ABC' AND process_status = 'SUCCESSFUL' )b
on a.process_timestamp=b.process_timestamp;
SET @to_snapshot_id = SELECT snapshot_id FROM (SELECT history_meta('DIM_TABLE_ABC')) WHERE is_current = true;
SET @last_updated_timestamp= SELECT CURRENT_TIMESTAMP;
INSERT INTO DIM_TABLE_ABC_Incremental
SELECT * FROM DIM_TABLE_ABC SNAPSHOT BETWEEN @from_snapshot_id AND @to_snapshot_id WHERE NOT EXISTS (SELECT _id FROM DIM_TABLE_ABC_Incremental a WHERE _id=a._id);
Insert Into
checkpoint_log
SELECT
'DIM_TABLE_ABC' process_name,
'SUCCESSFUL' process_status,
cast( @to_snapshot_id AS string) last_snapshot_id,
cast( @last_updated_timestamp AS TIMESTAMP) process_timestamp;
EXCEPTION
WHEN OTHER THEN
SELECT 'ERROR';
END
$$;
随着数据资产的增长,在Adobe Experience Platform数据湖中对它们进行逻辑组织非常重要。 查询服务扩展了SQL构造,使您能够按逻辑对沙盒中的数据资产进行分组。 这种组织方法允许在架构之间共享数据资产,而无需在物理上移动它们。
您可以使用标准SQL语法支持以下SQL结构,以便从逻辑上组织数据。
CREATE DATABASE dg1;
CREATE SCHEMA dg1.schema1;
CREATE table t1 ...;
CREATE view v1 ...;
ALTER TABLE t1 ADD PRIMARY KEY (c1) NOT ENFORCED;
ALTER TABLE t2 ADD FOREIGN KEY (c1) REFERENCES t1(c1) NOT ENFORCED;
请参阅指南,网址为 数据资产的逻辑组织 有关查询服务最佳实践的更多详细说明。
此 table_exists
SQL命令用于确认系统中当前是否存在表。 该命令返回一个布尔值: true
如果表 是 存在,并且 false
如果表有 非 存在。
通过在运行语句之前验证表是否存在, table_exists
功能简化了编写匿名块以同时涵盖 CREATE
和 INSERT INTO
用例。
以下语法定义 table_exists
命令:
$$
BEGIN
#Set mytableexist to true if the table already exists.
SET @mytableexist = SELECT table_exists('target_table_name');
#Create the table if it does not already exist (this is a one time operation).
CREATE TABLE IF NOT EXISTS target_table_name AS
SELECT *
FROM profile_dim_date limit 10;
#Insert data only if the table already exists. Check if @mytableexist = 'true'
INSERT INTO target_table_name (
select *
from profile_dim_date
WHERE @mytableexist = 'true' limit 20
) ;
EXCEPTION
WHEN other THEN SELECT 'ERROR';
END $$;
此 inline
函数将结构数组的元素分隔并生成表中的值。 它只能放置在 SELECT
列表或 LATERAL VIEW
.
此 inline
函数 无法 位于有其他生成器函数的选择列表中。
默认情况下,生成的列将命名为“col1”、“col2”等。 如果表达式为 NULL
则不生成行。
可使用重命名列名 RENAME
命令。
示例
> SELECT inline(array(struct(1, 'a'), struct(2, 'b'))), 'Spark SQL';
此示例返回以下内容:
1 a Spark SQL
2 b Spark SQL
第二个示例进一步演示了 inline
函数。 下图说明了示例的数据模型。
示例
select inline(productListItems) from source_dataset limit 10;
值取自 source_dataset
用于填充目标表。
SKU | _experience(体验) | 数量 | priceTotal |
---|---|---|---|
product-id-1 | (“(”(“(A,pass,B,NULL)”)“)”) | 5 | 10.5 |
product-id-5 | (“(”(“(A,通过, B,NULL)”)”)“) | ||
product-id-2 | (“(”(“(AF, C, D,NULL)”)“)”) | 6 | 40 |
product-id-4 | (“(”(“(BM,pass, NA,NULL)”)”)”) | 3 | 12 |
下面的子部分介绍了Query Service支持的Spark SQL命令。
此 SET
command设置一个属性,并返回现有属性的值或列出所有现有属性。 如果为现有的属性键提供了值,则会覆盖旧值。
SET property_key = property_value
参数 | 描述 |
---|---|
property_key |
要列出或更改的属性的名称。 |
property_value |
您希望属性设置为的值。 |
要返回任何设置的值,请使用 SET [property key]
没有 property_value
.
以下各小节涵盖 PostgreSQL 查询服务支持的命令。
此 ANALYZE TABLE
命令对命名表执行分布分析和统计计算。 使用 ANALYZE TABLE
根据数据集是否存储在 加速存储 或 数据湖. 有关其使用的更多信息,请参阅各自的部分。
此 ANALYZE TABLE
命令计算加速存储上表的统计信息。 统计信息是在加速存储上给定表的已执行CTAS或ITAS查询中计算的。
示例
ANALYZE TABLE <original_table_name>
以下是使用 ANALYZE TABLE
命令:-
计算值 | 描述 |
---|---|
field |
表中列的名称。 |
data-type |
每列可接受的数据类型。 |
count |
包含此字段的非null值的行数。 |
distinct-count |
此字段的唯一值或非重复值的数量。 |
missing |
此字段具有null值的行数。 |
max |
分析表中的最大值。 |
min |
分析表的最小值。 |
mean |
分析表的平均值。 |
stdev |
分析表的标准偏差。 |
您现在可以在以下位置计算列级统计信息 Azure Data Lake Storage (ADLS)数据集与 COMPUTE STATISTICS
sql命令。 计算整个数据集、数据集子集、所有列或列子集的列统计信息。
COMPUTE STATISTICS
扩展 ANALYZE TABLE
命令。 但是, COMPUTE STATISTICS
, FILTERCONTEXT
、和 FOR COLUMNS
加速存储表不支持命令。 的这些扩展 ANALYZE TABLE
当前仅支持ADLS表使用命令。
示例
ANALYZE TABLE tableName FILTERCONTEXT (timestamp >= to_timestamp('2023-04-01 00:00:00') and timestamp <= to_timestamp('2023-04-05 00:00:00')) COMPUTE STATISTICS FOR COLUMNS (commerce, id, timestamp);
此 FILTER CONTEXT
命令根据提供的过滤条件计算数据集子集的统计信息。 此 FOR COLUMNS
命令将目标定位到特定的列以供分析。
此 Statistics ID
生成的统计信息只对每个会话有效,不能跨不同的PSQL会话访问。
限制:
skip_stats_for_complex_datatypes
SET skip_stats_for_complex_datatypes = false
.
控制台输出如下所示。
| Statistics ID |
| ---------------------- |
| adc_geometric_stats_1 |
(1 row)
然后,您可以通过引用 Statistics ID
. 下面的示例语句允许您在与 Statistics ID
或别名。 要了解有关此功能的更多信息,请参阅 别名文档.
-- This statement gets the statistics generated for `alias adc_geometric_stats_1`.
SELECT * FROM adc_geometric_stats_1;
使用 SHOW STATISTICS
命令以显示会话中生成的所有临时统计信息的元数据。 此命令可帮助您优化统计分析的范围。
SHOW STATISTICS;
下面显示了SHOW STATISTICS的输出示例。
statsId | tableName | columnSet | filterContext | timestamp
----------------------+---------------+-----------+-----------------------------+--------------------
adc_geometric_stats_1 | adc_geometric | (age) | | 25/06/2023 09:22:26
demo_table_stats_1 | demo_table | (*) | ((age > 25)) | 25/06/2023 12:50:26
age_stats | castedtitanic | (age) | ((age > 25) AND (age < 40)) | 25/06/2023 09:22:26
请参阅 数据集统计文档 以了解更多信息。
Adobe Experience Platform查询服务提供了示例数据集,作为其近似查询处理功能的一部分。
当不需要对数据集进行聚合操作的确切答案时,最好使用数据集示例。 此功能允许您通过发出近似查询以返回近似答案,对大型数据集进行更有效的探索查询。
使用来自现有样本的均匀随机样本创建样本数据集 Azure Data Lake Storage (ADLS)数据集,仅使用来自原始数据的记录百分比。 数据集示例功能对 ANALYZE TABLE
命令和 TABLESAMPLE
和 SAMPLERATE
sql命令。
在以下示例中,第一行演示如何计算表格的5%样本。 第二行演示如何从表中数据的过滤视图中计算5%的样本。
示例
ANALYZE TABLE tableName TABLESAMPLE SAMPLERATE 5;
ANALYZE TABLE tableName FILTERCONTEXT (timestamp >= to_timestamp('2023-01-01')) TABLESAMPLE SAMPLERATE 5:
请参阅 数据集示例文档 以了解更多信息。
此 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 |
A SELECT 或 VALUES 命令提供游标要返回的行。 |
此 EXECUTE
命令用于执行以前准备的语句。 由于准备的语句只在会话期间存在,因此必须由创建该准备语句的 PREPARE
语句在当前会话中较早执行。 有关使用预准备语句的更多信息,请参见 PREPARE
命令 部分。
如果 PREPARE
创建语句的语句指定了某些参数,必须将一组兼容的参数传递给 EXECUTE
语句。 如果未传入这些参数,则会引发错误。
EXECUTE name [ ( parameter ) ]
参数 | 描述 |
---|---|
name |
要执行的预准备语句的名称。 |
parameter |
准备语句的参数实际值。 这必须是生成与此参数的数据类型兼容的值的表达式,该值在创建预准备语句时确定。 如果预准备语句有多个参数,则用逗号分隔。 |
此 EXPLAIN
命令显示提供的语句的执行计划。 执行计划显示了如何扫描语句引用的表。 如果引用了多个表,它将显示使用哪些连接算法来组合每个输入表中的所需行。
EXPLAIN statement
使用 FORMAT
带有的关键字 EXPLAIN
命令来定义响应的格式。
EXPLAIN FORMAT { TEXT | JSON } statement
参数 | 描述 |
---|---|
FORMAT |
使用 FORMAT 命令指定输出格式。 可用的选项包括 TEXT 或 JSON . 非文本输出包含的信息与文本输出格式相同,但程序更容易解析。 此参数默认为 TEXT . |
statement |
任何 SELECT , INSERT , UPDATE , DELETE , VALUES , EXECUTE , DECLARE , CREATE TABLE AS ,或 CREATE MATERIALIZED VIEW AS 语句,您希望查看其执行计划。 |
任何输出 SELECT
使用运行时,语句可能会返回 EXPLAIN
关键字。 这一声明的其他副作用照常发生。
示例
以下示例显示了对具有单个表的简单查询的计划 integer
列和10000行:
EXPLAIN SELECT * FROM foo;
QUERY PLAN
---------------------------------------------------------
Seq Scan on foo (dataSetId = "6307eb92f90c501e072f8457", dataSetName = "foo") [0,1000000242,6973776840203d3d,6e616c58206c6153,6c6c6f430a3d4d20,74696d674c746365]
(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
命令允许您添加或删除主键或外键约束,以及向表中添加列。
以下SQL查询显示了向表添加或删除约束的示例。 可以将主键和外键约束添加到具有逗号分隔值的多个列中。 您可以通过传递两个或多个列名称值来创建组合键,如下面的示例所示。
定义主键或复合键
ALTER TABLE table_name ADD CONSTRAINT PRIMARY KEY ( column_name ) NAMESPACE namespace
ALTER TABLE table_name ADD CONSTRAINT PRIMARY KEY ( column_name1, column_name2 ) NAMESPACE namespace
根据一个或多个键定义表之间的关系
ALTER TABLE table_name ADD CONSTRAINT FOREIGN KEY ( column_name ) REFERENCES referenced_table_name ( primary_column_name )
ALTER TABLE table_name ADD CONSTRAINT FOREIGN KEY ( column_name1, column_name2 ) REFERENCES referenced_table_name ( primary_column_name1, primary_column_name2 )
定义标识列
ALTER TABLE table_name ADD CONSTRAINT PRIMARY IDENTITY ( column_name ) NAMESPACE namespace
ALTER TABLE table_name ADD CONSTRAINT IDENTITY ( column_name ) NAMESPACE namespace
删除约束/关系/身份
ALTER TABLE table_name DROP CONSTRAINT PRIMARY KEY ( column_name )
ALTER TABLE table_name DROP CONSTRAINT PRIMARY KEY ( column_name1, column_name2 )
ALTER TABLE table_name DROP CONSTRAINT FOREIGN KEY ( column_name )
ALTER TABLE table_name DROP CONSTRAINT FOREIGN KEY ( column_name1, column_name2 )
ALTER TABLE table_name DROP CONSTRAINT PRIMARY IDENTITY ( column_name )
ALTER TABLE table_name DROP CONSTRAINT IDENTITY ( column_name )
参数 | 描述 |
---|---|
table_name |
正在编辑的表的名称。 |
column_name |
要向其添加约束的列的名称。 |
referenced_table_name |
外键引用的表的名称。 |
primary_column_name |
外键引用的列的名称。 |
表架构应该是唯一的,并且不在多个表之间共享。 此外,对于主键、主标识和标识约束,命名空间是必需的。
此 ALTER TABLE
命令允许直接通过SQL添加或删除主标识表和辅助标识表列的约束。
以下示例通过添加约束来添加主标识和辅助标识。
ALTER TABLE t1 ADD CONSTRAINT PRIMARY IDENTITY (id) NAMESPACE 'IDFA';
ALTER TABLE t1 ADD CONSTRAINT IDENTITY(id) NAMESPACE 'IDFA';
也可以通过删除约束来删除身份,如下面的示例所示。
ALTER TABLE t1 DROP CONSTRAINT PRIMARY IDENTITY (c1) ;
ALTER TABLE t1 DROP CONSTRAINT IDENTITY (c1) ;
查看文档 在临时数据集中设置身份 以了解更多详细信息。
以下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
下表列出了在使用向表中添加列时接受的数据类型 Postgres SQL、 XDM和 Accelerated Database Recovery (ADR)。
— | PSQL客户端 | XDM | ADR | 描述 |
---|---|---|---|---|
1 | bigint |
int8 |
bigint |
一种数值数据类型,用于存储从 — 9,223,372,036,854,775,807到9,223,372,036,854,775,807之间的大整数,以8字节为单位。 |
2 | integer |
int4 |
integer |
用于存储 — 2,147,483,648到2,147,483,647 (以4字节为单位)的整数的数字数据类型。 |
3 | smallint |
int2 |
smallint |
用于存储–32,768到215-1 32,767之间的整数(以2字节为单位)的数字数据类型。 |
4 | tinyint |
int1 |
tinyint |
用于存储0到255之间的整数(以1字节为单位)的数字数据类型。 |
5 | varchar(len) |
string |
varchar(len) |
可变大小的字符数据类型。 varchar 当列数据条目的大小差别很大时,最好使用它。 |
6 | double |
float8 |
double precision |
FLOAT8 和 FLOAT 是的有效同义词 DOUBLE PRECISION . double precision 是浮点数据类型。 浮点值以8字节为单位存储。 |
7 | double precision |
float8 |
double precision |
FLOAT8 是的有效同义词 double precision .double precision 是浮点数据类型。 浮点值以8字节为单位存储。 |
8 | date |
date |
date |
此 date 数据类型是4字节存储的日历日期值,没有任何时间戳信息。 有效日期的范围为01-01-0001到12-31-9999。 |
9 | datetime |
datetime |
datetime |
一种数据类型,用于存储以日历日期和时间表示的时间瞬间。 datetime 包括:年、月、日、小时、秒和分数。 A datetime 声明可以包括这些时间单位中在序列中连接任何子集,或者甚至包括单个时间单位。 |
10 | char(len) |
string |
char(len) |
此 char(len) 关键字用于指示项目是固定长度的字符。 |
以下SQL查询显示了向数据库/模式添加表的示例。
ALTER TABLE table_name ADD SCHEMA database_name.schema_name
无法将ADLS表和视图添加到DWH数据库/架构中。
以下SQL查询显示了从数据库/模式中删除表的示例。
ALTER TABLE table_name REMOVE SCHEMA database_name.schema_name
无法从物理链接的DWH数据库/架构中删除DWH表和视图。
参数
参数 | 描述 |
---|---|
table_name |
正在编辑的表的名称。 |
column_name |
要添加列的名称。 |
data_type |
要添加列的数据类型。 支持的数据类型包括:bigint、char、string、date、datetime、double、double precision、integer、smallint、tinyint、varchar。 |
此 SHOW PRIMARY KEYS
command列出给定数据库的所有主键约束。
SHOW PRIMARY KEYS
tableName | columnName | datatype | namespace
------------------+----------------------+----------+-----------
table_name_1 | column_name1 | text | "ECID"
table_name_2 | column_name2 | text | "AAID"
此 SHOW FOREIGN KEYS
command列出给定数据库的所有外键约束。
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"
此 SHOW DATAGROUPS
命令返回所有关联数据库的表。 对于每个数据库,该表包括方案、组类型、子类型、子名称和子ID。
SHOW DATAGROUPS
Database | Schema | GroupType | ChildType | ChildName | ChildId
-------------+-------------------+-----------+----------------------+----------------------------------------------------+--------------------------------------
adls_db | adls_scheema | ADLS | Data Lake Table | adls_table1 | 6149ff6e45cfa318a76ba6d3
adls_db | adls_scheema | ADLS | Accelerated Store | _table_demo1 | 22df56cf-0790-4034-bd54-d26d55ca6b21
adls_db | adls_scheema | ADLS | View | adls_view1 | c2e7ddac-d41c-40c5-a7dd-acd41c80c5e9
adls_db | adls_scheema | ADLS | View | adls_view4 | b280c564-df7e-405f-80c5-64df7ea05fc3
此 SHOW DATAGROUPS FOR
“table_name”命令返回包含该参数作为其子项的所有关联数据库的表。 对于每个数据库,该表包括方案、组类型、子类型、子名称和子ID。
SHOW DATAGROUPS FOR 'table_name'
参数
table_name
:要为其查找关联数据库的表的名称。 Database | Schema | GroupType | ChildType | ChildName | ChildId
-------------+-------------------+-----------+----------------------+----------------------------------------------------+--------------------------------------
dwh_db_demo | schema2 | QSACCEL | Accelerated Store | _table_demo2 | d270f704-0a65-4f0f-b3e6-cb535eb0c8ce
dwh_db_demo | schema1 | QSACCEL | Accelerated Store | _table_demo2 | d270f704-0a65-4f0f-b3e6-cb535eb0c8ce
qsaccel | profile_aggs | QSACCEL | Accelerated Store | _table_demo2 | d270f704-0a65-4f0f-b3e6-cb535eb0c8ce