(分析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を利用する(イメージ共通にしたいため)
 
### <u>◯Linux(Ubuntu22.04)にDockerをインストール</u>
```
# 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
```
### <u>◯コンテナイメージの作成</u>
＊グラフで日本語が使えるように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 .
```
### <u>◯コンテナの起動</u>
・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='{トークン}'
```

### <u>◯コンテナの停止</u>
```
＃ 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
```

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

## <u>■jupyterlabの共通コード(以下python3)</u>
以下のコードはjupyterlabのセルに入力する
### <u>◯共通ライブラリと日本語フォント</u>
```
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'
```

### <u>◯DB接続関数</u>
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
```
### <u>◯テーブル(ファイル)定義確認関数(DSPFFD相当)</u>
```
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 border="1" style="border-collapse:collapse;width:800px">
<tr><td></td><td>TABLE_SCHEMA</td><td>TABLE_NAME</td><td>TABLE_TEXT</td><td>TABLE_TYPE</td><td>TABLE_OWNER</td><td>LAST_ALTERED_TIMESTAMP</td></tr>
<tr><td>0</td><td>BKNLIBF</td><td>GMBKAF</td><td>物件マスター</td><td>P</td><td>EM</td><td>2024-06-04 18:31:50.821</td></tr>
</table>
件数 : 7,467<BR>
定義 : <BR>
<table border="1" style="border-collapse:collapse;width:800px">
<tr><td></td><td>TABLE_SCHEMA</td><td>TABLE_NAME</td><td>COLUMN_NAME</td><td>COLUMN_TEXT</td><td>DATA_TYPE</td><td>LENGTH
<tr><td>0</td><td>BKNLIBF</td><td>GMBKAF</td><td>BKA001</td><td>物件CD</td><td>CHAR</td><td>4</td></tr>
<tr><td>1</td><td>BKNLIBF</td><td>GMBKAF</td><td>BKA002</td><td>物件枝番</td><td>NUMERIC</td><td>2</td></tr>
<tr><td>2</td><td>BKNLIBF</td><td>GMBKAF</td><td>BKA003</td><td>戸数</td><td>NUMERIC</td><td>4</td></tr>
</table>
…

## <u>■juypterlabサンプルコード</u>
### <u>◯SQL実行</u>
＊count(＊)で件数、group byで集計等SQLを駆使して分析する  
＊件数が多い時はlimit {数字}で限定すると応答が早い  
```
conn = connect_dev()
query = """
SELECT ＊
  FROM {ライブラリ名}.{テーブル(ファイル)名}
"""
df = pd.read_sql(sql=query, con=conn)
display(df)
```

### <u>◯csv出力</u>
以下でcsvに出力し、jupyterの左メニューからDownloadできる  
漢字がUTF-8なので、Excelで直接開くならSJISに変換すること  
```
df.to_csv({csvファイル名})
```

### <u>◯テーブル(ファイル)一覧(以下SQLのみ掲載)</u>
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='{ライブラリ}'
"""
 ```

### <u>◯ソースファイル取得
　(DSPFD FILE({ターゲットライブラリ}/QCLSRC) TYPE(＊MBRLIST)相当)</u>
```
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}')
"""
```

### <u>◯オブジェクト名取得(DSPFD相当)</u>
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'))
"""
```

### <u>◯1年以内に利用されたプログラム名取得(DSPFD相当)</u>
```
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
"""
```

### <u>◯ユーザプロファイル取得(DSPUSRPRF相当)</u>
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
"""
```

### <u>◯ジョブログ取得(DSPJOBLOG相当)</u>
ジョブログは全実行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='{ライブラリ}'
"""
```