HDP, Zeppelin and BigSQL - stanislawbartkowski/wikis GitHub Wiki
Introduction
This article describes how to connect Zeppelin Spark2/Scala notebook with IBM BigSQL
Configure Interpreter
Spark2 Interpreter should be configured to include DB2 JDBC driver. Localize db2jcc4.jar file.
locate db2jcc4.jar
If the driver does not exist on the Zeppelin node, copy the file from BigSQL Head or Worker node and put in /usr/share/java directory
ls /usr/share/java/db2jcc4.jar
/usr/share/java/db2jcc4.jar
Then configure the Intepreter. In Zeppelin -> Interpreter -> spark2, add spark.jars property pointing to the driver jar file.
Load BigSQL table as DF
Collect data to prepare DB2 JDBC URL
| Property | Description | Example |
|---|---|---|
| Host | BigSQL Head hostname | aa1.fyre.ibm.com |
| Port | BigSQL port | 32051 |
| User | BigSQL user | bigsql |
| Password | BigSQL user password | **** |
| Database | BigSQL database to connect to | BIGSQL |
Verify that DB2 JDBC driver jar is properly located
Class.forName("com.ibm.db2.jcc.DB2Driver") Prepare URL val jdbcUsername = "bigsql" val jdbcPassword = ***** val jdbcHostname = "aa1.fyre.ibm.com" val jdbcPort = 32051 val jdbcDatabase ="bigsql" val jdbcUrl = s"jdbc:db2://${jdbcHostname}:${jdbcPort}/${jdbcDatabase}:user=${jdbcUsername};password=${jdbcPassword};" val connProp = new java.util.Properties() connProp.setProperty("driver", "com.ibm.db2.jcc.DB2Driver")
Load table, here PERFDB.CUSTOMER
val customerDF = spark.read.jdbc(jdbcUrl, "PERFDB.CUSTOMER", connProp).cache
Run several commands to make sure that the table is loaded
customerDF.show customerDF.createOrReplaceTempView("CUSTOMER") %sql SELECT COUNT(*) FROM CUSTOMER