KARTE DatahubとAmazon S3連携をステップ・バイ・ステップで手順を確認する

<html><head></head><body><h1 id="このブログについて">このブログについて</h1>
本ブログ最初の記事ということで、簡単にブログ開設の背景を記載させていただきます。 
本題を読みたい方は、そっと下にスクロールして次項に進んでください。
私はカスタマーエンジニアとして、<a href="https://karte.io/" target="_blank" rel="noreferrer">KARTE</a>活用の技術的なサポートを行っています。 
（カスタマーエンジニアがどんな職種か気になった方は、ぜひ<a href="https://blog.plaid.co.jp/n/n6d87538d003f" target="_blank" rel="noreferrer">こちら</a>をご一読ください。）
日々の業務の中で、
<ul>
<li>KARTEを深く利用することで、何ができるのか知ってほしい</li>
<li>サポートサイトの幅には収まらない、もっと踏み込んだ活用方法（ソリューション）を広く伝えたい</li>
</ul>
という思いを持つようになりました。 
そして、それは他のカスタマーエンジニアも同様でした。
KARTEは汎用性が高いプロダクトである反面、使いこなすことが難しいプロダクトでもあります。
このブログを通して、KARTEユーザーの創造性を刺激し、ソリューション実現の手助けとなるようなコンテンツを発信していきたいと考えております。
<h1 id="この記事について">この記事について</h1>
さて、本記事ではDatahubクエリについてのTipsをご紹介します。 
KARTEの汎用性を高める機能であるDatahubの活用により、KARTE内外のデータを利用することで施策の幅を広げたり、柔軟なデータ分析を実施したりすることが可能となります。 
Datahubクエリは、基本的にはBigQueryの標準SQLを踏襲しており、自由度が高い機能となっています。
今回は、その中でも難易度の高い要素の一つとなる、タイムゾーンを考慮したDatahubクエリの記述方法についてのご紹介です。 
具体的には、標準ではUTCでの絞り込みとなるPeriod型のパラメータを使って、JST（日本標準時）でデータを絞り込む方法について記載します。
<h1 id="仕様の整理">仕様の整理</h1>
まずは、DatahubでPeriod型のパラメータを指定した際の仕様について整理します。
今回は、クエリコレクションの「UU、PV、セッション数の算出」を例にみていきます。 
クエリは下記のようになっています。
<pre><code class="language-SQL hljs">SELECT
 DATE(sync_date) AS date
 , COUNT(DISTINCT user_id) AS uu
 , COUNT(DISTINCT session_id) AS session
 , COUNT(*) AS pv
FROM
 {% karte_event period[0], period[1] %}
WHERE
 event_name = 'view'
GROUP BY date
ORDER BY date DESC
</code></pre>
パラメータを下記のように設定し、クエリを実行します。 
その後、「変換後クエリ」から実際に実行されたクエリについて確認してみます。
<img src="https://ik.imagekit.io/plaid/c870cd60-addb-47e2-a104-e74c7bc989c6/%E3%82%B9%E3%82%AF%E3%83%AA%E3%83%BC%E3%83%B3%E3%82%B7%E3%83%A7%E3%83%83%E3%83%882023-02-2719.57.40.png" alt="スクリーンショット2023-02-2719.57.40.png">
変換後のクエリは下記のようになっています。
<pre><code class="language-SQL hljs">SELECT
 DATE(sync_date) AS date
 , COUNT(DISTINCT user_id) AS uu
 , COUNT(DISTINCT session_id) AS session
 , COUNT(*) AS pv
FROM
 (SELECT sync_date as sync_date, user_id as user_id, visitor_id as visitor_id, session_id as session_id, event_name as event_name, values as values, shorten_segments as shorten_segments, scores as scores, keys.is_visitor as is_visitor, source as source, event_hash as event_hash, user_fields as user_fields, dimensions as dimensions, processor as processor, keys.live_session_id as live_session_id FROM `karte-data.karte_stream_{{API_KEY}}.krt_pockyevent_v1_*` WHERE _TABLE_SUFFIX BETWEEN '20230220' AND '20230222')
WHERE
 event_name = 'view'
