Stay organized with collections Save and categorize content based on your preferences.

Optimized SQL functions and operators

BigQuery BI Engine optimizes the following Google 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 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 Google Standard SQL operators and functions, see Google 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

  • 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

Analytic functions

Analytic functions have limited support. Queries with analytic 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 analytic functions are in the following list of supported functions.
  • The scanned table size times the number of analytic function operators does not exceed 300 MB. Two analytic functions with identical OVER clauses and the same direct inputs can share the same analytic 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 analytic 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 analytic 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 analytic function operators because the two functions have different direct inputs even though their OVER clauses appear the same.

Supported analytic 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

The following SQL features are not supported:

  • UDFs
  • External tables
  • The ARRAY and STRUCT types

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.