Optimized SQL functions and operators

BigQuery BI Engine optimizes the following standard SQL functions and operators when connecting business intelligence (BI) tools to BigQuery using custom queries or views. Queries that use functions and operators that are not on this list might run more 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 more information about connecting other BI tools to BigQuery, see BigQuery BI Engine SQL interface overview (preview).

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
  • ASCII
  • SAFE_CONVERT_BYTES_TO_STRING
  • INSTR
  • LEFT
  • RIGHT
  • TRANSLATE
  • INITCAP

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

  • Math
    • MOD
    • ROUND
    • ABS
    • SQRT
    • POW
    • SINH
    • ASIN
    • COSH
    • ACOS
    • ACOSH
    • ATANH
    • LN
    • LOG
    • LOG10
    • FLOOR
    • CEIL
    • DIV
    • EXP
    • ROUND
    • SAFE_NEGATE
    • SAFE_ADD
    • SAFE_SUBTRACT
    • SAFE_MULTIPLY
    • SAFE_DIVIDE
  • Strings
    • REGEXP_CONTAINS
    • REGEXP_EXTRACT
    • REGEXP_REPLACE
    • FORMAT
    • SUBSTR
    • ASCII
    • SAFE_CONVERT_BYTES_TO_STRING
    • INSTR
    • LEFT
    • RIGHT
    • TRANSLATE
    • INITCAP
  • Timestamps, dates and times
    • DATE_FROM_UNIX_DATE
    • PARSE_DATE
    • PARSE_DATETIME
    • PARSE_TIME
    • PARSE_TIMESTAMP
    • FORMAT_TIMESTAMP
    • TIMESTAMP_SECONDS
    • TIMESTAMP_MILLIS
    • TIMESTAMP_MICROS
  • Networking
    • NET.SAFE_IP_FROM_STRING
  • Other
    • SAFE_CAST

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
  • 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:

  1. Create a custom query in BigQuery.
  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. Reference this table in your performance-critical queries.