データベースの内部表をオブジェクト・ストレージの外部表に置き換える

 データベースの内部表をオブジェクト・ストレージに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より実行します。

	exec dbms_random.seed ( 0 );
	create table bricks (
	brick_id not null constraint bricks_pk primary key,
	colour not null,
	shape not null,
	weight not null,
	insert_date not null,
	junk default lpad ( 'x', 50, 'x' ) not null
	) as
	with rws as (
	select level x from dual
	connect by level <= 10000000
	)
	select rownum brick_id,
	case ceil ( rownum / 2500000 )
	when 4 then 'red'
	when 1 then 'blue'
	when 2 then 'green'
	when 3 then 'yellow'
	end colour,
	case mod ( rownum, 4 )
	when 0 then 'cube'
	when 1 then 'cylinder'
	when 2 then 'pyramid'
	when 3 then 'prism'
	end shape,
	round ( dbms_random.value ( 1, 10 ) ),
	trunc(date'2011-01-01' + ( rownum/2400 ) + ( mod ( rownum, 24 ) / 36 )) insert_date,
	lpad ( 'x', 50, 'x' )
	from rws;

view raw bricks.sql hosted with ❤ by GitHub

% 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に与えます。

grant execute on dbms_cloud to apexdev;

grant read,write on directory data_pump_dir to apexdev;

管理ユーザーADMINにてデータベース・アクションのSQLを開き、GRANT文を実行します。

ユーザーAPEXDEVに必要な権限が割り当てられました。

続いて、オブジェクト・ストレージへのアクセスに使用するクリデンシャルDEF_CREDを作成します。

プロシージャDBMS_CLOUD.CREATE_CREDENTIALを呼び出します。認証トークンの生成手順は、本記事の末尾の準備作業のセクションに記載しています。

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とします。

	declare
	C_REGION constant varchar2(20) := 'us-ashburn-1';
	C_NAMESPACE constant varchar2(20) := 'ネームスペースに置き換え';
	C_BUCKET constant varchar2(20) := 'bricks';
	C_FILENAME constant varchar2(20) := 'bricks';
	l_path varchar2(400);
	begin
	l_path := 'https://objectstorage.' || C_REGION || '.oraclecloud.com/n/' || C_NAMESPACE
	|| '/b/' || C_BUCKET || '/o/' || C_FILENAME;
	dbms_output.put_line(l_path);
	dbms_cloud.export_data
	(
	credential_name => 'DEF_CRED'
	, file_uri_list => l_path
	, format => json_object('type' value 'csv')
	, query => 'SELECT * FROM bricks'
	);
	END;
	/

view raw export_data.sql hosted with ❤ by GitHub

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の内容を確認します。バケット内に複数のオブジェクトが作成されていることが確認できます。

エクスポートしたオブジェクトを参照する外部表BRICKS_EXTを作成します。

プロシージャDBMS_CLOUD.CREATE_EXTERNAL_TABLEを呼び出します。

	declare
	C_REGION constant varchar2(20) := 'us-ashburn-1';
	C_NAMESPACE constant varchar2(20) := 'ネームスペースに置き換え';
	C_BUCKET constant varchar2(20) := 'bricks';
	C_FILENAME constant varchar2(20) := 'bricks';
	l_path varchar2(400);
	begin
	l_path := 'https://objectstorage.' || C_REGION || '.oraclecloud.com/n/' || C_NAMESPACE
	|| '/b/' || C_BUCKET || '/o/' || C_FILENAME || '*.csv';
	dbms_output.put_line(l_path);
	dbms_cloud.create_external_table(
	credential_name => 'DEF_CRED'
	, table_name => 'bricks_ext'
	, file_uri_list => l_path
	, format => json_object(
	'type' value 'csv',
	'dateformat' value 'RR-MM-DD')
	, column_list =>
	'brick_id number,
	colour varchar2(6),
	shape varchar2(8),
	weight number,
	insert_date date,
	junk varchar2(50)'
	);
	end;
	/

