1. はじめに
Db2 Warehoue on Cloud (Db2WoC) はBIレポーティングやデータ分析向けのリレーショナル・データベースで、IBM CloudおよびAWSにおいてフルマネージド・サービスとして提供されています。
https://cloud.ibm.com/catalog/services/db2-warehouse
Db2WoCはクラウド・データベースとしてスケーリング機能を備えており、負荷に応じたCPUコア数増減やデータ量増加に応じたストレージ追加が可能です。Db2WoCのデータベース・エンジンはデータを列単位で保持し（列表、column-organized table)、BLU Accelerationと呼ばれるインメモリ高速化機能（列データを圧縮したままメモリ上で処理等）によってストレージ・コストを抑えながら高速処理を実現します。
Db2WoCはパブリック・クラウドのオブジェクト・ストレージ上(AWS S3, IBM Cloud Object Storage(ICOS))に外部表を作成してデータをロードおよびアンロードすることができます。本記事ではS3上に外部表を作成する方法を紹介します。マニュアルを読んでもわかりにくい次の点を解説することが本記事の目的です。
- S3のエンドポイント、アクセスキー、シークレット・アクセスキーをどこから取得するのか
- 外部表に対するSQLの制約
- 私が経験したエラーと対処方法
目次は次の通りです。
- はじめに
- 手順実行の前提
- 参考資料
- 類似テーマを扱った資料
- 手順概要
- 手順
- エラー例
- S3バケットの設定
2. 手順実行の前提
次の情報と実行環境が必要です。
- Db2WoCのデータベース・ユーザーID、パスワード、作業用スキーマ
- AWSのアカウント
- Db2クライアントが導入された端末
3. 参考資料
- IBMマニュアル: IBM Db2 Warehouse on Cloud, CREATE EXTERNAL TABLE statement
- AWSマニュアル：AWS service endpoints
- Qiita: AWSのアクセスキーを取得する方法
4. 類似テーマを扱った資料
- Qiita: AWS アクセスキーを作成する
- Qiita: Db2 Warehouse on Cloudで、ICOS上のCSVから外部表を作成する
- Qiita: Db2 Warehouse on Cloudで、外部表をつかったUNION ALL VIEWでアーカイブデータに透過的にアクセスする
- Qiita: S3からDb2 Warehouse on Cloudにデータをロードする
5. 手順概要
- AWSのIAMユーザーを作成、S3の権限を付与、アクセスキーとシークレット・アクセスキーを取得
- S3のエンドポイントを確認
- S3バケットを作成
- Db2クライアントからS3上の外部表を作成
- S3上の外部表に対するselect/insert/update/deleteの動作を確認
6. 手順1) AWSのIAMユーザーを作成、S3の権限を付与、アクセスキーとシークレット・アクセスキーを取得
次の記事を参考にして作業を実施します。
- Qiita: AWSのアクセスキーを取得する方法
6. 手順2) S3のエンドポイントを確認
AWS管理コンソールにログインします。
https://aws.amazon.com/console/
ルート・ユーザーによる作業は推奨されていないため、本記事では上の手順で作成したIAMユーザーとしてログインします。
IAMユーザー名とパスワードを入力します。
コンソール画面の右側にリージョン選択ボタンがあります（本例ではTokyoと表示）。これをクリックすると利用可能なリージョンが表示され、日本と海外のリージョンが利用可能となっています。本記事ではAsia Pacific (Tokyo) ap-northeast-1を使います。
このリージョンのエンドポイントは次のページで確認できます。本記事で使うエンドポイントはs3.ap-northeast-1.amazonaws.comとなります。
- AWS service endpoints
6. 手順3) S3バケットを作成
コンソール左上のメニューボタンを押し、表示されたパネルのStorageを押して、次にS3を押します。
画面右側のCreate bucketボタンを押します。
バケット名を任意に決めて入力しリージョンを選択します。本記事ではそれぞれbt20230504c-yamとAsia Pacific (Tokyo) ap-northeast-1を用います。Object Ownershipはデフォルト値ACLs disabled (recommmended)のままで変更しません。
同じ画面でBlock all public accessの設定もデフォルト（チェック済み）のままにします。
同じ画面でその他の設定もデフォルトのままにして、右下のCreate bucketボタンを押します。
バケットのメニュー画面が表示され、作成したバケットが表示されます。Access列にBucket and objects not publicと表示されていますが、この状態であってもAWSネットワーク外、つまりインターネットからアクセスキーを用いてアクセスできます。
6. 手順4) Db2クライアントからS3上の外部表を作成
Windows 11のスタートメニューからDb2コマンド・ウィンドウを起動して、Db2WoCのノードとデータベースをカタログに登録します。Linux環境の場合はDb2クライアントが導入されたシェルを起動します。
以下、Windows 11のDb2コマンド・ウィンドウでのコマンド入出力を記載します。ただし、下の1つ目と2つ目のコマンドはバージョンを示す事が目的で、実行する必要はありません。本記事で用いるDb2WoCインスタンスのURLはdb2w-xxxxxxx.ap-north.db2w.cloud.ibm.comです。
C:\Program Files\IBM\SQLLIB\BIN>ver
Microsoft Windows [Version 10.0.22621.1555]
C:\Program Files\IBM\SQLLIB\BIN>db2level
DB21085I このインスタンスまたはインストール (該当する場合のインスタンス名:
"DB2") は "64" ビットおよび DB2 コード・リリース "SQL11057" をレベル ID
"0608010F" で使用します。
情報トークンは、"DB2
v11.5.7000.1973"、"s2111221000"、"DYN2111221000WIN64"、およびフィックスパック
"0" です。
DB2 コピー名 "DB2COPY1" で製品は "C:\PROGRA~1\IBM\SQLLIB"
にインストールされています。
C:\Program Files\IBM\SQLLIB\BIN>db2 catalog tcpip node ND_NISO remote db2w-xxxxxxx.ap-north.db2w.cloud.ibm.com server 50001 security SSL
DB20000I CATALOG TCPIP NODE コマンドが正常に完了しました。
DB21056W ディレクトリーの変更は、ディレクトリー・キャッシュがリフレッシュされる
まで反映されません。
C:\Program Files\IBM\SQLLIB\BIN>db2 list node directory
...
ノード 6 項目:
ノード名 = ND_NISO
コメント =
ディレクトリー項目タイプ = LOCAL
プロトコル = TCPIP
ホスト名 = db2w-xxxxxxx.ap-north.db2w.cloud.ibm.com
サービス名 = 50001
セキュリティー・タイプ = SSL
C:\Program Files\IBM\SQLLIB\BIN>db2 catalog db BLUDB as DB_NISO at node ND_NISO
DB20000I CATALOG DATABASE コマンドが正常に完了しました。
DB21056W ディレクトリーの変更は、ディレクトリー・キャッシュがリフレッシュされる
まで反映されません。
C:\Program Files\IBM\SQLLIB\BIN>db2 list database directory
...
データベース 5 項目:
データベース別名 = DB_NISO
データベース名 = BLUDB
ノード名 = ND_NISO
データベース・リリース・レベル = 15.00
コメント =
ディレクトリー項目タイプ = リモート
カタログ・データベース・パーティション番号 = -1
代替サーバー・ホスト名 =
代替サーバーのポート番号 =
create external tableコマンドのオプションをDb2WoCマニュアルのS3 Syntaxから引用します。
S3 (endpoint, authKey1, authKey2, bucket)
各パラメータを次のように設定します。
- endopoint
- 【手順2) S3のエンドポイントを確認】で得た
s3.ap-northeast-1.amazonaws.com
- 【手順2) S3のエンドポイントを確認】で得た
- authKey1 (S3 access key ID)
- 【手順1) AWSのIAMユーザーを作成、、、】で得たアクセスキー
- authKey1 (S3 secret key of the access keys)
- 【手順1) AWSのIAMユーザーを作成、、、】で得たシークレット・アクセスキー
- buckett
- 【手順3) S3バケットを作成】で指定した
bt20230504c-yam
- 【手順3) S3バケットを作成】で指定した
Db2WoCに接続してS3上の外部表を定義します。下の例においてDb2WoCのユーザー名はyamasakk、作業スキーマもyamasakkです。
C:\Program Files\IBM\SQLLIB\BIN>db2
(c) Copyright IBM Corporation 1993,2007
DB2 クライアント 11.5.7.0 コマンド行プロセッサー
db2 => connect to DB_NISO user yamasakk using 'xxxxxxxx'
データベース接続情報
データベース・サーバー = DB2/LINUXX8664 11.5.8.0
SQL 許可 ID = YAMASAKK
ローカル・データベース別名 = DB_NISO
db2 => set current schema=yamasakk
DB20000I SQL コマンドが正常に完了しました。
db2 => values current schema
1
--------------------------------------------------------------------------------------------------------------------------------
YAMASAKK
1 レコードが選択されました。
db2 => CREATE EXTERNAL TABLE サンプル表0504c (番号 int, 名前 nvarchar(10)) using \
db2 (続き) => (dataobject 'ea0504b.dat' \
db2 (続き) => s3('s3.ap-northeast-1.amazonaws.com', \
db2 (続き) => 'xxxxxxxxxxxxxxxxxxxx', \
db2 (続き) => 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', \
db2 (続き) => 'bt20230504c-yam' \
db2 (続き) => ) \
db2 (続き) => )
DB20000I SQL コマンドが正常に完了しました。
db2 => list tables
表/ビュー スキーマ タイプ 作成時刻
------------------------------- --------------- ----- --------------------------
サンプル表0504C YAMASAKK T 2023-05-04-05.37.38.365013
6. 手順5) S3上の外部表に対するselect/insert/update/deleteの動作を確認
S3またはIBM Cloud Object Storage (ICOS)に外部表を作成するユースケースは、現時点でデータのロードとアンロードです。つまり通常の表に対するSQL(update/delete)実行は想定されておらず、また既存レコードが存在する状態で更にレコードを追加(insert)することも想定されていません。この制約は今後取り払われて、通常の表と同じようにSQLを実行できるようになる予定です。
以下、2023年5月時点の動作確認結果を示します。まず、2レコードをinsertしてdeleteを試みますがSQL1667Nエラー（指定された表タイプではこの操作がサポートされていない）が起きて、deleteは不可です。
db2 => insert into サンプル表0504c values (1,'山'),(2,'かず')
DB20000I SQL コマンドが正常に完了しました。
db2 => select * from サンプル表0504c
番号 名前
----------- --------------------
1 山
2 かず
2 レコードが選択されました。
db2 => select * from サンプル表0504c where 番号=2
番号 名前
----------- --------------------
2 かず
1 レコードが選択されました。
db2 => delete from サンプル表0504c where 番号=2
DB21034E コマンドが、有効なコマンド行プロセッサー・コマンドでないため、 SQL
ステートメントとして処理されました。 SQL 処理中に、次のエラーが返されました。
SQL1667N 操作が失敗しました。指定された表タイプではこの操作がサポートされていな
いためです。 指定された表: "YAMASAKK.サンプル表0504C"。 表タイプ: "EXTERNAL
TABLE"。 操作: "DELETE STATEMENT"。 SQLSTATE=42858
db2 => delete from サンプル表0504c
DB21034E コマンドが、有効なコマンド行プロセッサー・コマンドでないため、 SQL
ステートメントとして処理されました。 SQL 処理中に、次のエラーが返されました。
SQL1667N 操作が失敗しました。指定された表タイプではこの操作がサポートされていな
いためです。 指定された表: "YAMASAKK.サンプル表0504C"。 表タイプ: "EXTERNAL
TABLE"。 操作: "DELETE STATEMENT"。 SQLSTATE=42858
insertコマンドでレコード1個の追加を試みますが、元の2個のレコードは消えてしまいます。
db2 => insert into サンプル表0504c values (7,'川')
DB20000I SQL コマンドが正常に完了しました。
db2 => select * from サンプル表0504c
番号 名前
----------- --------------------
7 川
1 レコードが選択されました。
updateコマンドを実行するとdeleteコマンドと同様にSQL1667Nエラー（指定された表タイプではこの操作がサポートされていない）が起きて、updateも不可です。
db2 => update サンプル表0504c set 名前='海' where 番号=7
DB21034E コマンドが、有効なコマンド行プロセッサー・コマンドでないため、 SQL
ステートメントとして処理されました。 SQL 処理中に、次のエラーが返されました。
SQL1667N 操作が失敗しました。指定された表タイプではこの操作がサポートされていな
いためです。 指定された表: "YAMASAKK.サンプル表0504C"。 表タイプ: "EXTERNAL
TABLE"。 操作: "UPDATE STATEMENT"。 SQLSTATE=42858
7.1 エラー例：SQL1667N（指定された表タイプではこの操作がサポートされていない）
このエラーは【手順5) S3上の外部表に対するselect/insert/update/deleteの動作を確認】で説明したように現時点での制約であり、今後取り払われる予定です。
7.2 エラー例：SQL20569N（外部表の操作が失敗）
外部表を作成した直後かつレコードのinsert前にselectコマンドを実行するとエラーが起きます。レコードをinsertすれば解消します。
db2 => select * from サンプル表0505a
番号 名前
----------- --------------------
SQL20569N 外部表の操作が、対応するデータ・ファイルまたは診断ファイルの問題のた
めに失敗しました。 ファイル名: "ea0505a.dat"。 理由コード: "1"。
SQLSTATE=428IB
7.3 エラー例：SQL0901N（データベース・システム・エラー）
Db2WoCマニュアルのExample using AWS S3に載っているエンドポイントs3.amazonaws.comは環境に応じて修正する必要があります。修正せずにそのまま指定できますが、insertする時にエラーが起きます。正しくは【手順2) S3のエンドポイントを確認】で得たs3.ap-northeast-1.amazonaws.comを使ってください。
db2 => CREATE EXTERNAL TABLE サンプル表0505b (番号 int, 名前 nvarchar(10)) using \
db2 (続き) => (dataobject 'ea0505b.dat' \
db2 (続き) => s3('s3.amazonaws.com', \
db2 (続き) => 'xxxxxxxxxxxxxxxxxxxx', \
db2 (続き) => 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', \
db2 (続き) => 'bt20230504c-yam' \
db2 (続き) => ) \
db2 (続き) => )
DB20000I SQL コマンドが正常に完了しました。
db2 => insert into サンプル表0505b values (1,'山'),(2,'かず')
DB21034E コマンドが、有効なコマンド行プロセッサー・コマンドでないため、 SQL
ステートメントとして処理されました。 SQL 処理中に、次のエラーが返されました。
SQL0901N データベース・システム・エラーのために SQL
ステートメントまたはコマンドが失敗しました。 (理由 "") SQLSTATE=58004
8. S3バケットの設定
上のエラーが起きると私はバケットのパブリック・アクセス設定を変更したり、アクセスポイントを作成したりしましたが、徒労に終わりました。いずれも下に表示するデフォルト設定のままで問題は無く、変更する必要はありませんでした。
-
暗号化の設定（自動的に暗号化）
-
パーミッションの設定（パブリック・アクセス不可）
-
アクセスポイントの設定（アクセス・ポイント無し、未作成）
