Db2 Warehouse on CloudのREST APIを使用したデータロード (python & curl)



Db2 Warehouse on CloudはREST APIで操作可能です。
APIドキュメントは[こちら](https://cloud.ibm.com/apidocs/db2-warehouse-on-cloud)。
なぜが微妙に[Db2 on CloudのREST API](https://cloud.ibm.com/apidocs/db2-on-cloud/db2-on-cloud-v4)と仕様が違っていたので、[Db2 on CloudのREST APIを使用したデータロード (python & curl)](https://qiita.com/nishikyon/items/cf45ffa83334444674dc)という記事を以前書きましたが、コピって修正してDb2 Warehouse on Cloud版も今回作成しました。

なので、Db2 on Cloudの場合は[こちら](https://qiita.com/nishikyon/items/cf45ffa83334444674dc)を参照してください。

当記事ではDb2 **Warehouse** on CloudのREST APIを使用したデータロードの方法を説明します。RESTなのでcurlコマンドで実行可能ですが、ヘッダーやパラメーターの設定やレスポンスのパースが面倒なので、pythonを使用したコードで説明します。一応curlコマンドでの方法も載せます。

こちらで使用しているpythonのコードはnotebookとして以下でダウンロード可能です：
https://github.com/kyokonishito/Db2onCloud_RESTAPI/blob/main/notebooks/Db2_Warehouse_on_Cloud_API_Load.ipynb

**重要**

- ファイルの文字コードがUTF-8の場合は「BOMなし」で準備してください。BOMがあると一行目一行目の読み込みで思わぬエラーが発生する可能性があります。

# 1. 前準備
REST APIで使用するREST API host nameをあらかじめ取得しておきます。
(Db2 on Cloudで必要だったCRNは不要です)

## 1.1 REST API host name情報の取得
まずはREST API host name情報を取得します。

### 1.1.1. Db2 Webコンソールを開いて、左側のメニューからスパナのアイコン、「管理」をクリック
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/7c555596-325b-c758-54aa-205c6162cce1.png)


### 1.1.2. 上部メニューから接続をクリック
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/c9d74bfe-967c-2984-b466-e31f0f1ae850.png)


### 1.1.3.`ホスト名` を取得する
ここで取得したホスト名がAPIのhost nameになります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/8f9241f6-9adf-5042-edd9-773c6a4212d9.png)



# 2. アクセストークンの取得
ここからpythonまたはcurlコマンドを使用します。

Db2　Warehouse on Cloudのuserid, passwordを使用して、API実行に必要なアクセストークンをREST APIで取得します。
指定したuseridの権限と、APIで実行可能な権限は同じとなります。
useridとpasswordなど作った覚えのない方は、[こちらの方法](https://qiita.com/nishikyon/items/69fee851849e7ef7e163#ibm-cloud%E3%82%B3%E3%83%B3%E3%82%BD%E3%83%BC%E3%83%AB%E3%81%8B%E3%82%89%E8%B3%87%E6%A0%BC%E6%83%85%E5%A0%B1%E3%82%92%E4%BD%9C%E6%88%90%E5%8F%82%E7%85%A7lite%E3%83%97%E3%83%A9%E3%83%B3%E3%81%AE%E5%A0%B4%E5%90%88%E3%81%AF%E3%81%93%E3%82%8C%E3%81%AE%E3%81%BF)で取得してください。

API Doc：　https://cloud.ibm.com/apidocs/db2-warehouse-on-cloud#authenticate

## python

### python前準備

冒頭で必要なライブラリと変数をセットしておきます。
<>で囲まれた部分は前準備で取得した値に置き換えてください。

```python
#必要なライブラリのimport
import requests
import json
import sys

#必要な変数のセット
REST_API_URL = "<1.1.3で取得した名>"
DB_USERID = "<dbのuserid>"
DB_PW = "<dbのpassword>"
LOAD_FILE = "<ロードするファイル名（PATH付き）>"
SCHEMA_NAME = "<ロード先テーブルのスキーマ名>"
TABLE_NAME = "<ロード先テーブル名>"
```

### 本編
実行が成功すると変数access_tokenにアクセストークンの値が入ります。

```python
#REST API の　　URL 作成
service_name = '/dbapi/v4/auth/tokens'
url = 'https://' + REST_API_URL + service_name

# request header作成
headers = {}
headers ['content-type'] = "application/json"


# parameter dbアクセス用のuid, pwを指定
params = {}
params['userid'] = DB_USERID
params['password']= DB_PW

# Call the RESTful service
try:
    r = requests.post(url, headers=headers,  json=params)
except Exception as err:
    print("RESTful call failed. Detailed information follows.")
    print(err)
    sys.exit()

# Check for Invalid credentials
if (r.status_code == 401): # There was an error with the authentication
    print("RESTful called failed.")
    message = r.json()['errors']
    print(message)
    sys.exit()

# Check for anything other than 200/401
if (r.status_code != 200): # Some other failure
    print("RESTful called failed. Detailed information follows.")
    print(r.json())
    sys.exit()

# Retrieve the access token
try:
    access_token = r.json()['token']
    #print(r.json())
except:
    print("RESTful call did not return an access token.")
    print(r.json())
    sys.exit()

print (access_token)
```

## curl


<>で囲まれた部分は前準備で取得した値に置き換えて実行してください。


```sh
curl -X POST   https://<1.1.3で取得したAPI hostname>/dbapi/v4/auth/tokens \
-H 'content-type: application/json' \
-d '{"userid":"<dbのuserid>","password":"<dbのpassword>"}'
```

出力例：　tokenの値(青くマークした部分)がアクセストークンの値になります(一部マスキングしてます)。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/e635baae-54a7-5da1-b281-3014aca55161.png)

