Optimized SQL functions and operators

BigQuery BI Engine optimizes the following GoogleSQL 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 Looker Studio, see Connect to BigQuery. For more information about connecting other BI tools to BigQuery, see BigQuery BI Engine SQL interface overview.

For a list of all GoogleSQL operators and functions, see GoogleSQL 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

  • ASCII
  • BYTE_LENGTH
  • CHAR_LENGTH
  • CHR
  • CODE_POINTS_TO_BYTES
  • CODE_POINTS_TO_STRING
  • CONCAT
  • ENDS_WITH
  • FORMAT
  • FROM_BASE32
  • FROM_BASE64
  • FROM_HEX
  • INITCAP
  • INSTR
  • JSON_EXTRACT_SCALAR
  • JSON_EXTRACT
  • LEFT
  • LENGTH
  • LOWER
  • LPAD
  • LTRIM
  • NORMALIZE
  • NORMALIZE_AND_CASEFOLD
  • REGEXP_CONTAINS
  • REGEXP_EXTRACT
  • REGEXP_INSTR
  • REGEXP_REPLACE
  • REPEAT
  • REPLACE
  • REVERSE
  • RIGHT
  • RPAD
  • RTRIM
  • SAFE_CONVERT_BYTES_TO_STRING
  • SOUNDEX
  • STARTS_WITH