Использование коннектора PXF JDBC для чтения и записи данных между Greengage DB и PostgreSQL

Антон Монаков

Contents

Создание исходных таблиц PostgreSQL
Конфигурирование коннектора JDBC
Создание читающей внешней таблицы
Создание пишущей внешней таблицы
Создание именованного запроса

Этот пример демонстрирует, как настроить и использовать коннектор PXF JDBC для чтения и записи данных между Greengage DB и PostgreSQL с помощью внешних таблиц. Для его выполнения убедитесь, что у вас работает и доступен сервер PostgreSQL в дополнение к серверу Greengage DB. В примере сервер PostgreSQL 14 работает на хосте с именем pserver, а кластер Greengage DB состоит из четырех хостов:

mdw — мастер-хост;
smdw — резервный мастер-хост;
sdw1 и sdw2 — сегмент-хосты.

Создание исходных таблиц PostgreSQL

На хосте PostgreSQL подключитесь к базе данных PostgreSQL по умолчанию как пользователь postgres:
```
$ psql -U postgres
```
Создайте базу данных с именем customers и подключитесь к ней:
```
CREATE DATABASE customers;
\c customers
```

Создайте таблицу с именем customers и заполните ее данными:

CREATE TABLE customers (
    id INTEGER,
    name TEXT,
    city TEXT
);

INSERT INTO customers (id, name, city) 
VALUES (1, 'Alice Smith', 'New York'),
       (2, 'Bob Johnson', 'Los Angeles'),
       (3, 'Charlie Brown', 'Chicago'),
       (4, 'David Lee', 'San Francisco'),
       (5, 'Emily Davis', 'Houston'),
       (6, 'Frank Wilson', 'Phoenix'),
       (7, 'Grace Taylor', 'Philadelphia'),
       (8, 'Henry Moore', 'Dallas'),
       (9, 'Isabella Garcia', 'Seattle'),
       (10, 'Jack Rodriguez', 'Denver');

Создайте таблицу с именем orders:

CREATE TABLE orders (
    order_id INTEGER,
    customer_id INTEGER,
    order_date DATE,
    amount DECIMAL(10, 2)
);

Создайте пользователя PostgreSQL с именем pxfuser:
```
CREATE USER pxfuser WITH PASSWORD 'changeme';
```
Выдайте пользователю pxfuser все привилегии на таблицы customers и orders, что позволит пользователю читать и записывать данные в таблицы. Затем выйдите из psql:
```
GRANT ALL ON customers, orders TO pxfuser;
\q
```
Добавьте в файл конфигурации PostgreSQL (pg_hba.conf) следующее содержимое, что позволит пользователю pxfuser подключаться к базе данных customers с каждого хоста Greengage DB:
```
host    all             all             mdw            trust
host    all             all             smdw           trust
host    all             all             sdw1           trust
host    all             all             sdw2           trust
```
Перезагрузите конфигурацию PostgreSQL:
```
$ sudo pg_ctlcluster 14 main reload
```

Для подключения к PostgreSQL с помощью PXF необходимо создать конфигурацию сервера JDBC PostgreSQL, как описано в статье Настройка PXF-сервера, а затем синхронизировать ее с кластером Greengage DB.

Подключитесь к мастер-хосту Greengage DB как gpadmin.
Измените текущий каталог на $PXF_BASE/servers и создайте каталог конфигурации сервера JDBC с именем postgres-server:
```
$ cd $PXF_BASE/servers
$ mkdir postgres-server
```
Скопируйте файл шаблона конфигурации сервера jdbc-site.xml из каталога $PXF_HOME/templates/ в созданный каталог postgres-server:
```
$ cp $PXF_HOME/templates/jdbc-site.xml $PXF_BASE/servers/postgres-server
```