view raw create_external_table.sql hosted with ❤ by GitHub

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から始まる行がエクスポートされていません。エクスポートされたオブジェクトをダウンロードして確認しました。

データが出力されるオブジェクト名が秒単位であるため、出力が速いと上書きしているように見えます。

上書きをさせない指定などがあるのかもしれませんが、調べても見つけることはできませんでした。残念ながら、今回の用途でDBMS_CLOUD.EXPORT_DATAは使用できません。

追記 - DBMS_CLOUD.EXPORT_DATAだけで処理できました。手順の最終版としてはこちらになります。速度も比較にならないほど速いです。

これ以降の内容は表データのエクスポートでは使うことはないと思います。APEXのAPIをAPEX以外から使用する方法の、ひとつの例として残しておきます。

CSVの生成とアップロードを別々に実施

Oracle APEXが提供しているPL/SQL APIのファンクションAPEX_DATA_EXPORT.EXPORTを呼び出しCSV形式のエクスポートを行い、オブジェクト・ストレージを操作するPL/SQL SDKを使ってオブジェクト・ストレージへアップロードします。

Oracle APEXのPL/SQL APIの多くは、呼び出す際にAPEXのセッションを必要とします。APEXセッションを開始するプロシージャAPEX_SESSION.CREATE_SESSIONを呼び出す際に、引数として与えるアプリケーションを作成します。

アプリケーション作成ウィザードを実行します。名前は外部表の操作とし、それ以外は設定せず、アプリケーションの作成を実行します。

アプリケーションが作成されたら、ページ・デザイナでホーム・ページを開きます。

タイプが静的コンテンツのリージョンをひとつ作成し、そこにページ・アイテムとしてP1_START_ROWおよびP1_END_ROWを作成します。双方ともタイプは数値フィールドとします。

オブジェクト・ストレージを操作するPL/SQL SDKを呼び出す権限を、APEXのワークスペース・スキーマに与えます。

管理ユーザーADMINにて実行します。

grant execute on dbms_cloud to apexdev;

grant execute on dbms_cloud_oci_obs_object_storage to apexdev;

grant execute on DBMS_CLOUD_OCI_OBS_OBJECT_STORAGE_PUT_OBJECT_RESPONSE_T to apexdev;

PL/SQL SDKの呼び出しに指定するクリデンシャルMY_OCI_CREDを作成します。作成済みのAPIキーを使用します。APIキーの作成手順は、本記事の末尾にある準備作業に記載しています。

プロシージャDBMS_CLOUD.CREATE_CREDENTIALを実行します。引数として与える値は、APIキーを作成したときに構成ファイルとして表示されていた情報と、一行にした秘密キーです。

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> 

