関数のベストプラクティス
このドキュメントでは、SQL 関数を使用するクエリを最適化する方法について説明します。
文字列の比較を最適化する
ベスト プラクティス: 可能であれば、REGEXP_CONTAINS
ではなく LIKE
を使用します。
BigQuery では、REGEXP_CONTAINS
関数または LIKE
演算子を使用して文字列を比較できます。REGEXP_CONTAINS
は多くの機能を提供しますが、実行に時間がかかります。REGEXP_CONTAINS
ではなく LIKE
を使用すると、処理時間が短くなります。特に、ワイルドカード一致など、REGEXP_CONTAINS
が提供する正規表現をフルに活用する必要がない場合には処理時間が短くなります。
次の REGEXP_CONTAINS
関数の使用を検討してください。
SELECT dim1 FROM `dataset.table1` WHERE REGEXP_CONTAINS(dim1, '.*test.*');
このクエリは次のように最適化できます。
SELECT dim1 FROM `dataset.table` WHERE dim1 LIKE '%test%';
集計関数を最適化する
ベスト プラクティス: ユースケースでサポートされていれば、近似集計関数を使用します。
使用している SQL 集計関数に同等の近似関数がある場合は、近似関数を使用することでクエリのパフォーマンスが向上します。たとえば、COUNT(DISTINCT)
の代わりに APPROX_COUNT_DISTINCT
を使用します。詳細については、近似集計関数をご覧ください。
また、HyperLogLog++
関数を使用して概算(カスタム近似集計を含む)を行うこともできます。詳細については、GoogleSQL リファレンスの HyperLogLog 関数をご覧ください。
次の COUNT
関数の使用を検討してください。
SELECT dim1, COUNT(DISTINCT dim2) FROM `dataset.table` GROUP BY 1;
このクエリは次のように最適化できます。
SELECT dim1, APPROX_COUNT_DISTINCT(dim2) FROM `dataset.table` GROUP BY 1;
分位関数を最適化する
ベスト プラクティス: 可能であれば、NTILE
ではなく APPROX_QUANTILE
を使用します。
NTILE
関数を含むクエリを実行した場合、1 つのパーティションで多数の要素を ORDER BY
に設定することによりデータ量が増大し、Resources exceeded
エラーで失敗する可能性があります。分析ウィンドウはパーティショニングされていないため、NTILE
の計算には、テーブル内のすべての行を単一のワーカーまたはスロットで処理するためにグローバルな ORDER BY
が必要です。
代わりに APPROX_QUANTILES
を使用してください。この関数を使用すると、テーブル内のすべての行に対するグローバルな ORDER BY
が不要になるため、クエリをより効率的に実行できます。
次の NTILE
関数の使用を検討してください。
SELECT individual_id, NTILE(nbuckets) OVER (ORDER BY sales desc) AS sales_third FROM `dataset.table`;
このクエリは次のように最適化できます。
WITH QuantInfo AS ( SELECT o, qval FROM UNNEST(( SELECT APPROX_QUANTILES(sales, nbuckets) FROM `dataset.table` )) AS qval WITH offset o WHERE o > 0 ) SELECT individual_id, (SELECT (nbuckets + 1) - MIN(o) FROM QuantInfo WHERE sales <= QuantInfo.qval ) AS sales_third FROM `dataset.table`;
APPROX_QUANTILES
により、最適化されたクエリの結果は元のクエリと類似していますが、完全に同一ではありません。
- 近似集計を提供します。
- 残りの値(行数をバケットで割った残り)を別の方法で振り分けます。
UDF を最適化する
ベスト プラクティス: クエリ オプティマイザーは SQL UDF 定義に最適化を適用できるため、単純な計算には SQL UDF を使用します。SQL UDF でサポートされていない複雑な計算には JavaScript UDF を使用します。
JavaScript UDF を呼び出すには、サブプロセスをインスタンス化する必要があります。このプロセスをスピンアップして UDF を実行すると、クエリのパフォーマンスに直接影響します。可能であれば、代わりにネイティブ(SQL)UDF を使用してください。
永続的な UDF
UDF を作成して毎回コードで呼び出すのではなく、一元管理された BigQuery データセットに、クエリや論理ビューで呼び出せる永続的なユーザー定義の SQL 関数と JavaScript 関数を作成することをおすすめします。共有データセット内に組織全体のビジネス ロジック ライブラリを作成すると、パフォーマンスを最適化し、使用するリソースを削減できます。
次の例は、クエリで一時的な UDF を呼び出す方法を示しています。
CREATE TEMP FUNCTION addFourAndDivide(x INT64, y INT64) AS ((x + 4) / y); WITH numbers AS (SELECT 1 as val UNION ALL SELECT 3 as val UNION ALL SELECT 4 as val UNION ALL SELECT 5 as val) SELECT val, addFourAndDivide(val, 2) AS result FROM numbers;
一時的な UDF を永続的な UDF に置き換えることで、このクエリを最適化できます。
WITH numbers AS (SELECT 1 as val UNION ALL SELECT 3 as val UNION ALL SELECT 4 as val UNION ALL SELECT 5 as val) SELECT val, `your_project.your_dataset.addFourAndDivide`(val, 2) AS result FROM numbers;