如何在BigQuery中选择前缀为lm_和vs_的列？

BigQuery支持通过元数据查询结合动态SQL的方式，实现按前缀匹配选择列，以下是两种可行方法：

方法一：手动生成目标列列表 #

• 先查询表的元数据，筛选出前缀为lm_和vs_的列：

SELECT STRING_AGG(column_name, ', ') AS selected_columns
FROM `project-id.dataset.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'table'
AND (column_name LIKE 'lm_%' OR column_name LIKE 'vs_%');

• 将查询结果得到的列列表，和固定列charttime, id, gender组合，替换到原查询中，最终语句示例：

SELECT
  charttime,
  id,
  gender,
  lm_col1, lm_col2, -- 替换为实际的lm_前缀列
  vs_col1, vs_col2  -- 替换为实际的vs_前缀列
FROM `project-id.dataset.table`;

方法二：用动态SQL自动执行查询 #

• 定义变量存储筛选后的列名：

DECLARE cols STRING;

SET cols = (
  SELECT STRING_AGG(column_name, ', ')
  FROM `project-id.dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = 'table'
  AND (column_name LIKE 'lm_%' OR column_name LIKE 'vs_%')
);

• 使用EXECUTE IMMEDIATE执行动态生成的查询：

EXECUTE IMMEDIATE FORMAT("""
  SELECT
    charttime,
    id,
    gender,
    %s
  FROM `project-id.dataset.table`
""", cols);

注意事项：

• 请将project-id.dataset.table替换为你实际的项目ID、数据集和表名
• 使用动态SQL需要具备执行EXECUTE IMMEDIATE的对应权限

内容的提问来源于stack exchange，提问作者Muhammad Ikhwan Perwira