BigQuery基于现有表创建_PARTITIONTIME分区表报错解决

报错根因 #

使用CREATE TABLE AS SELECT(CTAS) 语法建表时，BigQuery 解析PARTITION BY子句的逻辑是从SELECT语句返回的字段列表里匹配分区键。你写的DATE(_PARTITIONTIME)里的_PARTITIONTIME是摄入时间分区表创建完成后才会生成的系统伪列，既不在原表字段里，也不在你SELECT返回的结果里，自然会报「Unrecognized name: _PARTITIONTIME」错误。

正确实现方式 #

根据实际分区需求选对应写法即可： #

场景1：创建标准摄入时间分区表（写入数据时BigQuery自动按写入时间生成_PARTITIONTIME伪列作为分区依据） #

不需要在SELECT里构造_PARTITIONTIME字段，直接使用_PARTITIONDATE作为分区键即可（这是BigQuery专门用于CTAS场景下声明天级摄入时间分区的标识），同时可以直接在SELECT里拼接你要新增的列：

CREATE OR REPLACE TABLE `mydataset.mytable_new`
-- 声明为天粒度摄入时间分区，建表完成后自动生成_PARTITIONTIME、_PARTITIONDATE伪列
PARTITION BY _PARTITIONDATE
AS SELECT 
  *,
  -- 在这里写你要新增的列逻辑，固定值、关联计算结果都可以
  "新增列的固定值" AS new_col_name
FROM `mydataset.mytable`
-- 只复制表结构不需要数据就保留LIMIT 0，要同步原表数据就删掉这行
LIMIT 0

建表完成后，就可以像普通摄入时间分区表一样用_PARTITIONTIME做分区过滤。 #

场景2：实际需求为基于表内时间字段分区（误将字段分区当成_PARTITIONTIME分区） #

如果你要的是按原表中已有的某个时间/日期字段分区（比如交易时间、创建时间），不需要用到系统伪列，直接在PARTITION BY里引用SELECT中返回的对应字段即可：

CREATE OR REPLACE TABLE `mydataset.mytable_new`
-- 引用SELECT里实际存在的时间字段，比如原表的create_time是TIMESTAMP类型
PARTITION BY DATE(create_time)
AS SELECT 
  *,
  "新增列内容" AS new_col_name
FROM `mydataset.mytable`
LIMIT 0

注意：这种方式建出来的是普通字段分区表，不会生成系统_PARTITIONTIME伪列，分区过滤必须用你指定的时间字段。

避坑提示 #

不要为了消除报错强行在SELECT里写一个同名字段命名为_PARTITIONTIME当分区键，这样建出来的表是普通字段分区表，不会有摄入时间分区的自动分区赋值、分区过期等原生特性，后续使用很容易出问题。

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