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:
Next, download the SQuirrel SQL Client, and add a connection to the database.
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.
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.
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:
USE <database name>;
Once you’ve specified the database, you can simply query the database in Zeppelin and it should work.
In conclusion, Zeppelin’s PostgreSQL connector really is a generic JDBC connector and will allow you to connect to any JDBC compliant database.
Hey this tutorial is mistitled. It explains how to connect to postgres (which I used)
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..
i have the same problem, cant see the %sql .. only %psql.. so the null pointer is always coming . Any idea ?
[…] なので、こちらの記事を参考にMysql接続設定を行う。 […]
you must edit dependencies artifact(ex. mysql:mysql-connector-java:5.1.38) in interpreter menu as shown
I imagine the process has changed a little as I think the most current version of Zeppelin is 0.7.
How anyone can their MySql database whose having ssh connection in zeppelin.