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 passwordmy-secret-pw
to it:> CREATE USER 'mysql-user' IDENTIFIED BY 'my-secret-pw';
-
Assign user
mysql-user
all privileges on tablenames
, and exit themysql
subsystem:> GRANT ALL PRIVILEGES ON mysqltestdb.names TO 'mysql-user'; > exit
With these privileges,
mysql-user
can read from and write to thenames
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 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
. Thejdbc-site.xml
file contents should look similar to the following (substitute your MySQL host system formysqlserverhost
):<?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
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 thenames
MySQL table:gpadmin=# SELECT * FROM names_in_mysql; id | name | last ----+------------+-------- 1 | John | Smith 2 | Mary | Blake 3 | Muhammad | Ali (3 rows)
Content feedback and comments