I would like to save the result of a bigquery query into a partitioned table, is that possible? I didn't see that as an option when I tried to save.
2 Answers
With #standardSQL and DML:
INSERT INTO `project.dataset.table` (field1, field2, field3)
SELECT *
FROM `anothertable`
WHERE 'this is my query'
If the table is already partitioned by one the fields, then everything will work as expected.
If it's going to a new table:
CREATE TABLE `project.dataset.table`
PARTITION BY DATE(timestamp)
AS
SELECT field1, field2, field3, timestamp
FROM `yourtable`
WHERE 'yourquery'
Inserting data into a partitioned table is the same as inserting data into a non-partitioned table. You just set destination table that is partitioned the way you need
Note, you cannot create partitioned table from query result if you are using Web UI
But you can do so using CLI for example:
bq --location=[LOCATION] query --destination_table [PROJECT_ID]:[DATASET].[TABLE] --time_partitioning_field [COLUMN] --use_legacy_sql=false '[QUERY]'
Related