pimientitoの機械学習

「機械学習って何だろう。」から、はじまり、いまだ???の毎日。数学初心者、PG・DBアマチュアのサービス・エンジニアが、どこまで理解できるのか。

【前処理の学習-12】データに触れる(Windows環境編)②

前回から機械学習に使うデータに触れはじめました。

pimientito-handson-ml.hatenablog.com

残念ながら、いまの状態では分析に利用できそうもなく、もう少しデータの整理が必要になりそうです。

【今回の目標到達点】

前処理の学習の方向性を探る

【目次】

データ項目の見直し

あらためてデータ項目(カラム)一覧を見てみます。

現在使用しているデータは「地上気象観測時日別編集データ(CSV版)2000年」((財)気象業務支援センター 発行)です。

項目名 項目名 項目名
地点番号 蒸発量 昼間.概況3接続詞.RMK
管区コード 日降水量 昼間.概況3天気
観測回数 最大1時間降水量 昼間.概況3天気.RMK
観測項目 最大10分間降水量 昼間.概況4接続詞
地点名 降雪の深さの日合計 昼間.概況4接続詞.RMK
日最深積雪 昼間.概況4天気
未使用 昼間.概況4天気.RMK
未使用 夜間.概況1接続詞
平均現地気圧 大気現象1 夜間.概況1接続詞.RMK
平均海面気圧 大気現象2 夜間.概況1天気
最低海面気圧 大気現象3 夜間.概況1天気.RMK
平均気温 大気現象4 夜間.概況2接続詞
最高気温 大気現象5 夜間.概況2接続詞.RMK
最低気温 降水強風時間 夜間.概況2天気
平均蒸気圧 欠測処理1 夜間.概況2天気.RMK
平均相対湿度 欠測処理2 夜間.概況3接続詞
最小相対湿度 昼間.概況1接続詞 夜間.概況3接続詞.RMK
平均風速 昼間.概況1接続詞.RMK 夜間.概況3天気
最大風速 昼間.概況1天気 夜間.概況3天気.RMK
最大風速の風向 昼間.概況1天気.RMK 夜間.概況4接続詞
最大瞬間風速 昼間.概況2接続詞 夜間.概況4接続詞.RMK
最大瞬間の風向 昼間.概況2接続詞.RMK 夜間.概況4天気
平均雲量 昼間.概況2天気 夜間.概況4天気.RMK
日照時間 昼間.概況2天気.RMK
全天日射量 昼間.概況3接続詞


いままで、何度もご紹介してきた項目一覧。しかし、ここからどのように進めて良いのか、正直分かりません。

データ分析を行うために必要な基礎知識(視点)が、まるっきり無いため、ただ数字の羅列を眺めているばかりです。。。


書籍『前処理大全 』から学ぶ

自身にスキルが無いのであれば、各分野の諸先輩方のお知恵をお借りして学習するしか方法がありません。

