Creating an external table using PXF

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.

(Optional) Create an external JDBC data source with the following parameters:

• Name: mysqlserver
• Driver: com.mysql.jdbc.Driver
• Url: jdbc:mysql://c-<cluster_ID>.rw.mdb.yandexcloud.net:3306/db1
• User: mysqluser

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

With no data source created, you need to provide the source connection parameters 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 Greenplum® DB.

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 or not:

• 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.

(Optional) Create an external JDBC data source with the following parameters:

• Name: pgserver
• Driver: org.postgresql.Driver
• Url: jdbc:postgresql://c-<cluster_ID>.rw.mdb.yandexcloud.net:6432/db1
• User: pguser

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

With no data source created, you need to provide the source connection parameters 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 Greenplum® DB.

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 or not:

• 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.

(Optional) Create an external S3 data source with the following parameters:

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

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

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

1,111
2,222

Upload your test file to the bucket.

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

Connect to the Greenplum® DB.

To read data from the 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 or not:

   • 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 a 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. Add data to the table:

   INSERT INTO pxf_s3_write VALUES (3,333);
   

   INSERT 0 1
   

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