如何从BQ中的结果集创建分区表？

Question

我只是从结果集创建了分区表，以确保正确理解。从黑客新闻中只有三个栏目，即time_ts、标题和分数。从2013-2015年开始，创建了1015个分区。从2007-2015年开始，共有3158个分区(超过限制)。

Query-1
Creating partitioned table based on date column.(2013-2015)
create table mydataset.y2013_y2015(title_date date, title string, score int64) partition by title_date as 
select extract(date from time_ts)extracted_date,title,score from `bigquery-public-data.hacker_news.stories`  where extract(date from time_ts) between '2013-01-01' and '2015-12-31'

Query-2
Creating partitioned table based on timestamp column.(2013-2015)
create table mydataset.y2013_y2015_ts(title_ts timestamp, title string, score int64) partition by date(title_ts) as 
select time_ts,title,score from `bigquery-public-data.hacker_news.stories`  where extract(date from time_ts) between '2013-01-01' and '2015-12-31'

Query-3 
Creating partitioned table based on timestamp column.(2007-2015)
create table mydataset.y2007_y2015(ts timestamp, title string, score int64) partition by date(ts) as 
select time_ts,title,score from `bigquery-public-data.hacker_news.stories`  where extract(date from time_ts) between '2007-01-01' and '2015-12-31'
Error: Too many partitions produced by query, allowed 2000, query produces at least 3158 partitions

查询-1和查询-2从结果集创建分区表。

检查-创建了多少分区。

select COUNT(*)No_of_partitions from [mydataset.y2013_y2015$__PARTITIONS_SUMMARY__] 
select COUNT(*)No_of_partitions from  [mydataset.y2013_y2015_ts$__PARTITIONS_SUMMARY__] 

我说的对吗？

Answer 1

是的，.You是对的。您还可以使用此代码(在标准SQL中)。

#standardSQL
select count(distinct _PARTITIONDATE) from `mydataset.y2013_y2015`