 在 TensorFlow.org 上查看
|
 在 Google Colab 中运行
|
 在 GitHub 上查看源代码
|
 下载笔记本
|
概览
本教程展示了如何从 PostgreSQL 数据库服务器创建 tf.data.Dataset，以便将创建的 Dataset 传递给 tf.keras 以用于训练或推理目的。
SQL 数据库是数据科学家数据的重要来源。作为最流行的开源 SQL 数据库之一，PostgreSQL 被广泛用于企业，用于存储关键和事务数据。直接从 PostgreSQL 数据库服务器创建 Dataset 并将 Dataset 传递给 tf.keras 以用于训练或推理，可以极大地简化数据管道，并帮助数据科学家专注于构建机器学习模型。
设置和使用
安装所需的 tensorflow-io 软件包，并重新启动运行时
try:
%tensorflow_version 2.x
except Exception:
pass
!pip install -q tensorflow-io
安装和设置 PostgreSQL（可选）
为了演示在 Google Colab 上的使用，您将安装 PostgreSQL 服务器。还需要密码和一个空数据库。
如果您不在 Google Colab 上运行此笔记本，或者您更喜欢使用现有数据库，请跳过以下设置并继续执行下一部分。
# Install postgresql serversudo apt-get -y -qq updatesudo apt-get -y -qq install postgresqlsudo 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 usedsudo -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 ... 254633 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 服务器中准备数据
出于演示目的，本教程将创建一个数据库并使用一些数据填充数据库。本教程中使用的数据来自 空气质量数据集，可从 UCI 机器学习存储库 获得。
以下是空气质量数据集子集的试用预览
日期|时间|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 机器学习存储库的更多信息，请参阅 参考资料 部分。
为了简化数据准备，我们准备了空气质量数据集的 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.sqlPGPASSWORD=$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 服务器创建数据集就像调用 tfio.experimental.IODataset.from_sql 并使用 query 和 endpoint 参数一样简单。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 字典对象，其中数据库表的列名作为键。这非常便于应用进一步的操作。例如，您可以选择 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. 和 Graff, C. (2019)。UCI 机器学习存储库 [http://archive.ics.uci.edu/ml]。加利福尼亚州欧文市：加州大学信息与计算机科学学院。
- S. De Vito、E. Massera、M. Piga、L. Martinotto、G. Di Francia，城市污染监测场景中电子鼻苯浓度估计的现场校准，传感器和执行器 B：化学，第 129 卷，第 2 期，2008 年 2 月 22 日，第 750-757 页，ISSN 0925-4005