BigQuery table partitioning difference between legacy and standard sql

0
votes

When a BigQuery table is partitioned using the --time_partitioning_type=DAY command line parameter ...

  1. Does it create a single physical table or multiple physical tables?Does it differ when using Standard vs Legacy SQL
  2. Is the pseudo column _PARTITIONTIME available in both legacy and standard SQL ?
  3. Since Standard SQL does not support Partition Decorator , how can one overcome the limitation when using standard SQL ?
2
google-bigquery

2 Answers

2
votes
  1. Does it create a single physical table or multiple physical tables?Does it differ when using Standard vs Legacy SQL

BigQuerys internal system is distributed, so indeed it's multiple physical copies of the same data inside Google's infrastructure. Since it's a managed service, this is transparent for us.

  1. Is the pseudo column _PARTITIONTIME available in both legacy and standard SQL ?

Yes, it's available. More about where to put this pseduo column in the examples.

  1. Since Standard SQL does not support Partition Decorator , how can one overcome the limitation when using standard SQL ?

Decorators in pure SQL can be mimic with the _PARTITIONTIME pseudo column. Think of the decorators as a complementary invention for the API side of this service. When you need to delete a day, and to reload that day into your table, you couldn't do that easily with SQL, but with the API and decorators you are able to run bq rm 'mydataset.table$20160301'. Partition decorators enable you to load data into a specific partition: [TABLE_NAME]$20160301

0
votes

For 3. - decorators are now implemented as in:

SELECT *
FROM t
  FOR SYSTEM TIME AS OF '2017-01-01 10:00:00-07:00';