GROUP BY date
ORDER BY date DESC
</code></pre>
少し見づらいですが、ポイントは下記2点です。
<ul>
<li><code>karte_event</code> の実態は <code>karte-data.karte_stream_{{API_KEY}}.krt_pockyevent_v1_*</code> の特定カラムを抜き出したviewである</li>
<li><code>karte-data.karte_stream_{{API_KEY}}.krt_pockyevent_v1_*</code> はUTCベースの日付でパーティションされており、Period型のパラメータでパーティションされたテーブルを絞り込むことで期間指定を実現している</li>
</ul>
<h1 id="JSTへの換算">JSTへの換算</h1>
DatahubではUTCでデータが格納されているので、上述の例で抽出対象の期間をJSTからUTCに変換してみます。
<div class="TableScroll"><table>
<thead>
<tr>
<th></th>
<th>JST</th>
<th>UTC</th>
</tr>
</thead>
<tbody>
<tr>
<td>起点となる時間</td>
<td>2023-02-20 00:00:00</td>
<td>2023-02-19 15:00:00</td>
</tr>
<tr>
<td>終点となる時間</td>
<td>2023-02-23 00:00:00</td>
<td>2023-02-22 15:00:00</td>
</tr>
</tbody>
</table></div>
前項より、JSTによる絞り込みには下記2つの工程が必要となります。
<ol>
<li>パーティションされたテーブル全体が抽出対象ではなくなるため、イベント送信日時（ <code>sync_date</code> ）ベースでの絞り込みを行う</li>
<li>UTCベースで2023-02-19からデータが必要になるため、一日分多くパーティションされたテーブルを取得する</li>
</ol>
<h2 id="Step1 抽出対象のイベント送信日時を算出する">Step1 抽出対象のイベント送信日時を算出する</h2>
今回の要件を整理すると、イベント送信日時の絞り込みのゴールは下記のようになります。 
<code>2023-02-20 00:00:00 JST &lt;= sync_date &lt; 2023-02-24 00:00:00 JST</code>
Period型のパラメータは、YYYYMMDD型の文字列の配列が実態となっています。 
つまり今回の例の場合、period[0]は20230220という文字列であり、period[1]は20230223という文字列になります。
ここからSQLを利用して、抽出対象となる時間を算出する方法を考えます。
<ol>
<li>period[0]をJSTのtimestampとして解釈、UTCに変換し、これを絞り込み期間の起点とする</li>
<li>period[1]のJSTのtimestampとして解釈、UTCに変換し、そこに一日加算したものを絞り込み期間の終点とする</li>
</ol>
<pre><code class="language-SQL hljs">WITH jst_timestamp AS (
 SELECT
 PARSE_TIMESTAMP('%Y%m%d', {{period[0]}}, 'Asia/Tokyo') AS start_timestamp
 , TIMESTAMP_ADD( PARSE_TIMESTAMP('%Y%m%d', {{period[1]}}, 'Asia/Tokyo'), INTERVAL 1 DAY) AS end_timestamp
)

SELECT * FROM jst_timestamp
</code></pre>
実行結果を見ると、意図した値が算出できていることがわかります。
<img src="https://ik.imagekit.io/plaid/650f45ca-4298-4ee5-b304-71f15cea3a2f/%E3%82%B9%E3%82%AF%E3%83%AA%E3%83%BC%E3%83%B3%E3%82%B7%E3%83%A7%E3%83%83%E3%83%882023-02-2720.53.54.png" alt="スクリーンショット2023-02-2720.53.54.png">
<h2 id="Step2 抽出対象の日付を算出する">Step2 抽出対象の日付を算出する</h2>
このステップのゴールは、パーティションテーブルの絞り込みに利用する日付文字列を算出することです。 
例の場合、「20230219」「20230222」の2つの文字列が必要になります。
こちらは、Step1の結果をYYYYMMDD型の文字列にCASTすることで取得できます。
<pre><code class="language-SQL hljs">WITH jst_timestamp AS (
 SELECT
 PARSE_TIMESTAMP('%Y%m%d', {{period[0]}}, 'Asia/Tokyo') AS start_timestamp
 , TIMESTAMP_ADD( PARSE_TIMESTAMP('%Y%m%d', {{period[1]}}, 'Asia/Tokyo'), INTERVAL 1 DAY) AS end_timestamp
)
, jst_period AS (
 SELECT
 CAST(start_timestamp AS STRING FORMAT 'YYYYMMDD') AS start_period
 , CAST(end_timestamp AS STRING FORMAT 'YYYYMMDD') AS end_period
 FROM
 jst_timestamp
)

SELECT * FROM jst_period
</code></pre>
実行結果を確認すると、こちらも意図通りの結果が得られていることがわかります。
<img src="https://ik.imagekit.io/plaid/f2c7cde4-1f9c-4249-bdfd-ab7d0c9fbab6/%E3%82%B9%E3%82%AF%E3%83%AA%E3%83%BC%E3%83%B3%E3%82%B7%E3%83%A7%E3%83%83%E3%83%882023-02-2721.33.48.png" alt="スクリーンショット2023-02-2721.33.48.png">
<h1 id="最終的なクエリ">最終的なクエリ</h1>
最終的には、下記のようなクエリでJST換算でデータを絞り込むことができました。
<pre><code class="language-SQL hljs">WITH jst_timestamp AS (
 SELECT
 PARSE_TIMESTAMP('%Y%m%d', {{period[0]}}, 'Asia/Tokyo') AS start_timestamp
 , TIMESTAMP_ADD( PARSE_TIMESTAMP('%Y%m%d', {{period[1]}}, 'Asia/Tokyo'), INTERVAL 1 DAY) AS end_timestamp
)
, jst_period AS (
 SELECT
 CAST(start_timestamp AS STRING FORMAT 'YYYYMMDD') AS start_period
 , CAST(end_timestamp AS STRING FORMAT 'YYYYMMDD') AS end_period
 FROM
 jst_timestamp
)
SELECT
 DATE(sync_date) AS date
 , COUNT(DISTINCT user_id) AS uu
 , COUNT(DISTINCT session_id) AS session
 , COUNT(*) AS pv
FROM
 jst_timestamp
 , jst_period
 , {% karte_event_all %} AS t
WHERE
 event_name = 'view'
 AND t.TABLE_SUFFIX BETWEEN start_period AND end_period
 AND sync_date &gt;= start_timestamp
 AND sync_date &lt; end_timestamp
GROUP BY date
ORDER BY date DESC
</code></pre>
いかがでしたでしょうか。 
今回ご紹介した内容が少しでもお役に立てば幸いです。
本ブログではこのような小ネタから、もっと本格的に作り込まれたソリューションまで、さまざまなコンテンツを発信していく予定ですので、定期的にチェックしてみてください！
</body></html>

DatahubのPeriod型パラメータをJST換算してデータを絞り込む