More than 3 years have passed since last update.

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

Last updated at 2022-02-28Posted at 2020-11-05

はじめに

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

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

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

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

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

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

作業の流れ

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

0. 事前準備

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

「コンソールメニュー」→「アイデンティティ」→「ユーザー」→「ユーザーの詳細」

「リソース」欄の「認証トークン」をクリックします。

「トークンの生成」ボタンをクリックします。

「説明」を入力し、「トークンの生成」をクリックします。

生成されたトークンをコピーしておきます。
トークンは一度しか表示されないので注意してください。

1. オブジェクト・ストレージ・バケットの作成

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

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

「バケットの作成」をクリックします。

「バケット名」に ATP_Bucket と入力し、その他の項目はデフォルトのまま、「バケットの作成」をクリックします。

作成したバケットの名前をクリックし、ネームスペースを確認してコピーしておきます。

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

「コンソールメニュー」→「オブジェクト・ストレージ」→「オブジェクト・ストレージ」→「バケットの詳細」

オブジェクト欄の「アップロード」をクリックします。

「ファイルを選択」をクリックして zaiko.csv を選択し、「アップロード」をクリックします。

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の内容にリフレッシュされていることが確認できました。

まとめ

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

めでたし、めでたし。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up