10万行ごとにファイルを分割し、表BRICKSの内容をオブジェクト・ストレージにアップロードします。以下のスクリプトを実行します。

	declare
	-- 各種条件
	C_REGION constant varchar2(20) := 'us-ashburn-1';
	C_NAMESPACE constant varchar2(20) := 'ネームスペースに置き換える';
	C_BUCKET constant varchar2(20) := 'bricks';
	C_FILENAME constant varchar2(20) := 'bricks';
	C_ROWS constant number := 1000000; -- 10万行ずつ
	-- エクスポートするデータを取り出すSELECT文
	C_SQL constant varchar2(4000) :=
	q'~select brick_id, colour, shape, weight, insert_date, junk from (
	select * from (
	select
	brick_id
	, colour
	, shape
	, weight
	, insert_date
	, junk
	, row_number() over (order by null) apx$rownum
	from bricks
	) where apx$rownum <= :P1_END_ROW
	) where apx$rownum >= :P1_START_ROW~';
	-- APEX API
	l_context apex_exec.t_context;
	l_export apex_data_export.t_export;
	l_total_rows number;
	l_loop number;
	l_start number;
	l_end number;
	l_offset varchar2(4);
	-- アップロードするデータの属性
	l_blob blob;
	l_filename varchar2(80);
	l_content_type varchar2(80);
	-- OCI Object Storage PL/SQL SDK
	$IF true $THEN -- OCI PL/SQL SDKを使うときはtrue
	-- OCI PL/SQL SDKを呼び出してアップロード
	l_put_response dbms_cloud_oci_obs_object_storage_put_object_response_t;
	l_status_code integer;
	$ELSE
	-- UTL_FILEとDBMS_CLOUD.PUT_OBJECTを呼び出してアップロード
	l_file utl_file.file_type;
	l_buffer raw(32767);
	l_amount binary_integer := 32767;
	l_pos integer := 1;
	l_blob_len integer;
	l_path varchar2(400);
	$END
	begin
	-- APEXセッションの生成
	apex_session.create_session(
	p_app_id => 100
	, p_page_id => 1
	, p_username => 'APEXDEV'
	);
	-- 繰り返し回数を事前に計算する。
	select count(*) into l_total_rows from bricks;
	l_loop := ceil(l_total_rows / C_ROWS);
	/*
	* 開始行、終了行を変えながら、指定された範囲を
	* CSV形式でオブジェクト・ストレージに順次書き込む。
	*/
	for i in 1..l_loop
	loop
	l_offset := to_char(i,'FM0999');
	l_start := ((i - 1) * C_ROWS) + 1;
	l_end := (i * C_ROWS);
	-- 開始行、終了行をバインド変数(ページ・アイテム)として渡す
	apex_util.set_session_state('P1_START_ROW', l_start, false);
	apex_util.set_session_state('P1_END_ROW', l_end, false);
	-- SELECT文のパース。
	l_context := apex_exec.open_query_context(
	p_location => apex_exec.c_location_local_db
	, p_sql_query => C_SQL
	);
	-- SELECT文の実行と結果のBLOBへの書き出し。
	l_export := apex_data_export.export (
	p_context => l_context
	, p_format => apex_data_export.c_format_csv
	, p_file_name => C_FILENAME || l_offset
	);
	-- 書き出されたBLOBのデータをオブジェクト・ストレージに保存。
	$IF true $THEN -- ファイルを圧縮するときはtrue
	-- ファイルを圧縮する
	l_blob := utl_compress.lz_compress(l_export.content_blob);
	l_filename := C_FILENAME || l_offset || '.csv.gz';
	l_content_type := 'application/gzip';
	$ELSE
	-- 非圧縮版
	l_blob := l_export.content_blob;
	l_filename := C_FILENAME || l_offset || '.csv';
	l_content_type := 'text/csv; charset=utf-8';
	$END
	$IF true $THEN -- OCI PL/SQL SDKを使うときはtrue
	l_put_response := dbms_cloud_oci_obs_object_storage.put_object
	(
	namespace_name => C_NAMESPACE
	, bucket_name => C_BUCKET
	, object_name => l_filename
	, content_type => l_content_type
	, put_object_body => l_blob
	, region => C_REGION
	, credential_name => 'MY_OCI_CRED'
	);
	l_status_code := l_put_response.status_code;
	$ELSE
	l_blob_len := dbms_lob.getlength(l_blob);
	l_file := utl_file.fopen('DATA_PUMP_DIR','bricks.csv','wb',32767);
	l_pos := 1;
	l_amount := 32767;
	while l_pos <= l_blob_len
	loop
	dbms_lob.read(l_blob, l_amount, l_pos, l_buffer);
	utl_file.put_raw(l_file, l_buffer, TRUE);
	l_pos := l_pos + l_amount;
	end loop;
	utl_file.fclose(l_file);
	l_path := 'https://objectstorage.' || C_REGION || '.oraclecloud.com/n/' || C_NAMESPACE
	|| '/b/' || C_BUCKET || '/o/' || l_filename;
	dbms_cloud.put_object(
	credential_name => 'DEF_CRED'
	, object_uri => l_path
	, directory_name => 'DATA_PUMP_DIR'
	, file_name => 'bricks.csv'
	);
	$END
	-- SELECT文の解放。
	apex_exec.close( l_context );
	end loop;
	$IF false $THEN -- UTL_FILEを使うときはtrue
	-- ファイルの削除
	dbms_cloud.delete_file('DATA_PUMP_DIR','bricks.csv');
	$END
	exception
	when others then
	apex_exec.close( l_context );
	raise;
	end;
	/

view raw export_data_apex.sql hosted with ❤ by GitHub

オブジェクト・ストレージへ保存するデータは圧縮しています。非圧縮で保存する方法もコードに含めています。また、OCI PL/SQL SDKではなく、DBMS_CLOUD.PUT_OBJECTを呼び出す方法についてもコードに含めています。

北米リージョン（us-ashburn-1）で実行したところ、すべてのデータがアップロードされるまでに４０分程度の時間がかかりました。

外部表の作成には以下のスクリプトを作成します。外部表BRICKS_EXTが作成済みの場合は、あらかじめ削除しておきます。

drop table bricks_ext;

	declare
	C_REGION constant varchar2(20) := 'us-ashburn-1';
	C_NAMESPACE constant varchar2(20) := 'ネームスペースに置き換える';
	C_BUCKET constant varchar2(20) := 'bricks';
	C_FILENAME constant varchar2(20) := 'bricks';
	l_path varchar2(400);
	l_filename varchar2(80);
	l_format clob;
	begin
	$IF true $THEN -- 圧縮されている場合
	l_filename := C_FILENAME || '*.csv.gz';
	l_format := json_object(
	'type' value 'csv',
	'dateformat' value 'RR-MM-DD',
	'skipheaders' value '1',
	'compression' value 'auto'
	);
	$ELSE
	l_filename := C_FILENAME || '*.csv';
	l_format := json_object(
	'type' value 'csv',
	'dateformat' value 'RR-MM-DD',
	'skipheaders' value '1'
	);
	$END
	l_path := 'https://objectstorage.' || C_REGION || '.oraclecloud.com/n/' || C_NAMESPACE
	|| '/b/' || C_BUCKET || '/o/' || l_filename;
	dbms_cloud.create_external_table(
	credential_name => 'DEF_CRED'
	, table_name => 'bricks_ext'
	, file_uri_list => l_path
	, format => l_format
	, column_list =>
	'brick_id number,
	colour varchar2(6),
	shape varchar2(8),
	weight number,
	insert_date date,
	junk varchar2(50)'
	);
	end;
	/

view raw create_external_table_apex.sql hosted with ❤ by GitHub

APEX_DATA_EXPORT.EXPORTを呼び出してCSV形式での出力を行なっているため、出力されたすべてのファイルにヘッダーが含まれています。そのためformatにskipheadersを含めています。

作成された外部表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を年単位で分けて、オブジェクト・ストレージにアップロードします。

	declare
	-- 各種条件
	C_REGION constant varchar2(20) := 'us-ashburn-1';
	C_NAMESPACE constant varchar2(20) := 'ネームスペースに置き換える';
	C_BUCKET constant varchar2(20) := 'bricks';
	C_FILENAME constant varchar2(20) := 'bricks';
	-- エクスポートするデータを取り出すSELECT文
	C_SQL constant varchar2(4000) :=
	q'~select
	brick_id
	, colour
	, shape
	, weight
	, insert_date
	, to_char(insert_date,'RRRR') year
	, junk
	from bricks
	where trunc(insert_date,'YEAR') = to_date(:P1_YEAR,'RRRR-MM-DD')~';
	-- APEX API
	l_context apex_exec.t_context;
	l_export apex_data_export.t_export;
	l_total_rows number;
	l_loop number;
	l_start number;
	l_end number;
	l_offset varchar2(4);
	-- アップロードするデータの属性
	l_blob blob;
	l_filename varchar2(80);
	l_content_type varchar2(80);
	-- OCI Object Storage PL/SQL SDK
	$IF true $THEN -- OCI PL/SQL SDKを使うときはtrue
	-- OCI PL/SQL SDKを呼び出してアップロード
	l_put_response dbms_cloud_oci_obs_object_storage_put_object_response_t;
	l_status_code integer;
	$ELSE
	-- UTL_FILEとDBMS_CLOUD.PUT_OBJECTを呼び出してアップロード
	l_file utl_file.file_type;
	l_buffer raw(32767);
	l_amount binary_integer := 32767;
	l_pos integer := 1;
	l_blob_len integer;
	l_path varchar2(400);
	$END
	begin
	-- APEXセッションの生成
	apex_session.create_session(
	p_app_id => 100
	, p_page_id => 1
	, p_username => 'APEXDEV'
	);
	/*
	* 年ごとにCSV形式でオブジェクト・ストレージに順次書き込む。
	*/
	for c in
	(
	-- 年単位でパーティションに分割する
	select to_char(year,'RRRR') year, to_char(year,'RRRR-MM-DD') new_year_day
	from (
	select distinct trunc(insert_date,'YEAR') year from bricks
	) order by year asc
	)
	loop
	apex_util.set_session_state('P1_YEAR',c.new_year_day,false);
	-- SELECT文のパース。
	l_context := apex_exec.open_query_context(
	p_location => apex_exec.c_location_local_db
	, p_sql_query => C_SQL
	);
	-- SELECT文の実行と結果のBLOBへの書き出し。
	l_export := apex_data_export.export (
	p_context => l_context
	, p_format => apex_data_export.c_format_csv
	, p_file_name => C_FILENAME || '-' || c.year
	);
	-- 書き出されたBLOBのデータをオブジェクト・ストレージに保存。
	$IF true $THEN -- ファイルを圧縮するときはtrue
	-- ファイルを圧縮する
	l_blob := utl_compress.lz_compress(l_export.content_blob);
	l_filename := C_FILENAME || '/' || 'year=' || c.year || '/'
	|| C_FILENAME || '-' || c.year || '.csv.gz';
	l_content_type := 'application/gzip';
	$ELSE
	-- 非圧縮版
	l_blob := l_export.content_blob;
	l_filename := C_FILENAME || '/' || 'year=' || c.year || '/'
	|| C_FILENAME || '-' || c.year || '.csv';
	l_content_type := 'text/csv; charset=utf-8';
	$END
	$IF true $THEN -- OCI PL/SQL SDKを使うときはtrue
	l_put_response := dbms_cloud_oci_obs_object_storage.put_object
	(
	namespace_name => C_NAMESPACE
	, bucket_name => C_BUCKET
	, object_name => l_filename
	, content_type => l_content_type
	, put_object_body => l_blob
	, region => C_REGION
	, credential_name => 'MY_OCI_CRED'
	);
	l_status_code := l_put_response.status_code;
	$ELSE
	l_blob_len := dbms_lob.getlength(l_blob);
	l_file := utl_file.fopen('DATA_PUMP_DIR','bricks.csv','wb',32767);
	l_pos := 1;
	l_amount := 32767;
	while l_pos <= l_blob_len
	loop
	dbms_lob.read(l_blob, l_amount, l_pos, l_buffer);
	utl_file.put_raw(l_file, l_buffer, TRUE);
	l_pos := l_pos + l_amount;
	end loop;
	utl_file.fclose(l_file);
	l_path := 'https://objectstorage.' || C_REGION || '.oraclecloud.com/n/' || C_NAMESPACE
	|| '/b/' || C_BUCKET || '/o/' || l_filename;
	dbms_cloud.put_object(
	credential_name => 'DEF_CRED'
	, object_uri => l_path
	, directory_name => 'DATA_PUMP_DIR'
	, file_name => 'bricks.csv'
	);
	$END
	-- SELECT文の解放。
	apex_exec.close( l_context );
	end loop;
	$IF false $THEN -- UTL_FILEを使うときはtrue
	-- ファイルの削除
	dbms_cloud.delete_file('DATA_PUMP_DIR','bricks.csv');
	$END
	exception
	when others then
	apex_exec.close( l_context );
	raise;
	end;
	/

view raw export_data_apex_part.sql hosted with ❤ by GitHub

行数で分割するよりコードはわかりやすくなっています。

アップロードされたオブジェクトは以下のようになります。

