DBMS_CLOUDとData Studioデータローディングツールを使用したシンプルなエクスポート/インポート (2022/12/01)

https://blogs.oracle.com/datawarehousing/post/loading-data-from-oracle-cloud-store-and-dbmscloudexportdata

投稿者： William Endress | Autonomous Database Product Management

はじめに

Oracle Autonomous Databaseは、Oracle Cloud Infrastructure Object Storeを介してインスタンスからデータを迅速、簡単、かつ安全にエクスポートし、別のインスタンスにロードするために必要なあらゆるものを提供します。この記事では、データベースの組み込み機能を使用してデータをクラウドストアにエクスポートし、ADWのData Studioツールを使用してクラウドストアからロードする例について説明します。 とてもシンプルなプロセスです。

1.     DBMS_CLOUD.EXPORT_DATAを使用してクエリからデータをエクスポートします。
2.     Data Studioで、データへのアクセスに必要な認証情報（ある場合）と共にこの場所を保存するCloud Store Locationを作成します。
3.     データロードジョブを作成し、実行します。

Oracle Autonomous Databaseには、さまざまな場所からさまざまな種類のファイルをロードするために使用できる、すばらしいツールのコレクションが含まれています。概要を知りたい場合は、ブログポスト「Data Loading Make Easy in Oracle Autonomous Database」をご覧ください。

EXPORT_DATAとData Studioデータロートツールについて

EXPORT_DATAとData Studioデータロードツールは、いずれもAutonomous Databaseに付属しています。 クラウドオブジェクトストアにデータをエクスポートし、そのデータにアクセスしてAutonomous Datatabaseのテーブルを作成およびロードするための簡単で効果的な手段を提供します。

dbms_cloud.export_data

EXPORT_DATA プロシージャは、Oracle のテーブルやビューから Oracle Cloud Infrastructure (OCI) Object Store などのクラウドベースのObject Storageにデータをエクスポートするためのシンプルで効率的な方法です。 EXPORT_DATA は、CSV、JSON、XML などのさまざまな形式でエクスポートでき、gzip 圧縮ファイルにエクスポートすることも可能です。

EXPORT_DATAは、CSV、JSON、XMLなどさまざまな形式でエクスポートでき、gzip圧縮ファイルへのエクスポートも可能です。

•     クエリからデータをエクスポートし、エクスポートの内容をカスタマイズすることができます。
•     並列処理を使用して、マルチパートファイルへエクスポートします。つまり、各クエリからのデータは複数のファイルにエクスポートされ、その集合が1つの論理ファイルを構成します。これにより、EXPORT_DATA は複数のプロセスを並行して使用して非常に高速に実行されます。

対応する DBMS_CLOUD のデータロード手順は、ワイルドカードを使用した URI を使用して、マルチパートファイルから簡単にデータをロードすることができます。

Data Studioデータロードツール

Data Studio は、Autonmous Datadata のテーブルへのデータの作成とロードを支援する一連の ツールを提供します。 以下のことが可能です。

•     ローカルコンピューター上のCSV、Excel、JSONファイルからテーブルを作成し、ロードすることができます。
•     Oracle、Amazon S3、Google、Azureのクラウドストアを登録するクラウドロケーションを作成します。
•     クラウドストアからCSV、JSON、Parquet、Avroのデータファイルをロードし、テーブルを作成します。
•     クラウドストアに接続する外部テーブルを作成し、データに動的にアクセスできます。

Data Studioのデータロードツールは、DBMS_CLOUDを使用してクラウドストアのデータをロードして接続します。

この記事で使用したサンプルデータ

この投稿では、Oracle LiveLabsワークショップ「Load and update Moviestream data in Oracle ADW using Data Tools」から、Movie Sales Dataを使用しました。 ラボで説明した方法でデータをテーブルにインポートし、DBMS_CLOUD.EXPORT_DATAを使ってオブジェクトストア上のバケットにデータをエクスポートしてみました。

Object Storeへのデータエクスポート

LiveLabで使用しているオリジナルデータと同様に、各月のデータを別々にエクスポートしています。しかし、各月を1つのファイルにエクスポートするLiveLabとは異なり、EXPORT_DATAを使用して、各月をマルチパートファイルを含む別々のフォルダにエクスポートしています。

バケットへの書き込み権限を持つ事前認証リクエスト(PAR)を使って、プレフィックスを使用してエクスポートしました。 PAR にはパスワードが埋め込まれているので、データベースのクレデンシャルを使用する必要はありません。 

以下に例を示します。

BEGIN
  dbms_cloud.export_data (
    file_uri_list => 'https://objectstorage.uk-london-1.oraclecloud.com/p/dgfs$%C$%S/n/adwc4pm/b/data_library/o/d1705/table=MOVIE_SALES_FACT/partition=APR-2019/APR-2019',
    format => '{"type":"CSV","delimiter":",","maxfilesize":536870912,"header":true,"compression":null,"escape":"true","quote":"\""}',
    query => 'select * from MOVIE_SALES_FACT WHERE MONTH = ''APR-2019''');
