Modeler 18.4/18.5のSnowflake関連の修正

Modeler 18.4/18.5のSnowflake関連の修正で気づいたものをまとめました。
# 1. to_integerが13桁までしかできない。
to_integerで13桁を超える文字列の場合にエラー: 「22003[100039] Numeric value '12345678901234' is out of range」や「[2024-05-31 15:32:59] 22018[100038] Numeric value '1234567890123456789' is not recognized」になります。フィックスを適用すると、19桁のBIGINTの範囲までプッシュバックが可能になります。

|改修前のSQL|改修後SQL|
|:----|:----|
|TRUNC(T0.STRINT14,0)|TO_NUMBER(T0.STRINT14)|

Known Issue: DT259082 An error occurs when 14 characters of data on Snowflake is set as a parameter to to_integer() and executed.

https://www.ibm.com/mysupport/s/defect/aCI3p000000LIHjGAO/dt259082?language=ja

SPSS Modelerの整数の範囲

https://qiita.com/kawada2017/items/4f6d25c21080fc223f32

# 2. randomの結果が不正確
## Modeler 18.4
エラーが起きたり、nullが返っていた。
|関数例|正しい範囲|改修前のSQL|問題点|改修後|
|:----|:----|:----|:----|:----|
|random0(2.0)| 0 <= ... < 2.0|(RANDOM() * 2.0000000000000000e+00) |22003[40470] [Snowflake][Support] (40470) Conversion error at column 2 and row 1: numeric value out of range.|2.0000000000000000e+00 - UNIFORM(4.9406564584124654e-324::FLOAT, 2.0000000000000000e+00, RANDOM())
|random(2.0)|0 < ... <= 2.0|(RANDOM() * 2.0000000000000000e+00)|22003[40470] [Snowflake][Support] (40470) Conversion error at column 2 and row 1: numeric value out of range.|UNIFORM(4.9406564584124654e-324::FLOAT, 2.0000000000000000e+00, RANDOM())|
|random0(2)| 0 <= ... < 2|FLOOR(RANDOM() * 2) |22003[40470] [Snowflake][Support] (40470) Conversion error at column 2 and row 1: numeric value out of range.|UNIFORM(0, 2-1, RANDOM())|
|random(2)|0 < ... <= 2|FLOOR(1.0 + (RANDOM() * 2)) |nullが返る|UNIFORM(1, 2, RANDOM())|

Known Issue: DT259274 Snowflake - Modeler 18.4 random0() and random() don't behavior correctly.

https://www.ibm.com/mysupport/s/defect/aCI3p000000LIKnGAO/dt259274?language=ja

## Modeler 18.5
randomとrandom0の結果がFLOATとINTEGERでそれぞれ不正確だった。


|関数例|正しい範囲|改修前のSQL|問題点|改修後|
|:----|:----|:----|:----|:----|
|random0(2.0)| 0 <= ... < 2.0|UNIFORM(0::FLOAT, 2.0000000000000000e+00, RANDOM())|2.0が含まれうる|2.0000000000000000e+00 - UNIFORM(4.9406564584124654e-324::FLOAT, 2.0000000000000000e+00, RANDOM())
|random(2.0)|0 < ... <= 2.0|UNIFORM(1.0, 2.0000000000000000e+00, RANDOM()) |1.0か2.0の2択になる。|UNIFORM(4.9406564584124654e-324::FLOAT, 2.0000000000000000e+00, RANDOM())|
|random0(2)| 0 <= ... < 2|UNIFORM(0, 2, RANDOM())|2が含まれうる|UNIFORM(0, 2-1, RANDOM())|
|random(2)|0 < ... <= 2|UNIFORM(1, 2, RANDOM()) |問題なし|改修なし|

Known Issue: DT259275 Snowflake - Modeler 18.5 random0() and random() don't behavior correctly.

https://www.ibm.com/mysupport/s/defect/aCI3p000000LIKsGAO/dt259275?language=en_US

# 3. データ区分ノードのサイズ指定が正しく反映されない
以下のようにサイズを50：50ではない値に設定した場合でも、大体50：50になってしまう。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/6b22053c-e642-3c33-124f-599ec7071dca.png)

修正前
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/4ac5fd4a-4d0b-6109-89ab-428638d9748b.png)
修正後
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/9816eb8d-57d8-a250-a893-475884701cb2.png)


|改修前のSQL|改修後SQL|
|:----|:----|
|SELECT T0."C_CUSTKEY" AS "C_CUSTKEY",(CASE WHEN (T0.HASHNUM < 80) THEN '1_学習' ELSE '2_テスト' END) AS "データ区分" FROM (SELECT T0."C_CUSTKEY" AS "C_CUSTKEY",FLOOR(RANDOM(12345678) * 100.0) AS HASHNUM FROM "TPCH_SF1".CUSTOMER T0) T0|SELECT T0."C_CUSTKEY" AS "C_CUSTKEY",(CASE WHEN (T0.HASHNUM < 80) THEN '1_学習' ELSE '2_テスト' END) AS "データ区分" FROM (SELECT T0."C_CUSTKEY" AS "C_CUSTKEY",UNIFORM(0, 100-1, RANDOM(1234567)) AS HASHNUM FROM "TPCH_SF1".CUSTOMER T0) T0|

Known Issue: DT260421 Modeler18.4 , 18.5 - In Snowflake, the settings of the [Partition] node are not reflected correctly.

https://www.ibm.com/mysupport/s/defect/aCI3p000000Xqg9/dt260421?language=ja

# 4. fix
フィックスが必要な場合は、IBM SupportにDT260421のフィックスを要求してください。１，２，３の問題修正フィックスが含まれます。

# 確認環境
* Modeler 18.4 IF052
* Modeler 18.5 IF012
* Windows 11 64bit