[OCI]Autonomous Databaseでオブジェクト・ストレージ上のファイルを外部表として登録し、外部表をもとにマテリアライズド・ビューを作ってあれこれしてみた

# はじめに
Autonomous Databaseは直接オブジェクト・ストレージ内のファイルにアクセスしてファイルをロードしたり、オブジェクト・ストレージ内のファイルを外部表として定義して直接参照することができます。

今回は、ユーザがデータにアクセスする時間帯に、オブジェクト・ストレージ内にあるCSVファイルをもとにアプリケーションで使用する表（在庫データ）の洗い替え（総入れ替え）を行うケースを想定します。

一般的に既存表へのオブジェクト・ストレージからのデータロードには **DBMS_CLOUD.COPY_DATA** プロシージャを使用しますが、 **DBMS_CLOUD.COPY_DATA** は既存表へのデータの追加を行うものです。
そのため、今回想定しているようなデータの洗い替えの場合は、あらかじめ表内の既存データを削除する必要があります。

また、**DBMS_CLOUD.COPY_DATA**は、トランザクションの先頭で実行する必要があるため、表をTRUNCATE、もしくは表の全データをDELETE後にCOMMITしてからでないと実行できません。

以上のことから、今回想定しているケースでは、**DBMS_CLOUD.COPY_DATA**を使用すると表にデータが存在しないタイミングが発生し、そのタイミングでアプリケーションからの表アクセスがあると、運用上の問題となることがわかります。

このような問題を回避する策として、外部表とマテリアライズド・ビューを組み合わせた方法を考えてみました。

# 作業の流れ
0. 事前準備
1. オブジェクト・ストレージバケットの作成
2. オブジェクト・ストレージ・バケットへのCSVファイルのアップロード
3. 資格証明（クレデンシャル）の作成
4. オブジェクト・ストレージ・バケット内のCSVファイルを使用した外部表の作成
5. 外部表をもとにしたマテリアライズド・ビューの作成
6. オブジェクト・ストレージ・バケットへの新しいCSVファイルのアップロード
7. 自動リフレッシュ後のマテリアライズド・ビューの確認

# 0. 事前準備
OCIコンソールからオブジェクト・ストレージへのアクセスに使用するクラウドユーザの認証トークンを作成します。

*「コンソールメニュー」→「アイデンティティ」→「ユーザー」→「ユーザーの詳細」*
![スクリーンショット 2020-11-05 18.35.21.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/93be3af5-6780-7c2c-06e6-d8e910e42076.png)
![スクリーンショット 2020-11-05 18.56.05.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/587e682f-4b9e-77cd-b31d-a61a9f39a728.png)
「リソース」欄の「認証トークン」をクリックします。
![スクリーンショット 2020-11-05 18.37.00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/f7274eb0-ea22-4a2d-5a84-7eeabc3445e3.png)
「トークンの生成」ボタンをクリックします。
![スクリーンショット 2020-11-05 18.37.25.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/5a3e535c-e51b-4b4f-22a9-3569b5975996.png)
「説明」を入力し、「トークンの生成」をクリックします。
![スクリーンショット 2020-11-05 18.38.57.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/4ece2ca5-759d-7830-092e-cf324dfe53bd.png)
生成されたトークンをコピーしておきます。
**トークンは一度しか表示されないので注意してください。**
![スクリーンショット 2020-11-05 18.39.39.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/e49b9fe7-31c1-6af6-2dde-d5aa34d948ed.png)

# 1. オブジェクト・ストレージ・バケットの作成
CSVファイルを格納するためのオブジェクト・ストレージ・バケット **ATP_Bucket** を作成します。

*「コンソールメニュー」→「オブジェクト・ストレージ」→「オブジェクト・ストレージ」*

「バケットの作成」をクリックします。
![スクリーンショット 2020-11-05 18.42.55.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/1e693f7d-fcbd-d798-9c05-3b0531096b4e.png)
「バケット名」に **ATP_Bucket** と入力し、その他の項目はデフォルトのまま、「バケットの作成」をクリックします。
![スクリーンショット 2020-11-05 18.44.07.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/1787818b-998d-0ff8-c0de-0026226fd1a7.png)
作成したバケットの名前をクリックし、ネームスペースを確認してコピーしておきます。
![スクリーンショット 2020-11-05 18.46.45.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/6c7c7445-9b86-5511-3077-0fd28444a0b5.png)

# 2. オブジェクト・ストレージ・バケットへのCSVファイルのアップロード
こちらの内容のCSVファイル **zaiko.csv** を1.で作成したオブジェクト・ストレージ・バケット **ATP_Bucket** にアップロードします。

```zaiko.csv
1,商品1,0
2,商品2,0
3,商品3,0
4,商品4,0
5,商品5,0
6,商品6,0
7,商品7,0
8,商品8,0
9,商品9,0
10,商品10,0
```
*「コンソールメニュー」→「オブジェクト・ストレージ」→「オブジェクト・ストレージ」→「バケットの詳細」*

