CODEWITHSUNDEEP
google-bigquery
Ran Avnimelech
Ran Avnimelech

Reputation: 233

How do I create a partitioned table in bigquery

Google recently announced partitioned tables in BigQuery which have many advantages. However, I found no documentation of how to create such tables. How do I create such a table, either in the UI, the CLI, or the API (java etc.)?

Upvotes: 9

Views: 36119

Answers (3)

Clément Prévost
Clément Prévost

Reputation: 8600

I did have a hard time finding the documentation so I'm adding it here:

{CREATE TABLE | CREATE TABLE IF NOT EXISTS | CREATE OR REPLACE TABLE}
[[project_name.]dataset_name.]table_name
[(
  column_name column_schema[, ...]
)]
[PARTITION BY partition_expression]
[CLUSTER BY clustering_column_list]
[OPTIONS(table_option_list)]
[AS query_statement]

Note that you can cluster a table from a view select, which is pretty neat.

Upvotes: 2

Damon Cool
Damon Cool

Reputation: 131

You can run this code in the UI / CLI to create a table with partitioning.

bq query --use_legacy_sql=false '
CREATE TABLE dw_prod.example_table (
    company STRING, 
    value FLOAT64,
    ds DATE)
PARTITION BY ds
OPTIONS(
  expiration_timestamp=TIMESTAMP "2028-01-01 00:00:00 UTC",
  description="Example table create in BQ CLI",
  labels=[("example","summary")]
);'

Upvotes: 7

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Take a look at timePartitioning property of table resources.
You can experiment with it I think :o)
More about it in Partitioned Tables and Creating and Updating Date-Partitioned Tables

Upvotes: 6

Related Questions