最適化される SQL 関数と演算子

BigQuery BI Engine は、ビジネスインテリジェンス（BI）ツールを BigQuery に接続するときに、カスタムクエリまたはビューを使用して次の GoogleSQL 関数と演算子を最適化します。このリストにない関数と演算子を使用するクエリは、実行速度が遅くなる可能性があります。

サポートされていない SQL の機能のリストについては、サポートされていない機能をご覧ください。

Looker Studio でのカスタムクエリとビューの使用については、BigQuery に接続するをご覧ください。他の BI ツールを BigQuery に接続する方法については、BigQuery BI Engine SQL インターフェースの概要をご覧ください。

GoogleSQL のすべての演算子と関数のリストについては、GoogleSQL の関数と演算子をご覧ください。

最適化される演算子

ORDER BY
LIMIT count [OFFSET skip_rows]
UNION ALL
HAVING
GROUP BY
WHERE
INNER、LEFT、RIGHT JOIN（サイズ制限が小さい）

最適化される関数

比較

=
<>
<
<=
>
>=
BETWEEN
IN
LEAST
GREATEST
CASE
COALESCE
IF
ISNULL
IS [NOT] NULL
IS [NOT] TRUE
IS [NOT] FALSE
IFNULL
NULLIF

論理

NOT
AND
OR

数学

-（単項マイナス）
+
-（バイナリマイナス）
*
/
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

ビット演算

BIT_COUNT
~（ビット演算 NOT）
&（ビット演算 AND）
|（ビット演算 OR）
^（ビット演算 XOR）
<<（ビット演算左シフト）
>>（ビット演算右シフト）

文字列とバイト

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

タイムスタンプ、日付、時間

タイムスタンプ関数
- 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(year, month, day)
- DATE_FROM_UNIX_DATE
- UNIX_DATE
- FORMAT_DATE
- PARSE_DATE
- DATE_ADD
- DATE_SUB
- DATE_DIFF
- EXTRACT（DATE から）
- DATE_TRUNC
- CURRENT_TIMESTAMP
時間関数
- EXTRACT
- FORMAT_TIME
- PARSE_TIME
- TIME_ADD
- TIME_SUB
- TIME_DIFF
- TIME_TRUNC
- CURRENT_TIME
日時関数
- DATETIME
- EXTRACT
- FORMAT_DATETIME
- PARSE_DATETIME
- DATETIME_ADD
- DATETIME_SUB
- DATETIME_DIFF
- DATETIME_TRUNC
- CURRENT_DATETIME

ハッシュ

FARM_FINGERPRINT
MD5
SHA1
SHA256
SHA512

ネットワーキング

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

地域

コンストラクタ
- ST_GEOGPOINT
- ST_MAKELINE
- ST_MAKEPOLYGON
- ST_MAKEPOLYGONORIENTED
パーサー
- ST_GEOGFROMGEOJSON
- ST_GEOGFROMTEXT
- ST_GEOGFROMWKB
- ST_GEOGFROMWKB
- ST_GEOGPOINTFROMGEOHASH
フォーマッター
- ST_ASBINARY
- ST_ASGEOJSON
- ST_ASKML
- ST_GEOHASH
変換
- ST_BOUNDARY
- ST_CENTROID
- ST_CLOSESTPOINT
- ST_CONVEXHULL
- ST_DIFFERENCE
- ST_EXTERIORRING
- ST_INTERSECTION
- ST_SIMPLIFY
- ST_SNAPTOGRID
アクセサー
- ST_DIMENSION
- ST_ENDPOINT
- ST_GEOMETRYTYPE
- ST_ISCOLLECTION
- ST_ISEMPTY
- ST_NPOINTS
- ST_NUMGEOMETRIES
- ST_NUMPOINTS
- ST_POINTN
- ST_STARTPOINT
- ST_X
- ST_Y
述語
- ST_CONTAINS
- ST_COVEREDBY
- ST_COVERS
- ST_DISJOINT
- ST_DWITHIN
- ST_EQUALS
- ST_INTERSECTS
- ST_INTERSECTSBOX
- ST_TOUCHES
- ST_WITHIN
メジャー
- ST_ANGLE
- ST_AREA
- ST_AZIMUTH
- ST_DISTANCE
- ST_LENGTH
- ST_MAXDISTANCE
- ST_PERIMETER

SAFE 関数