END;
/

データをエクスポートするには、DBMS_CLOUDのEXECUTE権限が必要なことに注意してください。

データはdata_libraryバケット内のmovie_salesフォルダにエクスポートされます。 このフォルダには複数のテーブルをエクスポートする可能性があるため、このテーブルを table=MOVIE_SALES_FACT サブフォルダにエクスポートしています。 各月は、partition= フォルダにエクスポートされます。 この命名規則を使用するのは、ファイルがどのテーブルに属しているか、また各ファイルの内容を簡単に理解できるようにするためです。 フォルダ構造が自己文書化されているので、これはうまくいっています。 

エクスポートされたデータの月ごとに1つのフォルダを持つバケットリスト

 

先ほども書きましたが、DBMS_CLOUD.EXPORT_DATAはクエリをマルチパートファイルにエクスポートすることができます。 これには2つの利点があります。

    並列処理により、クエリをより高速にファイルにエクスポートできます。

    各プロセスはより少ないメモリしか使用しないので、利用可能なリソースの実行を待つ必要が少なくなります。

エクスポートのパフォーマンスとデータロードの間には、トレードオフの関係がある場合があります。 行や列の数が多い大きなテーブルの場合、並列処理が進み、各ファイルのメモリ使用量が少なくなるため、小さなファイルを多数作成した方が、エクスポートが速くなる場合があります。しかし、データのロードは、通常、ファイル数が少ないほど効率的です。 使用するケースに適したバランスを得るために、いくつかの実験が必要な場合があります。

MAXFILESIZEパラメータが小さく設定されているほど、より多くのファイルが作成されます。  最も小さいMAXFILESIZEは10MBで、これはデフォルト値です。 また、最大のMAXFILESIZEは2GBであることに注意してください。HIGH リソースコンシューマーグループは、通常、より大きな並列化と、より多くの小さなファイルの作成につながります。

MovieStream SALES_FACTテーブルを使用した私の例では、デフォルトのMAXFILESIZE 10MBを使用して、EXPORT_DATAは35回のエクスポート（やはり各月に1回）で合計5,765個のファイルを作成しました。 MAXFILESIZE を 512MB に増やした場合、エクスポートでは 132 個のファイルが作成されました。 MOVIE_SALES_FACTテーブルはそれほど大きくないため、大量の小さなファイルではなく、少数の大きなファイルにエクスポートする場合のパフォーマンスにはほとんど差がありませんでした。

新しいテーブルの作成とデータのロード

もし、この作業に適したツールがなければ、このマルチパートファイルのセットをロードするのは難しいと考えるのは正しいかもしれません。しかし、Data Studioのデータロードツールを使えば、とても簡単です。

ステップ1 - クラウドロケーションの作成

Cloud Locationは、Data StudioのData Loadツールで作成されるオブジェクトです。 URLと、必要であればそのURLにアクセスするためのクレデンシャルを保存することができます。今回は事前認証のリクエストを使用するので、クレデンシャルは必要ありません。

このステップをスキップして、ファイルの URL を Data Load from Cloud Store ダイアログに貼り付けることもできました。クラウドロケーションを作成することで、再利用性が高まるという利点があります。

まず、Data Studio の Data Load tools にアクセスし、Cloud Locations カードを選択します。

Data Studioでクラウドストアのロケーションにアクセス

 

そして、Add Cloud Store Locationを選択します。

クラウドストアの場所を追加

 

Cloud Store Locationの名前、説明、Bucket URIを入力し、Public Bucketを選択します。  パブリックバケットと事前認証リクエストはどちらもクレデンシャルを必要としないので、パブリックバケットを選択します。 そして、Nextボタンを押します。

クラウドストアの場所設定

 

ツールはURIを検証し、バケツ内のファイルを表示します。 

楽しい事実

•     事前認証されたリクエストには有効期限があります。この記事を読む頃には、この事前認証リクエストの有効期限は切れています。私のデータは安全でセキュアです
•     オブジェクトストアのフォルダは、PCやMac OSのファイルシステムで考えられているようなフォルダではありません。 フォルダは、ファイル名の一部に過ぎません。ツールによって、その表示方法は異なります。

Createボタンを押すと、クラウドストアのロケーションの作成が完了します。

Add Cloud Store Locationツールのファイル一覧

 

これで、再利用可能な新しいクラウド ストア ロケーションができました。 

保存されたクラウドストアの場所

ステップ2 - データのロードジョブを作成

ステップ1は簡単でしたが、ここが難しいところでしょう？すべてのフォルダにファイルがある？ 心配いりません、こちらも簡単です。

