KARTE Signals の複雑 SQL を支える dbt の活用

<html><head></head><body>こんにちは、エンジニアの <a href="https://twitter.com/kargo113" target="_blank" rel="noreferrer">@kargo</a> です。
今回は、2023年4月からBigQueryがEditionsという料金体系の変更を行なったことによる、コスト最適化の挑戦について書いていこうと思います。前回は3年以上前になりますが、<a href="https://tech.plaid.co.jp/bigquery-slot-resource-optmization">6,000スロットを使うBigQueryのリソース配分最適化への挑戦</a> として同様にコストの最適化についてご説明してありますので、Slotとは何？という疑問をお持ちの方はこちらも是非ご参照下さい。
BigQueryは、Google Cloud Platformが提供する、大規模なデータセットを高速に分析できるデータウェアハウスです。BigQueryは、企業のデータ分析基盤として広く利用されており、その利用量は年々増加しています。
しかし、BigQueryのコストは、データの量やクエリの実行回数に応じて増加するため、コスト管理が重要となります。特に、BigQueryは2023年4月から、料金体系が<a href="https://cloud.google.com/bigquery/docs/editions-transition?hl=ja" target="_blank" rel="noreferrer">Editionsに変更</a>され、従来の料金体系が変化しました。弊社におけるインパクトは主に2つあり
<ul>
<li>slot(定額)の単価の増加</li>
<li>storageコストの減少</li>
</ul>
による影響が発生しました。
storageコストに関する<a href="https://cloud.google.com/bigquery/pricing?hl=ja#storage" target="_blank" rel="noreferrer">料金変更</a>としては、Physical StorageとLogical Storageといういずれかの課金体系が選択可能になったという点があります。どちらが最適かはユースケースによって大きく異なるため詳細は省きますが、Long-Term Physical Storage という圧縮効率が高く、長期保存されるデータに関しての費用が減少するため、弊社の場合はPhysical Storageへの課金とすることである程度のコスト減となりました。
一方で、slotの単価が $0.024/slothour から $0.048/slothour へ2倍の増加となるので、こちらのインパクトがより大きいという実情でした。従って、全体では大幅に増加という影響になり、弊社の分析基盤の要でもあるため、7月から9月にかけて集中的に主にSlotを中心としたコストを最適化する流れとなりました。その名も、Bigquery-Slot-Optimization Teamです！
本記事では、BigQueryのSlotの最適化について主に実行した ① Monitoringの整備によるJobごとのSlot監視と削減、②AutoScaleの導入によるSlotBaseLineの最適化 という2つの観点から解説します。
<h2 id="① Monitoringの整備によるJobごとのSlot監視と削減">① Monitoringの整備によるJobごとのSlot監視と削減</h2>
Slotのコスト削減のためには、まず、BigQueryの利用状況を把握することが重要です。BigQueryの利用状況を把握する第一歩は、BigQueryのConsoleのMonitoringを利用することです。Monitoringでは、BigQueryのジョブの実行状況やリソースの使用状況を時系列で確認することができます。Organization全体ではこのような利用状況であることが確認できます。 
<img src="https://ik.imagekit.io/plaid/cba093e9-b2e1-4746-b24f-4f0f99077eb7/bq_console_top_v2.png" alt="bq_console_top_v2.png">
MonitoringでBigQueryの利用状況を把握したら、slotを大きく消費しているJobを特定します。しかし、Monitoringの画面だけでは、ReseravtionやProjectごとでのGroupByしかできないため、クエリレベルでの細かい単位で調査することはできません。また、そもそも画面が非常に重く現実的に使いやすいものとは言えないという背景がありました（こちらはGoogleさん...お願いします...!）。クエリにラベルを適切に付与していれば、そちらで管理することも可能ですが、全てのProductでクエリにラベルを振るのは現実的ではありませんでした。そこで、独自のMonitoringを構築する必要が生じるため、CloudFunction/CloudScheduler/Datadog を用いて実装します。
まず、slotの消費は主にslot_ms (slot millisecond)で調査することになります。100slotを60sec利用するJobであれば、100×60×1000 = 6,000,000 slot millisecond という単位になります。
時系列で把握したいので、<code>INFORMATION_SCHEMA.JOBS_TIMELINE</code> というテーブルを利用します。 
単純には
<pre><code class="language-sql hljs"> SELECT
 job_id,
 period_start,
 period_slot_ms
 FROM
 `{YOUR_PROJECT_NAME}.region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE
 WHERE
 period_start BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 20 MINUTE)
 AND TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 5 MINUTE)
 AND job_creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 20 MINUTE)
 AND TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 5 MINUTE)
 AND job_type = 'QUERY'
 AND statement_type != 'SCRIPT'
</code></pre>
のようにすれば、簡単に取得できます。
period_slot_msとはperiod(=1sec)あたりのslot_msを意味するので、1secあたりのJobごとのスロット消費量が簡単に取得できます。
このテーブルをさらに、<code>INFORMATION_SCHEMA.JOBS</code> というテーブルを用いて、job_idをkeyにjoinすることで、実行されたqueryを取得できます。取得したqueryをさらに、<code>REGEXP_CONTAINS</code> 関数を用いて、正規表現で任意のJobに集約します。例えば、<code>WHEN REGEXP_CONTAINS(query, r'/* JOURNEY') THEN 'journey'</code> という表記により、queryのコメントに<code>/* JOURNEY</code> が含まれているqueryを <a href="https://karte.io/product/journey/" target="_blank" rel="noreferrer">Journey</a> というProductのために使っているQuery Jobという形で集約できます。
集約することで、Productごとに消費したslotのtimelineが得られるので、これをDatadogに送信します。送信するには <a href="https://cloud.google.com/functions?hl=en" target="_blank" rel="noreferrer">CloudFunction (2nd gen)</a> を用いることで簡単にDatadogに送信できます。
Query ResultをBigqueryClientとDatadogClientを用いて送信するだけです。 
TypeScriptで書くとこのようになります。（※一部抜粋）
<pre><code class="language-typescript hljs"> const slots: v2.MetricSeries = {
 metric: '${metric_name}',
 type: 2,
 interval: 1,
 points: points,
 tags: [`project:${projectId}`, `task:${task}`],
 };

 const params: v2.MetricsApiSubmitMetricsRequest = {
 body: {
 series: [slots],
 },
 };

 apiInstance
 .submitMetrics(params)
 .then((data: v2.IntakePayloadAccepted) =&gt; {
 console.log('API called successfully. Returned data: ' + JSON.stringify(data));
 })
 .catch((error: any) =&gt; console.error(error));
</code></pre>
この関数をDeployするには
<pre><code class="language-bash hljs">gcloud functions deploy $FUNCTION_NAME \
 --project=$GCP_PROJECT \
 --region=$REGION \
 --runtime nodejs14 \
 --entry-point=index \
 --gen2 \
 --source ./build \
 --trigger-topic=$TOPIC_NAME \
 --trigger-service-account=$TRIGGER_SERVICE_ACCOUNT_ADRESS \
 --service-account=$SERVICE_ACCOUNT_ADRESS \
 --retry \
 --timeout=180s \
 --set-env-vars=DATADOG_APP_KEY=$DATADOG_APP_KEY,DATADOG_API_KEY=$DATADOG_API_KEY
</code></pre>
というgcloudコマンドだけでdeployできます。
さらに、このFunction を Cloud Scheduler で定期的に実行します。 
ただし、あまりにも頻度が高いとslotに負荷をかけるため、10分おきに実行しています。
結果として、送信したMetricsを用いて以下のようなDashBoardを構築しました。 
<img src="https://ik.imagekit.io/plaid/61cdd26c-0f08-4fbd-b796-63bbce5c8612/datadog_top_v2.png" alt="datadog_top_v2.png">
これにより、どのJobがどの時間に大きなSlotを消費しているのかが、一目瞭然になります。 
消費Slotの多い上位のJobをターゲットにして、それぞれ個別に対策を行います。
<h2 id="② AutoScaleの導入によるSlotBaseLineの最適化">② AutoScaleの導入によるSlotBaseLineの最適化</h2>
BigQueryのコスト削減のためには、<a href="https://cloud.google.com/bigquery/docs/slots-autoscaling-intro?hl=ja" target="_blank" rel="noreferrer">AutoScale</a> を利用することも有効です。AutoScaleは、BigQueryの利用状況に応じて、自動的にリソースをスケールアップまたはスケールダウンする機能です。AutoScaleを利用することで、余剰していたBaseLineを最適化し、コストを削減することができます。
弊社はReservationという単位でProductごとに個別のSlotを持っているため、個別に利用状況を判断し調整します。 
例えば Datahub というProductではdatahub独自のSlotをReservationとして占有しており、サービス提供に支障がないように余裕をもったBaseLineを敷いていました。 
しかし、休日深夜帯などほとんど利用されていないような時間帯でも、BaseLine分の料金は課金されてしまうことになります。 
これを最適化するために導入されたのが、AutoScaleです。
そうなるとBaseLineを全部0にしてしまえばいい感じに最適化されるのでは？と疑問に思うかもしれませんが、一方でBaseLineにはCommitという概念があり、<a href="https://cloud.google.com/bigquery/pricing?hl=ja#flat-rate_pricing" target="_blank" rel="noreferrer">年間契約</a>することにより割引価格が適用できます。
そのため、固定分を割引価格で、変動分を定価で、のような使い方をすることができるため、一概に0にするのが正解とはいえません。
これらを考慮した上で、弊社で最適化した結果が以下の通りです。 
<img src="https://ik.imagekit.io/plaid/daa0289b-1fe7-4024-9dca-440bebed292d/slot_autoscaling.png" alt="slot_autoscaling.png">
いかがでしょうか。利用量に応じて動的にSlotがAutoScaleしていることがわかると思います。 
ただし、BaseLineと違って変動分にどれくらいのコストがかかっているのか？がなかなかConsoleだけだとわかりにくいので、こちらもDatadogに送信すると以下のような画面になります。 
これによりBaseLine+AutoScaleの料金の見積もりと監視が容易になります。 
<img src="https://ik.imagekit.io/plaid/a741e551-2926-4af0-a77b-1edc77c04a38/datadog_autoscale.png" alt="datadog_autoscale.png">
ただし、AutoScaleも完璧ではなく、Scaleup/downでOverHeadの時間はかかります。 
弊社の要件であると、「軽いクエリで必ず1,2sec以内で結果を返して欲しい」という要件も一部存在します。その場合は、interactive用のReservationを別途確保して、BaseLineを100や200で設定することで、この問題を解決することが可能です。
最も大事なのは、Productごとに要件を把握し適切にReservationを配分することであり、前回同様変わらないです。Product Teamとしてはもちろん余剰にSlotを使えた方が楽なのですが、会社全体としての予算があり、あくまでその配分の中で最適化するというのを理解してもらうことが大事です。
<h2 id="最終的なコストは...?">最終的なコストは...?</h2>
全体で約20%のコスト削減を実現 することができました。 
年間にして***円(非公開)の効果で、弊社の基盤としてはかなりのインパクトがありました。
以上のように 
① Monitoringの整備によるJobごとのSlot監視と削減 
② AutoScaleの導入によるSlotBaseLineの最適化全体
でBigQuery全体として、約20%のコスト削減を実現しました。
BigQueryのUpdateは特に早く、コストの最適方法はその都度変化するため、継続的に検討を行う必要があります。今回の方法は主にEditionsへの移行に伴う最適化でしたが、今後のUpdateに伴ってより良い方法が出る可能性があります。ご自身の環境へ適用する場合は、最新のドキュメントを参照の上、ご検討下さい。
CX（顧客体験）プラットフォーム「<a href="https://karte.io/" target="_blank" rel="noreferrer">KARTE</a>」を運営する<a href="https://plaid.co.jp/" target="_blank" rel="noreferrer">PLAID</a>では、データを活用しプロダクトを改善していくエンジニアを募集しています。 
詳しくは、<a href="https://recruit.plaid.co.jp/" target="_blank" rel="noreferrer">弊社採用ページ</a>をご覧ください。
</body></html>

BigQuery Editionsにおけるコスト最適化

BigQueryのコストの最適化への再挑戦〜BigQuery Editionsへの料金体系の移行について〜