How to convert a non-partitioned BigQuery table to partitioned?

Question

In June the BQ team announced support for date-partitioned tables. But the guide is missing how to migrate old non-partitioned tables into the new style.

I am looking for a way to update several or if not all tables to the new style.

Also outside of DAY type partitioned what other options are available? Does the BQ UI show this, as I wasn't able to create such a new partitioned table from the BQ Web UI.

Answer 1

What works for me is the following set of queries applied directly in the big query (big query create new query).

CREATE TABLE (new?)dataset.new_table PARTITION BY DATE(date_column) AS SELECT * FROM dataset.table_to_copy;

Then as the next step I drop the table:

DROP TABLE dataset.table_to_copy;

I got this solution from https://fivetran.com/docs/warehouses/bigquery/partition-table using only step 2

Answer 2

from Pavan’s answer: Please note that this approach will charge you the scan cost of the source table for the query as many times as you query it.

---

from Pentium10 comments: So suppose I have several years of data, I need to prepare different query for each day and run all of it, and suppose I have 1000 days in history, I need to pay 1000 times the full query price from the source table?

---

As we can see - the main problem here is on having full scan for each and every day. The rest is less of a problem and can be easily scripted out in any client of the choice

So, below is to - How to partition table while avoid full table scan for each and every day?

Below step-by-step shows the approach

It is generic enough to extend/apply to anyone real use-case - meantime I am using bigquery-public-data.noaa_gsod.gsod2017 and I am limiting "exercise" to just 10 days to keep it readable

Step 1 – Create Pivot table
In this step we
a) compress each row’s content into record/array
and
b) put them all into respective ”daily” column

#standardSQL
SELECT
  ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170101' THEN r END) AS day20170101,
  ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170102' THEN r END) AS day20170102,
  ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170103' THEN r END) AS day20170103,
  ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170104' THEN r END) AS day20170104,
  ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170105' THEN r END) AS day20170105,
  ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170106' THEN r END) AS day20170106,
  ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170107' THEN r END) AS day20170107,
  ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170108' THEN r END) AS day20170108,
  ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170109' THEN r END) AS day20170109,
  ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170110' THEN r END) AS day20170110
FROM (
  SELECT d, r, ROW_NUMBER() OVER(PARTITION BY d) AS line
  FROM (
    SELECT 
      stn, CONCAT('day', year, mo, da) AS d, ARRAY_AGG(t) AS r
    FROM `bigquery-public-data.noaa_gsod.gsod2017` AS t 
    GROUP BY stn, d
  ) 
)
GROUP BY line  

Run above query in Web UI with pivot_table (or whatever name is preferred) as a destination

As we can see - here we will get table with 10 columns – one column for one day and schema of each column is a copy of schema of original table:

Step 2 – Processing partitions one-by-one ONLY scanning respective column (no full table scan) – inserting into respective partition

#standardSQL
SELECT r.*
FROM pivot_table, UNNEST(day20170101) AS r

Run above query from Web UI with destination table named mytable$20160101

You can run same for next day

#standardSQL
SELECT r.*
FROM pivot_table, UNNEST(day20170102) AS r

Now you should have destination table as mytable$20160102 and so on

You should be able to automate/script this step with any client of your choice

There are many variations of how you can use above approach - it is up to your creativity

Note: BigQuery allows up to 10000 columns in table, so 365 columns for respective days of one year is definitely not a problem here :o) Unless there is a limitation on how far back you can go with new partitions – I heard (but didn’t have chance to check yet) there is now no more than 90 days back

Update

Please note: Above version has a little extra logic of packing all aggregated cells into as least final number of rows as possible.

ROW_NUMBER() OVER(PARTITION BY d) AS line
and then
GROUP BY line
along with
ARRAY_CONCAT_AGG(…)
does this

This works well when row size in your original table is not that big so final combined row size still will be within rows size limit that BigQuery has (which I believe is 10 MB as of now)

If your source table already has row size close to that limit – use below adjusted version

In this version – grouping is removed such that each row has only value for one column

