Db2 Warehouse as a Service(旧名: Db2 Warehouse on Cloud)では、S3からのCSVデータロード方法は4つあります。
-
外部表によるデータのロード(INSERT INTO xxx SELECT * FROM EXTERNAL...)
- Db2 CLIを使用
- 大量データの場合は高速
- TIMESTAMP列がある場合は一旦VARCHAR列にする必要がある
- この記事で説明します
-
LOADコマンド
- Db2 CLIを使用
- TIMESTAMP列もそのままLOAD可能
- こちらを参照: Db2 Warehouse as a Service: LOADコマンドによるS3からのCSVファイルのロード
-
Web GUIからのロード
- 上記1、2の方法が両方可能
- 外部表によるデータのロードの場合、TIMESTAMP列はエラーになる
- こちらを参照: Db2 Warehouse as a Service (Db2 Warehouse on Cloud): Webコンソールを使ったS3からのCSVファイルのロード
-
REST APIを使用したロード
- 以下を参考にしてください。ただしS3からのロードは
load_sourceはS3に指定し、server_sourceの代わりにcloud_sourceをパラメータに指定し、アクセス情報をセットする必要があります。API詳細
- 以下を参考にしてください。ただしS3からのロードは
当記事では1の「外部表によるデータのロード(INSERT INTO xxx SELECT * FROM EXTERNAL...) 」について説明します。
1. はじめに
外部表によるデータのロードは、ロード先の表にTIMESTAMP列がある場合はエラーになってしまうことがわかっています。当記事ではTIMESTAMP列がある場合は小技(?)でINSERTする方法を紹介します。
CSVファイルの最初の1列に列名のヘッダーをつけることはできません。ヘッダーなしでファイルを作成してください。
2. ロード先の表にTIMESTAMP列が「ない」場合
以下のテーブル定義のテーブルにロードするとします:
"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):
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
この場合のコマンドサンプルは以下になります：
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で別フォルダを指定するとよいです。 -
ここでは指定していませんが、 エラーがあってもロードを止めずに続けて欲しい場合は、
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
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列が「ある」場合
以下のテーブル定義のテーブルにロードするとします:
"COL1" INTEGER ,
"COL2" TIMESTAMP ,
"TEXT" VARCHAR(30 OCTETS)
以下のCSVファイルをロードするとします(文字コードはUTF-8):
371,"2024-09-05-09.35.17.753000","コメント"
372,"2024-09-05-10.35.17.753000","コメント"
この場合のコマンドサンプルは以下になります：
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
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などは使えないものあり)
以上です。