Google BigQuery Connectors

Back Next

Supported functions

The following table lists the functions that can be pushed to the Google BigQuery database by using full

SQL ELT optimization

Function	Function	Function	Function
ABS()	IN()	MOD()	SYSDATE()
ADD_TO_DATE()	INSTR()	POWER()	SYSTIMESTAMP()
AVG()	IS_DATE()	REG_REPLACE()	TAN()
CEIL()	IS_NUMBER()	REPLACECHR()	TANH()
CHR()	IS_SPACES()	REPLACESTR()	TO_BIGINT
CONCAT()	ISNULL()	ROUND(DATE)	TO_CHAR(DATE)
COS()	LAST_DAY()	ROUND(NUMBER)	TO_CHAR(NUMBER)
COSH()	LENGTH()	RPAD()	TO_CHAR(STRING)
COUNT()	LN()	RTRIM()	TO_DATE()
DATE_COMPARE()	LOG()	SIGN()	TO_DECIMAL()
DATE_DIFF()	LOWER()	SIN()	TO_FLOAT()
DECODE()	LPAD()	SINH()	TO_INTEGER()
EXP()	LTRIM()	SQRT()	TRUNC(DATE)
FLOOR()	MAX()	STDDEV()	TRUNC(NUMBER)
GET_DATE_PART()	MD5()	SUBSTR()	UPPER()
IIF()	MIN()	SUM()	VARIANCE()

Rules and guidelines

When you push functions to Google BigQuery, adhere to the following guidelines:

To push IS_DATE() function to Google BigQuery, you must configure the output field in the expression transformation to a column of string data type.

When you push the IS_DATE() function to Google BigQuery and use the SS.US format argument and specify values with the SS.MS or SS format, the IS_DATE() returns true.

When you push the IS_DATE() function to Google BigQuery and use the MON format argument and specify values with the MONTH format, the IS_DATE() returns true.

When you push the IS_DATE() function to Google BigQuery and use the MONTH format argument and specify values with the MON format, the IS_DATE() returns true.

When you use Is_Number(), Is_Spaces(), and Is_Date() in an Expression transformation, the output field type supports only integer and string data types.

When you push a function to Google BigQuery and the mapping runs without

SQL ELT optimization

, the IS_DATE() returns Boolean values of 0 or 1 to the Google BigQuery target table. If the mappings run with

SQL ELT optimization

, the IS_DATE() returns Boolean values of true or false to the Google BigQuery target table.

When you specify Is_Number(), Is_Spaces(), or Is_Date() functions in an Expression transformation, the output field type supports only integer and string data types.

To push the TO_CHAR(DATE) function to Google BigQuery, you must use the following string and format arguments:

YYYY

MON

MONTH

DDD

DAY

HH12

HH24

SS.MS

SS.US

To push the TO_DATE(string, format) function to Google BigQuery, you must use the following format arguments:

YYYY

MON

MONTH

HH12

HH24

SS.MS

SS.US

To push the ADD_TO_DATE(date, format, amount) or TRUNC(date, format) function to Google BigQuery, you must use the following format arguments:

YYYY

YYY

MON

MONTH

DDD

DAY

HH12

HH24

To push the GET_DATE_PART(date, format) function to Google BigQuery, you must use the following format arguments:

YYYY

YYY

MON

MONTH

DDD

DAY

HH12

HH24

When you push the GET_DATE_PART() function to the Google BigQuery database and specify null in the format argument, the mapping runs without

SQL ELT optimization

When you push the LAST_DAY() function to the Google BigQuery database and specify Date/Time or Timestamp value in the date argument, the LAST_DAY() function pushes only the date values to the Google BigQuery target. You might encounter a data mismatch when you compare a mapping that runs with full

SQL ELT optimization

and a mapping that runs without

SQL ELT optimization

To push the ROUND(DATE) function to Google BigQuery, you must use the following format arguments:

DDD

DAY

HH12

HH24

When you push the ROUND(DATE) function to the Google BigQuery database and use NS (nanoseconds) in the format argument, the mapping runs without

SQL ELT optimization

or with source

SQL ELT optimization

To push the ROUND(NUMBER) function to Google BigQuery, you must use a numeric value of the following data types:

Decimal

Numeric

NULL

To push the INSTR() function to Google BigQuery, you must only define the input_field and string arguments.

When you push the SYSTIMESTAMP() function to the Google BigQuery database, do not specify any format arguments. If you do not specify any format arguments, the Google BigQuery database returns the complete timestamp.

If you use a % operator in an expression transformation, the mapping converts the % operator to the MOD() function and pushes the MOD() function to Google BigQuery.

