Db2 Warehouse on Cloudで、外部表をつかったUNION ALL VIEWでアーカイブデータに透過的にアクセスする

IBM Cloud Object Storage(以下ICOS)上のCSVを置いたまま、Db2 Warehouse on Cloud（以下Db2WHOC）でテーブルとして見せることができる[外部表を作成する](https://qiita.com/kawada2017/items/8a0bbddd10aba11efec3)ことができます。
さらにこの外部表をUNION ALL VIEWで統合することでアーカイブデータへのアクセスを容易にすることができます。

これがどんな時に役に立つかをお話しします。
例えば、01/01から01/03の3日間のデータがあるとします。
多くのアクセスは最新データの01/03に対して行われて、01/01や01/02にはあまり行われないということを想定しています。

このような場合、01/01や01/02はアーカイブデータとしてCSVなどの形式で、ICOSの安価なストレージに保存して、最新の最新データの01/03のデータだけをRDBに保存して高速に扱うということを行うことが多いと思います。
そして、アーカイブデータが使いたい時には、DBの管理者にRDBにロードしてもらってから利用します。

しかし、これではいちいちロードの依頼が必要で、すぐにデータを見ることができません。

この問題に対応するために、外部表とUNION ALL VIEWでアーカイブデータに透過的にアクセスする方法をアイディアをご紹介します。


全体のイメージは以下になります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/b9790638-c537-cfe9-fa4c-0df74613c0de.png)

2021-01-01から2021-01-03の3日間のデータをICOS上のバケットにCSVで保存します。
2021-01-01と2021-01-02のデータはICOS上のCSVを外部表として作っています。
2021-01-03のデータは実表にLOADしています。
そして、2021-01-01と2021-01-02外部表と2021-01-03の実表をUNION ALL VIEWに統合しています。

このようにすることで、UNION ALL VIEWをみればすべてのデータを透過的に参照できることになります。
以下のようなメリットがあります。

* あまり使わないアーカイブデータをICOSのような安価なストレージに置いたまま利用できる
* よく使うデータは実表で高速アクセスできる
* ユーザーからは一つの表のイメージで使える
* 日付を指定することでパーティションアクセスが可能なため無駄なアクセスはしない


#1. 外部表の作成

まず、ICOSにアーカイブデータのCSVをおいて外部表を作成します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/584a0f42-a03e-acae-6991-91b524ad60a7.png)
ICOS上のCSVから外部表を作成する方法は以下で紹介しています。

https://qiita.com/kawada2017/items/8a0bbddd10aba11efec3

まず、以下のようにICOSのBucketにCSVを置きました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/620498b9-ceb3-4c33-393d-b2a64c00283b.png)

次のSQL分で外部表を作成します

```sql:外部表作成
CREATE EXTERNAL TABLE SENSOR_20210101("DATE" DATE,M_CD SMALLINT,POWER SMALLINT,TEMP SMALLINT,ERR_CD SMALLINT) using 
  (dataobject '20210101sensor.csv' 
   s3('s3.private.jp-tok.cloud-object-storage.appdomain.cloud', 
    '86xxxxxxxxxxxxxxxxxxxxxxx',
    'bxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', 
    'sensorcsv'
   ) DELIMITER ',' SKIPROWS 1
  )

CREATE EXTERNAL TABLE SENSOR_20210102("DATE" DATE,M_CD SMALLINT,POWER SMALLINT,TEMP SMALLINT,ERR_CD SMALLINT) using 
  (dataobject '20210102sensor.csv' 
   s3('s3.private.jp-tok.cloud-object-storage.appdomain.cloud', 
    '86xxxxxxxxxxxxxxxxxxxxxxx',
    'bxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', 
    'sensorcsv'
   ) DELIMITER ',' SKIPROWS 1
  )


```

#2.実表にデータをLOADする
最新のデータをICOSから実表にLOADします。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/381faa52-108a-5fb9-33a5-93144d05339b.png)

CREATE TABLEで実表をつくって、INSERTやLOADでロードします。
S3句の書き方は外部表を作成するときのものと同じです。

```sql:ICOSから実表へのLOAD
CREATE TABLE SENSOR_LATEST("DATE" DATE,M_CD SMALLINT,POWER SMALLINT,TEMP SMALLINT,ERR_CD SMALLINT)
;
INSERT INTO SENSOR_LATEST SELECT * FROM EXTERNAL '20210103sensor.csv' 
USING ( s3('s3.private.jp-tok.cloud-object-storage.appdomain.cloud', 
    '86xxxxxxxxxxxxxxxxxxxxxxx',
    'bxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', 
    'sensorcsv'
   ) DELIMITER ',' SKIPROWS 1
  )
;
```

#3.UNION ALL VIEWを作る
外部表と実表からUNION ALL VIEWを作ります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/01072104-5ead-9541-8d22-0ecb4312bf42.png)

以下のように外部表と実表をUNION ALLでつなぎます。
ポイントは各外部表、実表に対して含まれている日付をWHERE "DATE"='2021-01-XX'のように明示します。このWHERE句がなくてもUNION ALL VIEWはつくれますが、このWHERE句をつけることで日付指定をした場合のSQLで無駄なアクセスを省けます。

