Apache Zeppelin is a web-based notebook for data ingestion, exploration, visualization, sharing collaborative features of Hadoop ecosystem. It has a concept called “interpreter”, a language backend that enables various data sources to be plugged into Zeppelin. BigInsights/BigSQL can use JDBC driver like DB2 as a mean of communication to Zeppelin. The steps below will help users to configure Zeppelin to access BigSQL.
Pre-requisite: Setup Zeppelin notebook with BigInsights 4.2
It is a requirement that Zeppelin notebook is properly setup to talk to BigInsights before going through the actual steps below. Refer to this blog https://developer.ibm.com/hadoop/2016/05/21/setup-zeppelin-biginsights/ where it’s tested in BigInsights 4.1.x but still applies to BigInsights 4.2 with some minor checking made:
1. Make sure JAVA_HOME is defined
e.g. export JAVA_HOME=/usr/jdk64/java-1.8.0-openjdk-1.8.0.77-0.b03.el6_7.x86_64/
If wrong JDK is defined, or JRE is set as java home, Maven throws dependency errors when creating a package.
2. (Optional) Allot Maven more JVM memory if necessary
e.g. export MAVEN_OPTS ="-Xmx512m -XX:MaxPermSize=350m
Note: The following steps are tested in RHEL 6.x platform with Firefox 45.2. Zeppelin is installed in the BigSQL head node.
Steps:
1. Once Zeppelin has started from issuing a command zeppelin-daemon.sh –start, next thing to do is to launch Zeppelin UI then, click on “Create a new note”. In this example, “Sample2-db2 JDBC” notebook is created.
2. Zeppelin comes with common and pre-defined “Interpreter” like for Python, Spark, PySpark etc. For BigSQL, it has to be setup separately just like setting JDBC connection from an external applications like IBM Data Studio.
Navigate to the menu at the upper right hand corner of the UI page, select “Interpreter”, then select “Create”.
3. Fill-up the minimum required fields. In this example, the interpreter is named “db2” and interpreter group is “jdbc”, option field is set by default to “shared”. Once done, click “Save”
For reference, see required parameters when creating BigSQL JDBC connection https://www.ibm.com/support/knowledgecenter/SSPT3X_4.2.0/com.ibm.swg.im.infosphere.biginsights.biga.doc/doc/biga_connect_migrate.html
Note: Important step, need to supply db2jcc.jar as artifact (bottom).
4. Back to notebook, select “Settings” (gear icon at the upper right hand corner). Make sure the newly created interpreter is at the top of the list and enabled (click to enable – turns blue, click again to disable). Once done, click on “Save”.
5. Now, try running some queries using CREATE, LOAD, SELECT from HADOOP tables, notice that “%sql” prefix is used.