The MOD() function supports arguments of Int64 and Numeric data types. When you push the MOD() function to the Google BigQuery database, ensure that the format arguments are of the same data type. You can specify the arguments in the following formats:

MOD(Int64, Int64)

MOD(Numeric, Numeric)

When you push the TRUNC(DATE) function to the Google BigQuery database and specify a NULL value in the format argument, the mapping runs without

SQL ELT optimization

When you push the SYSDATE() function to the Google BigQuery database and the mapping runs with

SQL ELT optimization

, the function returns the current date and time based on the time zone associated with the Google BigQuery database.

When you push the SYSDATE() function to the Google BigQuery database and the mapping runs without

SQL ELT optimization

, the function returns the current date and time based on the time zone associated with the machine where the Secure Agent runs.

When you push the SUBSTR() function to the Google BigQuery database, you must specify a value of the String data type in the string argument. If you pass a numeric value, the mapping runs without

SQL ELT optimization

When you push the SUBSTR() function to the Google BigQuery database, the value of the length argument must be an integer greater than 0. If you specify a negative integer value for the length argument, the mapping runs without

SQL ELT optimization

When you push the EXP() function to the Google BigQuery database and specify a value of the Numeric or Double data type for the exponent argument, you might encounter a data mismatch in the decimal values when you compare a mapping that runs with full

SQL ELT optimization

and a mapping that runs without

SQL ELT optimization

When you push the RPAD() or LPAD() function to the Google BigQuery database, you must specify a value of the String data type in the first_string argument. If you specify a value other than the String data type in the first_string argument, the mapping runs without

SQL ELT optimization

When you push the RPAD() or LPAD() function to the Google BigQuery database and specify an empty string in the second_string or third_string argument, the mapping runs without full

SQL ELT optimization

When you push the REPLACECHR() function to the Google BigQuery database to write Numeric data to the Google BigQuery target, you can see a data mismatch between the results when you compare a mapping that runs with full

SQL ELT optimization

against a mapping disabled for

SQL ELT optimization

If the mapping runs with full

SQL ELT optimization

, the trailing zeroes after decimal is not considered while casting. However, if you run a mapping with disabled

SQL ELT optimization

, the Secure Agent casts the trailing zeroes after the decimal while casting from NUMERIC to String data types.

When you push the REPLACECHR() or REPLACESTR() function to the Google BigQuery database, the microseconds available in the timestamp data is considered in the casted string for a mapping that runs with full

SQL ELT optimization

You might encounter a data mismatch when you compare a mapping that runs with full

SQL ELT optimization

and a mapping that runs without

SQL ELT optimization

. The microseconds are not considered in the mapping that runs without

SQL ELT optimization

When you push the REPLACESTR() or REPLACECHR() function to the Google BigQuery database and specify special characters in the format arguments in a nested function, ensure that the nested function does not contain a single backslash. You can use a double backslash in the nested function.

You might encounter a data mismatch when you compare a mapping that runs with full

SQL ELT optimization

and a mapping that runs without

SQL ELT optimization

when you use a double backslash in the nested function.

When you push the REPLACESTR() or REPLACECHR() function using an Expression transformation with the data/time data types to Google BigQuery using full

SQL ELT optimization

, the default date format of the data/time data types returned for a mapping with

SQL ELT optimization

is YYYY-MM-DD HH24:MI:SS.US, whereas for a mapping without

SQL ELT optimization

is MM/DD/YYYY HH24:MI:SS.US.

To fix the issue in a mapping without

SQL ELT optimization

, use the TO_CHAR function to return the string date in the MM/DD/YYYY HH24:MI:SS.US format. For example, to get a similar result, use replacechr(1, TO_CHAR(col6_date), '09','1').

When you push down the Is_Number() function for Float data types with NaN, -inf, and +inf values, the Is_Number() function returns true.

When you push the Is_Date() function using an Expression transformation with the YYYY-MM-DD format and data contains data types with the YYYY-MM-DD and YYYY/MM/DD formats , the Is_Date() function returns true only for YYYYY-MM-DD. When you push the Is_Date() function with the YYYY/MM/DD format and data contains data types with the YYYY-MM-DD and YYYY/MM/DD formats , the Is_Date() function returns true only for YYYY/MM/DD.

When you push the Is_Number() function to process in Google BigQuery from a Secure Agent machine on Windows, the Is_Number() function returns false for the following format: '45.45d-2'

When you use the TO_CHAR(String) function, the string value must not contain a backslash (\). Else, the mapping fails.

