AWS SCTの変換後DDL修正

概要

OracleからRedshiftへの移行でAWS SCTを使う時に、変換されたDDLがそのままでは適用できなかったり、適用できても変換前と違う動作をしている場合があります。その修正点のメモです。

背景

以下の記事の派生です。SCT自体の使い方は元記事をご参照ください。

AWS SCTを使ってOracleからRedshift用のDDLを作成する
OracleをRedshiftに移行するために、AWS SCT(Schema Conversion Tool)を使ってRedshift用のDDLを作成する手順の解説です。
myusei.com
2026.03.15

やりかた

基本方針としては、SCTで変換したDDLを適用してみて、エラーが出た箇所を修正していき、全部適用できるようになったら、移行元のOracleと移行先のRedshiftで実行結果が一致するかを確認するのがよいと思います。

以下に、私が確認できたエラーが発生する箇所と対処方法を記載します。

TO_CHARがaws_oracle_ext.TO_CHARに変換される

OracleでTO_CHARを使った定義をしたとします。

CREATE TABLE to_date_test_table (
    id NUMBER PRIMARY KEY,
    created_date CHAR(8) DEFAULT TO_CHAR(SYSDATE, 'YYYYMMDD')
);

SCTでの変換結果は次のようになります。

CREATE TABLE to_date_test_table(
    id NUMERIC(38) ENCODE AZ64 NOT NULL,
    created_date CHARACTER VARYING(24) ENCODE LZO DEFAULT aws_oracle_ext.TO_CHAR(TRUNC(SYSDATE), 'YYYYMMDD', 'AMERICAN')
) DISTSTYLE AUTO SORTKEY AUTO;

おそらく、ほとんどの環境でこれを実行するとaws_oracle_extモジュールが追加されていないため、not existエラーが発生します。(エラーが出なかったとしてもロケールがAMERICANになっているので要修正)

やっている事はただのTO_CHARで、aws_oracle_extモジュールを使わなくとも、Redshiftの標準関数で十分なので修正しましょう。”aws_oracle_ext”と’AMERICAN’をとってあげるだけです。

-- 修正前
created_date CHARACTER VARYING(24) ENCODE LZO DEFAULT aws_oracle_ext.TO_CHAR(TRUNC(SYSDATE), 'YYYYMMDD', 'AMERICAN')
-- 修正後
created_date CHARACTER VARYING(24) ENCODE LZO DEFAULT TO_CHAR(TRUNC(SYSDATE), 'YYYYMMDD')

ADD_MONTHS周辺が変なことになる

OracleでADD_MONTHSを使った定義をしたとします。

CREATE VIEW add_months_test_view AS
SELECT
    id,
    created_date,
    TO_CHAR(ADD_MONTHS(TO_DATE(created_date, 'YYYYMMDD'), -1), 'YYYYMMDD') AS last_month
FROM to_date_test_table;

SCTでの変換結果は次のようになります。

CREATE OR REPLACE VIEW add_months_test_view (id, created_date, last_month) AS
SELECT
    id, 
    created_date, 
    aws_oracle_ext.TO_CHAR(ADD_MONTHS(aws_oracle_ext.TO_TIMESTAMP(created_date, 'YYYYMMDD', 'AMERICAN'), - 1), 'YYYYMMDD', 'AMERICAN') AS last_month
FROM to_date_test_table;

TO_CHARと同じようにaws_oracle_extが出てくるので消します。

また、TO_DATEがTO_TIMESTAMPに変換されていて、こいつはTIMESTAMPTZ型 (TZはwith timezoneの意)を返すのですが、ADD_MONTHSがDATE型かTIMESTAMP型しか受け入れないので、ここも手でTO_DATEに戻してあげます。

-- 修正前
aws_oracle_ext.TO_CHAR(ADD_MONTHS(aws_oracle_ext.TO_TIMESTAMP(created_date, 'YYYYMMDD', 'AMERICAN'), - 1), 'YYYYMMDD', 'AMERICAN') AS last_month
-- 修正後
TO_CHAR(ADD_MONTHS(TO_DATE(created_date, 'YYYYMMDD'), - 1), 'YYYYMMDD') AS last_month

TO_NUMBERは書式指定が必要

OracleでTO_NUMBERを使った定義をしたとします。

