时间戳

首页 > 解决方案 > 如何从 BigQuery 读取 Google Storage 存储桶中的每个文件并为存储桶中的每个文件创建一个表？

google-bigquery - 如何从 BigQuery 读取 Google Storage 存储桶中的每个文件并为存储桶中的每个文件创建一个表？

问题描述

我有一个包含 28 个文件的存储桶，我想将它们加载到不同的表中（存储桶中每个文件一个表）。

存储桶中的每个文件如下所示：

file_1_2020.csv
file_2_2020.csv
..
file_28_2020.csv

我希望该表具有文件名的前 6 个字符 + _table（它们都是带有标题的 csv）。例如，file_1_table。

我在博客中读到，我可以通过创建一个列出存储桶中所有文件的联合查询来实现这一点。然后我可以遍历所有名称并创建我需要的表。问题是我在 GCP 上阅读的所有文档都是关于 Cloud SQL 中的联合查询，所以我真的不知道如何让 BigQuery 读取我的存储桶。

我怎样才能做到这一点？是否通过联合查询完成并不重要（但我想知道，因为了解和学习新事物总是很好）。另外，我想按日期时间对表进行分区和聚类，这是每个文件的第一列（每个文件的架构是datetime:TIMESTAMP,col1:FLOAT,col2:FLOAT）

标签： google-bigquerygoogle-cloud-storagefederated-queriesfederated-table

解决方案

您可以使用EXECUTE IMMEDIATE来实现这一点：

步骤 1：创建一个外部表以获取存储桶中的文件列表

CREATE EXTERNAL TABLE mydataset.mytable OPTIONS (format = 'CSV', uris = ['gs://bucket_name/*.csv']);

步骤 2：使用 EXECUTE IMMEDIATE 动态创建表

DECLARE FILE_LIST ARRAY<STRING>;
DECLARE TABLE_NAME STRING;
DECLARE I INT64 DEFAULT 1;
DECLARE CNT INT64 DEFAULT 0;
SET FILE_LIST = ARRAY(SELECT DISTINCT _FILE_NAME as FILENAME FROM mydataset.mytable);
SET CNT = ARRAY_LENGTH(FILE_LIST);
WHILE I <= CNT 
DO
  SET TABLE_NAME = CONCAT(SUBSTR(REPLACE(SUBSTR(FILE_LIST[ORDINAL(i)], INSTR(FILE_LIST[ORDINAL(i)], '/', -1) + 1) ,'.csv', ''), 1, 6), '_table');
  EXECUTE IMMEDIATE "CREATE EXTERNAL TABLE mydataset." || TABLE_NAME || " OPTIONS (format = 'CSV', uris = ['" || FILE_LIST[ORDINAL(I)] || "'])";
  SET I = I + 1;
END WHILE;

推荐阅读