#standardSQL
SELECT
    CASE WHEN d = 'day20170101' THEN r END AS day20170101,
    CASE WHEN d = 'day20170102' THEN r END AS day20170102,
    CASE WHEN d = 'day20170103' THEN r END AS day20170103,
    CASE WHEN d = 'day20170104' THEN r END AS day20170104,
    CASE WHEN d = 'day20170105' THEN r END AS day20170105,
    CASE WHEN d = 'day20170106' THEN r END AS day20170106,
    CASE WHEN d = 'day20170107' THEN r END AS day20170107,
    CASE WHEN d = 'day20170108' THEN r END AS day20170108,
    CASE WHEN d = 'day20170109' THEN r END AS day20170109,
    CASE WHEN d = 'day20170110' THEN r END AS day20170110
FROM (
    SELECT 
        stn, CONCAT('day', year, mo, da) AS d, ARRAY_AGG(t) AS r
    FROM `bigquery-public-data.noaa_gsod.gsod2017` AS t 
    GROUP BY stn, d
)
WHERE d BETWEEN 'day20170101' AND 'day20170110'

As you can see now - pivot table (sparce_pivot_table) is sparse enough (same 21.5 MB but now 114,089 rows vs. 11,584 rows in pivot_table) so it has average row size of 190B vs 1.9KB in initial version. Which is obviously about 10 times less as per number of columns in the example.
So before using this approach some math needs to be done to project/estimate what and how can be done!

Still: each cell in pivot table is sort of JSON representation of whole row in original table. It is such as it holds not just values as it was for rows in original table but also has a schema in it

As such it is quite verbose - thus the size of cell can be multiple times bigger than original size [which limits the usage of this approach ... unless you get even more creative :o) ... which is still plenty of areas here to apply :o) ]

Answer 3

As of today, you can now create a partitioned table from a non-partitioned table by querying it and specifying the partition column. You'll pay for one full table scan on the original (non-partitioned) table. Note: this is currently in beta.

https://cloud.google.com/bigquery/docs/creating-column-partitions#creating_a_partitioned_table_from_a_query_result

To create a partitioned table from a query result, write the results to a new destination table. You can create a partitioned table by querying either a partitioned table or a non-partitioned table. You cannot change an existing standard table to a partitioned table using query results.

Answer 4

Until the new feature is rolled out in BigQuery, there is another (much cheaper) way to partition the table(s) by using Cloud Dataflow. We used this approach instead of running hundreds of SELECT * statements, which would have cost us thousands of dollars.

1. Create the partitioned table in BigQuery using the normal partition command
2. Create a Dataflow pipeline and use a BigQuery.IO.Read sink to read the table
3. Use a Partition transform to partition each row
4. Using a max of 200 shards/sinks at a time (any more than that and you hit API limits), create a BigQuery.IO.Write sink for each day/shard that will write to the corresponding partition using the partition decorator syntax - "$YYYYMMDD"
5. Repeat N times until all data is processed.

Here's an example on Github to get you started.

You still have to pay for the Dataflow pipeline(s), but it's a fraction of the cost of using multiple SELECT * in BigQuery.

Answer 5

If you have date sharded tables today, you can use this approach:

https://cloud.google.com/bigquery/docs/creating-partitioned-tables#converting_dated_tables_into_a_partitioned_table

If you have a single non-partitioned table to be converted to partitioned table, you can try the approach of running a SELECT * query with allow large results and using the table's partition as the destination (similar to how you'd restate data for a partition):

https://cloud.google.com/bigquery/docs/creating-partitioned-tables#restating_data_in_a_partition

Please note that this approach will charge you the scan cost of the source table for the query as many times as you query it.

We are working on something to make this scenario significantly better in the next few months.

Answer 6

CREATE TABLE `dataset.new_table`
PARTITION BY DATE(date_column) 
AS SELECT * FROM `dataset.old_table`;

drop table `dataset.old_table`;

ALTER TABLE `dataset.new_table`
RENAME TO old_table;