More than 3 years have passed since last update.

IBM Cloud Object StorageファイルをDB2へロードする(1)

Last updated at 2022-06-07Posted at 2022-05-18

1.目的

IBM Cloud SQL Query では、IBM Cloud Object StorageからIBM Db2　on Cloud へのデータの転送および変換の自動化がサポートされています。当文書及び次編ではWatsonStudio(PackforData)/Notebookを使いICOS上のFileからDB2テーブル・ロードを実施して、手順の確認及び性能測定テスト/チューニングを実施しました。参考までにDB2(Native)clientでも同様の内容を実施して比較検証しております。

IBM Cloud Object StorageファイルをDB2へロードする(1) :本文書
IBM Cloud Object StorageファイルをDB2へロードする(2)~SQL Query 性能チューニングについて

2.作業手順　

準備

前提条件

WatsonStudioインスタンスの起動/Project作成/Notebookの準備
DB2インスタンス作成/CRN(Cloud Resource Name)情報入手
SQL Queryインスタンス作成/CRN(Cloud Resource Name)情報入手

まずはサービス・インスタンス接続のための権限設定を行います。

APIkeyの作成

IAMでまずサービスIDを作成し、アクセスポリシーの設定をします。Cloud Portalの最上部メニュー[管理]-[アクセス(IAM)]-サービスIDを選択して新規作成のボタンを押します。ここではICOS,SQL Query,DB2の管理者アクセス権限を付与します。

APIキーのタブでAPIキーを作成して、APIキーをメモしておきます。

DB2新規資格情報を作成

DB2では新規資格情報を作成します。PortalのDB2初期画面の左メニューよりサービス資格情報を選択して新規資格情報ボタンを押して接続に必要になるusername/password/host/port等の情報を入手しておきます。

SQL Queryを利用したPythonCoding

Pythonのコードを入力していきます。WatsonStudioのNotebookを開きます。前提となるSWを導入します。

　　　　　　　　　　　中途省略

!pip install --upgrade ibmcloudsql
import ibmcloudsql

ibmcloudsqlというのは、SQL Queryを実行するためのPythonクライアントです。以下のページに詳細説明があります。

先ほど入手したAPIキー、SQLQueryインスタンスCRNを入力します。
sql_cos_endpointには、SQL Queryの実行結果を保管するObject Storageの場所を指定します。以下フォーマットになります。
cos://< エンドポイント>/< バケット名>/< エイリアス名またはファイル名>

#cloud_api_key 入力　(ICOS,SQLQuery,DB2権限)
cloud_api_key = "*************"

#SQLQuery Instance crn 入力 
sql_crn = "crn:v1:bluemix:public:sql-query:us-south:a/039dbe6794084c7cb514a276dd2345da:82c3edab-8a45-4f0d-b598-922cafef8b83::"

#SQLQuery Job log 出力先
sql_cos_endpoint = "cos://jp-tok/dbload/result/"

sqlClient = ibmcloudsql.SQLQuery(cloud_api_key,sql_crn,sql_cos_endpoint)

次にSQL Query文を入力します。
FROM cos://< エンドポイント>/< バケット名>/< ファイル名>
into < DB2サービスCRN名>/< スキーマ名>.< テーブル名>
の形式で指定します。スキーマ名を省略するとusernameが自動付与されます。

#SQL文の入力　(from: ICOS CSV名, into: DB2 table名)
query = """SELECT *
FROM cos://jp-tok/dbload/data10.csv STORED AS CSV
into crn:v1:bluemix:public:dashdb-for-transactions:jp-tok:a/039dbe6794084c7cb514a276dd2345da:4299a8bc-68c6-4080-895f-cfa4e3cf78fe::/employee30"""

#SQL文の実行
kekka = sqlClient.run_sql(query)

結果はIBM Cloud Portalで標準提供されるDB2コンソールやSQLQueryコンソール等から確認できます。

DB2clientを利用したPythonCoding

ICOSにあるCSVファイルを一度Pandasデータフレームとして取り込み、それをibmdbpyを使ってDb2 on Cloudの表に書き出します。
ICOSやDb2 on Cloudに接続するために必要な認証情報は、手動でコードを記述することもできますが、予めWatson StudioのプロジェクトにICOS上のファイルをデータ・アセットとして、DB2接続をデータアクセスとして登録しておけば、認証情報をNotebookのセルに自動で取り込むことができ、Coding負荷が大幅に軽減されます。

Notebookの画面右の方にある"1010"アイコン（"Find and add data"アイコン）をクリックしてデータ・アセット一覧を表示、事前に作成しておいたデータアセットemploy_icos(CSVファイル)の下に表示されている"コードに挿入"をクリック、さらに"Insert pandas DataFrame”を選択すると、そのCSVファイルをPadasデータフレームとして取り込むコードが自動入力されます。

import types
import pandas as pd
import ibm_boto3
from botocore.client import Config

def __iter__(self): return 0

