如何用DDL在BigQuery从GCS文件创建原生表（适配Dataform）

一、CREATE TABLE报错的原因及解决方案 #

BigQuery标准CREATE TABLE语句不支持直接通过FORMAT参数从GCS文件导入数据——这个参数是UI导入流程或bq load命令的专属选项，不属于DDL语法范畴。要实现和UI导入一致的原生表效果，有两种可行的DDL方案：

方案1：临时外部表+CTAS语句 #

先创建临时外部表指向GCS的CSV文件，再将数据导入原生托管表：

-- 1. 创建临时外部表用于读取GCS数据
CREATE OR REPLACE EXTERNAL TABLE `project.dataset.temp_external_table`
OPTIONS (
  format = 'CSV',
  uris = ['gs://your-bucket/path/*.csv'],
  skip_leading_rows = 1 -- 根据CSV是否有表头调整
);

-- 2. 将外部表数据导入原生表
CREATE OR REPLACE TABLE `project.dataset.native_table`
AS SELECT * FROM `project.dataset.temp_external_table`;

-- 可选：清理临时外部表
DROP TABLE `project.dataset.temp_external_table`;

这种方式生成的是完全托管的原生表，和UI导入效果一致，适合放在Dataform sqlx文件中执行。

方案2：使用LOAD DATA语句（需BigQuery版本支持） #

如果你的BigQuery环境支持最新DDL语法，LOAD DATA是最贴近UI导入的写法：

-- 首次导入（目标表不存在或结构匹配）
LOAD DATA INTO `project.dataset.native_table`
FROM FILES (
  format = 'CSV',
  uris = ['gs://your-bucket/path/*.csv'],
  skip_leading_rows = 1
);

-- 覆盖现有数据导入
LOAD DATA INTO `project.dataset.native_table`
OPTIONS (write_disposition = 'WRITE_TRUNCATE')
FROM FILES (
  format = 'CSV',
  uris = ['gs://your-bucket/path/*.csv'],
  skip_leading_rows = 1
);

二、CREATE EXTERNAL TABLE的缓存警告处理 #

你遇到的缓存禁用警告，本质是因为外部表的数据存储在GCS，BigQuery无法像托管表那样缓存查询结果——毕竟GCS文件可能随时更新。这个警告不影响功能，但可以通过启用元数据缓存优化性能：
在创建外部表时添加metadata_cache_mode = 'AUTOMATIC'选项：

CREATE OR REPLACE EXTERNAL TABLE `project.dataset.external_table`
OPTIONS (
  format = 'CSV',
  uris = ['gs://your-bucket/path/*.csv'],
  skip_leading_rows = 1,
  metadata_cache_mode = 'AUTOMATIC'
);

启用后，BigQuery会缓存外部表的元数据（如文件列表、Schema信息），减少每次查询时的GCS扫描开销，但数据本身无法缓存——因为外部数据不在BigQuery托管存储内。

不过要注意：如果你的最终目标是生成原生托管表，外部表只是过渡工具，用上述方案1或2生成原生表后，就不会有缓存问题了——原生表的数据存储在BigQuery内部，默认启用查询缓存。

三、Dataform sqlx中的最佳实践 #

在Dataform里推荐用以下方式实现：

• 若用LOAD DATA，可直接将语句放在sqlx文件的主查询块或pre_operations中；
• 若用临时外部表+CTAS，可将临时表定义为前置操作，主表引用后再清理临时表。

示例sqlx代码：

config {
  type: "table",
  description: "从GCS CSV导入的原生托管表"
}

CREATE OR REPLACE TABLE ${self()}
AS SELECT * FROM `project.dataset.temp_external_table`;

pre_operations {
  CREATE OR REPLACE EXTERNAL TABLE `project.dataset.temp_external_table`
  OPTIONS (
    format = 'CSV',
    uris = ['gs://your-bucket/path/*.csv'],
    skip_leading_rows = 1
  );
}

post_operations {
  DROP TABLE `project.dataset.temp_external_table`;
}

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