2021年10月22日金曜日

DBMS_CLOUD.EXPORT_DATAを使ってデータを移行する

 DBMS_CLOUD.EXPORT_DATAを呼び出すことにより、表に含まれるデータをオブジェクト・ストレージに保存することができます。オブジェクト・ストレージに保存したデータをデータベースに読み込むことによって、データを移行してみます。


オブジェクト・ストレージにバケットを作成する

データのエクスポート先とするバケットをオブジェクト・ストレージに作成します。すでに作成済みのバケットがあれば、それを使ってもかまいません。

オブジェクト・ストレージとアーカイブ・ストレージのバケットを開きます。

バケットの作成をクリックします。バケットを作成するコンパートメントを覚えておきます。以下のスクリーンショットではMyAPEXDomainとなっています。

バケット名はDataExchangeとしました。それ以外はデフォルトの設定を変更しません。デフォルト・ストレージ層は標準です。作成をクリックします。


バケットが作成されます。

作成されたバケットのURLを調べるために、ファイルをアップロードします。バケットDataExchangeのリンクをクリックし、詳細画面を開きます。


詳細画面にネームスペースが表示されています。このネームスペースよりバケットのURLがわかる方は以降の作業は不要です。そうでない方は、ファイルをアップロードして確認しましょう。

リソースのオブジェクトを選択し、オブジェクトをひとつアップロードします。アップロードをクリックします。アップロードするファイルはなんでも構いません。小さなファイルが良いでしょう。


ドロワーが開くので、ファイルを選択しアップロードをクリックします。


選択したファイルが終了済になっていることを確認し、閉じるをクリックします。


オブジェクトの一覧にアップロードしたファイル(スクリーンショット上はreport.csv)が現れます。右端のハンバーガー・メニューを開いてオブジェクト詳細の表示を実行します。


URLパス(URI)のバケット名までの部分が、このバケットのURLになります。フォーマットとしては以下になっています。

https://objectstorage.リージョン名.oraclecloud.com/n/ネームスペース/b/バケット名/


以上でバケットの準備は完了です。

リソース・プリンシパルを作成する


Autonomous Databaseからオブジェクト・ストレージのバケットを操作するためのクリデンシャルとして、リソース・プリンシパルを作成します。こちらのマニュアルの記述に従って作業を行います。

オブジェクト・ストレージへの操作を許可する対象を含める動的グループを作成します。対象の選択方法は色々とありますが、今回はAutonomous Databaseを許可する対象とします。

Autonomous DatabaseのOCIDを確認します。対象となるAutonomous Databaseの詳細画面を開き、OCIDをコピーします。


アイデンティティの動的グループを開きます。動的グループの作成をクリックします。


以下では、動的グループの名前をAllowDataExchangeGroupとしています。説明は、バケットDataExchangeの操作を許可と記述しています。

ルール1として、以下を設定します。resource.idとして操作を許可するAutonomous DatabaseのOCIDを指定します。

resource.id = 'ocid1.上記でコピーしたADBのOCID'

作成をクリックします。


動的グループAllowDataExchangeGroupが作成されました。


ポリシーを作成し、この動的グループに含まれるリソースから行われるオブジェクト・ストレージの操作を許可します。

アイデンティティのポリシーを開きます。ポリシーの作成をクリックします。


ポリシー名はAllowDataExchangePolicy、説明は動的グループと同じく、バケットDataExchangeの操作を許可と記述しています。ポリシー・ビルダーは手動エディタの表示をONにし、以下のポリシーを記載します。コンパートメント名MyAPEXDomainの部分は、それぞれの環境にあわせて置き換えます。

Allow dynamic-group AllowDataExchangeGroup to read buckets in compartment MyAPEXDomain
Allow dynamic-group AllowDataExchangeGroup to manage objects in compartment MyAPEXDomain


ポリシーが作成されます。これで、Oracle Cloudのコンソールで行う準備作業は完了です。


Autonomous Databaseのデータベース・アクションを開きます。


管理者ユーザーADMINでサインインし、開発のSQLを実行します。


