関数のベストプラクティス

このドキュメントでは、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;