Skip to main content

Datometry Documentation

Override Google BigQuery Partitions

You can override the default Hyper-Q table creation behavior using the hint bigquery_partitions_override. This hint allows you to override BigQuery partitions and serialize them.

Using bigquery_partitions_override, you can reference any of the valid partitions in the BigQuery CREATE TABLE statement. Hyper-Q adds the partitions as you specify them in the CREATE TABLE statement, and passes them directly to BigQuery. During table creation Hyper-Q lookups for the table name and schema. If there is already a partition specified for the table, it will use that value when processing the query hint.

To learn about the CREATE TABLE table options that you can pass using the bigquery_table_options_override hint, see Creating partitioned tables in the Google documentation.

Error Reporting in Google BigQuery

Because the table options you specify in the bigquery_partitions_override hint are passed "as is" to BigQuery, it returns errors as if you were using a native CREATE TABLE statement. For this reason, you must respond to these error messages as though they are native BigQuery errors, and not Hyper-Q errors.

Example 38. Override Partitions

In this example, BigQuery creates a table using _PARTITIONDATE.

CREATE TABLE table_name
  -- @dtm_bigquery_partitions_override=_PARTITIONDATE
   (column1 int, column2 int)
   UNIQUE PRIMARY INDEX(col2);

BigQuery translates the above SQL statement as shown in the following example.

CREATE TABLE
        `project_name`.`dataset_name`.`table_name` (
            `column1` INT64 ,
            `column2` INT64 
        ) PARTITION BY _PARTITIONDATE 

;