Firebase AnalyticsのデータをフラットなCSVに変換するETL処理

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

今日はFirebase Analyticsのデータについてです。

Firebase AnalyticsをBigQueryエクスポートすると、user_dimとevent_dimの2列だけのスキーマになりますよね。
Google Analyticsのデータもそうですが、JSONのようなネストになったレコードで構成されてます。

これらのネストになったデータを、サードパーティや自前のデータウェアハウスやデータベースにインポートしようとするなら、FLATTEN関数やUNNEST関数をつかってフラットな1行に展開して、つまりCSVデータに変換するようなETLを挟むと扱いやすくなります。

参考になるのは公式ブログですね。
BigQuery 活用術: UNNEST 関数
標準SQLのUNNEST関数を使う例。

あとは、レガシーSQLのFLATTEN関数を使う例もこちらのブログに載ってます。
Using BigQuery and Firebase Analytics to understand your mobile app

しかし私はどうもSQLじゃなくて他のプログラムで片付けるのが好みなので、Compute Engine上でNode.jsを使ってフラットなCSVデータに変換してみました。

BigQueryからJSON形式でCloud Storageにエクスポートする

まずはbqコマンドを使ってJSON型で抽出します。

$ bq extract --destination_format NEWLINE_DELIMITED_JSON foobar:firebasetest_ANDROID.app_events_20170702 gs://hoge/firebase/test20170702.json

Cloud StorageからCompute Engineにコピーする

次に、gsutilコマンドを使ってcompute engineのインスタンスにコピーします。

$ gsutil cp gs://hoge/firebase/test20170702.json ./

ネストされたJSONをフラットなCSVに変換

Node.jsで簡単なコードを書いて実行します。
JSONファイルをReadStreamで読み込みながら、ネストになったJSONレコードをフラットに1行にして、WriteStreamに書き出して行きます。

var fs = require("fs");
var rs = fs.createReadStream("test20170702.json");
var readline = require("readline");
var rl = readline.createInterface(rs, {});
var of = "test20170702.csv";
var ws = fs.createWriteStream(of, "utf-8");

rl.on("line", function(json) {
    var data = JSON.parse(json);

    //user_dimの展開
    var first_open_timestamp_micros = data.user_dim.first_open_timestamp_micros ? data.user_dim.first_open_timestamp_micros : "";
    var device_category = data.user_dim.device_info.device_category;
    var mobile_brand_name = data.user_dim.device_info.mobile_brand_name;
    var mobile_model_name = data.user_dim.device_info.mobile_model_name;
    var mobile_marketing_name = data.user_dim.device_info.mobile_marketing_name;
    var device_model = data.user_dim.device_info.device_model;
    var platform_version = data.user_dim.device_info.platform_version;
    var resettable_device_id = data.user_dim.device_info.resettable_device_id;
    var user_default_language = data.user_dim.device_info.user_default_language;
    var limited_ad_tracking = data.user_dim.device_info.limited_ad_tracking;
    var continent = data.user_dim.geo_info.continent;
    var country = data.user_dim.geo_info.country;
    var region = data.user_dim.geo_info.region;
    var city = data.user_dim.geo_info.city;
    var app_version = data.user_dim.app_info.app_version;
    var app_instance_id = data.user_dim.app_info.app_instance_id;
    var app_store = data.user_dim.app_info.app_store;
    var app_platform = data.user_dim.app_info.app_platform;
    var app_id = data.user_dim.app_info.app_id;
    var bundle_sequence_id = data.user_dim.bundle_info.bundle_sequence_id;
    
    //event_dimの展開
    for (var i = 0; i < data.event_dim.length; i++) {
        var date = data.event_dim[i].date;
        var event_name = data.event_dim[i].name;
        var timestamp_micros = data.event_dim[i].timestamp_micros;
        var firebase_screen = "";
        var firebase_screen_class = "";
        var engagement_time_msec = "";
        for (var j = 0; j < data.event_dim[i].params.length; j++) {
            if (data.event_dim[i].params[j].key == "firebase_screen") {
                firebase_screen = data.event_dim[i].params[j].value.string_value;
            } else if (data.event_dim[i].params[j].key == "firebase_screen_class") {
                firebase_screen_class = data.event_dim[i].params[j].value.string_value;
            } else if (data.event_dim[i].params[j].key == "engagement_time_msec") {
                engagement_time_msec = data.event_dim[i].params[j].value.int_value;
            }
        }
        ws.write(first_open_timestamp_micros + ",");
        ws.write(device_category + ",");
        ws.write(mobile_brand_name + ",");
        ws.write(mobile_model_name + ",");
        ws.write(mobile_marketing_name + ",");
        ws.write(device_model + ",");
        ws.write(platform_version + ",");
        ws.write(resettable_device_id + ",");
        ws.write(user_default_language + ",");
        ws.write(limited_ad_tracking + ",");
        ws.write(continent + ",");
        ws.write(country + ",");
        ws.write(region + ",");
        ws.write(city + ",");
        ws.write(app_version + ",");
        ws.write(app_instance_id + ",");
        ws.write(app_store + ",");
        ws.write(app_platform + ",");
        ws.write(app_id + ",");
        ws.write(bundle_sequence_id + ",");
        ws.write(date + ",");
        ws.write(event_name + ",");
        ws.write(timestamp_micros + ",");
        ws.write(firebase_screen + ",");
        ws.write(firebase_screen_class + ",");
        ws.write(engagement_time_msec + "\n");
    }
});

簡単なテストアプリのサンプルデータなので、カスタムのイベントとプロパティのデータの処理は入ってません。
なので実際はプロパティの数だけカラムは増えます。
あとはset_timestamp_usecやら、previous_timestamp_microsなど他にもデータはあるのですが、そこらへんは今回は省略してます。

このNode.jsを実行すると、このようなCSVデータになりました。