今回はデータベースのお話です。
AlteryxはServerと組み合わせることでスケジュール実行ができるため、いわゆる「ETLツール」として使うことでデータパイプラインの構築ができます。この場合、AWSのS3においている複雑なExcelなどを加工してDB/DWHに格納するといったことも他のきれいなデータを取り扱うようなETLツールに比べると非常に柔軟に対応することが可能かと思います。
ところで、データパイプラインの構築ということになると、非常に様々なDBに接続することになるかと思いますが、一部のDBではBulk接続に対応していないものもあり、ファイルで書き込みたいといった要望も出るかもしれません（MySQL！！！）。もしくは、空間型はデータ出力ツールではそのまま入らないようなDBもあり、そのような場合はどうしてもSQLをAlteryx内で構築し、そのSQLを実行したい、ということになります。
以前、「WeeklyAlteryxTips#21 PostgreSQLでストアドプロシージャを呼び出す」や「AlteryxでGoogle BigQueryのGeography型にデータをロードする」にてPreSQL（SQLステートメント事前実行）にて無理やり（？）好きなSQL文を実行する方法をご紹介しましたが、この機能を使います。
SQLステートメント事前(事後)実行とは？
この機能ですが、機能名が長いので「PreSQL」もしくは「PostSQL」と私は略して呼んでいますが、英語だと「Pre SQL statement」「Post SQL statement」と記載されています。
このオプションに指定したSQL文を実行してくれる機能です。
そもそも、データ入力ツール、データ出力ツールでは、DBから読み込み、DBへ書き込む際にSQL文を発行してデータを取得したり書き込んだりしますが、そのSQL自体は１行しか指定できないようになっています。しかしながら、どのデータベースを使うのか？などDBによって事前に「use」文などで設定を行う必要があるようなものもあり、このような場合に「Pre SQL」を使います。また、一時的に使ったテーブルを削除したいような場合に「PostSQL」で「drop table」などをすることがあります。
つまり、DBの操作を柔軟にするための機能が「Pre/PostSQL」ということになります。
実際にやってみる
今回は、PostgreSQLを使って実際にINSERTしてみましょう。
データとして以下のようなデータを持っています。
ここに一行追加してみましょう。SQL文としては以下のとおりです。
INSERT INTP public."SampleTablewithUniqueKey"("Category","Item","No","UnitPrice") VALUES('meat','beef',6,200);まぁ、なんの変哲もないSQL文なので、データ出力ツールでもそのまま出力できる内容です・・・。
これをあえてPreSQLでやってみましょう。データ入力ツールの設定を開きます。ここで「ツール設定でPreSQLを実行する」のチェックを外しましょう（これを行わないと、ツールの設定を開く度に勝手にPreSQLが走ってしまいます）。
その後、「SQLステートメント事前実行」を選択します。これにより「SQLステートメント事前実行とストアド・プロシージャを設定」というダイヤログが開くため、「SQLエディター」をクリックし、SQL文をペーストしましょう。
これでOKをクリックします。
次に、データ入力ツールなので、何かしらSELECTするものを指定する必要があります。正常に書き込めているかどうか、件数などを確認したい場合はCOUNTなどをすれば良いですし、普通にSELECT * FROM～とデータを確認することも可能ですが、DBや格納されているデータ量によっては時間がかかったりするので、ここでは一旦ダミーのSQLを指定しておきます。
select 1 as "dummy"
これで設定は完了です。改めて設定必要な部分を赤色の枠で囲っておきます。
これを実行し、改めてデータ入力ツールなどで確認すると、無事に６行目に書き込んだレコードを確認することができます。
ただし、これは固定のSQL文を実行しているだけですので、INSERT文を大量に流し込みたい、といった場合はマクロ化する必要があります。つまり、バッチマクロ化し、PreSQL部分をコントロールパラメータ入力ツールで書き換えることで達成できます。
バッチマクロ化してみる
バッチマクロ化は非常に簡単です。コントロールパラメータ入力はデータ入力ツールのPre SQLを更新するように設定します。また、汎用的なマクロにするためには、接続IDもテキストボックスツールなどで書き換えるようにしておきます（DCM接続の場合）。
つまり、コントロールパラメーター入力に接続しているアクションツールの設定は以下のとおり「PreSQL – value」を選択します。
さらに、テキストボックスツールに接続されているアクションツールは以下のように「Connection – value」を選択します。
注意点
- MySQLなどでBulk Insertを行ったり、その他のDBで空間データを扱うような場合、SQL文が長くなりすぎるケースがあります（複数行をまとめて１つのSQLにするため）。この場合、Alteryxで１セル（？）に格納できるデータ量を超えるケースがあるため、適当にSQL文を分割して実行する必要があります。
- PreSQLで複数行のINSERT INTOを行う場合：
途中でエラーが起きた場合、それ以降のINSERT INTOはスキップされますが、それ以前のINSERT INTOは実際に書き込まれます（commitされます）。書き込みされたくない場合はトランザクションの処理を行う必要があります（begin～commitをPreSQLに入れます）。以下の例では、２行目のINSERT INTOが一意制約違反でエラーとなるため、commitがスキップされ何も書き込まれず終了します。
begin;
INSERT INTO public."SampleTablewithUniqueKey"("Category","Item","No","UnitPrice") VALUES('meat','chikin',8,100);
INSERT INTO public."SampleTablewithUniqueKey"("Category","Item","No","UnitPrice") VALUES('meat','beef',6,200);
commit;また、begin～commitしない場合、途中でエラーが出るとそれ以降は書き込まれません（SQLがスキップされます）。以下の例では、1行目が一意制約違反でエラーとなり、それ以降はスキップされます。
INSERT INTO public."SampleTablewithUniqueKey"("Category","Item","No","UnitPrice") VALUES('meat','beef',6,200);
INSERT INTO public."SampleTablewithUniqueKey"("Category","Item","No","UnitPrice") VALUES('meat','chikin',8,100);
- PreSQLはDBやドライバによって異なる挙動を行うことがあるようですので実際にやりたいことを検証してみてください
他にSQLを実行する方法はないの？
Alteryxでは、SQLを実行できるツールはいくつかあります。
- In-DBカテゴリの「In-DB接続」
- In-DBカテゴリの「動的入力In-DB」にてSQLを指定
- 入出力カテゴリの「データ入力」
- Pythonツール
- Rツール
しかしながら、どんなSQLでも実行できるわけではなく、入出力カテゴリの「データ入力」やIn-DBカテゴリの「In-DB接続」「動的入力In-DB」では、基本的にSELECT文を記載するのが前提になっており、「INSERT INTO」などと書こうものなら、頭に勝手に「SELECT」がつけられてしまいます（余計なお世話・・・）。
実際に自由にSQLを実行できるツールは、入出力カテゴリの「データ入力」「データ出力」のPreSQL・PostSQL機能のみとなります。
また、複数行のSQLが許可されているのは、
- In-DBカテゴリの「In-DB接続」
- 入出力カテゴリの「データ入力」「データ出力」の「PreSQL」もしくは「PostSQL」
のみとなります。
また、Pythonツール、Rツールは追加のパッケージをインストールすることで様々なDBに接続することができます。どうしても困った場合はPythonツール、Rツールの利用を検討することになります。
もしくは、コマンド実行ツールで外部コマンドラインを呼び出すことができるので、各DB用のコマンドラインツールを呼び出すのが「ノーコード」的には簡単かと思います（PythonツールやRツールより高速に動作するという利点もあります）。
まとめ
- PreSQL（SQLステートメント事前実行）により好きなSQL文を実行できることをご紹介しました
- DBやドライバなどで挙動が変わるので、事前によく検証してください
- どうしても困る場合は、PythonツールやRツールも検討してみてください
- ちなみに、MySQLでバルクロードを行いたい場合は、この方法を利用してInFileを使った方法が一番高速にデータをロードできるようですが、色々と事前処理がめんどくさいので、正直MySQL使いたくないですね・・・。
サンプルワークフローダウンロード
次回
未定です。
コメント