Hyper-Q SQL Hints for Google BigQuery
Hyper-Q provides a query override feature that lets you register a custom query that will execute on the database when a client submits a specific input query. You use query override when the required behavior or performance cannot be achieved on the target database using standard optimization techniques, or by restructuring the original client query.
This guide contains the following sections:
About Hyper-Q SQL Hints Learn about Hyper-Q hints and how to apply them with different levels of specificity.
Use Hyper-Q Hints for an Entire Session Session hints let you specify hints at the session level, as opposed to passing them for each individual statement. Thus, all queries sent to the database using a given session will use the hints you provide. The hint will affect only those queries sent from the time the session hint is set. Any earlier queries in the same session are unaffected.
Cast Strings to Integer or Numeric Values You can cast a string to an integer or numeric value that is compatible with Teradata conversion rules.
Specify How Hyper-Q Performs Rounding When Computing DECIMAL Data Types The division rules for Azure Synapse differ from Teradata when calculating the last decimal digit. This issue becomes apparent when using the Teradata utilities RoundHalfwayMagUp = TRUE and RoundNumberAsDec = FALSE. You can specify how Hyper-Q performs rounding when using these utilities. By enabling the hint round_halfway_mag_up, Hyper-Q will return the same division values in the last decimal digit as Teradata.
Accept Timestamp Values Using Any Type of Character Separators When CASTing text to a timestamp, you can allow Hyper-Q to accept timestamp values with any character as separators between timestamp parts.
Apply Strict Checks of Teradata Timestamps You can perform a strict check of the Teradata default timestamp format using the Hyper-Q SQL hint text_to_timestamp_advanced_separators_strict_format_checking.
Translate Teradata TRYCAST to TIMESTAMP Function You can translate the Teradata TRYCAST to TIMESTAMP function using the timestamp_trycast hint. TRYCAST takes a string and casts it to the specified data type—in this case TIMESTAMP.
Disable Transaction Commands You can disable user transactions specified in one of the following ways:
Explicit commands for starting a transaction, such as BEGIN TRANSACTION (BT), BEGIN and other synonyms for the same SQL commands allowed by Teradata.
Implicit transactions formed by multi-statement requests.
Enable Support for Leap Seconds Teradata supports leap seconds, whereas many other databases do not. The leap_second_literals hint lets you enable the handling of leap seconds, in which there are greater than 59 seconds for the TIME, TIME WITH TIME ZONE, TIMESTAMP, and TIMESTAMP WITH TIME ZONE data types.
Specify a Custom Error Code for Teradata You can specify a custom Teradata error code to be returned by Hyper-Q.
Enable or Disable Index Not Supported Error Messages On databases that do not support indexes, you can specify if Hyper-Q should return an error when attempting to create or access an index, or ignore the error.
Enable or Disable Statistics Collection on Temp Tables You can specify whether or not Hyper-Q collects statistics on temporary tables created during nested case emulation. This allows you to determine when statistics are collected, particularly if they are not needed and may impact performance.
Enable or Disable Index Not Supported Error Messages On databases that do not support statistics you can specify if Hyper-Q should return an error when attempting to collect or drop statistics, or ignore the error, using the allow_stats_commands configuration parameter.
Check for Duplicate UPDATE Statements You can check for duplicate records in UPDATE statements using the update_duplicate_check hint.
Specify if CREATE TABLE Uses SET or MULTISET When using CREATE TABLE to create a new table, if the table type is not specified explicitly you can use default_table_type to create either a Teradata MULTISET or SET table type.
Override Teradata SET and UPI Table Emulation You can override Teradata SET and UPI table emulation using the Hyper-Q hint override_table_uniqueness.
Override CREATE TABLE Statements for Google BigQuery You can override the default table creation behavior using the bigquery_table_options_override hint. Use this in situations in which you require precise control over table creation options in BigQuery.
Specify How Many Levels to Nest CASE Statements Hyper-Q transforms CASE statements that are nested beyond the defined levels into temporary tables. However, some query optimizers fail to process CASE statements with too deep of a nesting. You can specify how many level of nesting to use with CASE statements using max_nested_case_levels
Override Google BigQuery Table Clustering By default, tables translated using Hyper-Q and qShift use the Teradata primary index columns as clustering columns in BigQuery to provide a tuning baseline. Tables may require additional tuning, such as adding partitions or customizing clustering, to achieve optimal query performance. You can override the creation of clustering columns using the Hyper-Q hint bigquery_clustering_cols_override.
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.
Override a SQL Query With a Custom Statement Using the Hyper-Q hint override_with_predefined_statement, you can override a query with a custom query that will be executed on the cloud data warehouse when the SQL client submits a specific input query. You can use this to replace a query from the on-premise database with one that performs better on the cloud data warehouse.
Specify a Custom Error Code for Teradata The SQL hint default_teradata_error_code lets you specify a Teradata error code that you choose to be returned by Hyper-Q.
Enable Generation 2 Bulk Load Error Handling The second generation of Hyper-Q bulk load error handling, generation 2 improves performance and provides more information about errors than the previous version of bulk load error handling, advanced adaptive error handling (AAEH). You enable generation 2 bulk load error handling using the Hyper-Q hint bulk_load_err_gen2.
Use BigQuery TRANSLATE Instead of Teradata OTRANSLATE You can specify that Hyper-Q use the native TRANSLATE function for BigQuery instead of the Hyper-Q emulation of the Teradata OTRANSLATE function.