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.)?
Add a comment |
3 Answers
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")] );' 
1 Comment
AfroThundr
Welcome to StackOverflow. Instead of just pasting a code block, you should give an explanation of what the code does and how it's relevant.
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
1 Comment
Ran Avnimelech
Note: you may need to update your cloud sdk shell for it to support the time_partitioning_type parameter
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.
