Using Zeppelin to Access MySQL

If you want to access MySQL Cloud Service using Zeppelin of Oracle Big Data Cloud Service Compute Edition (BDCSCE), you can use Spark DataFrames or Zeppelin interpreters. In this blog post, I’ll show how we can edit JDBC interpreter to connect MySQL Cloud Service.

First login to Oracle Big Data Cloud console, and go to “settings” tab, and open “notebook” section. You’ll see the interpreter settings. Search for “jdbc” and click “edit” button to edit the interpreter settings.

Oracle Solaris and unixODBC

In a project, I’m asked to create a database link between Oracle to MySQL. I thought that I can use Oracle’s Heterogeneous Services. Oracle Database is running on Sparc Server (Solaris 10), so I downloaded the free ODBC packages from unixODBC project. They already provide a binary package for Solaris but it says it will remove any non pkgmgr content in “/usr/local/bin” directory. So I decided to download the source codes and build it manually. Although it sounds like a scientific operation, you just need to run 3 commands to build and install the package in normal conditions but oh unfortunately it took a lot of time to fix “library” dependencies problem and adding MySQL support to unixODBC.

Here are the commands to build unixODBC:

Configuring Oracle SQL Developer for Microsoft SQL Server and MySQL

Most of DBAs I know, use TOAD for doing daily tasks, but I prefer Oracle SQL Developer. In my opinion, it has 3 important advantages against TOAD:

  • It’s platform-independent: Although I use Windows on my laptop now, I’m a big fan of Linux and Solaris, and I don’t like being dependent on a specific OS. Thanks to Java, Oracle SQL Developer is platform-independent.
  • It supports multiple databases: You can use SQL Developer for Microsoft Access, Microsoft SQL Server, MySQL, TimesTen, DB2 and (of course) Oracle.
  • It’s extendible: Yes I know that there are not too much documents about it, but it’s extensible. For example, check FourthElephant’s extensions for SQL Developer: http://www.fourthelephant.com/sqldeveloper/download/

TOAD has a better interface because of using native Windows components but you can get used to SQL Developer if you spend time on it. Anyway, this blog has nothing to do with comparing SQL Developer and TOAD. I’ll just show how to configure SQL Developer to connect Microsoft SQL Server and MySQL.

Split String Function in MySQL

I saw a question about how to split delimited string in MySQL. MySQL does not include a function to split a delimited string. So I decided to write a simple one:

When we call this function as SPLIT( ‘ali,ahmet,mehmet’, ‘,’, 3 ), we get the 3rd item “mehmet”. If we call this function as SPLIT( ‘ali,ahmet,mehmet’, ‘,’, 5 ), it will return an empty string.

MINUS and INTERSECT in MySQL

MySQL doesn’t support the INTERSECT and MINUS set operators. The INTERSECT operator takes the results of two queries and returns only rows that appear in both result sets. The MINUS operator takes the distinct rows of one query and returns the rows that do not appear in a second result set. We can rewrite these queries by using JOIN operator:

Sample query with the MINUS operator:

In MySQL: