【初学者向け】Oracle DBデータ移行業務での学び

はじめに

私は大手メーカーの生産技術本部でソフト開発を行う部門に配属しています。

部門では、React/TypeScript/Node.jsやC#、Pythonでのアプリケーション開発（工場のデータ収集・分析・可視化システム、装置稼働管理、故障予測、部品管理、在庫予測及び管理など）をメインに行なっている一方で、多数あるグループ会社や共同研究先からの委託業務（システム開発や保守、ネットワーク構築など）も請け負っています。

本記事は、昨年夏ごろにグループ会社の情報通信システム部から来た委託業務での話であり、現行のOracleデータをPostgresに移行するサポートを5ヶ月にわたり進めています。（2023年3月ごろまで続く見込み）

私自身、他専門（メカ系）から社内転職してまだ経験が浅いこともあり、本記事はこれからエンジニアになろうとしている方向け（つまり過去の自分）に書いたものであることをご了承のうえで読んでいただければ幸いです。

業務の概要

当社のグループ会社複数社が共同で進めているOracle→Postgres基幹データ移行があり、うち１社で計画に遅れが生じており（人手不足にて）、私が所属する部門に声がかかりました。

まだ経験が浅い私なので上司からもそこまで期待はされていませんでしたが、業務へアサインされる可能性があるとわかるや否や、ほかで評判の良かった以下の本を即購入し、最低限の知識をインプットしました。
ミックさんの以下の著書はどれも非常にわかりやすいのでおすすめです！
　　　　
幸いこの学びが功を奏している感があり、アラフォー半人前エンジニアの自分でもメンバーの足を引っ張ることなくここまでやって来れています。
（ついでに今年の秋に受験した応用情報でも選択必須科目にまでレベルを上げることができました。試験当日は変則問題が出て大コケしましたが・・・笑）

業務の詳細

上の図のように、データを1レコード目から順に多数あるテーブルを奥へ奥へと読みに行き、値があれば更新していきます。
更新であっても、UPDATEではなく、SELECTを使い、以下のSQL文を書いて更新をかけていきました。
（成果物はビューのSQLとして納入します）

SELECT   -- 更新対象テーブルの更新対象でない全カラムをベタ書き、*ではダメ
     old.更新対象外カラム1
    ,old.更新対象外カラム2
    ,old.更新対象外カラム3
            ・
            ・
            ・
    ,DECODE(new.更新対象カラム1,　NULL, old.更新対象カラム1, new.更新対象カラム1) AS 更新対象カラム1
    ,DECODE(new.更新対象カラム2,　NULL, old.更新対象カラム2, new.更新対象カラム2) AS 更新対象カラム2
    ,DECODE(new.更新対象カラム3,　NULL, old.更新対象カラム3, new.更新対象カラム3) AS 更新対象カラム3
FROM　更新対象テーブル old
LEFT　JOIN　(
    SELECT　・・・・
    FROM　更新対象テーブル
    LEFT JOIN ・・・
    ON　・・・
    LEFT JOIN ・・・
    ON　・・・
) new
ON ・・・

補足）
DECODEはOracle専用関数なのでCASE文を使うべきという記述も多く見かけますが、DECODE使ったほうがはるかに簡潔かつ可読性高く書けるため、今回は積極的に使用しました。

LEFT JOINするテーブルについてどれくらい奥まで読みにいくのかについて、イメージを一例として以下に書いてみます。

こうして図にしていくとやることはシンプルそうに見えるのですが、テーブル数がやたらと多く、テーブル名・カラム名複雑怪奇で、さらに主キーがなく重複データやNULLばかりのデータも多く、慣れるまでは苦労が絶えませんでした。
各データ変換仕様書についても、上のようなデータの不備は考慮されていないので、とくに結合条件に関わるカラムの型やデータの中身については都度見に行き、仕様書の不備を見つけては指摘して修正してもらう、のループがひたすら行われることとなりました。

SQL文は以下のような感じに LEFT JOIN を続けて書いていきます。
（１レコードにつきデータはひとつになるように、複数ヒットする際の条件をSQLに実装していきます）

SELECT カラムA,　カラムB,　・・・
FROM 更新対象テーブル
LEFT JOIN テーブルA
ON ・・・
 AND　・・・
 AND・・・
LEFT JOIN テーブルB
ON ・・・
 AND ・・・
 AND ・・・

ここで、慣れるまで工夫が必要だった仕様についていくつか例を上げたいと思います。

事例① 複数件存在する場合にはNULLをセットせよ

以下のようなDECODE文で対応します（CASEでも可）。

SELECT
    a.id 
    ,DECODE(COUNT(DISTINCT b.value), 1, MAX(b.value), NULL)
FROM tableA a
LEFT JOIN tableB b
ON a.b_id = b.id
GROUP BY　a.id

ここで、

DECODE(COUNT(DISTINCT b.value), 1, MAX(b.value), NULL)

について少し補足します。

DECODE関数
「第一引数が第二引数のときは第三引数の値を返し、
　第二引数でないときは第四引数を返す」
を使います。

これを使わずそのままLEFT JOINすると、ひとつのidに対し2つのレコードが出力されるため、GROUP BYしてあげる必要があります。
GROUP BYしたときのカウント数が２つ以上あるときにはNULLが入り、１つだけならその値が入るようにしています。（LEFT JOINにつき、カウントは０にはならない）
ここでDISTINCTを入れているのは、同一の値が重複する際にカウント数=２→NULLとしないためです。

