Skip to main content

Datometry Documentation

Override CREATE TABLE Statements for Google BigQuery

You can override the default Hyper-Q table creation behavior using the hint bigquery_table_options_override. Use this in situations in which you require precise control over table creation options in Google BigQuery.

Using bigquery_table_options_override, you can reference any of the valid table options in the Google BigQuery CREATE TABLE statement. Hyper-Q adds these options as you specify them in the CREATE TABLE statement, and passes them directly to Google BigQuery.

Important

Some SQL clients remove comments before the statement body. For this reason, Datometry recommends that you include the comment with a hint inside the statement body to ensure that is passed through to Hyper-Q. For more information refer to the example below.

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

Error Reporting in Google BigQuery

Because the table options you specify in the bigquery_table_options_override hint are passed "as is" to Google 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 Google BigQuery errors, and not Hyper-Q errors.

Example 34. Override a Teradata MULTISET Table to Create a CLUSTER BY Table in Google BigQuery

You can override the Teradata CREATE MULTISET TABLE statement and create a table in Google BigQuery that uses the CLUSTER BY clause. In this example, the resulting table uses column_a to organize the contents of the table’s schema.

CREATE MULTISET TABLE table_name (
    column_a int, column_b int
    -- @dtm_bigquery_table_options_override = CLUSTER BY column_a
);

The statement above creates the following CREATE TABLE statement on Google BigQuery.

CREATE TABLE
    `dataset_name`.`table_name` (
        `column_a` INT64,
        `column_b` INT64
    ) CLUSTER BY column_a

To learn more about creating Teradata SET and MULTISET tables on Google BigQuery, see Specify if CREATE TABLE Uses MULTISET or SET for Teradata Emulation.Specify if CREATE TABLE Uses MULTISET or SET for Teradata Emulation

To learn more about clustered tables, see Introduction to clustered tables and Creating and using clustered tables in the Google BigQuery documentation.



Example 35. Override a Teradata MULTISET Table to Create a PARTITION BY Table in Google BigQuery

You can override the Teradata CREATE MULTISET TABLE statement and create a table in Google BigQuery that uses PARTITION BY. In this example, the resulting table uses column_c to partition the table. A partitioned table is a special table that is divided into segments, called partitions, that make it easier to manage and query your data. By dividing a large table into smaller partitions, you can improve query performance, and you can control costs by reducing the number of bytes read by a query.

CREATE MULTISET TABLE table_name (
    column_a int, column_b int, column_c date
-- @dtm_bigquery_table_options_override = PARTITION BY column_c
);

The statement above create the following CREATE TABLE statement on Google BigQuery.

CREATE TABLE
    `dataset_name`.`table_name` (
        `column_a` INT64,
        `column_b` INT64,
         column_c  date
    ) PARTITION BY column_c

To learn more about creating Teradata SET and MULTISET tables on Google BigQuery, see Specify if CREATE TABLE Uses MULTISET or SET for Teradata Emulation.Specify if CREATE TABLE Uses MULTISET or SET for Teradata Emulation

To learn more about table partitioning, see Introduction to partitioned tables and CREATE TABLE statement in the Google BigQuery documentation.