如何向Google BigQuery高效插入10亿条按ID排序的时序数据

方案1：前置分桶归并（最低排序成本） #

• 提前将10亿个ID的全局范围划分为N个连续的区间桶，比如每个桶覆盖100万条ID的区间，总桶数控制在1000个以内即可
• 多进程拉取数据时，每拿到一批1000条的有序返回结果，直接按ID所属区间写入对应桶的缓冲文件（可存本地或GCS，推荐用Parquet格式），同个桶内直接追加写入即可
• 所有请求完成后，仅需要对每个桶内的数据单独做排序，单桶数据量最多百万级别，排序成本极低
• 最后将所有桶按ID区间从小到大的顺序，依次批量加载到BigQuery表中，最终表天然实现全局按ID有序

方案2：利用BigQuery原生特性（最少开发量） #

• 建表时直接指定按ID列创建聚簇表，SQL参考：

CREATE TABLE your_dataset.your_table (
  ID INT64,
  -- 其余字段定义
)
CLUSTER BY ID;

• 拉取到的数据直接批量写入该表，不需要关注写入顺序，BigQuery后台会自动维护聚簇排序逻辑，后续查询时会自动按有序结构处理，完全不需要手动做全量排序
• 如果要求物理存储层面全局严格有序，等所有数据写入完成后，执行一次排序重写即可：

CREATE OR REPLACE TABLE your_dataset.your_table
CLUSTER BY ID
AS SELECT * FROM your_dataset.your_table ORDER BY ID;

BigQuery的分布式排序引擎处理10亿行数据的耗时远低于自建服务处理的耗时，成本也更低

通用优化建议 #

• 放弃预生成空ID列再更新的方案：BigQuery为列存架构，更新操作的开销是插入操作的数倍到数十倍，完全没有性价比
• 写入BigQuery时避免单行插入，每攒到至少10万条数据后，转成Parquet/ORC格式再批量加载，写入效率会提升10倍以上，同时减少费用消耗
• 若单次返回的1000条数据是按ID有序的，写入缓冲前可直接和同批次的其他小批量做归并，进一步降低后续排序成本

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