なんの話

Autonomous DatabaseでDBMS_CLOUD.EXPORT_DATAでCSVファイルをエクスポートする方法と注意点

前段

Oracle Cloud Infrastructure（→OCI）上で使えるOracle Autonomous Database（→ADB）には、OCI Object StorageやAWS S3、Azure BLOBなどのクラウド上のストレージとの間で、ファイルをロードしたりエクスポートしたりするためのPL/SQLプログラムをまとめたDBMS_CLOUDというパッケージが付属しています（DBMS_CLOUDの一部はADB以外のOracleデータベースの新しめのバージョンにも含まれています）。

2021年12月のアップデートで、このDBMS_CLOUDに含まれるサブプログラムEXPORT_DATAに拡張が入り、CSV、JSON、XMLの形式でデータをエクスポートすることが可能になっています。これについて今のところ日本語の記事がなかったのでかんたんにポイントをまとめておきます。

なお、ここではOCI Object StorageへのCSVファイルの出力を扱っています。

やり方

準備

EXPORT_DATAの前段として以下の準備が必要です。

出力先のObject Storage Bucketを作成しておく
↑のBucketへのアクセス権を持ったユーザーの認証情報（ユーザー名と認証トークン）を控えておく
↑のユーザーの認証情報（クレデンシャル）をDBMS_CLOUD.CREATE_CREDENTIALでDBに登録しておく
DBにエクスポートする元のデータを用意しておく

このへんは↓のチュートリアルに親切に解説されているのでそちらを参照ください。エクスポートしがいのあるそこそこ大量のデータが積まれたテーブルも用意できるので一石二鳥です。

oracle-japan.github.io

EXPORT_DATAを実行する

準備ができたらあとはEXPORT_DATAを実行するだけです。

最低限のシンプルな形式では↓のかたちです。

BEGIN
 DBMS_CLOUD.EXPORT_DATA(
    credential_name =>'<保存したクレデンシャルの識別名>',
    file_uri_list =>'https://objectstorage.<リージョン>.oraclecloud.com/n/<Object Storageのネームスペース>/b/<バケット名>/o/<エクスポートファイル名接頭辞>',
    format => '{"type" : "csv"}',
    query => '<エクスポート対象のデータをクエリするSQL文>'
 );
END;
/

formatに指定するフォーマットオプションでファイル圧縮の有無やエクスポート時のファイル分割サイズ、区切り文字などいくつかいじれます。ドキュメントを参照ください。また、format部はJSON_OBJECT形式で記載することもできます。

以下はCUSTOMERSテーブルのデータ全量SELECT * FROM customersでクエリした結果を、USER_CREDのクレデンシャルを使い、東京リージョン（ap-tokyo-1）のhogehogeネームスペースのfugafugaバケットにcustomers_exportedというファイル名接頭辞でエクスポートする例です。また、maxfilesizeで10MBごとにファイルを分割する指定をしています。

BEGIN
 DBMS_CLOUD.EXPORT_DATA(
    credential_name =>'USER_CRED',
    file_uri_list =>'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/hogehoge/b/fugafuga/o/customers_exported',
    format => json_object(
        'type' value 'csv',
        'maxfilesize' value '10485760'
    ),
    query => 'SELECT * FROM customers'
 );
END;
/

注意点

執筆時点（2022/2/21）では以下のポイントに注意が必要かなと思います。今後の機能拡張や修正で解消されるかもしれません。

単一ファイルに出力を強制できない、ファイル名を完全には指定できない

データ量等に依らず、「必ず単一ファイルに出力」という指定ができません。ファイル分割のサイズ単位を指定するmaxfilesizeは最大2GBで、これを超える場合にはエクスポートされたファイルが分割されることになります。

また、エクスポートされたファイルのファイル名も完全には指定できず、接頭辞だけです。ファイル名には<接頭辞>_<シーケンス番号>_<タイムスタンプ>.<拡張子>といった形式に項目が自動的に補われます。例えば上記のcustomers_exportedを接頭辞に指定した例では、customers_exported_1_20220221T071006Z.csvといったファイル名でエクスポートされます。

サイズがmaxfilesizeを超えない場合にも、エクスポート処理分割の関係でシーケンス番号が分かれてエクスポートファイルが分割される場合もあります（データ量とADBのOCPU数に依存）。分割、ファイル名について詳しくはドキュメントを参照ください。

ヘッダがつかない

CSVにはヘッダ行が出力されず、データ行のみが書かれます。ヘッダ行を出力するためのオプションがありません。

区切り文字に特殊文字を指定する場合の注意

formatオプションのdelimiterで区切り文字を指定できます（デフォルトはカンマ）が、タブ文字などの特殊文字を指定しようとするとややエスケープのハンドリングが変なのか、エラーになるケースを発見しています。

例えばタブ文字の指定は前述のformatオプションについてのドキュメント上、

For example, the following specifies the TAB character as the delimiter: format => json_object('delimiter' value 'X''9''')

ということになっているんですが、これだとエラーになって動きませんでした（"ORA-20000: ORA-00923: FROM keyword not found where expected"）。'\t'も試したんですが、どうもJSON_OBJECT形式ではこれではダメらしく、そのまま"\t"で区切られてエクスポートされてきました。

回避策としてはJSON_OBJECT形式での指定を使わずに以下のように指定したところ無事タブ文字区切りでエクスポートされましたのでこちらを使いましょう（なお{"type" : "csv", delimiter" : "X''9''"}にしたらORA-20009: Format argument is not a valid JSONになった）。

 format => '{"type" : "csv", "delimiter" : "\t" }',

その他参考情報

Export Data as CSV to Cloud Object Storage

空谷に吼える

ブロックチェーン／DLTまわりのなにかしらを書いていく所存

Autonomous DatabaseからDBMS_CLOUD.EXPORT_DATAでObject Storage上のCSVファイルにエクスポート