LoginSignup
2
1

More than 3 years have passed since last update.

@kawada2017(Kawa da)

Db2 Warehouse on Cloudで、ICOS上のCSVから外部表を作成する

Last updated at Posted at 2021-04-14

IBM Cloud Object Storage(以下ICOS)上のCSVを置いたまま、Db2 Warehouse on Cloud（以下Db2WHOC）でテーブルとして見せることができる外部表を作成します。

以下のようなメリットが考えられます。

  • 使用頻度が低いデータをICOSなどの安価なストレージに置き、使用頻度の高いデータをRDBに置きながらも、ICOS上のデータも同じSQL文でアクセスすることが可能になります。
  • ICOS上のCSVに対してSQLアクセスを可能にするSQL Queryという機能もありますが、Db2経由でアクセスすることで既存のDb2のODBCドライバーを使ったり、Db2上の表との結合なども可能になります。

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

image.png

  1. Bucket作成
  2. サービス資格情報の作成
  3. サービスIDの定義
  4. 外部表の作成

ちなみにここではICOS上のデータを扱いますが、AWSのS3にあるデータも同様の方法で扱うことができます。

1.ICOSのBucket作成

ICOSの管理画面からバケットを作成します。
image.png

カスタムで作成します。
image.png

バケット名をいれて作成します。
image.png

作ったバケットにCSVファイルをアップロードします。
image.png

CSVファイルは以下のような内容です。列ヘッダーがあり、カンマ区切りです。

20210101sensor.csv
DATE,M_CD,POWER,TEMP,ERR_CD
2021-01-01,104,1003,244,0
2021-01-01,111,1071,254,0
2021-01-01,127,1050,244,0
2021-01-01,143,1088,244,0

2.サービス資格情報作成

以下のマニュアルを参考に、サービス資格情報を作ります。

image.png

以下を設定します。
Db2 WHOCの外部表アクセスではHMAC認証を行うので、HMACを有効にする必要があります。

名前：任意の名前
役割：ライター
サービスID:新規サービスIDの選択
新規サービスID名：任意の名前
HMAC資格情報を含める：オン

CREATE EXTERNAL TABLE ステートメントのマニュアルに以下のように書いてあります。
注: IBM Cloud Object Storage の場合、HMAC 資格情報を作成するために、新しいサービス資格情報を作成する際、「インラインの構成パラメーターの追加」フィールドに {"HMAC:true} を指定してください。

image.png

作成したサービス資格情報をコピーして取っておきます。
image.png
access_key_idとsecret_access_keyを外部表作成時に使います。

CREATE EXTERNAL TABLE ステートメントのマニュアルに以下のように書いてあります。
構文:
S3 (endpoint, authKey1, authKey2, bucket)
authKey1
IBM Cloud Object Storage の場合、HMAC 資格情報からのアクセス・キー ID です。
authKey2
IBM Cloud Object Storage の場合、HMAC 資格情報からのシークレット・アクセス・キーです。

3. サービスIDの定義

以下のマニュアルを参考にして、サービス資格情報作成時に作ったサービスIDをこのBucket限定のアクセスにするように制限する設定をします。

まず管理からアクセス管理（IAM）のメニューを開きます。
image.png

サービスIDの一覧からから先ほど作ったサービスID探し、クリックします。
image.png

アクセス・ポリシーの中で先ほど作ったライターの役割をクリックします。
image.png

以下の設定を加えます。
リソースタイプにbucket
リソースIDにbucket名（例：sensorcsv）
を指定します。これでこのサービスIDはこのbucktのみのアクセスしかできなくなります。
CSVファイルを読めるようにObject Readerのサービス・アクセス権限にもチェックを入れます。

image.png

今回CSVデータを読むことだけを想定していますが、エラーが発生した場合のログを書き込む権限が必要なのでライター権限をつけています。（LOGDIRをきちんと分けることもできます）
CREATE EXTERNAL TABLE ステートメントのマニュアルに以下のように書いてあります。
必要な権限:
外部表が名前付き外部表の場合、所有者にそのファイルに対する読み取り権限と LOGDIR ディレクトリーに対する 書き込み権限がなければなりません。
外部表が一時外部表の場合、ステートメントの許可 ID にそのファイルに対する読み取り権限と LOGDIR ディレクトリーに対する 書き込み権限がなければなりません。

以下のように更新されます。
image.png

4. Db2WHOCでの外部表作成

以下のマニュアルを参考にして、Db2WHOCで外部表を作成します。

ソース・データ・ファイルが S3 互換オブジェクト・ストア内にあることを指定します。ファイル名を指定するには、DATAOBJECT オプションを使用します。

構文:
S3 (endpoint, authKey1, authKey2, bucket)

endpoint
S3 互換 Web サービスの URL を指定する文字ストリングです。
ICOSのエンドポイントは以下に書いてあります。

https://cloud.ibm.com/docs/cloud-object-storage?topic=cloud-object-storage-endpoints&locale=ja
例ではプライベートのAP日本の地域エンドポイントを使いました。

authKey1：
ユーザーおよびすべてのユーザー・アクションの検証に使用されるアクセス・キーの S3 アクセス・キー ID を指定する文字ストリング。IBM Cloud Object Storage の場合、HMAC 資格情報からのアクセス・キー ID です。
authKey2：
ユーザーおよびすべてのユーザー・アクションの検証に使用されるアクセス・キーの S3 秘密鍵を指定する文字ストリング。IBM Cloud Object Storage の場合、HMAC 資格情報からのシークレット・アクセス・キーです。
bucket
ファイルが入っている S3 バケットの名前。 （例:sensorcsv)

また、ここでは
DELIMITER ','でカンマ区切りであることを指定し、
SKIPROWS 1でヘッダー行を無視することを指定しています。

以下のような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
  )

以下のようにテーブルのようにSELECTが可能になります。
image.png

5. 問題判別のヒント

私は以下のようなエラーに遭遇しましたので参考までに書いておきます。

  • Endpoint URLが間違っているとき
    • The external table operation failed due to a problem with the corresponding data file or diagnostic files. File name: "20210101sensor.csv". Reason code: "1".. SQLCODE=-20569, SQLSTATE=428IB, DRIVER=4.26.14
  • スキーマが違うとき、DELIMITERが違うとき。ファイルはbucket内にできます。
    • The statement failed because the number of rejected rows exceeds the limit set in the MaxErrors option value. Log file name: "BLUDB.HKWD.SENSOR_20210101.20210101sensor.csv.00064525.0000003.log".. SQLCODE=-20570, SQLSTATE=22551, DRIVER=4.26
  • 権限が足りないとき
    • An attempt to access media "" is denied.. SQLCODE=-2061, SQLSTATE= , DRIVER=4.26.14

エラーが起きた場合にはLOGDIRを指定していない場合にはデータのあるbucketにlogファイルが出力されます。このlogファイルが問題判別の役に立ちます。
image.png

2
1
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
2
1