Lookerで中森明菜の名曲「DESIRE -情熱-」のデータを分析する

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

先日、下記の記事をしたためました。

この記事の実態は「Snowflakeで半構造化データを扱ってみた」なのですが、これを業務で使うと考えた場合、直接クエリを書く方法の他に、何らかのBIツールを介してアクセスすることが考えられます。

果たして、そのままぶち込んだ半構造化データを、BIツールを介して扱うことができるのか…。

ということで、Lookerでやってみました。

データソースがSnowflakeだったらワンチャンある

LookerはLookMLと言語を用いて、読み込んだDBのデータを定義します。ただ、結局のところ、LookMLというのは、SQLを抽象化したものにすぎません（LookerはLookMLをSQLに変換してDBに問い合わせる）。

一方、Snowflakeはクエリで半構造化データを柔軟に扱うことができます。

ですので、接続しているDBがSnowflakeであれば、Lookerの機能だけで、Snowflakeにそのままぶち込んだ JSONファイルを扱うことができるのでは？と考えました。

下記は似たようなことが書かれている、Looker Communityの記事です。

• Modeling Nested Data in Snowflake - SQL - Looker Community

Lookerからアクセスできるところまでやってみる

とりあえずやってみます。

環境

• Snowflake 4.6.4
• Looker 7.2.12

データ

下記の記事で用意したテーブル（Spotify APIから取得したJSONファイルをそのまま入れているテーブル）。

LookerのConnectionにSnowflakeを登録する

まずは、LookerにSnowflakeを接続します。方法は下記を参照。

Projectを作成する

JSONをぶち込んでいるテーブル（があるスキーマ）を指定して、Projectを作成します。一応、自動でModelとViewが作成されるオプションを選んでおきます。

Projectが作成できました…といっても、カラムは1つ、レコード数も1、ということで、このままではとても分析できる状態ではありません。

SQL Runnerでクエリが実行できるか確認

とりあえず普通にSELECT文を投げてみます。

問題なく実行できました。

LookMLでJSONファイル内の各要素にアクセスできるか確認

今度はLookMLの確認です、Snowflakeの時と同様、動作を1つずつ確認していきたいと思います。

sectionsというキー下に存在する値だけ取得

Snowflakeの時のクエリをそのまま移植する感じでいけます。

  dimension: sections {
    type: string
    description: "Desireデータのsections内の要素だけ取り出したもの"
    sql: ${TABLE}.RAW_STATUS:sections;;
  }

sqlのところで、Snowflakeの記法を入れるところがポイントです。descriptionとかはお好みで。こちらをExploreで使ってみると？

Lookerからsections部だけとってくることができました。

任意の値をピンポイント取得

sqlの部分が、SELECT句に書かれるイメージを浮かべるとやりやすいと思います。ですので、下記のように書くことで、JSONのネストされた値の一番深いところも狙い撃てます。

  dimension: bars_confidence_24 {
    type: string
    description: "barsの5番目の配列のconfidenceを取り出したもの"
    sql: ${TABLE}.RAW_STATUS:bars[4]:confidence ;;
  }

値をとってくることができました。

Snowflakeにぶち込んでいるJSONデータをLookerで分析するには

Snowflakeで半構造化データを扱う方法を知っていれば、それをLookMLで記述することで、Looker上からJSONを扱うことができました。

しかし、この方法を使ったところで、分析自体（値の集計、可視化、etc）を行うには厳しいものがあります。

Snowflakeでもそうでしたが、Lookerで扱うにしても、やはり「構造化されたテーブル」を用意し、そこにクエリを実行して可視化していく…という方法をとる必要があります。

そこで、今回は「構造化されたテーブル」を、Lookerの機能の1つである派生テーブルとして用意して、そこに対して可視化を行えるようにしたいと思います。2通りやってみましたが、どちらかというとオススメはパターン1です（楽）。

パターン1：完全に構造化するクエリをそのまんま派生テーブルにする

Snowflake上で、ぶち込んだJSONファイル（があるテーブル）に対して、下記クエリを実行すると、sectionsという部分を構造化したテーブルにすることができます。

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

これをそのままLooker上で派生テーブルにします。

SQL Runnerから派生テーブルを作成する

そのままベタっと。

キレイに構造化された結果が取得できたところで、画面右上の歯車アイコンから派生テーブルを作成するメニューを選びます。

すると、実行したクエリをそのまま派生テーブルにするLookML（っていうかViewファイルまるまる）が生成されます。これをコピーするか、add it to your projectを選んでProject上にこのViewファイルを作成します。

こんな感じですね。派生テーブルの定義だけでなく、その派生テーブルに対して、ちゃんとdimension等も記述されています。

これで、データソース側はJSONファイルをぶち込んだだけの状態ですが、Looker側で構造化した表を用意することができました。

ちょっと分析してみる

既に定義されているdimensionですと、例えば、各セクションの開始時間（start）と、セクション毎の音の大きさ（loudness）があるので、それを使って、曲の音の大きさの移り変わりを見ることが出来ます。

もちろん、新しく項目を追加しても良いです。下記はテンポの平均を計算するmeasureです（小数点が多かったので、表記を小数点第二位までにしています）。

  measure: avg_tempo {
    type: average
    value_format_name: decimal_2
    sql: ${TABLE}.${tempo} ;;
    }

このmeasureを1つ使用するだけで、曲全体の平均テンポを可視化することができました（まあ「DESIRE -情熱-」は構成が目まぐるしく変わるような曲ではないので、どのセクションでもテンポは一定ですが）。

パターン2：分析したい要素（キー）をフラット化するところだけ派生テーブルにする

パターン1はクエリ全体を一気に派生テーブルにしましたが、こちらのパターン2では、sectionsをフラット化するところだけ派生テーブルにして、後は地道にLookMLでdimension等を定義していきます。

SQL Runnerから派生テーブルを作成する

流れはパターン1と同じですが、流すクエリが異なります。

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

パターン1と同じように派生テーブルを作成します。できたViewファイルがこちら。

ちょっと分析してみる

パターン2の派生テーブルは、sectionsの中身をフラット化しただけで、まだ半構造化データのままです。ですので、そこを意識して項目を定義します。

例えば、先程でもやった、テンポの平均を出すには、下記のような記述になります。

  measure: avg_tempo {
    type: average
    value_format_name: decimal_2
    sql: ${TABLE}.value:tempo ;;
  }

sqlのところが、Snowflakeの半構造化データを扱うときの記法になっていますね。これを使うと、先ほどと同じような可視化ができます。

おわりに

中森明菜の「DESIRE -情熱-」は名曲ということがわかりましたね。

…といっても、今回はほんのちょっとグラフを作っただけになりました。何が言いたかったと言うと、SnowflakeをデータソースにしたLookerは半構造化データの扱いが柔軟にできる」ってことを言いたかったのです。

今回やってみた2通りの方法ですが、結局「どこまでをLookerでやるか」によってくると思います。パターン1はテーブルの部分はSQLでやってしまうタイプ。パターン2は最低限のところだけSQLで定義して、後はLookMLでじっくり定義するって感じです。

「DESIRE -情熱-」の詳細な分析が知りたいという方は下記をどうぞ。