InDB処理で連番を作成したい。

[kobaken]

InDB処理の先頭でSQLを使用し、後続で対象テーブルにデータを挿入する。

という処理を使っているのですが、特定のカラムに連番を入れるという処理を追加することになりました。

DBはSQL Databaseなのですが、SEQUENCEを使用し以下のSQLを作成しました。

--SEQUENCEの定義

CREATE SEQUENCE スキーマ名.testid
AS bigint
START WITH 1
INCREMENT BY 1
CYCLE
CACHE

--SELECT文の実行

SELECT
next value for スキーマ名.testid

,NAME

,COMPANY

,ADDRESS

FROM TEST

WHERE xxx=xxx

InDBのクエリビルダでテストをすると問題ないのですが、

ワークフローを実行すると以下のようなエラーとなります。

Error: Write Data In-DB (11): Error running PreSQL on "NoTable":

[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]
NEXT VALUE FOR function is not allowed in check constraints, default objects,
computed columns, views, user-defined functions, user-defined aggregates, user-defined table types, sub-queries,
common table expressions, derived tables or return statements.

ちなみに上記、SQL構文を使用し以下のようなINSERT句を実行しても問題ないことは確認済みです。

INSERT INTO TEST_TAGET

～先ほどのSELECT文

また、以下のような処理でトライしてみたのですが1時間たっても全く何も処理が進まなかったため諦めました。

InDBの処理限定で連番を付与できる処理について、何かお知恵を拝借できないでしょうか？

よろしくお願いいたします。

 

[AkimasaKajitani]

In-DBについてはよくわかっていないのでそちらへのコメントは避けますが、

GenerateRow（行生成）ツールの使い方について気になります。

このツールは、各行に対して1-9999の連番を生成するため、

例えばインプットの行数が3の場合は3×9999行が生成されてしまいます。

それで時間がかかっているのではないかと思います。

連番をつけるのであれば、Multi-Row Formula（複数行フォーミュラ）の方が良いかと思います。

単に連番つけるだけであればレコードIDというツールもあります。

ちなみに、Sequenceというカラムが自動的に番号をつけてくれるようなものであれば、

DB挿入時に自動的に生成されるということはないのでしょうか？（MSのAccessだとそういう動きをしたかと思いますが）

[kobaken]

AkimasaKajitaniさん

返信ありがとうございます。

実はあのあとGenerateRowツールについて、

使い方が誤っていることに気が付きました。

申し訳ありません。

正しくは、レコードIDを使うべきでした。

ただ、GenerateRowツールの代わりにレコードIDツールを

使用しても、処理に時間がかかりすぎてエラーとなってしまいます。

テーブル定義でSequenceを設定し、自動的に連番を付与する方法も

トライしてみたのですが、InDB処理ではエラーとなり使用できませんでした。

SQLでデータ呼び出し→テーブル呼びだし→データを流し込む

という処理順になるのですが、SQLに連番対象となるIDを入れないと、

カラム数が足りずエラーになります。

（実際には連番にSQL記述の先頭カラムをInsertしようとしてエラー。）

という状況のため、連番をふれないか。という質問をさせていただいております。

[AkimasaKajitani]

＞ただ、GenerateRowツールの代わりにレコードIDツールを

＞使用しても、処理に時間がかかりすぎてエラーとなってしまいます。

　とのことですが、具体的にどこでエラーが出るのでしょうか？

　どのツールでエラーが出て、そのエラー内容がどんなものであるか

　わかりますと、原因を特定しやすいのではないかと思います。

　DBも絡んできているので環境含めてなかなか難しいですね・・・。

　ちなみに、毎回全データを抜いて１から連番を振るイメージなのでしょうか？

[kobaken]

AkimasaKajitaniさん

＞とのことですが、具体的にどこでエラーが出るのでしょうか？

＞どのツールでエラーが出て、そのエラー内容がどんなものであるか

＞わかりますと、原因を特定しやすいのではないかと思います。

エラーのスクショを取得しようと試しに再実行してみたところ、

正常終了しました。

ただし、40万件弱の処理件数で2時間30分かかってしまったため、

やはり実用には耐えなそうです。

エラー内容を覚えている限りでお話しするとDB接続のツールあたりで、

ODBCの接続タイムアウトでエラーが出ていました。

（処理に時間がかかりすぎているのでしょうか？）

＞ちなみに、毎回全データを抜いて１から連番を振るイメージなのでしょうか？

はい。毎回洗い替えのイメージになります。（テーブルデータをTruncateし、Insert処理を行う）

[AkimasaKajitani]

ODBCのタイムアウトということは、時間がかかりすぎている感じがします。

読み込みと書き込みがストリーミングで処理されていますので、

一旦データを読み込んでから書き込み処理に移るイメージで

完了までブロックツールを挟んでみてはいかがでしょうか？

これであれば読み込みがすべて終わってから書き込み処理に

移る形になるので、速度的には早くなると思います。

[kobaken]

AkimasaKajitaniさん

改善案ありがとうございます。

早速トライしてみたのですが、結果は10分短縮。

やはりInDB処理から一度出てしまうと、パフォーマンス的に

かなり厳しいことがわかりました。

InDBで連番振るのは難しいのでしょうか・・・。

もし解決しないとバッチ処理に逃げることになるのですが、

Alteryxを使っている意味が薄れるので避けたいところです。

ひとまずご報告まで。

[Mizuno · Accepted solution]

Over　OrderBYが必要なので並び替えが発生しますが、問題なければROW_NUMBER関数を使用してあげましょう。

IｎDBでできることは基本的にDB側の機能に依存します。SQLServerだとこの方法で連番はふれます

[AkimasaKajitani]

色々と調べてみたのですが、２つわかりました。

①Mizunoさんが提示している方法

　Identityを設定しているフィールドに対してはできないようです（Accessでいうところのオートナンバー型に設定）。

　逆に、Identityを設定しなければこれでいけます。

②データ出力ツールならPreSQLで「SET IDENTITY_INSERT なんちゃらTable ON」を追加すれば

　連番が振られます。事前に「SET IDENTITY_INSERT なんちゃらTable ON」としておけば普通に連番で書き込みできます。

　※PreSQLだとどうもエラーが出てしまいます・・・

　ただし、一度外部に出すので時間がかかるという欠点は克服できていません。

　また、他のテーブルをONにしているとできなくなります（他のテーブルをOFFにしてから

　データを挿入したいテーブルをONにする必要があります）

　※ONにしていてもIn-DB書き込みだとなぜかうまくいかないです

　どうも、In-DBでIdentity設定するとうまくいかないような感じです・・・（Alteryx社も認識している問題とのことです）

　Identity列にこだわりがなければ、①のフォーミュラIn-DBで「ROW_NUMBER() Over(ORDER BY カラム名)」

　が良いかと思います。

[kobaken]

Mizunoさん

まさにこれです！

というかRow_Number()が使えるとは思いませんでした。

その発想に至らなかった自分が恥ずかしいです。

もう半分諦めの境地だったので、とても助かりました！