PostgreSQLベースのBIツールの参照DBをBigQueryに移行したことで、
パフォーマンスが大きく改善(30分以上 -> 10秒)したので、その時の知見をメモとして残しておきたいと思います。
経緯
BIツールで扱うデータが大きくなり、集計処理が完了しない問題を抱えていました。
新しいデータがくるたびに高速化させるためのチューニングに時間をかけていて、
チューニングしても処理が遅くシステムとして多くの制限があり、
PostgreSQLを使わなければ全て解決しそうだったので分析基盤の移行を検討しました。
BigQueryの選定理由
単純にこれまで分析系の業務を中心にしており、BigQueryを使いデータ加工をしていたので
BigQueryへの移行で早くなるとは思っていたのですが、
以下の理由からNoSQLベースのBigtableやDynamoDBではなくBigQueryが良いと思いました。
2. 集計結果のデータサイズが小さい
集計結果が大きい場合、BigQuery自体の処理は高速ですが
GCP環境から集計結果を取得するのに時間がかかってしまいます。
パフォーマンス
圧倒的に良くなりました。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]'
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すごいす。これで新しいデータが来てもパフォーマンスチューニングに時間はかかりませんし、分析ツールもはるかに使い勝手が良くなりました。
