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:

  • SQL parameters
  • UDFs
  • External tables
  • The GEOGRAPHY, ARRAY, and STRUCT types
  • Analytic functions
  • Repeated fields (arrays)

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

  1. Create a custom query in Google Data Studio.
  2. Save the results of the query to a table.
  3. 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.
  4. Connect BI Engine to the table instead of using the query directly.