TensorFlow IO から PostgreSQL データベースを読み取る

TensorFlow.orgで表示

Google Colab で実行

GitHub でソースを表示{

ノートブックをダウンロード/a0}

概要

このチュートリアルでは、トレーニングまたは推論のために PostgreSQL データベースサーバーからtf.data.Datasetを作成し、作成したDatasetをtf.kerasに渡す方法を紹介します。

SQL データベースは、データサイエンティストにとって重要なデータソースです。最も人気のあるオープンソース SQL データベースの 1 つである PostgreSQL は、企業全体の重要なデータやトランザクションデータを格納するために広く使用されています。PostgreSQL データベースサーバーから直接Datasetを作成し、トレーニングまたは推論のためにDatasetをtf.kerasに渡すと、データパイプラインを大幅に簡略化されるのでデータサイエンティストは機械学習モデルの構築に専念できます。

セットアップと使用法

必要な tensorflow-io パッケージをインストールし、ランタイムを再起動する

try:
  %tensorflow_version 2.x
except Exception:
  pass

!pip install -q tensorflow-io

PostgreSQL のインストールとセットアップ (オプション)

注: このノートブックは、Google Colab でのみ実行するように設計されています。システムにパッケージをインストールし、sudo アクセスが必要です。ローカルの Jupyter ノートブックで実行する場合は、注意して続行してください。

Google Colab での使用法をデモするには、PostgreSQL サーバーをインストールします。パスワードと空のデータベースも必要です。

このノートブックを Google Colab で実行していない場合、または既存のデータベースを使用する場合は、次の設定をスキップして次のセクションに進んでください。

# Install postgresql server
sudo apt-get -y -qq update
sudo apt-get -y -qq install postgresql
sudo service postgresql start

# Setup a password `postgres` for username `postgres`
sudo -u postgres psql -U postgres -c "ALTER USER postgres PASSWORD 'postgres';"

# Setup a database with name `tfio_demo` to be used
sudo -u postgres psql -U postgres -c 'DROP DATABASE IF EXISTS tfio_demo;'
sudo -u postgres psql -U postgres -c 'CREATE DATABASE tfio_demo;'

Preconfiguring packages ...
Selecting previously unselected package libpq5:amd64.
(Reading database ... 243066 files and directories currently installed.)
Preparing to unpack .../0-libpq5_10.15-0ubuntu0.18.04.1_amd64.deb ...
Unpacking libpq5:amd64 (10.15-0ubuntu0.18.04.1) ...
Selecting previously unselected package postgresql-client-common.
Preparing to unpack .../1-postgresql-client-common_190ubuntu0.1_all.deb ...
Unpacking postgresql-client-common (190ubuntu0.1) ...
Selecting previously unselected package postgresql-client-10.
Preparing to unpack .../2-postgresql-client-10_10.15-0ubuntu0.18.04.1_amd64.deb ...
Unpacking postgresql-client-10 (10.15-0ubuntu0.18.04.1) ...
Selecting previously unselected package ssl-cert.
Preparing to unpack .../3-ssl-cert_1.0.39_all.deb ...
Unpacking ssl-cert (1.0.39) ...
Selecting previously unselected package postgresql-common.
Preparing to unpack .../4-postgresql-common_190ubuntu0.1_all.deb ...
Adding 'diversion of /usr/bin/pg_config to /usr/bin/pg_config.libpq-dev by postgresql-common'
Unpacking postgresql-common (190ubuntu0.1) ...
Selecting previously unselected package postgresql-10.
Preparing to unpack .../5-postgresql-10_10.15-0ubuntu0.18.04.1_amd64.deb ...
Unpacking postgresql-10 (10.15-0ubuntu0.18.04.1) ...
Selecting previously unselected package postgresql.
Preparing to unpack .../6-postgresql_10+190ubuntu0.1_all.deb ...
Unpacking postgresql (10+190ubuntu0.1) ...
Selecting previously unselected package sysstat.
Preparing to unpack .../7-sysstat_11.6.1-1ubuntu0.1_amd64.deb ...
Unpacking sysstat (11.6.1-1ubuntu0.1) ...
Setting up sysstat (11.6.1-1ubuntu0.1) ...

