Open search page
Hello, I’m DocuDroid!
Open Ask AI page
Submitting feedback
Thank you for rating our AI Search!
We would be grateful if you could share your thoughts so we can improve our AI Search for you and other readers.
GitHub

Use PXF JDBC connector to read and write data between Greengage DB and MySQL

Anton Monakov

This example demonstrates how to configure and use the PXF JDBC connector for reading and writing data between Greengage DB and MySQL by using external tables. To try it out, make sure you have a MySQL server running and accessible in addition to the Greengage DB server. In the example, the MySQL 8.0 server is running on the host named mysqlserver, and the Greengage DB cluster comprises four hosts:

  • mdw — the master host;

  • smdw — the standby master host;

  • sdw1 and sdw2 — the segment hosts.

Create the source MySQL table

  1. Connect to the MySQL host.

  2. Configure MySQL to accept external connections. Open the /etc/mysql/mysql.conf.d/mysqld.cnf file in a text editor and make sure the bind-address option is set as follows:

    bind-address = 0.0.0.0

  3. Connect to the default MySQL database as the root user:

    $ sudo mysql -u root

  4. Create a database named customers and connect to it:

    CREATE DATABASE customers;
USE customers;

  5. Create a table named customers and insert data into it:

    CREATE TABLE customers
(
    id INTEGER,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    address VARCHAR(255)
);

INSERT INTO customers (id, first_name, last_name, email, address)
VALUES (1,'John','Doe','john.doe@example.com','123 Elm Street'),
       (2,'Jane','Smith','jane.smith@example.com','456 Oak Street'),
       (3,'Bob','Brown','bob.brown@example.com','789 Pine Street'),
       (4,'Rob','Stuart','rob.stuart@example.com','119 Willow Street');

  6. Create a MySQL user named pxfuser:

    CREATE USER 'pxfuser' IDENTIFIED BY 'changeme';

  7. Assign pxfuser all privileges on the customers table, which will allow the user to read from and write data to the table. Then disconnect from MySQL:

    GRANT ALL PRIVILEGES ON customers to 'pxfuser';
\q

Configure the MySQL connector

  1. Log in to the Greengage DB master host as gpadmin.

  2. Download the MySQL JDBC driver from Maven Central and place it to the $PXF_BASE/lib directory:

    $ cd $PXF_BASE/lib
$ curl -O https://repo1.maven.org/maven2/com/mysql/mysql-connector-j/8.3.0/mysql-connector-j-8.3.0.jar

  3. Go to the $PXF_BASE/servers directory and create a JDBC server configuration directory named mysql-server:

    $ cd $PXF_BASE/servers
$ mkdir mysql-server

  4. Copy the jdbc-site.xml server configuration template file from the $PXF_HOME/templates/ directory to the created mysql-server directory:

    $ cp $PXF_HOME/templates/jdbc-site.xml $PXF_BASE/servers/mysql-server

  5. In the jdbc-site.xml file, provide the following configuration specifying jdbc.driver, jdbc.url, jdbc.user, and jdbc.password:

    <?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <property>
        <name>jdbc.driver</name>
        <value>com.mysql.jdbc.Driver</value>
        <description>Class name of the JDBC driver</description>
    </property>
    <property>
        <name>jdbc.url</name>
        <value>jdbc:mysql://mysqlserver:3306/customers</value>
        <description>The URL that the JDBC driver can use to connect to the database</description>
    </property>
    <property>
        <name>jdbc.user</name>
        <value>pxfuser</value>
        <description>User name for connecting to the database</description>
    </property>
    <property>
        <name>jdbc.password</name>
        <value>changeme</value>
        <description>Password for connecting to the database</description>
    </property>
</configuration>

  6. Synchronize the PXF server configuration to the Greengage DB cluster:

    $ pxf cluster sync

Create a readable external table

  1. On the Greengage DB master host, create a readable PXF external table that references the earlier created customers MySQL table. In the LOCATION clause, specify the remote table to connect to (customers), the profile used (jdbc), and the remote MySQL server (mysql-server). In the FORMAT clause, specify pxfwritable_import, which is the built-in custom formatter function for read operations:

    CREATE EXTERNAL TABLE pxf_customers_r (
        id INTEGER,
        first_name VARCHAR(50),
        last_name VARCHAR(50),
        email VARCHAR(100),
        address VARCHAR(255)    
    )
    LOCATION ('pxf://customers?PROFILE=jdbc&SERVER=mysql-server')
    FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');

  2. Query the created external table:

    SELECT * FROM pxf_customers_r;

    The output should look as follows:

     id | first_name | last_name |         email          |      address
----+------------+-----------+------------------------+-------------------
  1 | John       | Doe       | john.doe@example.com   | 123 Elm Street
  2 | Jane       | Smith     | jane.smith@example.com | 456 Oak Street
  3 | Bob        | Brown     | bob.brown@example.com  | 789 Pine Street
  4 | Rob        | Stuart    | rob.stuart@example.com | 119 Willow Street
(4 rows)

Create a writable external table

  1. On the Greengage DB master host, create a writable PXF external table that references the earlier created customers MySQL table. In the LOCATION clause, specify the remote table to connect to (customers), the profile used (jdbc), and the remote MySQL server (mysql-server). In the FORMAT clause, specify pxfwritable_export, which is the built-in custom formatter function for write operations:

    CREATE WRITABLE EXTERNAL TABLE pxf_customers_w ( 
        id INTEGER,
        first_name VARCHAR(50),
        last_name VARCHAR(50),
        email VARCHAR(100),
        address VARCHAR(255)
    )
    LOCATION ('pxf://customers?PROFILE=jdbc&SERVER=mysql-server')
    FORMAT 'CUSTOM' (FORMATTER='pxfwritable_export');

  2. Insert data into the pxf_customers_w table:

    INSERT INTO pxf_customers_w (id, first_name, last_name, email, address) 
VALUES (5, 'Alice', 'Johnson', 'alice.johnson@example.com', '101 Maple Avenue'),
       (6, 'David', 'Williams', 'david.williams@example.com', '222 Cedar Lane'),
       (7, 'Emily', 'Davis', 'emily.davis@example.com', '333 Birch Road'),
       (8, 'Michael', 'Wilson', 'michael.wilson@example.com', '444 Pine Court');

  3. On the MySQL server, query the initially created source customers table to view the data:

    SELECT * FROM customers;

    The output should look as follows:

    +------+------------+-----------+----------------------------+-------------------+
| id   | first_name | last_name | email                      | address           |
+------+------------+-----------+----------------------------+-------------------+
|    1 | John       | Doe       | john.doe@example.com       | 123 Elm Street    |
|    2 | Jane       | Smith     | jane.smith@example.com     | 456 Oak Street    |
|    3 | Bob        | Brown     | bob.brown@example.com      | 789 Pine Street   |
|    4 | Rob        | Stuart    | rob.stuart@example.com     | 119 Willow Street |
|    5 | Alice      | Johnson   | alice.johnson@example.com  | 101 Maple Avenue  |
|    6 | David      | Williams  | david.williams@example.com | 222 Cedar Lane    |
|    7 | Emily      | Davis     | emily.davis@example.com    | 333 Birch Road    |
|    8 | Michael    | Wilson    | michael.wilson@example.com | 444 Pine Court    |
+------+------------+-----------+----------------------------+-------------------+
8 rows in set (0.00 sec)
Previous
PostgreSQL