# @hidden_cell
# The following code accesses a file in your IBM Cloud Object Storage. It includes your credentials.
# You might want to remove those credentials before you share your notebook.

ICOS_client = ibm_boto3.client(
    service_name='s3',
    ibm_api_key_id='xxxxxxxxx',
    ibm_service_instance_id='crn:v1:bluemix:public:cloud-object-storage:global:a/039dbe6794084c7cb514a276dd2345da:78ee3380-6b51-4cc5-b61f-f0bd9d3e0fbd::',
    ibm_auth_endpoint='https://iam.cloud.ibm.com/identity/token',
    config=Config(signature_version='oauth'),
    endpoint_url='https://s3.jp-tok.cloud-object-storage.appdomain.cloud'
)

body = ICOS_client.get_object(Bucket='dbload', Key='data10.csv')['Body']
# add missing __iter__ method, so pandas accepts body as file-like object
if not hasattr(body, "__iter__"): body.__iter__ = types.MethodType( __iter__, body )

df_data_1 = pd.read_csv(body)
df_data_1.head()

Notebookの画面右の方にある"1010"アイコン（"Find and add data"アイコン）をクリックして今度は”接続"とある方を選択、登録済みのdb2connectの下に表示されている"コードに挿入"をクリック、さらに"資格情報”を選択すると、接続のために必要な認証情報についての変数定義がセルに自動入力されます。（下記例におけるcredential_1の部分）
Db2への接続はIdaDatabaseによって作成されます。

# @hidden_cell
# The following code contains the credentials for a connection in your Project.
# You might want to remove those credentials before you share your notebook.
credentials_1 = {
    'username': 'uuuuuuuuu',
    'password': """ppppppppp""",
    'database': 'bludb',
    'host': '4299a8bc-68c6-4080-895f-cfa4e3cf78fe.bs2ipmbt0ifld8rdc9u0.databases.appdomain.cloud',
    'port': '30450',
}

dsn = 'DASHDB;Database={};Hostname={};Port={};PROTOCOL=TCPIP;UID={};PWD={};SECURITY=SSL'.format(
    credentials_1['database'],
    credentials_1['host'],
    credentials_1['port'],
    credentials_1['username'],
    credentials_1['password']
)

!pip install ibmdbpy
from ibmdbpy import IdaDataBase, IdaDataFrame

idadb = IdaDataBase(dsn)

作成した表EMPLOYEE30を参照するIDAデータフレームida_dfを定義し、Pandasデータフレームdf_data_1の内容を書き出してから、コミット処理をしています。最後にDb2への接続を切断するためにはclose()を実行します。

ida_df = IdaDataFrame(idadb, 'EMPLOYEE30')
idadb.append(ida_df, df_data_1)

idadb.commit

idadb.close()

DB2 client vs SQL Queryとの性能比較

ケース1 CSV FileのDB2ロード時間

1つのCSV FileをDB2にロードして時間を測定したところ以下のようになりました。

Fileサイズ	SQL Query	DB2Client
22Kbyte	21.98秒	4.35秒
11.2Mbyte	49.6秒	6.15秒
100.8Mbyte	173.56秒	18.19秒

WatsonStudio Notebookリソース: 1vCPU,4GB RAM

ケース2 複数ParquetFileのDB2ロード時間

業務プロトタイプ作成して7*Parquet Fileから3columnを抽出してDB2にLoadする時間を測定

Parquet File: 1.2~2.4MB x 7 File,約3,200万行
Notebook: (2vCPU, 8GB MEM)

	SQL Query	DB2Client
処理時間	59分55秒	39分40秒

*5回実施平均値
*SQLQueryはIBM Cloud SQLQueryコンソール上での処理時間,WatsonStudioより実行するとさらに時間かかると考えられる。

いずれのケースもDB2Clientの方が処理速度は速いという結論が得られた。
ICOS上でpartition化されたファイルを処理するのと異なり、DB2に１つのテーブルを作成するだけなのでSparkQueryの並列処理のメリットが生かされないと考えられます。

3.まとめ

SQLQueryは、ICOS上の大量データ(サイズ,数)のDataLakeのPipeLine処理全体に適用するのは有効であるが、単独でDB2テーブルにLoadするのであれば、DB2 Nativeで実施するのに比較して　

(1)パフォーマンス劣化
(2)現時点ではDB2のテーブル新規作成のみ可能、既存に同一テーブルがある場合は削除後に新規にテーブルを作成(文字列型はVARCHAR(32000)にマップ)、したがいデータ形式check等は別途実施する必要ある、
(3)WatsonStudioのデータ資産としてSQLQueryの接続を登録しても認証情報などのコード自動挿入はサポートされない(現時点ではICOSデータからの結果表示/ICOS出力する時のみサポート)

等の制約があり、これらを踏まえた上で利用を検討するのがベターです。
なおSQLQueryにはDB2Load時に複数の並列データベース接続を開き処理することがサポートされており次回これを検証予定です。

[IBM Cloud Object StorageファイルをDB2へロードする[2]~SQL Query Parallelismについて]