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

「カスタマーストーリー」を支えるツール特集

#ビッグデータ

#Alteryx

#ビジネス・アナリティクス

#Snowflake

兼本侑始

2019.12.22

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

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

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

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

はじめに

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

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

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

Table/Schema	TPCF_SF1	TPCF_SF10	TPCF_SF100	TPCF_SF1000	TPCF_SF10000
ORDER	150,000	1,500,000	15,000,000	150,000,000	1,500,000,000
CUSTOMER	1,500,000	15,000,000	150,000,000	1,500,000,000	15,000,000,000

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

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

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

通常のワークフロー

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

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フォーミュラツールで使用できる関数はアクセスするデータベースの仕様に依存することに注意してください。

このワークフローを実行すると設定した内容が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秒で集計が完了してしまいました。