case-kの備忘録

日々の備忘録です。データ分析とか基盤系に興味あります。

分析基盤をPostgreSQLからBigQueryに移行させた時の知見

PostgreSQLベースのBIツールの参照DBをBigQueryに移行したことで、
パフォーマンスが大きく改善(30分以上 -> 10秒)したので、その時の知見をメモとして残しておきたいと思います。

経緯

BIツールで扱うデータが大きくなり、集計処理が完了しない問題を抱えていました。
新しいデータがくるたびに高速化させるためのチューニングに時間をかけていて、
チューニングしても処理が遅くシステムとして多くの制限があり、
PostgreSQLを使わなければ全て解決しそうだったので分析基盤の移行を検討しました。

BigQueryの選定理由

単純にこれまで分析系の業務を中心にしており、BigQueryを使いデータ加工をしていたので
BigQueryへの移行で早くなるとは思っていたのですが、
以下の理由からNoSQLベースのBigtableやDynamoDBではなくBigQueryが良いと思いました。

1. 現行がRDBベース

BigQueryはSQLが使えるので、NoSQLに切り替えるよりも楽だと考えました。

2. 集計結果のデータサイズが小さい

集計結果が大きい場合、BigQuery自体の処理は高速ですが
GCP環境から集計結果を取得するのに時間がかかってしまいます。

3. コスト面

BigQueryはBigtableやDynamoDBよりも、
パーティションやキャッシュ(24時間有効)を使えばコストを抑えることが可能と考えました。
(GCSやDataFlowと相性が良いのも理由です)

パフォーマンス

圧倒的に良くなりました。30分かかっても終わらなかった処理が10秒で済むように
なりました。

コスト

移行直後の検証ではコストが懸念でしたが、パーティショニングと事前計算を行い参照するカラムを少なくすることで
1日100~300円程度で済んでいます。(サービスの性質もあると思います)
また開発視点からも新しいデータを搭載するのに、SQLやテーブルの細かいチューニングが不要になったので
工数を抑えることができると思っています。

キャッシュ有効期間

キャッシュの有効期間は24時間です。同じクエリであればコストはかかりません。
cloud.google.com

パーティション方法

GoogleはTimeやDateといった時系列のパーティションのみサポートしています。
時間軸でパーティションするケースでない場合は新しくカラムを作ることで行うことができます。
今回は時間軸でパーティションではなかったので、パーティションしたいカラムとDate型のマスタテーブルを作りパーティションしてみました。
(テーブルの分割でも対応できたのですが、扱うデータの種類が多く全てのテーブルを毎回分割するのは好ましくないと考えました。)

   bq query \
   --destination_table [table name ] \
   --time_partitioning_type=DAY \
   --time_partitioning_field=[partitioning columns] \
   --use_legacy_sql=false \
   '[SQL]'

dream-yt.github.io


BigQuery実行コード(Node.js)
非同期処理となるので、Promissで処理が完了してから実行させるようにしました。
認証方法はサービスアカウントキーを使いました。公式サイトでは環境変数として扱う
方法が紹介されていますが、インスタンス生成時に読み込むこともできます。
googleapis.dev


インスタンス生成時にBigQueryのインスタンスも生成し、コンストラクタにサービスアカウントキーのパスを読み込みます。
注意事項として絶対パスでないといけません。

const ClassName = class [ClassName] {
  constructor(app, dependencies) {
    this.bigqueryClient = new BigQuery({
      projectId: [プロジェクトID],
      keyFilename: '[サービスアカウントキーのパス]'
    });
  }

関数定義。今回の仕様では非同期処理が完了するまで待つ必要があったので、PromissでBigQueryによる集計処理が完了してから結果を返すようにしました。
qiita.com
qiita.com

  selectBigQuery(query) {
    return new Promise((resolve, reject) => {
      const options = {
        query,
        // Location must match that of the dataset(s) referenced in the query.
        // location:'US',
      };
      this.bigqueryClient.query(options, (err, rows) => {
        if (err) {
          return reject(err);
        }
        return resolve(rows);
      });
    });
  }

関数は以下のようにして呼びます。注意点としてfinallyを使うとエラーが出るので注意です。
Error: Can't set headers after they are sent.
参考:https://teratail.com/questions/97516

apiFunc (req, res) {
  // パラメータ取得
  const query  = `SELECT ???? WHERE X = ${req.body}`;
  this.selectBigQuery(query).then((result) => {
    // 集計結果
    return res.status(200).send(result);
  }).catch((err) => {
    return res.status(400).send(err.message);
  });
}

仕様を考慮し実装方法を変えましたが基本的には以下の
公式サイトを参考に行いました。
cloud.google.com
cloud.google.com

おわりに

BigQueryすごいす。これで新しいデータが来てもパフォーマンスチューニングに時間はかかりませんし、分析ツールもはるかに使い勝手が良くなりました。