BigQuery是否有类似Oracle的批量查询功能？如何批量查询多项目数据？

当然可以不用循环逐个传入项目，你可以通过动态SQL结合数组的方式批量查询所有目标项目的数据，以下是具体实现方案：

方法：使用EXECUTE IMMEDIATE + 项目数组 #

这种方式适合你有明确的项目列表，或者可以通过查询获取项目列表的场景：

DECLARE project_list ARRAY<STRING> DEFAULT [
  'project-1',
  'project-2',
  'project-3' -- 替换成你的目标项目列表
];

EXECUTE IMMEDIATE (
  SELECT STRING_AGG(
    FORMAT("""
      SELECT catalog_name as project_name
           , schema_name as dataset_name
           , option_name
           , option_type
           , option_value
           , CURRENT_TIMESTAMP as _insert_timestamp
      FROM `%s.INFORMATION_SCHEMA.SCHEMATA_OPTIONS`
    """, project),
    "\nUNION ALL\n"
  )
  FROM UNNEST(project_list) AS project
);

代码说明： #

• DECLARE定义项目数组：把你需要查询的所有项目ID放到project_list数组中
• UNNEST展开数组：将数组拆分为单个项目行
• FORMAT生成单项目查询：用FORMAT函数把每个项目ID替换到你的查询模板中
• STRING_AGG拼接查询：用UNION ALL把所有单项目的查询语句拼接成一个完整的查询
• EXECUTE IMMEDIATE执行动态SQL：运行拼接好的完整查询，一次性返回所有项目的结果

注意事项 #

• 确保你的账号对数组中所有项目的INFORMATION_SCHEMA.SCHEMATA_OPTIONS有读取权限，否则会出现权限错误
• 如果项目数量较多，BigQuery会自动处理查询的并行执行，无需担心性能问题
• 若你需要动态获取当前账号有权限的所有项目，可以先查询bigquery-public-data.google_cloud_projects.projects（需对应权限）获取项目列表，再替换上述的project_list数组

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