LoginSignup
0
0

More than 1 year has passed since last update.

@TakuyaAma(A Takuya)in日本オラクル株式会社

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

Last updated at Posted at 2021-04-24

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

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

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

■ システム構成イメージ

ADB on OCI -- Azure Blob Storage

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

image.png

■ 事前準備

● Azureのセットアップ

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

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

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

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

※基本以外はデフォルト

- コンテナの作成

image.png

image.png

● ADBのセットアップ

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

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

Database Actionsを開く
image.png

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

image.png

image.png

image.png

image.png

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

image.png

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

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

image.png

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

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

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

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

select
image.png

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

  • JSONファイルの生成

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

oci db autonomous-database list|jq -c

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

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

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

  • ロードしたデータの確認
    image.png

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

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

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

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

● Blobへのアップロード

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

image.png

● 外部表の定義

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

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    自営業

■ 参考

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0