When you configure an IN function that returns a value of string data type in an Expression transformation and writes the value to an integer data type in full

SQL ELT optimization

, the mapping fails with the following error:

The Secure Agent failed to run the full SQL ELT query due to the following error: [Bad int64 value: false]

When you configure an IN function that returns a value of integer data type in an Expression transformation and writes the value to an integer data type in full

SQL ELT optimization

, the mapping fails with the following error:

The Secure Agent failed to run the full SQL ELT query due to the following error: [Query column 1 has type BOOL which cannot be inserted into column COL_INT, which has type INT64 at [3:4]]

When you configure an IN function that returns a value of integer data type in an Expression transformation and writes the value to any data type in the target in source

SQL ELT optimization

, the mapping fails with the following error:

The following error occurred: [For input string: "true"]

When you run a mapping enabled with full

SQL ELT optimization

and if the arguments are null in the DATE_COMPARE function, the mapping runs without

SQL ELT optimization

When you configure a mapping enabled with full

SQL ELT optimization

, the mapping switches to source

SQL ELT optimization

or runs without

SQL ELT optimization

if the operands of

, or

operators contain NULL in the expression, aggregator, or filter transformations.

When you push the REG_REPLACE() function to the Google BigQuery database and specify a backslash (

) in the column name or a nested function, the mapping fails with the following error:

[ERROR] The Secure Agent failed to run the full SQL ELT query due to the following error: [Cannot parse regular expression: invalid escape sequence: \o]

When you push the REG_REPLACE() function to the Google BigQuery database, ensure that the column name or nested function does not contain a single backslash. The function only supports single backslashes that are followed by a digit or another backslash.

You might encounter a data mismatch when you compare a mapping that runs with full

SQL ELT optimization

and a mapping that runs without

SQL ELT optimization

when you use a double backslash in the nested function.

You cannot use the following escape sequences with the regular expression in the REG_REPLACE() function:

Escape sequence	Description
\1	Back reference
\b	Backspace (use \010 )
\cK	Control char ^K (For example, use \001 )
\e	Escape (use \033 )
\g1	Back reference
\g{1}	Back reference
\g{+1}	Back reference
\g{-1}	Back reference
\g{name}	Named back reference
\g<name>	Subroutine call
\g'name'	Subroutine call
\k<name>	Named back reference
\k'name'	Named back reference
\lX	Lowercase X
\ux	Uppercase x
\L...\E	Lowercase text ...
\K	Reset beginning of $0
\N{name}	Named Unicode character
\R	Line break
\U...\E	Upper case text ...
\X	Extended Unicode sequence
\%d123	Decimal character 123
\%xFF	Hex character FF
\%o123	Octal character 123
\%u1234	Unicode character 0x1234
\%U12345678	Unicode character 0x12345678

You cannot use the following empty strings with the regular expression in the REG_REPLACE() function:

Empty string	Description
\g	At beginning of subtext being searched
\G	At end of last match
\Z	At end of text, or before newline at end of text
(?=re)	Before text matching re
(?!re)	Before text not matching re
(?<=re)	After text matching re
(?<!re)	After text not matching re
re&	Before text matching re
re@=	Before text matching re
re@!	Before text not matching re
re@<=	After text matching re
re@<!	After text not matching re
\zs	Sets start of match (= \K )
\ze	Sets end of match
\%^	Beginning of file
\%$	End of file
\%V	On screen
\%#	Cursor position
\%'m	Mark m position
\%23l	In line 23
\%23c	In column 23
\%23v	In virtual column 23

When you configure the DATE_DIFF function in an Expression transformation and the transformation output is set to double or decimal data type in a mapping enabled with

SQL ELT optimization

, the function returns an integer value. However, when you run the mapping without

SQL ELT optimization

, the function returns a double value. Also, you might encounter a data mismatch when the transformation output is set to integer data type.

When you set the optional property

OptimizeCastsInPDO

in the Google BigQuery connection, you can compare the following data types in the DATE_DIFF function:

Date1 argument	Date2 argument	Format argument
Date	Date	Year, Month, and Day
Date	Datetime	Year, Month, Day, Hour, Minute, Second, Millisecond, and Microsecond
Datetime	Date	Year, Month, Day, Hour, Minute, Second, Millisecond, and Microsecond
Datetime	Datetime	Year, Month, Day, Hour, Minute, Second, Millisecond, and Microsecond
Time	Time	Hour, Minute, Second, Millisecond, and Microsecond
Timestamp	Timestamp	Day, Hour, Minute, Second, Millisecond, and Microsecond