How to use Zeppelin to display MySQL data

1. Configure Zeppelin to support MySQL
Zeppelin native supports JDBC links. However, the default setting is to point to PostgreSQL.

One of the simplest ways is to directly modify the content in JDBC Interpreter and direct it to your own MySQL instance. As shown in the following figure:

 

You must set the following parameters:

Default. user
Default. password
Default. url
Default. driver

In addition, you need to add JDBC artifict for MySQL. I used: mysql-connector-java: 5.1.38

 

After the preceding configuration is complete, restart interpreter enables the configuration to take effect.

2. Test the knife

Create a new NoteBook, for example, Daily Report, and confirm that jdbc interpreter has been used. (For confirmation methods, refer to the previous article)

Then you can call % jdbc to compile the SQL statement and display the result.

For example:

 

Here we can write SQL statements and display the data in a pie chart. You can also set a Title

In addition, we can share the entire report to colleagues in other project teams. We only need to select the "report" option below.

  

You can also share a paragraph directly (you can create a paragraph required for page integration)

Zeppeline-link-to-this-paragraph

 

3. Some pitfalls to bypass
I have to say that Zeppelin is not stable enough, but there are some pitfalls. At present, I have no way to fill it out and can only go around

3.1 An error occurred while creating an independent mysql interpreter.
I tried to create a separate MySQL Interpreter. But I always encountered various errors, such:

Prefix not found
Interpreter not found
And so on.

Solution: directly modify JDBC instead of creating a new one.

3.2 inexplicably, I need Login
Zeppelin is developed for 0.0.0.0 by default and is accessed anonymously by default.

However, sometimes Zeppelin cannot be restored even if it is recovered after some wrong configurations, and I still need to log on. The problem is that I have never set the account password...

Solution: restart Zeppelin-> restart the machine-> re-configure

For the above three solutions, try T_T one by one.

 

3.3 bar chart displayed, automatically sorted by X coordinate
That is to say, the order of the content of the x coordinates displayed is not in the order of the given values.

This problem has not been solved in 0.6.1. PR has been provided on GitHub, but it has not been merged due to conflicts.

The following figure shows the problem:

 

 

As you can see, I have forced order by count desc in SQL, that is, I want to display charts in descending order by number of people. However, the result is automatically sorted by key (build_num_oem.

The reason is that Zeppelin does not take the order into consideration when feeding the data to Pivort. js for data display. You need to add another Index to solve this problem.

Specific can refer to: https://github.com/apache/zeppelin/pull/83

The old solution: persuade your customers that the problem will be solved soon.

 

4. Conclusion:

Zeppelin is a very promising project. Currently, it has helped small teams solve many problems.

But the main problem is stability.

Hope Zeppelin can be improved for a long time

Finally, put a completed figure: