Snowflakeに中森明菜の名曲「DESIRE -情熱-」のデータを入れる

ゲラッゲラッゲラッゲラッバーニラー

「カスタマーストーリー」を支えるツール特集

#ビッグデータ

#ビジネス・アナリティクス

#Snowflake

#中森明菜

たまちゃん

2020.03.04

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

大阪オフィスの玉井です。

…とはいっても、弊社は2020年3月現在BCPが発動しているので、オフィスには出社せず、奈良県の自宅でリモート労働しています。

Snowflakeと半構造化データ

ちょっと聞いてよ～。Snowflakeは半構造化データの取り扱いが強力なんです。

詳細は上記記事をご覧いただきたいのですが、簡単に言うと、JSONやXML等をそのままテーブルにぶっ込むことができます（そしてSnowflake上で半構造化データを扱える）。

最近、SaaSサービスのデータ分析に対する需要が高まっています（Salesforceなんかがその筆頭でしょうか）。そして、そういうアプリケーションから出せるデータの形式っていうのは、大体がJSONやXML…いわゆる半構造化データ形式となっています。

詳細は語りませんが、アプリケーション開発の観点から見ると、データのやり取りは半構造化データの方が楽なんですね。だから、アプリケーションから取得できるデータの形式は半構造化データが多いです。しかし、データ分析という観点から見ると、半構造化データは使いづらい…。データ整形処理は避けられません。

ですので、「SaaSのデータを分析したい」となると、SaaS（のAPIとか）から取得した半構造化データを、構造化データの形に整形してからDWH等に入れる…というのがよくある流れです（いわゆるETL）。

しかし、（前述したように）Snowflakeは半構造化データをそのまま入れることができます。データの整形は入れてからやります。つまりETLではなくELT。LoadしてからTransform！

「半構造化データをそのままぶっ込むってどゆこと？」「ぶっ込んだ半構造化データはどうやって整えんの？」っていう疑問が沸き起こってくるかと思いますが、そこらへんを具体的にどうするのか…ということで、実際にやってみました。

やってみた

環境

Snowflake 4.6.4

データ

下記の記事でも使用した中森明菜の名曲「DESIRE -情熱-」のデータを使います。

もう少し具体的に言うと、Spotify APIの「Get Audio Analysis for a Track」で取得したJSONデータを使います。要するにSpotifyのデータを使います。もちろんSpotifyのAPIから取得できるデータも半構造化データです。

データ自体の解説についても、この記事をご覧ください。

JSONデータをSnowflakeに入れる

テーブルを作成する

下記クエリを実行します。

CREATE TABLE "TAMAI_TEST_DB"."SPOTIFY"."NAKAMORI_AKINA"(
    "RAW_STATUS" VARIANT
)

TAMAI_TEST_DBはDB名、SPOTIFYはスキーマ名です（事前に用意）。そこにNAKAMORI_AKINAというテーブルを作ります。

ここに「DESIRE -情熱-」データを入れるわけですが、何度も言っているように、そのデータは半構造化データ（JSON）です。普通の型のカラムでは入りません。ですので、ここではVARIANTという型を指定します。

ファイルフォーマットを作成する

ファイルをテーブルにロードするためには、ファイルフォーマットを作成しておく必要があります。下記クエリを実行します。

CREATE FILE FORMAT "TAMAI_TEST_DB"."SPOTIFY".JSON_FILE_FORMAT 
TYPE = 'JSON' 
COMPRESSION = 'AUTO' 
ENABLE_OCTAL = FALSE 
ALLOW_DUPLICATE = FALSE 
STRIP_OUTER_ARRAY = TRUE 
STRIP_NULL_VALUES = FALSE 
IGNORE_UTF8_ERRORS = FALSE;

ファイルフォーマット等については下記が詳しいです。

JSONデータをロードする

上記のテーブルの画面に移り、「Load Table」を選びます。

