(分析1)jupyterでSQL設計とデータ分析

■概略

オープンソースエンジニア歴30年超の筆者が2023年からIBMiを学びだした学習記録です
jupyterを使ってIBMiのデータベースの確認、分析を行ってみます

◯GUI DB管理ツールとしてのjupyter

オープンソースでよく使われるMySQL・MariaDBやPostgreSQLの管理はSQLで
行えますが、phpMySQLAdminやphpPgAdminなどのGUIツールを使うことも
多いと思います

ACSの[SQLスクリプトの実行]アプリでSQLの実行はできるのですが取得件数に
制限があり(最初100行までしか表示されずスクロールすると表示されます)、
またGUIではありません。
IBMiのCGIでSQLを使うなら、GUIでテーブル定義を確認し、SQLをサンプル実行して
事前に正しいSQLかを確認したいものです

筆者はデータ分析ツールのデファクトスタンダードであるjupyterlabをGUI DB管理
ツールおよびDB分析ツールとして使うことにしました

jupyterの本来の使い方である、DB分析ツールについては記載しませんが、
IBMiは社内で最も古いDBの一つで経営に直結するデータのはずです
IBMiからSQLでデータ取得しグラフ化すれば有用なデータ分析ができる思います

■jupyterlabインストール

＊jupyterはcsvやSQL結果をpythonで分析するWebアプリでjupyterlabは最新版
＊IBMiと通信可能なLinux VM(Ubuntu22.04)を作成する
＊jupyterlabはLinux VM上のDocker(コンテナ)で稼働させる
＊S3にあるテキストファイル(ログファイル)をSQLで分析したいニーズがあるので、
　コンテナイメージはjupyter-pysparkを利用する(イメージ共通にしたいため)

◯Linux(Ubuntu22.04)にDockerをインストール

# apt-get install ca-certificates curl gnupg lsb-release
# mkdir -p /etc/apt/keyrings
# curl -fsSL https://download.docker.com/linux/ubuntu/gpg \
  | sudo gpg --dearmor -o /etc/apt/keyrings/docker.gpg
# echo "deb [arch=$(dpkg --print-architecture) signed-by=/etc/apt/keyrings/docker.gpg] \
  https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable" \
  | sudo tee /etc/apt/sources.list.d/docker.list > /dev/null
# apt-get update
# apt-get install docker-ce docker-ce-cli containerd.io docker-compose-plugin
# docker -v
Docker version 24.0.6, build ed223bc
# usermod -aG docker ubuntu ※ubuntuでdockerを実行できるようにするため
docker:x:118:ubuntu
# /etc/init.d/docker start
# systemctl enable docker ※自動起動設定
# exit
$ mkdir jupyter-as400
$ cd jupyter-as400

◯コンテナイメージの作成

＊グラフで日本語が使えるようにDockerイメージ内に日本語フォントをインストール
　する
　jupyter公式pyspark-notebookにNoto Sans JPフォントを追加する
＊IBMiのODBCにアクセスするには以下3つのアプリが必要
　ibm-iaccess+unixodbc+pyodbc
　https://www.ibm.com/support/pages/ibm-iaccess-linux-odbc-configuration

Dockerfile

FROM jupyter/pyspark-notebook
USER root

# Noto-Sans
RUN wget https://fonts.google.com/download?family=Noto%20Sans%20JP -O /tmp/fonts_noto.zip && \
    mkdir -p /usr/share/fonts &&\
    unzip /tmp/fonts_noto.zip -d /usr/share/fonts

# AS400 ODBC
RUN curl https://public.dhe.ibm.com/software/ibmi/products/odbc/debs/dists/1.1.0/ibmi-acs-1.1.0.list | sudo tee /etc/apt/sources.list.d/ibmi-acs-1.1.0.list
RUN apt update
RUN apt install -y unixodbc
RUN pip install pyodbc
RUN apt install -y ibm-iaccess

Dockerfileからjupyter-pysparkイメージをbuildする

$ ./build_pyspark.bash
---内容---
#!/bin/bash
docker build -t pyspark-jpfont:latest .

◯コンテナの起動

・tokenを指定する(jupyterlabログイン時に入力する認証情報)
・Dockerなので再起動するとデータが消える
　データを保存できるようjupyterlab起動時に表示されるworkに
　Ubuntu内のディレクトリをマウントする
・自動起動(--restart-always)

$ ./start_pyspark.bash  
---内容---
#!/bin/bash
docker run --restart always -d -p 8888:8888 -e TZ=Asia/Tokyo --name pysparkjp -v /home/{Ubuntuのディレクトリ}/:/home/jovyan/work pyspark-jpfont start-notebook.sh --NotebookApp.token='{トークン}'

◯コンテナの停止

＃ docker ps -a  
CONTAINER ID   IMAGE                      COMMAND                  CREATED         STATUS                   PORTS                                                 NAMES
4ba49e68c5a0   jupyter/pyspark-notebook   "tini -g -- start-no…"   9 minutes ago   Up 9 minutes (healthy)   4040/tcp, 0.0.0.0:8888->8888/tcp, :::8888->8888/tcp   pyspark

