LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@spssfun2017(spss fun)

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

Last updated at Posted at 2021-04-15

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でアーカイブデータに透過的にアクセスする方法をアイディアをご紹介します。

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

image.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
ICOS上のCSVから外部表を作成する方法は以下で紹介しています。

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

image.png

次の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

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

以下のように外部表と実表を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 のデータを抽出しました。

image.png

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

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

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

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

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

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

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

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

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

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

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

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 のデータを抽出しました。
image.png

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

5.データの追加

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

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

image.png

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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?