Autonomous DatabaseとAzure Blob Storageを連携したときの自分用メモ

18731 ワード
oracle Autonomous Azure BlobStorage oci Azure テキストリンク

■ この記事で記載すること

Autonomous Database（ADB）からAzure Blob Storage（Blob）上のデータを、取り扱うための方法について自分用メモを記載します。
具体的には、以下の内容を記載します。

  • Blob上のデータをADBにロードする
  • Blob上のデータをADBの外部表として利用する

■ システム構成イメージ

ADB on OCI -- Azure Blob Storage

  • OCI上のADBとAzure Blobとをインターネット経由で接続
  • 本番環境などで利用する場合は、OCI/Azure間を直接接続を経由する

■ 事前準備

● Azureのセットアップ

  • ストレージアカウントのセットアップ
  • コンテナの作成
  • 公開設定

- ストレージアカウントのセットアップ

リソースの作成 → ストレージアカウントの作成

基本
・リソースグループ作成
・ストレージアカウント名の作成
・冗長性：ローカル冗長ストレージを選択

※基本以外はデフォルト

- コンテナの作成

● ADBのセットアップ

こちらのサイトを参考にADBをセットアップ

● BlobにADBからアクセスする

Database Actionsを開く

  • Database Actionsツールで、登録したクレデンシャルを使って、Cloud Locationsに、Blob（blob2adb）を登録

上記で登録したCloud Locationsが登録されていることを確認

■ Blob上のデータ(CSV)をADBにロードする

上記で登録したCloud LocationsからCSVファイルをADBにロードする

blob2adbにアプロードしたファイルが表示されていることを確認

ロードするファイルをドラッグアンドドロップで右ペインにコピーの上、実行ボタンをクリックする

データロードが成功したことを確認

ロードされたデータをDevelopment/SQLを用いて確認

select

■ Blob上のデータ(JSON)をADBにロードする

  • JSONファイルの生成

以下コマンドでADBの一覧を取得生成

oci db autonomous-database list|jq -c

実際に生成されたJSONデータのイメージ

  • 生成したJSONファイルをblob2adbにアップロード

  • blob2adbにアップロードしたJSONファイルをDatabase Actionsを用いてADBにロード

  • ロードしたデータの確認

  • ロードにあわせて自動生成されるView（JSON→二次元表）の確認

  • JSONデータから二次元表のViewを生成するDDLを確認

■ Blob上のデータをADBの外部表として利用する

はじめてのAutonomous Databaseへのデータロード（Object Storage経由の場合）の記事の内容をAzure Blobで実施します。

●外部表とは？ 外部表を使ったデータロードとは？

上記記事からの引用

外部表とは？ 外部表を使ったデータロードとは？

EXTERNAL TABLE とは文字通り外部表です。外部表の機能自体は古くから(Oracle Database 9i～)存在し、Oracle Databaseの外にある、例えばOS上のCSVファイルを、あたかもデータベース上の表のように見せるための機能です。Autonomous Databaseの場合は、Object Storage上のファイルを外部表として参照・定義するのにDBMS_CLOUD.CREATE_EXTERNAL_TABLEを使用します。

CSVファイルを外部表として定義して、

CREATE TABLE <新規表> AS SELECT * from <外部表>;
または INSERT INTO TABLE <既存表> SELECT * from <外部表>;

のように、外部表のデータを参照して実表にデータをコピーする、というのが、外部表を使用したデータロードになります。

（テキストファイルからのデータロードといえばSQL*Loaderが有名ですが、SQL*Loaderを使うよりもメリットの多い、外部表を使ったデータロードをこれまでも好んで使ってきました。）

● 取り扱うデータ