# 3. ロードするファイル(CSVファイル)のアップロード
「え？　データロードしないの？」　と思う方もいるかもしれません。どこにも書いてありませんが(!)、Db2 REST APIでデータロードするにはまずロードするファイルをテンポラリーのDb2用のcloud object storageにアップロードする必要があります。この記事では説明しませんが、自分のS3やIBM Cloud Object　Storage(ICOS)にアップロードしたものでもロードできます。ローカルから直にはできないようです。

API Doc： https://cloud.ibm.com/apidocs/db2-warehouse-on-cloud#uploadhomestoragefile

## python
2の前準備、本編コードを実行して、必要なシェル変数、access_tokenにアクセストークンの値が入っていることが前提です。

実行が成功すると変数FILE_PATHにアップロードしたファイルのPATHの値が入ります。


```python
import os

#REST API の　　URL 作成
service_name = '/dbapi/v4/home_content/'
url = 'https://' + REST_API_URL + service_name

# request header作成
headers = {}
headers ['authorization'] =  'Bearer ' + access_token #アクセストークンをHeaderにセット
headers ['x-deployment-id'] = CRN

# アップロードするファイルのセット
file = {'file': open( LOAD_FILE , 'rb')}


try:
    r = requests.post(url, headers=headers, files=file)
    print( r.status_code)
    #print(r.json())
except Exception as err:
    print("RESTful call failed. Detailed information follows.")
    print(err)


# Check for Invalid credentials
if (r.status_code == 401): # There was an error with the authentication
    print("RESTful called failed.")
    message = r.json()['errors']
    print(message)


# Check for anything other than 201/401
elif (r.status_code != 201): # Some other failure
    print("RESTful called failed. Detailed information follows.")
    print(r.text)
    #print(json.dumps(r.json(), indent=4))


# Print resppose
else:
    print(json.dumps(r.json(), indent=4))
    FILE_NAME = r.json()["resources"][0]["name"]
    print("FILE_NAME: " + FILE_NAME)
    
```

出力例： `TESTDATA.0000MB.55cols.csv`というファイルをアップロード

```json
201
{
    "count": 1,
    "resources": [
        {
            "size": 2907,
            "contents": [],
            "is_folder": "no",
            "name": "TESTDATA.0000MB.55cols.csv",
            "location": "/ldaphome/yyyyyyyy/TESTDATA.0000MB.55cols.csv"
        }
    ]
}
FILE_NAME: TESTDATA.0000MB.55cols.csv
```

## curl
2のコードを実行して、アクセストークンの値を取得していることが前提です。
<>で囲まれた部分は前準備、2で取得した値、アップロードするファイル名に置き換えてください。

```sh
!curl  -X POST   'https://<1.1.3で取得したAPI hostname>/dbapi/v4/home_content/' \
-H 'authorization: Bearer <2で取得したアクセストークンの値>' \
-H 'content-type: multipart/form-data'  \
-F 'data=@<ロードするファイル名（PATH付き）>'
```

