Hexだけで中森明菜の名曲「サザン・ウインド」についてSpotifyからデータを取得してSnowflakeにロードして分析する

白いヨットの上の美少年の大阪オフィスの玉井です。

久しぶりの中森明菜シリーズとなります。

題名の通り、Hexを使って、Spotifyから中森明菜の名曲「サザン・ウインド」のデータの取得〜DWH（Snowflake）へロード〜分析を一気通貫でやりたいと思います。Hex以外では作業・処理はしないところがポイントです。

Hexとは？

こちらをどうぞ。

中森明菜の名曲「サザン・ウインド」とは？

本記事でやることの概要

Hexの特徴の一つは同一NotebookでSQLとPythonを両方使えることです。それを最大限に活かす方向で、今回のプロジェクトを実施したいと思います。

• PythonでSpotifyからサザン・ウインドのデータを取得する
• Python+SQLでサザン・ウインドのデータをSnowflakeにロードする
• SQLでサザン・ウインドのデータを分析する

これらの処理を、同一のNotebookで行います。

また、ロードする際は、pandas等で整形してからロード…というようなことはせず、とりあえずjsonファイルのままロードします。これはSnowflakeは半構造化データを扱うことができるため、とりあえず先に入れて後からSQLで整形する方が楽だからです（つまりETLではなくELTで行う）。

実施環境

本記事は、以下の状態を前提にしています。

• Hexが使用できる
• Snowflakeが使用できる
• HexとSnowflakeが連携している
• Spotify APIが利用できる（クライアントシークレットとかが発行できる）

やってみた

Spotifyからデータ取得編

各種Secretを設定する

Hexでは、パスワード等のベタ書き厳禁なパラメータをSecretとして別で設定することができます（まあ他のツールでも大体できると思いますが）。今回は個人でやるだけですので、別に設定しなくても問題ないっちゃないのですが、せっかくなので設定します。

Variablesというメニューから、Secret登録メニューにいきます。

後は、ただひたすらに、名前と値を設定するだけです。

今回は以下を設定しました。

• Spotify APIの…
  • クライアントID
  • クライアントシークレット
• Snowflakeの…
  • アカウント名
  • ユーザー名
  • パスワード

Spotifyからデータを取得する

まずはSpotifyからサザン・ウインドのデータを取得します。

SpotifyにはSpotipyというPython用のライブラリがあるため、これを使用します。Hexにデフォルトで存在しないため、インストールから行います（Notebook系のサービス？ではエクスクラメーションマークをつけることでpipが実行できるってことを今回の件で初めて知りました）。

!pip install spotipy --upgrade

こんな感じでインストールされます。

後は、データを取得するだけです。

ちなみに、サザン・ウインドの何のデータをとるかって話ですが、前回と同じく「Get Audio Analysis for a Track」で取得できる楽曲の詳細なデータをとります。

import json
import spotipy
import snowflake.connector as sc
from spotipy.oauth2 import SpotifyClientCredentials

client_credentials_manager = spotipy.oauth2.SpotifyClientCredentials(sp_client_id, sp_client_secret)
spotify = spotipy.Spotify(client_credentials_manager=client_credentials_manager)
# サザン・ウインドのSpotify ID
track_id = 'https://open.spotify.com/track/2dZHD66ameYxyZkJtR6bYG?si=8629cbd4675e417f'

# Spotify APIを叩く
audio_analysis = spotify.audio_analysis(track_id)

snowflake.connectorですが、これは後で使うため、ここで一緒にインポートしています。

データをHexに置く

この段階では、上記で取得したデータは辞書型として保持しています。これは後々Snowflakeにロードするため、ファイルとして一旦どこかに置いておく必要があります。

Hexの場合、下記のようにファイルとして吐き出すことで、Hex上にファイルを置くことができます。「Hexから見たローカル」って感じでしょうか。

path = './southern_wind_analysis.json'
audio_file = open(path, mode="w")

json.dump(audio_analysis, audio_file)

置いたファイルは、左メニューのFilesから確認できます。

Snowflakeへロード編

テーブルを作成する

サザン・ウインドのデータを入れるためのテーブルを先に作っておきます。冒頭で書いたとおり、今回はjsonファイルをそのままぶちこむため、VARIANT型のカラムを1つ用意するだけとなります。

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

