Получение данных из внешних источников с помощью именованных запросов

В Greenplum® для получения данных из внешних СУБД по протоколу PXF можно использовать именованные запросы (named queries).

Именованный запрос — это заранее подготовленный SQL-запрос, который хранится в таблице mdb_toolkit.pxf_named_queries системной базы данных кластера Greenplum®. Ссылка на имя запроса указывается при создании внешней таблицы.

Именованные запросы позволяют объединять (JOIN) таблицы и агрегировать данные во внешнем источнике, когда нет возможности создать представление (VIEW). Поскольку вычисления выполняются во внешнем источнике, это также повышает производительность кластера.

Именованные запросы используются с источниками данных, подключенными к кластеру Greenplum® через JDBC-коннектор.

Чтобы получить данные из внешнего источника с помощью именованного запроса:

1. Подготовьте тестовые данные.
2. Создайте внешний источник данных.
3. Создайте именованный запрос.
4. Создайте внешнюю таблицу и получите из нее данные.

Если созданные ресурсы вам больше не нужны, удалите их.

Необходимые платные ресурсыНеобходимые платные ресурсы

В стоимость поддержки описываемого решения входят:

• Плата за кластер Greenplum®: выделенные хостам вычислительные ресурсы, объем хранилища и резервных копий (см. тарифы Yandex MPP Analytics for PostgreSQL).
• Плата за кластер Yandex Managed Service for PostgreSQL: выделенные хостам вычислительные ресурсы, объем хранилища и резервных копий (см. тарифы Managed Service for PostgreSQL).
• Плата за почасовое использование NAT-шлюзов и исходящий через них трафик (см. тарифы Yandex Virtual Private Cloud).
• Плата за использование публичных IP-адресов (см. тарифы Virtual Private Cloud).

Перед началом работыПеред началом работы

Подготовьте инфраструктуру:

Подготовьте тестовые данныеПодготовьте тестовые данные

1. Подключитесь к базе данных PostgreSQL.

2. Создайте таблицу customers и добавьте в нее тестовые данные:

   CREATE TABLE customers(id int, name text, city text);
   INSERT INTO customers VALUES (111, 'Борис', 'Саратов');
   INSERT INTO customers VALUES (222, 'Мария', 'Москва');
   INSERT INTO customers VALUES (333, 'Павел', 'Брянск');
   INSERT INTO customers VALUES (444, 'Петр', 'Новосибирск');
   INSERT INTO customers VALUES (555, 'Наталья', 'Псков');
   

3. Проверьте результат:

   SELECT * FROM customers;
   

   id  |  name   |    city
   ----+---------+-------------
   111 | Борис   | Саратов
   222 | Мария   | Москва
   333 | Павел   | Брянск
   444 | Петр    | Новосибирск
   555 | Наталья | Псков
   

4. Создайте таблицу orders и добавьте в нее тестовые данные:

   CREATE TABLE orders(customer_id int, amount int, year int);
   INSERT INTO orders VALUES (111, 12, 2018);
   INSERT INTO orders VALUES (222, 234, 2019);
   INSERT INTO orders VALUES (333, 34, 2018);
   INSERT INTO orders VALUES (444, 456, 2019);
   INSERT INTO orders VALUES (555, 56, 2021);
   

5. Проверьте результат:

   SELECT * FROM orders;
   

   customer_id | amount | year
   ------------+--------+------
           111 |     12 | 2018
           222 |    234 | 2019
           333 |     34 | 2018
           444 |    456 | 2019
           555 |     56 | 2021
   

Создайте внешний источник данныхСоздайте внешний источник данных

В кластере Greenplum® создайте внешний источник данных с параметрами:

• Имя — pgserver;
• Driver — org.postgresql.Driver;
• Url — jdbc:postgresql://c-<идентификатор_кластера_PostgreSQL>.rw.mdb.yandexcloud.kz:6432/<имя_БД_в_кластере_PostgreSQL>;
• User — <имя_пользователя_PostgreSQL>.

Идентификатор кластера можно получить со списком кластеров в каталоге.

Создайте именованный запросСоздайте именованный запрос

Чтобы создать именованный запрос, добавьте его в таблицу mdb_toolkit.pxf_named_queries:

1. Подключитесь к Greenplum®.

2. Выполните запрос:

   INSERT INTO mdb_toolkit.pxf_named_queries (pxf_profile, name, query) VALUES (
      'pgserver',
      'my_query',
      'SELECT c.name, o.year
      FROM customers c
      JOIN orders o ON c.id = o.customer_id;'
   );
   

   Где:

   • pgserver — имя источника данных.
   • my_query— имя именованного запроса.

   В качестве примера именованного запроса используется запрос, который объединяет таблицы customers и orders по полю id = customer_id, а затем выводит имя клиента (c.name) и год, когда клиент совершал заказы (o.year).

3. Проверьте результат:

   SELECT * FROM mdb_toolkit.pxf_named_queries;
   

   pxf_profile |    name     |              query
   ------------+-------------+--------------------------------------------
   pgserver    | my_query    | SELECT c.name, o.year                       
               |             |       FROM customers c                      
               |             |       JOIN orders o ON c.id = o.customer_id;
   

   В столбце query должен содержаться текст именованного запроса.

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

1. Подключитесь к Greenplum®.

2. Создайте внешнюю таблицу pxf_named_query. Она будет ссылаться на данные, которые именованный запрос получает из таблиц customers и orders в БД PostgreSQL:

   CREATE READABLE EXTERNAL TABLE pxf_named_query(name text, year int)
   LOCATION ('pxf://query:my_query?PROFILE=JDBC&SERVER=pgserver')
   FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');
   

   Подробнее о синтаксисе SQL-запроса для создания внешней таблицы см. в соответствующем разделе.

3. Получите данные:

   SELECT * FROM pxf_named_query;
   

   Результат:

     name  | year
   --------+------
   Борис   | 2018
   Мария   | 2019
   Павел   | 2018
   Петр    | 2019
   Наталья | 2021
   

   Совет

   Если при выполнении запроса возникает ошибка, подождите несколько минут и попробуйте снова. Возможно, изменения еще не вступили в силу.

Удалите созданные ресурсыУдалите созданные ресурсы

Некоторые ресурсы платные. Удалите ресурсы, которые вы больше не будете использовать, чтобы за них не списывалась плата:

Greenplum® и Greenplum Database® являются зарегистрированными товарными знаками или товарными знаками Broadcom Inc в США и/или других странах.