お世話になっております。
postgresqlやAmazon Redshiftを対象に、データ出力ツールによるバルクロード後のPost-SQLが意図通りに動作しません。
知見をお持ちの方がいらっしゃいましたら、アドバイス頂けますと幸いです。
背景:
データ出力ツールでは次のような処理を行っております。
(1) Pre-SQL: truncate table tb1;
(2) バルクロード: tb1テーブルに対して、データをバルクロード。
(3) Post-SQL: tb1テーブルを使用してtb2のデータをdelete from し、tb2テーブルにtb1データを加工しながらinsert into。(トランザクション処理)
上記の(3)では以下のようなことを行っております。
start transaction;
delete from tb2 where exists ( select 1 fom tb1 where <条件> );
insert into tb2 select <加工処理> from tb1;
commit;
詳細:
(3)にbegin transaction; ~ commit; を記載しておりますが、
Alteryx DesignerのPost-SQLで実行すると、start transaction; が認識されていないのか、後続処理の「insert into tb2 select <加工処理> from tb1;」で変換エラーが発生した場合、ロールバックされない状況です。
※Amazon Redshift、Postgresqlともに同様の挙動を示しています。
またDbeaverなどSQLクライアントより、(3)のSQLステートメントを実行すると意図通りに動くことを確認しています。
Alteryx DesignerのPost-SQLから、start transaction; ~ commit;で記載したSQLステートメントを
SQLエラー発生時でも正しく実行するために何か特別な設定が必要なのかと思いご相談させて頂きました。
よろしくお願いいたします。
<添付図>
Amazon Redshiftのクエリ履歴(stl_query)を確認すると、
Alteryx Designerから実行した場合は、Redshiftでは同一プロセスIDで処理されていますが、異なるトランザクションIDとなっております。
Amazon Redshiftに対して、(3)について挙動確認SQL文を投げた結果になります。
3~5行目はSQLクライアントから手動実行した結果で、同一トランザクションID(XID)になっております。
8~10行目は、Alteryx Designer - PostSQLから実行した結果で、XIDが異なる値になっております。
-- 検証用の簡易SQLステートメント
start transaction;
insert into tb1 values (値); ※成功
insert into tb1 values (値); ※成功
insert into tb1 values (値); ※成功
insert into tb1 values (値); ※エラー
commit;
<その他>
Amazon Redshiftのみで動作確認しておりますが
上記(3)の処理を、ストアドプロシージャ化(NONATOMIC)して
Alteryx Designer PostSQLからプロシージャーをcallする形を取れば、意図通りの結果を得ることができております。
解決済! 解決策の投稿を見る。
@Masa23 さん
出力オプションなどはどうされてますでしょうか?
ちょっとSnowflakeで試してみました。
Post SQL:
start transaction;
INSERT INTO "community_test" ("Field1","Field2") values (1,'a');
INSERT INTO "community_test" ("Field1","Field2") values (1,'b');
INSERT INTO "community_test" ("Field1","Field2") values (1,'2a'); Field2は1文字しか入らないフィールドです
INSERT INTO "community_test" ("Field1","Field2") values (1,'d');
commit;
この3行目のINSERT INTOでエラーを起こさせて、バルクで突っ込んだところまで残ることは確認できました、、、
Snowflakeの場合は、start transaction;などのコマンドもしっかり流れていることが確認できました(ただ、ロックがかかってしまうので、次にWFを流すと止まってしまうので、SQLのabortなりcommitなどしてあげないといけなかったです)。
以下、Snowflake側のログです。
ドライバの挙動などもありそうですが、、、
お忙しいところ、ご確認ありがとうございます。
マクロ内で使用しているデータ出力ツールの設定ですが以下の通りとなっております。
ヘルプも確認しているのですが、オプション項目で該当しそうなものはなさそうと考えております。
私の環境でもsnowflake環境で確認したところ、AkimasaKajitani様にお試し頂いた結果と同じになりました。
SQLステートメントを手動実行して動作検証しておりますが
Dbeaver SQLクライアントはJDBCドライバを使用しているので、
Mk-5 SQLクライアントからODBC経由でSQLステートメントを手動実行確認を行いました。手動実行だからかもしれませんが期待通りにstart transaction ~ commit; が機能している模様です。
@Masa23 さん
そうですね、おっしゃるとおり正直特に設定としてはないように見えますね・・・。
RedshiftだけではなくPostgreSQLでも発生しているというのが悩ましいところですね。
このあたりDriverでも挙動が変わることがあるので、むしろそちらの影響が大きい可能性もあるかもしれません・・・。
この結果を持ってCASE登録した方が良いかもしれないですね・・・。
ありがとうございます。
Post-SQLでエラーを起こした場合、ロールバックされる仕組みが必要かを含めて、CASE作成を検討しようと思います。
サポートに確認した結果、少なくとも現行バージョンでは対応していないとのことでした。
なるほど、何かしらAlteryxで勝手にやってるんでしょうね、、、