CODEWITHSUNDEEP
google-bigquery
Georges Lorré
Georges Lorré

Reputation: 443

Creating partitioned table from querying partitioned table

I have an existing date partitioned table and I want to create a new date partitioned table with only one column from the original table while keeping the original partitioning.

I have tried: Creating an empty partitioned table and copying the results in from a query but then the partitioning is missing.

The following Create statement should work if I also include the partitioned date as a column in my new table which I don't want. Is there a way to use the part_date column as a partition decorator when loading in the data from a query result ?

    CREATE TABLE
      cat_dataset.cats_names(cat_name string)
    PARTITION BY
      part_date AS
    SELECT
      cat_name,
      _PARTITIONDATE AS part_date
    FROM
      `myproject.cat_dataset.cats`

I want to avoid looping over all the dates and writing the data off that date to the new table. Is there a way to use the part_date column as a partition decorator when loading in the data from a query result ?

Upvotes: 1

Views: 1065

Answers (1)

Yun Zhang
Yun Zhang

Reputation: 5503

INSERT INTO allows you to specify _PARTITIONTIME as a column, see link. Code below should work:

CREATE TABLE cat_dataset.cats_names(cat_name string)
PARTITION BY DATE(_PARTITIONTIME);

INSERT INTO cat_dataset.cats_names (_PARTITIONTIME, cat_name) 
SELECT _PARTITIONTIME, cat_name
FROM `myproject.cat_dataset.cats`

Upvotes: 2

Related Questions