Qlik SenseとSnowflakeで150億件のデータを可視化してみた

Qlik Senseユーザーの皆様、こんにちは。

Qlik Sense DesktopやTableauなどのBIツールを使っていると、DBサーバーに接続して大量のデータを分析することがありますが、

画面が固まってしまったり、PCのパフォーマンスが悪くなってしまうことがあります。

今回は通常BIツールでは扱えないような大量のデータを、

クラウドDWHのSnowflakeを活用して一瞬で分析できる方法をレポートしていきます。

(Tableauの記事はこちら)

1. Snowflakeの下準備

1-1. Snowflakeのアカウント作成・初期設定

Snowflakeのトライアルでは、1ヶ月で400ドル分のリソースを無料で利用することが出来ます。

トライアルを開始するには Snowflake にアクセスし、氏名、メールアドレス、会社名、国名(JapanでOK)を入力してアカウントを作成します。

数分経つと登録したメールアドレスにアクティベート用リンクが送られてくるので、

リンク先にアクセスします。

ログインを行うとコンソール画面が開かれ、アカウント登録が完了します。

この状態でもQlik Senseから接続を行うことは可能ですが、

その前にSnowflakeの ウェアハウス(クエリ実行などをするサーバー)の初期設定を行います。

コンソール画面上部の「Warehouses」を開き、

ウェアハウスを選択し「Configuration」を選択します。

「Configure Warehouse」で以下の項目を設定します。

Size: ウェアハウスの性能です。性能が高いほど消費コストが高くなります。今回のレポートでは一番性能が良い「4X-Large」を選択しました。
Auto Suspend: クエリ実行が終了してから次のクエリが実行されるまでに自動でウェアハウスがサスペンドされる時間です。今回は5分を選択しました。（設定ファイルでもっと短い時間を選択することもできます。）
Auto Resume: ウェアハウスがサスペンド状態の時にクエリが実行された時、自動でウェアハウスが起動されるようにするかの項目です。今回はオンにしました。

1-2. Snowflake ODBCドライバーのインストール・設定

Snowflakeの設定が完了したので次は接続のための準備を行います。

Qlik Sense DesktopでSnowflakeに接続する方法はいくつかありますが、

今回はODBCで接続します。

SnowflakeはODBC接続するためのドライバーを以下のページで提供しているので、

そこからドライバーをダウンロード、インストールします。

https://sfc-repo.snowflakecomputing.com/odbc/index.html

今回は2019/10/25時点で最新だった「snowflake64_odbc-2.19.16.msi」をインストールしました。

(サポートしているOSや詳細情報などは公式ガイドを参照。)

ドライバーをインストールした後、

「ODBCデータソース」などでデータソースを作成します。

Qlik SenseとSnowflakeで150億件のデータを可視化してみた-1

「ドライバー」タブでSnowflakeのドライバーがインストールされていることを確認します。

Qlik SenseとSnowflakeで150億件のデータを可視化してみた-2

「システムDSN」タブでデータソースを追加します。

Qlik SenseとSnowflakeで150億件のデータを可視化してみた-3

「SnowflakeDSIIDriver」を選択し、設定に必要な項目を入力していきます。

Qlik SenseとSnowflakeで150億件のデータを可視化してみた-4

Qlik SenseとSnowflakeで150億件のデータを可視化してみた-5

【必要入力項目】

Data Source	名前。自由に設定可能
User	SnowflakeのログインID
Password	Snowflakeのログインパスワード
Server	Snowflakeのコンソール画面のURL

（他は空欄でも作成可能でした。）

1-3. Qlik Sense からSnowflakeへ接続

データソースの追加が完了したら、

Qlik Senseから作成したデータソースを利用してSnowflakeに接続します。

「新規データソースへの接続」でODBCを選択し、

作成したデータソースに接続する為のユーザー名、パスワードを入力して接続を作成します。

Qlik SenseとSnowflakeで150億件のデータを可視化してみた-6

以下の画面でSnowflakeのデータベース等が確認出来たら接続設定完了です。

チャート作成

2-1. Direct Discoveryでデータをロード

今回は単純な日次売上グラフを作成してみます。

Snowflakeに最初から入っているサンプルデータの、

購入情報をまとめた「ORDERS」テーブルを使用します。

Qlik SenseとSnowflakeで150億件のデータを可視化してみた-7

【テーブル概要】

Database	SNOWFLAKE_SAMPLE_DATA
Schema	TPCH_SF10000
行数	150億件
サイズ	516GB

ロードスクリプトエディタを開き、以下のように記入します。

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';
SET MoneyFormat='¥#,##0;-¥#,##0';
SET TimeFormat='h:mm:ss';
SET DateFormat='YYYY-MM-DD'; // 日付のフォーマットをSnowflakeのものに合わせます
SET TimestampFormat='YYYY-MM-DD h:mm:ss[.fff]';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='ja-JP';
SET CreateSearchIndexOnReload=1;
SET MonthNames='1月;2月;3月;4月;5月;6月;7月;8月;9月;10月;11月;12月';
SET LongMonthNames='1月;2月;3月;4月;5月;6月;7月;8月;9月;10月;11月;12月';
SET DayNames='月;火;水;木;金;土;日';
SET LongDayNames='月曜日;火曜日;水曜日;木曜日;金曜日;土曜日;日曜日';
SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';

SET DirectTableBoxListThreshold = 10000000000; // テーブルチャートの行数が多い時に設定。デフォルトは1000

LIB CONNECT TO 'snowflake_test'; // コネクション名

DIRECT QUERY // Direct Discoveryの宣言や初期化

DIMENSION

O_ORDERDATE

MEASURE
O_TOTALPRICE,
O_ORDERKEY,
O_CUSTKEY

FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF10000.ORDERS;

Direct Query以下の行がDirect Discoveryの特徴的なフィールドとなります。

各フィールドは以下のような特性を持っています。

フィールドタイプ	メモリーにロードされるか？	関連付け	チャートでの利用
DIMENSION	される	可	軸に使用
MEASURE	されない	不可	メジャーに使用。集約関数が使用可能
DETAIL	されない	不可	表示は出来るがチャートや集約関数は使用不可