出力例： `TESTDATA.0000MB.55cols.csv`というファイルをアップロード

**name**の値は次で使います。

```json
{"count":1,"resources":[{"size":2907,"contents":[],"is_folder":"no","name":"TESTDATA.0000MB.55cols.csv","location":"/ldaphome/yyyyyyyy/TESTDATA.0000MB.55cols.csv"}]}
```

# 4. データロードジョブの作成

アップロードしたファイルをロードするジョブを作成し、ロードを実行します。

前述したように、この記事では説明しませんが、自分のS3やIBM Cloud Object　Storage(ICOS)にアップロードしたものでもロードできます。その場合のパラメーター等は下記のAPI Docを参照してください。
API Doc: https://cloud.ibm.com/apidocs/db2-warehouse-on-cloud#createloadjob

file_optionsとload_actionは必要に応じて変更してください。
file_optionsはWebコンソールから「データのロード」する際の、「定義」で指定するオプションと同じです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/6fbe4b84-07c3-8116-581f-54f5431cd90f.png)




注意点：

 - code_page: ファイルのコードページ, utf-8であれば1208 SJISは943 その他は[こちら](https://www.ibm.com/docs/ja/db2/11.1?topic=support-supported-territory-codes-code-pages)から
 - has_header_row: Header（1行目の項目名） なし no / あり yes
     - Db2 on Cloudではnoのみだったのですが、Warehouseは両方OKでした
 - load_action：　追加でINSERT INSERT / データの置換 REPLACE

```python
    "file_options":{
        "code_page":"1208",　 #ファイルのコードページ, utf-8であれば1208
        "has_header_row":"yes",　#Header（1行目の項目名） なし no / あり yes
        "column_delimiter":",",　#カラムのデリミター
        "date_format":"YYYY-MM-DD",　#Dateのフォーマット
        "time_format":"HH:MM:SS",　#Timeのフォーマット
        "timestamp_format":"YYYY-MM-DD HH:MM:SS"}, 　#TimeStampのフォーマット
    "load_action": "INSERT",　# 追加でINSERT INSERT / データの置換 REPLACE
    "server_source":{"file_path": FILE_NAME}　#アップロードしたファイルのhomedirからのPATH
```



## python
2の前準備、本編、3のコードを実行して、必要なシェル変数、access_tokenにアクセストークンの値、　FILE_PATHの値が入っていること、ロードするファイルのアップロード実施済みが前提です。

実行が成功すると変数JOB_IDにアップロードしたデータロード　ジョブのIDの値が入ります。

```python
#REST API の　　URL 作成
service_name = '/dbapi/v4/load_jobs'
url = 'https://' + REST_API_URL + service_name

# request header作成
headers = {}
headers ['content-type'] = "application/json"
headers ['authorization'] =  'Bearer ' + access_token #アクセストークンをHeaderにセット

# ロード属性のセット
data = {
   "load_source": "SERVER",
   "schema": SCHEMA_NAME,
    "table":TABLE_NAME,
    "file_options":{
        "code_page":"1208", #ファイルのコードページ, utf-8であれば1208
        "has_header_row":"yes",　#Header（1行目の項目名） なし no / あり yes
        "column_delimiter":",", #カラムのデリミター
        "date_format":"YYYY-MM-DD", #Dateのフォーマット
        "time_format":"HH:MM:SS", #Timeのフォーマット
        "timestamp_format":"YYYY-MM-DD HH:MM:SS"}, #TimeStampのフォーマット
    "load_action": "INSERT", # 追加でINSERT INSERT / データの置換 REPLACE
    "server_source":{"file_path":FILE_NAME}  #アップロードしたファイルのhomedirからのPATH
}

try:
    r = requests.post(url, headers=headers, json=data)
    print( r.status_code)
except Exception as err:
    print("RESTful call failed. Detailed information follows.")
    print(err)


# Check for Invalid credentials
if (r.status_code == 401): # There was an error with the authentication
    print("RESTful called failed.")
    message = r.json()['errors']
    print(message)


# Check for anything other than 201/401
elif (r.status_code != 201): # Some other failure
    print("RESTful called failed. Detailed information follows.")
    print(r.text)
    #print(json.dumps(r.json(), indent=4))


# Print response
else:
    print(json.dumps(r.json(), indent=4))
    JOB_ID = r.json()["id"]
    print(f"JOB_ID: {JOB_ID}" )   
```

出力例:
3でアップロードした`TESTDATA.0000MB.55cols.csv`ファイルの データロードジョブを作成。 JOB_IDは`1675100032966`

```json
201
{
    "schema": "yyyyyyyy",
    "database": "BLUDB",
    "load_source": "SERVER",
    "load_action": "INSERT",
    "id": 1675100032966,
    "userid": "xxxxxxxx",
    "table": "0000MB_55COLS",
    "status": "Initialized"
}
JOB_ID: 1675100032966
```

## curl
2, 3のコードを実行して、アクセストークンの値、アップロードしたファイルのPATHを取得、ロードするファイルのアップロード実施済みが前提です。
<>で囲まれた部分は前準備、2、3で取得した値に置き換えてください。
file_optionsについてはこのセクションの最初の説明を参照してください。

```sh
curl -X POST https://<1.1.3で取得したAPI hostname>/dbapi/v4/load_jobs \
-H 'authorization: Bearer <2で取得したアクセストークンの値>'\
-H 'content-type: application/json' \
-d '{  \
"load_source":"SERVER", \
"load_action":"INSERT", \
"schema":"<ロード先テーブルのスキーマ名>", \
"table":"<ロード先テーブル名>", \
"server_source":{"file_path":"<3で取得したlocationの値>"}, \
"file_options":{"code_page":"1208", \
"has_header_row":"no", \
"column_delimiter":",", \
"date_format":"YYYY-MM-DD", \
"time_format":"HH:MM:SS", \
"timestamp_format":"YYYY-MM-DD HH:MM:SS"}}'
```

出力例：
**id**の値は次で使います。

```json
{"schema":"yyyyyyyy","database":"BLUDB","load_source":"SERVER","load_action":"INSERT","id":1675100032968,"userid":"xxxxxxxx","table":"0000MB_55COLS","status":"Initialized"}
```

# 5. データロードジョブの状況確認
作成したジョブの実行状況を確認します。

API Doc： https://cloud.ibm.com/apidocs/db2-warehouse-on-cloud#getloadjobbyid

ここで確認できる内容はWebコンソールの「データ」→「履歴のロード」でも確認できます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/75428c32-c34b-cd40-6d3b-18b6ef18c487.png)


