こんにちは、スズです。
現地時間の2023年5月22日（月）～5月25日（木）に、ラスベガスにてAlteryx Inspire 2023が開催されました。本記事では、Alteryx Inspire 2023で開催されたセッション『Solving Weekly Challenge using In-Database tools』についてお届けします。
セッション概要（日本語訳）
本セッションでは、分析スキルのエクササイズやSQLのスキルアップのために、In-DBツールやSQLを活用するメリットを掘り下げます。Weekly Challengeに掲載された課題を中心に、In-DB処理を活用した効率的かつ効果的な解決策を学びます。この機会にIn-DB処理の世界に触れて、分析力を高めてみてはいかがでしょうか。
セッションレポート
登壇者紹介
- Thales Donizeti Silva
- Senior Consultant, Analytics Engineering, phData
- 2019年からAlteryx ACE
What are Weekly Challenge?
- Weekly ChallengeはAlteryx Communityによる取り組み
- ユーザーは課題に対して解決策を提示する
- 他のユーザーの解決策を見ることができる
- 練習や腕試しにとてもいい方法
What are In-Database tools?
- In-DBツールは通常のツールとは少し違う仕組みになっている
- データの処理はデータベースサーバーで行われる
- データベース処理と外部ファイルやAPIを組み合わせて、外部データをデータベースにプッシュし、データを融合させるというハイブリッドなアプローチを考える上で、In-DBツールはとても便利なツール
How do In-Database tools work?
- どのツールもCTE（共通テーブル式）を構築している
- Alteryxが最終的に連結SQL文をデータベースにプッシュするまで、全てのツールがCTEを構築している
- ユーザーがデータを見るために閲覧In-DBツールを使用する場合、そのデータはデータベースを離れる
- この処理は透過的で、Alteryxが動的出力In-DBツールを使って生成するクエリを見ることができる
- また、動的入力In-DBツールを使うことで、SQLをデータベースにプッシュすることもできる
- このセッションでは、これらの機能をお見せすることを第一の目的としている
- 初心者向けのチャレンジ
Weekly Challenge #1 - Join to Range
- Weekly Challenge #1では、郵便番号のデータと、郵便番号の範囲を持つ顧客データを結合、地域別に顧客の数を集計する
ブログ執筆者補足：Alteryx Communityの日本語サイトでも問題が掲載されています。以下のサイトをご確認ください。
チャレンジ #1: 範囲に結合 - Alteryx Community
- Weekly Challengeではstartのファイルと、最終的なデートセットとなるsolutionのファイルがある
- solutionのファイルには最終的なデータセットがあり、これと比較することで正しいかどうかを確認できる
Setup
- In-DBツールを使うために使用するデータベースを用意
- データベースに接続するためのドライバをインストール
- あわせてODBCを設定
- Alteryxでデータ接続を作成
- In-DBツールで作業が行えるよう、データベースにstartファイルのデータを生成
- オブジェクトをテーブルに接続
Database - Snowflake
- Snowflakeは30日間の無料トライアルが可能
- In-DBツールを使ったWeekly Challengeの取り組みに利用できる
- データベースサーバーなどのインストールは不要
- Weekly Challengeのテーブルを保持するためにデータベースリソースの作成が必要
ODBC Driver and Connections
- ODBCドライバはAlteryxのライセンスポータルからダウンロード可能
- Windows PC上でODBCを検索するとメニューが表示される
- Snowflakeに接続するためのDSNを作成する
Data Connection to load data
- データをロードするためのデータ接続を作成する
- バルクローダーも利用可能
- AlteryxからSnowflakeにデータをロードする最も早い方法
- データ接続を管理するオプションを選択し、Snowflake接続を作成する
- ローカルステージング方式を選択する
In-DB connection
- In-DBツールで作業するため、エンティティ接続を作成する
- ODBCで作成したDSNを参照し、Table/Fieldを選択し、SQL StyleをNoneにする
Load Start files to Snowflake
- データ型が適切であることを確認し、作成した接続を参照してデータを出力する
- Weekly Challengeごとにスキーマを作成することで、整理して使える
- In-DBツールを使ってWeekly Challengeに取り組むためのセットアップは完了
Original Solution
- Weekly Challengeの元のsolutionを確認
- 列分割ツール、行生成ツールがあるが、これらのツールはIn-DBツールには存在しない
- In-DBツールを使って処理する場合は避ける必要がある
- 列分割ツールでは対象の列を複数の列に分割する
- 行生成ツールで範囲内の全ての行（レコード）を生成
In-DB approach - Connect to the Reference Data
- In-DBツールを使った場合のアプローチ
- 接続In-DBツールにSELECT句を使ってテーブルを読み込む
Formula In-DB tool
- 参照テーブル、ルックアップテーブルを操作して、テキストをカラムに変換する動作を再現
- いくつか文字列操作のSQL関数を使うことができる
- LEFT関数、RIGHT関数を使うことで、範囲を取得できる
- 郵便番号の場合、2000年から2019年までの範囲がある
- 真ん中にダッシュがあるため、ダッシュで分割する
Output the Query generated
- 行生成する方法は基本的にはないため、Alteryxで生成してSnowflakeに戻す
- 動的出力In-DBツールを使って、これまで生成したクエリを参照する
Paste the full query output in Snowflake
- SQL Formatterを使用することがおすすめ
- Alteryxで作成したこれまでのCTEを参照して、SQLを使って作業を継続する
- Alteryxは使用する各ツールのCTE名を一意のIDで生成する
Add the Last Query Alias to the CTE
- 最後のクエリエイリアスを追加してクエリを編集する
Add a Recursive CTE
- Recursive CTEでテーブルをループして行を生成する
- CTE名は大文字にする必要がある
Use a Range Join (SQL)
- SQLでは範囲結合にBETWEEN区を使うことができる
- 範囲結合は行生成ツールの条件部分
- Recursive CTEと組み合わせることで行生成ツール相当になる
Transform the user added CTEs into a Query Alias List
- Alteryxにクエリをプッシュして、In-DBツールでの作業を再開できる
- クエリリストを用意する
- ポジトリからSQLファイルを読み込んで、プロセスを自動化することもできる
Combine the Query Alias Lists
- 生成されたリストを結合ツールで結合できる
- 手動で行う場合は余分な空白が発生する可能性がある
- 全てを1つのレコードに連結すると、基本的に1つのクエリとして解釈される
- 新しい行の区切り文字で連結し、動的入力In-DBツールを追加
- クエリエイリアスリストをプッシュする
Final In-DB steps
- 結合In-DBツールで内部結合を行う
- 集計In-DBツールで集計する
- 最後にデータをストリームアウトして、Alteryxで見ることができるようにする
- Snowflakeに書き戻すことも、最終的なクエリを確認することもできる
- In-DBツールを使用することで得られる柔軟性と、Alteryxを使用することで何が行われているかの理解を促進できる
最後に
Alteryx Inspire 2023のセッション『Solving Weekly Challenge using In-Database tools』のレポートをお届けしました。
個人的にIn-DBツールはあまり使う機会がなく、馴染みの薄いツールでした。Weekly Challengeの問題をIn-DBツールを使って解くことで、In-DBツールやSQLを使う練習になりそうですね。
2
