Jupyter NotebookからDb2に簡単アクセス

(この投稿は[IBMソリューションブログ Jupyter NotebookからDb2に簡単アクセス](https://www.ibm.com/blogs/solutions/jp-ja/db2-jupyter-magic-command/)のリライトです。)

Jupyter Notebookから今回紹介するDb2 Magicコマンドを使えば、Pythonのコードを書かずに、データベース内でのSQLのプロトタイピング、アプリケーションの構築、結果の分析、データのグラフ化などを素早く行うことができます。ぜひお試しください!

# 1. Db2 Magic コマンド とは
MagicコマンドとはJupyter Notebookの機能でコードブロック内で使用できる構文を拡張します。Magicコマンドは、パーセント記号「%」で始まります。

標準で提供されているMagicコマンドはNotebookの動作の変更、OSコマンドの実行、 Notebook機能の拡張など、 環境内でさまざまな機能を提供しています。

Jupyter NotebookからDb2へのアクセスを簡単にするために作成されたのがDb2 Magicコマンド`%sql`です。このMagicコマンドを使用すると、標準的なSQL構文を使用してDb2テーブルを照会することができます。このコマンドは標準では提供されていないので、ダウンロードしてロードが必要です。

# 2. 前準備
## 2-1. Python環境にibm_dbパッケージの導入
Db2にアクセスするためpipで`ibm_db`パッケージを導入します。下記のコマンドの`-U`オプションは導入済みでもライブラリが古い場合は更新するオプションです。
```ksh
pip install -U ibm_db
```

IBM Cloud Watson Studio やGoogle ColaboratoryなどのSaaS環境であれば、直接Notebookのセルで!を先頭につけてibm_dbパッケージを導入可能です。
```py
! pip install -U ibm_db
```
## 2-2. Db2 magic コマンドのモジュールをダウンロード
Jupyter Notebookでアクセスできる場所にDb2 magic コマンドのモジュール`db2.ipynb`をダウンロードします。GitHubのパスは以下になります:
- https://github.com/IBM/db2-jupyter/blob/master/db2.ipynb

このURLにWebブラウザーでアクセスし、[Raw]ボタンを右クリックしてメニューの「リンク先を別名で保存」(使用しているブラウザーによって多少表現が違います)をクリックし、保存してください。
![db2_ipynb.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/d1066ae5-18ca-bc83-e5a3-44ed29cdc187.png)

こちらもSaaS環境であれば、直接Notebookのセルで!を先頭につけてwgetコマンドで直接仮想環境にdb2.ipynbをダウンロードします。
```py
! wget https://raw.githubusercontent.com/IBM/db2-jupyter/master/db2.ipynb
```

# 3. ダウンロードしたdb2.ipynbを実行
前準備ができたら、Db2 Magicコマンドをロードします。空のNotebookを開いてください。
セルに以下のコマンドを入力して実行してください。`db2.ipynb`には必要に応じてPATHをつけてください。`%run db2.ipynb`コマンドは、db2.ipynbファイルの内容を読み込み、新しい %sqlと %%sqlコマンドを作成します。
```py
%run db2.ipynb
```
環境によっては下記の警告が表示されることがありますが、無視してください。
```console
Warning: QGRID is unavailable for displaying results in scrollable windows.
Install QGRID if you want to enable scrolling of result sets.
Warning: PANDAS level does not support Db2 typing which will can increase memory usage.
Install PANDAS version 1.3+ for more efficient dataframe creation.
```

# 4. コマンドの基本
以下がDb2 Magicコマンドの基本的な使用方法です:

- `%sql` コマンドは一行コマンドに使用され、`%%sql` は SQL のブロック(複数のSQL)を実行します。
- `%sql`コマンドの結果のみ変数に代入できます。
- Pythonの変数を`%sql`コマンドに渡すには，{}の中括弧で変数名をかこみます。
- 単一の `%sql`コマンドは、行末にバックスラッシュ\を使用して複数行に記載できます。
- `%%sql` ブロック内の複数のステートメントでは、セミコロン;をデリミタとして使用します。

# 5. Db2への接続
ではいよいよDb2 Magicコマンドを使ってDb2に接続してみます。
接続前に接続先のDb2のホスト名、ポート番号、Db名、ユーザーID、パスワード、SSL接続かどうかの情報を取得しておいてください。

下記のコマンドをセルに入力し、実行します。SSL接続の場合は最後に`SSL TRUE`を付けます。SSL接続が不要な場合は`SSL TRUE`は不要です。
```py
%sql CONNECT TO <データベース名> USER <ユーザー名> USING <パスワード> HOST <ホスト名　または　IPアドレス> port <ポート番号> SSL TRUE
```

コマンド実行時は先頭に`%sql`を付けます。
Db2にCLIでコマンドラインから接続した方はご存知と思いますが、この接続構文はCLIを使用して接続する時の構文とほぼ同様です。コマンドは大文字でも小文字でも問題ありません。

実行例(SSL接続):
![db_connect.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/adadf061-2cd9-de92-b89a-f21000519036.png)

# 6. SQLの実行
SQLの実行は普通に`%sql`にスペースを1つ以上開けて、SQLを記載すれば実行できます。

```sql
%sql select * FROM EMPLOYEE
```

実行例:![db_select.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/5f2411b7-ef07-a50b-429c-35ed4f17b402.png)

デフォルトでは[pandas DataFrame形式](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.html)で出力されます。

出力の最初の5行と最後の5行を表示します。
もっと見たい場合は、特別なフラグ(-all)を使用します。

-all. -a : 全部レコード表示 (ただし現状MAX 100行)
```sql
%sql -all select * FROM EMPLOYEE
```
他にもオプションがありますが、ここでは割愛します。他のオプションについては当記事の一番最後のセクション「まとめ」に記載している[Db2 and Jupyter Notebooks](https://speakerdeck.com/kyokonishito/db2-and-jupyter-notebooks)を参照してみてください。

# 7. SELECTの結果セットのPythonの変数への代入
```py
df_employee = %sql select * FROM EMPLOYEE
```
変数に格納された後は、[pandas](https://pandas.pydata.org/docs/index.html)の構文を使って表示したり、スライスしたりすることができます。以下はその例です(Db2 Magic コマンドではなくpandasの構文です)。

```py
#単に表示 (前後5行)
df_employee
# 0行目のFIRSTNMEを表示 (行数は0から開始)
df_employee[‘FIRSTNME’][0]
# index 10行目を表示 (0から始まるので実質11行目)
df_employee.iloc[[10]]
# WORKDEPTがC01の行を表示 (これはSQLでもいいかも、、、)
df_employee.loc[(df_employee[‘WORKDEPT’]==’C01′)]
#　最初10行
df_employee.head(10)
#　最後10行
df_employee.tail(10)
```

実行例: 単に表示 (前後5行)
![pandas01.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/82c8db59-1860-7885-3dc6-6a1bbbfbb306.png)

実行例: 0行目のFIRSTNMEを表示、WORKDEPTがC01の行を表示
![pandas02.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/9f30515f-ec37-9af4-d65a-bbf81c1629bc.png)

実行例: 最初10行
![pandas03.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/3c0ebcd9-0d36-5de5-cb42-9cb7dca4b780.png)

実行例: 最後10行
![pandas04.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/2f4f9f5c-6e86-906f-2529-31322dab28bc.png)

# 8. 簡易グラフ機能
`%sql`コマンドで、簡単なチャートが作成できます:

- `-bar` 棒グラフ
- `-pie` 円グラフ
- `-line` 折れ線グラフ

```py
# 棒グラフ
%sql -bar SELECT WORKDEPT, AVG(SALARY) FROM EMPLOYEE GROUP BY WORKDEPT
# 円グラフ
%sql -pie SELECT WORKDEPT, AVG(SALARY) FROM EMPLOYEE GROUP BY WORKDEPT
# 折れ線グラフ
%sql -line SELECT WORKDEPT, AVG(SALARY) FROM EMPLOYEE GROUP BY WORKDEPT
```

実行例:
![graph.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/294516/3e934518-8581-eb19-49a8-649ddca12f74.png)

# 9. まとめ
今回はDb2 Magicコマンドの使い方の一部をご紹介しましたが、その他にも通常CLIで実施するようなこととPythonを組み合わせたりして簡単な構文でDb2のデータを活用可能です。

その他のいろいろなコマンドの使い方については以下の資料をぜひ参照してみてください。

- [Db2 and Jupyter Notebooks](https://speakerdeck.com/kyokonishito/db2-and-jupyter-notebooks)
 

またDb2のテーブル作成可能な環境があれば一通りの機能を簡単に動かせる説明入りのJupyter NotebookのサンプルをGitHubで公開してありますので、ダウンロードして使ってみてください。


https://github.com/kyokonishito/db2magic-cmd-sample/blob/main/Db2MagicCommand-Demo.ipynb
 

尚Db2環境はIBM CloudのDb2 on CloudのLiteプランを使用すれば無料で試すことができます。

- [Db2 on CloudをLiteプラン(無料)で作成](https://qiita.com/nishikyon/items/0219a81673f0b84980da)
 

本家db2-jupyter のGithub repositoryにはいろいろなサンプルNotebookがあります。最新の情報もこちらになります。ぜひこちらも参照してみてください(コンテンツは英語になります)。

https://github.com/IBM/db2-jupyter
 
ぜひ皆様実際に使ってみていただければと思います!