6.15. MySQL Connector

The MySQL connector allows querying and creating tables in an externalMySQL database. This can be used to join data between differentsystems like MySQL and Hive, or between two different MySQL instances.

Configuration

To configure the MySQL connector, create a catalog properties filein etc/catalog named, for example, mysql.properties, tomount the MySQL connector as the mysql catalog.Create the file with the following contents, replacing theconnection properties as appropriate for your setup:

  1. connector.name=mysql
  2. connection-url=jdbc:mysql://example.net:3306
  3. connection-user=root
  4. connection-password=secret

Multiple MySQL Servers

You can have as many catalogs as you need, so if you have additionalMySQL servers, simply add another properties file to etc/catalogwith a different name (making sure it ends in .properties). Forexample, if you name the property file sales.properties, Prestowill create a catalog named sales using the configured connector.

Querying MySQL

The MySQL connector provides a schema for every MySQL database.You can see the available MySQL databases by running SHOW SCHEMAS:

  1. SHOW SCHEMAS FROM mysql;

If you have a MySQL database named web, you can view the tablesin this database by running SHOW TABLES:

  1. SHOW TABLES FROM mysql.web;

You can see a list of the columns in the clicks table in the web databaseusing either of the following:

  1. DESCRIBE mysql.web.clicks;
  2. SHOW COLUMNS FROM mysql.web.clicks;

Finally, you can access the clicks table in the web database:

  1. SELECT * FROM mysql.web.clicks;

If you used a different name for your catalog properties file, usethat catalog name instead of mysql in the above examples.

MySQL Connector Limitations

The following SQL statements are not yet supported: