Creating an external table using PXF

Create a Yandex Managed Service for MySQL® cluster with public access to its hosts and mysqluser for username.

Add rules allowing all incoming and outgoing traffic to the security group of the Managed Service for MySQL® cluster.

Create a table in MySQL® and populate it with data:

1. Connect to the MySQL® DB using mysql.

2. Create a table named test:

   CREATE TABLE test (
     a INT,
     b INT
   );
   

3. Add data:

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

Get access to external data in Yandex MPP Analytics for PostgreSQL:

1. Connect to the database in Yandex MPP Analytics for PostgreSQL.

2. Create an external data source:

   CREATE SERVER "myserver"
     FOREIGN DATA WRAPPER jdbc_pxf_fdw
     OPTIONS (
       jdbc_driver 'com.mysql.jdbc.Driver',
       db_url 'jdbc:mysql://c-<cluster_ID>.rw.mdb.yandexcloud.net:3306/<DB_name>',
       user 'mysqluser',
       pass '<password>'
     );
   

   db_url is a special FQDN always pointing to the current master host in the Managed Service for MySQL® cluster.

   You can get the cluster ID with the list of clusters in the folder.

3. Create a mapping between a local user and a user in the external data source:

   CREATE USER MAPPING FOR CURRENT_USER
     SERVER "myserver";
   

4. Create an external table named fdw_mysql to reference the test table in the MySQL® cluster:

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

5. Query the external table for data:

   SELECT * FROM fdw_mysql;
   

6. Populate the external table with data:

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

Create a Yandex Managed Service for PostgreSQL cluster with public access to its hosts and pguser for username.

Add rules allowing all incoming and outgoing traffic to the security group of the Managed Service for PostgreSQL cluster.

Create a table in PostgreSQL and populate it with data:

1. Connect to the PostgreSQL DB using psql.

2. Create a table named test:

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

3. Add data:

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

Get access to external data in Yandex MPP Analytics for PostgreSQL:

1. Connect to the database in Yandex MPP Analytics for PostgreSQL.

2. Create an external data source:

   CREATE SERVER "pgserver"
     FOREIGN DATA WRAPPER jdbc_pxf_fdw
     OPTIONS (
       jdbc_driver 'org.postgresql.Driver',
       db_url 'jdbc:postgresql://c-<cluster_ID>.rw.mdb.yandexcloud.net:6432/<DB_name>',
       user 'pguser',
       pass '<password>'
     );
   

   db_url is a special FQDN always pointing to the current master host in the Managed Service for PostgreSQL cluster.

   You can get the cluster ID with the list of clusters in the folder.

3. Create a mapping between a local user and a user in the external data source:

   CREATE USER MAPPING FOR CURRENT_USER
     SERVER "pgserver";
   

4. Create an external table named fdw_pg to reference the public.test table in the PostgreSQL cluster:

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

5. Query the external table for data:

   SELECT * FROM fdw_pg;
   

6. Populate the external table with data:

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

Create a service account named fdw-agent and assign it the storage.editor role to access the Object Storage bucket.

Create an Object Storage bucket with restricted access.

Grant fdw-agent the READ and WRITE permissions for the new bucket.

Create a static access key.

Prepare a test file and upload it to the bucket.

1. Create a test file named test.csv on your local machine:

   1,11
   2,22
   

2. Upload the test.csv file to the bucket.

Get access to external data in Yandex MPP Analytics for PostgreSQL:

1. Connect to the database in Yandex MPP Analytics for PostgreSQL.

2. Create an external data source:

   CREATE SERVER "objserver"
     FOREIGN DATA WRAPPER s3_pxf_fdw
     OPTIONS (
       accesskey '<static_access_key_ID>',
       secretkey '<secret_access_key>',
       endpoint 'storage.yandexcloud.net'
     );
   

3. Create a mapping between a local user and a user in the external data source:

   CREATE USER MAPPING FOR CURRENT_USER
     SERVER "objserver";
   

4. Create an external table named fdw_s3 to reference the test.csv table in the bucket:

   CREATE FOREIGN TABLE fdw_s3 (
     a INT,
     b INT
   )
   SERVER "objserver"
   OPTIONS (
     resource '<bucket_name>/test.csv',
     format 'csv'
   );
   

5. Query the external table for data:

   SELECT * FROM fdw_s3;
   

Create a Yandex Managed Service for MySQL® cluster with the following settings:

• Username: mysqluser.
• In the host settings, select the Public access option.

In the cluster subnet, set up a NAT gateway and create a security group allowing all incoming and outgoing traffic from all addresses.

Optionally, create an external JDBC data source with the following properties:

• Name: mysqlserver

• Driver: com.mysql.jdbc.Driver

