Example: Reading From and Writing to a MySQL Table

In this example, you:

Create a MySQL database and table, and insert data into the table
Create a MySQL user and assign all privileges on the table to the user
Configure the PXF JDBC connector to access the MySQL database
Create a PXF readable external table that references the MySQL table
Read the data in the MySQL table using PXF
Create a PXF writable external table that references the MySQL table
Write data to the MySQL table using PXF
Read the data in the MySQL table again

Create a MySQL Table

Perform the following steps to create a MySQL table named names in a database named mysqltestdb, and grant a user named mysql-user all privileges on this table:

Identify the host name and port of your MySQL server.
Connect to the default MySQL database as the root user:
```
$ mysql -u root -p
```
Create a MySQL database named mysqltestdb and connect to this database:
```
> CREATE DATABASE mysqltestdb;
> USE mysqltestdb;
```

Create a table named names and insert some data into this table:

> CREATE TABLE names (id int, name varchar(64), last varchar(64));
> INSERT INTO names values (1, 'John', 'Smith'), (2, 'Mary', 'Blake');

Create a MySQL user named mysql-user and assign the password my-secret-pw to it:
```
> CREATE USER 'mysql-user' IDENTIFIED BY 'my-secret-pw';
```
Assign user mysql-user all privileges on table names, and exit the mysql subsystem:
```
> GRANT ALL PRIVILEGES ON mysqltestdb.names TO 'mysql-user';
> exit
```
With these privileges, mysql-user can read from and write to the names table.

Configure the MySQL Connector

You must create a JDBC server configuration for MySQL, download the MySQL driver JAR file to your system, copy the JAR file to the PXF user configuration directory, synchronize the PXF configuration, and then restart PXF.

This procedure will typically be performed by the Greenplum Database administrator.

Log in to the Greenplum Database master host:
```
$ ssh gpadmin@<gpmaster>
```
Download the MySQL JDBC driver and place it under $PXF_BASE/lib. If you relocated $PXF_BASE, make sure you use the updated location. You can download a MySQL JDBC driver from your preferred download location. The following example downloads the driver from Maven Central and places it under $PXF_BASE/lib:
1. If you did not relocate $PXF_BASE, run the following from the Greenplum master:
```
gpadmin@gpmaster$ cd /usr/local/pxf-gp<version>/lib
gpadmin@gpmaster$ wget https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.21/mysql-connector-java-8.0.21.jar
```
2. If you relocated $PXF_BASE, run the following from the Greenplum master:
```
gpadmin@gpmaster$ cd $PXF_BASE/lib
gpadmin@gpmaster$ wget https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.21/mysql-connector-java-8.0.21.jar
```

Synchronize the PXF configuration, and then restart PXF:

gpadmin@gpmaster$ pxf cluster sync
gpadmin@gpmaster$ pxf cluster restart

Create a JDBC server configuration for MySQL as described in Example Configuration Procedure, naming the server directory mysql. The jdbc-site.xml file contents should look similar to the following (substitute your MySQL host system for mysqlserverhost):

<?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://mysqlserverhost:3306/mysqltestdb</value>
        <description>The URL that the JDBC driver can use to connect to the database</description>
    </property>
    <property>
        <name>jdbc.user</name>
        <value>mysql-user</value>
        <description>User name for connecting to the database</description>
    </property>
    <property>
        <name>jdbc.password</name>
        <value>my-secret-pw</value>
        <description>Password for connecting to the database</description>
    </property>
</configuration>

Synchronize the PXF server configuration to the Greenplum Database cluster:
```
gpadmin@gpmaster$ pxf cluster sync
```

Read from the MySQL Table

Perform the following procedure to create a PXF external table that references the names MySQL table that you created in the previous section, and reads the data in the table:

Create the PXF external table specifying the jdbc profile. For example:

gpadmin=# CREATE EXTERNAL TABLE names_in_mysql (id int, name text, last text)
          LOCATION('pxf://names?PROFILE=jdbc&SERVER=mysql')
          FORMAT 'CUSTOM' (formatter='pxfwritable_import');

Display all rows of the names_in_mysql table:

gpadmin=# SELECT * FROM names_in_mysql;
 id |   name    |   last  
----+-----------+----------
  1 |   John    |   Smith
  2 |   Mary    |   Blake
(2 rows)

Write to the MySQL Table

Perform the following procedure to insert some data into the names MySQL table and then read from the table. You must create a new external table for the write operation.

Create a writable PXF external table specifying the jdbc profile. For example:

gpadmin=# CREATE WRITABLE EXTERNAL TABLE names_in_mysql_w (id int, name text, last text)
          LOCATION('pxf://names?PROFILE=jdbc&SERVER=mysql')
          FORMAT 'CUSTOM' (formatter='pxfwritable_export');

Insert some data into the names_in_mysql_w table. For example:

=# INSERT INTO names_in_mysql_w VALUES (3, 'Muhammad', 'Ali');

Use the names_in_mysql readable external table that you created in the previous section to view the new data in the names MySQL table:

gpadmin=#  SELECT * FROM names_in_mysql;
 id |   name     |   last  
----+------------+--------
  1 |   John     |   Smith
  2 |   Mary     |   Blake
  3 |   Muhammad |   Ali
(3 rows)