Create beautiful data with MariaDB SkySQL and Apache Zeppelin

This blog post was refreshed in July 2020 to reflect the latest version of Apache Zeppelin 0.9.0. Read the latest here

We recently announced the immediate availability of MariaDB SkySQL, the first DBaaS (database-as-a-service) to unlock the full power of MariaDB Platform on an industry-leading, cloud-native architecture.

One of the key tenets of a DBaaS is simplicity. Not only should a DBaaS be easy to set up, but it should also be easy to use. Not only easy to use, but easy to use with tools that help us visualize and conceptualize our data. In this article you’ll learn just how easy it is to integrate with MariaDB SkySQL.

If you don’t already have a SkySQL account there’s no better time than the present to sign up.

You can read through the demo and see theoretically how easy it is to integrate BI tools with your SkySQL database, but to test it out, you need a SkySQL instance, either transactional or analytical, up and running. Even an instance without data will work for our purposes. You’ll also need to have Docker installed because we are going to use it to run a Zeppelin container. (I’ll talk more about containerization later.)

Apache Zeppelin

For this walkthrough you’ll be using Apache Zeppelin, an open-source, web-based “notebook” application to integrate directly with MariaDB SkySQL. Zeppelin enables you to visualize your data (row-based or column-based) using a variety of tables, graphs and charts.

In Zeppelin, a notebook is simply a collection of notes, which is a collection of “paragraphs”. Each paragraph then uses an interpreter to connect to and communicate with a datasource, which in this case, as I mentioned before, is MariaDB SkySQL. Personally, I’m more of a visual learner so I think it helps conceptualize everything with the image below.

Using MariaDB SkySQL, Apache Zeppelin allows you to make beautiful, data-driven, interactive documents (notes) using SQL. In the following walkthrough we’ll focus on setting up Apache Zeppelin to integrate with MariaDB SkySQL to enable you to create dynamic visualizations using your data.

Getting Zeppelin

There are three options for installing Apache Zeppelin on your machine.

1. Downloading a pre-built binary package from the Zeppelin archive.
2. Running the official Apache Zeppelin Docker image to launch a container.
3. Building from source.

We’ll be using option #2, as it’s fairly straightforward and allows us to walk through the same steps for installation and configuration regardless of operating system. The reason for that is rooted in the concept of containerization. More specifically we’ll be using Docker and Dockerhub, so make sure you have Docker installed before moving on.

Launch an Apache Zeppelin container.

docker run -p 8080:8080 -e ZEPPELIN_ADDR=0.0.0.0 --name zeppelin apache/zeppelin:0.8.2

That’s it, when the pull is complete and the container has been created, you have a local instance of Apache Zeppelin running on your machine!

Now you can jump into the Zeppelin portal by opening a browser window and navigating to http://localhost:8080.

Configuring Zeppelin to use MariaDB SkySQL

The Zeppelin portal is fairly straightforward. Using the “Notebook” menu you can create a new note. But first you’ll need to create a MariaDB interpreter. Apache Zeppelin comes with several interpreters, but MariaDB is not one of them. Luckily creating a new interpreter is very straightforward.

1. Select “Interpreter” within the (anonymous) user menu in the top right corner of the window.

2. On the Interpreters page, click the “+ Create” button at the top right corner of the page.

3. Give the interpreter a name, and select “jdbc” as the interpreter group*.

* The MariaDB interpreter will use MariaDB Connector/J, which adheres to the JDBC API. For more information on how creating a generic JDBC interpreter is possible in Apache Zeppelin please check out the official documentation.

4. Fill in the following properties information. For the default.url, substitute the Fully Qualified Domain Name of your SkySQL instance for skysql_domain, and substitute the port number given on your SkySQL instance for skysql_port. This information is available by clicking on the database name in the “Your Services” page in SkySQL. Your password and default user name are in Connection Details.

default.driver org.mariadb.jdbc.Driver
default.password (Your MariaDB SkySQL Password)
default.url  jdbc:mariadb://skysql_domain:skysql_port?useSSL=true&serverSslCert=/zeppelin/skysql/skysql_chain.pem
default.user (Your MariaDB SkySQL User)

The default.url will look similar to the following but with your information in it:

jdbc:mariadb://sky0001247.mdb0001361.db.skysql.net:5001?useSSL=true&serverSslCert=/zeppelin/skysql/skysql_chain.pem

5. Click the “Save” button at the bottom of the interpreter form.

But, wait, it can’t be that easy, can it? No, you’re right, it isn’t, but you’re very close to being done. To use a MariaDB SkySQL database instance you’ll need to add two things.

1. The certificate authority chain file. (The portion serverSslCert=/zeppelin/skysql/skysql_chain.pem within the default.url value above indicates where you will be adding the certificate authority chain file so that the new MariaDB interpreter can access it)

2. The .jar file of MariaDB Connector/J.

To add those items, you’ll need to jump into the Apache Zeppelin Docker container instance. So, open up a local terminal window and execute the following command.

$ docker exec -it zeppelin bash

Download the Connector/J .jar file to interpreter/jdbc

$ wget -O interpreter/jdbc/mariadb-java-client-2.6.0.jar https://downloads.mariadb.com/Connectors/java/connector-java-2.6.0/mariadb-java-client-2.6.0.jar

Then create a new directory to house the MariaDB certificate authority chain.

$ mkdir skysql

Download the certificate.

$ wget -O skysql/skysql_chain.pem 
https://mariadb.com/.well-known/skysql_chain.pem

Finally, head back to the browser and edit your mariadb interpreter by adding the path to your .jar file as a new artifact under the “Dependencies” section

/zeppelin/interpreter/jdbc/mariadb-java-client-2.6.0.jar

to the interpreter you created, and click the “Save” button at the bottom of the interpreter edit form.

Bringing it all together

Now that you have an interpreter for MariaDB SkySQL you’re ready to create a new note!

Simply click “Create new note”, located within the “Notebook” menu on the top left navigation bar.

Name your new note, select “jdbc” as the Default Interpreter, and click the “Create” button.

Within each paragraph you will need to reference the name of the interpreter you’ve created with the format of %. For example, %mariadb. Then supply a SQL query including fully qualified domain names (e.g. accounting.employees) for your database running on SkySQL.

Finally, execute the query by clicking the run (triangle) button located at the top right corner of the paragraph.

If you are testing the setup using a SkySQL instance without a database, try a note using SHOW databases;

Next steps

Thanks for taking the time to read through this tutorial! If you’d like to learn more I’ve created a few other apps you can try for yourself, see: https://github.com/mariadb-corporation/Developer-Examples

SkySQL documentation is available at: https://mariadb.com/products/skysql/docs/

You can Get Started today with SkySQL.

And I would welcome your feedback on GitHub or by Twitter (@mariadb).