Database Actionsに戻り、Data Loadカードを選択します。 今回は、Load Data, Cloud StoreとNextを選択して進みます。

Load Data from Cloud Storeツールにアクセス

 

画面上部にあるCloud Store Locationを選択します。 ツールは、ナビゲーターツリーにクラウドストアのコンテンツを表示します。

MovieStream Sales Fact Cloud Store のロケーションにあるフォルダとファイル

 

トップレベルのフォルダを MOVIE_SALES_FACT テーブル用に設定したので（したがって、すべてのサブフォルダはそのテーブルのデータです）、トップレベルのフォルダを 'cart' エリアにドラッグすればよいのです。

このフォルダをカートリージョンにドラッグすることで、これらのファイルはすべて同じテーブルに属していることをツールに伝えることができます。  このフォルダ内のファイルはすべてそのテーブルに属しており、すべて同じフォーマットであることが非常に重要です。 

cart / MOVIE_SALES_FACTテーブルを読み込むためのジョブ

 

カートの鉛筆をクリックすると、ジョブがどのように設定されるかがわかります。 データロードツールがデータロードジョブの設定に優れた働きをしていることがわかります。

データロードジョブの設定

 

デフォルトの動作は、新しいテーブルを作成することです。 これは、選択したフォルダをテーブル名として使用します。テーブル名をMOVIE_SALES_FACTに変更します。 テーブルを範囲やリストで分割することもできますが、今はそんなことはしません。

このツールが本当に優れているのは、データファイルとデフォルトのカラムマッピングを検査するところです。 ファイルにはヘッダーがあり、それがカラム名として使用されます。 

プロからのアドバイス：データのロードを簡単にするには、エクスポートファイルのヘッダーから始めます。DBMS_CLOUD.EXPORT_DATA はクエリのカラム名をファイルのカラムヘッダとして使用するので、非常に簡単に良い名前を提供することができます。 シンプルで引用符のないカラム名とヘッダーは、通常、最も作業しやすいものです。

Data Loadツールは、すべてのカラムに対して正しいデータ型を選択しました。VARCHAR2 カラムには、Auto が選択されています。データロードツールは、データがロードされた後、列の長さを安全なサイズに自動的に調整します。

左側のFileタブを選択すると、ファイル内のデータをプレビューすることができます。デフォルトでは1つのファイルが選択されています。ドロップダウンリストから他のファイルを選択することができます。

ファイル内のデータをプレビュー

 

テーブルはまだ存在しないので、Tableタブはスキップしてください。テーブルの作成とジョブの実行に使用されるSQLを表示することができます。

データロードジョブ用のSQL

 

これをSQL Worksheetにコピー＆ペーストして、そのままSQLを実行することができます。 今回はツールに実行させることにします。  これを閉じて、カートのすぐ上にある緑のボタンをポチッと押してジョブを実行します。ジョブをできるだけ早く実行したいので、HIGHコンシューマーグループを使い、ジョブにできるだけ多くのリソースを提供することにします。

データロードジョブの開始

 

緑のチェックボックスは、ジョブがエラーなく完了したことを示します。 インスタンスにかかる時間は、利用可能なリソースと、バケットに対するインスタンスの位置に依存します。 データベースとBucketが同じリージョンにある場合に、最高のパフォーマンスを発揮することができます。

よくできた仕事！？

クラウドストアの場所のショートカット

トップレベルフォルダーのURLをURLフィールドに貼り付けることで、クレデンシャルが不要な場合は、クラウドストアロケーションの作成とデータロードジョブのセットアップを1つのステップにまとめることができます。  ここからのプロセスは同じです。フォルダをカートエリアにドラッグするだけで、ロード処理を継続できます。

まとめ

クラウドストアからOracle Autonomous Databaseにデータをロードする方法には、さまざまなものがあります。 Data StudioのData Loadツールは、最も簡単な方法でしょう。 すべてGUIで、コーディングは不要です。 ファイルの検査やデータ型の自動検出が非常に効果的です。 Data Loadツールは、さまざまな場所（Oracle Cloud Store、Amazon S3など）からのさまざまな形式（CSV/Text、JSON、Parquet、Avro）に対応しています。 また、すべてDBMS_CLOUDパッケージをベースにしているため、素晴らしいパフォーマンスを提供します。

Oracle Autonomous Databaseの詳細

Oracle Autonomous Databaseとその組み込みData Studioの詳細については、Oracle.comを参照してください。

LiveLabsを使用して実際に試してみる

Data Tools using Oracle ADWにおけるMoviesStreamデータのロードと更新」ワークショップを実行して、データ・ロード・ツールを実際に試してみてください。

データローディングツールの動作を見る

YouTubeで20分間のデータローディングの詳細をご覧ください。