DeNA、Googleと共同でGCP勉強会を開催しました！

<html><head></head><body><h2 id="BigQueryの監査ログは役に立つ">BigQueryの監査ログは役に立つ</h2>
PLAID Engineerの 原田(@harada_hi) です。
弊社製品KARTEでは行動データという大量データの集計においてBigQueryを多用しています。 
今回はそのBigQueryのパフォーマンス確認やトラブル時の調査に利用しているBigQueryの監査ログの話を紹介します。
<h3 id="集計を実施する上で確認したい内容">集計を実施する上で確認したい内容</h3>
BigQueryはSparkやHadoopを組み合わせて実施していた集計処理を賄うことが可能です。 
前述のとおりPLAIDにおいてもユーザーの行動データをBigQueryに収集し、クエリを実行して集計を実施しています。 
BigQueryで集計を行う上で次のような点を確認したい場面があります。
<ol>
<li>各集計クエリの実行時間</li>
<li>時間帯ごとの集計クエリの同時実行数</li>
<li>各集計クエリで使用しているslot</li>
<li>動的に生成される集計クエリそのもの</li>
</ol>
1を確認したい理由はパフォーマンス確認の一つとしてです。 
2,3を確認したい理由は、BigQueryの制限によるものです。
Bigquery &gt; Documentation &gt; Quotas &amp; limits 
<a href="https://cloud.google.com/bigquery/quotas" target="_blank" rel="noreferrer">https://cloud.google.com/bigquery/quotas</a>
この中での 
<code>Concurrent rate limit for interactive queries — 100 concurrent queries</code> 
が理由です。 
この制限に掛かっている場合、新規のインタラクティブ クエリを実行しようとすると強制的にエラーとなります。 
2は同時実行の数そもそもを確認して、現状で危険な時間帯はないかを確認するために必要です。 
3はこの制限にかかる潜在的なリスクを確認するためのものです。 
slotを使いすぎている集計が存在するとその集計がボトルネックとなり他の集計が終わらず同時実行数が増えてしまうことがあります。
4を確認したい理由は、何らかのバグを確認した場合やパフォーマンスが悪いことを確認した場合に該当のクエリがわからないと対策が打てないためです。 
変更のないクエリであれば問題になりませんが、動的にクエリを変更・生成している場合は重要です。
<h3 id="BigQueryのデータアクセスログ">BigQueryのデータアクセスログ</h3>
上記で確認したい点はシステムからクエリを実行する以上、自前のログに書き込むことで確認は可能です。 
しかしBigQueryの監査ログには自動的に上記の確認したい情報が入るので自前で用意する必要がありません。
BigQueryを含めたGoogle Cloud サービスの監査ログ(Cloud Audit Logging)の詳細は次の公式ドキュメントが存在しています。
Stackdriver &gt; Documentation &gt; Logging &gt; Cloud Audit Logging 
<a href="https://cloud.google.com/logging/docs/audit" target="_blank" rel="noreferrer">https://cloud.google.com/logging/docs/audit</a>
この監査ログの中には以下の3つが存在しています。
<ul>
<li>管理アクティビティログ</li>
<li>システムイベントログ</li>
<li>データアクセスログ</li>
</ul>
このうちの<code>データアクセスログ</code>が上で記載している確認したいことをカバーしています。 
データアクセスログは各CRUD処理が全てログとして残ります。 
こうした特徴から出力する対象のサービスは任意で指定する必要がありますが、 
BigQueryに関しては標準で出力されています。
Stackdriver &gt; Documentation &gt; Logging &gt; Configuring Data Access Logs 
<a href="https://cloud.google.com/logging/docs/audit/configure-data-access" target="_blank" rel="noreferrer">https://cloud.google.com/logging/docs/audit/configure-data-access</a>
実際の確認方法は以下の2つです。
<ol>
<li>Google Cloudが提供しているログビューワーからの確認</li>
<li>監査ログをGoogle Cloudサービスにエクスポートして確認</li>
</ol>
推奨は2番目の<code>監査ログをGoogle Cloudサービスにエクスポートして確認</code>で、BigQueryに監査ログをエクスポートして確認することです。 
クエリで確認する情報を絞ることで調査を容易にすることにも繋げられる他、調査クエリを残しておくことで誰でも調査しやすい環境を作ることができます。 
ただしBigQueryで監査ログを確認していくには次の対応が必要となります。
BigQuery &gt; Overview &gt; Stackdriver Logging exports 
<a href="https://cloud.google.com/bigquery/docs/reference/auditlogs/#stackdriver-logging-exports" target="_blank" rel="noreferrer">https://cloud.google.com/bigquery/docs/reference/auditlogs/#stackdriver-logging-exports</a>
1のログビューワーでの確認は監査ログのデータ構成を確認するのに利用するのにおすすめです。 
これはBigQueryで監査ログを確認するにあたって監査ログのデータ構成を理解する必要があるためです。 
ログビューワーは次のURLで移動できます。 
<a href="https://console.cloud.google.com/logs/viewer" target="_blank" rel="noreferrer">https://console.cloud.google.com/logs/viewer</a>
<h3 id="実際に活用しているクエリの紹介">実際に活用しているクエリの紹介</h3>
実際弊社で利用しているクエリを簡単にしたものを2つほど紹介します。 
{{}}で囲んでいるところを変更していただければ使えるようにしています。
<h4 id="slotを消費しているクエリを確認する">slotを消費しているクエリを確認する</h4>
<pre><code class="language-sql hljs">WITH
{{project_id}} AS
(
 SELECT 
 '{{project_id}}' project_name,
 protopayload_auditlog.servicedata_v1_bigquery.*,
 protopayload_auditlog.serviceName as serviceName,
 protopayload_auditlog.methodName as methodName,
 protopayload_auditlog.resourceName as resourceName,
 timestamp
 FROM
 `{{project_id}}.{{dataset}}.cloudaudit_googleapis_com_data_access_{{timestamp}}`
),