上のSQLではシンプルに見えますが、実際のSQL文では前に10〜30に及ぶカラムがくっついているため、SELECT句やGROUP BY句に膨大な数のカラム名が並ぶことになり、この点も大変でした（当然エラーも頻発）。

事例② 複数件存在する場合に「○○カラム」が最大のものを選べ。最大のものが複数ある場合はNULLをセットせよ

この仕様については、サブクエリで一旦ナンバリングを振ってあげて（ウインドウ関数は今回初めて知ったのですがかなり使い勝手のよいです）、一階層上で最小となるレコードをWHERE句で抽出したのちに、事例①でも活躍したDECODE関数で複数あるレコードにNULLをセットしていきます。

SELECT 
     id
    ,b_id
    ,DECODE(COUNT(DISTINCT value1 || value2), 1, value1, NULL) AS value1
    ,DECODE(COUNT(DISTINCT value1 || value2), 1, value2, NULL) AS value2
FROM (
    SELECT　
         a.id
        ,a.b_id
        ,b.value1
        ,b.value2
        ,RANK() OVER (PARTITION BY b.id ORDER BY b.value2) AS rank
    FROM tableA a
    LEFT JOIN tableB b
    ON a.b_id = b.id
)
WHERE rank = 1

上では、WHERE = 1 で最小値が複数あるデータについてはレコードが２行出力されるので、それをDECODE文でNULLをセットしていきます。

事例③ 複数件存在する場合に「○○カラム」の値が10〜20の範囲にあればその中の最小値を、なければ最小値をセットせよ

こちらはどう実装すべきか頭を悩ませていたのですが、以下の実装でエレガントに実現できました。
（ツイッターでの悲鳴投稿に対しアドバイスいただいた友人エンジニアの方には本当に感謝していますm(_ _)m）

SELECT　
     a.id
    ,a.b_id,
    ,COALESCE(MIN(CASE WHEN 10 <= b.value AND b.value <= 20 
                       THEN b.value ELSE NULL), MIN(b.value)
    )
FROM tableA a
LEFT JOIN tableB b
ON a.b_id = b.id
GROUP BY 
     a.id
    ,a.b_id 

ポイントはMIN()の引数にCASE文を入れ込むことです。

苦労した点

以下、依頼元側の環境制約のところでかなりつまずきました（現在進行中）。

テーブルに主キーがない

これがないせいで、上で紹介したようなDECODE文による対応が必要になり、SQLも複雑になっています。
20年前のシステムとはいえ、私自身主キーの大切さを身をもって学ぶことができています。

テーブル結合する際のデータ型が異なる

正直これがとんでもなくしんどく、ケアしなければならない項目が多々発生しています。

日付がDATE型だったり、文字列だったり、さらには数値型で格納されていると、日付なのに【101】など格納されていて驚いたりしたものです・・
（2000年については20000101 → 000101 →(NUMBER型)→ 101）

これについては、NUMBER型からDATE型に一発変換できないので、

TO_DATE(LPAD(101,'0',6),　'YYMMDD')

として直してあげてから 「=」　でつないであげる必要があります。

ただ、これなんかは全然かわいい方で、もっと苦しめられているのはVARCHAR2型とCHAR型を結合条件で比較する際の対応です。
旧システムのテーブル群は全てCHAR型になっていることもあり、

CREATE OR REPLACE VIEW ビュー名 AS (
SELECT
     RTRIM(カラムA) AS カラムA
    ,RTRIM(カラムB) AS カラムB
    ,カラムC   --CHAR型以外の、NUMBER型カラムにはRTRIMしない
FROM　旧テーブル
)

といった具合にCHAR→VARCHAR2に変更したビューを全て使用テーブルで行うこととなったのですが、この対応による副作用に苦しんでいます。

ひとつだけ例を挙げるなら、CHAR(1)（ここではカラム名をvalueとする）のデータを判定する以下の仕様があります。

CASE value
  WHEN　' ' THEN　'データは空文字です'
  WHEN　'A' THEN　'データはAです'
  WHEN　'B' THEN　'データはBです'
  WHEN　NULL　THEN　'データはNULLです'
  ELSE　THEN　'データはそれ以外です'
END

空文字を判定しようとする際に、上で書いたようにRTRIM関数を入れるとOracle特別仕様でNULLとなり、上のCASE文のひとつ目に引っ掛からなくなりました。
この対応として、

DECODE(value,　NULL,　COALESCE(value,　'　'), value)

とCOALESCE関数で空文字を復元してあげる必要に追われることになりました。
（COALESCE関数これまでほぼ知らなかったのですが、かなり使える標準関数だと実感しています）

おわりに

今回、一会社の基幹データベースを触らせていただき、数百万件、数千万件に及ぶデータが格納されたデータベースを触る機会を与えていただき、ある意味貴重な経験をさせていただいています。
ここでは触れられなかった、SQLのためのテスト仕様書（テストケースやテストデータの作り方）やPL/SQLでの学び（プロシージャ、ファンクション、自律型トランザクション、トリガー、等々）についても、また機会あれば続編としてこちらに書きたいと思います。

半人前エンジニアの学びの記事を最後までご覧いただき、ありがとうございました。