Generic JDBC Interpreter for Apache Zeppelin

Overview

This interpreter lets you create a JDBC connection to any data source, by now it has been tested with:

• Postgres
• MySql
• MariaDB
• Redshift
• Apache Hive
• Apache Phoenix
• Apache Drill (Details on using Drill JDBC Driver)
• Apache Tajo

If someone else used another database please report how it works to improve functionality.

Create Interpreter

When you create a interpreter by default use PostgreSQL with the next properties:

It is not necessary to add driver jar to the classpath for PostgreSQL as it is included in Zeppelin.

Simple connection

Prior to creating the interpreter it is necessary to add maven coordinate or path of the JDBC driver to the Zeppelin classpath. To do this you must edit dependencies artifact(ex. mysql:mysql-connector-java:5.1.38) in interpreter menu as shown:

To create the interpreter you need to specify connection parameters as shown in the table.

Multiple connections

JDBC interpreter also allows connections to multiple data sources. It is necessary to set a prefix for each connection to reference it in the paragraph in the form of %jdbc(prefix). Before you create the interpreter it is necessary to add each driver's maven coordinates or JDBC driver's jar file path to the Zeppelin classpath. To do this you must edit the dependencies of JDBC interpreter in interpreter menu as following:

You can add all the jars you need to make multiple connections into the same JDBC interpreter. To create the interpreter you must specify the parameters. For example we will create two connections to MySQL and Redshift, the respective prefixes are default and redshift:

Bind to Notebook

In the Notebook click on the settings icon at the top-right corner. Use select/deselect to specify the interpreters to be used in the Notebook.

More Properties

You can modify the interpreter configuration in the Interpreter section. The most common properties are as follows, but you can specify other properties that need to be connected.

To develop this functionality use this method. For example if a connection needs a schema parameter, it would have to add the property as follows:

Examples

Hive

Properties

Dependencies

Phoenix

Phoenix supports thick and thin connection types:

• Thick client is faster, but must connect directly to ZooKeeper and HBase RegionServers.
• Thin client has fewer dependencies and connects through a Phoenix Query Server instance.

Use the appropriate phoenix.driver and phoenix.url for your connection type.

Properties:

Dependencies:

Include the dependency for your connection type (it should be only one of the following).

Tajo

Properties

Dependencies

How to use

Reference in paragraph

Start the paragraphs with the %jdbc, this will use the default prefix for connection. If you want to use other connection you should specify the prefix of it as follows %jdbc(prefix):

%jdbc
SELECT * FROM db_name;

or

%jdbc(prefix)
SELECT * FROM db_name;

Apply Zeppelin Dynamic Forms

You can leverage Zeppelin Dynamic Form inside your queries. You can use both the text input and select form parametrization features

%jdbc(prefix)
SELECT name, country, performer
FROM demo.performers
WHERE name=''

Bugs & Reporting

If you find a bug for this interpreter, please create a JIRA ticket.