ファイルをテーブルにロードする設定画面が表示されるので、仮想ウェアハウス（サイズの小さいものでよい）、ロードしたいデータ、ファイルフォーマットを選びます。

特に問題なくロードが完了します（するはず）。

データを確認する

ロードが終わったら、とりあえずデータを確認しましょう。

普通にSELECT文

いとも簡単にJSONファイルをロードすることができましたが、テーブル上ではどうなっているのでしょうか。

SELECT
    RAW_STATUS
FROM
    "TAMAI_TEST_DB"."SPOTIFY"."NAKAMORI_AKINA"

まさにそのまんまJSONが文字列として格納されています。もちろんこのままじゃデータを扱うことはできません。SnowflakeにはVARIANT型にぶち込んだ半構造化データを取り扱う機能が色々あるので見てみましょう。

任意の値を指定して出力

今回Spotifyから取得したデータ（の一部）は下記のようになっています（詳細はこちら）

...
    "bars": [
        {
            "start": 2.33444,
            "duration": 2.98043,
            "confidence": 0.545
        },
        {
            "start": 5.31487,
            "duration": 2.97082,
            "confidence": 0.238
        }
    ]
...

例えばbarsというキー名の部分だけ取得する場合は下記のクエリとなります。

SELECT
    RAW_STATUS:bars
FROM
    "TAMAI_TEST_DB"."SPOTIFY"."NAKAMORI_AKINA"

もっとピンポイントに値を指定することもできます。

SELECT
    RAW_STATUS:bars[24]:duration
FROM
    "TAMAI_TEST_DB"."SPOTIFY"."NAKAMORI_AKINA"

これはbarsの25番目（0からカウントするため）の要素のdurationの値を取得しています。

構造化した「DESIRE -情熱-」データを作成する

上述したような形で、直接ぶち込んだJSONデータに対して、値を問い合わせることができます。しかし、データを分析するという観点だと、いちいちJSONデータに直接問い合わせ続けるのも辛いです。

ですので、ぶち込んだJSONデータを、もっと使いやすいように構造化します（今回はsectionというキーの要素を構造化します）。

データを配列毎にバラす（フラット化）

「DESIRE -情熱-」データのsectionには12個の配列があります。まずはsectionの各要素を12行のデータにします。この際使うのがFLATTENという関数です。

簡単に言うと、JSONのように縦にずらーっと持っているデータを行持ちに変換してくれます。

今回はラテラル結合と併用して、下記のクエリをやってみます。

SELECT
    VALUE
FROM
     "TAMAI_TEST_DB"."SPOTIFY"."NAKAMORI_AKINA"
    ,LATERAL FLATTEN(
        input => RAW_STATUS:sections
    )

sectionsをフラット化できました。

構造化する（リレーショナルなテーブルにする）

フラット化した各行に対して、それぞれの要素を出してテーブルにします。上記クエリをそのままWITH句でまとめて、さらにそこにSELECTを仕掛けます。

WITH tmp_desire AS(
    SELECT
        VALUE AS raw_status
    FROM
         "TAMAI_TEST_DB"."SPOTIFY"."NAKAMORI_AKINA"
        ,LATERAL FLATTEN(
            input => RAW_STATUS:sections
        )
)
SELECT
     raw_status:start::float AS "start"
    ,raw_status:duration::float AS "duration"
    ,raw_status:confidence::float AS "confidence"
    ,raw_status:loudness::float AS "loudness"
    ,raw_status:tempo::float AS "tempo"
    ,raw_status:tempo_confidence::float AS "tempo_confidence"
    ,raw_status:key::integer::float AS "key"
    ,raw_status:key_confidence::float AS "key_confidence"
    ,raw_status:mode::integer AS "mode"
    ,raw_status:mode_confidence::float AS "mode_confidence"
    ,raw_status:time_signature::integer AS "time_signature"
    ,raw_status:time_signature_confidence::float AS "time_signature_confidence"
FROM
    tmp_desire