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

Создайте кластер Yandex Managed Service for MySQL® с настройками:

• Имя пользователя — mysqluser.
• В настройках хостов выберите опцию Публичный доступ.

В подсети кластера настройте NAT-шлюз и создайте группу безопасности, разрешающую весь входящий и исходящий трафик со всех адресов.

(Опционально) Создайте внешний источник данных JDBC с параметрами:

• Имя — mysqlserver;
• Driver — com.mysql.jdbc.Driver;
• Url — jdbc:mysql://c-<идентификатор_кластера>.rw.mdb.yandexcloud.net:3306/db1;
• User — mysqluser.

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

Если не создать источник данных, параметры подключения к источнику нужно передать в SQL-запросе на создание внешней таблицы.

Подключитесь к БД MySQL® с помощью утилиты mysql.

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

CREATE TABLE test (a INT, b INT);

INSERT INTO test VALUES (1, '11'), (2, '22');

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

Создайте внешнюю таблицу pxf_mysql, которая будет ссылаться на таблицу test в кластере MySQL®. SQL-запрос зависит от того, был ли создан ранее внешний источник данных:

• С источником данных:

  CREATE READABLE EXTERNAL TABLE pxf_mysql(a int, b int)
  LOCATION ('pxf://test?PROFILE=JDBC&SERVER=mysqlserver')
  FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');
  

• Без источника данных:

  CREATE READABLE EXTERNAL TABLE pxf_mysql(a int, b int)
  LOCATION ('pxf://test?PROFILE=JDBC&JDBC_DRIVER=com.mysql.jdbc.Driver&DB_URL=jdbc:mysql://c-<идентификатор_кластера>.rw.mdb.yandexcloud.net:3306/db1&USER=mysqluser')
  FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');
  

Получите данные из внешней таблицы:

SELECT * FROM pxf_mysql;

Результат:

test_pxf=> SELECT * FROM pxf_mysql;
a | b
--+----
1 | 11
2 | 22
(2 rows)

Создайте кластер Yandex Managed Service for PostgreSQL с настройками:

• Имя пользователя — pguser;
• В настройках хостов выберите опцию Публичный доступ.

В подсети кластера настройте NAT-шлюз и создайте группу безопасности, разрешающую весь входящий и исходящий трафик со всех адресов.

(Опционально) Создайте внешний источник данных JDBC с параметрами:

• Имя — pgserver;
• Driver — org.postgresql.Driver;
• Url — jdbc:postgresql://c-<идентификатор_кластера>.rw.mdb.yandexcloud.net:6432/db1;
• User — pguser.

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

Если не создать источник данных, параметры подключения к источнику нужно передать в SQL-запросе на создание внешней таблицы.

Подключитесь к БД PostgreSQL с помощью утилиты psql.

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

CREATE TABLE public.test ("a" INT,"b" INT);

INSERT INTO public.test VALUES (1, '11'), (2, '22');

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

Создайте внешнюю таблицу pxf_pg, которая будет ссылаться на таблицу public.test в кластере PostgreSQL. SQL-запрос зависит от того, был ли создан ранее внешний источник данных:

• С источником данных:

  CREATE READABLE EXTERNAL TABLE pxf_pg(a int, b int)
  LOCATION ('pxf://public.test?PROFILE=JDBC&SERVER=pgserver')
  FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');
  

• Без источника данных:

  CREATE READABLE EXTERNAL TABLE pxf_pg(a int, b int)
  LOCATION ('pxf://public.test?PROFILE=JDBC&JDBC_DRIVER=org.postgresql.Driver&DB_URL=jdbc:postgresql://c-<идентификатор_кластера>.rw.mdb.yandexcloud.net:6432/db1&USER=pguser')
  FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');
  

Получите данные из внешней таблицы:

SELECT * FROM pxf_pg;

Результат:

test_pxf=> SELECT * FROM pxf_pg;
a | b
--+----
1 | 11
2 | 22
(2 rows)

В подсети кластера настройте NAT-шлюз и создайте группу безопасности, разрешающую весь входящий и исходящий трафик со всех адресов.

Создайте бакет Object Storage с ограниченным доступом.

Создайте статический ключ доступа.

(Опционально) Создайте внешний источник данных S3 с параметрами:

• Имя — objserver;
• Access Key — идентификатор статического ключа доступа, созданного ранее;
• Secret Key — секретыный ключ, созданный ранее вместе со статическим ключом доступа;
• Endpoint — storage.yandexcloud.net.

Если не создать источник данных, параметры подключения к источнику нужно передать в SQL-запросе на создание внешней таблицы.

На локальной машине создайте тестовый файл test.csv:

1,111
2,222

Загрузите тестовый файл в бакет.

Файлы, которые вы загружаете в бакет, не должны начинаться с символов . и _. Такие файлы считаются скрытыми, и PXF не считывает из них данные.

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

Чтобы считать данные из бакета Object Storage:

1. Создайте внешнюю таблицу pxf_s3_read, которая будет ссылаться на бакет. SQL-запрос зависит от того, был ли создан ранее внешний источник данных:

   • С источником данных:

     CREATE READABLE EXTERNAL TABLE pxf_s3_read(a int, b int)
     LOCATION ('pxf://<имя_бакета>/test.csv?PROFILE=s3:text&SERVER=objserver')
     FORMAT 'CSV';
     

   • Без источника данных:

     CREATE READABLE EXTERNAL TABLE pxf_s3_read(a int, b int)
     LOCATION ('pxf://<имя_бакета>/test.csv?PROFILE=s3:text&accesskey=<идентификатор_ключа>&secretkey=<секретный_ключ>&endpoint=storage.yandexcloud.net')
     FORMAT 'CSV';
     

2. Получите данные из внешней таблицы:

   SELECT * FROM pxf_s3_read;
   

   Результат:

   test_pxf=> SELECT * FROM pxf_s3_read;
   a | b
   ---+----
   1 | 111
   2 | 222
   (2 rows)
   

Чтобы записать данные в бакет Object Storage:

1. Создайте внешнюю таблицу pxf_s3_write с опцией WRITABLE. Имя файла при создании таблицы указывать не нужно:

   • С источником данных:

     CREATE WRITABLE EXTERNAL TABLE pxf_s3_write(a int, b int)
     LOCATION ('pxf://<имя_бакета>/?PROFILE=s3:text&SERVER=objserver')
     FORMAT 'CSV';
     

   • Без источника данных:

     CREATE WRITABLE EXTERNAL TABLE pxf_s3_write(a int, b int)
     LOCATION ('pxf://<имя_бакета>/?PROFILE=s3:text&accesskey=<идентификатор_ключа>&secretkey=<секретный_ключ>&endpoint=storage.yandexcloud.net')
     FORMAT 'CSV';
     

2. Добавьте данные в таблицу:

   INSERT INTO pxf_s3_write VALUES (3,333);
   

   INSERT 0 1
   

3. Убедитесь, что в бакете создан новый объект.