Adobe Analytics: DatafeedをGoogle BigQueryにロード(2019年9月版)

こんにちは、エクスチュアの權泳東(権泳東/コン・ヨンドン)です。

2年前に Adobe Analytics: データフィードをGoogle BigQueryのテーブルにロードするという記事を書いたのですが、さすがに情報が古くなりました。
というわけで「今やるならこの方法」、というやり方です。

DatafeedをSFTPでGCEに送る

これまた2年前に書いた Adobe Analytics: データフィードをGoogle Compute EngineのLinuxインスタンスにSFTP転送するという記事で、CentO7S7のSFTPサーバーを立てる方法を書きましたが、その後私がUbuntu派に宗旨替えしたので簡単に書き直します。

1. まずはGCEでUbuntu 18.04LTSのインスタンス立てます。

tar.gzファイルを解凍するので、Datafeedのデータサイズに応じてマシンタイプやディスクサイズを適宜選んで下さい。

2. SSHでUbuntuにログインして、authorized_keys にAdobeの公開鍵を登録します。

Adobeの公開鍵は2019年9月現在、ここからダウンロード出来ます。
実は全クライアントで共通の公開鍵使ってます。

UbuntuにSSHログインしてから、こうやればOKです。

wget https://aauicdnva7.azureedge.net/deploy/builds/profile201908-31635.Ifecdd6.M0-23/public_keys/private_sftp_key-2013-10.pub
echo &quot;#adobe pubkey&quot; &gt;&gt; ~/.ssh/authorized_keys
cat private_sftp_key-2013-10.pub &gt;&gt; ~/.ssh/authorized_keys

3. Adobe管理画面でDatafeed配信設定をする

管理画面で以下のフィード配信設定を行います。

■フィード情報

データフィード名: 任意の名前をつけます。
レポートスイート: データフィードを配信する対象RSを選択
メールアドレス: 配信完了や失敗の通知メールを受けとるメアドを入力
フィード間隔： 毎日
遅延処理: 遅延なし
開始日と終了日: データフィードの配信開始日を選ぶ。最大で3年前のデータから配信出来ます。
連続フィード: チェック入れる

■宛先

タイプ: SFTP
ホスト: GCE VMのIPアドレス
パス: データフィードをアップロードするディレクトリ
ユーザー名: SFTPアップロード先のLinuxユーザー名

■データ列の定義
エスケープ文字を削除: チェック入れる
列テンプレート: All Columns Premium (August 2018)
圧縮形式: gzip
パッケージタイプ: 単一のファイル
マニフェスト: ファイルなし

「列テンプレート」は All Columns Premium (August 2018) を使います。
1065個のカラムが含まれるバージョンです。

All Columns Premium (August 2018)

なぜ列テンプレートを指定するのかというと、AdobeAnalyticsのカラムは新機能がリリースされると増える事があります。
複数レポートスイートを扱っている場合に、あとから作成したレポートスイートと既存レポートスイートでカラムがずれてしまうので、あえてカラム数を限定します。

BigQueryにテーブルを作成する

hit_dataテーブル作成用bashスクリプトを書いたので、githubに置いておきます。
下記の設定だけ書き換えて実行すれば、All Columns Premium (August 2018) で指定した1065個のカラムを持ったテーブルが出来上がります。

project=myproject
dataset=adobe_datafeed
table=hit_data

bq mkコマンドでスキーマ定義をする際に、–time_partitioning_fieldパラメータで hit_time_gmt を指定して日別分割パーティションを作ってますが、もし他のカラム(例えばdate_time)を使いたければ直してください。
パーティションテーブルの作成については、以前この記事で解説したとおりです。

BiQueryにデータをロードする

Adobeからデータフィードのtar.gzファイルが届いたら、それをtarで展開してBigQueryにロードします。
これも展開してデータをロードするためのbashスクリプトを書いたのでgithubに置いておきます。

これも下記の設定を変えて実行すれば、hit_data.tsvならびに関連するルックアップ用のサポートファイルをアップロードしてくれます。

bucket=mybucket
project=myproject
dataset=adobe_datafeed
table=hit_data

ここで言うbucketは、BiqQueryにロードが済んだtar.gzファイルのバックアップ先のCloud Storageバケットです。
バケットは別途用意しておいてください。

複数のtar.gzファイルがあっても順番に展開して、hit_data.tsvをBigQueryにロードするようにループ処理をいれてます。
あと、ルックアップ用のサポートファイルは最新版だけあればいいので、最後にロードしてます。

サポートファイルのうち、plugins.tsvはSiteCatalystとの互換性とのために含まれてますが、今はもう使ってないので無視してます。

以上でAdobe DatafeedをBigQueryにロードする事が簡単に出来ます。
ロードしたあとは、これまた以前書いた Adobe Analytics + Google BigQueryでよく使うSQL例 6選という記事を参考にしたり、Adobeのヘルプを読みながらBigQuery上でクエリをぶん回してください。

弊社では、Google Cloud認定データエンジニア資格とAdobe認定エキスパート資格を保持した技術者達によるデータ分析基盤構築業務を承っております。
お問い合わせはこちらからどうぞ。