BigQuery の Federated Query で CloudSQL 側のインデックスを利用する

Author:

2023-09-15

はじめに

本記事では BigQuery (BQ) の Federated Query における SQL Pushdown ついて説明します。 BQ の Federated Query では以前までデータを全て BQ 側に取得してからフィルタリングが行われていましたが、SQL Pushdown の仕組みにより BQ 側の WHERE 句をデータソースに伝播することが可能になりました。これにより SQL の組み立てによってはインデックスの利用も可能になり、パフォーマンスの向上が期待できます。

2023 年 4 月にブログで発表されていましたが私は認知しておらず、最近のデータプラットフォームに関わる取り組みでクエリが伝播しているのではないかと気づき調査を行ったので、機能の説明と調査のために行ったことをまとめます。

前提知識

Federated Query

Federated Query とは、BigQuery が外部データソースを参照する機能です。ドキュメントにあるように、 BQ で EXTERNAL_QUERY 関数を用いることで CloudSQL や Spanner など外部のデータソースを参照することができます。

SELECT c.customer_id, c.name, rq.first_order_date
FROM mydataset.customers AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
  'us.connection_id',
  '''SELECT customer_id, MIN(order_date) AS first_order_date
  FROM orders
  GROUP BY customer_id''') AS rq ON rq.customer_id = c.customer_id
GROUP BY c.customer_id, c.name, rq.first_order_date;

これにより異なるデータソースに分散しているデータのコピーや移動を行わず、 BQ をハブにして Join したり、BQ の仕組みを利用してデータの加工ができるようになります。

プッシュダウン

プッシュダウンは ELT で用いられる用語で、データの抽出処理をデータソース側に行わせるなど、ロジックを基盤となるターゲットにプッシュしてパフォーマンスを向上させる仕組みです。クエリ処理中に不要な行を早い段階でフィルタリングすることでデータの抽出量を減らし、処理時間の短縮やデータ送信量の削減をすることができます。

SQL Pushdown in BigQuery

SQL Pushdown は、BQ の最適化技術の一つで、Google Cloud の公式ブログで 2023 年 4 月頃に発表されています。
この技術を使用すると、BQ は外部のデータソース（CloudSQL や Cloud Spanner）に対して、特定の操作（例: フィルタリング）を委任することができます。具体的には、 BQ が外部データソースからデータを取得する際に、必要なデータのみを選択的に取得するようなクエリを生成します。

SQL pushdown

SQL pushdown is an optimization technique. It happens when BigQuery delegates operations like filtering down to the external data source (e.g., Cloud SQL or Cloud Spanner database) instead of performing them on their own. Thanks to that smaller amount of data needs to be transferred back to BigQuery, the overall query execution time is lower and the cost can be significantly reduced since less data is processed. SQL pushdown ecompasses both column pruning (SELECT clauses) and filter pushdowns (WHERE clauses).

この技術の主な利点は以下の通りです：

データ転送量の削減: 必要なデータのみが BigQuery に転送されるため全体のデータ転送量が大幅に削減
クエリ実行時間の短縮: データの転送量が少なくなることでクエリの実行時間短縮
コスト削減: データの転送と処理量が減少することでコスト削減

SQL Pushdown 実装以前の Federated Query では、外部データソース向けのクエリは独立して実行され BQ としてのクエリのフィルタリングは外部データソースからの結果を受け取ってから処理を行っていました。

ブログの例を用いて説明すると、例えば以下のクエリを CloudSQL に対して発行した場合を考えます。

SELECT COUNT(*) FROM
    (Select * from
        EXTERNAL_QUERY("<connection>",
                       "select * from operations_table")
    )
where a = 'Y' and b not in ('COMPLETE','CANCELLED') and c = 'Y';

SQL Pushdown がない場合、CloudSQL では以下のクエリが実行されます。

SELECT * FROM  operations_table

これは BQ 側の WHERE 句で定義されたものには影響を受けず、EXTERNAL_QUERY 関数内で定義されたデータソース向けのクエリが純粋に実行されています。

SQL Pushdown が適用された場合、 CloudSQL で実行されるクエリは以下となり、BQ で定義した WHERE 句の内容が CloudSQL まで伝わります。

SELECT
  "a",
  "b",
  "c"
FROM (
  SELECT *
  FROM  operations_table) t
WHERE
  (("a" = $1)
    AND (NOT "b" IN ($2,
        $3))
    AND ("c" = $4))

SQL Pushdown 実装以前にデータソース側のインデックスを利用したり転送データを絞りたい場合、 EXTERNAL_QUERY 関数内部のクエリでフィルタリングを行う必要がありました。そのため外部参照データを BQ で常用しやすいように View を作るような場合、ユースケースごとにデータソース向けクエリのカスタマイズが必要になります。
SQL Pushdown の仕組みが適用されると、 BQ の SQL の WHERE 句で指定するだけでデータソース側でインデックスの利用やデータ量削減が期待でき、データソース側の SQL を意識する必要がなくなります。

