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

Note: For joins in the BI Engine SQL interface, acceleration is restricted to leaf-level subqueries with INNER and LEFT OUTER JOINS, where a large (fact) table is joined with up to four small tables. Each of the small tables must have fewer than 5 million rows. Each small table must be less than 5 GB for unpartitioned tables or less than 1 GB for partitioned tables for the referenced columns.

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

Important: Timezone arguments for the following functions must be const.

• 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 the following limitations when accelerated by BigQuery BI Engine:

• The input stages are accelerated by BigQuery BI Engine if they don't have window functions. In this case INFORMATION_SCHEMA.JOBS view reports bi_engine_statistics.acceleration_mode as FULL_INPUT.
• The input stages of queries with window functions in their input stages are accelerated by BI Engine, but cannot have the limitations described in the BI Engine Window functions limitations section. In that case, the input stages or the full query is executed in BI Engine. In this case INFORMATION_SCHEMA.JOBS view reports bi_engine_statistics.acceleration_mode as FULL_INPUT or FULL_QUERY.

For more information about the BiEngineStatistics field, see the Job reference.

BI Engine Window functions limitations

Queries with window functions are executed in BI Engine 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 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.
• The referenced window functions are in the following list of supported functions.

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

If window functions aren't supported, then you might see the following error:

Analytic function is incompatible with other operators or its inputs are too large

Other functions

• CAST
• HLL_COUNT.EXTRACT
• The dot operator for STRUCT fields
• ARRAY_LENGTH

Limitations

The following limitations and restrictions apply to BI Engine SQL function and operator support.

BI Engine unsupported features

BI Engine acceleration is not available for the following features:

• JavaScript UDFs
• External tables
• Querying native JSON data - Error message: JSON native type is not supported
• 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.

Limited Looker Studio support

The following features have limited support in Looker Studio:

• SQL UDFs
• Querying ARRAY columns

Work-around for unsupported features

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

1. Write a query in BigQuery.
2. Save the results of the query to a table.
3. Schedule your 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.