Creating config file /etc/default/sysstat with new version
update-alternatives: using /usr/bin/sar.sysstat to provide /usr/bin/sar (sar) in auto mode
Created symlink /etc/systemd/system/multi-user.target.wants/sysstat.service → /lib/systemd/system/sysstat.service.
Setting up ssl-cert (1.0.39) ...
Setting up libpq5:amd64 (10.15-0ubuntu0.18.04.1) ...
Setting up postgresql-client-common (190ubuntu0.1) ...
Setting up postgresql-common (190ubuntu0.1) ...
Adding user postgres to group ssl-cert

Creating config file /etc/postgresql-common/createcluster.conf with new version
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
Removing obsolete dictionary files:
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /lib/systemd/system/postgresql.service.
Setting up postgresql-client-10 (10.15-0ubuntu0.18.04.1) ...
update-alternatives: using /usr/share/postgresql/10/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode
Setting up postgresql-10 (10.15-0ubuntu0.18.04.1) ...
Creating new PostgreSQL cluster 10/main ...
/usr/lib/postgresql/10/bin/initdb -D /var/lib/postgresql/10/main --auth-local peer --auth-host md5
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/10/main ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... Etc/UTC
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    /usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main -l logfile start

Ver Cluster Port Status Owner    Data directory              Log file
10  main    5432 down   postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
update-alternatives: using /usr/share/postgresql/10/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
Setting up postgresql (10+190ubuntu0.1) ...
Processing triggers for man-db (2.8.3-2ubuntu0.1) ...
Processing triggers for ureadahead (0.100.0-21) ...
Processing triggers for libc-bin (2.27-3ubuntu1.2) ...
Processing triggers for systemd (237-3ubuntu10.38) ...
ALTER ROLE
NOTICE:  database "tfio_demo" does not exist, skipping
DROP DATABASE
CREATE DATABASE

必要な環境変数を設定する

次の環境変数は、前のセクションの PostgreSQL 設定に基づいています。設定が異なる場合、または既存のデータベースを使用している場合は、それに応じて変更する必要があります。

%env TFIO_DEMO_DATABASE_NAME=tfio_demo
%env TFIO_DEMO_DATABASE_HOST=localhost
%env TFIO_DEMO_DATABASE_PORT=5432
%env TFIO_DEMO_DATABASE_USER=postgres
%env TFIO_DEMO_DATABASE_PASS=postgres

env: TFIO_DEMO_DATABASE_NAME=tfio_demo
env: TFIO_DEMO_DATABASE_HOST=localhost
env: TFIO_DEMO_DATABASE_PORT=5432
env: TFIO_DEMO_DATABASE_USER=postgres
env: TFIO_DEMO_DATABASE_PASS=postgres

PostgreSQL サーバーでデータを準備する

このチュートリアルではデータベースを作成し、デモのためにデータベースにデータを入力します。このチュートリアルで使用されるデータは、Air Quality Data Set からのデータで、UCI Machine Learning Repository から入手できます。

以下は、Air Quality Data Set のサブセットのプレビューです。

Date	Time	CO(GT)	PT08.S1(CO)	NMHC(GT)	C6H6(GT)	PT08.S2(NMHC)	NOx(GT)	PT08.S3(NOx)	NO2(GT)	PT08.S4(NO2)	PT08.S5(O3)	T	RH	AH
10/03/2004	18.00.00	2,6	1360	150	11,9	1046	166	1056	113	1692	1268	13,6	48,9	0,7578
10/03/2004	19.00.00	2	1292	112	9,4	955	103	1174	92	1559	972	13,3	47,7	0,7255
10/03/2004	20.00.00	2,2	1402	88	9,0	939	131	1140	114	1555	1074	11,9	54,0	0,7502
10/03/2004	21.00.00	2,2	1376	80	9,2	948	172	1092	122	1584	1203	11,0	60,0	0,7867
10/03/2004	22.00.00	1,6	1272	51	6,5	836	131	1205	116	1490	1110	11,2	59,6	0,7888