## python
2の前準備、本編、3,4のコードを実行して、必要なシェル変数、access_tokenにアクセストークンの値、　JOB_IDの値が入っていることが前提です。

```python
#REST API の　　URL 作成
service_name = '/dbapi/v4/load_jobs'
url = 'https://' + REST_API_URL + service_name + '/' +  str(JOB_ID)

# request header作成
headers = {}
headers ['content-type'] = "application/json"
headers ['authorization'] =  'Bearer ' + access_token #アクセストークンをHeaderにセット


try:
    r = requests.get(url, headers=headers)
    print( r.status_code)
except Exception as err:
    print("RESTful call failed. Detailed information follows.")
    print(err)


# Check for Invalid credentials
if (r.status_code == 401): # There was an error with the authentication
    print("RESTful called failed.")
    message = r.json()['errors']
    print(message)


# Check for anything other than 201/401
elif (r.status_code != 200): # Some other failure
    print("RESTful called failed. Detailed information follows.")
    print(r.text)
    #print(json.dumps(r.json(), indent=4))


# Print response
else:
    print(json.dumps(r.json(), indent=4))
```

出力例:
4で出力されたJOB_IDのデータロードジョブの状況確認。
status.statusに`"status": "Success"`となっており、成功しています。
ロードされた行数など状況詳細はstatusの中身を確認する。

