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
  • STRING_LIKE
  • STRPOS
  • SUBSTR
  • TO_BASE32
  • TO_BASE64
  • TO_HEX
  • TRANSLATE
  • TRIM
  • UNICODE
  • UPPER

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

Geography

  • Constructors
    • ST_GEOGPOINT
    • ST_MAKELINE
    • ST_MAKEPOLYGON
    • ST_MAKEPOLYGONORIENTED
  • Parsers
    • ST_GEOGFROMGEOJSON
    • ST_GEOGFROMTEXT
    • ST_GEOGFROMWKB
    • ST_GEOGFROMWKB
    • ST_GEOGPOINTFROMGEOHASH
  • Formatters
    • ST_ASBINARY
    • ST_ASGEOJSON
    • ST_ASKML
    • ST_GEOHASH
  • Transformations
    • ST_BOUNDARY
    • ST_CENTROID
    • ST_CLOSESTPOINT
    • ST_CONVEXHULL
    • ST_DIFFERENCE
    • ST_EXTERIORRING
    • ST_INTERSECTION
    • ST_SIMPLIFY
    • ST_SNAPTOGRID
  • Accessors
    • ST_DIMENSION
    • ST_ENDPOINT
    • ST_GEOMETRYTYPE
    • ST_ISCOLLECTION
    • ST_ISEMPTY
    • ST_NPOINTS
    • ST_NUMGEOMETRIES
    • ST_NUMPOINTS
    • ST_POINTN
    • ST_STARTPOINT
    • ST_X
    • ST_Y
  • Predicates
    • ST_CONTAINS
    • ST_COVEREDBY
    • ST_COVERS
    • ST_DISJOINT
    • ST_DWITHIN
    • ST_EQUALS
    • ST_INTERSECTS
    • ST_INTERSECTSBOX
    • ST_TOUCHES
    • ST_WITHIN
  • Measures
    • ST_ANGLE
    • ST_AREA
    • ST_AZIMUTH
    • ST_DISTANCE
    • ST_LENGTH
    • ST_MAXDISTANCE
    • ST_PERIMETER

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

Window functions

Window functions, also known as analytical functions, have limited support. Queries with window functions are supported only if all the following conditions are met:

  • The query scans exactly one table.
    • The table is not partitioned.
    • The table has less than 5 million rows.
  • The query does not have any JOIN operators.
  • The referenced window functions are in the following list of supported functions.
  • The scanned table size times the number of window function operators does not exceed 300 MB. Two window functions with identical OVER clauses and the same direct inputs can share the same window function operator. For example:
    • The query SELECT ROW_NUMBER() OVER (ORDER BY x), SUM(x) OVER (ORDER BY x) FROM my_table has only one window function operator.
    • The query SELECT ROW_NUMBER() OVER (ORDER BY x), SUM(x) OVER (PARTITION BY y ORDER BY x) FROM my_table has two window function operators because the two functions do not have the same OVER clause.
    • The query SELECT ROW_NUMBER() OVER (ORDER BY x) FROM (SELECT SUM(x) OVER (ORDER BY x) AS x FROM my_table) has two window function operators because the two functions have different direct inputs even though their OVER clauses appear the same.

Supported window function list:

  • ANY_VALUE
  • AVG
  • BIT_AND
  • BIT_OR
  • BIT_XOR
  • CORR
  • COUNT
  • COUNTIF
  • COVAR_POP
  • COVAR_SAMP
  • CUME_DIST
  • DENSE_RANK
  • FIRST_VALUE
  • LAG
  • LAST_VALUE
  • LEAD
  • LOGICAL_AND
  • LOGICAL_OR
  • MAX
  • MIN
  • NTH_VALUE
  • NTILE
  • PERCENT_RANK
  • PERCENTILE_CONT
  • PERCENTILE_DISC
  • RANK
  • ROW_NUMBER
  • ST_CLUSTERDBSCAN
  • STDDEV_POP
  • STDDEV_SAMP
  • STDDEV
  • STRING_AGG
  • SUM
  • VAR_POP
  • VAR_SAMP
  • VARIANCE

    Other

  • CAST

  • HLL_COUNT.EXTRACT

  • The dot operator for STRUCT fields

  • ARRAY_LENGTH

Unsupported features

BI Engine acceleration is not available for the following features:

  • JavaScript UDFs
  • External tables
  • Querying native JSON data
  • Writing results to a permanent BigQuery table
  • Tables containing upserts via BigQuery change data capture
  • Transactions
  • Queries that return more than 1 GB of data. For latency-sensitive applications, a response size of less than 1 MB is recommended.

The following features have limited support in Looker Studio:

  • SQL UDFs
  • Querying ARRAY columns

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.