#■ この記事で記載すること
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 <外部表>;
のように、外部表のデータを参照して実表にデータをコピーする、というのが、外部表を使用したデータロードになります。
（テキストファイルからのデータロードといえばSQLLoaderが有名ですが、SQLLoaderを使うよりもメリットの多い、外部表を使ったデータロードをこれまでも好んで使ってきました。）
##● 取り扱うデータ
顧客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ファイルをアップロード
##● 外部表の定義
以下のパッケージを用いて外部表を定義します。
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 自営業
#■ 参考