数学
- 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
文字列
- REGEXP_CONTAINS
- REGEXP_EXTRACT
- REGEXP_REPLACE
- FORMAT
- SUBSTR
- ASCII
- SAFE_CONVERT_BYTES_TO_STRING
- INSTR
- LEFT
- RIGHT
- TRANSLATE
- INITCAP
タイムスタンプ、日付、時間
- DATE_FROM_UNIX_DATE
- PARSE_DATE
- PARSE_DATETIME
- PARSE_TIME
- PARSE_TIMESTAMP
- FORMAT_TIMESTAMP
- TIMESTAMP_SECONDS
- TIMESTAMP_MILLIS
- TIMESTAMP_MICROS
ネットワーキング
- NET.SAFE_IP_FROM_STRING
その他
- SAFE_CAST

集計関数

SUM
COUNT
COUNTIF
ANY_VALUE
AVG
MAX
MIN
HLL_COUNT.INIT
HLL_COUNT.MERGE
HLL_COUNT.MERGE_PARTIAL
GROUP BY 句
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

ウィンドウ関数

分析関数とも呼ばれるウィンドウ関数は、BigQuery BI Engine で高速化される際に次の制限があります。

入力ステージにウィンドウ関数がない場合は、BigQuery BI Engine によって高速化されます。この場合、INFORMATION_SCHEMA.JOBS ビューは bi_engine_statistics.acceleration_mode を FULL_INPUT として報告します。
入力ステージにウィンドウ関数を含むクエリの入力ステージは BI Engine によって高速化されますが、BI Engine のウィンドウ関数の制限事項に記載されている制限は適用されません。その場合、入力ステージまたはクエリ全体が BI Engine で実行されます。この場合、INFORMATION_SCHEMA.JOBS ビューは bi_engine_statistics.acceleration_mode を FULL_INPUT または FULL_QUERY として報告します。

BiEngineStatistics フィールドの詳細については、ジョブ参照をご覧ください。

BI Engine のウィンドウ関数の制限事項

ウィンドウ関数を使用したクエリは、次のすべての条件が満たされている場合にのみ BI Engine で実行されます。

クエリが 1 つのテーブルのみをスキャンする。
- テーブルが分割されていない。
- テーブルの行数が 500 万行未満。
クエリに JOIN 演算子が含まれていない。
スキャンされるテーブルのサイズにウィンドウ関数演算子の数を掛けた結果が 300 MB を超えない。同じ OVER 句と同じ直接入力を持つ 2 つのウィンドウ関数が同じウィンドウ関数演算子を共有できる。次に例を示します。
- クエリ SELECT ROW_NUMBER() OVER (ORDER BY x), SUM(x) OVER (ORDER BY x) FROM my_table には、ウィンドウ関数演算子が 1 つしかありません。
- 2 つの関数に同じ OVER 句がないため、クエリ SELECT ROW_NUMBER() OVER (ORDER BY x), SUM(x) OVER (PARTITION BY y ORDER BY x) FROM my_table には 2 つのウィンドウ関数演算子があります。
- 2 つの関数で OVER 句が同じであっても、直接入力が異なるため、クエリ SELECT ROW_NUMBER() OVER (ORDER BY x) FROM (SELECT SUM(x) OVER (ORDER BY x) AS x FROM my_table) には 2 つのウィンドウ関数演算子があります。
参照されるウィンドウ関数が、次のサポートされている関数のリストにある。

サポートされているウィンドウ関数のリスト:

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

ウィンドウ関数がサポートされていない場合は、次のようなエラーが表示されることがあります。

分析関数が他の演算子と互換性がないか、入力が大きすぎます

その他の関数

CAST
HLL_COUNT.EXTRACT
STRUCT フィールドのドット演算子
ARRAY_LENGTH

制限事項

BI Engine SQL 関数と演算子のサポートには、次の制限があります。

BI Engine でサポートされていない機能

BI Engine による高速化は、次の機能では使用できません。

JavaScript UDF
外部テーブル
ネイティブ JSON データのクエリ - エラーメッセージ: JSON ネイティブ型はサポートされていません
BigQuery 永続テーブルへの結果の書き込み
BigQuery の変更データキャプチャを介した upsert を含むテーブル
トランザクション
1 GB を超えるデータを返すクエリレイテンシの影響を受けやすいアプリケーションの場合、レスポンスサイズを 1 MB 未満にすることをおすすめします。

Looker Studio の制限付きサポート

Looker Studio では、次の機能のサポートが制限されています。

SQL UDF
ARRAY 列のクエリ

機能がサポートされていない場合の回避策

一部の SQL 機能は BigQuery BI Engine でサポートされていませんが、次の回避策があります。

BigQuery でクエリを記述する。
クエリの結果をテーブルに保存する。
クエリをスケジューリングして、テーブルを定期的に更新する。1 分ごとに更新すると BI Engine キャッシュが頻繁に無効になるため、1 時間ごとまたは 1 日ごとの更新頻度が最適です。
パフォーマンスが重要なクエリでこのテーブルを参照します。