Oracleインライン外部表を活用して外部データソースと既存システムのデータの比較検証環境を構築した話

こんにちは

データウェアハウス開発部データレイクグループの安海です。

データレイクグループにて
医療系のマスタデータをメンテナンスするシステムを担当しています。

多種多様なマスタを取り扱っており、

外部からのメンテナンスの元となるファイルを取込
JMDCの業務に必要な形にメンテナンス
メンテナンスしたマスタを必要なシステム、部署に配信

といったことを行っています。

少し古いですが、データレイクグループの雰囲気がわかりますので興味がある方は下記記事も読んでみてください

blog.jmdc.co.jp

techblog.jmdc.co.jp

今回はOracleDBのインライン外部表機能を使用して
既存システムのデータと外部データを簡単に比較・検証出来る環境を構築したお話を紹介させて頂きます。

やろうと思ったきっかけ

私が担当しているシステムでは
「メンテナンスの元となる新たなファイルを導入を検討したい」
　↓
「新しいファイルと既存のデータの内容の差異が知りたい」
といった依頼をもらうことが有ります。

そういう時は

テキストエディタでデータを整形
Excelに複数のデータを貼り付けて整形、関数を使用して１つにまとめる
正しくデータが突合出来ているか、目視で確認

といった工程を踏んで
時間にして１時間以上かけて資料を作成していました。
検証の都合で再実施が必要になれば
同じ事を繰り返す必要もあり
ストレス、工数との闘いでもありました。

手作業での実施だと
データの数、形などが増えると比例して、作業にかかる時間も多くなり
（多いときは４時間以上かかったり）
Excelの整形などの作業を手順として残すのは手間も大きく
他メンバが実施しようと思うと再現性が乏しくて、ちょっといまいちでした。

今後も同様の作業が発生する事が予想されるので
何か改善出来ないか、を検討してみることにしました。

改善案を検討するにあたり
以下の４つの観点を意識して検討してみました。

Excelやテキストエディタなどで実施するよりスマート
導入に工数があまりかからない
実施の手間が少ない
手順が使いまわせる（ファイルがたくさんあってもやりやすい）

上記の観点をクリア出来る良い手法を採用したいと思います。

やろうとしている環境

前提として今回は
Linuxサーバー + OracleDBという
よくある典型的なオンプレの環境が舞台になります。

このOracleDBにすでにある既存のデータと
どこか別のところから持ってきたデータを良い感じに比較・検証出来る状態を目指してみました。

検討してみる

さっそく実現方法を検討してみます。

ChatGPTさんに聞いたり色々調べてみて
いくつか案を考えてみました。

前述したように比較するデータはOracleDBに入っているので
最終的にDBのデータとして扱えるようになるのが
比較検証時に扱いやすいし良さそうなので、そこも重要なポイントとして考えてみます。

①取込機能を作る
システムになんでも取り込めそうな機能を作ってしまえ、という案です。
一見出来そうではありますが、いろいろ考慮して設計、開発、テストとかする工数は今はありません。
（というか、手間がかからない方法、という趣旨に反してしまいますのでボツです）

②Oracleの外部表（External Table）を使う
すでに一部の機能などで使っている手法です。
技術的なハードルも低く、導入は容易です。
でもDDLを都度作成する手間が有ります。

都度、DDLを作成、コンパイルする手間は1~2ファイル程度ならば
そこまで気になりませんが、
対象ファイルが増えて、DDLの数が増えていくと
検証作業完了後の後片付けの手間なども増えてしまいます。
（検証が終わったら不要になったものは消しておきたいです）

３. 実施の手間が少ない

この観点がやや厳しい印象です。。。

③AWS上でAthenaなどを使用する
ファイルをS3にアップして、Athenaなどで読み取る方法です。
S3へのアップは簡単ですし、個人的にはこれがいいな、と思ったのですが、
既存システムのデータをS3に上げる手間が発生してしまいます。。。

新しいファイルだけでなく既存データも上げる手間がかかるとなると
この手法でも実施の手間がかかる事になります。。。

④Oracleインライン外部表を使う
タイトルに上げている手法です。
②の外部表に比べて、外部表DDLを事前に作成する必要もなく
実行するSQL文をうまい事テンプレ化することを考えてあげる程度で
手間もかからず始められそうです。

DDLをコンパイルしない（あとで削除する手間が無い）
既存データをアップする必要がない（既存DB環境でそのまま出来る）
といった感じでここまでの手法の問題点もクリア出来そうです。

というわけでこの④の手法でやってみようと思います。

準備してみる

まずは

比較したいファイル（CSVとかTSV）をLinuxサーバーにアップ
OracleDBにディレクトリオブジェクトを設定（ここらへんは元々あったのを転用）

ここまでを準備して
次はインライン外部表として読み取るためのSQLの準備です。

アレコレ調べてみて出来上がったのが↓のサンプルコードです。

SELECT  
    *  
FROM  
    EXTERNAL  
