数据探查 SQL 语法说明

简单查询

查询当前 Database 下的表：

SELECT id, name, dt
FROM orders
WHERE dt = '<partition_date>'
LIMIT 100;

查询指定 Database 下的表：

SELECT id, name
FROM sales.orders
WHERE id = 1001
LIMIT 100;

字段选择

SELECT name
FROM users
LIMIT 100;

过滤、排序和分页

SELECT id, name, amount
FROM orders
WHERE dt = '<partition_date>'
  AND amount > 100
ORDER BY amount DESC
LIMIT 100;

聚合查询

SELECT category, COUNT(*) AS cnt
FROM goods
GROUP BY category
ORDER BY cnt DESC
LIMIT 100;

多表关联查询

SELECT u.id, u.name, s.score
FROM users u
JOIN scores s
  ON u.id = s.id
WHERE s.score >= 80
LIMIT 100;

子查询

SELECT id, name
FROM users
WHERE id IN (
  SELECT user_id
  FROM orders
  WHERE dt = '<partition_date>'
)
LIMIT 100;

CTE 查询

WITH filtered AS (
  SELECT *
  FROM orders
  WHERE dt = '<partition_date>'
)
SELECT COUNT(*) AS cnt
FROM filtered;

Vector Search

vector_search(...) 用于基于向量列进行相似度检索。参数依次为表名、向量列名、查询向量和返回条数。

语法如下：

SELECT *
FROM vector_search('<database_name>.<table_name>', '<vector_column>', '<query_vector_json_array>', <top_k>);

示例如下。执行前，请在页面上方选择目标 Catalog。

SELECT *
FROM vector_search(
  'multimodal_samples.berkeley_deepdrive_100k_images',
  'image_embedding',
  '<query_vector_json_array>',
  3
);

Full-text Search

full_text_search(...) 用于基于文本列进行全文检索。参数依次为表名、文本列名、查询文本和返回条数。

语法如下：

SELECT *
FROM full_text_search('<database_name>.<table_name>', '<text_column>', '<query_text>', <top_k>);

查询关键词。执行前，请在页面上方选择目标 Catalog。

SELECT *
FROM full_text_search(
  'multimodal_samples.berkeley_deepdrive_100k_images',
  'tags',
  'traffic',
  5
);

查询短语：

SELECT *
FROM full_text_search(
  'multimodal_samples.berkeley_deepdrive_100k_images',
  'tags',
  '"traffic light"',
  5
);

创建和删除 Database

在 SQL 中使用 SCHEMA 语法，对应 DLF 中的 Database（数据库）。

CREATE SCHEMA IF NOT EXISTS demo_db;

DROP SCHEMA IF EXISTS demo_db CASCADE;

创建 Append 表

CREATE TABLE IF NOT EXISTS demo_db.orders (
  order_id BIGINT,
  price BIGINT,
  customer STRING
);

创建主键表

CREATE TABLE IF NOT EXISTS demo_db.orders_pk (
  order_id BIGINT,
  price BIGINT,
  customer STRING,
  PRIMARY KEY (order_id) NOT ENFORCED
);

创建分区表

分区字段需要先在表字段中声明，再通过 PARTITIONED BY 指定。

CREATE TABLE IF NOT EXISTS demo_db.events (
  event_id BIGINT,
  event_name STRING,
  dt STRING
)
PARTITIONED BY (dt);

修改表结构

ALTER TABLE demo_db.orders
ADD COLUMN remark STRING;

ALTER TABLE demo_db.orders
RENAME COLUMN remark TO comment;

删除表

DROP TABLE IF EXISTS demo_db.orders;

临时表和临时视图

临时对象仅在当前 SQL 提交内可见，适合在多语句中组织中间结果。

CREATE TEMPORARY TABLE tmp_users AS
SELECT * FROM (VALUES (1, 'alice'), (2, 'bob')) AS t(id, name);

SELECT COUNT(*) FROM tmp_users;

DROP TEMPORARY TABLE tmp_users;

CREATE TEMPORARY VIEW tmp_view AS
SELECT * FROM orders WHERE dt = '<partition_date>';

SELECT COUNT(*) FROM tmp_view;

DROP TEMPORARY VIEW tmp_view;

删除分区

ALTER TABLE demo_db.events
DROP PARTITION (dt = '<partition_date>');

查询表参数

SELECT key, value
FROM demo_db.orders$options;

查询 Schema 历史

SELECT *
FROM demo_db.orders$schemas;

查询 Snapshot

SELECT *
FROM demo_db.orders$snapshots
ORDER BY snapshot_id DESC
LIMIT 10;

查询 Tag

SELECT *
FROM demo_db.orders$tags;

查询 Manifest

SELECT *
FROM demo_db.orders$manifests
LIMIT 10;

查询分区统计

SELECT *
FROM demo_db.orders$partitions;

查询文件大小统计

SELECT *
FROM demo_db.orders$physical_files_size;

SELECT *
FROM demo_db.orders$referenced_files_size;

表存储概览

SELECT dt,
       COUNT(DISTINCT table_id) AS table_cnt,
       SUM(obj_size) + SUM(meta_obj_size) AS total_size,
       SUM(obj_size) AS total_data_obj_size,
       SUM(meta_obj_size) AS total_meta_obj_size
FROM system.table_summary
WHERE dt BETWEEN '<start_date>' AND '<end_date>'
GROUP BY dt
ORDER BY dt;

分区存储统计

SELECT database_name, table_name, partition_name,
       obj_cnt AS data_obj_cnt,
       obj_size AS data_obj_size
FROM system.partition_summary
WHERE dt = '<partition_date>'
ORDER BY data_obj_size DESC
LIMIT 100;

计算用量趋势

SELECT dt,
       SUM(cu) AS daily_cu,
       MIN(start_time) AS min_start_time,
       MAX(start_time) AS max_start_time
FROM system.compute_usage
WHERE dt BETWEEN '<start_date>' AND '<end_date>'
GROUP BY dt
ORDER BY dt;

权限授权查询

SELECT principal, principal_kind, principal_name,
       database_name, object_name, resource_type, access
FROM system.auth_privilege_grants
WHERE dt = '<partition_date>'
  AND access = 'SELECT'
ORDER BY database_name, object_name, principal;