In this example, you:
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.
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 coordinator host:
$ ssh gpadmin@<coordinator>
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
:
If you did not relocate $PXF_BASE
, run the following from the Greenplum coordinator:
gpadmin@gcoord$ cd /usr/local/pxf-gp<version>/lib
gpadmin@coordinator$ wget https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.21/mysql-connector-java-8.0.21.jar
If you relocated $PXF_BASE
, run the following from the Greenplum coordinator:
gpadmin@coordinator$ cd $PXF_BASE/lib
gpadmin@coordinator$ 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@coordinator$ pxf cluster sync
gpadmin@coordinator$ 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@coordinator$ pxf cluster sync
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)
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)