最適化される 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
  • INNERLEFTRIGHT 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
    • EXTRACTDATE から)
    • 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_modeFULL_INPUT として報告します。
  • 入力ステージにウィンドウ関数を含むクエリの入力ステージは BI Engine によって高速化されますが、BI Engine のウィンドウ関数の制限事項に記載されている制限は適用されません。その場合、入力ステージまたはクエリ全体が BI Engine で実行されます。この場合、INFORMATION_SCHEMA.JOBS ビューは bi_engine_statistics.acceleration_modeFULL_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 カスタムクエリでサポートされていませんが、次の回避策があります。

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