如何查询Snowflake中cluster_by字段有值的全部数据表列表

你可以通过show tables in <database name>语句查询指定数据库下的所有数据表，返回结果中的cluster_by列会标识对应数据表是否启用了聚簇功能。如果需要直接筛选出所有cluster_by字段存在值的表，可通过以下两种方案实现：

方案1：搭配RESULT_SCAN过滤SHOW TABLES结果 #

SHOW TABLES官方公开的语法仅支持如下过滤条件：

SHOW [ TERSE ] TABLES [ HISTORY ] [ LIKE '<pattern>' ]
                                  [ IN { ACCOUNT | DATABASE [ <db_name> ] | SCHEMA [ <schema_name> ] } ]
                                  [ STARTS WITH '<name_string>' ]
                                  [ LIMIT <rows> [ FROM '<name_string>' ] ]

注：原生语法不支持直接对cluster_by列做过滤，因此需要对返回结果做二次处理。

操作步骤：

• 第一步：执行SHOW TABLES查询目标范围的所有表，以查询指定数据库为例：
  SHOW TABLES IN DATABASE <替换为你的数据库名称>;
• 第二步：执行RESULT_SCAN过滤聚簇表：

SELECT 
  "name" AS table_name,
  "database_name",
  "schema_name",
  "cluster_by"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) 
WHERE "cluster_by" IS NOT NULL 
  AND TRIM("cluster_by") != '';

如果需要查询整个账号下的所有聚簇表，把第一步的SHOW语句修改为SHOW TABLES IN ACCOUNT;即可。

方案2：直接查询INFORMATION_SCHEMA系统视图 #

该方案不需要分两次执行查询，更适合自动化脚本使用：

SELECT 
  table_catalog AS database_name,
  table_schema,
  table_name,
  cluster_by
FROM information_schema.tables
-- 可根据需求加范围限定条件，比如限定指定数据库：
-- WHERE table_catalog = '<替换为你的数据库名称>'
WHERE cluster_by IS NOT NULL 
  AND TRIM(cluster_by) != '';

内容的提问来源于stack exchange，提问作者AlexD