(  
    (    -- 列名を定義  
    CODE_1      VARCHAR2(6),  
    NAME_1      VARCHAR2(300),  
    CODE_2      VARCHAR2(12),  
    NAME_2      VARCHAR2(300),  
    CODE_3      VARCHAR2(18),  
    NAME_3      VARCHAR2(300)  
    )  
    TYPE ORACLE_LOADER  
    DEFAULT DIRECTORY EXT_TABLE_DIRECTORY    -- EXT_TABLE_DIRECTORY ←OracleDBで事前に設定済みのディレクトリオブジェクト
    ACCESS PARAMETERS   
    (  
        RECORDS DELIMITED BY NEWLINE      -- 改行区切り  
        SKIP 1                            -- 1行目をスキップ  
        FIELDS TERMINATED BY ','          -- カンマ区切り  
    )   
    LOCATION (EXT_TABLE_DIRECTORY:'sample_data.csv')   
    REJECT LIMIT UNLIMITED  
) INLINE_EXTERNAL  
;

これをSQL実行環境で実行！
見事に配置したCSVの中身を読み取る事が出来ました！

実際に使用する際は
頭にCREATE TABLE TBL_SAMPLE_DATA AS などを付けて
物理のテーブルとして作成してしまえば
あとからどうとでも出来そうです。

この段階で以下の赤字はほぼ達成できた気分です。

Excelやテキストエディタなどで愚直にやるよりスマートで
導入の手間がかからなくて
実施がそんなに難しくなくて
手順が使いまわせる

最後の手順の使いまわしをなんとかしてあげたいと思います。

先ほどのサンプルのSQLですが↓の列の定義と

    (    -- 列名を定義  
    CODE_1      VARCHAR2(6),  
    NAME_1      VARCHAR2(300),  
    CODE_2      VARCHAR2(12),  
    NAME_2      VARCHAR2(300),  
    CODE_3      VARCHAR2(18),  
    NAME_3      VARCHAR2(300)  
    )

↓の拡張子などの設定と読み取るファイルの名前を変えてあげれば

    (  
        RECORDS DELIMITED BY NEWLINE      -- 改行区切り  
        SKIP 1                            -- 1行目をスキップ  
        FIELDS TERMINATED BY ','          -- カンマ区切り  
    )   
    LOCATION (EXT_TABLE_DIRECTORY:'sample_data.csv')

他の部分はそのまま使いまわせそうです。

特に列の定義は一つずつ置換とか書き換えるのは面倒なので
Excelでテンプレートを作って
必要なとこをペタッとすればよいだけのフォーマットを作ってしまうのが楽そうです。

と、いうことで作ってみたExcelの画像が下記になります。

編集するのは

ファイル名の箇所
B、C、D列の列名などの箇所
ファイルの形式などに応じて改行区切りなどの設定箇所

後は関数などで
H列にいい感じにSQLが組まれるようにしています。

実践してみた結果

冒頭に述べたような依頼を受けた際に
準備したフォーマットを使って資料作成作業を行ってみたところ
とても楽になりました。

工数削減の面
1時間超かけて作成していた資料を
1. 対象ファイルをアップ
2. インライン外部表用のフォーマットでSQLを作成
3. SQLを実行、結果を調整して問題なさそうであればExcelに添付
4. スタイルを整形しつつ、おかしなところが無いか最終確認

という流れで大体15~30分くらいで作ることが出来ました！
対象とするファイルやデータが複雑になると効果はもっと出そうです。

ストレス軽減の面
SQLで抽出出来るようになったので、
例外パターンの処理をSQLで書いてすぐ実行、結果を確認
という手直しがとてもやりやすくなりました。

またデータの突合もSQLで実施しているので
正しく出来ているかの目視チェックも簡略化出来て
作業のストレスが大幅に軽減出来ました。

まとめ

インライン外部表を使用してみよう！までは割とすぐに決まったのですが、
私の調べ方が悪かったのか

そもそも検索してもインライン外部表の記事が少ない
- 通常の外部表の記事はいっぱい見つかる
ACCESS PARAMETERS 周りの設定の情報が見つからない
Oracleの公式ドキュメントに記載足りない、分かりにくい

といった感じで準備にややてこずりました。

とはいえ、準備したことで、ストレスの軽減、工数削減などの効果が得られました。

こうやってExcelのフォーマットとしたことで
チームメンバーが類似の作業を行う際にも
やり方を転用する事が出来て、「楽に対応出来た」という話も聞けたので
個人としては大変満足しています。

最後までお読みいただきありがとうございます。

JMDCでは、ヘルスケア領域の課題解決に一緒に取り組んでいただける方を積極採用中です！
フロントエンド /バックエンド/ データベースエンジニア等、様々なポジションで募集をしています。
詳細は下記の募集一覧からご確認ください。
hrmos.co まずはカジュアルに
JMDCメンバーと話してみたい／経験が活かせそうなポジションの話を聞いてみたい等ございましたら、下記よりエントリーいただけますと幸いです。 hrmos.co ★最新記事のお知らせはぜひ X（Twitter）、またはBlueskyをご覧ください！ Tweets by jmdc_tech twitter.com bsky.app