• Url: jdbc:mysql://c-<cluster_ID>.rw.mdb.yandexcloud.net:3306/db1, where:

  • c-<cluster_ID>.rw.mdb.yandexcloud.net is a special FQDN always pointing to the current master host in the Managed Service for MySQL® cluster.
  • 3306: Port for connection to the Managed Service for MySQL® cluster.
  • db1: DB name in the Managed Service for MySQL® cluster.

• User: mysqluser

You can get the cluster ID with the list of clusters in the folder.

With no data source created, you will need to provide the source connection properties in the SQL query for creating an external table.

Connect to the MySQL® DB using mysql.

Create a test table and populate it with data:

CREATE TABLE test (a INT, b INT);

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

Connect to the database.

Create an external table named pxf_mysql to reference the test table in the MySQL® cluster. The SQL query depends on whether you previously created an external data source:

• With a data source created:

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

• With no data source created:

  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-<cluster_ID>.rw.mdb.yandexcloud.net:3306/db1&USER=mysqluser')
  FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');
  

Query the external table for data:

SELECT * FROM pxf_mysql;

Result:

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

Create a Yandex Managed Service for PostgreSQL cluster with the following settings:

• Username: pguser
• In the host settings, select the Public access option.

In the cluster subnet, set up a NAT gateway and create a security group allowing all incoming and outgoing traffic from all addresses.

Optionally, create an external JDBC data source with the following properties:

• Name: pgserver

• Driver: org.postgresql.Driver

• Url: jdbc:postgresql://c-<cluster_ID>.rw.mdb.yandexcloud.net:6432/db1, where:

  • c-<cluster_ID>.rw.mdb.yandexcloud.net is a special FQDN always pointing to the current master host in the Managed Service for PostgreSQL cluster.
  • 6432: Port for connection to the Managed Service for PostgreSQL cluster.
  • db1: DB name in the Managed Service for PostgreSQL cluster.

• User: pguser

You can get the cluster ID with the list of clusters in the folder.

With no data source created, you will need to provide the source connection properties in the SQL query for creating an external table.

Connect to the PostgreSQL DB using psql.

Create a test table and populate it with data:

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

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

Connect to the database.

Create an external table named pxf_pg to reference the public.test table in the PostgreSQL cluster. The SQL query depends on whether you previously created an external data source:

• With a data source created:

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

• With no data source created:

  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-<cluster_ID>.rw.mdb.yandexcloud.net:6432/db1&USER=pguser')
  FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');
  

Query the external table for data:

SELECT * FROM pxf_pg;

Result:

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

In the cluster subnet, set up a NAT gateway and create a security group allowing all incoming and outgoing traffic from all addresses.

Create an Object Storage bucket with restricted access.

Create a static access key.

Optionally, create an external S3 data source with the following properties:

• Name: objserver.
• Access Key: ID of the static access key created earlier.
• Secret Key: Secret key created earlier together with the static access key.
• Endpoint: storage.yandexcloud.net.

With no data source created, you will need to provide the source connection properties in the SQL query for creating an external table.

Create a test file named test.csv on your local machine:

1,111
2,222

Upload your test file to the bucket.

Files you are uploading to the bucket must not start with . or _. Such files are considered hidden, and PXF does not read data from them.

Connect to the database.

To read data from an Object Storage bucket:

1. Create an external table named pxf_s3_read to reference the bucket. The SQL query depends on whether you previously created an external data source:

   • With a data source created:

     CREATE READABLE EXTERNAL TABLE pxf_s3_read(a int, b int)
     LOCATION ('pxf://<bucket_name>/test.csv?PROFILE=s3:text&SERVER=objserver')
     FORMAT 'CSV';
     

   • With no data source created:

     CREATE READABLE EXTERNAL TABLE pxf_s3_read(a int, b int)
     LOCATION ('pxf://<bucket_name>/test.csv?PROFILE=s3:text&accesskey=<key_ID>&secretkey=<secret_key>&endpoint=storage.yandexcloud.net')
     FORMAT 'CSV';
     

2. Query the external table for data:

   SELECT * FROM pxf_s3_read;
   

   Result:

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

To write data to an Object Storage bucket:

1. Create an external table named pxf_s3_write with the WRITABLE option. You do not need to specify a file name when creating the table:

   • With a data source created:

     CREATE WRITABLE EXTERNAL TABLE pxf_s3_write(a int, b int)
     LOCATION ('pxf://<bucket_name>/?PROFILE=s3:text&SERVER=objserver')
     FORMAT 'CSV';
     

   • With no data source created:

     CREATE WRITABLE EXTERNAL TABLE pxf_s3_write(a int, b int)
     LOCATION ('pxf://<bucket_name>/?PROFILE=s3:text&accesskey=<key_ID>&secretkey=<secret_key>&endpoint=storage.yandexcloud.net')
     FORMAT 'CSV';
     

2. Populate the table with data:

   INSERT INTO pxf_s3_write VALUES (3,333);
   

   INSERT 0 1
   

3. Make sure that a new object has been created in the bucket.