如何用Python创建带每日Ingestion Time分区及聚类字段的BigQuery表

要实现每日Ingestion Time分区和指定聚类字段，只需在创建bigquery.Table对象时，设置对应的time_partitioning和clustering_fields属性即可。以下是修改后的完整创建表代码：

from google.cloud import bigquery

bq_client = bigquery.Client()
table_name = "my_test_table"

dataset = bq_client.dataset("MY_TEST_DATASET")
table_ref = dataset.table(table_name)
table = bigquery.Table(table_ref)

# 设置每日Ingestion Time分区
table.time_partitioning = bigquery.TimePartitioning(
    type_=bigquery.TimePartitioningType.DAY,
    # 留空field参数即使用Ingestion Time作为分区依据
)

# 设置聚类字段
table.clustering_fields = ["business_id", "software_house", "product_id"]

# 创建表
table = bq_client.create_table(table)

数据加载说明 #

你原本的Pandas DataFrame上传代码无需额外修改，只要DataFrame中包含指定的聚类字段（business_id、software_house、product_id），BigQuery会自动处理聚类逻辑；Ingestion Time分区则由系统根据数据写入时间自动分配分区：

# --- Define BQ options ---
job_config = bigquery.LoadJobConfig()
job_config.write_disposition = "WRITE_APPEND"
job_config.source_format = bigquery.SourceFormat.CSV

# --- Load data ---
job = bq_client.load_table_from_dataframe(
        df, f"MY_TEST_DATASET.{table_name}", job_config=job_config
    )
# 可选：等待加载任务完成
job.result()

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