AlteryxのIn-databaseツールを使って大量データを効率よく集計する – Snowflake Advent Calendar 2019 #SnowflakeDB

こんにちは、DA部プリセールスエンジニアの兼本です。

当エントリは『Snowflake Advent Calendar 2019』の22日目のエントリです。

Snowflake Advent Calendar 2019 - Qiita Snowflake Advent Calendar 2019 ｜ シリーズ ｜ Developers.IO

はじめに

私は普段「Alteryx」というデータブレンディング＆分析製品のプリセールスエンジニアとして活動をしています。 データウェアハウス（以下DWH）に保存されているデータを抽出して様々なデータ加工や分析を行うツールなので、Snowflakeに限らず、データウェアハウスとの親和性はとても高いといえます。 「Alteryxって何ですか？」という方は、ぜひ以下のコンテンツをご覧ください。

• Alteryxでデータ準備やレポート作成の負荷を軽減して、 分析業務に集中しませんか
• Alteryx – 特集カテゴリー –

さて、今回のエントリでは、Alteryxで通常のワークフローとIn-Databaseワークフロー（いわゆるSQLバッチ/ELT処理）を作成して、その動作の違いを確認したいと思います。

検証ではSnowflakeのハンズオンラボなどで提供されているSNOWFLAKE_SAMPLE_DATABASEデータベースで提供されているTPCF_SFというデータを使用します。 このデータベースは受注データを想定したもので、以下のようなさまざまな規模のデータが用意されています。

最後のほうは0が多すぎて桁がよくわからないですが、最大で150億行（500GB強）のORDERデータが用意されています。

検証では、以下のような処理を通常のワークフローとIn-Databaseワークフローの2パターンで作成します。

1. ORDERテーブルとCUSTOMERテーブルをジョインして抽出
2. ORDER_STATUS=Fになるデータのみフィルタリング
3. 受注日から受注月を計算
4. リージョンごと受注月ごとの販売金額と販売アイテム数を集計

通常のワークフロー

通常のワークフローでは、以下のような処理を作成しています。 もっと複雑な処理も作れるのですが、それは今回の趣旨ではないので、シンプルに行きます。

Snowflakeに対して発行するクエリはビルトインのビジュアルクエリエディタで生成していますが、必要であればSQL文を直接編集することもできます。

クエリエディタの設定：

作成されたSQL文：

Select SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.REGION.R_NAME, 
  SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION.N_NAME, 
  SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER.C_NAME, 
  SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER.C_ADDRESS, 
  SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER.C_MKTSEGMENT, 
  SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER.C_CUSTKEY, 
  SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS.O_ORDERKEY, 
  SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS.O_ORDERSTATUS, 
  SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS.O_TOTALPRICE, 
  SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS.O_ORDERDATE, 
  SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS.O_ORDERPRIORITY 
From SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER 
  Inner Join SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS On SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER.C_CUSTKEY = SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS.O_CUSTKEY 
  Inner Join SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION On SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION.N_NATIONKEY = SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER.C_NATIONKEY 
  Inner Join SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.REGION On SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.REGION.R_REGIONKEY = SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION.N_REGIONKEY

TPCH_SF1（ORDER: 1,500,000件）

まずは最も少ないデータを使ったワークフローの実行結果からみてみましょう。

およそ1分15秒で処理が終わりました。 処理時間の95%（およそ72秒）がデータロードに使われていますが、このくらいなら手元で実行してもいいかなという感じです。

Snowflake側では先ほどAlteryxから実行したクエリの実行履歴とプロファイルを確認することができます。

これを見るとSnowflake側ではこのクエリを2.7秒（！）で処理していることがわかりますね。

TPCH_SF10（ORDER: 15,000,000件）

続いてデータ量を10倍に増やします。

実行してから処理が完了するまで4分43秒かかりましたが、まだまだいけそうです。 こちらも実際にはワークフローの処理時間の93%（265秒程度）がデータロードに使われているので、ネットワーク経由でデータをダウンロードする処理がボトルネックになっていることがわかります。

こちらもSnowflakeのクエリ実行履歴を確認してみます。

クエリは3.8秒で実行完了していますので、やはりネットワーク経由でのデータ送信がボトルネックですね。

TPCH_SF100（ORDER: 150,000,000件）

さらに10倍のデータを使用します。

一番最初のクエリからデータ量が100倍に増え、ワークフローの処理時間は43分も掛かってしまいました。 ただし、これも処理の内訳はほとんどがデータロードで、実に93%（40分強）をデータロードのためだけに使っています。

こちらもSnowflake側のクエリ実行結果を確認してみます。

Snowflakeはデータ量が最初の100倍になっても16.2秒でクエリの実行が完了しています。 この検証ではSnowflakeのウェアハウス（コンピュートノード）をX-Largeに設定していますので、必要に応じてスケールアップしてクエリスピードを上げることもできますが、一方で、データ分析のたびにネットワーク経由で30GB強のデータロードが発生するのは効率が良いとは言えませんね。

In-databaseツールを使用したワークフロー

次にIn-Databaseを使ったワークフローを作成します。

ワークフローの作りは通常のワークフローと同じくツールの組み合わせとプロパティの設定だけで行いますが、使用するツールはIn-Database専用のツールを使います。