customer.csv
顧客ID,名前,ふりがな,メールアドレス,性別,生年月日,婚姻,都道府県,電話番号,職業
C10001,百瀬 丈史,ももせ たけし,[email protected],男,1938/09/25,既婚,石川県,000-4101-2921,営業
C10002,津田 まみ,つだ まみ,[email protected],女,1938/09/27,既婚,岡山県,000-2148-4441,建築
C10003,小寺 優,こでら ゆう,[email protected],女,1938/10/8,既婚,京都府,000-5128-4436,事務
C10004,横川 遥,よこかわ はるか,[email protected],女,1938/11/15,既婚,東京都,000-8588-7054,技能工
C10005,荻原 陽子,はぎわら ようこ,[email protected],女,1938/12/18,既婚,神奈川県,000-4449-6819,自営業
C10006,大橋 陽子,おおはし ようこ,[email protected],女,1939/01/31,既婚,高知県,000-9016-2328,営業
C10007,菅沼 なつみ,すがぬま なつみ,[email protected],女,1939/02/26,既婚,山形県,000-4527-6536,営業
C10008,外山 砂羽,そとやま さわ,[email protected],女,1939/04/07,既婚,熊本県,000-9216-7220,登録なし
C10009,藤原 千佳子,ふじわら ちかこ,[email protected],女,1939/05/04,既婚,東京都,000-3076-4029,登録なし
C10010,北村 広司,きたむら こうじ,[email protected],男,1939/05/20,既婚,広島県,000-6004-3842,自営業
C10011,武井 瑠璃亜,たけい るりあ,[email protected],女,1939/05/29,既婚,佐賀県,000-8683-3579,自営業
C10012,白川 美佳,しらかわ みか,[email protected],女,1939/06/06,既婚,宮城県,000-2328-4569,自営業
C10013,岩谷 ヒロ,いわたに ひろ,[email protected],男,1939/07/15,既婚,神奈川県,000-9885-4766,建築
C10014,佐川 雅彦,さがわ まさひこ,[email protected],男,1939/08/21,既婚,兵庫県,000-2687-7932,営業
C10015,上条 聖陽,かみじょう まさあき,[email protected],男,1939/09/19,既婚,富山県,000-1432-7322,教育
C10016,小松 まひる,こまつ まひる,[email protected],女,1939/10/28,既婚,福岡県,000-8134-3716,サービス業
C10017,豊田 倫子,とよた のりこ,[email protected],女,1939/12/14,既婚,広島県,000-2424-5328,事務
C10018,山野 ひとみ,やまの ひとみ,[email protected],女,1940/03/13,既婚,山梨県,000-4565-7877,クリエイター
C10019,及川 健,おいかわ けん,[email protected],男,1940/03/14,既婚,埼玉県,000-1283-2386,営業
C10020,安藤 敦,あんどう あつし,[email protected],男,1940/04/28,既婚,山形県,000-7495-8133,技能工
C10021,竹中 徹,たけなか とおる,[email protected],男,1980/07/01,既婚,岐阜県,000-1554-3673,営業
C10022,湊 浩介,みなと こうすけ,[email protected],男,1940/09/15,既婚,兵庫県,000-0371-0676,サービス業
C10023,緒形 夏空,おがた そら,[email protected],女,1980/09/16,既婚,滋賀県,000-7475-5465,教育
C10024,小泉 一恵,こいずみ かづえ,[email protected],女,1940/11/07,既婚,茨城県,000-8759-9978,士業
C10025,竹田 友香,たけだ ともか,[email protected],女,1980/12/23,既婚,秋田県,000-0724-9546,エンジニア
C10026,前田 鉄二,まえだ てつじ,[email protected],男,1981/01/06,既婚,富山県,000-4564-5797,事務
C10027,安倍 美紀,あべ みき,[email protected],女,1941/02/17,既婚,東京都,000-5651-8006,サービス業
C10028,松山 進,まつやま すすむ,[email protected],男,1981/02/19,既婚,大分県,000-8141-9928,技能工
C10029,橘 佳乃,たちばな よしの,[email protected],女,1941/02/24,既婚,静岡県,000-9000-1834,営業
C10030,関口 美帆,せきぐち みほ,[email protected],女,1981/05/06,既婚,兵庫県,000-8345-2548,自営業

※上記は、ジェネレータツール：なんちゃって個人情報を使用して生成・加工した、架空のデータ

● Blobへのアップロード

Azureコンソールを用いてコンテナにcustomer.csvファイルをアップロード

● 外部表の定義

以下のパッケージを用いて外部表を定義します。

DBMS_CLOUD.CREATE_EXTERNAL_TABLE

SQL> BEGIN
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
    table_name =>'CUSTOMER_EXT',
    credential_name =>'AZURE_BLOB_CRED',
    file_uri_list =>'https://blob2adb.blob.core.windows.net/dumpfiles/customer.csv',
    format => json_object('type' value 'csv','ignoremissingcolumns' value 'true', 'dateformat' value 'YYYY/MM/DD', 'skipheaders' value '1'),
    column_list => 'CUSTOMER_ID  VARCHAR2(26),
    NAME VARCHAR2(128),
    FURIGANA VARCHAR2(128),
    EMAIL VARCHAR2(128),
    GENDAR VARCHAR2(26),
    BIRTHDAY DATE,
    MARITAL_STATUS VARCHAR2(26),
    PREFECTURE VARCHAR2(26),
    MOBILE_NO VARCHAR2(26),
    OCCUPATION VARCHAR2(26)'
  );
END;
/

● 外部表へのアクセス

SQL> select * from customer_ext where rownum <= 5;
   CUSTOMER_ID     NAME    FURIGANA                          EMAIL    GENDAR     BIRTHDAY    MARITAL_STATUS    PREFECTURE        MOBILE_NO    OCCUPATION
______________ ________ ___________ ______________________________ _________ ____________ _________________ _____________ ________________ _____________
C10001         百瀬 丈史    ももせ たけし     momose_takeshi@example.com     男         25-SEP-38    既婚                石川県           000-4101-2921    営業
C10002         津田 まみ    つだ まみ       tsuda_mami@example.com         女         27-SEP-38    既婚                岡山県           000-2148-4441    建築
C10003         小寺 優     こでら ゆう      kodera_yuu@example.com         女         08-OCT-38    既婚                京都府           000-5128-4436    事務
C10004         横川 遥     よこかわ はるか    yokokawa_haruka@example.com    女         15-NOV-38    既婚                東京都           000-8588-7054    技能工
C10005         荻原 陽子    はぎわら ようこ    hagiwara_youko@example.com     女         18-DEC-38    既婚                神奈川県          000-4449-6819    自営業

■ 参考