Adobe Analytics

Adobe Analytics: データフィードをGoogle BigQueryのテーブルにロードする

こんにちは、エクスチュアCTOの権です。

前回のブログの続きです。
今回は、Adobe AnalyticsのデータフィードファイルをGoogle BigQueryのテーブルにロードする方法についてです。

データフィードファイルを加工してアップロード用ファイルを作成する

データフィードファイル(.tar.gz)をGoogle Compute EngineのVMにSFTP転送したので、そのファイルをtarコマンドで展開します。

$ tar -zxvf exturedev_2017-05-17.tar.gz

tar.gzを展開すると、中身は以下のようなファイルになってます。

browser.tsv
browser_type.tsv
color_depth.tsv
column_headers.tsv
connection_type.tsv
country.tsv
event.tsv
hit_data.tsv
javascript_version.tsv
languages.tsv
operating_systems.tsv
plugins.tsv
referrer_type.tsv
resolution.tsv
search_engines.tsv

主役はhit_data.tsvです。Adobe Analyticsで収集したヒットデータは、このhit_data.tsvファイルに入ってます。
残りのファイルはルックアップ用データでして、ヒットデータに含まれるメタデータのキーデータとマッピングするためのものです。

hit_data.tsvどのくらいのデータが含まれるか見て見ましょう。

$ wc -l hit_data.tsv
1131 hit_data.tsv

1131行(件)のヒットデータが含まれます。
弊社のテストデータなのでこんなもんです。

ではカラム数がどれだけあるか見ましょう。

$ wc -w column_headers.tsv
1013 column_headers.tsv

Adobe Analyticsの全カラムをデータフィード配信したので、全部で1000カラム以上含まれてます。
そしてこれらのデータを全てBigQueryに取り込むためのテーブルを作ろうとすると中々忙しいので、今日はそこまでやりません。
hit_data.tsvから必要なカラムだけ抜き取り、そのデータを格納するためのテスト用の小さなテーブルを作って見ます。

まずは、column_headers.tsvをタブ区切りファイルから、改行区切りファイルに変換します。

$ cat column_headers.tsv | tr '\t' '\n' > column_headers-desc.txt

それからviでcolumn_headers-desc.txtファイルを開き、必要な各カラムの行数を調べます。

$ vi column_headers-desc.txt
:set nu

:set nuで行数を確認します


今回使うカラムは、下記の9カラムだけにしてみます。

30 date_time
287 exclude_hit
304 hit_source
623 post_event_list
661 post_page_url
662 post_pagename
812 post_visid_high
813 post_visid_low
994 visit_num

cutコマンドを使って、上記に該当するカラムを取り出してvisid_details.txtというファイルを作ります。

$ cut -f 30,287,304,623,661,662,812,813,994 hit_data.tsv > visid_details.txt

これでロードするファイルは出来上がりました。
続いてBigQueryの用意をします。

BigQueryのセットアップをする

BigQueryもGoogle Cloud Platformの常時無料プランで利用できます。
月間1TBクエリまででしたら、いつまでも無料です。

GCPのダッシュボードから、[BigQuery]を開いて、BigQueryの利用を開始します。

BigQueryのUIが開いたら、今回データフィードをインポートするための[データセット]と[テーブル]を作成するのですが、せっかくなのでBigQueryクライアントライブラリを使ってデータセットとテーブルを作成してみます。

BigQueryクライアントライブラリ
https://cloud.google.com/bigquery/docs/reference/libraries?hl=ja

最近個人的によく使ってるNode.jsでやってみます。
npmでライブラリをインストールしておきます。

$ npm install --save @google-cloud/bigquery

ライブラリをインストールしたら、Node.jsでAPIを実装します。

/* bqdataset.js */
var BigQuery = require('@google-cloud/bigquery');

var projectId = 'hogehoge'; //GCPプロジェクトIDに置き換える

var bigquery = BigQuery({
    projectId: projectId
});

var datasetName = 'my_adobe_datafeed';

// dataset作成
bigquery.createDataset(datasetName)
    .then((results) => {
    const dataset = results[0];
    console.log("Dataset " + dataset.id + " created.");
});

bqdataset.jsという名前で保存して、nodeで実行するとmy_adobe_datafeedデータセットが出来上がります。

$ node bqdataset.js

わざわざAPI使わなくてもBigQueryのUIから作った方が早いんですが、あえてAPIで実行します。

データセットの次は、テーブルです。
これもAPIで作って見ます。

/* bqtable.js*/
var BigQuery = require('@google-cloud/bigquery');

