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

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

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

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

#■ システム構成イメージ

ADB on OCI -- Azure Blob Storage

- OCI上のADBとAzure Blobとをインターネット経由で接続
- 本番環境などで利用する場合は、[OCI/Azure間を直接接続](https://docs.oracle.com/ja/solutions/learn-azure-oci-interconnect/index.html#GUID-FBE38C70-A4CF-40C5-A37A-121241D21199)を経由する

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/543e7ff6-8c0a-2545-42ca-d250a85c1774.png)


#■ 事前準備

##● Azureのセットアップ

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

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

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

基本
・リソースグループ作成 
・ストレージアカウント名の作成 
・冗長性：ローカル冗長ストレージを選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/e3087e14-45b7-c4b1-05b4-17d8c4f2c164.png)

※基本以外はデフォルト

###- コンテナの作成

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/34500bfc-bf22-e1de-39cc-c59b9641268a.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/8db9e02c-92e7-acfc-855c-6ec57306dddb.png)

##● ADBのセットアップ

[こちらのサイト](https://community.oracle.com/tech/welcome/discussion/4474286)を参考にADBをセットアップ

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

Database Actionsを開く
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/b691a586-6ce5-3a25-5643-a8d8ee0faf66.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/3daa844d-64ae-efa9-5088-6d58c41c7a64.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/2ee7a9ea-7e1d-9fbb-d817-3e4f587e9ed6.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/8ab46e51-ab9a-67c6-b3b0-fb8f352e9b88.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/1d50583d-d7cc-ce0a-bf3a-dea4242ca5e3.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/c012723e-0ac8-7bae-9fb8-b7e2b9a64af2.png)

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/67074b96-123a-9906-fccd-5427b2b9b571.png)

blob2adbにアプロードしたファイルが表示されていることを確認
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/bb8c61da-59f5-fba5-8625-93891bff75c3.png)

ロードするファイルをドラッグアンドドロップで右ペインにコピーの上、実行ボタンをクリックする
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/7e059d39-cb58-d3b1-8f6b-ca50551833ec.png)

データロードが成功したことを確認
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/3b0435bb-654a-9e2f-e04e-d556a0e96280.png)

ロードされたデータをDevelopment/SQLを用いて確認
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/f2bd0fa0-3009-18cd-88ee-31aa71d31594.png)

select
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/60a58fa3-f5fc-796f-1703-eac674af51b1.png)

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

- JSONファイルの生成

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

```bash
oci db autonomous-database list|jq -c
```
実際に生成されたJSONデータのイメージ
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/1d30326a-c5d5-ec6c-4d49-c426446ca120.png)

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

- blob2adbにアップロードしたJSONファイルをDatabase Actionsを用いてADBにロード
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/e2e57037-d35c-0dce-b0bd-79230d69a609.png)

- ロードしたデータの確認
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/3f137001-08ad-c80f-4dbc-0c4c2f7e1ed5.png)

- ロードにあわせて自動生成されるView（JSON→二次元表）の確認
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/d389e8d2-bc4b-eed4-1b1b-ccbadcc30e55.png)

- JSONデータから二次元表のViewを生成するDDLを確認
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/132abfdd-5f87-921f-ad09-d23090e5ac2e.png)

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

 [はじめてのAutonomous Databaseへのデータロード（Object Storage経由の場合）](https://qiita.com/mikika/items/5323651cb5826a5b495e#%E5%A4%96%E9%83%A8%E8%A1%A8%E3%81%A8%E3%81%AF-%E5%A4%96%E9%83%A8%E8%A1%A8%E3%82%92%E4%BD%BF%E3%81%A3%E3%81%9F%E3%83%87%E3%83%BC%E3%82%BF%E3%83%AD%E3%83%BC%E3%83%89%E3%81%A8%E3%81%AF)の記事の内容を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を使うよりもメリットの多い、外部表を使ったデータロードをこれまでも好んで使ってきました。）

##● 取り扱うデータ

```text: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,自営業
```

※上記は、ジェネレータツール：[なんちゃって個人情報](https://qiita.com/mikika/items/5323651cb5826a5b495e)を使用して生成・加工した、架空のデータ

##● Blobへのアップロード

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/38031/72a679bc-927a-a0bc-0bec-7c22c9693c97.png)


##● 外部表の定義

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

`DBMS_CLOUD.CREATE_EXTERNAL_TABLE`

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

#■ 参考

- ADB Documentation
 - [ADBでの外部表の利用](https://docs.oracle.com/cd/E83857_01/paas/autonomous-database/adbsa/query-external-data.html#GUID-72DC8BB6-5245-4262-A100-C35A2E553F70) 
 - [外部パーティション化されたデータの問合せ](https://docs.oracle.com/cd/E83857_01/paas/autonomous-database/adbsa/query-external-partition.html#GUID-1399BCF7-05E6-49FE-8710-7571DDF49E39)
- Azure Blobの準備
 - [Azure Blob Storage の概要](https://docs.microsoft.com/ja-jp/azure/storage/blobs/storage-blobs-introduction) 
 - [Azureストレージ アカウントを作成する](https://docs.microsoft.com/ja-jp/azure/storage/common/storage-account-create?toc=%2Fazure%2Fstorage%2Fblobs%2Ftoc.json&tabs=azure-portal)
 - [Azure Blob Storageの権限設定](https://qiita.com/shingo_kawahara/items/097cdc7b0e2975362fce)
- [はじめてのAutonomous Databaseへのデータロード（Object Storage経由の場合）](https://qiita.com/mikika/items/5323651cb5826a5b495e)
- [Autonomous Database から AWS S3 のデータは見えますか？](https://qiita.com/mikika/items/22caa656a83b2b093c3e)
- [[Autonomous DB]Autonomous Database でのハイブリッドパーティション表](https://qiita.com/kenwatan/items/aa3e7588e62062fffcfe)