如何让BigQuery读取Drive同Schema CSV并实现查询自动更新

答案：完全可以通过BigQuery直接实现，无需借助Apps Script，且支持自动同步文件夹内的文件变化 #

1. 核心问题逐一解答

• 能不能查询Drive文件夹内同Schema的CSV？当然可以，BigQuery支持创建指向Drive文件夹的外部表，自动读取所有符合条件的CSV文件。
• 新增/删除文件后查询是否自动更新？会自动更新，因为外部表是动态读取文件夹内容的，每次查询都会扫描当前文件夹内的有效文件，无需手动刷新。
• 是否需要Apps Script？完全不需要，纯BigQuery控制台或SQL就能完成全部配置。

2. 具体配置步骤

• 前置准备：

  • 确保Drive文件夹内所有CSV文件的Schema完全一致（列名、列数、数据类型必须统一），避免查询时出现报错或数据错乱。
  • 给BigQuery服务账号分配Drive文件夹的访问权限：找到项目的BigQuery服务邮箱（在项目IAM设置中可查看），将Drive文件夹共享给该邮箱，权限设为「查看者」即可。

• 创建外部表：

  1. 打开BigQuery控制台，进入目标数据集，点击「创建表」。
  2. 在「数据源」区域选择「Google Cloud Storage 或 Google Drive」，输入Drive文件夹的路径：格式为 drive://folder/<你的文件夹ID>，或者直接粘贴文件夹的共享链接（BigQuery会自动解析）。
  3. 文件格式选择「CSV」，然后配置CSV参数：
     • 勾选「跳过表头行」（如果你的CSV文件第一行是列名）。
     • 设置分隔符（默认逗号，根据你的CSV实际格式调整）、引号字符等。
  4. 定义表Schema：
     • 可以选择「自动检测」让BigQuery从样本文件识别Schema，但建议手动输入并确认，确保和所有文件的Schema完全匹配。
     • 逐个添加列名和对应的数据类型（比如STRING、INT64、FLOAT64等）。
  5. 表类型选择「外部表」，填写表名，点击「创建表」完成配置。

• 查询外部表：
  创建完成后，直接用标准SQL查询即可，比如：

  SELECT * FROM `your-project-id.your-dataset-id.your-external-table-name`;
  

  每次执行查询时，BigQuery会自动读取当前Drive文件夹内的所有CSV文件，新增或删除文件后，下次查询就会返回最新数据。如果不想使用查询缓存，可以在SQL前添加 --no_cache 注释，或者在控制台查询设置中禁用缓存。

3. 关键注意事项

• Schema一致性要求严格：所有CSV文件的Schema必须完全匹配，否则BigQuery可能返回错误或自动忽略不匹配的文件。如果有文件Schema不同，建议单独处理或修正后再放入文件夹。
• 权限配置不能少：一定要确保BigQuery服务账号能访问目标Drive文件夹，如果是域内账号，也可以通过域级权限配置实现自动授权。
• 非CSV文件自动忽略：文件夹内的非CSV文件会被BigQuery自动过滤，无需额外设置。
• 性能优化建议：如果文件夹内文件数量多、体积大，查询速度可能较慢。如果不需要实时数据，可以定期将数据加载到BigQuery内部表；如果需要实时访问，外部表是最优选择。

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