Skip to content

Tutorial: Using Apache Zeppelin with MySQL

I’ve been playing with Apache Zeppelin for a little while now, and have been really impressed.  If you aren’t familiar with Zeppelin,  it is a tool for creating interactive notebooks to visualize data.  With the latest version, Zeppelin includes an interpreter for PostgreSQL and I discovered that you can use this interpreter to connect Zeppelin to a MySQL server and quickly visualize your data.

Step 1:  Install the MySQL JDBC Driver

The first step is to install the JDBC driver and verify that your database can connect via JDBC.  You’ll need to download the MySQL JDBC driver here.  After downloading this file and unzipping it, copy the .JAR file into your classpath.  On a Mac, copy the mysql-connector-java-<version>-bin.jar file into /Library/Java/Home/lib.

Next, I would recommend testing the connection to verify that works.  To do that, first start MySQL using the control panel in the system settings:

Start MySQLNext, download the SQuirrel SQL Client, and add a connection to the database.

SQuirrel SQL with MySQL

Click on test and SQuirrel SQL should be able to connect to your database.

Step 2:  Add an Interpreter in Zeppelin

Now that your JDBC connection is working, the next step is to create and configure a Zeppelin interpreter for MySQL.  Once you’ve opened up Zeppelin, click on the Interpreter link at the top of the screen.  Click on create and populate the form as shown below.

Screen Shot 2015-11-20 at 00.23.54

The fields you’ll need to fill in are:

  • Interpreter: psql
  • postgresql.driver.name: com.mysql.jdbc.Driver
  • postgresql.url: jdbc:mysql://localhost:3306/ or jdbc:mysql://<your host>:3306/

Of course, you’ll have to fill out the username/password fields.  There is an optional database name which you can put as follows: jdbc:mysql://<your host>:3306/<database>.

Step 3:  Activate the MySQL Interpreter

The next step is to bind the MySQL interpreter to your notebook.  Click on the gear icon in the upper right corner and make sure that the MySQL interpreter is on.  You might want to drag it to the top of your interpreter list. Screen Shot 2015-11-20 at 00.29.51

Step 4:  Run Your Query

The final step is to execute a query.  If you didn’t already specify which database you are using, enter the following into the first Zeppelin text window:

%sql
USE <database name>;

Once you’ve specified the database, you can simply query the database in Zeppelin and it should work.

Screen Shot 2015-11-20 at 00.35.10Conclusion

In conclusion, Zeppelin’s PostgreSQL connector really is a generic JDBC connector and will allow you to connect to any JDBC compliant database.

Share the joy

7 Comments

  1. Leo Leo

    Hey this tutorial is mistitled. It explains how to connect to postgres (which I used)

  2. Priyanka Priyanka

    Awesome Guide, i could add mysql interpreter as you explained.. but if i run the use Database it is throwing java.lang.NullPointerException may be some problem with the mysql interpreter .. what i could figure out is after adding mysql interpreter in your document its showing mysql(%sql) , but in mine its showing as mysql(%psql).
    is this creating a problem?? i dont know how to do.. i removed the created mysql interpreter and addedagain.. still its coming same as mysql(%psql). i could connect with SQuirrel SQL Client and same driver i have given in this.. please tell me where is the problem.. im breaking my head to solve this..
    Thank you

    • d!rtY d!rtY

      i have the same problem, cant see the %sql .. only %psql.. so the null pointer is always coming . Any idea ?

    • Charles Givre Charles Givre

      I imagine the process has changed a little as I think the most current version of Zeppelin is 0.7.

  3. Rajeev Limaye Rajeev Limaye

    How anyone can their MySql database whose having ssh connection in zeppelin.

Leave a Reply to Charles Givre Cancel reply

Your email address will not be published. Required fields are marked *