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という機能もあります](https://qiita.com/ishida330/items/45513e1644d058db04a4)が、Db2経由でアクセスすることで既存のDb2のODBCドライバーを使ったり、Db2上の表との結合なども可能になります。

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/566fa47a-711c-e19b-9097-2d3e3b38fa0e.png)

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

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

#1.ICOSのBucket作成

ICOSの管理画面からバケットを作成します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/c16d7623-5f6a-0e3c-41b3-6584dab8ec33.png)

カスタムで作成します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/1551082d-05ff-112c-2681-ea4b04018555.png)

バケット名をいれて作成します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/8143d444-72b1-7e08-698b-dac58c227a74.png)

作ったバケットにCSVファイルをアップロードします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/49f768ac-fbe2-9f81-9450-5c3687fe8678.png)

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


https://cloud.ibm.com/docs/cloud-object-storage?topic=cloud-object-storage-service-credentials&locale=ja

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/c677d3bb-2f7e-3713-ddf0-b8ff90fdc936.png)

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

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

[CREATE EXTERNAL TABLE ステートメントのマニュアル](https://www.ibm.com/docs/ja/db2woc?topic=statements-create-external-table)に以下のように書いてあります。
注: IBM Cloud Object Storage の場合、HMAC 資格情報を作成するために、新しいサービス資格情報を作成する際、「インラインの構成パラメーターの追加」フィールドに {"HMAC:true} を指定してください。


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/358b7b39-b190-7164-3d67-b0d23dd2bc8c.png)

作成したサービス資格情報をコピーして取っておきます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/bb439a80-0eba-3bb3-8d99-d15b69a06678.png)
access_key_idとsecret_access_keyを外部表作成時に使います。

[CREATE EXTERNAL TABLE ステートメントのマニュアル](https://www.ibm.com/docs/ja/db2woc?topic=statements-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限定のアクセスにするように制限する設定をします。

https://cloud.ibm.com/docs/cloud-object-storage/iam?topic=cloud-object-storage-iam-bucket-permissions

まず管理からアクセス管理（IAM）のメニューを開きます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/7595783a-6736-7c92-111d-06d7acda093b.png)

サービスIDの一覧からから先ほど作ったサービスID探し、クリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/7ef6ed38-199f-9bd4-c012-45c3992f3635.png)

アクセス・ポリシーの中で先ほど作ったライターの役割をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/b2d11994-5f41-1d84-8385-3c453443f870.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/4a14acf8-9ceb-52f7-112d-9ece14e28d75.png)

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

以下のように更新されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/7ca1451d-479a-6473-ef2f-1a67d7fd3661.png)

#4. Db2WHOCでの外部表作成

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

https://www.ibm.com/docs/ja/db2woc?topic=statements-create-external-table

ソース・データ・ファイルが 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文になります。

```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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/d51e4ac3-fd17-04de-9f79-5a2068785536.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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/1d2887e5-52f3-488d-cb7b-4ee7c250ee98.png)