Search code examples
google-bigquerydata-partitioning

Partitioning BigQuery table based on nested column

I am trying to partition a BigQuery table based on a timestamp but the column I want to use for partitioning is a nested column and has a parent record. For instance: transaction.timestamp.

I would like to pass the column name as String to a java method. How can I define this column name as String in java when I pass it as a parameter?

I have previously tried partitioning with non-nested columns and it worked fine. Following piece of code does not recognize the column name and results in error:

String columnName = "transaction.timestamp";

I would appreciate your help in figuring this problem out.

Solution

  • For partitioning and clustering: You will need to unnest the column and have it as a first level column.

    From the docs:

    The partitioning column must be a top-level field. You cannot use a leaf field from a RECORD (STRUCT) as the partitioning column.

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