$ ./stop_pyspark.bash  
---内容---
#!/bin/bash
docker stop pysparkjp

◯jupyterlabにアクセス

ブラウザで http://{IP}:8888 にアクセスする
/workにubuntuのディレクトリ(例:/home/ubuntu/jupyter)がマウントされている

■jupyterlabの共通コード(以下python3)

以下のコードはjupyterlabのセルに入力する

◯共通ライブラリと日本語フォント

import numpy as np
import pandas as pd
import matplotlib
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
from matplotlib.ticker import ScalarFormatter
import time, datetime
from datetime import timedelta
import math
from IPython.display import HTML
import IPython

# 日本語フォントの組み込み
import matplotlib.font_manager
flag=False
for f in matplotlib.font_manager.fontManager.ttflist:
    if f.name == 'Noto Sans JP':
	flag = True
if flag == False:
    matplotlib.font_manager.fontManager.addfont("/usr/share/fonts/NotoSansJP-VariableFont_wght.ttf")
plt.rcParams['font.family'] = 'Noto Sans JP'

◯DB接続関数

as400dev,as400用の関数を作成する
接続はReadOnlyで行う

as400cred.py(同じディレクトリ)

as400dev='192.168.xx.xa'
as400='192.168.xxx.xb'
as400dev_user='{ユーザ1}'
as400dev_password='{パスワード1}'
as400_user='{ユーザ2}'
as400_password='{パスワード2}'

import as400cred
import pyodbc
from sqlalchemy import create_engine, text
import warnings
warnings.filterwarnings('ignore')

class ConnectionType:
    ReadWrite = 0 #Read/Write (all SQL statements allowed)
    ReadCall = 1 #Read/Call (SELECT and CALL statements allowed)
    ReadOnly = 2 #Read-only (SELECT statements only)
    
# 関数
def connect_prd():
    conn = pyodbc.connect(
	driver='{IBM i Access ODBC Driver 64-bit}',
	system = as400cred.as400,
	uid = as400cred.as400_user,
	pwd = as400cred.as400_password,
	CCSID=1208,TRANSLATE=1,
	ConnectionType=ConnectionType.ReadOnly
    )
    return conn
    
def connect_dev():
    conn = pyodbc.connect(
    driver='{IBM i Access ODBC Driver 64-bit}',
    system = as400cred.as400dev,
    uid = as400cred.as400dev_user,
    pwd = as400cred.as400dev_password,
    CCSID=1208,TRANSLATE=1,
    ConnectionType=ConnectionType.ReadOnly
    )
    return conn

◯テーブル(ファイル)定義確認関数(DSPFFD相当)

def showCountDescData(SCHEMA_NAME,TABLE_NAME,showDesc,showData):
    t0 = time.time() 
    
    # 全件表示
    pd.set_option('display.max_rows', 200)
    pd.set_option('display.max_columns', 200)
    
    # DB接続
    conn = connect_dev()
    
    # テーブル名
    query = """
    SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TEXT,TABLE_TYPE,TABLE_OWNER,LAST_ALTERED_TIMESTAMP 
      FROM QSYS2.SYSTABLES
     where TABLE_SCHEMA='{0}'
       and TABLE_NAME='{1}'
      order by ROW_LENGTH desc
    """.format(SCHEMA_NAME, TABLE_NAME)
    df = pd.read_sql(sql=query, con=conn)
    print("◯テーブル名:")
    display(df.head(2))

    # 件数
    query = """
    select count(*) from {0}.{1} limit 10
    """.format(SCHEMA_NAME, TABLE_NAME)
    df = pd.read_sql(sql=query, con=conn)
    print("件数 : {0:,}".format(df.iat[0,0]))

    if showDesc:
	# 定義
	query = """
	SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_TEXT,DATA_TYPE,LENGTH
	  FROM QSYS2.SYSCOLUMN2 
	 WHERE TABLE_SCHEMA='{0}' and TABLE_NAME = '{1}'
	""".format(SCHEMA_NAME, TABLE_NAME)
	df = pd.read_sql(sql=query, con=conn)
	print("定義 : ")
	display(df)

    if showData:
	# 内容
	query = """
	select * from {0}.{1} limit 5
	""".format(SCHEMA_NAME, TABLE_NAME)
	df = pd.read_sql(sql=query, con=conn)
	print("内容(5件): ")
	display(df.head(5))
    
    ## CSV出力とダウンロードリンク
    #csvfile = "05-data\PJLIBF_{0}.csv".format(TABLE_NAME)
    #df.to_csv(csvfile)
    #display(HTML('<a [CSVダウンロード]="{0}" href="{1}">Download</a>'.format(csvfile,csvfile)))
    print("")

実行例

showCountDescData('BKNLIBF', 'GMBKAF', True, False)

◯テーブル名:

	TABLE_SCHEMA	TABLE_NAME	TABLE_TEXT	TABLE_TYPE	TABLE_OWNER	LAST_ALTERED_TIMESTAMP
0	BKNLIBF	GMBKAF	物件マスター	P	EM	2024-06-04 18:31:50.821

