Optimized SQL functions and operators

BigQuery BI Engine optimizes the following standard SQL functions and operators when connecting Google Data Studio to BigQuery using custom queries or views. Queries that use functions and operators that are not on this list might run slowly.

For a list of unsupported SQL features, see the Unsupported features section.

For more information about using custom queries and views in Data Studio, see Connect to BigQuery. For a list of all BigQuery Standard SQL operators and functions, see Standard SQL functions & operators.

Optimized operators

ORDER BY
LIMIT count [OFFSET skip_rows]
UNION ALL
HAVING
GROUP BY
WHERE
INNER, LEFT, and RIGHT JOIN (with smaller size limits)

Optimized functions

Comparison

=
<>
<
<=
>
>=
BETWEEN
IN
LEAST
GREATEST
CASE
COALESCE
IF
ISNULL
IS [NOT] NULL
IS [NOT] TRUE
IS [NOT] FALSE
IFNULL
NULLIF

Logical

NOT
AND
OR

Math

- (Unary minus)
+
- (Binary minus)
*
/
IEEE_DIVIDE
DIV
MOD
ROUND
TRUNC
FLOOR
CEIL
ABS
SIGN
SQRT
EXP
POW
IS_INF
IS_NAN
SIN
SINH
ASIN
ASINH
COS
COSH
ACOS
ACOSH
TAN
TANH
ATAN
ATAN2
ATANH
LOG
LN
LOG10

Bitwise

BIT_COUNT
~ (Bitwise NOT)
& (Bitwise AND)
| (Bitwise OR)
^ (Bitwise XOR)
<< (Bitwise left-shift)
>> (Bitwise right-shift)

Strings and bytes

CHAR_LENGTH
LENGTH
BYTE_LENGTH
STRPOS
SUBSTR
STARTS_WITH
ENDS_WITH
STRING_LIKE
REGEXP_CONTAINS
REGEXP_EXTRACT
REGEXP_REPLACE
UPPER
LOWER
CONCAT
TRIM
LTRIM
RTRIM
LPAD
RPAD
REPEAT
REVERSE
REPLACE
SAFE_CONVERT_BYTES_TO_STRING
TO_HEX
FROM_HEX
TO_BASE32
FROM_BASE32
TO_BASE64
FROM_BASE64
FORMAT
NORMALIZE
JSON_EXTRACT
JSON_EXTRACT_SCALAR

Timestamps, dates, and times

Timestamp functions
- TIMESTAMP_MICROS
- TIMESTAMP_MILLIS
- TIMESTAMP_SECONDS
- TIMESTAMP
- STRING(timestamp_expression,[timezone])
- UNIX_SECONDS
- UNIX_MILLIS
- UNIX_MICROS
- FORMAT_TIMESTAMP
- PARSE_TIMESTAMP
- TIMESTAMP_ADD
- TIMESTAMP_SUB
- TIMESTAMP_DIFF
- EXTRACT
- DATE
- TIME
- DATETIME
- TIMESTAMP_TRUNC
Date functions
- DATE(year, month, day)
- DATE_FROM_UNIX_DATE
- UNIX_DATE
- FORMAT_DATE
- PARSE_DATE
- DATE_ADD
- DATE_SUB
- DATE_DIFF
- EXTRACT (from DATE)
- DATE_TRUNC
- CURRENT_TIMESTAMP
Time functions
- EXTRACT
- FORMAT_TIME
- PARSE_TIME
- TIME_ADD
- TIME_SUB
- TIME_DIFF
- TIME_TRUNC
- CURRENT_TIME
Datetime functions
- DATETIME
- EXTRACT
- FORMAT_DATETIME
- PARSE_DATETIME
- DATETIME_ADD
- DATETIME_SUB
- DATETIME_DIFF
- DATETIME_TRUNC
- CURRENT_DATETIME

Hashing

FARM_FINGERPRINT
MD5
SHA1
SHA256
SHA512

Networking

NET_IP_IN_NET
NET_MAKE_NET
NET.HOST
NET.REG_DOMAIN
NET.PUBLIC_SUFFIX
NET.IP_FROM_STRING
NET.IP_TO_STRING
NET.IP_NET_MASK
NET.IP_TRUNC
NET.IPV4_FROM_INT64
NET.IPV4_TO_INT64

SAFE functions

SAFE_CAST
MOD
ROUND
ABS
SQRT
POW
REGEXP_CONTAINS
SAFE_NEGATE
SAFE_ADD
SAFE_SUBTRACT
SAFE_MULTIPLY
SAFE_DIVIDE
NET.SAFE_IP_FROM_STRING

Aggregation functions

SUM
COUNT
COUNTIF
ANY_VALUE
AVG
MAX
MIN
HLL_COUNT.INIT
HLL_COUNT.MERGE
HLL_COUNT.MERGE_PARTIAL
The GROUP BY clause
BIT_AND
BIT_OR
BIT_XOR
LOGICAL_AND
LOGICAL_OR
DISJUNCTION_AGG
STRING_AGG
CORR
COVAR_POP
COVAR_SAMP
STDDEV_POP
STDDEV_SAMP
STDDEV
VAR_POP
VAR_SAMP
VARIANCE
COUNT DISTINCT
APPROX_COUNT_DISTINCT

Other

CAST
HLL_COUNT.EXTRACT
The dot operator for STRUCT fields
ARRAY_LENGTH

Unsupported features

The following SQL features are not supported:

UDFs
External tables
The GEOGRAPHY, ARRAY, and STRUCT types
Analytic functions

While some SQL features are not supported in BigQuery BI Engine custom queries, there is an available workaround:

Create a custom query in BigQuery.
Save the results of the query to a table.
Schedule your custom query to update the table on a regular basis. An hourly or daily refresh rate works best, as refreshing every minute will invalidate the BI Engine cache more frequently.
Connect Google Data Studio to the table instead of using the query directly.