DQL 语法

最近更新时间:2023-03-09 14:35:51

我的收藏

SELECT

语法

SELECT [ hints ] [ ALL | DISTINCT ] { named_expression | star_clause } [, ...]
FROM from_item [, ...]
[ LATERAL VIEW clause ]
[ PIVOT clause ]
[ WHERE clause ]
[ GROUP BY clause ]
[ HAVING clause]
[ QUALIFY clause ]
from_item
{ table_name [ TABLESAMPLE clause ] [ table_alias ] |
JOIN clause |
[ LATERAL ] table_valued_function [ table_alias ] |
VALUES clause |
[ LATERAL ] ( query ) [ TABLESAMPLE clause ] [ table_alias ] }
named_expression
expression [ column_alias ]
star_clause
[ { table_name | view_name } . ] *

TABLE METADATA

支持四段式的 Iceberg 表元数据查询,包括:historysnapshotsfilesmanifests

语法

SELECT select_expr (, select_expr)*
FROM `Catalog`.`db`.`tableName${history|snapshots|files|manifests|partitions|all_data_files|all_manifests}`
[WHERE where_condition]
[LIMIT [offset,] rows]

示例

SELECT * FROM `DataLakeCatalog`.`validation`.`dempts$history` ORDER BY snapshot_id DESC LIMIT 1;
SELECT * FROM `DataLakeCatalog`.`validation`.`dempts$snapshots` ORDER BY snapshot_id LIMIT 1;
SELECT * FROM `DataLakeCatalog`.`validation`.`dempts$files` ORDER BY file_size_in_bytes LIMIT 1;
SELECT * FROM `DataLakeCatalog`.`validation`.`dempts$manifests` ORDER BY length LIMIT 1;
SELECT * FROM `DataLakeCatalog`.`validation`.`dempts$partitions` LIMIT 10;
SELECT * FROM `DataLakeCatalog`.`validation`.`dempts$all_data_files` LIMIT 10;
SELECT * FROM `DataLakeCatalog`.`validation`.`dempts$all_manifests` LIMIT 10;

TIME TRAVEL

FOR SYSTEM_TIME AS OF/ TIMESTAMP AS OF

Spark3.3以上支持,支持字符串和 Unix 时间戳两种格式

示例

SELECT empno FROM sales.emp FOR SYSTEM_TIME AS OF '1986-10-26 01:21:00';
SELECT empno FROM sales.emp FOR SYSTEM_TIME AS OF 12324235546;
SELECT empno FROM sales.emp TIMESTAMP AS OF '1986-10-26 01:21:00';
SELECT empno FROM sales.emp TIMESTAMP AS OF 11111;

FOR SYSTEM_VERSION AS OF/ VERSION AS OF (Spark 3.3支持)

支持字符串和 snapshot id

示例

SELECT empno FROM sales.emp VERSION AS OF 'Snapshot123456789';
SELECT empno FROM sales.emp VERSION AS OF 11111;
SELECT empno FROM sales.emp FOR SYSTEM_VERSION AS OF 'Snapshot123456789';
SELECT empno FROM sales.emp FOR SYSTEM_VERSION AS OF 11111;

AT_TIMESTAMP_XXXX

示例

SELECT * FROM `sales`.`emp$at_timestamp_1111`

SNAPSHOT_ID_XXXX

示例

SELECT * FROM `sales`.`emp$snapshot_id_1111`