件数 : 7,467
定義 :

	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	COLUMN_TEXT	DATA_TYPE	LENGTH
0	BKNLIBF	GMBKAF	BKA001	物件CD	CHAR	4
1	BKNLIBF	GMBKAF	BKA002	物件枝番	NUMERIC	2
2	BKNLIBF	GMBKAF	BKA003	戸数	NUMERIC	4

…

■juypterlabサンプルコード

◯SQL実行

＊count(＊)で件数、group byで集計等SQLを駆使して分析する
＊件数が多い時はlimit {数字}で限定すると応答が早い

conn = connect_dev()
query = """
SELECT ＊
  FROM {ライブラリ名}.{テーブル(ファイル)名}
"""
df = pd.read_sql(sql=query, con=conn)
display(df)

◯csv出力

以下でcsvに出力し、jupyterの左メニューからDownloadできる
漢字がUTF-8なので、Excelで直接開くならSJISに変換すること

df.to_csv({csvファイル名})

◯テーブル(ファイル)一覧(以下SQLのみ掲載)

TABLE_TEXTをlike検索すれば、例えば「物件」が含まれるテーブル一覧を得られる

query = """
SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TEXT,COLUMN_COUNT,ROW_LENGTH,TABLE_TYPE,TABLE_OWNER,LAST_ALTERED_TIMESTAMP 
  FROM QSYS2.SYSTABLES
 where TABLE_SCHEMA='{ライブラリ}'
"""

◯フィールド一覧

COLUMN_TEXTをlike検索すれば、例えば全テーブルの中で「役員」が含まれる、
フィールドの一覧が得られる

query = """
SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_TEXT,DATA_TYPE,LENGTH
  FROM QSYS2.SYSCOLUMN2 
 WHERE TABLE_SCHEMA='{ライブラリ}'
"""

◯ソースファイル取得

　(DSPFD FILE({ターゲットライブラリ}/QCLSRC) TYPE(＊MBRLIST)相当)

query = """
SELECT TABLE_SCHEMA,TABLE_PARTITION,SOURCE_TYPE,NUMBER_ROWS,PARTITION_TEXT,LAST_SOURCE_UPDATE_TIMESTAMP
  FROM QSYS2.SYSPARTITIONSTAT 
 WHERE (TABLE_SCHEMA='{ライブラリ名}')
   AND (TABLE_NAME='{ソース・ファイル名 例:QRPGSRC}')
"""

◯オブジェクト名取得(DSPFD相当)

whereで条件をつけ表示を絞るとよい
種類(RPG,CL等)、作成日時、最終利用日時、利用回数、日本語説明などが表示できる

query = """
SELECT OBJATTRIBUTE,OBJNAME,OBJCREATED,LAST_USED_TIMESTAMP AS LAST_USED,DAYS_USED_COUNT as DAYS,OBJTEXT
  FROM TABLE(QSYS2.OBJECT_STATISTICS('{ライブラリ}','*ALL'))
"""

◯1年以内に利用されたプログラム名取得(DSPFD相当)

query = """
SELECT OBJATTRIBUTE,OBJNAME,OBJCREATED,LAST_USED_TIMESTAMP AS LAST_USED,DAYS_USED_COUNT as DAYS,OBJTEXT
  FROM TABLE(QSYS2.OBJECT_STATISTICS('{ライブラリ}','*PGM'))
 where LAST_USED_TIMESTAMP is not null
   and '{1年前の日付 yyyy-mm-dd}'<=LAST_USED_TIMESTAMP
   order by DAYS_USED_COUNT desc
"""

◯ユーザプロファイル取得(DSPUSRPRF相当)

2023/04/01以後にログインしたユーザの初期メニューと初期プログラム

query = """
select 
AUTHORIZATION_NAME as username,
PREVIOUS_SIGNON,
USER_CLASS_NAME as class,
INITIAL_MENU_NAME as ini_menu,
INITIAL_MENU_LIBRARY_NAME as ini_menu_lib,
INITIAL_PROGRAM_NAME as ini_pgm,
INITIAL_PROGRAM_LIBRARY_NAME as ini_pgm_lib,
STORAGE_USED as bytesused,
TEXT_DESCRIPTION
FROM QSYS2.USER_INFO
WHERE '2023-04-01'<=PREVIOUS_SIGNON
  and (INITIAL_PROGRAM_LIBRARY_NAME='BKNLIB' 
   or INITIAL_PROGRAM_LIBRARY_NAME='BKNLIB')
order by PREVIOUS_SIGNON desc
"""

◯ジョブログ取得(DSPJOBLOG相当)

ジョブログは全実行PGMが出力されない
CLで出力設定したPGMのみ出力されている

query = """
select  CREATE_TIMESTAMP,USER_NAME,JOB_NAME,
	PROGRAM_THAT_OPENED_FILE_LIBRARY as LIB,PROGRAM_THAT_OPENED_FILE_NAME as FILE
  from qsys2.output_queue_entries
 where PROGRAM_THAT_OPENED_FILE_LIBRARY='{ライブラリ}'
"""