BigQuery如何选取列名包含指定字符串的所有列（含前后缀匹配）

BigQuery不支持直接在静态SELECT语句里按列名匹配规则动态选列，需要先通过系统元数据视图筛选符合要求的列，再拼接动态SQL执行，三种匹配规则的实现方式如下。
示例场景的测试列：a_test、b_test、c_test、d_test、e、f、g、test_h、test_i，匹配字符串为test。

通用前置说明 #

以下代码中需要替换3类占位参数：

• 替换为你自己的GCP项目名
• 替换为你自己的BigQuery数据集名
• 替换为你自己的表名
  如果当前查询编辑器已经选中了对应项目和数据集，可省略项目、数据集的前缀路径。

1. 前缀匹配（列名以指定字符串开头） #

使用STARTS_WITH()函数做前缀判断，匹配示例中返回列：test_h、test_i。
可先单独查询符合规则的列名，校验结果是否符合预期：

SELECT column_name
FROM `项目名.数据集名.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = '表名'
  AND STARTS_WITH(column_name, 'test');

校验通过后，执行动态SQL直接查询所有匹配列的内容：

EXECUTE IMMEDIATE FORMAT("""
SELECT %s
FROM `项目名.数据集名.表名`
""", (
  SELECT STRING_AGG(FORMAT('`%s`', column_name), ', ')
  FROM `项目名.数据集名.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = '表名'
    AND STARTS_WITH(column_name, 'test')
));

2. 后缀匹配（列名以指定字符串结尾） #

使用ENDS_WITH()函数做后缀判断，匹配示例中返回列：a_test、b_test、c_test、d_test。
校验列名的查询语句：

SELECT column_name
FROM `项目名.数据集名.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = '表名'
  AND ENDS_WITH(column_name, 'test');

查询匹配列内容的动态SQL：

EXECUTE IMMEDIATE FORMAT("""
SELECT %s
FROM `项目名.数据集名.表名`
""", (
  SELECT STRING_AGG(FORMAT('`%s`', column_name), ', ')
  FROM `项目名.数据集名.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = '表名'
    AND ENDS_WITH(column_name, 'test')
));

3. 包含匹配（列名任意位置存在指定字符串） #

使用LIKE '%关键词%'做全位置匹配，匹配示例中返回列：a_test、b_test、c_test、d_test、test_h、test_i。
校验列名的查询语句：

SELECT column_name
FROM `项目名.数据集名.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = '表名'
  AND column_name LIKE '%test%';

查询匹配列内容的动态SQL：

EXECUTE IMMEDIATE FORMAT("""
SELECT %s
FROM `项目名.数据集名.表名`
""", (
  SELECT STRING_AGG(FORMAT('`%s`', column_name), ', ')
  FROM `项目名.数据集名.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = '表名'
    AND column_name LIKE '%test%'
));

兼容性说明 #

代码中拼接列名时用FORMAT('%s', column_name)给列名加了反引号包裹，兼容列名包含特殊字符、空格、关键字的场景，不会出现语法错误。
如果需要排除嵌套字段、重复字段，可以在查询INFORMATION_SCHEMA.COLUMNS时加对应的过滤条件即可。

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