google-bigquery - 如何根据 BQ 中的时间戳列创建分区表

Question

在下表中只有两列(ts 和 transaction_id)和 3 行。我刚刚尝试根据时间戳列创建分区表，但收到错误消息。

create table myfirstdataset.partition_table1(ts timestamp, transaction_id int64)partition by ts as
select timestamp '2013-12-31 01:55:02.000 UTC' ts, 10 transaction_id union all
select timestamp '2013-12-31 02:10:02.000 UTC' ts, 5 transaction_id union all
select timestamp '2013-12-31 04:20:02.000 UTC' ts, 6 transaction_id 

Error: PARTITION BY expression must be DATE(<timestamp_column>) or else a DATE column

Answer 1

应该是 PARTITION BY DATE(ts) 而不是 PARTITION BY ts - 参见 CREATE TABLE statement和 partition_expression特别是

create table myfirstdataset.partition_table1(ts timestamp, transaction_id int64)partition by date(ts) as
select timestamp '2013-12-31 01:55:02.000 UTC' ts, 10 transaction_id union all
select timestamp '2013-12-31 02:10:02.000 UTC' ts, 5 transaction_id union all
select timestamp '2013-12-31 04:20:02.000 UTC' ts, 6 transaction_id