BigQuery单语句建表并加载本地CSV数据的方法咨询

问题描述 #

希望在BigQuery中通过单条操作完成表创建并加载本地CSV文件数据，当前尝试的SQL语句存在错误，仅能完成建表无法插入数据，同时想了解Upsert、Merge等可行替代方法。

错误语句示例 #

CREATE OR REPLACE TABLE Project1.DataSet_Creation.tbl_Store_List_Full
( Store_Nbr string(255),Sister_Store string(255))
,
INSERT INTO Project1.DataSet_Creation.tbl_Store_List_Full (Store_Nbr,Sister_Store)
FROM  C:\GCP_Transition\tbl_Store_List_Full.csv

一、单操作完成建表+加载本地CSV的正确实现 #

BigQuery的SQL语句无法直接读取本地磁盘文件，需借助bq命令行工具完成单步建表+加载操作，示例如下：

bq load --source_format=CSV \
--skip_leading_rows=1 \ # 如果CSV文件包含表头行，添加此参数跳过
Project1.DataSet_Creation.tbl_Store_List_Full \
"C:\GCP_Transition\tbl_Store_List_Full.csv" \
Store_Nbr:string(255),Sister_Store:string(255)

命令说明 #

• --source_format=CSV：指定数据源格式为CSV
• --skip_leading_rows=1：跳过CSV第一行表头（若无需跳过可删除此参数）
• 表名参数：明确目标表的完整路径Project1.DataSet_Creation.tbl_Store_List_Full
• 本地文件路径：使用双引号包裹含特殊字符的Windows路径
• 字段定义：直接在命令末尾指定表结构，格式为字段名:数据类型

二、已建表后的数据加载方法 #

若已手动创建好目标表，可简化bq命令省略字段定义：

bq load --source_format=CSV --skip_leading_rows=1 \
Project1.DataSet_Creation.tbl_Store_List_Full \
"C:\GCP_Transition\tbl_Store_List_Full.csv"

三、Upsert/Merge（合并更新数据）的实现 #

如果需要实现「存在则更新，不存在则插入」的逻辑，可通过MERGE语句完成，但前提是先将本地CSV数据加载到BigQuery临时表，再执行合并操作：

步骤1：将CSV加载到临时表 #

bq load --source_format=CSV --skip_leading_rows=1 \
Project1.DataSet_Creation.tmp_store_list \
"C:\GCP_Transition\tbl_Store_List_Full.csv" \
Store_Nbr:string(255),Sister_Store:string(255)

步骤2：执行MERGE语句完成Upsert #

MERGE Project1.DataSet_Creation.tbl_Store_List_Full AS target
USING Project1.DataSet_Creation.tmp_store_list AS source
ON target.Store_Nbr = source.Store_Nbr
WHEN MATCHED THEN
  UPDATE SET Sister_Store = source.Sister_Store
WHEN NOT MATCHED THEN
  INSERT (Store_Nbr, Sister_Store)
  VALUES (source.Store_Nbr, source.Sister_Store);

逻辑说明 #

• 通过Store_Nbr字段匹配目标表与临时表的数据
• 匹配成功时：更新目标表的Sister_Store字段值
• 匹配失败时：插入临时表中的新数据行

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