Reputation: 2873
I have some values in a non-partitioned table 'A' shown below
{'column_1':'string_1','timestamp':2018-01-01 00:00:00}
{'column_1':'string_6','timestamp':2021-01-01 00:00:00}
{'column_1':'string_2','timestamp':2018-01-01 00:00:00}
{'column_1':'string_4','timestamp':2020-01-01 00:00:00}
{'column_1':'string_3','timestamp':2019-01-01 00:00:00}
{'column_1':'string_5','timestamp':2021-01-01 00:00:00}
How can I create a new table Table 'B' which is partitioned yearly-wise and new values get automatically inserted into the right year
partitions upon firing an insert command into BigQuery?
Structure of table 'B'
string_1 | 2018-01-01 00:00:00 | 2018
string_2 | 2018-01-01 00:00:00 | 2018
string_3 | 2019-01-01 00:00:00 | 2019
string_4 | 2020-01-01 00:00:00 | 2020
string_5 | 2021-01-01 00:00:00 | 2021
string_6 | 2021-01-01 00:00:00 | 2021
Upvotes: 1
Views: 259
Reputation: 5518
You can use a technique used by BigQuery public dataset table bigquery-public-data:crypto_bitcoin.blocks
which is a DAY partitioned table, and uses the first day of a month as the partition column timestamp_month
.
CREATE TEMP TABLE table_a (
column_1 STRING,
timestamp TIMESTAMP,
) as
select "string_1", TIMESTAMP "2018-01-01 00:00:00" UNION ALL
select "string_2", TIMESTAMP "2019-01-01 00:00:00";
CREATE TEMP TABLE table_b (
column_1 STRING,
timestamp TIMESTAMP,
timestamp_year DATE
) PARTITION BY timestamp_year;
INSERT INTO table_b
SELECT column_1,
timestamp,
DATE(EXTRACT(YEAR FROM DATE(timestamp)), 1, 1) timestamp_year
FROM table_a;
SELECT *
FROM table_b;
Output:
+----------+---------------------+----------------+
| column_1 | timestamp | timestamp_year |
+----------+---------------------+----------------+
| string_1 | 2018-01-01 00:00:00 | 2018-01-01 |
| string_2 | 2019-01-01 00:00:00 | 2019-01-01 |
+----------+---------------------+----------------+
Upvotes: 1