Jupyter Notebook で MariaDB ColumnStore に接続

Jupyter Notebook では web ブラウザ上で対話的に Python スクリプト実行を行うことが可能です。今回は Jupyter Notebook から MariaDB ColumnStore に接続し，簡単なデータ分析，グラフ描画を行ってみます。

テスト環境

MariaDB ColumnStore のインストール

Preparing and Installing MariaDB ColumnStore 1.2.X を参照して事前準備を行います。

SELinux は無効(disabled)にし，firewalld と 古いバージョン(5.5)のMariaDBのライブラリを削除します。

yum -y remove firewalld mariadb-libs

MariaDB ColumnStore 1.2.3 をダウンロード，展開，インストールします。

wget https://downloads.mariadb.com/ColumnStore/1.2.3/centos/x86_64/7/mariadb-columnstore-1.2.3-1-centos7.x86_64.rpm.tar.gz
tar xvf mariadb-columnstore-1.2.3-1-centos7.x86_64.rpm.tar.gz
yum -y localinstall mariadb-columnstore-*.rpm

MariaDB ColumnStore Quick Installer for a single server system に書かれているように，quick_installer_single_server.sh により，シングルノード UM+PM Combined 構成でインストールを行います。

LANG=C /usr/local/mariadb/columnstore/bin/quick_installer_single_server.sh

quick_installer_single_server.sh 実行後，/etc/profile.d/columnstoreAlias.sh にある管理コマンドなどへの alias 設定を反映します。
ma(mcsadmin)で ColumnStore システムのステータスを確認します。Status がすべて ACTIVE となっていれば問題ありません。

[root@mcs12 queries]# ma getsystems
getsystemstatus Sat Mar 23 21:30:50 2019

System columnstore-1

System and Module statuses

Component Status Last Status Change
------------ -------------------------- ------------------------
System ACTIVE Sat Mar 23 20:43:18 2019
Module pm1 ACTIVE Sat Mar 23 20:43:15 2019

サンプルデータをインポート

GitHub にある ColumnStore 用のサンプルデータを clone します(データ展開にunzipが必要です)。

git clone https://github.com/mariadb-corporation/mariadb-columnstore-samples.git

今回は flights サンプルデータを用います。
テーブル作成/データダウンロード/データインポート用のスクリプトがありますので，順次実行します。

sudo yum -y install unzip
cd mariadb-columnstore-samples/flights
./create_flights_db.sh
./get_flight_data.sh
./load_flight_data.sh

load_flight_data.sh では ColumnStore 専用の bulk load ツール，cpimport を用いて高速にデータをインポートできます。

Windows 10 に Jupyter Notebook をインストール

https://www.anaconda.com/distribution/#download-section で Windows を選択，64-Bit Graphical Installer をクリックして Windows 64bit 用のインストーラをダウンロード，インストールします。

次に Anaconda Prompt を起動し，

pip install PyMySQL

で MySQL(MariaDB) コネクタをインストールします。以上でインストール作業は完了です。

Jupyter Notebook から MariaDB ColumnStore 上のデータを扱う

Jupyter Notebook を起動

Windowsスタートメニューから Jupyter Notebook を起動すると，自動的に web ブラウザが開かれ，Jupyter Notebook が操作可能となります。

[I 21:22:27.758 NotebookApp] JupyterLab extension loaded from C:\Users\foo\Anaconda3\lib\site-packages\jupyterlab
[I 21:22:27.759 NotebookApp] JupyterLab application directory is C:\Users\foo\Anaconda3\share\jupyter\lab
[I 21:22:27.762 NotebookApp] Serving notebooks from local directory: C:\Users\foo
[I 21:22:27.762 NotebookApp] The Jupyter Notebook is running at:
[I 21:22:27.763 NotebookApp] http://localhost:8888/?token=8c2b11ae401f0edf2776cb234b1eecf47e68a72635132487
[I 21:22:27.763 NotebookApp] Use Control-C to stop this server and shut down all kernels (twice to skip confirmation).
[C 21:22:27.871 NotebookApp]

To access the notebook, open this file in a browser:
file:///C:/Users/foo/AppData/Roaming/jupyter/runtime/nbserver-18360-open.html
Or copy and paste one of these URLs:
http://localhost:8888/?token=8c2b11ae401f0edf2776cb234b1eecf47e68a72635132487

FIle – New Notebook – Python 3 で新規の notebook を作成できます。

In [ ]: 

と左に書かれているセルには，Python コードを記述することができます。Cell – Cell Type で Markdown を選択すると，Markdown 形式で説明文等を加えることが可能です(LaTeX形式で数式を記述することも可能です)。

MariaDB ColumnStore へ接続

以下のようなコードで MariaDB ColumnStore に接続します。

import os
import pymysql
import pandas as pd
import matplotlib.pyplot as plt

conn = pymysql.connect(
host = '192.168.2.12',
port = 3306,
user = 'jupyter',
passwd = 'password',
db = 'flights')

flights.airports テーブルから 10 レコード取得

df = pd.read_sql_query("SELECT * FROM airports", conn)
df.head(10)

flights.airlines テーブルから 10 レコード取得

df = pd.read_sql_query("SELECT * FROM airlines", conn)
df.head(10)

2016_airline_summary.sql を実行

次にサンプルデータの flights/queries/2016_airline_summary.sql を実行しますが，今回は 2017 年のデータに対して実行したいため，以下のように where f.year = 2017 に変更します。

df = pd.read_sql_query("""
select 
q.airline,
q.volume flight_count,
round(100 * q.volume / sum(q.volume) over (order by q.airline rows between unbounded preceding and unbounded following),2) market_share_pct,
round(100 * (q.cancelled / q.volume), 2) cancelled_pct,
round(100 * (q.diverted / q.volume), 2) diverted_pct
from (
select a.airline, 
count(*) volume, 
sum(diverted) diverted, 
sum(cancelled) cancelled 
from flights f 
join airlines a on f.carrier = a.iata_code 
where f.year = 2017
group by a.airline
) q
order by flight_count desc;
""", conn)
df

チャートのプロット

matplotlib を用いて棒グラフ(bar chart)をプロットしてみます。

%matplotlib inline
df.plot.bar('airline', y=['market_share_pct','cancelled_pct','diverted_pct'], figsize=(12,5))
df.plot.bar('airline', y=['cancelled_pct','diverted_pct'], figsize=(12,5))
df.plot.bar('airline', y=['flight_count'], figsize=(12,5))

今回作成した Notebook と実行結果は Jupyter nbviewer や GitHub 上で確認することもできます。

https://nbviewer.jupyter.org/github/goto-satoru/mariadb-columnstore-sample-jupyter-notebook/blob/master/ColumnStore-flights-sample.ipynb

https://github.com/goto-satoru/mariadb-columnstore-sample-jupyter-notebook/blob/master/ColumnStore-flights-sample.ipynb

まとめ

今回は Jupyter Notebook から MariaDB ColumnStore に接続し，対話的なデータ分析，グラフ描画を行ってみました。
実行結果は nbviewer や GitHub 等で共有できますので，学習/研修目的，定期レポートの共有にも活用可能かと存じます。