外部表を作成します。プロシージャDBMS_CLOUD.CREATE_EXTERNAL_PART_TABLEを呼び出します。

	declare
	C_REGION constant varchar2(20) := 'us-ashburn-1';
	C_NAMESPACE constant varchar2(20) := 'ネームスペースに置き換える';
	C_BUCKET constant varchar2(20) := 'bricks';
	C_FILENAME constant varchar2(20) := 'bricks';
	l_path varchar2(400);
	l_filename varchar2(80);
	l_format clob;
	begin
	$IF true $THEN -- 圧縮されている場合
	l_filename := C_FILENAME || '*.csv.gz';
	l_format := json_object(
	'type' value 'csv',
	'dateformat' value 'RR-MM-DD',
	'skipheaders' value '1',
	'compression' value 'auto',
	'partition_columns' value '[{"name":"year","type":"varchar2(4)"}]' format json
	);
	$ELSE
	l_filename := C_FILENAME || '*.csv';
	l_format := json_object(
	'type' value 'csv',
	'dateformat' value 'RR-MM-DD',
	'skipheaders' value '1',
	'partition_columns' value '[{"name":"year","type":"varchar2(4)"}]' format json
	);
	$END
	l_path := 'https://objectstorage.' || C_REGION || '.oraclecloud.com/n/' || C_NAMESPACE
	|| '/b/' || C_BUCKET || '/o/' || l_filename;
	dbms_cloud.create_external_part_table(
	credential_name => 'DEF_CRED'
	, table_name => 'bricks_part_ext'
	, file_uri_list => l_path
	, format => l_format
	, column_list =>
	'brick_id number,
	colour varchar2(6),
	shape varchar2(8),
	weight number,
	insert_date date,
	year varchar2(4),
	junk varchar2(50)'
	);
	end;
	/

view raw create_external_table_apex_part.sql hosted with ❤ by GitHub

表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の有効化を実施します。

Autonomous DatabaseのコンソールよりDB接続を実行し、DB接続に使用するウォレット・ファイルをダウンロードします。今回の例ではデータベース名はDWAPEXなので、Wallet_DWAPEX.zipになります。

グループAdministratorsに所属しているユーザーにて、認証トークンを生成します。

APIアクセスに使用するクリデンシャルとなるユーザーに管理者を使うのことは、セキュリティ上望ましくありません。別にユーザーを作成し、必要最小限の権限を与えた上で認証トークン（このあとにAPIキーも同様）を生成することを推奨します。

生成した認証トークンはどこかに保存しておきます。データベース側で、オブジェクト・ストレージにアクセスする際のクリデンシャルとして使用します。

オブジェクト・ストレージに、バケットbricksを作成しておきます。

データを保存するオブジェクト・ストレージのリージョンとネームスペースを確認しておきます。

PL/SQL SDKを呼び出すときに使用する、APIキーを作成します。

APIキーの追加をクリックします。

APIキー・ペアの作成を選択し、秘密キーのダウンロードをクリックします。

追加をクリックします。

構成ファイルのプレビューが表示されます。この内容をコピーし、どこかに保存しておきます。PL/SQL SDKを呼び出すときに使用するクリデンシャルを作成する際に使用します。

閉じるをクリックします。

APIキーを追加したときにダウンロードされた秘密キーのファイルの内容は、ヘッダーを削除した上で一行にする必要があります。

以下のシェル・スクリプトにダウンロードされたファイル名を引数として与えることにより、DBMS_CLOUD.CREATE_CREDENTIALのprivate.keyとなるデータを作成できます。

#!/bin/sh

while read l

do

   test ${l:0:1} != "-" && echo $l | tr -d '\r\n'

done < $1

echo

本編に含まれなかった準備作業の説明は以上になります。

参考にしたサイト

BLOBをファイルに書き出す方法について、ORACLE-BASEの記事を参照しています。

Export BLOB Contents Using UTL_FILE

https://oracle-base.com/articles/9i/export-blob-9i

パーティション化された外部表の作成について、オラクルの公式ブログの記事を参照しています。

Create and manage Partitioned External Tables easier than ever before
https://blogs.oracle.com/datawarehousing/post/simplified-partitioning-xt

完