オブジェクト欄の「アップロード」をクリックします。
![スクリーンショット 2020-11-05 18.48.11.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/15f5133d-8ca2-dda5-58a5-288eda845295.png)
「ファイルを選択」をクリックして **zaiko.csv** を選択し、「アップロード」をクリックします。
![スクリーンショット 2020-11-05 18.50.41.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/50c9c5e6-3e59-4761-8f17-7b0da43e51fa.png)

# 3. 資格証明（クレデンシャル）の作成
SQL*PlusやSQL Developerなどから、Autonomous Databaseにアクセスします。

Autonomous Database内からオブジェクト・ストレージにアクセスするための資格証明（クレデンシャル） **MY_CRED** を作成します。

```
SQL> BEGIN
  2    DBMS_CLOUD.CREATE_CREDENTIAL(
  3      credential_name => 'MY_CRED',  -- クレデンシャルの名前(任意の文字列)
  4      username => 'oracleidentitycloudservice/XXX@XXXXX.com', -- OCIユーザー名
  5      password => 'ABCDEFGHIJKLMN#!'    -- 生成した認証トークン
  6    );
  7  END;
  8  /

PL/SQLプロシージャが正常に完了しました。
```
# 4. オブジェクト・ストレージ・バケット内のCSVファイルを使用した外部表の作成
準備が整ったので、オブジェクト・ストレージ・バケット内のCSVファイルを使用して外部表 **zaiko_ext** を作成します。

「file_uri_list」には、CSVファイル **zaiko.csv** のエンドポイントを指定します。
東京リージョン（ap-tokyo-1）の場合のエンドポイントは以下のようなフォーマットです。

https\://objectstorage.ap-tokyo-1.oraclecloud.com/n/ ***<ネームスペース>*** /b/ ***<バケット名>*** /o/ ***<オブジェクト名（ファイル名）>*** 

```
SQL> BEGIN
  2    DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
  3      table_name =>'ZAIKO_EXT', -- 作成する外部表の名前
  4      credential_name =>'MY_CRED', -- 認証に使用するクレデンシャルの名前
  5      file_uri_list =>'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/xxxxxxxxxxx/b/ATP_Bucket/o/zaiko_tbl.csv', -- CSVファイルのエンドポイント
  6      format => json_object('type' value 'csv'), -- ファイルタイプの設定
  7      column_list => 'prod_id NUMBER, prod_name VARCHAR2(20), zaiko NUMBER' -- 列名と型の宣言
  8    );
  9  END;
 10  /

PL/SQLプロシージャが正常に完了しました。
```
作成した外部表 **zaiko_ext** にアクセスしてみます。

```
SQL> set pagesize 200
SQL> SELECT * FROM zaiko_ext;

   PROD_ID PROD_NAME		     ZAIKO
---------- -------------------- ----------
         1 商品1                         0
         2 商品2                         0
         3 商品3                         0
         4 商品4                         0
         5 商品5                         0
         6 商品6                         0
         7 商品7                         0
         8 商品8                         0
         9 商品9                         0
        10 商品10                        0

10行が選択されました。
```
オブジェクト・ストレージにある **zaiko.csv** の内容が、外部表 **zaiko_ext** の内容として表示されることが確認できました。

# 5. 外部表をもとにしたマテリアライズド・ビューの作成
このあとの作業のために、セッションのタイムゾーンの設定と日付表示のフォーマットを変更しておきます。

```
SQL> ALTER SESSION SET TIME_ZONE = 'Asia/Tokyo';

セッションが変更されました。

SQL> ALTER SESSION SET nls_date_format  = 'YYYY-MM-DD HH24:MI:SS';

セッションが変更されました。

SQL> SELECT current_date FROM dual;

CURRENT_DATE
-------------------
2020-11-05 15:08:57
```

アプリケーションからアクセスするマテリアライズド・ビュー **zaiko_mv** を外部表 **zaiko_ext** を元表として作成します。
今回は、作成後10分おきに自動的に完全リフレッシュをする設定としています。

```
SQL> CREATE MATERIALIZED VIEW zaiko_mv
  2  REFRESH COMPLETE -- 完全リフレッシュを実行
  3  START WITH CURRENT_DATE -- 最初のリフレッシュ時間
  4  NEXT CURRENT_DATE + 1/24/60*10 -- 以降10分おきに自動的にリフレッシュ
  5  AS
  6  SELECT
  7  prod_id,
  8  prod_name,
  9  zaiko,
 10  TO_CHAR(sysdate + 9/24,'YYYY-MM-DD HH24:Mi:SS') version --更新時間をversion列にセット
 11  FROM zaiko_ext;

マテリアライズド・ビューが作成されました。
```
作成したマテリアライズド・ビュー **zaiko_mv** にアクセスしてみます