リソース・プリンシパルを有効にします。DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPALを実行します。初回呼び出時は必ず引数なしで実行します。リソース・プリンシパルの所有者がユーザーADMINになります。

BEGIN
dbms_cloud_admin.enable_resource_principal();

END; 

有効にしたリソース・プリンシパルを確認します。ビューDBA_CREDENTIALSを検索します。クリデンシャル名としてOCI$RESOURCE_PRINCIPALが見つかれば、有効になっています。

select * from dba_credentials



DBMS_CLOUD.EXPORT_DATAを実行する


有効になったリソース・プリンシパルを使って、DBMS_CLOUD.EXPORT_DATAを呼び出してみます。file_uri_listとして与えるURIのリージョンとネームスペースは環境に合わせて置き換えます。スキーマAPEXDEVに表EMPがあることを前提として、queryにselect * from apexdev.empを与えています。

BEGIN
DBMS_CLOUD.EXPORT_DATA(
credential_name =>'OCI$RESOURCE_PRINCIPAL',
file_uri_list =>'https://objectstorage.リージョン.oraclecloud.com/n/ネームスペース/b/DataExchange/o/emp',
format => json_object('type' value 'json'),
query => 'SELECT * FROM apexdev.emp'
);
END;
/


オブジェクト・ストレージのバケットDataExchangeの内容を確認します。file_uri_listに与えた接頭辞のempに日付の情報が付いて、拡張子がjsonとなっているオブジェクトが作成されていることが確認できます。

右端のハンバーガー・メニューを開き、オブジェクトをダウンロードします。ファイルが作成されるので、その内容を確認します。


ファイルの内容は改行で区切られたJSON形式であることが確認できます。

{"EMPNO":7839,"ENAME":"KING","JOB":"PRESIDENT","MGR":null,"HIREDATE":"1981-11-17T00:00:00","SAL":5000,"COMM":null,"DEPTNO":10}

{"EMPNO":7698,"ENAME":"BLAKE","JOB":"MANAGER","MGR":7839,"HIREDATE":"1981-05-01T00:00:00","SAL":2850,"COMM":null,"DEPTNO":30}

{"EMPNO":7782,"ENAME":"CLARK","JOB":"MANAGER","MGR":7839,"HIREDATE":"1981-06-09T00:00:00","SAL":2450,"COMM":null,"DEPTNO":10}

{"EMPNO":7566,"ENAME":"JONES","JOB":"MANAGER","MGR":7839,"HIREDATE":"1981-04-02T00:00:00","SAL":2975,"COMM":null,"DEPTNO":20}

{"EMPNO":7788,"ENAME":"SCOTT","JOB":"ANALYST","MGR":7566,"HIREDATE":"1982-12-09T00:00:00","SAL":3000,"COMM":null,"DEPTNO":20}

{"EMPNO":7902,"ENAME":"FORD","JOB":"ANALYST","MGR":7566,"HIREDATE":"1981-12-03T00:00:00","SAL":3000,"COMM":null,"DEPTNO":20}

{"EMPNO":7369,"ENAME":"SMITH","JOB":"CLERK","MGR":7902,"HIREDATE":"1980-12-17T00:00:00","SAL":800,"COMM":null,"DEPTNO":20}

{"EMPNO":7499,"ENAME":"ALLEN","JOB":"SALESMAN","MGR":7698,"HIREDATE":"1981-02-20T00:00:00","SAL":1600,"COMM":300,"DEPTNO":30}

{"EMPNO":7521,"ENAME":"WARD","JOB":"SALESMAN","MGR":7698,"HIREDATE":"1981-02-22T00:00:00","SAL":1250,"COMM":500,"DEPTNO":30}

{"EMPNO":7654,"ENAME":"MARTIN","JOB":"SALESMAN","MGR":7698,"HIREDATE":"1981-09-28T00:00:00","SAL":1250,"COMM":1400,"DEPTNO":30}

{"EMPNO":7844,"ENAME":"TURNER","JOB":"SALESMAN","MGR":7698,"HIREDATE":"1981-09-08T00:00:00","SAL":1500,"COMM":0,"DEPTNO":30}