パフォーマンス向上に寄与する便利で嬉しい SQL Pushdown ですが、利用するための制限もあります。
2023 年 9 月時点では、SQL Pushdown を利用するためにはデータソース向けのクエリは SELECT * FROM T で表現する必要があります。また、すべてのデータタイプやケースで SQL Pushdown がサポートされているわけではありません。このことから、データソース側で複雑なクエリを必要とするユースケースで SQL Pushdown を利用したい場合にはデータソース側で予め View を定義しておくことが必要になります。また、インデックスの利用などはデータソース側のオプティマイザに依存する部分もあるので、SQL Pushdown に依存したユースケースを本番で利用するには事前に以下の点を確認したほうが良いでしょう。

データソースに期待したフィルタリングが伝わっているか実行されるクエリを確認する
データソース側でインデックスが利用されているかなど、SQL Pushdown で伝わるクエリに対して EXPLAIN などをして統計情報を確認する

SQL Pushdown に気づいた経緯と調査

SQL Pushdown は 2023 年 4 月に発表されていましたが、私は 9 月頃までが実装されていることを知りませんでした。

これに気づいた経緯としては、最近のデータプラットフォーム関連の取り組みで重めのクエリを走らせたところ効かないはずのインデックスが効いているような挙動をメンバーが指摘し、 (それは出来ないはずと思いつつも) 一応調査をしてみたら実際に BQ 上の WHERE 句における条件がデータソースに伝播していることを見つけたことでした。

調査で行ったこと

調査では以下のことを行いました

CloudSQL 上で走るデータベースにログインし BQ から受け取るクエリを確認する
クエリに対して EXPLAIN を行い実行計画を確認する

CloudSQL 上で走るデータベースでクエリを確認する

今回の調査では CloudSQL 上では BQ からどのようなクエリを受け取っているかを確認するため、CloudSQL 上で走るデータベースに直接ログインしました。 Cloud SQL に発行するクエリを確認するためには Query Insights という機能があります。この機能は実行されたクエリやそのクエリによる負荷などがわかるのですが、MySQL の場合、2023 年 9 月時点では Query Insights を有効にしていると Federated Query が実行できない制限があります。そのため、今回のユースケースで利用するデータベースでは Query Insights を無効にしていました。

今回は開発環境の CloudSQL に直接ログインし(参考)、show full processlist; で実行中のクエリを確認しました。

今回 BQ 上で実行したクエリと MySQL 上で観測できたクエリが以下のものです。このクエリは MySQL 上で定義してある View を BQ から参照しており、一部マスクしてあります。

BigQuery:

SELECT *
FROM  EXTERNAL_QUERY(
    'project_id.asia-northeast1.bd_name',
    '''
    select * from schema.view_name
    '''
)
    WHERE event_created_at BETWEEN "2023-07-01" AND "2023-09-01";

MySQL on CloudSQL:

 SELECT `f1`, `f2`, `f3`, `f4`, `f5`, `f6`, `event_created_at`
 FROM (select * from schema.view_name) t
 WHERE `event_created_at` BETWEEN STR_TO_DATE('2023-07-01T00:00:00.000000', '%Y-%m-%dT%H:%i:%s.%f') AND STR_TO_DATE('2023-09-01T00:00:00.000000', '%Y-%m-%dT%H:%i:%s.%f')

f1 ~ f6 は View で定義されているカラムをマスクした形です。

BQ 上における event_created_at というフィールドの WHERE 句が CloudSQL に伝播していることがわかります。

クエリの EXPLAIN を確認する

次に、CloudSQL 上で実行されるクエリに対して EXPLAIN を行い、実行計画を確認しました。この MySQL 上のクエリで、 FROM (select * from schema.view_name) t として参照されている View を構成する実テーブルに対するインデックスが期待通りに効くのかが気になったためです。

EXPLAIN の結果の詳細は省きますが、今回の条件では期待通りのインデックスが利用できていました。

おわりに

本記事では BigQuery の Federated Query における SQL Pushdown ついて説明し、CloudSQL 上での確認方法についても触れました。 SQL Pushdown により早い段階でのフィルタリングやデータ転送量の削減、データソース側の仕組みを用いたパフォーマンス向上が期待できます。一方で、データソース側へのクエリは SELECT * FROM T の形である必要があります。

SQL Pushdown を複数のテーブルを参照したり複雑な条件が必要なデータに用いたい場合、データソース側で予め View を定義し、BQ 側から見える形にすると良いと思います。また、Federated Query を用いてデータを利用する場合には本番化前 SQL Pushdown が利用できるのか、データソースへ伝播するクエリの実行計画はどのようなものになるのかを確認して調整すると、より効率的で使いやすい仕組みが作れると思います。

最後に、Belong Inc. では我々と一緒にサービスの成長にコミットしてくれるメンバーを募集中です。 GCP スペシャリストやデータパイプラインを構築したい方、分析に興味がある方など様々なポジションがあります。

ぜひエンジニアリングチーム紹介ページをご覧いただけたら幸いです。