```
SQL> SELECT current_date FROM dual;

CURRENT_DATE
-------------------
2020-11-05 15:10:10

SQL> SELECT * FROM zaiko_mv;

   PROD_ID PROD_NAME		     ZAIKO     VERSION
---------- -------------------- ---------- -------------------
         1 商品1                         0  2020-11-05 15:10:10
         2 商品2                         0  2020-11-05 15:10:10
         3 商品3                         0  2020-11-05 15:10:10
         4 商品4                         0  2020-11-05 15:10:10
         5 商品5                         0  2020-11-05 15:10:10
         6 商品6                         0  2020-11-05 15:10:10
         7 商品7                         0  2020-11-05 15:10:10
         8 商品8                         0  2020-11-05 15:10:10
         9 商品9                         0  2020-11-05 15:10:10
        10 商品10                        0  2020-11-05 15:10:10

10行が選択されました。
```
オブジェクト・ストレージにある **zaiko.csv** の内容が、マテリアライズド・ビュー **zaiko_mv** の内容として表示されました。

# 6. オブジェクト・ストレージ・バケットへの新しいCSVファイルのアップロード
先ほどと同じファイル名（**zaiko.csv**）で内容の異なるCSVファイルをオブジェクト・ストレージにアップロードします。

```zaiko.csv
1,商品1,76
2,商品2,96
3,商品3,64
4,商品4,9
5,商品5,18
6,商品6,36
7,商品7,4
8,商品8,87
9,商品9,60
10,商品10,42
```
外部表 **zaiko_ext** の内容を確認します。

```
SQL> SELECT current_date FROM dual;

CURRENT_DATE
-------------------
2020-11-05 15:13:26

SQL> SELECT * FROM zaiko_ext;

   PROD_ID PROD_NAME		     ZAIKO
---------- -------------------- ----------
         1 商品1                        76
         2 商品2                        96
         3 商品3                        64
         4 商品4                         9
         5 商品5                        18
         6 商品6                        36
         7 商品7                         4
         8 商品8                        87
         9 商品9                        60
        10 商品10                       42

10行が選択されました。
```
新しくアップロードしたCSVファイル **zaiko.csv** の内容が反映されていることが確認できました。

この状態で、マテリアライズド・ビュー **zaiko_mv** の内容を確認してみます。

```
SQL> SELECT * FROM zaiko_mv;

   PROD_ID PROD_NAME		     ZAIKO VERSION
---------- -------------------- ---------- -------------------
         1 商品1                         0 2020-11-05 15:10:10
         2 商品2                         0 2020-11-05 15:10:10
         3 商品3                         0 2020-11-05 15:10:10
         4 商品4                         0 2020-11-05 15:10:10
         5 商品5                         0 2020-11-05 15:10:10
         6 商品6                         0 2020-11-05 15:10:10
         7 商品7                         0 2020-11-05 15:10:10
         8 商品8                         0 2020-11-05 15:10:10
         9 商品9                         0 2020-11-05 15:10:10
        10 商品10                        0 2020-11-05 15:10:10

10行が選択されました。

SQL> SELECT TO_CHAR(current_date,'YYYY-MM-DD HH24:MI:SS') FROM dual;

TO_CHAR(CURRENT_DAT
-------------------
2020-11-05 15:13:41
```
この時点では、まだ初回の自動リフレッシュが実行されていないため、 マテリアライズド・ビュー **zaiko_mv** の内容は最初にアップロードした時のCSVファイル **zaiko.csv** の内容のままです。


# 7. 自動リフレッシュ後のマテリアライズド・ビューの確認
初回のリフレッシュの時間（**zaiko_mv**の作成から10分後）が過ぎたら、**zaiko_mv** の内容を確認してみます。

```
SQL> SELECT current_date FROM dual;

CURRENT_DATE
-------------------
2020-11-05 15:20:35

SQL> SELECT * FROM zaiko_mv;

   PROD_ID PROD_NAME		     ZAIKO VERSION
---------- -------------------- ---------- -------------------
         1 商品1                        76 2020-11-05 15:20:10
         2 商品2                        96 2020-11-05 15:20:10
         3 商品3                        64 2020-11-05 15:20:10
         4 商品4                         9 2020-11-05 15:20:10
         5 商品5                        18 2020-11-05 15:20:10
         6 商品6                        36 2020-11-05 15:20:10
         7 商品7                         4 2020-11-05 15:20:10
         8 商品8                        87 2020-11-05 15:20:10
         9 商品9                        60 2020-11-05 15:20:10
        10 商品10                       42 2020-11-05 15:20:10

10行が選択されました。
```

マテリアライズド・ビュー**zaiko_mv**の内容が、新しくアップロードした**zaiko.csv**の内容にリフレッシュされていることが確認できました。

# まとめ
オブジェクト・ストレージ上のファイルをもとにした外部表とマテリアライズド・ビューのリフレッシュ機能を使用することで、リフレッシュ完了まではファイル更新前のデータ、リフレッシュ完了後はファイル更新後のデータを途切れることなくアプリケーションに返すことができるようになりました。

めでたし、めでたし。