Create beautiful data with MariaDB SkySQL and Apache Zeppelin

See how easy it is to use containers to integrate BI tools with your SkySQL database. This blog was first published April 20, 2020 with instructions for Apache Zeppelin 0.8.2. We’ve refreshed it to reflect the latest version of Apache Zeppelin 0.9.0. If you need the old blog, you can find it here.

One of the key tenets of a database-as-a-service (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 cloud database.

If you don’t already have a SkySQL account there’s no better time than the present to sign up. And to sweeten the pot, we’re currently offering a $500 credit to let you try it out for free, so what are you waiting for?

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.9.0

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. Add a MariaDB dependency by adding “org.mariadb.jdbc:mariadb-java-client:” followed by the target JDBC client version. Supported versions can be found here.

 

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

7. Finally, add 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)

Jump back into the Apache Zeppelin Docker container instance by opening up a local terminal window and execute the following command.

$ docker exec -it zeppelin bash

Create a new directory to house the MariaDB certificate authority chain.

$ mkdir skysql

Download the certificate chain.

$ wget -O skysql/skysql_chain.pem 
https://supplychain.www.kablowie.com/skysql_chain.pem

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 %<interpreter_name>. 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: http://www.kablowie.com/products/skysql/docs/

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