Db2 Warehouse as a Service: 外部表によるS3からのCSVファイルのロード(INSERT INTO xxx SELECT * FROM EXTERNAL...)

Db2 Warehouse as a Service(旧名: Db2 Warehouse on Cloud)では、S3からのCSVデータロード方法は4つあります。
1. 外部表によるデータのロード(INSERT INTO xxx SELECT * FROM EXTERNAL...)　
    - Db2 CLIを使用
    - 大量データの場合は高速
    - TIMESTAMP列がある場合は一旦VARCHAR列にする必要がある
    - **この記事で説明します**
    - 

1. LOADコマンド
    - Db2 CLIを使用
    - TIMESTAMP列もそのままLOAD可能
    - こちらを参照: [Db2 Warehouse as a Service: LOADコマンドによるS3からのCSVファイルのロード](https://qiita.com/nishikyon/items/22f0463f8eaccc55a4ec)
    - 

1. Web GUIからのロード
    - 上記1、2の方法が両方可能
    - 外部表によるデータのロードの場合、TIMESTAMP列はエラーになる
    - こちらを参照: [Db2 Warehouse as a Service (Db2 Warehouse on Cloud): Webコンソールを使ったS3からのCSVファイルのロード](https://qiita.com/nishikyon/items/65c7f475a0279de217a1)
    - 
1. REST APIを使用したロード 
    - 以下を参考にしてください。ただしS3からのロードは`load_source`は`S3`に指定し、`server_source`の代わりに`cloud_source`をパラメータに指定し、アクセス情報をセットする必要があります。[API詳細](https://cloud.ibm.com/apidocs/db2-warehouse-on-cloud/db2-warehouse-on-cloud-v4#createloadjob)
        - [Db2 Warehouse on CloudのREST APIを使用したデータロード (python & curl)　の4. データロードジョブの作成](https://qiita.com/nishikyon/items/3cf88857245a6d084b63#4-%E3%83%87%E3%83%BC%E3%82%BF%E3%83%AD%E3%83%BC%E3%83%89%E3%82%B8%E3%83%A7%E3%83%96%E3%81%AE%E4%BD%9C%E6%88%90)
        - 


当記事では1の「外部表によるデータのロード(INSERT INTO xxx SELECT * FROM EXTERNAL...)　」について説明します。


# 1. はじめに

外部表によるデータのロードは、ロード先の表にTIMESTAMP列がある場合はエラーになってしまうことがわかっています。当記事ではTIMESTAMP列がある場合は小技(?)でINSERTする方法を紹介します。

CSVファイルの最初の1列に列名のヘッダーをつけることはできません。ヘッダーなしでファイルを作成してください。

# 2. ロード先の表にTIMESTAMP列が「ない」場合
以下のテーブル定義のテーブルにロードするとします:
```sql:TEST_TBL
 "COLUMN_0" DATE , 
 "COLUMN_1" VARCHAR(3 OCTETS) , 
 "COLUMN_2" DECIMAL(4,1) , 
 "COLUMN_3" DECIMAL(4,1) , 
 "COLUMN_4" DECIMAL(3,1) , 
 "COLUMN_5" DECIMAL(4,1) , 
 "COLUMN_6" DECIMAL(3,1) ) 
```


以下のCSVファイルをロードするとします(文字コードはUTF-8):
```py:CSVサンプル - text.csv
2020-01-02,"木",6.2,11.3,1.9,0.0,5.7
2020-01-03,"金",6.1,12.0,1.4,0.0,8.8
2020-01-04,"土",7.2,12.2,3.6,0.0,3.4
```

この場合のコマンドサンプルは以下になります：
```sql
insert into <テーブル名> select * from external '<S3のCSVファイルのS3のパス>' 
using (DELIMITER ',' ENCODING 'UTF8' LOGDIR '<ログを置くS3のパス>' STRING_DELIMITER 'DOUBLE' 
S3 ('<S3エンドポイント>', 
'<アクセスキー>', '<シークレットアクセスキー>', 
'<バケット名>') );
```

- usingの中に入れるオプションは以下のLink先の「option」と同じです(ただし`TIMESTAMP_FORMAT`, `INCLUDEHEADER`などは使えないものあり)
https://www.ibm.com/docs/ja/db2w-as-a-service?topic=statements-create-external-table
    - 何が使えないかの記載はないので、やってみないとわかりません(いまいち)
    - 
- コマンド実行時、S3にログファイルが作成されます。何も指定しないと、CSVファイルと同じフォルダーに作成されます。データと区別するためになるべく`LOGDIR`で別フォルダを指定するとよいです。
    - LOGDIRについて
        - https://www.ibm.com/docs/ja/db2w-as-a-service?topic=statements-create-external-table#d591934e4099
        - 

- ここでは指定していませんが、　エラーがあってもロードを止めずに続けて欲しい場合は、`MaxErrors`の値を設定すると、設定回数分エラーが発生した行は無視してLOAD続行する(エラー行はLogで確認)

**以下の場合のコマンド例**:
- S3エンドポイント: s3.ap-northeast-1.amazonaws.com
- S3バケット名: woc-test-202504
- csvファイル: data/test.csv
- ロード先テーブル: TEST_TBL
- Log file: data/log  
- アクセスキー: xxxxxxxxxxxxxx
- シークレットアクセスキー: yyyyyyyyyyyyyyyyyyyyyyyyyyyy

```sql:コマンド例
insert into TEST_TBL select * from external 'data/test.csv' 
using (DELIMITER ',' ENCODING 'UTF8' LOGDIR 'data/log' 
STRING_DELIMITER 'DOUBLE' 
S3 ('s3.ap-northeast-1.amazonaws.com', 
'xxxxxxxxxxxxxx', 'yyyyyyyyyyyyyyyyyyyyyyyyyyyy', 
'woc-test-202504') );
```

# 3. ロード先の表にTIMESTAMP列が「ある」場合

以下のテーブル定義のテーブルにロードするとします:
```sql:TEST_TIMESTAMP
"COL1" INTEGER , 
"COL2" TIMESTAMP , 
"TEXT" VARCHAR(30 OCTETS)
```


以下のCSVファイルをロードするとします(文字コードはUTF-8):
```py:CSVサンプル - test_timestamp.csv
371,"2024-09-05-09.35.17.753000","コメント"
372,"2024-09-05-10.35.17.753000","コメント"
```

この場合のコマンドサンプルは以下になります：
```sql
insert into <テーブル名> 
select * from external '<S3のCSVファイルのS3のパス>'
(
 COL1 INTEGER , 
 COL2 VARCHAR(26), 
 TEXT VARCHAR(30 OCTETS) 
)
using (DELIMITER ',' ENCODING 'UTF8' LOGDIR '<ログを置くS3のパス>' STRING_DELIMITER 'DOUBLE'
S3 ('<S3エンドポイント>', 
'<アクセスキー>', '<シークレットアクセスキー>', 
'<バケット名>')) );
```

- TIMESTAMPが文字列がNGなので、一旦、**文字列のVARCHAR(26)  に入れてからINSERT**します。
- その他の列はテーブル定義と同じにする


**以下の場合のコマンド例**:
- S3エンドポイント: s3.ap-northeast-1.amazonaws.com
- S3バケット名: woc-test-202504
- csvファイル: data/test_timestamp.csv
- ロード先テーブル: TEST_TIMESTAMP
- Log file: data/log  
- アクセスキー: xxxxxxxxxxxxxx
- シークレットアクセスキー: yyyyyyyyyyyyyyyyyyyyyyyyyyyy

```sql:コマンド例
insert into TEST_TIMESTAMP
select * from external 'data/test_timestamp.csv'
(
 COL1 INTEGER , 
 COL2 VARCHAR(26), 
 TEXT VARCHAR(30 OCTETS) 
)
using (DELIMITER ',' ENCODING 'UTF8' LOGDIR 'data/log' 
STRING_DELIMITER 'DOUBLE'
S3 ('s3.ap-northeast-1.amazonaws.com', 
'xxxxxxxxxxxxxx', 'yyyyyyyyyyyyyyyyyyyyyyyyyyyy', 
'woc-test-202504') );
```

ファイルの内容に応じてusingの中に入れるオプションは以下のLink先の「option」を追加・変更してください(ただし`TIMESTAMP_FORMAT`, `INCLUDEHEADER`などは使えないものあり)
- https://www.ibm.com/docs/ja/db2w-as-a-service?topic=statements-create-external-table

以上です。