❄️
OracleからSnowflake移行のSQL非互換対応まとめ
記事の目的
Oracleからsnowflakeへの移行事例はそれなりにあると思いますが、
その技術的な課題や対処についてのノウハウはなかなか世に出る事はないと思います。
そこで皆様の一助になればよいと思い、
弊社が2022年にOracleからSnowflakeへ移行PoCをする中で検出した、
主にSQL非互換とそれに対する対応方法についてまとめたものを公開いたします。
また現在はSnowflakeより、マイグレーションツールであるSnowConvertが提供されています。
こちらで要件を満たせる場合は、ツールを活用した移行の実施が良いと思います。
一方でプログラム言語で書かれており、ツールによる置き換えが困難な環境であれば、この記事が参考になるかと思います。
なお、参考までに弊社のOracleからの移行含めた全体像は以下の記事をご覧ください。
注意
弊社で移行PoC時に存在したプログラムやDDL/DMLベースでの検証となり、PoC後の本番移行でのプログラム動作確認で新種が見つかったケースもあります笑
（クリティカルなものはありませんでしたが）
そのため、対応方法の網羅性や妥当性を保証するものではないため、どのような対応が必要なのかを大まかに捉える、規模感の参考としてください。
性能面の評価
そもそも取り扱うクエリのパターンや既存システムの規模とsnowflakeの適用サイズで大きく変わるので、自社内でしっかりと評価いただくのが何よりも重要です。
ただ弊社ではExadataで割り当てているCPUを元にLサイズをターゲットにした結果としては
現行システムを比べ、ほぼ半分の処理時間＝性能2倍に向上と判断しました。
とはいえ、ショートクエリ（処理時間が短いクエリ）では、Oracleの方が早いケースもあり、
ショートクエリというかトランザクション的な処理はOracleが強いと判断し、その上で許容範囲と判断しました。
※Oracleで1秒未満が、Snowflakeだと数秒というケースがありましたので特に速度がクリティカルな要件の場合は細かく確認しておいた方が良いと思います。
snowflakeの場合、ウエアハウス上げたり、ワークロード別に分割したり、クラスタリング貼ったり、Search Optimizationなど、色々工夫出来る手段があるので個人的にはよほど予算をケチらなければ性能問題はないと考えています！
非互換の精査方法
2022年にOracle Exadataからのクエリパターン別のPoCを実施し、SQL変換時に構文エラーや出力結果に差異が生じたものに対する移行方針を精査しました。
※JavaやPerlで実装しているケースが多く、またこの時点ではSnowconvertが提供されておらず、自力でやらざるを得なかった次第です。。
このパターン精査は、Snowflakeのドキュメントをベースにしつつ、Oracleの各プログラムを単純処理から複雑処理、特にPL/SQLの処理パターンなど、怪しそうな関数をピックしたり、ソースを何度もGrepしたりと洗い出しをひたすらしていきました。
※調査時点の結果のため、現在は解消しているものや対応方針が変わっている可能性があります
非互換一覧
弊社のPoCでは全部で54種検出しており、当然プログラム改修もかなりの規模となりましたが、
移行による性能向上や運用コスト削減が目的として、それとトレードオフとなる移行コストを試算するための互換性評価が要点でした。
以下にその詳細を対象や分類ごとにまとめておりますのでご確認ください。
構文系①（DDL）
Oracleのみに存在するDDL上の構文や制御に関する非互換のため、該当部分の削除（使えない）
| # | 対象 | 分類 | 非互換内容 | 対応方針 |
|---|---|---|---|---|
| 1 | DDL | 構文 | 以下オプションは不要となる PCTFREE NOLOGGING PARALLEL TABLESPACE COMPRESS FOR QUERY LOW/HIGH PCTUSED INITRANS MAXTRANS STORAGE SEGMENT CREATION IMMEDIATE |
該当の制御、機能が存在しないので、削除する。 |
| 2 | DDL | 構文 | EXECUTE DBMS_STATS.LOCK_TABLE_STATSプロシージャは使用できない。 | 該当の制御、機能が存在しないので、削除する。 |
| 3 | DDL | 構文 | パーティションの作成が出来ない、 | 該当の制御、機能が存在しないので、削除する。 |
| 4 | DDL | 構文 | 制約のENABLE指定できない。 | 該当の制御、機能が存在しないので、削除する。 |
| 5 | DDL | 構文 | SHOKAI_FLG CHAR(1) DEFAULT 0 NOT NULL のように、文字列型の項目のDEFAULT値に数値を指定できない（暗黙の型変換されない）。 |
SHOKAI_FLG CHAR(1) DEFAULT '0' NOT NULL のようにデータ型に合わせた値を設定する。 |
構文系②（DML）
OracleのSQL記述式の差異による非互換のため、該当部分の置き換えや構文修正で対応する
| # | 対象 | 分類 | 非互換内容 | 対応方針 |
|---|---|---|---|---|
| 14 | DML | 構文 | ROW_NUMBER() OVER() のOVER句にOrder byが必要。 | ROW_NUMBER() OVER(ORDER BY 1)の様にダミーのOrder byを入れる |
| 16 | DML | 構文 | NOLOGGINGは使えない INSERT INTO テーブル名 NOLOGGING |
該当の制御、機能が存在しないので、削除する。 |
| 17 | DML | 構文 | sysdateが使えない（sysdate()はTIMESTAMP型でUTC） |
CURRENT_DATEまたはCURRENT_TIMESTAMPに変換する。 |
| 18 | DML | 構文 | INSERT先のテーブルに別名を指定できない。 | 別名を削除する。※DELETE、UPDATEは問題なし。 |
| 19 | DML | 構文 | ORACLEのROWNUM擬似列は使用できない | ROW_NUMBER()を使った形式に書き換える。 Where ROWNUM <= 10のように行数を絞る場合は select TOP 10 ～ または select ～ from TABLE LIMIT 10; に変換する。 |
| 20 | DML | 構文 | 時刻に整数値を加減算できない。 | DATEADD関数を使用する。 |
| 21 | DML | 構文 | 旧ヒント句が原因でエラーになる。 | ヒント句（コメント）が書式の都合でエラーになるケースがある。 →旧ヒント句は全て除去する。 |
| 22 | DML | 構文 | Oracleでspool出力していたSQLをsnowsqlでCOPY INTO出力に変更した場合、 ||','||で項目を結合してデータを出力していると、aaa\,bbb\,ccc のように「\」が入った状態で出力されてしまう。 |
Oracleでspool出力していたSQLをCOPY INTO出力に変更する場合、 ||','|| ⇒ ,(カンマのみ)に修正する。 |
| 23 | DML | 構文 | 下記のようなSPOOLコマンドは使用できない。 SPOOL 'ファイル名' ～ SPOOL OFF |
下記COPY INTOコマンド(①)、またはoutput_fileオプション(②)に変更する。 ① COPY INTO @外部ステージ名/パス from ( ～ ) ② !set output_file=ファイル名 ～ |
| 24 | DML | 構文 | 下記の様にAS句を使用して全角文字で別名を付ける場合、二重引用符で囲まないとエラーが発生する。 SELECT AGE as 年齢 |
AS句を使用して全角文字で別名を付ける場合、二重引用符で囲むよう修正する。 |
| 25 | DML | 構文 | SQL実行完了後のEXITコマンドは使用できない。 | リターンコードを返していない場合削除する。 リターンコードを返している場合、#25の対応を実施する。 |
| 26 | DML | 構文 | MERGE文内のUPDATE文・INSERT文にWHERE句を設定できない。 | WHEN MATCHED THEN UPDATE ... WHERE ～ WHEN NOT MATCHED THEN INSERT ... WHERE ～ ↓以下の通りにケース述語に修正する WHEN MATCHED AND ～ THEN UPDATE ... WHEN NOT MATCHED AND ～ THEN INSERT ... |
構文系③（PL/SQL）
前提としてPL/SQLそのままでの移行は出来ないため、非互換部分を考慮しつつ、snowflakeスクリプト化することで対応を図ることになります
その際に非互換については以下のように該当部分の置き換えや構文修正で対応する
| # | 対象 | 分類 | 非互換内容 | 対応方針 |
|---|---|---|---|---|
| 27 | PL/SQL | 構文 | カーソルの宣言の構文差異 ・Oracle CURSOR C1 IS select ～
|
変更する ・Snowflake C1 CURSOR FOR select ～
|
| 28 | PL/SQL | 構文 | if～else if ～の構文差異 ・Oracle IF(～) then ～ ELSIF(～) END IF;
|
変更する ・Snowflake IF(～) then ～ ELSEIF(～) END IF;
|
| 29 | PL/SQL | 構文 | その他例外の処理ブロックについての構文差異 ・Oracle WHEN OTHERS THEN
|
変更する ・Snowflake WHEN OTHER THEN
|
| 30 | PL/SQL | 構文 | DBMS_ERRLOG.CREATE_ERROR_LOGプロシージャは使用できない。 | 該当の制御、機能が存在しないので、削除する。 |
| 31 | PL/SQL | 構文 | DBMS_OUTPUTでログ出力できない。 | メッセージ出力用の配列の利用してログ出力させる。 ① メッセージ出力用の配列を定義 ② ログ出力させたいタイミングでメッセージを配列に格納 ③ 処理終了時にメッセージ配列をテーブル形式に変換し、返却する |
| 32 | PL/SQL | 構文 | EXIT 100 のように任意のリターンコードを返却できない。 | #24のメッセージ出力用の配列を利用して任意のリターンコードを返却できるようにする。 ① 配列の一番最後にリターンコードを設定。 ② SQLの実行結果は一時ログファイルに書き込み ③ 一時ログファイルの最終行の内容をリターンコードとし、エラーハンドリングする |
| 33 | PL/SQL | 構文 | DECLARE～ENDをexecute immediate $$ ～ $$ で囲む必要がある |
DECLAREの前にexecute immediate $$、ENDの後に$$を追記する。 |
| 34 | PL/SQL | 構文 | カーソルを開く際にusing句でバインド変数を指定する必要がある。 |
OPEN C1;↓ OPEN C1 using (vByteCd); のようにする。 |
| 35 | PL/SQL | 構文 | OracleではPL/SQLブロックの外で宣言されたバインド変数の代入時には以下のように左辺の変数名の前にコロン（:）をつける必要があるが、Snowflakeスクリプトでは、ブロックの外（DECLAREの前）に変数宣言ができないので、他の変数と同じよう扱う必要がある。VAR P_RET NUMBER;DECLARE ～ BEGIN ～:P_RET := RC_NG;
|
変数宣言はDECLARE句に移動する。 変数の代入も以下の通り変数名の前のコロンを削除する。 現: :P_RET := RC_NG;新: P_RET := RC_NG;
|
| 36 | PL/SQL | 構文 | DBMS_LOCK.SLEEPで指定秒の処理の待機ができない。 |
call system$wait(n)でn秒待機するように変更する |
| 37 | PL/SQL | 構文 | IF文の条件部分に括弧が無いとエラーとなる。IF XXX = YYY THEN ～
|
条件部分を括弧で囲むIF (XXX = YYY) THEN ～
|
| 38 | PL/SQL | 構文 | 直前のDMLの実行結果の取得にSQL%ROWCOUNTは使えない |
■ EXECUTE IMMEDIATEを使用せずにDMLを実行した場合SQLROWCOUNTへと修正する。更新件数0件の場合、 SQLROWCOUNTはNULLとなるので、以下の修正が必要。① DECLARE句で以下のような数値型の変数を用意 V_ROWCOUNT NUMBER;② DML実行後に処理件数が0件か判定し、宣言していた変数に値を設定 IF (SQLFOUND = TRUE) THEN V_ROWCOUNT := SQLROWCOUNT; ELSE V_ROWCOUNT := 0; END IF;
|
| 39 | PL/SQL | 構文 | 統計情報取得のDBMS_STATS.GATHER_TABLE_STATSは使えない。 |
該当の制御、機能が存在しないので、削除する。 |
| 40 | PL/SQL | 構文 | select句ではない場所でCURRENT_TIMESTAMPが使用できない。 ・変数の設定 msgTmp := TO_CHAR(CURRENT_TIMESTAMP, 'YYYY/MM/DD HH24:MI:SS') || 'ログメッセージ'→エラーとなる ・select句 select CURRENT_TIMESTAMP;→正常に結果が返る |
CURRENT_TIMESTAMP()へと修正する。msgTmp := TO_CHAR(CURRENT_TIMESTAMP(), 'YYYY/MM/DD HH24:MI:SS') || 'ログメッセージ'
|
| 41 | PL/SQL | 構文 | カーソルでのループ処理の終了にEXIT WHEN カーソル名%NOTFOUNDが使用できない。 |
IF文で終了条件を判定しbreakでループを抜けるように修正する。IF (終了条件) THEN break; END IF;
|
| 42 | PL/SQL | 構文 | RAISE_APPLICATION_ERRORでユーザが定義する例外をスローできない。RAISE_APPLICATION_ERROR(-20001, 'エラーメッセージ');
|
DECLARE句で例外を定義しておき、RAISEで例外をスローする。DECLARE myException exception (-20001, 'エラーメッセージ'); BEGIN RAISE myException; END;
|
| 43 | PL/SQL | 構文 | 変数の定義時に、以下のようにカラム名%TYPEでデータ型を指定できない。kigyoCd SYOHIN.TEST_CD%TYPE;
|
参照しているテーブルの定義を確認して、定義に沿うように修正する。kigyoCd SYOHIN.TEST_CD%TYPE;↓ kigyoCd CHAR(4);※SYOHIN.TEST_CDの定義が CHAR(4)の場合 |
| 44 | PL/SQL | 構文 | 数値型のINTに桁数指定ができない。 |
桁数指定しているものに関してはNUMBERにする。※ INTに桁数指定していない場合は修正不要 |
| 45 | PL/SQL | 構文 | 以下のようなSQLPLUSのオプションは使用できないSET SERVEROUTPUT ONSET LINE 5000SET PAGES 0SET TRIM ONSET TRIMSPOOL ON
|
該当の制御、機能が存在しないので、削除する。 |
| 46 | PL/SQL | 構文 |
WHENEVER～は使用できない |
該当の制御、機能が存在しないので、削除する。 |
| 47 | PL/SQL | 構文 | カーソルのクエリにバインド変数をそのまま設定するとエラーになる。 カーソルを開く際に using句でバインド変数を指定する必要がある。 |
カーソルのクエリにバインド変数を含める場合、?を設定する。カーソルのオープンをする際に OPEN C1 using (vByteCd);のようにする。 |
| 48 | PL/SQL | 構文 | 以下のようにSQL文字列の変数設定を複数回に分けて実施すると、変数設定部分に時間がかかる。V_SQL := 'INSERT INTO ' || regTableName || '' || CHR(10);V_SQL := V_SQL || '(' || CHR(10);V_SQL := V_SQL || ' A ,' || CHR(10);V_SQL := V_SQL || ' B ,' || CHR(10);... EXECUTE IMMEDIATE V_SQL;
|
変数設定を1回だけ行うようにする。V_SQL := 'INSERT INTO ' || regTableName || CHR(10) || '(' || CHR(10) || ' A ,' || CHR(10) || ' B ,' || CHR(10) || ' C ' || CHR(10) || ')' || CHR(10) || ' SELECT' || CHR(10) || ' WP1.A ,' || CHR(10) || ' FROM ' || targetTableName || ' WP1';EXECUTE IMMEDIATE V_SQL;
|
関数差異
関数名は同じだが、振る舞いや仕様の差異があるもの。適切な関数への置換や記述の調整が必要
| # | 対象 | 分類 | 非互換内容 | 対応方針 |
|---|---|---|---|---|
| 7 | DDL | 関数差異 | DATE型は秒数を持たない。 | 秒数が必要なパターンがなかったため、対応なし ⇒影響する場合は時刻項目を持つ型に変換が必要 |
| 8 | DDL | 関数差異 | TIMESTAMP WITH TIME ZONE型の宣言方法が異なる | TIMESTAMP(6) WITH TIME ZONE →TIMESTAMP_TZ(6)に修正する。 |
| 9 | DDL | 関数差異 | DEFAULT CURRENT_TIMESTAMPが指定できない | CURRENT_TIMESTAMPに()をつける TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP → TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP() |
| 12 | DML | 関数差異 | YYYYMMDD形式の文字列項目をDATE型へ暗黙の型変換してADD_MONTHSしている箇所がSnowflakeではOracleと結果が異なる。※エラーにはならない | TO_DATEでDATE型に変換後ADD_MONTHSするように修正する。 |
| 13 | DML | 関数差異 | Oracleと異なり、snowsqlはYYYYMMDD形式の文字列項目をDATE型へ暗黙の型変換しないため、YYYYMMDD形式の文字列項目でMONTHS_BETWEENを使用している箇所で下記エラーが発生する。 >SQLコンパイルエラー： 関数 EXTRACT は VARCHAR(8) 引数型をサポートしていません |
TO_DATEでDATE型に変換後にMONTHS_BETWEENで扱うように修正する。 |
| 15 | DML | 関数差異 | TO_CHAR(DATE型またはTIMESTAMP型,'D')で曜日を表す整数値を取得できない。 日：1 月：2 火：3 水：4 木：5 金：6 土：7 |
EXTRACT関数へと変換する。 EXTRACT(dayofweek, DATE型またはTIMESTAMP型) で曜日を表す整数値を取得する。 取得結果はOracleと異なりUNIX 標準の以下の通りの値となり、結果ずれるため、取得結果に1を足す。 日：0 月：1 火：2 水：3 木：4 金：5 土：6 |
データ型、NULLの取り扱い
Snowflakeは文字型が集約されており、運用しやすいのだが、
レガシーから移行の場合、Charなどの固定桁数を埋めるような振る舞いは個別に入力値の確認が必要
またOracle特有の、”よしなにやってくれる”が、NULL周りでは鬼門となる
| # | 対象 | 分類 | 非互換内容 | 対応方針 |
|---|---|---|---|---|
| 6 | DDL | データ型 | 文字列型は全てVARCHARとなる。 CHAR、NCHAR、VARCHAR2、NVARCHARを指定して作成可能だが、全てVARCHARとなる。 指定する桁数についてはバイト数ではなく文字数となる。 |
自動的に変換されるため対応なし ただし、空白処理周りで影響するため、データセット時に補完等は個別に検討 |
| 10 | DML | nullの扱い | null項目を含む文字列結合した場合、結果がNULLとなる 例） select 'AAA' || null || 'BBB'・Oracle → 'AAABBB' ・Snowflake → NULL |
NULLを返す可能性のある項目と文字列結合する場合はNVL(AAA, '')のようにNULL時に空文字に変換するように修正が必要。暗黙の型変換がされないので、対象の列の定義が文字列以外の場合は NVL(TO_CHAR(AAA), '')のように文字列に変換してからNVLする。 |
| 11 | DML | nullの扱い | 空文字の扱いがOracle⇔Snowflakeで異なる。 Oracleは空文字はNULLとなるが、Snowflakeでは ''とNULLは別となる |
空文字の仕様差異に沿って実際のプログラム時に影響を見て調整する |
その他のケース
上記に該当しないような個別にケースや特定パターンで生じる対応
※PoC時点での検出内容ですが、本番移行対象のプログラムの中でも新種が生まれています。。
| # | 対象 | 分類 | 非互換内容 | 対応方針 |
|---|---|---|---|---|
| 49 | PL/SQL | 変数 | SQL中に変数を使用できない。 | 変数の前に「:」を付ける。 例「:V_SERVICE_KBN」 |
| 50 | snowsql | トランザクション | デフォルトでは自動コミットがオン。 | 明示的にトランザクションを開始していない更新処理を実行する際は以下セッションパラメータ設定を行い自動コミットをオフにする必要がある。 Alter session set autocommit = false; begin ～ endで明示的なトランザクションを開始している場合は問題なし。 ※ただし、execute immediate $から始まる匿名ブロックは明示的なトランザクション開始とはならない。 |
| 51 | snowsql | 変数 | SQLファイル実行時の変数の設定方法の差異 ・sqlplus ・SQL where ALC_CD = '&1' AND RCV_DATE = '&2' ・実行 sqlplus @ファイル名 変数1 変数2 ・snowsql ・SQL where ALC_CD = '&変数名1' AND RCV_DATE = '&変数名2' ・実行 snowsql -f ファイル名 -o variable_substitution=True -D 変数名1=変数1 -D 変数名2=変数2 |
&変数名の形式にし、snowsql実行時に以下の例の通りオプションを設定する。 例 -o variable_substitution=True -D ALC_CD=1111 -D RCV_DATE=20220427 -o variable_substitution：TrueにするとSQL中の変数を利用可能になる（デフォルトFalse） -D：--variableと同義。変数を定義する。 変数名は1、2のような数字も使用できるので、SQL側は修正せず、実行側を以下のような対応でもよさそう。 -o variable_substitution=True -D 1=1111 -D 2=20220427 →&1に9072、&2に20220427が設定される |
| 52 | snowsql | ファイル編集 | Oracleでspool出力している処理で、spoolしたファイルに対してヘッダ付与等のファイル編集を行っている場合、COPY INTO出力に変更するとBLOBに出力ファイルが作成されるようになるため、BLOBから出力ファイルをダウンロードしないとファイル編集が行えない。 | 出力したファイルへ固定のヘッダをアペンドする対応を追加する |
| 53 | PL/SQL | 動的SQL | EXECUTE IMMEDIATEを使用してDMLを実行した場合に 以下のようにEXECUTE IMMEDIATEを使用してDMLを実行するとSQLROWCOUNTは常にNULLとなるので使用できない V_SQL VARCHAR := 'insert into T1 select * from T2'; EXECUTE IMMEDIATE V_SQL; |
以下のように対応する。 ①DECLARE句で以下のような数値型の変数と、resultset型の変数を用意 V_ROWCOUNT NUMBER; rs resultset; ②DML実行resultsetに格納し、cursor fetchで処理件数を設定 rs := (EXECUTE IMMEDIATE V_SQL); let cur cursor for rs; open cur; fetch cur into V_ROWCOUNT; close cur; |
| 54 | PL/SQL | 動的SQL | EXECUTE IMMEDIATEを使用して登録・更新どちらも実施するMERGEを実行した場合、 以下のようにEXECUTE IMMEDIATEを使用して登録・更新どちらも実施するMERGEを実行したすると更新件数と登録件数に項目が分かれる ※更新のみ、または登録のみのMERGE文の場合、INSERTの時と同様のやり方でよい |
①DECLARE句で以下のような数値型の変数を2つと、resultset型の変数を用意 V_ROW1 NUMBER; V_ROW2 NUMBER; rs resultset; ②DML実行resultsetに格納し、cursor fetchで処理件数を設定 V_SQL := 'merge into merge_test1 t0 using merge_test2 t1 on t0.id = t1.id when matched then update set t0.name = t1.name when not matched then insert (id, name) values (t1.id, t1.name)'; rs := (EXECUTE IMMEDIATE V_SQL); let cur cursor for rs; open cur; fetch cur into V_ROW1, V_ROW2; close cur; 合計件数を取る場合、V_ROW1 + V_ROW2とかにする。 |
元々PoC時に課題管理していたEXCELベースですので、
見づらいなーとかこうして欲しいなど要望あれば、Xのアカウント宛にご相談ください。
かなり細かい内容なので、役に立たないかもしれませんが、
お役に立つようでしたら、いいね❤を付けていただければ、
モチベーション上がってまた何か書き始めるかもしれません笑
皆さんのモダンデータスタックに向けたレガシー脱出の一助になれば幸いです。
（弊社も頑張ります！）
Discussion