本ブログ開始時にご紹介させていただきました本橋智光氏著「前処理大全 データ分析のためのSQL/R/Python実践テクニック」(技術評論社(以後「前処理大全」と表記)を参考に前処理について学んでいきます。



本書「前処理大全」のなかでもサンプルデータが使われていますが、本ブログでは、引続き「地上気象観測時日別編集データ」を使用いたします。そのため「前処理大全」の内容と、本ブログでご紹介する内容が、必ずしも一致するものではございません。


また本ブログは、ご紹介する書籍の書評するものではございません。あくまで「機械学習初心者」の学習の軌跡を綴っているものです。本ブログ読者のみなさまには、ご理解いただけますようお願いいたします。


なお今後、本「前処理大全」より引用した部分については、文章末尾にカッコ()で括った章番号や章のタイトルのみ表記することといたします。書籍名や著者名については、毎回ブログ記事先頭でご紹介することとして、記事内で繰返し表記することを割愛いたします。


では最初に学ぶのは「抽出」からです。書籍のなかでは、抽出について、このように述べています。

単純な作業に思えますが、適切な抽出作業は、無駄な処理を減らせたり、扱うデータサイズを小さくしたりすることができるため、重要です。

(参考・参照元:第2章「抽出」より抜粋)


また同章では、以下の4種類の抽出について説明されています。

  1. データ列を指定して抽出

  2. 条件指定によるデータ行の抽出

  3. データ値に基づかないサンプリング

  4. 集約IDに基づくサンプリング

(参考・参照元:第2章「抽出」より抜粋)

ひとつずつ、手を動かしながら抽出について学んでみましょう。


1. データ列を指定して抽出

書籍のなかでは、列(カラム)を指定して抽出することの利点について、このように説明されています。

必要な列のみに絞り込むことによって、1行あたりのデータサイズを減らし、後続のデータ分析をやりやすくすることが、データ列抽出の役割です。

(参考・参照元:第2章「抽出」2-1 「データ列指定による抽出」より抜粋)


著者は、データ列の選定をする際、氏名などの固有名詞やマスキングされて内容が特定できない列は、データの傾向を把握する分析には利用しづらい要素としてお話しされています。

また数字型に比べて、文字列型はデータサイズが大きくなる傾向にあるため、必要最小限の文字列型項目の抽出を勧めています。


「前処理大全」では、DB上から列を抽出するサンプルコードが紹介されています。本ブログでも、データや環境に合わせて再編したサンプルコードを記載いたします。

『学習の概要』

Pythonで列を抽出する場合、PandasのDataFrameの機能を利用する方法が簡単です。ただし、列の指定方法もさまざまなパターンが提供されており、可読性が高く、データの変更にも強いAwesomeなコードを実現するには、適切な指定方法を学ぶことが大事です。

(参考・参照元:第2章「抽出」2-1 「データ列指定による抽出」より抜粋)

『学習のポイント』

  • loc/iloc/ix関数の仕様の特性について学ぶ。

  • 抽出する列を指定する場合、列番号指定(iloc関数)より、列名指定(loc関数)の方がコードの可読性が良く、また列の追加/削除があった場合でも、列名で指定しているためコード修正の必要性が無い。


上記『学習のポイント』をもとにコーディングしてみました。

[Windows7][Python, PostgreSQL][jupyter notebook]

#【前処理の学習-12】データに触れる(Windows環境編)②

#!/usr/bin/python
# -*- coding: utf-8 -*-

import pandas as pd
import psycopg2
import sys

con = None
Tbl_Nm ='dly_edit_data_grnd_wthr_obsrv'
sql = 'SELECT * FROM ' + Tbl_Nm + ';'

try:
    con = psycopg2.connect("host='localhost' dbname='weather_data' user='pimientito' password='****'")
    df = pd.read_sql(sql,con)
        
except psycopg2.DatabaseError as e:
    print ('Error %s' %e)    
    sys.exit(1)

else:
    print('iloc関数による抽出')
    print(df.iloc[0:5, 0:4])
    print('')
    print('')
    print('loc関数による抽出')
    print(df.loc[0:5, ['point_number', 'province_code', 'number_of_observations', 'observation_item']])

finally:
    if con:
        con.close()
    print('')
    print('Processing has ended.')


iloc関数とloc関数のコーディング部分のみ切り出しました。

f:id:Pimientito:20180923163318p:plain

確かにメンテナンスするときには、loc関数で記載されている方が親切ですね。とても見やすいコードです。


実行結果は、以下の通りです。

f:id:Pimientito:20180923163518p:plain

表示内容は、iloc関数、loc関数とも同じですが、コーディングの視点からは、それぞれ特色がありました。


今回から、はじまりました新しい試み。書籍からの写経だけで終らず、少しずつでも学習内容を実用的に使えるよう進めて行きたいと思います。


今回は、以上です。




【参考資料】

本橋智光氏著「前処理大全 データ分析のためのSQL/R/Python実践テクニック」(技術評論社

loc関数

pandas.DataFrame.loc — pandas 0.23.4 documentation

iloc関数

pandas.DataFrame.iloc — pandas 0.23.4 documentation

ix関数

pandas.DataFrame.ix — pandas 0.23.4 documentation



【告知】 10/8 技術書典5へ出品いたしますっ!

まだはじめて間もない「機械学習」ですが、はてなブログ「Pimientitoの機械学習」で綴ってきた内容をブラッシュアップして一冊の同人誌にいたします。

技術書典5

techbookfest.org

サークルカット(出品場所:き01 サークル名:でーたちっぷす

f:id:Pimientito:20180916141126p:plain

同人誌『非理系ですが「機械学習」はじめました。』の表紙

f:id:Pimientito:20180916135737j:plain

同人誌『非理系ですが「機械学習」はじめました。』の目次

目次1

f:id:Pimientito:20180916140551p:plain

目次2

f:id:Pimientito:20180916140601p:plain

目次3

f:id:Pimientito:20180916140610p:plain

当日、お近くをお通りの際は、是非お立ち寄りください。よろしくお願いします。

【前処理の学習-11】データに触れる(Windows環境編)①

前回までの番外編では、PostgreSQLのインストールからDB構築までの工程を全7話で学習してきました。

pimientito-handson-ml.hatenablog.com

今回から、いよいよ「機械学習の前処理」について学習を進めていきます。

【今回の目標到達点】

DB化したデータに触れる。

【目次】

基本的なSQL文の実行・データ確認(psql

前回の番外編3では、DB「weather_data」を作成し、そこへ(財)気象業務支援センター発行の「地上気象観測時日別編集データ(CSV版)2000年」のデータを入力いたしました。


はじめにSQL Shell「psql」で基本的な問合せを行ってデータに触れてみます。

下記の図では、psqlコマンド\dを実行して、ユーザテーブルリストを表示しています。(「dly_edit_data_grnd_wthr_obsrv」が番外編3で作成したテーブル名)

f:id:Pimientito:20180916142054p:plain

次にDBテーブル内に、どれくらいのデータが登録されているのか、データ行数を数えてみます。

f:id:Pimientito:20180916122813p:plain

56,730行。

本格的なデータ分析で扱うには、少ないデータ量とは思いますが、初心者が始めるには、良いデータ量ではないでしょうか。今後、機械学習を学ぶなかで、いろいろなオープンデータにも触れていきたいと考えています。


続いて、今回使用しているデータには、気象観測所の拠点地域名が入っているため、何カ所の拠点が、このデータに含まれているのか確認してみます。

f:id:Pimientito:20180916123606p:plain

155拠点。

実際に、拠点名も表示してみましょう。

f:id:Pimientito:20180916123758p:plain

155拠点名が、アイウエオ順で表示できました。


最後にselect * from dly_edit_data_grnd_wthr_obsrv;を実行してみます。

f:id:Pimientito:20180916124735p:plain


f:id:Pimientito:20180916125419p:plain

コマンドを実行する前に、薄々とは気付いていましたが、カラム数(項目数)が約70項目、データ行数が56,730行。狭い画面上では表示が難しく、また表示が崩れてしまっています。

データ分析の内容によって、SQLの条件式や副問合せを使って、カラムやデータ行の取捨選択を行う必要がありそうです。

次回から、あらためてデータの内容を確認し、より効率的な機械学習の前処理について学んでいきます。

今回は、以上です。




【告知】 技術書典5へ出品いたしますっ!

まだはじめて間もない「機械学習」ですが、はてなブログ「Pimientitoの機械学習」で綴ってきた内容をブラッシュアップして一冊の同人誌にいたします。

技術書典5

techbookfest.org

サークルカット(出店場所:き01 サークル名:でーたちっぷす)

f:id:Pimientito:20180916141126p:plain

同人誌『非理系ですが「機械学習」はじめました。』の表紙

f:id:Pimientito:20180916135737j:plain

同人誌『非理系ですが「機械学習」はじめました。』の目次

目次1

f:id:Pimientito:20180916140551p:plain

目次2

f:id:Pimientito:20180916140601p:plain

目次3

f:id:Pimientito:20180916140610p:plain

当日、お近くをお通りの際は、是非お立ち寄りください。

【前処理の学習-番外編3-⑦】PostgreSQL 管理Tool「pgAdmin」によるDB管理(Windows環境編)

前回は、PostgreSQL管理ツール「pgAdmin」を使用して、データベースのテーブルを作成しました。

pimientito-handson-ml.hatenablog.com

今回は作成したテーブルへ、実際にデータを入れていきます。

【今回の目標到達点】

(財)気象業務支援センター発行の「地上気象観測時日別編集データ(CSV版)2000年」のデータをDBテーブルへ挿入

【もくじ】

CSVファイルの取込み

今回作成したコードの概要をフロー図にいたしました。

f:id:Pimientito:20180909195303p:plain


大きく分けて、以下の二つの処理を行います。

  • 指定したディレクトリ(フォルダ)配下にあるサブディレクトリ(サブフォルダ)を含んだすべてのディレクトリからCSVファイル(dxxxxxxx.csv)を検索する。

  • 見つけたCSVファイル(dxxxxxxx.csv)から項目名が入った先頭二行のみ除外した実データ行のみをDBテーブルへ挿入する。


今回、Python上でPostgreSQLの操作を行うためPython DB AIP「Psycopg」を利用しました。このAPIは事前にpipコマンドなどでインストールする必要があります。

私はWindows環境のためANACONDA NAVIGATORの「Environments」画面からインストールいたしました。画面から「psycopg2」を検索してインストールしてください。

f:id:Pimientito:20180909220854p:plain


以下は、実際のコードです。

[Windows7][Python][Jupyter Notebook]

#【前処理の学習-番外編3-⑦】データ挿入(INSERT)編

#!/usr/bin/python
# -*- coding: utf-8 -*-

import sys
import csv
from pathlib import Path

import psycopg2

con = None
Tbl_Nm ='dly_edit_data_grnd_wthr_obsrv'

try:
    con = psycopg2.connect("host='localhost' dbname='weather_data' user='pimientito' password='****'")
    cur = con.cursor()

    p = Path('Z:\ML\SDP00')
    for x in list(p.glob('**/d*.csv')):
        if x:
            with open(x,newline='',encoding='SHIFT-JIS') as csvDtFl:
                csvDts = csv.reader(csvDtFl)

                i = 0
                for row in csvDts:
                    if i>=2:
                        sql = 'INSERT INTO ' + Tbl_Nm  + ' VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
                        cur.execute(sql,(row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9],row[10],row[11],row[12],row[13],row[14],row[15],row[16],row[17],row[18],row[19],row[20],row[21],row[22],row[23],row[24],row[25],row[26],row[27],row[28],row[29],row[30],row[31],row[32],row[33],row[34],row[35],row[36],row[37],row[38],row[39],row[40],row[41],row[42],row[43],row[44],row[45],row[46],row[47],row[48],row[49],row[50],row[51],row[52],row[53],row[54],row[55],row[56],row[57],row[58],row[59],row[60],row[61],row[62],row[63],row[64],row[65],row[66],row[67],row[68],row[69],row[70],row[71],row[72],row[73]))

                        con.commit()

                    i = i + 1
        else:
            break    

    print('DBへのデータ取込み処理が正常に完了しました.')

except psycopg2.DatabaseError as e:
    if con:
        con.rollback()
        
    print ('Error %s' %e)
    sys.exit(1)

finally:
    if con:
        con.close()


取込みデータの確認

次に取込んだデータが、ちゃんとDBテーブル「dly_edit_data_grnd_wthr_obsrv」に保存されているか確認いたします。

はじめにpsqlを利用して確認いたします。

[Windows7][SQL][psql]

f:id:Pimientito:20180909223149p:plain

psqlでは、SELECT COUNT(*) FROM dly_edit_data_grnd_wthr_obsrv;として取込んだデータの行数を表示いたしました。

続いてJupyter Notebookで、取込んだデータをSELECT文で表示するコードを作成いたしました。

[Windows7][Python][Jupyter Notebook]

#【前処理の学習-番外編3-⑦】 データ操作(SELECT文)編

#!/usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2
import sys

con = None
Tbl_Nm ='dly_edit_data_grnd_wthr_obsrv'

try:
    con = psycopg2.connect("host='localhost' dbname='weather_data' user='pimientito' password='****'")
    cur = con.cursor()

    sql = 'SELECT * FROM ' + Tbl_Nm + ';'
    cur.execute(sql)
    
    while True:
        row = cur.fetchone()
        
        if row == None:
            break
            
        print(row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9],row[10],row[11],row[12],row[13],row[14],row[15],row[16],row[17],row[18],row[19],row[20],row[21],row[22],row[23],row[24],row[25],row[26],row[27],row[28],row[29],row[30],row[31],row[32],row[33],row[34],row[35],row[36],row[37],row[38],row[39],row[40],row[41],row[42],row[43],row[44],row[45],row[46],row[47],row[48],row[49],row[50],row[51],row[52],row[53],row[54],row[55],row[56],row[57],row[58],row[59],row[60],row[61],row[62],row[63],row[64],row[65],row[66],row[67],row[68],row[69],row[70],row[71],row[72],row[73])
    
    print('処理が正常に完了しました.')
        
except psycopg2.DatabaseError as e:
    print ('Error %s' %e)    
    sys.exit(1)

finally:
    if con:
        con.close()

こちらのコードの実行結果からも、正常にCSVデータが取込まれていることが確認できました。

DB内データの加工(SQL文副問合せなどの利用や、すべてのデータをCSVファイルへ一括出力など)については、また別の機会に取り組みます。

8月初旬からはじまった本「番外編③」の本来の目的である「pgAdmin」を利用したDB構築と、複数ファイルに分かれたCSVファイルデータの取込みは、今回で完了いたしました。


次回から、約一か月ぶりに本編へ戻り、機械学習の前処理の続きを進めたいと思います。

今回は、以上です。




【参考資料】

postgreSQL python-API「psycopg」

http://initd.org/psycopg/

Python Tutorials

https://pythonspot.com/

Python Database

https://pythonspot.com/en/python-database/

「あずみ.net」

https://a-zumi.net/python-insert-csv/

【前処理の学習-番外編3-⑥】PostgreSQL 管理Tool「pgAdmin」によるDB管理(Windows環境編)

前回は、PostgreSQL管理ツール「pgAdmin」の操作から少し離れて、データベースのテーブルデザインを行いました。

pimientito-handson-ml.hatenablog.com

今回は作成したデザインをもとに、pgAdminでDBテーブルを作成いたします。

【今回の目標到達点】

(財)気象業務支援センター発行の「地上気象観測時日別編集データ(CSV版)2000年」のデータ用DBテーブルを作成

【もくじ】


DBテーブルデザインの確認

DBテーブルを作成する前に、前回作成したテーブルデザインの資料をもとに定義内容を再確認いたします。

f:id:Pimientito:20180830224823p:plain


  • テーブルカラム名(項目名)は「日本語名称」を「英語名称」に変換し、その後、意味が不明にならない程度に母音を抜いて「略称」を作成いたしました。

  • 型は、PythonのPandasライブラリからDataFrame.info()関数を使って調べた概要と、実際に見たデータの内容をもとに、過剰に大きな型にならないよう気を付けました。

  • 主キー(Primary Key)は、複合主キー(Composite Primary Key)とし、選択した項目は「(観測)地点番号」「(観測)年」「(観測)月」「(観測)日」といたしました。(※1)

(※1)今回使用するデータのCSVファイルは、観測拠点ごとに1カ月単位で分かれており、データレコード一行が、一日分のデータを表しています。


なお今回は、以下の基準(ルール)でテーブル作成を進めました。

データの正規化は行わない(非正規形)

主な理由として、今回使用している気象データは既に整備されており、またデータ解析やDBに不慣れな自分が、データを無駄に分割してテーブルを増やすことは得策ではないと思い、生のデータをそのまま使用することにいたしました。


DBテーブル作成

はじめにテーブル作成のためにテーブル作成ダイアログを表示させます。

ダイアログの表示方法は、ツリーコントロールから[weather_data](①) > [スキーマ](②) > [テーブル](③)を選択した状態で、メニューバーの[オブジェクト] > [作成] > [テーブル](④)をクリックします。

f:id:Pimientito:20180831015910p:plain


表示されたダイアログの「一般」タブを開き、基本設定を行います。

f:id:Pimientito:20180831022347p:plain

項目名 設定値
名称 Dly_Edit_Data_Grnd_Wthr_Obsrv(※1)
所有者 pimientito
スキーマ public
テーブル空間 pg_default
パーティションテーブル No(※2)
コメント ※記載なし

(※1) Daily edition data on ground weather observation(地上気象観測時日別編集データ)の略称

(※2) 機能を理解していないため、初期値の"No"に設定


続いて「カラム」タブを開き、前回作成したテーブルデザインを参考に、各カラム(項目)の設定を行います。

f:id:Pimientito:20180831020658p:plain


SQL」タブを開くと、設定内容がSQL文として生成されていることが確認できます。

f:id:Pimientito:20180831020713p:plain

ここでワンポイント!

設定するカラム数が多くなると、設定や登録漏れの可能性が増えると思います。私の場合、元データとの突合せを節目節目で行います。今回の場合を例にいたしますと。。。

① 「SQL」タブのカラム名(項目名)のSQL文をコピーして。。。

f:id:Pimientito:20180831020743p:plain


テキストエディタに貼り付けて、不要な文字列を削除するなど、データを整理します。

f:id:Pimientito:20180831020759p:plain


③ 最初に作成したテーブルデザインに併記して比較します。今回はExcelで作成いたしましたので「SQL」タブからコピーしたカラム名(項目名)を、元データの横に転記してEXACT関数で、差分が無いことを確認いたしました。

f:id:Pimientito:20180831020817p:plain

---「ここでワンポイント!」終わり ---

最後にダイアログの「保存」ボタンを押下いたします。

f:id:Pimientito:20180831024956p:plain


ツリーコントロールから、新規作成したDBテーブルを確認することができます。

f:id:Pimientito:20180831025402p:plain


これで、やっとデータベースの大枠が完成いたしましたっ!


いよいよ、次回で番外編3が完結(予定)です。

次回では、階層化されたフォルダに保存されている複数のCSVファイルのデータを、どのように読込んでDBテーブルへ出力するのか。

SQL単独で処理できるのか、それともVBAマクロを使用するのか、はたまたPythonで?いろいろと調べて参ります。


今回は、以上です。



【参考資料】

なし

【前処理の学習-番外編3-⑤】PostgreSQL 管理Tool「pgAdmin」によるDB管理(Windows環境編)

前回は、PostgreSQL管理ツール「pgAdmin」を使って新しいDBを作成いたしました。

pimientito-handson-ml.hatenablog.com

今回は使用するデータの内容を確認し、テーブルデザインを作成いたします。

【今回の目標到達点】

テーブルデザインの作成

【もくじ】

データの確認

ブログ本編で使用しているデータ「地上気象観測時日別編集データ(CSV版)2000年」((財)気象業務支援センター 発行)をもとに、テーブルデザインを行います。


過去記事「【前処理の学習-9】データ読込み~下準備(Windows環境編)④~」では、大まかにデータの項目について調べました。

pimientito-handson-ml.hatenablog.com


DBテーブルを作成するにあたり、最低限知る必要があることは各項目の「データ型」についてです。PythonのPandasライブラリからDataFrame.info()関数を使いデータの概要を調べます。

f:id:Pimientito:20180820001359p:plain

表示された結果を、おおまかに見たところデータ型の大半が「int64」と表示されています。Pandasのドキュメントを確認し「int64」は、Integer型同等ということが分かりました。


あらためてデータ型についてPostgreSQLのドキュメントでも確認いたしました。

数値型

名称 別名 範囲
smallint int2 -32,768~+32,767
integer int/int4 -2,147,483,648
~+2,147,483,647
bigint int8 -9,223,372,036,854,775,808
~+9,223,372,036,854,775,807

文字(列)型

名称 別名 概要
character[(n)](※1,2) char[(n)] 固定長文字列
character varying[(n)](※3) varchar[(n)] 可変長文字列

(※1) (n)を指定しない場合、1が指定されます。

(※2) (n)に指定した値より文字列が短い場合、不足文字数分の空白が埋められます。

(※3) (n)を指定しない場合、可変長となります。


「integer」型の範囲を見ると、かなり広い範囲まで表現できます。果たして今回使用するデータで、そこまで大きい値が必要なのでしょうか。先ほどPythonで表示したデータを、今度はテキストエディタで開いて内容を確認いたします。

f:id:Pimientito:20180820005851p:plain

大きい数値が入っている項目は、2~3個程度でした。またPythonで「object」型として表示されていた項目のデータも確認したところ、半角カタカナで土地名が入っていました。

この確認によって、今回使用するデータ型は「int2」「int4」「char」「varchar」で足りることが分かりました。


テーブルデザインの作成

pgAdmin上でDBテーブルを作成する前に、頭にイメージしているテーブルデザインをExcelを使って具体的な形にいたしました。

主な内容は、以下の通りです。

  1. データ項目を一覧にまとめます。

  2. 日本語の項目名を英語表記に変換いたします。(※1, 2)

  3. 英語の項目名をSQL問合せで利用しやすいよう短い略称を考えます。(※3)

  4. 項目ごとにデータ型や制約(NOT NULLなど)を決めます。

  5. Primary Keyを決めます。


(※1) SQL問合せで日本語を使用しないために英語表記に置換えました。

(※2) Google翻訳などを利用すると、簡単に英語名に変換できます。

(※3) 略称の作成基準はありませんが、母音のアルファベットを抜くなどして名称を短くいたしました。


実際に作成したExcelファイルは、以下の通りです。

f:id:Pimientito:20180820014447p:plain


考えながらDBテーブルを作成するより、事前にこのような資料を作成しておくと作業が円滑に進み、また再確認する場合も、容易に見直すことができます。


次回は、このテーブルデザインを用いて、pgAdminからDBテーブルを作成いたします。

今回は、以上です。



【参考資料】

PostgreSQL 10.4文書「第8章 データ型」 https://www.postgresql.jp/document/10/html/datatype.html

pandas 0.23.4 documentation「IO Tools(Text, CSV, HDF5, ...)」 http://pandas.pydata.org/pandas-docs/stable/io.html#specifying-column-data-types

【前処理の学習-番外編3-④】PostgreSQL 管理Tool「pgAdmin」によるDB管理(Windows環境編)

前回は、PostgreSQLの大まかな構成を調べてみました。

pimientito-handson-ml.hatenablog.com

今回から具体的にDBを作成していきます。

【今回の目標到達点】

pgAdminを使ってDBを新規作成する。

【もくじ】


DB設定項目の系統図

はじめにDBを作成するときの設定項目を、おおまかに理解するため項目の系統図を書いてみました。

ざっと見渡してみても、いろいろと設定する必要があり、ひとつひとつ理解するためには、長い時間がかかりそうです。

今回は、ブログ「pimientitoの機械学習」本編で必要な項目のみ設定してDBを作成いたします。


f:id:Pimientito:20180811130348j:plain


DB新規作成

メニューバーから[オブジェクト] > [作成] > [データベース]を選択して押下します。

f:id:Pimientito:20180811140844j:plain

表示された「作成 - データベース」画面から各種設定を行っていきます。


「一般」タブの設定

「一般」タブでは、"データベース名"と"所有者"を設定いたします。

f:id:Pimientito:20180811141201j:plain

今回の設定内容

項目名 設定値
データベース weather_data
所有者 pimientito
コメント ※今回は記載していません

現在、ブログ本編で気象データを使用しているため、今回作成するデータベース名を「weather_data」といたしました。

所有者は、前々回【前処理の学習-番外編3-②】で作成したスーパーユーザ「pimientito」(赤枠内)を選択いたします。


「定義」タブの設定

「定義」タブでは、新規作成するDBの基本的な仕様を設定いたします。

f:id:Pimientito:20180813163843p:plain

今回の設定内容

項目名 設定値
エンコーディング UTF8(※1)
テンプレート template1(※2)
テーブル空間 pg_default
照合順序 Japanese_Japan.932(※3)
文字の分類 Japanese_Japan.932(※3)
接続制限 -1(※4)

(※1) 初期値はUTF8。今回使用するデータの文字コードUTF-8のため、初期値のままに設定いたします。

(※2) マニュアル(pgAdmin4.3.2 documentation「Database Dialog」)では、指定するテンプレートが無い場合「template1」を選択することを推奨しています。

(※3) 本記事後半「補足「定義」タブ内の各種設定値について」に選択理由を記載しています。

(※4) 「-1」は無制限を表現します。


「セキュリティ」タブの設定

「セキュリティ」タブでは、「権限」でアクセス権限を定義し「セキュリティラベル」では、オブジェクトに適用するセキュリティラベルを定義いたします。

なお「セキュリティラベル」は、SE-Linuxの強制アクセス制御機能に関するもので、今回作成するDBはローカル内での使用と限定しているため、特に設定は行いません。

定義する場合、項目欄右側にある「+」マークを押下してください。

f:id:Pimientito:20180813190752p:plain

今回の設定内容(権限)

受領者 権限 権限 付与者
pimientito ALL WITH GRANT OPTION(※1) postgres
CREATE WITH GRANT OPTION
TEMPORARY(※2) WITH GRANT OPTION
CONNECT(※3) WITH GRANT OPTION

(※1) 「WITH GRANT OPTION」を付与されることで受領者は、第三者に同じ権限を付与することができます。

(※2) TEMPORARY TABLE(一時テーブル)を作成することを許可します。TEMPORARY TABLEは、セッションまたはトランザクションが終了すると自動的に削除されます。

(※3) 指定されたDBに接続することができます。

今回の設定内容(セキュリティラベル)

プロバイダ セキュリティラベル
- -

SE-Linuxの強制アクセス制御に関する機能のため、今回は設定いたしません。


「パラメータ」タブの設定

「パラメータ」タブで設定する値のほとんどがpostgresql.confファイル内の値であり、すべての設定値を理解・把握するには、あまりに広範囲過ぎるため、今回の記事では割愛させていただきました。

f:id:Pimientito:20180813191023p:plain

今回の設定内容

名称 ロール
- - -


SQL」タブの確認

SQL」タブでは、各種設定がSQL文として生成されていることを確認できます。

f:id:Pimientito:20180813231925p:plain

生成されたSQL文を確認後、画面下部の「保存」ボタンを押下すると、DBが新規作成されます。 画面上では「postgres」DBの下に、新たに「weather_data」DBが表示されていることを確認できます。

f:id:Pimientito:20180813232832p:plain


以上で、DBの新規作成の手順は完了です。



補足「定義」タブの各種設定について

今回DBを新規作成するにあたり「定義」タブの設定項目には、非常に悩まされました。

設定に至るまでの過程で、さまざまな資料を確認した際に得た情報の一部を、大まかではありますが下記に記載いたします。

なお、より詳細についてご興味のある方は、本ブログ記事末尾の【参考資料】に各資料へのリンクを記載しておりますのでご確認ください。


エンコーディング」設定値一覧

こちらは、DB上の文字セットの選択肢です。一覧ではリスト内の選択肢名と言語名を紐付けています。

マニュアル「PostgreSQL 10.4文書 第23章 多言語対応 23.3.1 サポートされる文字セット」では、下記の一覧に加えて、各文字セットのPostgreSQLサーバでの利用可否や、ICU(International Components for Unicode)ライブラリのサポート適用有無についても言及されています。

選択肢名 文字コード名 言語名
BIG5 Big Five 繁体字
EUC_CN Extended UNIX Code-CN 簡体字
EUC_JP Extended UNIX Code-JP 日本語
EUC_JIS_2004 Extended UNIX Code-JP
JIS X 0213
日本語
EUC_KR Extended UNIX Code-KR 韓国語
EUC_TW Extended UNIX Code-TW 繁体字/台湾語
GB18030 National Standard 中国語
GBK Extended National Standard 簡体字
ISO_8859_5 ISO 8859-5/ECMA 113 ラテン/キリル
ISO_8859_6 ISO 8859-6/ECMA 114 ラテン/アラビア語
ISO_8859_7 ISO 8859-7/ECMA 118 ラテン/ギリシャ語
ISO_8859_8 ISO 8859-8/ECMA 121 ラテン/ヘブライ語
JOHAB JOHAB 韓国語(ハングル)
KOI8R KOI8-R キリル文字(ロシア)
KOI8U KOI8-U キリル文字(ウクライナ)
LATIN1 ISO 8859-1/ECMA 94 西ヨーロッパ
LATIN2 ISO 8859-2/ECMA 94 中央ヨーロッパ
LATIN3 ISO 8859-3/ECMA 94 南ヨーロッパ
LATIN4 ISO 8859-4/ECMA 94 北ヨーロッパ
LATIN5 ISO 8859-9/ECMA 128 トルコ
LATIN6 ISO 8859-10/ECMA 144 北欧
LATIN7 ISO 8859-13 バルト派
LATIN8 ISO 8859-14 ケルト
LATIN9 ISO 8859-15 LATIN1でヨーロッパと訛りを含む
LATIN10 ISO 8859-16/ASRO SR 14111 ルーマニア
MULE_INTERNAL Mule internal code 他言語Emacs
SJIS Shift JIS 日本語
SHIFT_JIS_2004 Shift JIS/JIS X 0213 日本語
SQL_ASCII 未指定(※1) 何でも
UHC 統合ハングルコード 韓国語
UTF8 Unicode/8-bit すべて
WIN866 Windows CP866 キリル文字
WIN874 Windows CP874 タイ語
WIN1250 Windows CP1250 中央ヨーロッパ
WIN1251 Windows CP1251 キリル文字
WIN1252 Windows CP1252 西ヨーロッパ
WIN1253 Windows CP1253 ギリシャ
WIN1254 Windows CP1254 トルコ
WIN1255 Windows CP1255 ヘブライ
WIN1256 Windows CP1256 アラビア語
WIN1257 Windows CP1257 バルド語派
WIN1258 Windows CP1258 ベトナム語

(※1)

SQL_ASCIIの設定は、他の設定とかなり異なります。サーバのキャラクタセットSQL_ASCIIのとき、サーバは0から127のバイト値をASCIIに変換します。一方、128から255までは変換されません。 設定がSQL_ASCIIの場合は、符号化は実行されません。よって、この設定は特定の符号化を使用している場合には、その符号化を無視するようになってしまいます。

PostgreSQL 10.4文書「23.3.1 サポートされる文字セット」より抜粋


「テンプレート」設定一覧

次にテンプレートの選択肢です。マニュアルによると新規DBを作成する場合、基本的には「template1」を選択することを勧めています。

「template1」は「template0」を複製したもので、そこへ新たにオブジェクト(テーブルや手続き言語など)を追加して保存することで「template0」から拡張された「template1」が作成されます。

「template0」は、PostgreSQL DBの標準仕様の原型であり、複製して利用します。例えばWindows環境にインストールしたPostgreSQLの「template1」の設定パラメータを確認してみます。

f:id:Pimientito:20180813130143p:plain

「setting」の値(ロケール)が"Japanese_Japan.932"となっていますが、もしこの値を別の値(CやPOSIX)でDBを作成したい場合は、テンプレート「template0」を指定して新しいテンプレートを作成する必要があります。


テンプレート名 概要
postgres PostgreSQLデータベースサーバ
template0 テンプレート原型(DB標準オブジェクト群のみ)
template1 template0をベースに用途に合わせたオブジェクトを追加し拡張した雛形


「テーブル空間」設定一覧

テーブル空間とは、DBオブジェクトを表すファイルを格納するためのファイルシステム上の場所を指します。テーブル空間を新規に作成していない場合、PostgreSQLをインストールした際に作成された「pg_default」のみとなります。

テーブル空間名
pg_default


「照合順序」設定一覧

「照合順序」とは、文字列の並び替え順を管理しています。

照合順序名 概要
C 標準C( ISO C ) /POSIX類似仕様
POSIX POSIX正規表現
Japanese_Japan.932 SJIS


「文字の分類」設定一覧

「文字の分類」とは、文字の分類や大文字や小文字の変換などを管理しています。

分類 概要
C 標準C( ISO C ) /POSIX類似仕様
POSIX POSIX正規表現
Japanese_Japan.932 SJIS


「照合順序」と「文字の分類」の設定値選択基準

「定義」タブの「照合順序」と「文字の分類」については、設定値の選択基準が、とても複雑で、なかなか理解することができませんでした。

また「照合順序」と「文字の分類」の定義は、DB作成後、変更する事ができないため、安易に選択することができません。

今回は下記の情報をもとに、双方ともに「Japanese_Japan.932(SJIS)」を選択することにいたしました。


日本語を扱う場合、サーバのエンコーディングは UTF8 または EUC_JP になると思いますが、日本語ロケールを使う場合には 表1 の組み合わせを使う必要があります。


表1:エンコーディングと日本語ロケールの対応表

エンコーディング POSIX Windows
UTF8 ja_JP.utf8 Japanese_Japan.932(※)
EUC_JP ja_JP.eucJP Japanese_Japan.932

Windows で UTF8 エンコーディングを使う場合のみ、ロケールエンコーディングにコードページ 932 (SJIS) を使うことができます。これは Windows の C ライブラリが UTF8 でのロケール処理をサポートしていないためで、PostgreSQL 側で専用の対応を行っています。

Let's Postgres 「ロケール(国際化と地域化) 」より抜粋


以上が、DBを新規作成する際に得た情報となります。

特に「照合順序」と「文字の分類」の設定値に関しては、DBの使用環境も含めて検討しないで設定すると、文字の表示順序や手続き言語の利用時に思わぬエラーに遭遇することがあるようで、今回の学習だけでは把握しきれないものでした。

今後、ブログを続けていくなかで、今回作成したDBで想定外の不具合に遭遇したとき、あらためて今回学んだ内容を見直したいと考えています。


次回は、DBテーブルの設計と作成について学習していきます。

今回は、以上です。



【参考資料】

PostgreSQL 10 documentation「23.3.1 Supported Character Sets」(英文)

https://www.postgresql.org/docs/current/static/multibyte.html#MULTIBYTE-CHARSET-SUPPORTED

PostgreSQL 10.4文書「19.1 パラメータの設定」

https://www.postgresql.jp/document/10/html/config-setting.html#CONFIG-SETTING-NAMES-VALUES

PostgreSQL 10.4文書「23.1 ロケールのサポート」

https://www.postgresql.jp/document/10/html/locale.html

PostgreSQL 10.4文書「23.3.1 サポートされる文字セット」

https://www.postgresql.jp/document/10/html/multibyte.html#CHARSET-TABLE

PostgreSQL 10.4文書「SQLコマンド CREATE DATABASE」

https://www.postgresql.jp/document/10/html/sql-createdatabase.html

Let's Postgres「ロケール(国際化と地域化)」

https://lets.postgresql.jp/documents/technical/text-processing/2

Let's Postgres「PostgreSQL 9.1 の新機能 SE-PostgreSQLの統合」

https://lets.postgresql.jp/documents/technical/9.1/1

pgAdmin4.3.2「Managing Cluster Level Objects - Database Dialog -」

https://www.pgadmin.org/docs/pgadmin4/dev/database_dialog.html

ORACLE「国際化対応言語環境の利用ガイド - Cロケール -」

https://docs.oracle.com/cd/E26924_01/html/E27144/glmbx.html

ICU - International Components for Unicode(英文)

http://site.icu-project.org/home#TOC-What-is-ICU-

WikipediaPOSIX

https://ja.wikipedia.org/wiki/POSIX

【前処理の学習-番外編3-③】PostgreSQL 管理Tool「pgAdmin」によるDB管理(Windows環境編)

前回は、PostgreSQL管理ツール「pgAdmin」で、新しいスーパーユーザ「pimientito」を作成いたしました。

pimientito-handson-ml.hatenablog.com

今回は、PostgreSQLを通してデータベース(以後、DBと表記)の構成について学びます。

【今回の目標到達点】

PostgreSQLのサーバ機能とDB機能の概要を把握する。

【もくじ】


まずインストール直後のPostgreSQLでDBの基本構成を調べます。

PostgreSQL 基本構成

ツリーコントロールで確認するとPostgreSQLは、大きく分けて「サーバ」と「DB」で構成されています。

PostgreSQLの構成を理解するため、便宜上、この章では「PostgreSQL Server」と「PostgreSQL DB」と分けて呼称いたします。

またDBサーバとDBとの関係性をイメージしやすいよう、以下のような図を作成いたしました。

【DBサーバとDBの関係概要図】

f:id:Pimientito:20180808224100j:plain


ではひとつずつ見ていきましょう。

PostgreSQL Server

サーバとしての「PostgreSQL 10」は、以下のような構成になっています。

f:id:Pimientito:20180808010546p:plain

構成要素と概要は、以下の通りです。

構成要素名 概要
テーブル空間 データベースオブジェクトファイルを管理するファイルシステムの所在
データベース データベースオブジェクト(テーブルや関数など)の集合体
ログイン/グループロール データベースオブジェクトに対する権限を管理


インストール直後では「PostgreSQL」というサーバがひとつ存在するだけですが、新たなサーバを作成する場合、ツリーコントロールの「Servers」を右クリックし、プルダウンメニューから[作成] > [サーバ]または[サーバグループ]を選択します。

f:id:Pimientito:20180808225943j:plain

なお本編では、新規サーバを構築する説明は割愛いたします。


続いてDBについて調べていきます。

PostgreSQL DB

DB(DBオブジェクト群)としての「PostgreSQL」は、以下のような構成になっています。

f:id:Pimientito:20180808230458j:plain

構成要素と概要は、以下の通りです。

構成要素名 概要
DB名 ※上記画面では"postgres"と表記
FDW Foreign Data Wrapperの略
外部SQLサーバのデータにアクセスする機能
イベントトリガ 関連付けられたイベント発生時の所作を定義する機能
カタログ DBオブジェクトのあらゆる情報(関数、属性、データ型や
依存関係など)をタイプごとに管理する機能
キャスト 型キャストを関数に関連付け定義する機能
スキーマ 関係(表)と属性(フィールド)の関連を定義する機能
手続き言語 SQLC言語以外の言語(PL/pgSQLやPL/Pythonなど)で
ユーザ定義の関数を作成する機能
拡張 関連するDBオブジェクトを拡張パッケージ化する機能


インストール直後では「postgres」というDBがひとつ存在するだけですが、新たなDBを作成する場合、ツリーコントロールの「データベース」を右クリックし、プルダウンメニューから[作成] > [データベース]を選択します。

f:id:Pimientito:20180809015136j:plain



おおまかですが、今回はpgAdminのツリーコントロールから「Server」と「DB」の構成を学びました。

次回からは、実際にDBを新規作成し、テーブル設計や定義、データの流し込みへと学習を進めていきます。

なかなか本編の「機械学習」へ戻ることができませんが、この番外編に、いましばらくお付き合いください。


今回は、以上です。



【参考資料】

PostgreSQL 10.4文書

https://www.postgresql.jp/document/10/html/index.html

PostgreSQL wiki

https://wiki.postgresql.org/wiki/Main_Page

スキーマ (データベース) - Wikipedia