Datometry Documentation

You can specify that Hyper-Q use the native TRANSLATE function for BigQuery instead of the Hyper-Q emulation of the Teradata OTRANSLATE function.

You can configure your Hyper-Q environment to use the native TRANSLATE function when you have users who access BigQuery outside of Hyper-Q . Typically, Hyper-Q is granted more access than many users have, which prevents those users from running queries using Teradata OTRANSLATE outside of Hyper-Q.

There are behavioral differences between Teradata OTRANSLATE and BigQuery TRANSLATE. When using Teradata OTRANSLATE, if the same character occurs more than once in the from_string, only the replacement character from the to_string corresponding to the first occurrence is used. When using BigQuery TRANSLATE, a duplicate character in source_characters results in an error. You should carefully review the documentation for Teradata OTRANSLATE and BigQuery TRANSLATE to determine which is the preferred solution for your environment.

The syntax for use_native_translate_for_otranslate is:

use_native_translate_for_otranslate = true | false

where:

Hint Scope

The use_native_translate_for_otranslate hint can be specified at the session, request, query, and global level. To learn more about Hyper-Q SQL hints, and how to use them within a SQL query, see About Hyper-Q SQL Hints.

SELECT
-- @dtm_use_native_translate_for_otranslate=true for request
OTRANSLATE('abcde', 'ace', '1234');

"gateway".use_native_translate_for_otranslate=true

...

SELECT
@ dtm_use_native_translate_for_otranslate=false for request
OTRANSLATE('abcde', 'ace', '1234');