Lookerで中森明菜の名曲「DESIRE -情熱-」のデータを分析する
大阪オフィスの玉井です。
先日、下記の記事をしたためました。
この記事の実態は「Snowflakeで半構造化データを扱ってみた」なのですが、これを業務で使うと考えた場合、直接クエリを書く方法の他に、何らかのBIツールを介してアクセスすることが考えられます。
果たして、そのままぶち込んだ半構造化データを、BIツールを介して扱うことができるのか…。
ということで、Lookerでやってみました。
データソースがSnowflakeだったらワンチャンある
LookerはLookMLと言語を用いて、読み込んだDBのデータを定義します。ただ、結局のところ、LookMLというのは、SQLを抽象化したものにすぎません(LookerはLookMLをSQLに変換してDBに問い合わせる)。
一方、Snowflakeはクエリで半構造化データを柔軟に扱うことができます。
ですので、接続しているDBがSnowflakeであれば、Lookerの機能だけで、Snowflakeにそのままぶち込んだ JSONファイルを扱うことができるのでは?と考えました。
下記は似たようなことが書かれている、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 -情熱-」の詳細な分析が知りたいという方は下記をどうぞ。