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

(この投稿はIBMソリューションブログ Jupyter NotebookからDb2に簡単アクセスのリライトです。)

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オプションは導入済みでもライブラリが古い場合は更新するオプションです。

pip install -U ibm_db

IBM Cloud Watson Studio やGoogle ColaboratoryなどのSaaS環境であれば、直接Notebookのセルで!を先頭につけてibm_dbパッケージを導入可能です。

! 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]ボタンを右クリックしてメニューの「リンク先を別名で保存」(使用しているブラウザーによって多少表現が違います)をクリックし、保存してください。

こちらもSaaS環境であれば、直接Notebookのセルで!を先頭につけてwgetコマンドで直接仮想環境にdb2.ipynbをダウンロードします。

! 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コマンドを作成します。

%run db2.ipynb

環境によっては下記の警告が表示されることがありますが、無視してください。

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は不要です。

%sql CONNECT TO <データベース名> USER <ユーザー名> USING <パスワード> HOST <ホスト名　または　IPアドレス> port <ポート番号> SSL TRUE

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

実行例(SSL接続):

6. SQLの実行

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

%sql select * FROM EMPLOYEE

実行例:

デフォルトではpandas DataFrame形式で出力されます。

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

-all. -a : 全部レコード表示 (ただし現状MAX 100行)

%sql -all select * FROM EMPLOYEE

他にもオプションがありますが、ここでは割愛します。他のオプションについては当記事の一番最後のセクション「まとめ」に記載しているDb2 and Jupyter Notebooksを参照してみてください。

7. SELECTの結果セットのPythonの変数への代入

df_employee = %sql select * FROM EMPLOYEE

変数に格納された後は、pandasの構文を使って表示したり、スライスしたりすることができます。以下はその例です(Db2 Magic コマンドではなくpandasの構文です)。

#単に表示 (前後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行)

実行例: 0行目のFIRSTNMEを表示、WORKDEPTがC01の行を表示

実行例: 最初10行

実行例: 最後10行

8. 簡易グラフ機能

%sqlコマンドで、簡単なチャートが作成できます:

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

# 棒グラフ
%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

実行例:

9. まとめ

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

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

• Db2 and Jupyter Notebooks

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

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

• Db2 on CloudをLiteプラン(無料)で作成

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

ぜひ皆様実際に使ってみていただければと思います!