データベースの内部表をオブジェクト・ストレージにCSV形式でエクスポートし、外部表に置き換える手順を確認してみました。実施してみると色々あって、最終的にはパッケージDBMS_CLOUD、APEXのPL/SQL API、OCIのオブジェクト・ストレージを操作するPL/SQL SDKを使って、パーティション分割した外部表を作っています。
追記 - DBMS_CLOUD.EXPORT_DATAだけで処理できました。手順の最終版としてはこちらになります。速度も比較にならないほど速いです。
Always FreeのAutonomous Data Warehouseのインスタンスを使用します。APEXのワークスペースとして、APEXDEV（スキーマAPEXDEVも）が作成済みで、表はそのスキーマに作成します。
以下より、作業を記録します。
データの準備
SQLclにてデータベースに接続し、以下のスクリプトを実行して表BRICKSを作成します。元ネタはLiveSQLのこちらのPrerequisite SQLになります。生成する行数は10,000,000行に増やしています。
SQLclより実行します。
% sql -cloudconfig Wallet_DWAPEX.zip apexdev/***********@dwapex_low
SQLcl: 水 3月 30 16:40:30 2022のリリース21.4 Production
Copyright (c) 1982, 2022, Oracle. All rights reserved.
接続先:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.1.0
SQL> set time on timing on
16:40:54 SQL> set echo on
16:40:58 SQL> @bricks
16:41:04 SQL> exec dbms_random.seed ( 0 );
PL/SQLプロシージャが正常に完了しました。
経過時間: 00:00:00.629
16:41:04 SQL> create table bricks (
2 brick_id not null constraint bricks_pk primary key,
3 colour not null,
4 shape not null,
5 weight not null,
6 insert_date not null,
7 junk default lpad ( 'x', 50, 'x' ) not null
8 ) as
9 with rws as (
10 select level x from dual
11 connect by level <= 10000000
12 )
13 select rownum brick_id,
14 case ceil ( rownum / 2500000 )
15 when 4 then 'red'
16 when 1 then 'blue'
17 when 2 then 'green'
18 when 3 then 'yellow'
19 end colour,
20 case mod ( rownum, 4 )
21 when 0 then 'cube'
22 when 1 then 'cylinder'
23 when 2 then 'pyramid'
24 when 3 then 'prism'
25 end shape,
26 round ( dbms_random.value ( 1, 10 ) ),
27 trunc(date'2011-01-01' + ( rownum/2400 ) + ( mod ( rownum, 24 ) / 36 )) insert_date,
28 lpad ( 'x', 50, 'x' )
29 from rws;
Table BRICKSは作成されました。
経過時間: 00:01:33.983
経過時間: 00:01:33.983
16:42:38 SQL>
表BRICKSには10M(10,000,000)行のデータが挿入されます。
DBMS_CLOUD.EXPORT_DATAによるオブジェクト・ストレージへのエクスポート
DBMS_CLOUD.EXPORT_DATAを呼び出し、表BRICKSの内容をオブジェクト・ストレージにエクスポートします。
DBMS_CLOUDの実行権限とディレクトリDATA_PUMP_DIRへの読み書き権限を、ユーザーAPEXDEVに与えます。
管理ユーザーADMINにてデータベース・アクションのSQLを開き、GRANT文を実行します。
SQL> set echo on
SQL> @credential
SQL> begin
2 dbms_cloud.create_credential
3 (
4 credential_name => 'DEF_CRED'
5 , username => 'ユーザー名'
6 , password => '認証トークン'
7 );
8 end;
9 /
PL/SQLプロシージャが正常に完了しました。
SQL>
プロシージャDBMS_CLOUD.EXPORT_DATAを呼び出し、表BRICKSの内容をオブジェクト・ストレージにエクスポートします。エクスポート先のバケットはbricks、作成されるファイルの接頭辞はbricksとします。
SQL> set echo on
SQL> set time on timing on
16:03:28 SQL> @export_data
16:03:32 SQL> declare
2 C_REGION constant varchar2(20) := 'us-ashburn-1';
3 C_NAMESPACE constant varchar2(20) := 'ネームスペース名';
4 C_BUCKET constant varchar2(20) := 'bricks';
5 C_FILENAME constant varchar2(20) := 'bricks';
6 l_path varchar2(400);
7 begin
8 l_path := 'https://objectstorage.' || C_REGION || '.oraclecloud.com/n/' || C_NAMESPACE
9 || '/b/' || C_BUCKET || '/o/' || C_FILENAME;
10 dbms_output.put_line(l_path);
11 dbms_cloud.export_data
12 (
13 credential_name => 'DEF_CRED'
14 , file_uri_list => l_path
15 , format => json_object('type' value 'csv')
16 , query => 'SELECT * FROM bricks'
17 );
18 END;
19 /
PL/SQLプロシージャが正常に完了しました。
経過時間: 00:00:40.333
経過時間: 00:00:40.333
16:04:12 SQL>
バケットbricksの内容を確認します。バケット内に複数のオブジェクトが作成されていることが確認できます。
SQL> set echo on
SQL> set time on timing on
16:19:04 SQL> @create_external_table
16:19:09 SQL> declare
2 C_REGION constant varchar2(20) := 'us-ashburn-1';
3 C_NAMESPACE constant varchar2(20) := 'ネームスペース名';
4 C_BUCKET constant varchar2(20) := 'bricks';
5 C_FILENAME constant varchar2(20) := 'bricks';
6 l_path varchar2(400);
7 begin
8 l_path := 'https://objectstorage.' || C_REGION || '.oraclecloud.com/n/' || C_NAMESPACE
9 || '/b/' || C_BUCKET || '/o/' || C_FILENAME || '*.csv';
10 dbms_output.put_line(l_path);
11 dbms_cloud.create_external_table(
12 credential_name => 'DEF_CRED'
13 , table_name => 'bricks_ext'
14 , file_uri_list => l_path
15 , format => json_object(
16 'type' value 'csv',
17 'dateformat' value 'RR-MM-DD')
18 , column_list =>
19 'brick_id number,
20 colour varchar2(6),
21 shape varchar2(8),
22 weight number,
23 insert_date date,
24 junk varchar2(50)'
25 );
26 end;
27 /
PL/SQLプロシージャが正常に完了しました。
経過時間: 00:00:00.423
経過時間: 00:00:00.424
16:19:10 SQL>
表BRICKS_EXTが作成されたので、行数を確認します。
16:48:04 SQL> select count(*) from bricks_ext;
COUNT(*)
___________
4789278
経過時間: 00:00:19.441
16:49:51 SQL>
元の表BRICKSは10,000,000行なので、半分程度しかエクスポートされていません。
足りない行を確認してみます。
16:49:51 SQL> select * from (
2 select * from bricks
3 minus
4 select * from bricks_ext
5* ) where rownum < 10;
BRICK_ID COLOUR SHAPE WEIGHT INSERT_DATE JUNK
___________ _________ ___________ _________ ______________ _____________________________________________________
260102 blue pyramid 9 11-04-19 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
260103 blue prism 9 11-04-19 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
260104 blue cube 3 11-04-19 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
260105 blue cylinder 10 11-04-19 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
260106 blue pyramid 7 11-04-19 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
260107 blue prism 2 11-04-19 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
260108 blue cube 7 11-04-19 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
260109 blue cylinder 10 11-04-19 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
260110 blue pyramid 7 11-04-19 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
9行が選択されました。
経過時間: 00:00:26.803
16:52:26 SQL>
少なくとも、BRICK_IDが260102から始まる行がエクスポートされていません。エクスポートされたオブジェクトをダウンロードして確認しました。
CSVの生成とアップロードを別々に実施
SQL> @create_api_cred
SQL> begin
2 dbms_cloud.create_credential(
3 credential_name => 'MY_OCI_CRED'
4 , user_ocid => '構成ファイルのuserの値'
5 , tenancy_ocid => '構成ファイルのtenancyの値'
6 , private_key => '一行にした秘密キー'
7 , fingerprint => '構成ファイルのfingerprintの値'
8 );
9 end;
10 /
PL/SQLプロシージャが正常に完了しました。
SQL>
作成された外部表BRICKS_EXTの内容が、元の表であるBRICKSと同じかどうか確認します。
12:16:52 SQL> select count(*) from bricks_ext;
COUNT(*)
___________
10000000
経過時間: 00:00:17.975
12:17:17 SQL> select * from bricks
2 minus
3* select * from bricks_ext;
行が選択されていません
経過時間: 00:00:39.345
12:18:09 SQL>
行数も10,000,000であり元表との差分もないため、表の内容は同一と言えます。
パーティションに分割
Autonomous Databaseでは、パーティション機能を使用するに当たって追加の費用は発生しません。そのため、100,000行ごとにファイルを分割するよりは、最初からパーティション表として作成するつもりで、データをエクスポートする方が合理的です。
表BRICKSを列INSERT_DATAの年ごとにパーティション分割できるように、オブジェクト・ストレージへデータをアップロードします。データのアップロード後にパーティション分割された外部表を作成します。
最初にAPEXアプリケーションにページ・アイテムP1_YEARを作成します。
表BRICKSを年単位で分けて、オブジェクト・ストレージにアップロードします。
行数で分割するよりコードはわかりやすくなっています。
アップロードされたオブジェクトは以下のようになります。
外部表を作成します。プロシージャDBMS_CLOUD.CREATE_EXTERNAL_PART_TABLEを呼び出します。
表BRICKS_PART_EXTが作成されます。
元の表であるBRICKSとデータが同じであるか確認します。
15:10:30 SQL> select count(*) from bricks_part_ext;
COUNT(*)
___________
10000000
経過時間: 00:00:30.637
15:15:31 SQL> select brick_id,colour,shape,weight,insert_date,
2 to_char(insert_date,'RRRR') year, junk from bricks
3 minus
4* select * from bricks_part_ext;
行が選択されていません
経過時間: 00:00:47.329
15:17:03 SQL>
行数が10,000,000行あり、表BRICKSとの差分もないので、データは同じであることが確認できました。
元々は外部表のINMEMORYオプションを試すつもりだったのですが、それができるのは21cからでした。自分が目的としていた作業はできませんでしたが、これまで記載した手順が何かの参考になれば幸いです。
準備作業
管理ユーザーADMINにてデータベース・アクションに接続し、データベース・ユーザーを開き、ユーザーAPEXDEVのRESTの有効化を実施します。
#!/bin/sh
while read l
do
test ${l:0:1} != "-" && echo $l | tr -d '\r\n'
done < $1
echo
参考にしたサイト
https://blogs.oracle.com/datawarehousing/post/simplified-partitioning-xt