CREATE VIEW to_number_view AS
SELECT
    id,
    TO_NUMBER(created_date) as to_number_create_date
FROM to_date_test_table;

SCTでの変換結果は次のようになります。

CREATE OR REPLACE VIEW to_number_view (id, to_number_create_date) AS
SELECT
    id, 
    aws_oracle_ext.TO_NUMBER(created_date) AS to_number_create_date
FROM to_date_test_table;

OracleのTO_NUMBERは引数が整数であれば書式指定不要ですが、RedshiftのTO_NUMBERは書式指定が必要です。カラムを見て適切な桁数になるよう9を埋めます。

-- 修正前
aws_oracle_ext.TO_NUMBER(created_date) AS to_number_create_date
-- 修正後
TO_NUMBER(created_date, '999999') AS to_number_create_date

TO_NUMBERのformat部分(上では’999999’の部分)、元のカラムを見て適切な長さにするのが一番ですが、よくわからなければ18桁(‘999999999999999999’)くらいにしておくとよいです。

小数点など、整数以外のフォーマットはリファレンスを参照。

TO_NUMBER - Amazon Redshift
文字列を数値 (10 進) に変換します。
docs.aws.amazon.com

連結演算子の順序

Oracleで連結演算子( || )を使った定義をしたとします。

CREATE VIEW concat_test_view AS
SELECT
    *
FROM to_date_test_table
WHERE '202001012359' <= created_date || '2359';

SCTでの変換結果は次のようになります。

CREATE OR REPLACE VIEW concat_test_view (id, created_date) AS
SELECT
    id, 
    created_date
FROM to_date_test_table
WHERE '202001012359' <= created_date || '2359';

適用しようとすると、boolean型とcharacter varying型を結合しようとしてエラーになります。
これは、Oracleでは連結演算子が解決された後に比較演算子の解決が行なわれるのに対し、Redshiftでは左から右に(比較演算子が解決された後に連結演算子が)処理されるからです。

優先順位を正しくするために括弧で括ってあげます。

-- 修正前
WHERE '202001012359' <= created_date || '2359';
-- 修正後
WHERE '202001012359' <= (created_date || '2359');

NVLの型変換の違い

OracleでNVLを使った定義をしたとします。

CREATE VIEW nvl_test_view AS
SELECT
    NVL(id + 1, '0') as nvled
FROM to_date_test_table;

SCTでの変換結果は次のようになります。

CREATE OR REPLACE VIEW nvl_test_view (nvled) AS
SELECT
    NVL(id + 1, '0'::VARCHAR(MAX)) AS nvled
FROM to_date_test_table;

これは正直Oracleでの書き方がよくないと思いますが、Oracleの方は引数の型をよしなに解釈してくれる親切設計なので、NVLの引数に、数値である”id + 1″と文字である”0″が混在していてもCREATEが通ります。
対して、Redshiftは型変換エラーになります。

VARCHARに変換している部分を数値型にしてあげれば解消します。

-- 修正前
NVL(id + 1, '0'::VARCHAR(MAX)) AS nvled
-- 修正後
NVL(id + 1, '0'::NUMERIC(1)) AS nvled

副問い合わせの配慮

Oracleで副問い合わせを使った定義をしたとします。

CREATE VIEW subquery_test_view AS
SELECT
    id,
    created_date,
    magic_number
FROM (
    SELECT
        id,
        created_date,
        1234 as magic_number
    FROM to_date_test_table
);

SCTでの変換結果は次のようになります。

CREATE OR REPLACE VIEW subquery_test_view (id, created_date, magic_number) AS
SELECT
    id, 
    created_date, 
    magic_number
FROM (
    SELECT
        id, 
        created_date, 
        1234 AS magic_number
    FROM to_date_test_table
);

Oracleは副問い合わせの結果を配慮してくれるので、どこの表のidなのかmagic_numberなのか、を省略できますが、Redshiftではそうはいきません。ちゃんと名前をつけてあげましょう。

CREATE OR REPLACE VIEW subquery_test_view (id, created_date, magic_number) AS
SELECT
-- 修正前
    id, 
    created_date, 
    magic_number
-- 修正後
    s.id, 
    s.created_date, 
    s.magic_number
