11.19. Generic JDBC Connector

The Generic JDBC connector allows querying and creating tables in an external database that Presto does not have a dedicated connector for. This can be used for proof-of-concept deployments of federation use-cases, enabling joining multiple data sources. For production deployments a dedicated supported connector should be used.

Warning

Due to its generic nature, this connector may or may not work with a particular database or deployment scenario. In particular, you should expect the connector to handle at most only a subset of data types available in the external database.

Installation and configuration

The Generic JDBC connector requires a JDBC 4.2 driver dedicated for the external database. No JDBC drivers are shipped with the connector. The installation steps assume you have the external database already running and that you have the appropriate JDBC driver jar.

  1. Locate generic-jdbc directory under Presto’s plugin directory. When Presto is installed from RPM, this is /usr/lib/presto/lib/plugin/generic-jdbc.
  2. Add the appropriate JDBC driver jar file to the generic-jdbc directory.
  3. Locate etc/catalog directory containing configuration for Presto catalogs. When Presto is installed from RPM, this is /etc/presto/catalog.
  4. Create some_catalog.properties file in the catalogs configuration directory (replace some_catalog with your database name or some other descriptive name of the catalog) with the following contents:
connector.name=generic-jdbc

# the name of the Driver class. E.g. for Postgres this would be org.postgresql.Driver
generic-jdbc.driver-class=FULLY_QUALIFIED_NAME_OF_THE_DRIVER_CLASS

# JDBC connection URL. E.g. for Postgres this could be jdbc:postgresql://example.net:5432/database?defaultRowFetchSize=10000
connection-url=JDBC_CONNECTION_URL_AS_APPROPRIATE_FOR_THE_EXTERNAL_DATABASE_DRIVER

# connection credentials
connection-user=USERNAME
connection-password=PASSWORD

# additional configuration
case-insensitive-name-matching=true
  1. Perform the above steps on every Presto cluster node.
  2. Restart Presto on every node.

Multiple External Databases

If you want to connect to multiple external databases using the Generic JDBC connector, follow these steps:

  1. Add appropriate JDBC driver jars to the generic-jdbc directory under Presto’s plugin directory for all database servers you are going to connect to. If two external databases use the same driver jar, you do not need to add the driver jar twice.
  2. Define additional catalogs in Presto catalogs configuration directory, by creating some_additional_catalog.properties file (replacing some_additional_catalog with some descriptive name of the catalog).

Limitations

Data types

Support for data types depends on the data types in the external database and how the data types are reported by the JDBC driver of the external database. Actual support may vary from database to database. The Generic JDBC connector will hide columns which data type it considered unsupported. In the extreme case, this may mean that no columns are visible, if all data types being used are unsupported.

If you want to query data with unsupported types, you can define a view in your external database, converting unsupported data types to supported ones (converting to varchar is usually a good choice). Then you should query this view from Presto, instead of the underlying table.

Statements

Some statements are not supported because they are not supported by all Presto JDBC-based connectors. Other statements are supported on a “best effort” basis. They may or may not work, depending on the external database and the driver being used.