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.
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 ;