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

Создайте кластер Yandex Managed Service for MySQL® с публичным доступом к хостам и именем пользователя mysqluser.

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

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

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

2. Создайте таблицу test:

   CREATE TABLE test (
     a INT,
     b INT
   );
   

3. Добавьте данные:

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

Получите доступ к внешним данным в Yandex MPP Analytics for PostgreSQL:

1. Подключитесь к БД в сервисе Yandex MPP Analytics for PostgreSQL.

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

   CREATE SERVER "myserver"
     FOREIGN DATA WRAPPER jdbc_pxf_fdw
     OPTIONS (
       jdbc_driver 'com.mysql.jdbc.Driver',
       db_url 'jdbc:mysql://c-<идентификатор_кластера>.rw.mdb.yandexcloud.kz:3306/<имя_БД>',
       user 'mysqluser',
       pass '<пароль>'
     );
   

   Где db_url — особый FQDN, который всегда указывает на текущий хост-мастер в кластере Managed Service for MySQL®.

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

3. Создайте сопоставление локального пользователя с пользователем на внешнем источнике:

   CREATE USER MAPPING FOR CURRENT_USER
     SERVER "myserver";
   

4. Создайте внешнюю таблицу fdw_mysql, которая будет ссылаться на таблицу test в кластере MySQL®:

   CREATE FOREIGN TABLE fdw_mysql (
     a INT,
     b INT
   )
   SERVER "myserver"
   OPTIONS (
     resource 'test'
   );
   

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

   SELECT * FROM fdw_mysql;
   

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

   INSERT INTO fdw_mysql (a, b)
   VALUES (3, 33);
   

Создайте кластер Yandex Managed Service for PostgreSQL с публичным доступом к хостам и именем пользователя pguser.

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

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

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

2. Создайте таблицу test:

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

3. Добавьте данные:

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

Получите доступ к внешним данным в Yandex MPP Analytics for PostgreSQL:

1. Подключитесь к БД в сервисе Yandex MPP Analytics for PostgreSQL.

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

   CREATE SERVER "pgserver"
     FOREIGN DATA WRAPPER jdbc_pxf_fdw
     OPTIONS (
       jdbc_driver 'org.postgresql.Driver',
       db_url 'jdbc:postgresql://c-<идентификатор_кластера>.rw.mdb.yandexcloud.kz:6432/<имя_БД>',
       user 'pguser',
       pass '<пароль>'
     );
   

   Где db_url — особый FQDN, который всегда указывает на текущий хост-мастер в кластере Managed Service for PostgreSQL.

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

3. Создайте сопоставление локального пользователя с пользователем на внешнем источнике:

   CREATE USER MAPPING FOR CURRENT_USER
     SERVER "pgserver";
   

4. Создайте внешнюю таблицу fdw_pg, которая будет ссылаться на таблицу public.test в кластере PostgreSQL:

   CREATE FOREIGN TABLE fdw_pg (
     a INT,
     b INT
   )
   SERVER "pgserver"
   OPTIONS (
     resource 'public.test'
   );
   

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

   SELECT * FROM fdw_pg;
   

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

   INSERT INTO fdw_pg (a, b)
   VALUES (3, 33);
   

Создайте сервисный аккаунт fdw-agent и назначьте ему роль storage.editor для доступа к бакету Object Storage.

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

Предоставьте разрешение READ и WRITE для сервисного аккаунта fdw-agent на созданный бакет.

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

Подготовьте тестовый файл и загрузите его в бакет:

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

   1,11
   2,22
   

2. Загрузите в бакет файл test.csv.

Получите доступ к внешним данным в Yandex MPP Analytics for PostgreSQL:

1. Подключитесь к БД в сервисе Yandex MPP Analytics for PostgreSQL.

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

   CREATE SERVER "objserver"
     FOREIGN DATA WRAPPER s3_pxf_fdw
     OPTIONS (
       accesskey '<идентификатор_статического_ключа_доступа>',
       secretkey '<секретный_ключ_доступа>',
       endpoint 'storage.yandexcloud.kz'
     );
   

3. Создайте сопоставление локального пользователя с пользователем на внешнем источнике:

   CREATE USER MAPPING FOR CURRENT_USER
     SERVER "objserver";
   

4. Создайте внешнюю таблицу fdw_s3, которая будет ссылаться на таблицу test.csv в бакете:

   CREATE FOREIGN TABLE fdw_s3 (
     a INT,
     b INT
   )
   SERVER "objserver"
   OPTIONS (
     resource '<имя_бакета>/test.csv',
     format 'csv'
   );
   

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

   SELECT * FROM fdw_s3;
   

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

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

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

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

• Имя — mysqlserver;

• Driver — com.mysql.jdbc.Driver;

• Url — jdbc:mysql://c-<идентификатор_кластера>.rw.mdb.yandexcloud.kz:3306/db1, где:

  • c-<идентификатор_кластера>.rw.mdb.yandexcloud.kz — особый FQDN, который всегда указывает на текущий хост-мастер в кластере Managed Service for MySQL®.
  • 3306 — порт для подключения к кластеру Managed Service for MySQL®.
  • db1 — имя БД в кластере Managed Service for MySQL®.

• User — mysqluser.

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

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

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

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

CREATE TABLE test (a INT, b INT);

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

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

Создайте внешнюю таблицу 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.kz: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.kz:6432/db1, где:

  • c-<идентификатор_кластера>.rw.mdb.yandexcloud.kz — особый FQDN, который всегда указывает на текущий хост-мастер в кластере Managed Service for PostgreSQL.
  • 6432 — порт для подключения к кластеру Managed Service for PostgreSQL.
  • db1 — имя БД в кластере Managed Service for PostgreSQL.

• User — pguser.

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

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

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

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

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

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

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

Создайте внешнюю таблицу 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.kz: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.kz.

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

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

1,111
2,222

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

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

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

Чтобы считать данные из бакета 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.kz')
     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.kz')
     FORMAT 'CSV';
     

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

   INSERT INTO pxf_s3_write VALUES (3,333);
   

   INSERT 0 1
   

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