WeeklyAlteryxTips#97　2024.2以前のDesignerでIn-DBツールでPreSQLするには？

本ブログシリーズは、毎週AlteryxのTipsをお伝えしていく週刊AlteryxTipsブログです。

データパイプライン構築の際に、毎日データを特定のテーブルに投入したい、というケースはよくあるかと思いますが、普通にバルクインサートしていると、何かの拍子に２回実行しないといけない・２回実行してデータがだぶってしまった、というケースが出てくるかと思います。これが単なるミスなのか、誤ってスケジューラで動かしているワークフローを手元でも動かしてしまった、とか、色々とあるとは思いますが、１日分など特定の期間のデータを書き込む際に、先にDELETE文でその期間のデータを削除してから投入すればデータがだぶってしまう、という問題は回避されます。また、先に削除していれば、最初投入したデータに不具合があっても気にせずにワークフローを実行すれば良いだけ、ということになります。

※一意制限をつけたカラムがあればそもそもエラーになるのでこのような現象は起きませんが、SnowflakeなどのDWHの場合は一意制限がないケース（形としては一意制限をつけれるのですが、エラーとならない）がありますし、だぶらないにしても１日分を削除してから改めて追加したい、というケースはよくあるシナリオです。

しかしながら、2024.2以前のDesignerのIn-DBにはPreSQL機能がありません。このようなケースだとどうすればよいのでしょうか？

Contents

In-DBでPreSQLを行う方法
動的入力/出力In-DBを使った方法
まとめ
サンプルワークフローダウンロード
次回

In-DBでPreSQLを行う方法

以前の記事「WeeklyAlteryxTips#38　PreSQL(SQLステートメント事前実行)を使ってDBに書き込む方法」でPreSQLについてご紹介しました。

結論としては、PreSQLにはデータ入力/出力ツールを使うしかない、というのがDesigner 2024.2時点での結論です。これをバッチマクロを使ってIn-DB処理より先に実行するようにするのが今回ご紹介する方法です。

PreSQL用のバッチマクロを作る

まず最初にPreSQL用のバッチマクロを作成してみましょう。マクロは以下のようになります。

一応データベースの接続先が変わっても良いように、DCM接続のインターフェースをつけています。2024.2でもパッチレベルによってはインターフェースのDCM接続でうまく設定できないことがあるので、最新パッチでお試し下さい。

データ入力ツールを使ったPreSQLの実行では、何かしらSQLが必要になるので、今回は非常に単純なSQL（そして何も意味がないSQLです）として

SELECT 1

という文にしています。

コントロールパラメータで書き換える部分はもちろんPreSQLになります。

それではこのバッチマクロを組み込んでいきましょう。

PreSQLマクロを使ったワークフロー

PreSQLを使うために、以下のようにPreSQLで削除クエリを走らせるパートを先にコントロールコンテナで実行させ、その後IN-DBでデータを移すようにしています。

最初のテキスト入力ツールにベースとなるSQL文を記載しています。

DELETE FROM SalesSample_HighTech WHERE SalesDate =

実行日の前日のデータを移す、というのが一般的かと思いますが（とはいえデータソース次第です）、実行日の前日のデータを削除するSQLは以下のようにフォーミュラツールで作ります。

[SQL]+"'"+DateTimeFormat(DateTimeAdd(DateTimeToday(),-1,"Day"),"%Y-%m-%d")+"'"

これによって実際に2025/5/10に実行すると、以下のようなSQL文になります。

DELETE FROM SalesSample_HighTech WHERE SalesDate ='2025-05-09'

これが、次のPreSQLマクロで実行されたのちに、IN-DBパートに移ります。

In-DBパート

In-DBパートでは、前日のデータを抽出し、データを投入しています。今回は、SQLで前日のデータを抽出するようなSQLを組んでいます。Microsoft SQLサーバー用のSQLですが以下のようなSQLです。

select * 
from [SalesSample_HighTech_NewTransaction] 
where [SalesSample_HighTech_NewTransaction].[SalesDate] = DateAdd(day, -1, Cast(GetDate() as Date))

PreSQLとデータ抽出用のSQLの日付が全く同じものが使われているわけではないので、それが気持ち悪い、という場合は、動的入力/出力In-DBを使って、PreSQLで使用した日付をそのまま持ってくることもできます（確かにDB内の関数を使った場合、タイムゾーンの違いでDesigner内の日付とSQL内の関数とで異なる日付になる可能性もあるので、安全策を取るならこれから紹介する方法のほうが確実かもしれません）。

動的入力/出力In-DBを使った方法

こちらの方法は、PreSQL発行時に、Designerで作成した昨日の日付を、データ抽出でも使っているワークフローです。Designer側の一箇所で日付を作るため、ワークフロー実行時に日付をまたいだり、DBサーバーと実行しているDesignerのタイムゾーンの違いなどを気にする必要がないという方法になります。

IN-DB側は、接続In-DBでは

SELECT 1

というSQLを発行し、DBとの接続セッションを作成しています。そのセッション情報をそのまま動的入出力In-DBでDesigner側に持ってきて、改めてSQLを再作成し（PreSQL作成時に使った日付情報を反映します）、再度DBに接続しに行く、という若干回りくどい方法を取っています。もちろん、接続In-DBのSQLをバッチマクロで書き換える方法もありますが、今回は動的出力/入力In-DBを使った方法にしています（マクロ内に隠蔽するより通常のワークフローにしたほうが見通しが良い、というのが一つの理由です）。

ちなみに、動的出力In-DBツールの出力は以下のようになっています。

接続時のSQL文と、接続名が出力されています。この接続名はそのままにしておき、Queryのところを書き換えます。

このQueryとConnection Nameを動的入力In-DBツールで指定すれば、好きなSQL文でIn-DBでデータの抽出が可能です。

まとめ

In-DBで、PreSQLを適用する方法をご紹介しました。
また、In-DBで動的にデータの抽出（動的にSQLを書き換えて実行する方法）を行う方法をご紹介しました。
なお、Alteryx Serverをお使いの場合、DCM接続にコツがあるので、ナレッジ・ベースを参照してください

サンプルワークフローダウンロード

WeeklyBlog_97_Sample ダウンロード

次回

未定です。