```sql:UNION_ALL_VIEWを作る
CREATE OR REPLACE VIEW SENSOR_ALL AS
SELECT * FROM SENSOR_20210101 WHERE "DATE"='2021-01-01'
UNION ALL
SELECT * FROM SENSOR_20210102 WHERE "DATE"='2021-01-02'
UNION ALL
SELECT * FROM SENSOR_LATEST WHERE "DATE"='2021-01-03'
```

#4.アクセスパスの確認
###4.1.日付を指定しない。全データアクセス
WHERE条件に日付が入っていない場合すべてのデータにアクセスします。

```sql:全データアクセス
select * from SENSOR_ALL
where M_CD=104
```
全ての表から、すべての日のM_CD=104 のデータを抽出しました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/6dd67e21-fa66-ae48-f3b4-c37260788977.png)

アクセス計画をみるとすべてのデータにアクセスしていることがわかります。データ量が多い場合、これはとても重いクエリーになります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/e371deb5-1c16-7609-d915-7d94421a6bc2.png)

###4.2.日付を指定する外部表アクセス
WHERE条件に日付が入っていますのでこの外部表のデータだけを見ます。

```sql:日付を指定する外部表アクセス
select * from SENSOR_ALL
where M_CD=104 AND "DATE"='2021-01-01'
```
1/1のCSVから、M_CD=104 のデータを抽出しました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/9c272352-6b9a-c555-405d-75ed0ff2739a.png)

アクセス計画をみると20210101の外部表にのみアクセスしています。他の表へのアクセスは省略できました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/64deef05-fa85-6566-473c-8bd08b5e050f.png)

###4.3.日付を指定する実表アクセス
WHERE条件に日付が入っています。そして、この日付のデータは実表にはいっていますので、実表のデータだけを見ます。

```sql:日付を指定する実表アクセス
select * from SENSOR_ALL
where M_CD=104 AND "DATE"='2021-01-03'
```
1/3の実表から、M_CD=104 のデータを抽出しました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/85459897-e2eb-a4e1-d094-2b595ec0c2aa.png)

アクセス計画をみるとLATESTの実表にのみアクセスしています。他の表へのアクセスは省略できました。これがもっとも効率的なアクセスです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/27b86e99-1e22-2fb6-79a2-3e4f5ff2192d.png)


###4.4.複数の日付を指定する外部表と実表アクセス
WHERE条件に1/2と1/3の日付が入っています。そして、この日付のデータは外部表と実表にはいっていますが、指定された外部表と実表だけを見ます。

```sql:複数の日付を指定する外部表と実表アクセス
select * from SENSOR_ALL
where M_CD=104 AND "DATE" between '2021-01-02' and '2021-01-03'
```
1/2の外部表と1/3の実表から、M_CD=104 のデータを抽出しました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/7a64b6b0-f025-853f-213d-2be4b6f86cf7.png)

アクセス計画をみると1/2のCSVとLATESTの実表にのみアクセスしています。1/1の外部表へのアクセスは省略できました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/c9cebfc5-0459-18a9-b54e-7b70401ac751.png)

#5.データの追加
なお、この形式にしておくとデータの追加も容易です。

1/4のデータを追加すると以下のようなイメージになります。アプリケーションからは同じVIEW名でアクセスできるのが便利です。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/2ef55780-e459-597b-fdd4-38cb1283f2e1.png)


```SQL
--アーカイブ外部表作成
CREATE EXTERNAL TABLE SENSOR_20210103("DATE" DATE,M_CD SMALLINT,POWER SMALLINT,TEMP SMALLINT,ERR_CD SMALLINT) using 
  (dataobject '20210103sensor.csv' 
   s3('s3.private.jp-tok.cloud-object-storage.appdomain.cloud', 
    '86xxxxxxxxxxxxxxxxxxxxxxx',
    'bxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', 
    'sensorcsv'
   ) DELIMITER ',' SKIPROWS 1
  )
;

--実表の洗い替え
TRUNCATE TABLE SENSOR_LATEST
;

INSERT INTO SENSOR_LATEST SELECT * FROM EXTERNAL '20210103sensor.csv' 
USING  s3('s3.private.jp-tok.cloud-object-storage.appdomain.cloud', 
    '86xxxxxxxxxxxxxxxxxxxxxxx',
    'bxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', 
    'sensorcsv'
   ) DELIMITER ',' SKIPROWS 1
  )
;

--UNION ALL VIEWの置き換え
CREATE OR REPLACE VIEW SENSOR_ALL AS
SELECT * FROM SENSOR_20210101 WHERE "DATE"='2021-01-01'
UNION ALL
SELECT * FROM SENSOR_20210102 WHERE "DATE"='2021-01-02'
UNION ALL
SELECT * FROM SENSOR_20210103 WHERE "DATE"='2021-01-03'
UNION ALL
SELECT * FROM SENSOR_LATEST WHERE "DATE"='2021-01-04'
;
```

#注意点
非常に便利な外部表を含むUNION ALL VIEWですが、外部表の実体はCSVファイルです。RDBと違ってキャッシュやカラムナーなどのデータアクセスを効率的に行う仕組みを持っていません。パフォーマンスが良くないことは意識して以下のような点に注意して、使ってもらう必要があります。

* 日付を指定しない場合はCSVデータをすべてスキャンしてしまう。基本的に日付を指定するというルールを設けておくのが望ましい
* 外部表はRDBのようにキャッシュやカラムナーなどの機能は使えないので遅い。複雑なアクセスを繰り返す場合は一時表や実表にロードする。