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

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

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

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

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

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

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

#1.ICOSのBucket作成

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

カスタムで作成します。

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

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

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.サービス資格情報作成
以下のマニュアルを参考に、サービス資格情報を作ります。

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

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

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

作成したサービス資格情報をコピーして取っておきます。

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）のメニューを開きます。

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

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

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

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

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

#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が可能になります。

#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ファイルが問題判別の役に立ちます。