最適化される 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 による高速化は、次の機能では使用できません。
- JavaScript UDF
- 外部テーブル
- ネイティブ JSON データのクエリ
- BigQuery 永続テーブルへの結果の書き込み
- BigQuery の変更データ キャプチャを介した upsert を含むテーブル
- トランザクション
- 1 GB を超えるデータを返すクエリレイテンシの影響を受けやすいアプリケーションの場合、レスポンス サイズを 1 MB 未満にすることをおすすめします。
Looker Studio では、次の機能のサポートが限定されています。
- SQL UDF
ARRAY
列のクエリ
一部の SQL 機能は BigQuery BI Engine カスタムクエリでサポートされていませんが、次の回避策があります。
- BigQuery でカスタムクエリを作成する。
- クエリの結果をテーブルに保存する。
- カスタムクエリをスケジューリングして、テーブルを定期的に更新する。1 分ごとに更新すると BI Engine キャッシュが頻繁に無効になるため、1 時間ごとまたは 1 日ごとの更新頻度が最適です。
- パフォーマンスが重要なクエリでこのテーブルを参照します。