大気質データセットと UCI 機械学習リポジトリの詳細については、参照文献セクションをご覧ください。

データの準備をシンプルにするために、Air Quality Data Setの SQL バージョンが用意されており、AirQualityUCI.sql として入手できます。

表を作成するステートメントは次のとおりです。

CREATE TABLE AirQualityUCI (   Date DATE,   Time TIME,   CO REAL,   PT08S1 INT,   NMHC REAL,   C6H6 REAL,   PT08S2 INT,   NOx REAL,   PT08S3 INT,   NO2 REAL,   PT08S4 INT,   PT08S5 INT,   T REAL,   RH REAL,   AH REAL );

データベースに表を作成してデータを入力するための完全なコマンドは以下のとおりです。

curl -s -OL https://github.com/tensorflow/io/raw/master/docs/tutorials/postgresql/AirQualityUCI.sql

PGPASSWORD=$TFIO_DEMO_DATABASE_PASS psql -q -h $TFIO_DEMO_DATABASE_HOST -p $TFIO_DEMO_DATABASE_PORT -U $TFIO_DEMO_DATABASE_USER -d $TFIO_DEMO_DATABASE_NAME -f AirQualityUCI.sql

PostgreSQL サーバーからデータセットを作成し、TensorFlow で使用する

PostgreSQL サーバーからのデータセットの作成は、queryおよびendpoint引数を指定してtfio.experimental.IODataset.from_sqlを呼び出して簡単に実行できます。queryはテーブル内の選択した列の SQL クエリで、endpoint引数はアドレスとデータベース名です。

import os
import tensorflow_io as tfio

endpoint="postgresql://{}:{}@{}?port={}&dbname={}".format(
    os.environ['TFIO_DEMO_DATABASE_USER'],
    os.environ['TFIO_DEMO_DATABASE_PASS'],
    os.environ['TFIO_DEMO_DATABASE_HOST'],
    os.environ['TFIO_DEMO_DATABASE_PORT'],
    os.environ['TFIO_DEMO_DATABASE_NAME'],
)

dataset = tfio.experimental.IODataset.from_sql(
    query="SELECT co, pt08s1 FROM AirQualityUCI;",
    endpoint=endpoint)

print(dataset.element_spec)

{'co': TensorSpec(shape=(), dtype=tf.float32, name=None), 'pt08s1': TensorSpec(shape=(), dtype=tf.int32, name=None)}

上記のdataset.element_specの出力からわかるように、作成されたDatasetの要素は、データベーステーブルの列名をキーとして持つ python dict オブジェクトです。

{   'co': TensorSpec(shape=(), dtype=tf.float32, name=None),   'pt08s1': TensorSpec(shape=(), dtype=tf.int32, name=None), }

さらに簡単に演算を実行できます。たとえば、Datasetのnoxフィールドとno2フィールドの両方を選択して、差分を計算できます

dataset = tfio.experimental.IODataset.from_sql(
    query="SELECT nox, no2 FROM AirQualityUCI;",
    endpoint=endpoint)

dataset = dataset.map(lambda e: (e['nox'] - e['no2']))

# check only the first 20 record
dataset = dataset.take(20)

print("NOx - NO2:")
for difference in dataset:
  print(difference.numpy())

NOx - NO2:
53.0
11.0
17.0
50.0
15.0
-7.0
-15.0
-14.0
-15.0
0.0
-13.0
-12.0
-14.0
16.0
62.0
28.0
14.0
3.0
9.0
34.0

作成されたDatasetは、トレーニングまたは推論のために直接tf.kerasに渡す準備ができています。

参照文献

Dua, D. and Graff, C. (2019). UCI Machine Learning Repository [http://archive.ics.uci.edu/ml]. Irvine, CA: University of California, School of Information and Computer Science.
S. De Vito, E. Massera, M. Piga, L. Martinotto, G. Di Francia, On field calibration of an electronic nose for benzene estimation in an urban pollution monitoring scenario, Sensors and Actuators B: Chemical, Volume 129, Issue 2, 22 February 2008, Pages 750-757, ISSN 0925-4005