var projectId = 'hogehoge'; //GCPプロジェクトIDに置き換える
 
var bigquery = BigQuery({
    projectId: projectId
});

var datasetName = 'my_adobe_datafeed';
var dataset = bigquery.dataset(datasetName);

var tableId = 'online_users';

var options = {
  schema: 'date_time:timestamp,exclude_hit:integer,hit_source:integer,post_event_list,post_page_url,post_pagename,post_visid_high:integer,post_visid_low:integer,visit_num:integer'
};

dataset.createTable(tableId, options).then(function(data) {
    var apiResponse = data[1];
    console.log(apiResponse);
});

これもBigQueryUIから作った方が早いかも知れませんが、いずれ1000カラム以上あるテーブルを作るとしたらAPIの方が効率が良いので、覚えておいて損はしません。
bqtable.jsを実行すると、online_usersテーブルが出来上がります。

$ node bqtable.js

online_usersテーブル

BigQueryにデータをロードする

さて、テーブルが用意できたので、今度はさきほど作ったvisid_details.txtをonline_usersテーブルにロードします。

これも例のごとく、わざわざAPIで実行してみます。

/* bqload.js */
var BigQuery = require('@google-cloud/bigquery');

var projectId = 'hogehoge'; //GCPプロジェクトIDに置き換える

var bigquery = BigQuery({
    projectId: projectId
});

var datasetName = 'my_adobe_datafeed';
var dataset = bigquery.dataset(datasetName);

var table = dataset.table('online_users');

var metadata = {
    fieldDelimiter: "\t"
};

var fs = require('fs');

fs.createReadStream('visid_details.txt')
  .pipe(table.createWriteStream(metadata))
  .on('complete', function(job) {
    console.log("load completed.");
});

bqload.jsを実行すると、visid_deitals.txtのデータがonline_usersテーブルにロードされます。

$ node bqload.js

データがテーブルに入ったかどうか見てみましょう。
BigQueryのUIから[COMPOSE QUERY]を開き、以下のクエリを実行してみます。

SELECT * FROM [hogehoge:my_adobe_datafeed.online_users] LIMIT 1000

※hogehogeは、GCPプロジェクトIDに置き換えてください。

正しくデータがロードされてれば、結果が返ってきます。

今回は、データフィードファイルを展開して、BigQueryテーブルにロードする方法について説明しました。
次回は、BigQuery上でページビュー数や訪問者数などを抽出するためのクエリについて書いてみようと思います。

続き: Adobe Analytics: データフィードをBigQueryで集計する

関連記事

  1. Adobe Analytics

    Adobe Analytics: Webサイト内で発生したJavascriptエラーの内容をAdob…

    こんにちは、エクスチュアCTOの権です。今日は、Webサイト内で発…

  2. Adobe Analytics

    Adobe Analytics: RSIDを間違えてしまったデータを正しいRSに入れ直す

    こんにちは、エクスチュアCTOの権です。今回はAdobe An…

  3. Adobe Analytics

    Adobe Analyticsと「どこどこJP」のAPIを連携する

    こんにちは、エクスチュアCTOの権です。弊社Webサイトでは「どこ…

  4. Cloud Dataproc

    BigQueryテーブルをAVRO形式でエクスポートしてHiveで扱う

    こんにちは、エクスチュアCTOの権です。今日も普段あまり使わな…

  5. Google Cloud Platform

    Google Compute Engine のLinuxVMにVNC接続する

    こんにちは、エクスチュアCTOの権です。今回は、GCEのLin…

  6. Firebase Analytics

    Firebase Analyticsの新しいBigQueryスキーマを試す

    こんにちは、エクチュアの権泳東(コン・ヨンドン)です。「お名前なん…

最近の記事

  1. Google Compute EngineのUbuntu V…
  2. Firebase Analyticsの新しいBigQuery…
  3. Adobe Cloud Platform Auditor (…
  4. Google Analytics Standardのデータを…
  5. Google Tag Manager上でGoogle Ana…
  1. プログラミング

    Node.jsでCSVファイル内のダブルクオートで囲まれたカラム内のカンマを除去…
  2. Adobe Analytics

    Adobe Analytics: ランドスケイプの「企業ログ解析データベースAP…
  3. Mouseflow

    Mouseflow:レコーディングを確認する前に
  4. Adobe Analytics

    Adobe Analytics: データフィードをGoogle BigQuery…
  5. Adobe Analytics

    Adobe Analytics:IF関数を使用した計算指標
PAGE TOP