{"EMPNO":7876,"ENAME":"ADAMS","JOB":"CLERK","MGR":7788,"HIREDATE":"1983-01-12T00:00:00","SAL":1100,"COMM":null,"DEPTNO":20}

{"EMPNO":7900,"ENAME":"JAMES","JOB":"CLERK","MGR":7698,"HIREDATE":"1981-12-03T00:00:00","SAL":950,"COMM":null,"DEPTNO":30}

{"EMPNO":7934,"ENAME":"MILLER","JOB":"CLERK","MGR":7782,"HIREDATE":"1982-01-23T00:00:00","SAL":1300,"COMM":null,"DEPTNO":10


管理者であるADMINではなく、APEXのワークスペースからも実行できるようします。APEXのスキーマがAPEXDEVであることを前提として、以下のコマンドを実行します。リソース・プリンシパルをAPEXDEVで利用可能にします。

BEGIN
dbms_cloud_admin.enable_resource_principal(
username => 'APEXDEV'
);
END;


パッケージDBMS_CLOUDの実行権限も付与します。

grant execute on dbms_cloud to apexdev;


不思議なことにビューDBA_CREDENTIALSを検索しても、usernameがAPEXDEVとなっているリソース・プリンシパルは見つかりません。そのため、管理者ユーザー以外のユーザーに対して、リソース・プリンシパルが有効になっているかどうかを確認する方法がありません。


APEXのSQLコマンドからDBMS_CLOUD.EXPORT_DATAを実行してみます。


バケットDataExchangeに含まれているオブジェクトが増えていることが確認できます。



オブジェクトをデータベースに取り込む


DBMS_CLOUD.COPY_COLLECTIONを呼び出して、オブジェクト・ストレージからJSONファイルを取り込むには、SODAを利用可能にする必要があります。ユーザーAPEXDEVにロールSODA_APPを割り当てます。

grant soda_app to apexdev;


オブジェクト・ストレージのデータを読み出す際に、一旦オブジェクトをディレクトリDATA_PUMP_DIRにダウンロードしているらしく、DATA_PUMP_DIRの操作権限がないとエラーが発生します。そのため、ユーザーAPEXDEVにDATA_PUMP_DIRの操作権限を与えます。

grant all on directory DATA_PUMP_DIR to apexdev;


以上で、ユーザーAPEXDEVでDBMS_CLOUD.COPY_COLLECTIONの実行ができるようになりました。以下を実行してみます。

begin
dbms_cloud.copy_collection(
collection_name => 'EmpCol',
credential_name => 'OCI$RESOURCE_PRINCIPAL',
file_uri_list => 'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/ネームスペース/b/DataExchange/o/emp_1_日付.json',
format => json_object('unpackarrays' value 'true')
);
end;

コレクションEmpColが作成されました。作成されたSODAコレクション"EmpCol"の内容を確認してみます。

select json_object(json_document) from "EmpCol";



表にロードするには、SODAコレクションよりJSONのデータを読み出し、INSERT文を発行する必要がありそうです。

DBMS_CLOUD.COPY_DATAを使ってJSONを直接表に読み込むことができるのかは不明です。マニュアルには記載が見つかりませんでした。DBMS_CLOUD.CREATE_EXTERNAL_TABLEについても同様に、改行区切りのJSONを元にして外部表を作る方法は見つかりませんでした。

おまけ


色々と操作がうまくいかず、途中で諦めてオブジェクト・ストレージを操作するPL/SQL SDKを使ってコードを書きました。
  • オブジェクト・ストレージのPL/SQL SDKの使い方については、こちらの記事で紹介しています。
  • OCIのSDKを呼び出すのに必要なクリデンシャルの作り方については、こちらの記事で紹介しています。
  • BLOBに保存されているNDJSONを読み出す方法は、こちらの記事で紹介しています。
  • 表の内容をNDJSON形式でBLOBに保存する方法は、こちらの記事で紹介しています。
以上の記事に含まれているコードを組み合わせています。

任意のSQLの検索結果をNDJSON形式にして、オブジェクト・ストレージに保存するコードのサンプルが以下になります。


declare
l_source_sql varchar2(4000);
l_sql varchar2(800);
l_put_response dbms_cloud_oci_obs_object_storage_put_object_response_t;
l_status_code integer;
l_blob blob;
plsql_sdk_error exception;
l_line varchar2(32767);
l_raw raw(32767);
l_len integer;
type t_ndjson is ref cursor;
c_ndjson t_ndjson;
begin
-- NDJSON形式で出力するSELECT文を記述する。
l_source_sql := 'select * from emp';
-- 一行ずつJSON形式で取り出し、BLOBに書き込む。
l_sql := 'select json_object(*) as l from (' || l_source_sql || ')';
dbms_lob.createtemporary(l_blob, TRUE, dbms_lob.session);
dbms_lob.open(l_blob, dbms_lob.lob_readwrite);
open c_ndjson for l_sql;
loop
fetch c_ndjson into l_line;
exit when c_ndjson%notfound;
l_line := l_line || chr(10); -- 改行の追加
l_raw := utl_raw.cast_to_raw(l_line);
l_len := utl_raw.length(l_raw);
dbms_lob.writeappend(l_blob, l_len, l_raw); -- BLOBに追記
end loop;
close c_ndjson;
dbms_lob.close(l_blob);
-- 検索結果をすべてBLOBに書き込んだので、Object Storageにアップロードする。
l_put_response := dbms_cloud_oci_obs_object_storage.put_object
(
namespace_name => 'ネームスペース'
, bucket_name => 'バケット'
, object_name => 'オブジェクト名'
, content_type => 'application/json'
, put_object_body => l_blob
, region => 'リージョン'
, credential_name => 'クリデンシャル'
);
l_status_code := l_put_response.status_code;
-- status 200で書き込み成功。
dbms_output.put_line('status = ' || l_status_code);
end;


オブジェクト・ストレージからNDJSON形式のデータを取得し、パースして表に保存するコードのサンプルです。オブジェクト・ストレージ上のデータが、サンプル・データセットに含まれる表EMPであると仮定して、その内容を表EMP_NEWに保存しています。


declare
l_response dbms_cloud_oci_obs_object_storage_get_object_response_t;
l_status_code number;
l_blob blob;
plsql_sdk_error exception;
C_NL constant raw(1) := utl_raw.cast_to_raw(chr(10));
l_current integer;
l_start integer := 1;
l_line varchar2(32767);
l_json json_object_t;
begin
-- オブジェクト・ストレージから改行区切りJSONのデータを取り込む。
l_response := dbms_cloud_oci_obs_object_storage.get_object(
namespace_name => 'ネームスペース'
,bucket_name => 'バケット'
,object_name => 'オブジェクト名'
,region => 'リージョン'
,credential_name => 'クリデンシャル'
);
l_status_code := l_response.status_code;
if l_status_code != 200 then
raise plsql_sdk_error;
end if;
l_blob := l_response.response_body;
---
while true
loop
-- 改行位置を見つける。
l_current := dbms_lob.instr(l_blob, C_NL, l_start);
-- 一行を取り出す。
l_line := utl_raw.cast_to_varchar2(
dbms_lob.substr(l_blob, (l_current - l_start), l_start)
);
-- 改行が見つからなければ終了。
-- ファイルの最終行でも改行がある - いきなりEOFにはならないのが前提。
exit when (l_current = 0);
if l_line is not null then
l_json := json_object_t.parse(l_line);
-- INSERT文はデータの投入先に合わせて変更する。
execute immediate
'insert into emp_new(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(:1,:2,:3,:4,:5,:6,:7,:8)'
using l_json.get_number('EMPNO'), l_json.get_string('ENAME'), l_json.get_string('JOB'),
l_json.get_string('MGR'), l_json.get_date('HIREDATE'), l_json.get_number('SAL'),
l_json.get_number('COMM'), l_json.get_number('DEPTNO');
-- dbms_output.put_line(l_json.to_string);
end if;
-- 次の行の処理へ移る。
l_start := l_current + 1;
end loop;
end;

以上でDBMS_CLOUD.EXPORT_DATAの使い方の紹介は終了です。

Oracle APEXのアプリケーション作成の参考になれば幸いです。

完
投稿者 時刻:
ラベル: , , ,