急にコードがPythonからSQLになったため、「ん、ここでSnowflakeの画面にうつったのかな？」と思われるかもしれませんが、Hexから一切動いておりません。同じ画面で処理を行っています。

Spotifyデータ（ファイル）をステージングする

次は、テーブルにロードしたいデータをSnowflakeのステージに配置します。

今回はテーブルステージを使用します（楽なので）。先ほど作ったテーブルのテーブルステージに、ファイルをステージングします。

conn = sc.connect(
    user=username,
    password=password,
    account=account_name,
    role='SYSADMIN',
    warehouse='X_SMALL_WH',
    database='TAMAI_TEST_DB',
    schema ='SPOTIFY'
)

conn.cursor().execute("PUT file:///hex/southern_wind_analysis.json @%SOUTHERN_WIND")

これを見たとき「え、またPythonに戻ってる？」「っていうかSnowflakeはステージングもSQLでできるんだから、HexだったらSQLで出来るのでは？」とか言いたくなると思います。

私も同じことを思い、最初はSQLでPUTしてみたのですが、うまくステージングできませんでした（エラーもないがステージングもされてない）。おそらく、Hex上でネイティブで実行できるSQLだと、Hexに配置しているファイルが認識できないのだと思われます。

ですので、ファイルのエクスポート&配置が出来たPythonで実行することにしました（このためにsnowflake.connectorをインポートしていた）。

テーブルにデータをロードする

ファイルをステージングできたら、後はそこからテーブルにロードするだけです（言い忘れていましたが、ファイルフォーマットは作成済でした）。

COPY INTO "TAMAI_TEST_DB"."SPOTIFY"."SOUTHERN_WIND"
FILE_FORMAT = (type = json);

ロードできているかどうか、Snowflake側で確認します。

jsonファイルの中身がそのままテーブルにロードされていますね。

分析編（おまけレベル）

データを構造化の形にする

無事、テーブルにロードすることはできましたが、この半構造化の状態では分析することはほぼ不可能です。というわけで、これを構造化の形にします。

…面倒だったので、前回と全く同じクエリを使い回すことにします。許してくれ。

WITH tmp_wind AS(
    SELECT
        VALUE AS raw_status
    FROM
         "TAMAI_TEST_DB"."SPOTIFY"."SOUTHERN_WIND"
        ,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_wind

Hex上でも、曲のセクション毎のデータがキレイに取得できました。

可視化する

Hexはグラフを作ることもできます。しかも、前セルで実行したSELECT文の結果を自動でdataframeとして持ってくれるため、設定が簡単です。

というわけで、セクション毎の音量（loudness）の大きさを折れ線グラフにしてみました。

最後がガッて下がってますが、サザン・ウインドを聴き込んでいる皆さんなら「あ〜〜」ってなりますよね。この曲のアウトロは「ティンティンティンティン…」って感じで、急にシンセサイザーか何かの音だけが残りつつ終わるので、こういうグラフになるんですねえ。

完成形

今回のHexプロジェクトは、Githubの自分のリポジトリと連携しています（Git Syncという機能）。ズラーッと見たい場合はこちらをどうぞ。

…といっても、これはHexファイル形式（yaml）というものらしく、他のNotebook等で確認することはできません（その代わり、Hexだとインポートしたときに完璧に再現できる）。一応、手動では.ipynbで出すこともできます。

メモなど

ロードの方法

今回はHexローカル（？）にファイルを出してからロードしましたが、当初はS3やGCS等のクラウドストレージに配置→Snowflakeにロード、という方法を考えていました（Snowflake的に言うと「外部ステージ」を使う）。

これも出来るっちゃ出来るのですが、外部ステージの設定として、Snowflakeとクラウドストレージを連携させる必要があり、それってもうHex以外でもゴリゴリ作業しちゃってる感が出たため、今回は辞めました。

ただ、Hex上からクラウドストレージにファイルをアップロードすること自体はできるので（GCSで確認済）、別の使い方をするときは、この方法も便利だと思います。

おわりに

中森明菜の「サザン・ウインド」は名曲ということがわかりましたね。

今度はDagsterとかdbtとか使って似たようことをやってみたいですね。

参考にしたドキュメントなど