見出し画像

Snowflakeでデータリネージやってみた

分析屋

分析屋の中田(ナカタ)です。
Snowflakeでデータリネージっぽいことを試してみました。

データリネージとは

データの変遷を追跡することです。
「そもそもこのテーブルってどのテーブルが元になってるんだっけ？」
という疑問が発生したときに

  • 一部のエンジニアしか知らない、人によって言うことが違う

  • ドキュメントが古くて、書いてることが当てにならない

という状況で、源泉まで辿るのが大変な場合もあります。

目標

Snowflakeでテーブルの変遷を可視化する！
※2023年06月時点でデータリネージの可視化は公式未実装

手順

  1. 過去のクエリを取得

  2. Pythonのsqllineageモジュールをインストール

  3. ブラウザで可視化

環境

エディション：ビジネスクリティカル版(トライアル期間)
クラウド：AWS（東京リージョン）
Pythonバージョン：3.10.4

1.過去のクエリを取得

Snowflakeでは、様々なメタ情報を管理しています。
今回は以下のビューから過去に実行されたクエリを取得します。

データベース名：SNOWFLAKE
スキーマ名：ACCOUNT_USAGE
ビュー名：QUERY_HISTORY

-- データ変遷のクエリ取得
SELECT
    QUERY_TEXT
FROM
    SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE
    LOWER(QUERY_TEXT) LIKE 'insert%'
    OR LOWER(QUERY_TEXT) LIKE 'merge%'
    OR LOWER(QUERY_TEXT) LIKE 'update%'
    OR LOWER(QUERY_TEXT) LIKE 'create table%'
    AND EXECUTION_STATUS = 'SUCCESS'
ORDER BY
    START_TIME ASC
;

上記のサンプルコードでは
過去全期間のINSERT文、MERGE文、UPDATE文、CREATE TABLE文のうち
成功したクエリを取得します。

SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORYのビューでは
過去に実行されたクエリが、大文字小文字もそのまま、かつ失敗したクエリも含めて残ります。
ですので、WHERE句では

  • LOWER関数で小文字に統一

  • EXECUTION_STATUS列を「SUCCESS」に限定

としています。

適宜、状況に合わせてカスタマイズしてください。
例えば日常的に大量のクエリを投げる環境であれば、実行完了までに長時間かかる可能性があるため

  • 期間を絞る

  • 特定のテーブル名を含むクエリだけに絞る

などの工夫が必要です。

他にも、CREATE TABLEではなくCREATE OR REPLACEも含める必要があるかもしれません。

実行すると、以下のような結果が表示されます。

結果画面の右側、下向き矢印ボタン（結果のダウンロード）をクリックします。

「.csvとしてダウンロード」をクリックします。

CSVファイルで結果がダウンロードされます。
テキストエディタで開いてみると以下のような見た目をしています。

このままでは、正しくクエリが読み取られないため
以下の加工を実施します。

  • １行目の「QUERY_TEXT」を削除

  • 各行のクエリ末尾にセミコロンがなければ付ける

  • クエリを囲んでいるダブルクォーテーションは除去

最後に、拡張子を.csvから.sqlに変えておきます。

2.Pythonのsqllineageモジュールをインストール

ここからはSnowflakeではなく、コマンドライン操作になります。
例として、PowerShellで実行していきます。

以下のコマンドを入力して実行、sqllineageモジュールをインストールします。

pip install sqllineage

このモジュールは、クエリを解析してデータの変遷を追跡するものです。


3.ブラウザで可視化

さらにコマンドラインにて操作を続けます。
カレントディレクトリと、sqlファイルの置き場を一致させます。

  • cdコマンドで、カレントディレクトリを移動

  • sqlファイルをカレントディレクトリに移動

どちらでもお好きな方でかまいません。

コマンドラインに以下のコマンドを入力して実行します。

sqllineage -g -f sample.sql

-gオプション：データの変遷を可視化します
-fオプション：クエリのファイルを指定します
※sample.sqlは各自のsqlファイル名に合わせて変えてください

実行すると、直後にURLらしき１行が表示されます。

* SQLLineage Running on http://localhost:5000/?f=sample.sql

http以降のURLをブラウザで開きます（以下の画像ではGoogleChromeを使用）。

結果

無事、可視化されました！

参考：カラムリネージ

カラム単位でのデータリネージも2023年01月に一般公開されました。
が、グラフィカルに可視化されるわけではなく
テキスト形式（JSON）で取得できる機能です。

以下のビューで取得できます。

データベース名：SNOWFLAKE
スキーマ名：ACCOUNT_USAGE
ビュー名：ACCESS_HISTORY 
カラム名：OBJECTS_MODIFIED

このデータを元に、カラムリネージが可視化される日もきっと近いはず（期待）！

おまけ

現段階ではdbtというELT製品を使うことで、テーブルの変換を可視化できます。

ここまでお読みいただき、ありがとうございました！
この記事が少しでも参考になりましたら「スキ」を押していただけると幸いです！

株式会社分析屋について

ホームページはこちら。

noteでの会社紹介記事はこちら。

【データ分析で日本を豊かに】
分析屋はシステム分野・ライフサイエンス分野・マーケティング分野の知見を生かし、多種多様な分野の企業様のデータ分析のご支援をさせていただいております。 「あなたの問題解決をする」をモットーに、お客様の抱える課題にあわせた解析・分析手法を用いて、問題解決へのお手伝いをいたします！
【マーケティング】
マーケティング戦略上の目的に向けて、各種のデータ統合及び加工ならびにPDCAサイクル運用全般を支援や高度なデータ分析技術により複雑な課題解決に向けての分析サービスを提供いたします。
【システム】
アプリケーション開発やデータベース構築、WEBサイト構築、運用保守業務などお客様の問題やご要望に沿ってご支援いたします。
【ライフサイエンス】
機械学習や各種アルゴリズムなどの解析アルゴリズム開発サービスを提供いたします。過去には医療系のバイタルデータを扱った解析が主でしたが、今後はそれらで培った経験・技術を工業など他の分野の企業様の問題解決にも役立てていく方針です。
【SES】
SESサービスも行っております。

この記事が参加している募集

#やってみた

36,964件