```json
200
{
    "request": {
        "schema": "yyyyyyyy",
        "stream_source": {
            "file_name": ""
        },
        "auto_create_table": {
            "column_names": [],
            "execute": ""
        },
        "load_source": "SERVER",
        "cloud_source": {
            "path": "",
            "endpoint": "",
            "auth_id": "",
            "auth_secret": ""
        },
        "max_warning_count": "1000",
        "isET": "false",
        "load_action": "INSERT",
        "max_row_count": "",
        "server_source": {
            "file_path": "TESTDATA.0000MB.55cols.csv"
        },
        "file_options": {
            "boolean_style": "",
            "date_delimiter": "",
            "ignore_zero": "",
            "has_header_row": "yes",
            "time_format": "HH:MM:SS",
            "date_style": "",
            "string_delimiter": "",
            "column_delimiter": ",",
            "null_value": "",
            "escape_char": "",
            "time_style": "",
            "encoding": "",
            "require_quotes": "",
            "delimiter_priority": "",
            "code_page": "1208",
            "timestamp_format": "YYYY-MM-DD HH:MM:SS",
            "string_blanks": "",
            "cde_analyze_frequency": "",
            "identity_columns": "",
            "use_defaults_for_missing_values": "",
            "time_delimiter": "",
            "implicitly_hidden_columns": "",
            "ctrl_chars": "",
            "date_format": "YYYY-MM-DD"
        },
        "table": "0000MB_55COLS"
    },
    "metadata": {
        "updated_at": "",
        "guid": "",
        "created_at": "",
        "url": ""
    },
    "id": "1675100032966",
    "userid": "xxxxxxxx",
    "status": {
        "rows_loaded": 9,
        "sub_status": "Success",
        "end_time": 1675833524298,
        "rows_rejected": 0,
        "bad_file": "",
        "error_count": 0,
        "start_time": 1675833512982,
        "rows_read": 9,
        "rows_deleted": 0,
        "rows_committed": 9,
        "errors_summary": [],
        "log_file": "load_1675100032966_yyyyyyyy_0000MB_55COLS_20230208-051832-GMT.txt",
        "warning_count": 0,
        "warnings_summary": [],
        "rows_partitioned": 0,
        "status": "Success",
        "rows_skipped": 0
    }
}

```

## curl
2, 3, 4のコードを実行して、アクセストークンの値取得済み、ロードするファイルのアップロード　、データロードジョブ実施済み、が前提です。
<>で囲まれた部分は前準備、2、4で取得した値に置き換えてください。

```sh
curl -X POST https://<1.1.3で取得したAPI hostname>/dbapi/v4/load_jobs/<4で取得したid> \
-H 'authorization: Bearer <2で取得したアクセストークンの値>'\
-H 'content-type: application/json' 

```

出力例：

```json
{"request":{"schema":"yyyyyyyy","stream_source":{"file_name":""},"auto_create_table":{"column_names":[],"execute":""},"load_source":"SERVER","cloud_source":{"path":"","endpoint":"","auth_id":"","auth_secret":""},"max_warning_count":"1000","isET":"false","load_action":"INSERT","max_row_count":"","server_source":{"file_path":"TESTDATA.0000MB.55cols.csv"},"file_options":{"boolean_style":"","date_delimiter":"","ignore_zero":"","has_header_row":"yes","time_format":"HH:MM:SS","date_style":"","string_delimiter":"","column_delimiter":",","null_value":"","escape_char":"","time_style":"","encoding":"","require_quotes":"","delimiter_priority":"","code_page":"1208","timestamp_format":"YYYY-MM-DD HH:MM:SS","string_blanks":"","cde_analyze_frequency":"","identity_columns":"","use_defaults_for_missing_values":"","time_delimiter":"","implicitly_hidden_columns":"","ctrl_chars":"","date_format":"YYYY-MM-DD"},"table":"0000MB_55COLS"},"metadata":{"updated_at":"","guid":"","created_at":"","url":""},"id":"1675100032966","userid":"xxxxxxxx","status":{"rows_loaded":9,"sub_status":"Success","end_time":1675833524298,"rows_rejected":0,"bad_file":"","error_count":0,"start_time":1675833512982,"rows_read":9,"rows_deleted":0,"rows_committed":9,"errors_summary":[],"log_file":"load_1675100032966_yyyyyyyy_0000MB_55COLS_20230208-051832-GMT.txt","warning_count":0,"warnings_summary":[],"rows_partitioned":0,"status":"Success","rows_skipped":0}}
```

# 6. まとめ
Db2 Warehouse on CloudのREST APIを使用したデータロードには以下のステップを踏むことが必要です：

1. アクセストークンの取得
1. ロードするファイル(CSVファイル)のアップロード
1. データロードジョブの作成
1. データロードジョブの状況確認

ここでのコードは説明のために冗長に書いています。関数などでまとめてしまえば簡単に書けるかと思いますので、ぜひ実際の業務でも使用してみてください。