FROM (
    SELECT
        id, 
        created_date, 
        1234 AS magic_number
    FROM to_date_test_table
-- 修正前
);
-- 修正後
) AS s;

NULLと空文字(”)は等価か

OracleでIS NULLを使った定義をしたとします。

CREATE VIEW null_test_view AS
SELECT
    id
FROM to_date_test_table
WHERE created_date IS NULL;

SCTでの変換結果は次のようになります。

CREATE OR REPLACE VIEW null_test_view (id, created_date) AS
SELECT
    id
FROM to_date_test_table
WHERE created_date IS NULL;

一見問題なさそうですが、OracleとRedshiftで空文字の扱いが違うので結果が違ってくる場合があります。

Oracleでは空文字とNULLは等価なので、created_dateに空文字が入っているレコードも抽出されます。Redshiftでは空文字は空文字として扱われるのでIS NULLに引っ掛かりません。

空文字が入っていても抽出されるよう、条件を追加します。

-- 修正前
WHERE created_date IS NULL;
-- 修正後
WHERE created_date IS NULL OR created_date = '';

GROUP BYの親切さ

OracleでGROUP BYを使った定義をしたとします。

-- 参照用のテーブル
CREATE TABLE group_test_table (
    id NUMBER PRIMARY KEY,
    grouping_number NUMBER(10,3)
);

-- 値をいくつか入れておく
INSERT into group_test_table values (1, 100);
INSERT into group_test_table values (2, 101);
INSERT into group_test_table values (3, 102);
INSERT into group_test_table values (4, 100.000);
INSERT into group_test_table values (5, NULL);

-- GROUP BYの動作を確認するビュー
CREATE VIEW group_test_view AS
SELECT
    g_num,
    count(*) as cnt
FROM (
    SELECT
        NVL2(grouping_number, cast(grouping_number as varchar(10)), '100') as g_num
    FROM group_test_table)
GROUP BY g_num;

Oracleだと次の結果が返ります。

SQL> select * from group_test_view;

G_NUM                     COUNT
-------------------- ----------
100                           3
101                           1
102                           1

SCTでの変換結果は次のようになります。

CREATE OR REPLACE VIEW group_test_view (g_num, count) AS
SELECT
    g_num, 
    COUNT(*) AS count
FROM (
    SELECT
        NVL2(grouping_number, CAST (grouping_number AS VARCHAR(10)), '100') AS g_num
    FROM group_test_table)
GROUP BY g_num;

まずは副問い合わせの問題を修正しておきます。

CREATE OR REPLACE VIEW group_test_view (g_num, count) AS
SELECT
    s.g_num, 
    COUNT(*) AS count
FROM (
    SELECT
        NVL2(grouping_number, CAST (grouping_number AS VARCHAR(10)), '100') AS g_num
    FROM group_test_table) AS s
GROUP BY s.g_num;

結果は以下のようになります。

G_NUM                     COUNT
-------------------- ----------
100.000                       2
100                           1
101.000                       1
102.000                       1

Redshiftだと数値型の精度のままの文字列でGROUPされています。

修正方法はデータの特性次第ですが、小数点3桁に合わせてNVLの値を変える、もしくは、grouping_numberが少数が入らないようなものであればキャスト先を整数にする、で解消します。

-- 修正前
NVL2(grouping_number, CAST (grouping_number AS VARCHAR(10)), '100') AS g_num
-- 修正後
NVL2(grouping_number, CAST (grouping_number AS VARCHAR(10)), '100.000') AS g_num

-- もしくは
-- 修正後
NVL2(grouping_number, CAST (grouping_number AS numeric(10)), 100) AS g_num

おまけ

参考情報

TO_CHAR - Amazon Redshift
タイムスタンプまたは数値式を文字列データ形式に変換します。
docs.aws.amazon.com
TO_TIMESTAMP 関数 - Amazon Redshift
TO_TIMESTAMP は TIMESTAMP 文字列を TIMESTAMPTZ に返します。
docs.aws.amazon.com
ADD_MONTHS 関数 - Amazon Redshift
日付またはタイムスタンプの値または式に、指定された月数を加算します。
docs.aws.amazon.com
CONCAT 関数 - Amazon Redshift
2 つの式を連結し、その結果の表現を返します。
docs.aws.amazon.com

コメント