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

17249 ワード
ICOS externalTable db2warehouse_on_cloud ICOS テキストリンク

IBM Cloud Object Storage(以下ICOS)上のCSVを置いたまま、Db2 Warehouse on Cloud（以下Db2WHOC）でテーブルとして見せることができる外部表を作成することができます。
さらにこの外部表を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でアーカイブデータに透過的にアクセスする方法をアイディアをご紹介します。

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

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をおいて外部表を作成します。


ICOS上のCSVから外部表を作成する方法は以下で紹介しています。

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

次の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します。

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

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を作ります。

以下のように外部表と実表をUNION ALLでつなぎます。
ポイントは各外部表、実表に対して含まれている日付をWHERE "DATE"='2021-01-XX'のように明示します。このWHERE句がなくてもUNION ALL VIEWはつくれますが、このWHERE句をつけることで日付指定をした場合の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条件に日付が入っていない場合すべてのデータにアクセスします。

全データアクセス
select * from SENSOR_ALL
where M_CD=104

全ての表から、すべての日のM_CD=104 のデータを抽出しました。

アクセス計画をみるとすべてのデータにアクセスしていることがわかります。データ量が多い場合、これはとても重いクエリーになります。

4.2.日付を指定する外部表アクセス

WHERE条件に日付が入っていますのでこの外部表のデータだけを見ます。

日付を指定する外部表アクセス
select * from SENSOR_ALL
where M_CD=104 AND "DATE"='2021-01-01'

1/1のCSVから、M_CD=104 のデータを抽出しました。

アクセス計画をみると20210101の外部表にのみアクセスしています。他の表へのアクセスは省略できました。

4.3.日付を指定する実表アクセス

WHERE条件に日付が入っています。そして、この日付のデータは実表にはいっていますので、実表のデータだけを見ます。

日付を指定する実表アクセス
select * from SENSOR_ALL
where M_CD=104 AND "DATE"='2021-01-03'

1/3の実表から、M_CD=104 のデータを抽出しました。

アクセス計画をみるとLATESTの実表にのみアクセスしています。他の表へのアクセスは省略できました。これがもっとも効率的なアクセスです。

4.4.複数の日付を指定する外部表と実表アクセス

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

複数の日付を指定する外部表と実表アクセス
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 のデータを抽出しました。

アクセス計画をみると1/2のCSVとLATESTの実表にのみアクセスしています。1/1の外部表へのアクセスは省略できました。

5.データの追加

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

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

--アーカイブ外部表作成
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のようにキャッシュやカラムナーなどの機能は使えないので遅い。複雑なアクセスを繰り返す場合は一時表や実表にロードする。