如何在Snowflake中动态添加非RAW_前缀字段到SELECT语句

在Snowflake中处理含大量字段的表时，手动筛选非'RAW_'前缀字段效率极低，以下两种方案可实现动态生成符合要求的查询语句：

方案1：生成动态SELECT语句 #

利用Snowflake内置的INFORMATION_SCHEMA.COLUMNS视图，自动筛选并拼接非'RAW_'前缀的字段，生成可直接执行的SELECT语句：

-- 替换YOUR_DATABASE和YOUR_SCHEMA为实际的数据库和模式名
SELECT 'SELECT ' || LISTAGG(COLUMN_NAME, ', ') || ' FROM YOUR_DATABASE.YOUR_SCHEMA.reviews;' AS dynamic_select_sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'YOUR_DATABASE'
  AND TABLE_SCHEMA = 'YOUR_SCHEMA'
  AND TABLE_NAME = 'reviews'
  AND COLUMN_NAME NOT LIKE 'RAW_%';

执行上述语句后，会返回一条完整的SELECT语句（例如SELECT name, acting_rating FROM YOUR_DATABASE.YOUR_SCHEMA.reviews;），直接复制该结果执行即可获取所有非'RAW_'前缀字段的数据。

方案2：创建持久化视图（一劳永逸） #

如果需要频繁查询该表的非'RAW_'字段，可创建一个视图，后续直接查询视图即可：

方式A：手动生成视图（简单直接） #

先运行方案1中的语句，得到所有非'RAW_'字段的拼接字符串，再替换到以下语句创建视图：

CREATE OR REPLACE VIEW YOUR_DATABASE.YOUR_SCHEMA.reviews_clean AS
SELECT 
  name,
  acting_rating,
  -- 其他非RAW_字段...
FROM YOUR_DATABASE.YOUR_SCHEMA.reviews;

方式B：动态SQL自动创建视图（需对应权限） #

通过存储过程自动完成视图创建，无需手动复制字段：

DECLARE
  sql_stmt STRING;
BEGIN
  SELECT 'CREATE OR REPLACE VIEW YOUR_DATABASE.YOUR_SCHEMA.reviews_clean AS SELECT ' || LISTAGG(COLUMN_NAME, ', ') || ' FROM YOUR_DATABASE.YOUR_SCHEMA.reviews;'
  INTO sql_stmt
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_CATALOG = 'YOUR_DATABASE'
    AND TABLE_SCHEMA = 'YOUR_SCHEMA'
    AND TABLE_NAME = 'reviews'
    AND COLUMN_NAME NOT LIKE 'RAW_%';
  
  EXECUTE IMMEDIATE sql_stmt;
END;

创建完成后，直接查询视图即可获取过滤后的数据集：

SELECT * FROM YOUR_DATABASE.YOUR_SCHEMA.reviews_clean;

测试数据示例 #

insert into reviews values
    ('abc', 4, NULL, 1, 'NO'),
    ('xyz', 3, 'some', 1, 'haha'),
    ('lmn', 1, 'what', 4, NULL);

手动方案对比（仅作参考） #

select 
name , 
acting_rating 
from reviews;

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