如何在Snowflake Schema中创建含各表最新load_time的视图？

针对你的需求，Union All是核心实现方式，但手动编写100多张表的Union语句效率太低，推荐结合Snowflake的元数据系统动态生成查询，以下是具体步骤：

1. 核心逻辑说明 #

每张表独立查询MAX(load_time)并带上表名，再用Union All合并所有结果即可。Join不适用，因为这些表没有可关联的业务键，无需进行表关联操作。

2. 动态生成查询语句 #

首先运行以下SQL，生成所有目标表的子查询语句：

SELECT 'SELECT ''' || TABLE_NAME || ''' AS table_name, MAX(load_time) AS max_load_time FROM ' || TABLE_SCHEMA || '.' || TABLE_NAME || ' UNION ALL'
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c 
  ON t.TABLE_CATALOG = c.TABLE_CATALOG 
  AND t.TABLE_SCHEMA = c.TABLE_SCHEMA 
  AND t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_SCHEMA = '你的Schema名称' -- 替换为实际Schema
  AND t.TABLE_TYPE = 'BASE TABLE' -- 仅包含实体表，排除视图
  AND c.COLUMN_NAME = 'LOAD_TIME'; -- 确保表包含目标列

该查询会输出类似以下的结果：

SELECT 'table_a' AS table_name, MAX(load_time) AS max_load_time FROM my_schema.table_a UNION ALL
SELECT 'table_b' AS table_name, MAX(load_time) AS max_load_time FROM my_schema.table_b UNION ALL
...

3. 创建视图 #

将生成的语句复制，删除最后一行的UNION ALL，然后套入视图创建语句：

CREATE OR REPLACE VIEW schema_load_times AS
-- 粘贴生成的子查询（已删除最后一个UNION ALL）
SELECT 'table_a' AS table_name, MAX(load_time) AS max_load_time FROM my_schema.table_a
UNION ALL
SELECT 'table_b' AS table_name, MAX(load_time) AS max_load_time FROM my_schema.table_b
-- ... 所有表的子查询

4. 自动化维护（可选） #

如果后续会新增表，可创建存储过程自动更新视图，避免重复手动操作：

CREATE OR REPLACE PROCEDURE create_load_time_view()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
  sql_stmt VARCHAR;
BEGIN
  -- 自动生成Union All语句
  SELECT LISTAGG('SELECT ''' || TABLE_NAME || ''' AS table_name, MAX(load_time) AS max_load_time FROM ' || TABLE_SCHEMA || '.' || TABLE_NAME, ' UNION ALL ')
  INTO sql_stmt
  FROM INFORMATION_SCHEMA.TABLES t
  JOIN INFORMATION_SCHEMA.COLUMNS c 
    ON t.TABLE_CATALOG = c.TABLE_CATALOG 
    AND t.TABLE_SCHEMA = c.TABLE_SCHEMA 
    AND t.TABLE_NAME = c.TABLE_NAME
  WHERE t.TABLE_SCHEMA = '你的Schema名称'
    AND t.TABLE_TYPE = 'BASE TABLE'
    AND c.COLUMN_NAME = 'LOAD_TIME';

  -- 创建/更新视图
  sql_stmt := 'CREATE OR REPLACE VIEW schema_load_times AS ' || sql_stmt;
  EXECUTE IMMEDIATE sql_stmt;
  RETURN '视图已成功创建/更新';
END;
$$;

调用存储过程更新视图：

CALL create_load_time_view();

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