• AlteryxのIn-DB機能の話

また、ワークフロー内で計算式を使う場合、通常のフォーミュラツールではAlteryxの関数を使って数式を作成しますが、In-Databaseのフォーミュラツールを使う場合は、アクセスするデータベースが提供している関数を使用する必要があります。

例えば、今回のワークフローでは、日時データを月で切り捨てています。

• 通常のワークフローでのフォーミュラの設定

• In-Databaseワークフローでのフォーミュラの設定

ここで使用しているDATE_TRUNC関数はSnowflakeが提供している関数です。 In-Databaseフォーミュラツールで使用できる関数はアクセスするデータベースの仕様に依存することに注意してください。

• DATE_TRUNK

• Alteryxにおける通常のツールとIn-DBツールでできることの違い

このワークフローを実行すると設定した内容がSQLクエリに変換され、フィルタリングや集計処理のすべてがSnowflake側で実行されます。

WITH "Tool6_d002" AS (
  Select SNOWFLAKE_SAMPLE_DATA.TPCH_SF10000.REGION.R_NAME, 
  SNOWFLAKE_SAMPLE_DATA.TPCH_SF10000.CUSTOMER.C_NAME,
  SNOWFLAKE_SAMPLE_DATA.TPCH_SF10000.NATION.N_NAME, 
  SNOWFLAKE_SAMPLE_DATA.TPCH_SF10000.CUSTOMER.C_ADDRESS, 
  SNOWFLAKE_SAMPLE_DATA.TPCH_SF10000.CUSTOMER.C_MKTSEGMENT, 
  SNOWFLAKE_SAMPLE_DATA.TPCH_SF10000.CUSTOMER.C_CUSTKEY, 
  SNOWFLAKE_SAMPLE_DATA.TPCH_SF10000.ORDERS.O_ORDERKEY, 
  SNOWFLAKE_SAMPLE_DATA.TPCH_SF10000.ORDERS.O_ORDERSTATUS, 
  SNOWFLAKE_SAMPLE_DATA.TPCH_SF10000.ORDERS.O_TOTALPRICE, 
  SNOWFLAKE_SAMPLE_DATA.TPCH_SF10000.ORDERS.O_ORDERDATE, 
  SNOWFLAKE_SAMPLE_DATA.TPCH_SF10000.ORDERS.O_ORDERPRIORITY 
From SNOWFLAKE_SAMPLE_DATA.TPCH_SF10000.CUSTOMER 
  Inner Join SNOWFLAKE_SAMPLE_DATA.TPCH_SF10000.ORDERS 
  On SNOWFLAKE_SAMPLE_DATA.TPCH_SF10000.CUSTOMER.C_CUSTKEY = SNOWFLAKE_SAMPLE_DATA.TPCH_SF10000.ORDERS.O_CUSTKEY 
  Inner Join SNOWFLAKE_SAMPLE_DATA.TPCH_SF10000.NATION 
  On SNOWFLAKE_SAMPLE_DATA.TPCH_SF10000.NATION.N_NATIONKEY = SNOWFLAKE_SAMPLE_DATA.TPCH_SF10000.CUSTOMER.C_NATIONKEY 
  Inner Join SNOWFLAKE_SAMPLE_DATA.TPCH_SF10000.REGION 
  On SNOWFLAKE_SAMPLE_DATA.TPCH_SF10000.REGION.R_REGIONKEY = SNOWFLAKE_SAMPLE_DATA.TPCH_SF10000.NATION.N_REGIONKEY), 
"Tool7True_27eb" AS (SELECT * FROM "Tool6_d002" WHERE "O_ORDERSTATUS" != 'F'), 
"Tool41_7fbf" AS (
  SELECT "R_NAME", "C_NAME", "N_NAME", "C_ADDRESS", "C_MKTSEGMENT", "C_CUSTKEY", "O_ORDERKEY", "O_ORDERSTATUS", 
  "O_TOTALPRICE", "O_ORDERDATE", "O_ORDERPRIORITY", (DATE_TRUNC('MONTH',"O_ORDERDATE")) AS "ORDERMONTH" FROM "Tool7True_27eb"), 
"Tool8_35a4" AS (SELECT "ORDERMONTH", "R_NAME", COUNT(*) AS "Count", SUM("O_TOTALPRICE") AS "Sum_O_TOTALPRICE" 
  FROM "Tool41_7fbf" GROUP BY "ORDERMONTH", "R_NAME") SELECT * FROM "Tool8_35a4" ORDER BY "ORDERMONTH" ASC, "R_NAME" ASC

データは思い切って最大の150億行で集計してみましたが、なんと1分53秒で集計が完了してしまいました。

Snowflakeのクエリ実行履歴を確認したところ、驚いたことにSnowflake側の集計処理は50秒で完了していました。

繰り返しますが、最後の検証は150億件の集計です。 ウェアハウスのパワーをうまく活用することで、ノーコーディングでもここまでできるのはうれしいですね。

まとめ

以上、AlteryxのIn-databaseツールを使って大量データを効率よく集計する方法についてご紹介いたしました。 データのありかとデータ分析の要件を把握して、最適なデータ分析ライフを送りたいものですね。

明日23日目はスズによる「Alteryx Designerのデータ出力ツール」の予定です。お楽しみに！