CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquerybigquery-udf
Mani Shankar.S
Mani Shankar.S

Reputation: 39

GCP : Is there a way create dynamic partition field in table definition of Bigquery

I am trying to check the feasibility of creating dynamic partition field in table definition.

create table <dataset>.<table> (col1 datatype, col2 datatype) 
partition by (cast(col1 as int))

When I tried, I got an error

PARTITION BY expression must be _PARTITIONDATE, DATE(_PARTITIONTIME), DATE(<timestamp_column>), DATE(<datetime_column>), DATETIME_TRUNC(<datetime_column>, DAY/HOUR/MONTH/YEAR), a DATE column, TIMESTAMP_TRUNC(<timestamp_column>, DAY/HOUR/MONTH/YEAR), DATE_TRUNC(<date_column>, MONTH/YEAR), or RANGE_BUCKET(<int64_column>, GENERATE_ARRAY(<int64_value>, <int64_value>[, <int64_value>]))

Can someone let me know, is it possible to have something like this? Or any other way of doing it dynamically, instead of directly referring the column that present in table definition

Upvotes: 0

Views: 209

Answers (1)

kiran mathew
kiran mathew

Reputation: 2373

As @guillaume blaquiere mentioned in comments:

You can't use expressions when you define the column to partition. In addition, if you use an integer, you have to use range_bucket partition mode.

Different ways to partition tables are:

  1. Integer range partitioning.
  2. Time-unit column partitioning.
  3. Ingestion time partitioning.
  4. Select daily, hourly, monthly, or yearly partitioning.

The BigQuery partition has some limitations also .For more information you can refer to these link1,link2 and link3.

Posting the answer as community wiki for the benefit of the community that might encounter this use case in the future.

Feel free to edit this answer for additional information.

Upvotes: 0

Related Questions