В файле jdbc-site.xml укажите следующую конфигурацию с помощью параметров jdbc.driver, jdbc.url, jdbc.user и jdbc.password:

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <property>
        <name>jdbc.driver</name>
        <value>org.postgresql.Driver</value>
        <description>Class name of the JDBC driver (e.g. org.postgresql.Driver)</description>
    </property>
    <property>
        <name>jdbc.url</name>
        <value>jdbc:postgresql://pserver:5432/customers</value>
        <description>The URL that the JDBC driver can use to connect to the database (e.g. jdbc:postgresql://localhost/postgres)</description>
    </property>
    <property>
        <name>jdbc.user</name>
        <value>pxfuser</value>
        <description>User name for connecting to the database (e.g. postgres)</description>
    </property>
    <property>
        <name>jdbc.password</name>
        <value>changeme</value>
        <description>Password for connecting to the database (e.g. postgres)</description>
    </property>
</configuration>

Синхронизируйте конфигурацию сервера PXF с кластером Greengage DB:
```
$ pxf cluster sync
```

На мастер-хосте Greengage DB создайте читающую внешнюю таблицу PXF, ссылающуюся на ранее созданную таблицу customers PostgreSQL. В выражении LOCATION укажите удаленную таблицу для подключения (customers), используемый профиль (jdbc) и удаленный PostgreSQL-сервер (postgres-server). В выражении FORMAT укажите pxfwritable_import — встроенную кастомную функцию форматирования для операций чтения:
```
CREATE EXTERNAL TABLE pxf_customers_r (
        id INTEGER,
        name TEXT,
        city TEXT
    )
    LOCATION ('pxf://customers?PROFILE=jdbc&SERVER=postgres-server')
    FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');
```

Выполните запрос к созданной внешней таблице:

SELECT * FROM pxf_customers_r;

Вывод должен выглядеть следующим образом:

 id |      name       |     city
----+-----------------+---------------
  1 | Alice Smith     | New York
  2 | Bob Johnson     | Los Angeles
  3 | Charlie Brown   | Chicago
  4 | David Lee       | San Francisco
  5 | Emily Davis     | Houston
  6 | Frank Wilson    | Phoenix
  7 | Grace Taylor    | Philadelphia
  8 | Henry Moore     | Dallas
  9 | Isabella Garcia | Seattle
 10 | Jack Rodriguez  | Denver
(10 rows)

На мастер-хосте Greengage DB создайте пишущую внешнюю таблицу PXF, ссылающуюся на ранее созданную таблицу orders PostgreSQL. В выражении LOCATION укажите удаленную таблицу для подключения (orders), используемый профиль (jdbc) и удаленный PostgreSQL-сервер (postgres-server). В выражении FORMAT укажите pxfwritable_export — встроенную кастомную функцию форматирования для операций записи:
```
CREATE WRITABLE EXTERNAL TABLE pxf_orders_w ( 
        order_id INTEGER,
        customer_id INTEGER,
        order_date DATE,
        amount DECIMAL(10, 2)
    )
    LOCATION ('pxf://orders?PROFILE=jdbc&SERVER=postgres-server')
    FORMAT 'CUSTOM' (FORMATTER='pxfwritable_export');
```

Вставьте данные в таблицу pxf_orders_w:

INSERT INTO pxf_orders_w (order_id, customer_id, order_date, amount) 
VALUES (101, 1, '2023-01-15', 75.50),
       (102, 2, '2023-01-20', 120.00),
       (103, 1, '2023-10-10', 90.25),
       (104, 3, '2023-02-28', 60.75),
       (105, 2, '2023-03-05', 150.00),
       (106, 4, '2023-03-12', 85.00),
       (107, 5, '2023-05-18', 110.75),
       (108, 6, '2023-04-01', 95.20),
       (109, 7, '2023-06-15', 135.50),
       (110, 8, '2023-04-22', 70.00),
       (111, 9, '2023-05-01', 105.00),
       (112, 10, '2023-07-08', 65.75),
       (113, 1, '2023-05-15', 80.50),
       (114, 2, '2023-09-22', 140.00),
       (115, 3, '2023-11-01', 72.25);

На сервере PostgreSQL выполните запрос к первоначально созданной таблице orders, чтобы просмотреть данные:

SELECT * FROM orders ORDER BY order_id;

Вывод должен выглядеть следующим образом:

 order_id | customer_id | order_date | amount
----------+-------------+------------+--------
      101 |           1 | 2023-01-15 |  75.50
      102 |           2 | 2023-01-20 | 120.00
      103 |           1 | 2023-10-10 |  90.25
      104 |           3 | 2023-02-28 |  60.75
      105 |           2 | 2023-03-05 | 150.00
      106 |           4 | 2023-03-12 |  85.00
      107 |           5 | 2023-05-18 | 110.75
      108 |           6 | 2023-04-01 |  95.20
      109 |           7 | 2023-06-15 | 135.50
      110 |           8 | 2023-04-22 |  70.00
      111 |           9 | 2023-05-01 | 105.00
      112 |          10 | 2023-07-08 |  65.75
      113 |           1 | 2023-05-15 |  80.50
      114 |           2 | 2023-09-22 | 140.00
      115 |           3 | 2023-11-01 |  72.25
(15 rows)

Подключитесь к мастер-хосту Greengage DB как gpadmin.
Измените текущий каталог на каталог конфигурации сервера JDBC $PXF_BASE/servers/postgres-server и создайте в нем текстовый файл с именем order_report.sql со следующим содержимым:
```
SELECT
    c.name,
    c.city,
    SUM(o.amount) AS total,
    EXTRACT(MONTH FROM o.order_date) AS order_month
FROM
    customers c
JOIN
    orders o ON c.id = o.customer_id
GROUP BY
    c.name, c.city, EXTRACT(MONTH FROM o.order_date)
ORDER BY
    c.city, total;
```
Этот запрос извлекает имена клиентов и названия городов, в которых они находятся, вместе с общей суммой всех заказов, сделанных каждым клиентом в определенном месяце. Данные группируются по клиенту и месяцу оформления заказа, что позволяет получить ежемесячную сводку суммарных значений заказов для каждого клиента. Затем результаты сортируются сначала по городу, а потом по общей сумме заказов.
Синхронизируйте конфигурацию сервера PXF с кластером Greengage DB:
```
$ pxf cluster sync
```
Создайте читающую внешнюю таблицу PXF, ссылающуюся на ранее созданный файл запроса. В выражении LOCATION укажите имя запроса (order_report), используемый профиль (jdbc) и удаленный PostgreSQL-сервер (postgres-server). Выражение PARTITION_BY используется для разделения данных на партиции на основе столбца order_month с интервалом в три месяца на партицию. В выражении FORMAT укажите pxfwritable_import — встроенную кастомную функцию форматирования для операций чтения:
```
CREATE EXTERNAL TABLE pxf_query_order_report (
        name text,
        city text,
        total int,
        order_month int
    )
    LOCATION ('pxf://query:order_report?PROFILE=jdbc&SERVER=postgres-server&PARTITION_BY=order_month:int&RANGE=1:12&INTERVAL=3')
    FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');
```

Выполните несколько тестовых запросов к созданной внешней таблице и просмотрите результаты:

Просмотр всех данных, возвращаемых запросом:

SELECT * FROM pxf_query_order_report;

Вывод должен выглядеть следующим образом:

      name       |     city      | total | order_month
-----------------+---------------+-------+-------------
 Henry Moore     | Dallas        |    70 |           4
 Emily Davis     | Houston       |   110 |           5
 Alice Smith     | New York      |    80 |           5
 Grace Taylor    | Philadelphia  |   135 |           6
 Frank Wilson    | Phoenix       |    95 |           4
 Isabella Garcia | Seattle       |   105 |           5
 Charlie Brown   | Chicago       |    60 |           2
 Bob Johnson     | Los Angeles   |   120 |           1
 Bob Johnson     | Los Angeles   |   150 |           3
 Alice Smith     | New York      |    75 |           1
 David Lee       | San Francisco |    85 |           3
 Charlie Brown   | Chicago       |    72 |          11
 Alice Smith     | New York      |    90 |          10
 Jack Rodriguez  | Denver        |    65 |           7
 Bob Johnson     | Los Angeles   |   140 |           9
(15 rows)

Просмотр городов, объем заказов в которых превышает 100:

SELECT city, sum(total) FROM pxf_query_order_report
    WHERE total > 100
    GROUP BY city;

Вывод должен выглядеть следующим образом:

     city     | sum
--------------+-----
 Philadelphia | 135
 Los Angeles  | 410
 Seattle      | 105
 Houston      | 110
(4 rows)

Просмотр пяти крупнейших клиентов по сумме заказов:

SELECT name, SUM(total) AS total_ordered FROM pxf_query_order_report
    GROUP BY name
    ORDER BY total_ordered DESC
    LIMIT 5;

Вывод должен выглядеть следующим образом:

     name      | total_ordered
---------------+---------------
 Bob Johnson   |           410
 Alice Smith   |           245
 Grace Taylor  |           135
 Charlie Brown |           132
 Emily Davis   |           110
(5 rows)

Просмотр общего объема заказов по месяцам:

SELECT order_month, SUM(total) AS monthly_total FROM pxf_query_order_report
    GROUP BY order_month
    ORDER BY order_month;

Вывод должен выглядеть следующим образом:

 order_month | monthly_total
-------------+---------------
           1 |           195
           2 |            60
           3 |           235
           4 |           165
           5 |           295
           6 |           135
           7 |            65
           9 |           140
          10 |            90
          11 |            72
(10 rows)

Сторонние таблицы

MySQL

Использование коннектора PXF JDBC для чтения и записи данных между Greengage DB и PostgreSQL

Создание исходных таблиц PostgreSQL

Конфигурирование коннектора JDBC

Создание читающей внешней таблицы

Создание пишущей внешней таблицы

Создание именованного запроса