如何无需SHOW COLUMNS大规模识别Snowflake中的虚拟列

针对schema级2.5万张表量级的虚拟列识别需求，可采用以下两种成熟方案，完全规避逐表查询、1万条返回上限的问题：

方案1：全量视图查询（效率最高，推荐优先使用） #

直接查询Snowflake内置的账户级全量视图SNOWFLAKE.ACCOUNT_USAGE.COLUMNS，该视图不受information_schema的字段限制、也没有SHOW命令的1万条返回上限，单条SQL即可覆盖目标范围：

• 视图中COLUMN_KIND字段会明确标记列类型，值为VIRTUAL_COLUMN即为虚拟列
• 仅存在最多3小时的元数据同步延迟，适合绝大多数非实时要求的扫描场景
• 查询时记得加DELETED IS NULL条件，过滤已经被删除的表、列历史记录

参考SQL：

SELECT
  TABLE_CATALOG AS DATABASE_NAME,
  TABLE_SCHEMA,
  TABLE_NAME,
  COLUMN_NAME,
  DATA_TYPE
FROM SNOWFLAKE.ACCOUNT_USAGE.COLUMNS
WHERE
  DELETED IS NULL
  AND TABLE_CATALOG = '目标数据库名'
  AND TABLE_SCHEMA = '目标SCHEMA名'
  AND COLUMN_KIND = 'VIRTUAL_COLUMN';

方案2：分块SHOW查询（无ACCOUNT_USAGE权限时使用） #

如果账号没有ACCOUNT_USAGE视图的查询权限，不需要逐表执行SHOW COLUMNS或者DESC TABLE，可以利用SHOW COLUMNS自带的前缀过滤参数，把全量表拆成多个小批次查询，把单批次返回结果控制在1万条以内即可：

• 按表名首字符（A-Z、0-9、下划线等特殊字符）拆分查询批次，2.5万张表拆成30个左右批次即可，每个批次平均返回不到1000条结果，远低于上限
• 每次查询后用RESULT_SCAN拉取结果写入临时表，所有批次跑完后统一筛选即可，整体耗时仅需数分钟
• 该方案拿到的是实时元数据，没有同步延迟

参考执行逻辑：

-- 初始化临时表存储所有列信息
SHOW COLUMNS STARTS WITH 'A' IN SCHEMA 目标数据库.目标SCHEMA;
CREATE TEMP TABLE tmp_all_cols AS
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

-- 依次遍历剩余前缀（B到Z、0到9、_等），每次查询后追加结果到临时表
-- 示例遍历B前缀的逻辑：
-- SHOW COLUMNS STARTS WITH 'B' IN SCHEMA 目标数据库.目标SCHEMA;
-- INSERT INTO tmp_all_cols SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

-- 所有批次跑完后，统一筛选虚拟列
SELECT * FROM tmp_all_cols WHERE "kind" = 'VIRTUAL_COLUMN';

注意：不建议逐表执行DESC TABLE或单表单条SHOW COLUMNS，2.5万次查询会产生大量不必要的仓库负载，执行耗时会达到数小时，远高于上述两种方案。

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