Cast Strings to Integer or Numeric Values

You can cast a string to an integer or numeric value on Google BigQuery that is compatible with Teradata conversion rules.

Hyper-Q synthesizes SQL that transforms string data according to the Teradata conversion rules, which allow for several non-standard formats. Consider the following sample Teradata queries.

SELECT CAST(t AS int) FROM (SELECT '1:23' AS t) x;

–and–

SELECT s FROM (SELECT '1:23' AS t, ‘aaa’ AS s) x WHERE t = 123;

The cast of t to int in these examples cannot be executed natively on Google BigQuery and will result in an error at runtime. However, using Hyper-Q, this operation succeeds and produces results compatible with the Teradata conversion logic. The additional logic may, however, affect the performance of queries. For this reason, Datometry has added a mechanism for database administrators to fine tune the translated queries.

To enable the casting of strings to integer or numeric values compatible with the Teradata conversion logic, use the Hyper-Q hint text_to_number.

The syntax for text_to_number is:

text_to_number = [ direct | emulated ]

where:

Parameter	Description
`direct`	Hyper-Q synthesizes the `CAST` statement in such a way that it uses a native implementation of `CAST`. In cases where the string data does not contain Teradata-specific formatting, the behavior is equivalent to using the Google BigQuery native `CAST` implementation.
`emulated`	Applies full emulation according to Teradata conversion rules.

The text_to_number hint is meant to simplify synthesized SQL in cases where the underlying text data is expected to conform to formatting that can be cast to numbers using the native implementation of CAST.

The format used in the above examples is not supported by a cloud data warehouse, hence, the statement will error out. For example:

SEL CAST(t as INT) FROM 
--@dtm_text_to_number=direct 
(SEL '1:23' AS t) x;

–and–

SELECT s FROM  
--@dtm_text_to_number=direct 
(SELECT '1:23' AS t, ‘aaa’ AS s) x WHERE t = 123;

These statements produce the following error:

Failure 3706 DTM3103: Failed to run query on the underlying database: Error Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server] Conversion failed when converting the varchar value '1:23' to data type int. SQLSTATE IS: 22018, Backend Error Code: 245, SQL State: 22018 (odbc_beta:457)

Important

Datometry recommends that you test the text_to_number hint using sample SQL statements before applying it to production workloads.

Example 3. Disable Teradata CAST Behavior Using a Global Setting

You can specify text_to_number as a global setting in the Hyper-Q configuration file (dtm.ini).

If you want to apply text_to_number to all statements that cast a string to an integer or numeric value on Google BigQuery that is compatible with Teradata conversion rules, the preferred method is to specify it as a global setting. The text_to_number property is in the [gateway] section of the dtm.ini file, which defines the connection to the target data warehouse.

"gateway".text_to_number="direct"

To learn more about the dtm.ini file, see Hyper-Q Configuration for Google BigQuery

Example 4. Disable Teradata CAST Behavior Using a Hyper-Q Session Hint

To disable the Teradata CAST behavior, specify the following Hyper-Q hint at the request level.

SELECT  
--@dtm_text_to_number=direct 
CAST(t AS int) FROM (SELECT SQL int '123' AS t) x;

The hint must be enclosed in the SQL statement to prevent the client querying the database from removing the comment before sending the statement to Hyper-Q. Regardless of its location in the statement text, the hint applies to the entire statement.

Note

The hint does not apply to individual casts in the statement, and the use of different settings for one hint within one statement are not supported.

Example 5. Enable Teradata CAST Behavior Using a Hyper-Q Query Hint

To enable the Teradata CAST behavior, specify the following Hyper-Q hint at the request level.

SELECT  
--@dtm_text_to_number=emulated 
CAST(t AS int) FROM (SELECT SQL int '123' AS t) x;

Datometry Documentation