Эта страница была переведа с помощью Cloud Translation API.
Switch to English

Чтение базы данных PostgreSQL из TensorFlow IO

Посмотреть на TensorFlow.org Запустить в Google Colab Посмотреть источник на GitHub Скачать блокнот

обзор

В этом руководстве показано, как создать tf.data.Dataset из сервера базы данных PostgreSQL, чтобы созданный Dataset мог быть передан в tf.keras для обучения или вывода.

База данных SQL является важным источником данных для ученого. Как одна из наиболее популярных баз данных SQL с открытым исходным кодом, PostgreSQL широко используется на предприятиях для хранения критических и транзакционных данных по всем направлениям. Создание Dataset данных непосредственно на сервере базы данных PostgreSQL и передача Dataset в tf.keras для обучения или вывода может значительно упростить конвейер данных и помочь ученому сосредоточиться на построении моделей машинного обучения.

Настройка и использование

Установите необходимые пакеты tenorflow-io и перезапустите среду выполнения

 try:
  %tensorflow_version 2.x
except Exception:
  pass

!pip install -q tensorflow-io
 

Установить и настроить PostgreSQL (необязательно)

Для демонстрации использования в 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 ... 226072 files and directories currently installed.)
Preparing to unpack .../0-libpq5_10.12-0ubuntu0.18.04.1_amd64.deb ...
Unpacking libpq5:amd64 (10.12-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.12-0ubuntu0.18.04.1_amd64.deb ...
Unpacking postgresql-client-10 (10.12-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.12-0ubuntu0.18.04.1_amd64.deb ...
Unpacking postgresql-10 (10.12-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.12-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.12-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.12-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) | НМУВ (GT) | C6H6 (GT) | PT08.S2 (NMHC) | NOx (GT) | PT08.S3 (NOx) | NO2 (GT) | PT08.S4 (NO 2) | PT08.S5 (О3) | 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 | 1 174 | 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 | тысяча пятьсот пятьдесят-пять | 1074 | 11,9 | 54,0 | 0,7502 | 10/03/2004 | 21.00.00 | 2,2 | тысяча триста семьдесят шесть | 80 | 9,2 | 948 | 172 | +1092 | 122 | тысяча пятьсот восемьдесят четыре | 1203 | 11,0 | 60,0 | 0,7867 | 10/03/2004 | 22.00.00 | 1,6 | 1 272 | 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.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 так же просто, как вызвать tfio.experimental.IODataset.from_sql с аргументами query и endpoint . query является 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),
}
 

Вполне удобно применять дальнейшие операции. Например, вы можете выбрать поля nox и no2 Dataset и рассчитать разницу:

 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 непосредственно в tf.keras для обучения или вывода.

Ссылки

  • Dua D. и Graff C. (2019). Репозиторий машинного обучения UCI [ http://archive.ics.uci.edu/ml ]. Ирвин, Калифорния: Калифорнийский университет, Школа информации и компьютерных наук.
  • С. Де Вито, Э. Массера, М. Пига, Л. Мартинотто, Г. Ди Франсиа, О полевой калибровке электронного носа для оценки бензола в сценарии мониторинга загрязнения городов, Датчики и исполнительные механизмы B: Химическая промышленность, Том 129, Выпуск 2, 22 февраля 2008 г., страницы 750-757, ISSN 0925-4005