Oracle APEX 21.1では、データ・ロード・ウィザードが刷新されました。また、新たにパッケージAPEX_DATA_LOADINGが提供されています。今まではロードするファイルのパースだけをAPEX_DATA_PARSER.PARSEで行い、列のマッピングや値の変換の処理はコーディングが必要でした。Oracle APEX 21.1から、それらは共有コンポーネントのデータ・ロード定義として構成されます。
刷新されたデータ・ロード・ウィザードですが、以前からOracle APEXが提供していた、SQLワークショップのデータ・ワークショップのデータのロードの機能を、ユーザーが作成するアプリケーションに組み込めるようになったものです。また、１ページで操作が完結するため、ウィザード形式ではなくなりました。
今までの機能との違いを説明するために、以前の記事で作成したアプリケーションをOracle APEX 21.1で作り直してみます。
最初に、Oracle APEXでのデータ・ロード(1) - はじめにの記事に従って、表COVID19_PATIENTSとアプリケーションである新型コロナウイルス感染症陽性患者属性を作成します。ここまでが準備です。
以下よりOracle APEXでのデータ・ロード(3) - データ・ローディング・ウィザードの作業をAPEX 21.1の新機能を使ってやり直します。
データ・ロードの組み込み
21.1以前のAPEXでは、データ・ロード・ウィザードの組み込みは、ページ作成ウィザードから実施しました。21.1でも同様にデータのロードを選択し、データ・ロードのページを作成します。
ナビゲーションのプリファレンスとして、新規ナビゲーション・メニュー・エントリの作成を選択します。次へ進みます。
データ・ロードの指定が求められます。新しいデータ・ロード・ウィザードを作成するには、あらかじめデータ・ロード定義が作成されている必要があります。まだ未定義なので、ここでは今までの作業を取消し、データ・ロード定義の作成を行います。
共有コンポーネントのデータ・ロード定義を開きます。
定義済みのデータ・ロード定義の一覧画面より、作成を実行します。
データ・ロードの作成は、最初からを選択し、次へ進みます。
データ・ロード定義の名前を陽性患者属性とします。ターゲット・タイプには表を選択します。SQLワークショップのデータ・ワークショップとは異なり、新規に表は作成しないため、データをロードする表はあらかじめ作成しておきます。ターゲット・タイプのコレクションは、APEXのコレクションです。コレクションは事前に作成する必要はありません。表名としてCOVID19_PATIENTSを選択し、次へ進みます。
列名 | 定義書の属性名 |
No | No |
MUNICIPALITY_NAME | 全国地方公共団体コード |
PREFECTURE_NAME | 都道府県名 |
CITY_NAME | 市区町村名 |
PUBLISHED_DATE | 公表_年月日 |
ONSET_DATE | 発症_年月日 |
PATIENT_LOCATION | 患者_居住地 |
PATIENT_AGE | 患者_年代 |
PATIENT_SEX | 患者_性別 |
PATIENT_OCCUPATION | 患者_職業 |
PATIENT_STATUS | 患者_状態 |
PATIENT_SYMPTOM | 患者_症状 |
PATIENT_TRAVEL_HISTORY | 患者_渡航歴の有無フラグ |
PATIENT_LEFT_HOSPITAL | 患者_退院済フラグ |
REMARK | 備考 |
最初の行にヘッダーが含まれるをチェックします。ソース列のNo(Number)と全国地方公共団体コードに主キーのチェックを入れます。表に主キーと定義されている必要はありません。続いてマップ先の指定を行います。下にスクロールし、退院済フラグのマッピングまで忘れずに指定します。全て設定したのち、データ・ロードの作成を実行します。
ページの作成および追加を実行すると、ページ作成ウィザードが起動します。今回はページを作成する前に、作成されたデータ・ロード定義のレビューを行います。
データ・ロード定義が作成されると、一覧画面に表示されます。今回作成したデータ・ロード定義である陽性患者属性をクリックして開きます。
データ・ロード定義の名前がすべて日本語であるため静的IDが適切に生成されていません。手動でPATIENTSと設定しています。コミット間隔は特に変更せず、デフォルトの200のままとします。200行処理を行うごとにコミットします。エラー処理のエラー時は停止にします。停止以外の設定では、ファイルに含まれる全行の処理が行われるため、データ量によっては長時間かかります。そのため、画面からデータ・ロードを行う場合は停止にしておくのがお勧めです。以上の設定を行い、変更の適用をクリックします。
ロード・メソッドとして追加、マージ、置換を選択できます。これらの動作についてオンライン・ヘルプでは、以下のように説明されています。
使用するロード・メソッドを指定します。使用可能な値は次のとおりです:
- 追加: 表またはコレクションに行を追加します。データ・プロファイルに1つ以上の主キー列が定義されている場合、既存の行は変更されず、新しい行が追加されます。データ・プロファイルに主キー定義がない場合、すべての行が単に追加されます。
- マージ: 表に行をマージします。このオプションを使用するには、データ・プロファイルに主キーが定義されている必要があります。指定した主キー値の行が存在する場合は行が更新され、それ以外の場合は行が作成されます。これは、データを表にロードする場合にのみ使用できます。
- 置換: ターゲット表またはコレクション内の既存のデータは、新しいデータがロードされる前に削除されます。
再度、ページ作成ウィザードを実行し、データのロードのページを作成します。ページ番号のデフォルトは５になるので、これは４に変更します。前回、取消した画面にて、データ・ロードとして陽性患者属性を選択し、データのアップロード元はファイル、最大ファイル・サイズ(MB)は5から20へ変更します。以上を設定し、作成をクリックします。最大ファイル・サイズの変更は慎重に行うようにしましょう。実際のデータ・ロード時にタイムアウトが発生してしまうサイズを許可すべきではありません。（今回の東京都のデータをロードすると、実際にエラーが発生します。）
データ・ロードのページが作成されます。
ページ作成ウィザードが色々なコンポーネントをページに配置しています。21.1で新規に開発されたコンポーネントは、タイプがデータのロードのプロセスです。それ以外は、ページ作成ウィザードによって構成された、既存のコンポーネントです。どのように構成されているかについては、こちらの記事が参考になります。
プレビューの見栄えを良くする、XLSXに含まれるシートを選択する、といったカスタマイズも可能になっています。
データ・ロードの確認
作成されたデータ・ロードのページを実行し、動作を確認します。データのロードのページを開きます。
ファイル130001_tokyo_covid19_patients.csvを選択します。ロード済みファイルにファイル名が表示され、データのプレビューが表示されます。データのロードをクリックします。
東京都のデータは大量にあります(この作業の時点では147,168行)。作業を行なったapex.oracle.com上はリソース・マネージャーによる制限が設定されています。そのためORA-40が発生し処理が中断します。確認したところ、18,400行が読み込まれた時点で中断されていました。
しかし、エラー時の処理が停止以外の場合はロード処理は継続しているので、続けてデータのロードをクリックすると、処理が重複し、時間がかかる処理がさらに悪化します。
データのロードを行うプロセスが始まる前に表のロックを確認することが、回避策のひとつとなるでしょう。実行するコードは以下です。V$表にアクセスできる場合は、そちらを確認するのもひとつの方法です。
begin
lock table covid19_patients in exclusive mode nowait;
rollback;
end;
プロセスを作成し、タイプをコードを実行とします。PL/SQLコードに上記を記述し、サーバー側の条件として、ボタン押下時にLOADを選択します。
上記のプロセスを実装すると、データのロード処理が継続していて、表がロックされているとORA-54が発生し、データのロード処理が行われません。
データ・ロード・ウィザードはあくまで画面からデータ・ロードを実施するためのページを作成する機能であり、大量のデータ・ロードの実施については、現在のところAPEX_DATA_LOADINGパッケージを使用したコーディングが必要になるでしょう。こちらの方法については、APEX_DATA_LOADINGの使い方として、別途、解説を行おうと考えています。
エラー処理の設定
ロード時に発生したエラーの扱いには、いくつかの方法があります。停止以外には、無視、エラーをコレクションに記録とエラー・ログに記録があります。
無視は、エラーが発生した行は無視して処理を継続します。取り込みに失敗したデータを考慮しなくて良い場合はあまり無いと思いますが、ロードにかかる時間は短くなるでしょう。
エラーをコレクションに記録では、APEXのコレクションにエラーを記録します。エラー・コレクション名にAPEXのコレクション名を設定します。エラー・メッセージおよび行順序(アップロードされたファイル内)はC001およびN001コレクション・メンバーとして格納されます。参照はビューAPEX_COLLECTIONSより行います。発生したエラーは同じセッションからのみ参照でき、また、参照できる期間はセッションが有効な間に限定されます。
エラー・ログに記録では、エラー・ログを表に記録します。記録する表をあらかじめ作成する必要があります。オンライン・ヘルプにログを記録する表の作成方法が記載されています。今回のケースを例にとると、以下のようにプロシージャDBMS_ERRLOG.CREATE_ERROR_LOGの引数DML_TABLE_NAMEにCOVID19_PATIENTSを渡して実行します。
begin
dbms_errlog.create_error_log(
dml_table_name => 'COVID19_PATIENTS' );
end;
結果として表ERR$_COVID19_PATIENTSが作成されます。エラー・ログ表の情報の保存期間はAPEXのセッションに限定されず、また、記録される情報も行番号とエラー・メッセージに限定されず、エラーが発生した行の値まで含まれます。
データ・ロードのカスタマイズ
以前の記事では、山口県のデータを使用して、トランスフォーメーション・ルールと表参照(21.1ではルックアップという名称になっています)の実装を紹介しています。
同じ設定をOracle APEX 21.1の機能により実装してみます。山口県のデータは東京都と異なり日本語Shift-JISでエンコードされているため(東京都はUTF-8)、同じデータ・ロード定義を流用できません。そのため、データ・ロード定義を新たに作成します。
共有コンポーネントのデータ・ロード定義を開き、作成をクリックします。
データ・ロードの作成として最初からを選択し、次へ進みます。
名前は山口県とし、ターゲット・タイプは表、表名にCOVID19_PATIENTSを選択し、次へ進みます。
サンプル・ファイルとして山口県のオープン・データ(350001_yamaguchi_covid19_patients.csv)を指定し、次へ進みます。
ファイル・エンコーディングとして日本語(Shift JIS)を選択します。マップ先、書式マスクおよび主キーの設定を行い、ページの作成および追加をクリックします。設定自体はほぼ東京都と同じです。陽性確定日は表COVID19_PATIENTSに対応する列がないためマップせず、また、公表日の書式マスクは-(ハイフン)の代わりに/(スラッシュ)にて、年月日を区切ります。(ダブル・クオートで囲まなければ"-"でも"/"を認識します)。
作成するページの名前は山口県とします。次へ進みます。
ナビゲーションのプリファレンスとして、新規ナビゲーション・メニュー・エントリの作成を選択します。新規ナビゲーション・メニュー・エントリは山口県のままで、次へ進みます。
データ・ロードの定義は山口県のままで、作成をクリックします。
山口県のデータをロードするページが作成されるので、実行します。ファイルをアップロードするとプレビューされますが、文字化けしています。
select p.line_number,
p.col001, p.col002, p.col003, p.col004, p.col005, p.col006, p.col007, p.col008, p.col009, p.col010
-- add more columns (col011 to col300) here.
from apex_application_temp_files f,
table( apex_data_parser.parse(
p_content => f.blob_content,
p_file_name => f.filename,
p_file_charset => 'JA16SJIS',
p_max_rows => 10 ) ) p
where f.name = :P5_FILE
アップロードしたデータが指定した文字コードで認識されるため、プレビューが適切に表示されるようになります。
最初にルックアップを設定します。ルックアップに使用する表COVID19_MUNICIPALITIESを作成します。手順は以前の記事に記載しています。ですので説明は省き、手順のみを紹介します。
SQLワークショップのデータ・ワークショップより、データのロードを実行します。
コピー・アンド・ペーストを選択し、以前の記事にある全国地方公共団体コードと都道府県名のデータを貼り付けます。次へ進みます。
ロード先は新規表、表名としてCOVID19_MUNICIPALITIESを入力します。列見出しの最初の行にヘッダーが含まれるにチェックを入れ、列デリミタとして,(カンマ)、囲み文字は"(ダブル・クォート)を指定します。データのロードを実行します。
47行ロードされれば、ルックアップのための表COVID19_MUNICIPALITIESの完成です。フォームを閉じます。
現在は列タイプがデータです。列タイプは変更できないので、一旦、データ・プロファイル列を削除します。
列が削除されるので、続いて列の追加を行います。
削除したMUNICIPALITY_CODEと同じデータが投入されるように設定します。順序は２、名前はMUNICIPALITY_CODEを指定します。主キーはONにします。列タイプはSQL問合せ(単一の値を返す)を選択します。本来はルックアップを選びますが、apex.oracle.com上では表をリストする処理に時間がかかりすぎエラーになるため、直接SELECT文を記載します。指定するSQL問合せは以下になります。指定するSELECT文にロード・プロファイル名を含めます。ここではPREFECTURE_NAMEを条件にしています。
select code from covid19_municipalities
where name = "PREFECTURE_NAME"
以上の設定を行い、作成をクリックします。
続いて患者_年代のデータより歳を除きます。PATIENT_AGEの鉛筆アイコンをクリックします。
トランスフォーメーションのルール・タイプとして正規表現置換を選択し、式1に(\d+)歳代、式2に\1代を指定することで、データから歳を除きます。変更の適用をクリックします。
作成したページを実行し、山口県のデータをロードします。プレビューにはルックアップもトランスフォーメーションも適用されません。CSVに含まれるデータが、そのまま表示されます。ルックアップやトランスフォーメーションはデータのロード時に適用されます。エンドユーザーへCSVに含まれるデータではなく、実際に表にロードされるデータをプレビューするには、プレビューのリージョンをカスタマイズする必要があります。
データのロードを実行します。
ロードされたデータを確認します。陽性患者一覧のページを開き、都道府県名 = '山口県'のフィルタを設定します。全国地方公共団体コードが設定されていること、患者_年代から歳が除かれていることが確認できます。
今回はルックアップを行うためにSELECT文を記載しています。列タイプとしてルックアップを選択した場合は以下のようになります。
表名にCOVID19_MUNICIPALITIES(表)を選択します。戻り値としてCODE (Number)を選択します。表の列1はNAME (Varchar2)、データ列1はPREFECTURE_NAMEになります。
SQL問合せとして設定したSELECT文は以下でした。
select code from covid19_municipalities
where name = "PREFECTURE_NAME"
ルックアップでの設定は以下のように解釈され、SELECT文が作られます。
select 戻り値 from 表名
where 表の列1 = "データ列1"
より一般的には以下の形式になります。
select 戻り値 from 表名
where 表の列1 = "データ列1" and 表の列2 = "データ列2" and 表の列3 = "データ列3" and WHERE句
最後にトランスフォーメーションです。今回は歳を除くために正規表現置換を使用しました。トランスフォーメーションのルール・タイプとして、一番、汎用的に使えるのが正規表現置換ですが、もっと手続き的にトランスフォーメーションを行いたい、といった要件はあるでしょう。実際、以前のデータ・ロードではPL/SQLによるトランスフォーメーションのコーディングができました。
PL/SQLのコーディングによるトランスフォーメーションはデータ・プロファイル列のトランスフォーメーションではなく、新たにデータ・プロファイル列を作成することで対応します。
最初にデータ・プロファイル列のPATIENT_AGEを開きます。
名前をPATIENT_AGEからPATIENT_AGE_LOADに変更し、表示可能をOFFにします。トランスフォーメーションのルール・タイプは- 選択 -に戻します。変更の適用をクリックします。
CSVに含まれる患者_年代のデータはPATIENT_AGE_LOADとして読み込まれます。これを変換(トランスフォーメーション)を適用しつつ、PATIENT_AGEとして読み込みます。列の追加を実行します。
追加するデータ・プロファイル列として、順序は9、名前はPATIENT_AGEとします。表示可能はONです。列タイプにSQL式を選択し、SQL式として以下を指定します。
regexp_replace("PATIENT_AGE_LOAD",'(\d+)歳代','\1代')
トランスフォーメーションとしてPATIENT_AGEに設定していた正規表現置換と同じ処理が行われます。より複雑なトランスフォーメーションを行う場合は、PL/SQLにてファンクションを記述した上で、作成したファンクションを呼び出すことになるでしょう。データ型にVarchar2を選択し、最大長は40とします。作成をクリックします。
データ・プロファイル列の定義を以上のように変更しても、データ・ロードの結果としては同じになります。宣言的な指定としてルックアップやトランスフォーメーションを利用し、それでは賄いきれないときに、データ・プロファイル列の列タイプのSQL式やSQL問合せを使用します。
セレクタの変更
福岡県のデータを読み込む設定を行います。福岡県のCSVデータは東京都とは若干フォーマットが異なります。
No,全国地方公共団体コード,都道府県名,公表_年月日,曜日,居住地,年代,性別,
感染経路不明,濃厚接触者,海外渡航歴有
1,400009,福岡県,2020/02/20,木,福岡市中央区,60代,男性,1,,
2,400009,福岡県,2020/02/20,木,福岡市中央区,60代,女性,,1,
東京都では患者_居住地、患者_年代、患者_性別および患者_渡航歴の有無フラグですが、福岡県は居住地、年代、性別および海外渡航歴有です。ヘッダーの違いはセレクタの設定で対応できます。
データ・ロード定義の陽性患者属性を開き、エラー処理のエラー時をエラーをコレクションに記録へ変更します。エラー・コレクション名にLOAD_ERRORを指定します。福岡県のCSVデータには空行が含まれていて、その空行でデータ・ロードを停止させないための措置になります。
データ・プロファイルの編集を行います。
データ・プロファイル列のPATIENT_LOCATIONを開き、セレクタ・タイプを正規表現へ変更します。セレクタは患者_居住地|居住地とし、ヘッダーの値が患者_居住地と居住地のどちらでもPATIENT_LOCATIONとして扱うように設定します。変更の適用をクリックします。
PATIENT_AGEも同様にセレクタ・タイプを正規表現に変更し、セレクタとして患者_年代|年代を設定し、変更の適用を行います。
PATIENT_SEXもセレクタ・タイプを正規表現に変更し、セレクタとして患者_性別|性別を設定し、変更の適用を行います。
PATIENT_TRAVEL_HISTORYもセレクタ・タイプを正規表現に変更し、セレクタとして患者_渡航歴の有無フラグ|海外渡航歴有を設定し、変更の適用を行います。
以上で福岡県のデータを読み込むためのデータ・ロード定義の変更は完了です。データ・プロファイルの画面より、セレクタ・タイプとセレクタが変更されていることが確認できます。
変更の適用を行い、アプリケーションを実行します。
データのロードを開き、ロードするファイルとして福岡県のデータ400009_pref_fukuoka_covid19_patients.csvを選択し、データのロードを実行します。
福岡県は記事を書いた時点で27,244行のデータが含まれており、apex.oracle.com上ではリソース・マネージャーの制限によりエラーが発生しました。エラーはAPEXコレクションに記録するようにしているため、開発者ツール・バーのセッションをクリックし、コレクションの内容を確認してみます。
ビューとしてコレクションを選択し、設定をクリックします。現在のセッションから参照できるAPEXコレクションの内容が表示されます。CSVに含まれる空行について、ORA-01400: ("WKSP_DATALOADTEST"."COVID19_PATIENTS"."PREFECTURE_NAME")にはNULLは挿入できません。が発生しています。
このエラーの中にORA-00040: アクティブな時間制限を超えました - コールは異常終了しました、も含まれています。
N0001の値が10664なので、10664行目の処理でORA-40が発生し、10664行目の処理はスキップされています。しかし、ORA-40がデータのエラーと同様に扱われ、エラーが発生した時点の行の処理をスキップした上で、データ・ロードが継続されています。
実際には10664行目のデータは以下で、データとしては取り込む必要があります。
10664,400009,福岡県,2021/01/08,金,福岡市博多区,30代,男性,1,,
ORA-1400が発生した行は以下で、データとして取り込む必要はありません。
2352,,,,,,,,,,
データ・ロード定義のエラー処理のエラー時が停止でなくても、ORA-40が発生した場合は処理を停止する方が妥当ではないかと感じます。
Oracle APEX 21.1のデータ・ロードは以前と比較して、とても便利になりました。しかし、データのロードは負荷が高いことに注意し、エンドユーザーが画面からデータ・ロードを実行する頻度が高いと想定される際には、最大ファイル・サイズの制限を厳しくしたり、データ・ロードの要求をキューイングして、バックグランドで順次実施するといったコーディングを検討する必要があるでしょう。
昨年、COVID-19の陽性患者のデータをAPEXのアプリケーションを作ってデータベースに取り込んでみました。その際に、結果的にCSVやXLSXとして提供されているデータも全てAPEX_DATA_PARSERパッケージを使ったコードによってデータ・ロードを行いました。
Oracle APEX 21.1から提供されるパッケージAPEX_DATA_LOADINGのLOAD_DATAファンクションは、引数として、ロードするデータ(BLOBもしくはCLOB)と、共有コンポーネントのデータ・ロード定義(静的ID)を与えて、ロード処理を行います。そのため、コーディングを行う場合でもデータのロードは非常に簡単になりました。おそらく今後は、CSV、XLSX、JSON、XMLといった形式のデータをロードする場合に、APEX_DATA_PARSERパッケージを呼び出すコーディングを行う必要は無いでしょう。
新しいデータ・ロードの紹介は以上になります。
今回作成したアプリケーションを以下に置きました。Oracle APEX 21.1より下のバージョンではインポートできません。
https://github.com/ujnak/apexapps/blob/master/exports/new-data-load.sql
Oracle APEXのアプリケーション作成の参考になれば幸いです。
完