{{project_id}}_COMPLETED_EVENT AS
(
 SELECT
 project_name,
 jobCompletedEvent.job.jobConfiguration.query.query as query,
 jobCompletedEvent.job.jobStatistics.createTime as jobCreateTime,
 jobCompletedEvent.job.jobStatistics.startTime as jobStartTime,
 jobCompletedEvent.job.jobStatistics.endTime as jobEndTime,
 jobCompletedEvent.job.jobConfiguration.query.queryPriority queryPriority,
 REGEXP_EXTRACT(jobCompletedEvent.job.jobConfiguration.query.query, "\"user\":\"([^\"]+)\"") mode_user,
 jobCompletedEvent.job.jobStatistics.totalSlotMs as totalSlotMs,
 timestamp
 FROM
 {{project_id}}
 WHERE
 methodName = 'jobservice.jobcompleted'
)

SELECT
 FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', timestamp, 'Asia/Tokyo') timestamp_jst,
 TIMESTAMP_DIFF(jobEndTime, jobStartTime, MILLISECOND) / (1000 * 60) act_span,
 TIMESTAMP_DIFF(jobEndTime, jobCreateTime, MILLISECOND) / (1000 * 60) job_span,
 project_name,
 queryPriority,
 query,
 totalSlotMs,
 totalSlotMs / (1000 * 60 * 60) totalSlotHour,
 totalSlotMs / (1000 * 60 * 60 * 24) totalSlotDay
FROM
 {{project_id}}_COMPLETED_EVENT
ORDER BY totalSlotMs DESC
</code></pre>
slotを消費した順にクエリを並べています。 
このクエリを元にして同時実行数が多くなっている時間帯を確認するなども行なっています。
<h4 id="エラーを発生させたクエリを確認する">エラーを発生させたクエリを確認する</h4>
<pre><code class="language-sql hljs">WITH
{{project_id}} AS
(
 SELECT 
 '{{project_id}}' project_name,
 timestamp,
 severity,
 protopayload_auditlog.servicedata_v1_bigquery.*,
 protopayload_auditlog.serviceName as serviceName,
 protopayload_auditlog.methodName as methodName,
 protopayload_auditlog.resourceName as resourceName
 FROM
 `{{project_id}}.{{dataset}}.cloudaudit_googleapis_com_data_access_{{timestamp}}`
),

{{project_id}}_COMPLETED_EVENT AS
(
 SELECT
 project_name,
 timestamp,
 severity,
 jobCompletedEvent.job.jobConfiguration.query.query as query,
 jobCompletedEvent.job.jobConfiguration.query.queryPriority queryPriority,
 jobCompletedEvent.job.jobStatus.error.message error_message,
 jobCompletedEvent.job.jobStatistics.totalSlotMs as totalSlotMs
 FROM
 {{project_id}}
 WHERE
 methodName = 'jobservice.jobcompleted'
 AND severity = "ERROR"
)

SELECT
 project_name,
 timestamp,
 severity,
 queryPriority,
 error_message,
 query,
 totalSlotMs
FROM
 {{project_id}}_COMPLETED_EVENT 
ORDER BY timestamp DESC
</code></pre>
ユーザーの設定によって動的にクエリが生成される場合は意図していないエラーが発生することがあります。 
用意しておくことで調査をスムーズに進めることができています。
<h3 id="まとめ">まとめ</h3>
BigQueryでは自動的に出力されている監査ログでも多くのことを調べることができるという紹介でした。 
実際に確認いただくと上で紹介した以外のことも確認できるということが見えてくるかと思います。 
BigQueryを利用したサービスを開発する上での参考になれば幸いです。
<h3 id="最後に">最後に</h3>
CX（顧客体験）プラットフォーム「<a href="https://karte.io/" target="_blank" rel="noreferrer">KARTE</a>」を運営するプレイドでは、KARTEを使ってこんなアプリケーションが作りたい！ KARTE自体の開発に興味がある！というエンジニア（インターンも！）を募集しています。 
詳しくは<a href="https://plaid.co.jp/recruit/engineer.html" target="_blank" rel="noreferrer">弊社採用ページ</a>または<a href="https://www.wantedly.com/companies/plaid/projects" target="_blank" rel="noreferrer">Wantedly</a>をご覧ください。 もしくはお気軽に、下記の「話を聞きに行きたい」ボタンを押してください！
<div class="wantedly-visit-button" data-visit-button-id="lyE_IgY4VF5ukBo_EUKZLg" data-width="300" data-height="60"></div>
<script>
 (function(d, s, id) {
 var js, fjs = d.getElementsByTagName(s)[0];
 if (d.getElementById(id)) return;
 js = d.createElement(s); js.id = id;
 js.src = "https://platform.wantedly.com/visit_buttons/script.js";
 fjs.parentNode.insertBefore(js, fjs);
 }(document, "script", "wantedly-visit-buttons-wjs"));
</script>
</body></html>

Google Cloudサービスが提供しているBigQuery監査ログの活用方法。
実際に弊社で利用しているクエリも併せて紹介します。