如何在BigQuery中按日分区建表并通过SDK插入数据？

1. 这种表的名称及创建方法 #

你提到的Google Analytics自动生成的按年月日拆分、支持_TABLE_SUFFIX查询的表，在BigQuery中叫做日期分区表（Date-partitioned table）。这类表会按日期自动拆分存储，查询时通过_TABLE_SUFFIX可快速筛选特定日期分区，大幅提升查询效率。

手动创建（控制台操作） #

1. 进入BigQuery控制台，选择目标数据集，点击「创建表」
2. 选择数据源（可选择「空表」或导入已有数据）
3. 在「分区和集群」配置区域：
   • 选择「分区类型」为「按日期分区」
   • 若基于表中现有日期字段（如event_date）分区，选择「字段」并指定字段名（需为DATE或TIMESTAMP类型）；若无需现有字段，可选择伪列_PARTITIONDATE
   • 可选设置分区过期时间、表描述等参数，完成后点击「创建表」

代码创建（SQL或Python SDK） #

SQL方式

通过CREATE TABLE语句直接定义分区表：

-- 基于现有日期字段分区
CREATE OR REPLACE PROJECT_ID.DATASET_ID.your_events_table
(
  event_id STRING,
  event_name STRING,
  event_params JSON,
  event_date DATE
)
PARTITION BY DATE(event_date)
OPTIONS(
  partition_expiration_days = 365, -- 可选，设置分区自动过期时间
  description = "按event_date分区的自定义事件表"
);

-- 基于伪列_PARTITIONDATE分区
CREATE OR REPLACE PROJECT_ID.DATASET_ID.your_events_table
(
  event_id STRING,
  event_name STRING,
  event_params JSON
)
PARTITION BY _PARTITIONDATE
OPTIONS(
  partition_expiration_days = 365
);

Python SDK方式

通过bigquery.Table对象配置分区规则：

from google.cloud import bigquery

client = bigquery.Client()
table_id = "PROJECT_ID.DATASET_ID.your_events_table"

# 定义表结构
schema = [
    bigquery.SchemaField("event_id", "STRING"),
    bigquery.SchemaField("event_name", "STRING"),
    bigquery.SchemaField("event_params", "JSON"),
    bigquery.SchemaField("event_date", "DATE")
]

# 配置分区规则（基于event_date字段）
table = bigquery.Table(table_id, schema=schema)
table.time_partitioning = bigquery.TimePartitioning(
    type_=bigquery.TimePartitioningType.DAY,
    field="event_date",  # 指定分区字段
    expiration_ms=365 * 24 * 60 * 60 * 1000  # 可选，分区过期时间（毫秒）
)

# 创建表
table = client.create_table(table)
print(f"已创建日期分区表: {table.full_table_id}")

2. Python SDK插入对应日期的分区 #

根据分区表的类型，有两种常用写入方式：

方式1：基于分区字段自动路由（适用于基于现有日期字段的分区表） #

插入数据时只需指定对应日期的字段值，BigQuery会自动将数据写入匹配的分区：

from google.cloud import bigquery
from datetime import date

client = bigquery.Client()
table_id = "PROJECT_ID.DATASET_ID.your_events_table"

# 构造目标日期的数据集
target_date = date(2024, 5, 20)
data = [
    {
        "event_id": "evt_001",
        "event_name": "page_view",
        "event_params": {"page": "/home"},
        "event_date": target_date.isoformat()
    },
    {
        "event_id": "evt_002",
        "event_name": "purchase",
        "event_params": {"amount": 99.9},
        "event_date": target_date.isoformat()
    }
]

# 批量插入数据
errors = client.insert_rows_json(table_id, data)
if not errors:
    print(f"数据已成功写入{target_date}分区")
else:
    print(f"插入错误: {errors}")

方式2：直接指定分区表后缀（适用于所有日期分区表） #

通过在表名后追加$YYYYMMDD后缀，直接写入指定日期的分区：

from google.cloud import bigquery

client = bigquery.Client()
# 构造目标分区的表ID（格式：表名$YYYYMMDD）
target_partition_table_id = "PROJECT_ID.DATASET_ID.your_events_table$20240520"

data = [
    {
        "event_id": "evt_003",
        "event_name": "login",
        "event_params": {"user_id": "user_123"}
    }
]

# 写入指定分区
errors = client.insert_rows_json(target_partition_table_id, data)
if not errors:
    print(f"数据已成功写入2024-05-20分区")
else:
    print(f"插入错误: {errors}")

注意：批量写入时，使用load_table_from_dataframe或load_table_from_file等加载方式，比逐条insert_rows的效率更高，适合大数据量场景。

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