BigQuery定时查询如何配置小时级表分区，避免每次运行生成新表

你之前通过表名加{run_time|"%Y%m%d%H"}后缀的方式属于生成多张时间分片独立表，并非单表内的分区逻辑，和你预期的小时级分区不是同一种实现，正确配置方式如下：

步骤1：创建支持小时级分区的目标表 #

方式1：通过SQL创建 #

按业务自带时间字段分区（推荐，匹配业务逻辑）

CREATE TABLE `你的项目ID.你的数据集名.mytable`
(
  -- 按你的业务需求定义字段，示例如下
  id INT64,
  event_time TIMESTAMP,
  content STRING
)
-- 核心配置：按业务时间字段的小时粒度分区
PARTITION BY DATE_TRUNC(event_time, HOUR)
OPTIONS(
  description = "小时级分区业务表",
  partition_expiration_days = 90 -- 可选，配置分区自动过期时间，不需要可删除
);

按BigQuery内置摄入时间分区（无业务时间字段时使用）

CREATE TABLE `你的项目ID.你的数据集名.mytable`
(
  -- 按你的业务需求定义字段
  id INT64,
  content STRING
)
-- 用BigQuery自动生成的写入时间作为分区依据
PARTITION BY DATE_TRUNC(_PARTITIONTIME, HOUR)
OPTIONS(
  description = "小时级分区业务表"
);

方式2：通过控制台可视化创建 #

• 进入BigQuery控制台，选中对应数据集后点击「创建表」
• 填写基础表信息、字段定义后，找到「分区设置」板块
• 分区类型按需选择「按时间列分区」/「按摄入时间分区」
• 分区粒度下拉选择「小时」，按需设置分区过期时间后完成创建

步骤2：配置每小时定时查询的写入逻辑 #

目标表直接填写固定路径你的项目ID.你的数据集名.mytable，不需要加任何时间后缀，根据写入需求选择对应逻辑：

场景1：仅追加当次小时数据，不修改历史 #

INSERT INTO `你的项目ID.你的数据集名.mytable`
SELECT 
  -- 你的业务查询逻辑
  id,
  event_time,
  content
FROM 你的数据源表
-- 过滤出本次要同步的小时区间数据，示例为同步运行时间的上一小时数据
WHERE event_time >= TIMESTAMP_SUB(@run_time, INTERVAL 1 HOUR)
  AND event_time < @run_time

场景2：每次运行需要覆盖当次小时的历史数据 #

-- 定义本次要覆盖的目标小时，示例为运行时间的上一小时
DECLARE target_hour TIMESTAMP DEFAULT TIMESTAMP_SUB(@run_time, INTERVAL 1 HOUR);

-- 仅删除目标小时的旧分区数据，不会影响其他分区
DELETE FROM `你的项目ID.你的数据集名.mytable`
WHERE DATE_TRUNC(event_time, HOUR) = DATE_TRUNC(target_hour, HOUR);

-- 插入新的小时数据
INSERT INTO `你的项目ID.你的数据集名.mytable`
SELECT 
  -- 你的业务查询逻辑
  id,
  event_time,
  content
FROM 你的数据源表
WHERE DATE_TRUNC(event_time, HOUR) = DATE_TRUNC(target_hour, HOUR);

验证配置是否生效 #

写入完成后执行以下SQL可查看分区列表：

SELECT
  partition_id,
  total_rows,
  size_bytes
FROM
  `你的项目ID.你的数据集名.mytable$__PARTITIONS_SUMMARY__`

正常返回结果中会看到每小时对应一条分区记录，partition_id格式为YYYYMMDDHH。

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