Oracle DBからInput Data Toolで抽出前にランダムサンプリングしたい

[Yoshiro_Fujimori]

いつもお世話になっております。

Oracle DBにある比較的大きなテーブル(約17GB)からデータを抽出したいのですが

Input Data ToolでVisual Query Builderからwhere条件で絞ろうとすると

どうしても4GB程度までしか絞り込みができません。

Alteryx側のデータの置き場の制約があり、もう少しデータを絞りたく、ランダムサンプリングを試みているのですが、

Input Data ToolのSQL Editor画面で以下のように書いた後に"Test Query"すると；

SELECT * 
  FROM (SELECT [列名]
          FROM [テーブル名]
         WHERE [絞り込み条件])
WHERE random() < 0.0001

以下のようなエラーとなります。

（無視してWorkflowを実行しても同じエラーがResult Windowに表示されます。）

Input Data ToolからのSQLでは上記のような入れ子構造のSQLは実行できないのでしょうか。

なお、このサイト https://dev.classmethod.jp/articles/alteryx-in-db/　を見て、

In-DBツールで行けるのかな、とも思っておりますが、まだ試しておりません。

In-DBツールが目指す方向であれば、そちらに方向転換いたします。

[gawa]

@Yoshiro_Fujimori さん

弊方のSQL SERVERで同様なことができないか試したところ、以下のようにするとうまくいきました。

WITH句で一時表を定義して、それを参照するようにすると良いかと思います。また、ORDER BY NEWID()　を使うことで、呼び出すたびにランダムに並び順が変わります。

WITH TEMP AS (SELECT [列名] FROM [テーブル名] WHERE [絞り込み条件])
SELECT * 
  FROM TEMP
ORDER BY NEWID()

また、ファイルインプットのRecord Limitを適切な値にすることで、読み込む件数を指定できます

※SQL Editor内でのLIMIT句指定はエラーになりました・・・

参考になれば幸いです

[Yoshiro_Fujimori]

@gawa さん　ありがとうございます。

いただいたようにWITH句で囲ってみたところ、私の環境では同様のエラーとなりました。

Designer Versionは 2021.3.4.00445 で少し古いですが、その後のリリースノートでSQL関連の修正をみても関連しそうなものはないようです。

@gawa さんのSQL Server環境では動いているということでしたら、Oracle固有の問題として報告してみようかと思います。

[Yoshiro_Fujimori]

（Replyの場所を間違えたので、削除・再作成しております）

@gawa さん　ありがとうございます。

いただいたようにWITH句で囲ってみたところ、私の環境では同様のエラーとなりました。

Designer Versionは 2021.3.4.00445 で少し古いですが、その後のリリースノートでSQL関連の修正をみても関連しそうなものはないようです。

@gawa さんのSQL Server環境では動いているということでしたら、Oracle固有の問題として報告してみようかと思います。

[gawa]

@Yoshiro_Fujimori さん

私の環境は Designer 2022.1、Microsoft SQL Server　ですので、少し状況が異なるかもしれませんね。

ひょっとすると、こちらのマスクした部分の記述でエラーが出ている可能性がありますので、まずはWITH句内のクエリーを「select * from {テーブル名}」のように単純化し、入力ツールの設定でレコード数を10くらいに設定してエラーがでるかどうか試されるといいかもしれません。

それでもエラーが出る➡WITH句自体が使えない。OracleあるいはDesginerのバージョンの問題？

エラーが出なければ➡WITH句はOK。とすると、下記マスク部分でエラーが出そうな記述してないか？

のように原因を潰していくのが良いかなと思います。

AlteryxのSQL Editorでは、Random()やLIMITがあるとエラーが出ていたので「SQLの文法的に常識でしょ」と思う記述が、意外とアウトなのかもしれませんね・・・。

あまりお役に立てず、申し訳ないです

[Yoshiro_Fujimori]

訂正です。接続先DBはOracleだと思っていたのですが、確認したところDB2でした。

（どちらともDB接続しているため混乱しました。文脈上は影響ないと思いますが訂正しておきます。）

本件は別途 Issueとして報告したうえで、Issueへのリンクを張った後 クローズとさせていただきます。

ありがとうございました。

[AkimasaKajitani]

@Yoshiro_Fujimori さん

DB2の話を見る前に書いてしまったのですが、念のため投稿します。

----

In-DBの方がすっきりする気がします・・・。ただ、ちょっと試したところ（SQL Serverで）、ランダム関数がなんかいい感じで返してくれないですね、、、異なるシード値を渡さないと同じ値が返ってくるので、数値型のユニークなIDを渡す必要があるようです（１～１０の範囲だと１～１０の順番通りだったのであんまりランダムじゃない・・・）

あと、Oracleだと以下のようなSQLでランダムに抽出できていいるという話もあるので試してみてはいかがでしょうか？

SELECT * FROM (SELECT * FROM hogehoge ORDER BY dbms_random.random) WHERE ROWNUM <= 10;

ソース：https://2lat.net/it/oracle-random

[Yoshiro_Fujimori]

@AkimasaKajitani さん　ありがとうございます。

訂正が遅れまして申し訳ありません。

In-DBはDB2をサポートしていないので本件自体の解決とはなりませんが、

いただいたサンプルはもう一方のDB接続で勉強させていただきます。

もう少しサブクエリで頑張ってみることにして、シンプルなSQLを試してみます。

[Yoshiro_Fujimori · Accepted solution]

order by random関数の動作を OracleとDB2の両方で試してみました（状況報告まで）。

Oracle

SQL

select * from (select * from [Oracleテーブル名] order by dbms_random.random) where RowNum <= 10

Message

Input Data (2) DataWrapOCI: Unable to prepare the query: "select * from (select * from [Oracleテーブル名] order by dbms_random.random) where RowNum <= 10" Error: ORA-00904: "DBMS_RANDOM"."RANDOM": –³Œø‚ÈŽ¯•ÊŽq‚Å‚·B¶¶

メッセージが文字化けしていますが、ORA-00904は「無効な識別子です」という意味のOracleが出すエラーらしいので、

OracleのODBCドライバー(?)から「"DBMS_RANDOM", "RANDOM"なんて単語は知らないぞ」と言われている気がします。

ODBCドライバーがちゃんと仕事をしていない疑惑？

DB2

SQL

select * from (select * from [DB2テーブル名] order by dbms_random.random) where ROWNUM <= 10

Message

Input Data (1) Error opening "select * from (select * from [DB2テーブル名] order by dbms_random.random) where ROWNUM <= 10": No Columns Returned.

dbms_random.randomをrand()に置き換えても同じエラーになりました。

このメッセージを出しているのがAlteryxアプリなのかDB2のODBCドライバーなのかわかりませんが、

「そもそも文法が違ってて列を返せないぞ」と言われている気がします。

とりあえず情報を集めたので、Issue報告したいと思います。

[AkimasaKajitani]

@Yoshiro_Fujimori さん

単純なところから確認してみますと、例えば以下のようなSQL

SELECT * FROM(
select SampleSuperStore_en.*,
from SampleSuperStore_en )

でも、通らないので、こういう記載のやり方はNGな気がします・・・（SQL Server）