Wednesday, June 21, 2017

Connecting to Redshift from KDB+ (Using Babel)

If you want to connect to Oracle from KDB+ refer to my "Connecting to Oracle from KDB+ (Using Babel)" post.

Let's assume your q home is /opt/q


$ mkdir babel
$ cd babel

Download babel.zip & unzip it

$ wget https://github.com/CharlesSkelton/babel/raw/master/dist/babel.zip

$ unzip babel.zip

You will see babel.jar & lib folder
$ ls -la
total 4664
drwxr-xr-x   5 balaji  staff      170 Jun 21 17:53 .
drwxr-xr-x@ 10 balaji  staff      340 Jun 21 17:52 ..
-rw-r--r--   1 balaji  staff    27713 Mar 24  2014 babel.jar
-rw-r--r--@  1 balaji  staff  2357704 Jun 21 17:52 babel.zip
drwxr-xr-x   8 balaji  staff      272 Jun 21 17:57 lib

Goto lib and download Amazon Redshift jdbc driver
$cd lib
$ wget https://s3.amazonaws.com/redshift-downloads/drivers/RedshiftJDBC42-1.2.1.1001.jar
$ ls -la
total 9392
drwxr-xr-x  8 balaji  staff      272 Jun 21 17:57 .
drwxr-xr-x  5 balaji  staff      170 Jun 21 17:53 ..
-rw-r--r--@ 1 balaji  staff  2316196 Nov 17  2016 RedshiftJDBC42-1.2.1.1001.jar
-rw-r--r--  1 balaji  staff    21986 Mar 24  2014 csvjdbc.jar
-rw-r--r--  1 balaji  staff   706710 Mar 24  2014 hsqldb.jar
-rw-r--r--  1 balaji  staff   463412 Mar 24  2014 janino.jar
-rw-r--r--  1 balaji  staff   703265 Mar 24  2014 mysql-connector-java-5.1.6-bin.jar

-rw-r--r--  1 balaji  staff   583286 Mar 24  2014 sqljdbc.jar

Here is the scary part. Including Redshift driver (lib/RedshiftJDBC42-1.2.1.1001.jar) into babel.jar
http://code.kx.com/wiki/Babel#Specifying_your_jdbc_driver_and_repacking_the_jar
$ jar -xvf babel.jar
  created: META-INF/
 inflated: META-INF/MANIFEST.MF
  created: de/
  created: de/skelton/
  created: de/skelton/babel/
  created: de/skelton/util/
  created: kx/
 inflated: de/skelton/babel/Babel$1.class
 inflated: de/skelton/babel/Babel$2.class
 inflated: de/skelton/babel/Babel.class
 inflated: de/skelton/util/Logger.class
 inflated: de/skelton/util/SmartArray.class
 inflated: kx/c$Dict.class
 inflated: kx/c$Flip.class
 inflated: kx/c$IAuthenticate.class
 inflated: kx/c$KException.class
 inflated: kx/c$Minute.class
 inflated: kx/c$Month.class
 inflated: kx/c$Msg.class
 inflated: kx/c$Second.class
 inflated: kx/c$Timespan.class

 inflated: kx/c.class

$vi META-INF/MANIFEST.MF
add lib/RedshiftJDBC42-1.2.1.1001.jar

Save and exit vi
Recompile the jar file and ignore the warnings

$ jar umf META-INF/MANIFEST.MF babel.jar
Jun 21, 2017 6:07:47 PM java.util.jar.Attributes read
WARNING: Duplicate name in Manifest: Manifest-Version.
Ensure that the manifest does not have duplicate entries, and
that blank lines separate individual sections in both your
manifest and in the META-INF/MANIFEST.MF entry in the jar file.
Jun 21, 2017 6:07:47 PM java.util.jar.Attributes read
WARNING: Duplicate name in Manifest: Ant-Version.
Ensure that the manifest does not have duplicate entries, and
that blank lines separate individual sections in both your
manifest and in the META-INF/MANIFEST.MF entry in the jar file.
Jun 21, 2017 6:07:47 PM java.util.jar.Attributes read
WARNING: Duplicate name in Manifest: Created-By.
Ensure that the manifest does not have duplicate entries, and
that blank lines separate individual sections in both your
manifest and in the META-INF/MANIFEST.MF entry in the jar file.
Jun 21, 2017 6:07:47 PM java.util.jar.Attributes read
WARNING: Duplicate name in Manifest: X-COMMENT.
Ensure that the manifest does not have duplicate entries, and
that blank lines separate individual sections in both your
manifest and in the META-INF/MANIFEST.MF entry in the jar file.
Jun 21, 2017 6:07:47 PM java.util.jar.Attributes read
WARNING: Duplicate name in Manifest: Main-Class.
Ensure that the manifest does not have duplicate entries, and
that blank lines separate individual sections in both your

manifest and in the META-INF/MANIFEST.MF entry in the jar file.
Cleanup de, kx directories if you want to

$rm -rf de kx META-INF

Now start the babel.jar and make it listen to any port you want
$ java -jar babel.jar 6868 com.amazon.redshift.jdbc42.Driver
Babel for kdb+ v1.34 2014.03.24
2017.06.21 18:11:16.437 Listening on localhost:6868 for connections...

...

Start the q in another terminal and fire the query..
Substitute the jdbc connection string with yours. You can get it from AWS Redshit cluster config page. Of course you should know your user id and password.

$ q
KDB+ 3.5 2017.06.15 Copyright (C) 1993-2017 Kx Systems
m32/ 4()core 16384MB balaji balaji-macbook.local 255.255.255.255 NONEXPIRE  

Welcome to kdb+ 32bit edition
For support please see http://groups.google.com/d/forum/personal-kdbplus
Tutorials can be found at http://code.kx.com/wiki/Tutorials
To exit, type \\
To remove this startup msg, edit q.q
q)h:hopen 6868
q)h("query";"jdbc:redshift://redshift.xxxxxxxxxx.us-east-2.redshift.amazonaws.com:5439/dev?user=balaji&password=ThisIsNotSecreat";"select distinct(tablename) from pg_table_def where schemaname = 'public'")
tablename 
----------
"category"
"date"    
"event"   
"listing" 
"sales"   
"users"   
"venue"   

q)

Picture Credit: AWS Redshift documentation


All Credits goes to:
Bable reference: https://github.com/CharlesSkelton/babel
KX reference: http://code.kx.com/wiki/Babel
Redshift reference: http://docs.aws.amazon.com/redshift/latest/mgmt/configure-jdbc-connection.html#download-jdbc-driver