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 )
以下子部分提供了有关可在查询中使用的附加条款的详细信息,前提是这些条款遵循上述格式。
此子句可用于基于快照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
子句 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 |
如果指定了此值,则在表执行此操作时不会引发异常 not 存在。 |
的 CREATE DATABASE
命令创建ADLS数据库。
CREATE DATABASE [IF NOT EXISTS] db_name
的 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;
查询服务支持一个可选的会话级设置,以作为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
如果表 does 存在,并且 false
如果表格显示 not 存在。
通过在运行语句之前验证表是否存在, 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
用于填充target表。
SKU | _experience(体验) | 数量 | priceTotal |
---|---|---|---|
product-id-1 | ("("("(A,pass,B,NULL)")") | 5 | 10.5 |
product-id-5 | ("("("(A, pass, B,NULL)")") | ||
product-id-2 | ("("(AF, C, D,NULL)")") | 6 | 40 |
product-id-4 | ("("(BM,传递, NA,NULL)")") | 3 | 12 |
以下子部分涵盖查询服务支持的Spark SQL命令。
的 SET
命令会设置属性,并返回现有属性的值或列出所有现有属性。 如果为现有属性键值提供了,则会覆盖旧值。
SET property_key = property_value
参数 | 描述 |
---|---|
property_key |
要列出或更改的属性的名称。 |
property_value |
您希望将属性设置为的值。 |
要返回任何设置的值,请使用 SET [property key]
没有 property_value
.
以下各分节涵盖 PostgreSQL 查询服务支持的命令。
的 ANALYZE TABLE
命令计算加速存储上表的统计信息。 统计数据是根据对加速存储的给定表执行的CTAS或ITAS查询计算的。
示例
ANALYZE TABLE <original_table_name>
以下是使用 ANALYZE TABLE
命令:-
计算值 | 描述 |
---|---|
field |
表中列的名称。 |
data-type |
每列可接受的数据类型。 |
count |
包含此字段非空值的行数。 |
distinct-count |
此字段的唯一值或不同值的数量。 |
missing |
此字段具有空值的行数。 |
max |
分析表中的最大值。 |
min |
分析表中的最小值。 |
mean |
分析表的平均值。 |
stdev |
分析表的标准偏差。 |
的 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 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 FOREIGN KEY ( column_name ) REFERENCES referenced_table_name ( primary_column_name )
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 FOREIGN KEY ( column_name )
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 |
一种用于存储2个字节中–32,768到215-1 32,767的整数的数值数据类型。 |
4 | tinyint |
int1 |
tinyint |
一种用于存储1字节内0到255之间的整数的数字数据类型。 |
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,整数, 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"
的 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 | Data Warehouse Table | _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 | Data Warehouse Table | _table_demo2 | d270f704-0a65-4f0f-b3e6-cb535eb0c8ce
dwh_db_demo | schema1 | QSACCEL | Data Warehouse Table | _table_demo2 | d270f704-0a65-4f0f-b3e6-cb535eb0c8ce
qsaccel | profile_aggs | QSACCEL | Data Warehouse Table | _table_demo2 | d270f704-0a65-4f0f-b3e6-cb535eb0c8ce