Wednesday, May 2, 2018

Connecting to SQL Server from KDB+ (Using Babel)

I already blogged about connecting to Oracle and Redshift from KDB.

Here I will cover SQL Server.

Step 0: Install Java (preferably 8) if you have not installed. java -version should give you the version

Step 1:
Follow instruction http://code.kx.com/wiki/Babel and Install Babel.
You can download Babel from wget https://github.com/CharlesSkelton/babel/raw/master/dist/babel.zip
and unzip it.

Step 2:
Credit: https://stackoverflow.com/questions/5307048/where-do-i-install-a-jdbc-driver-on-ubuntu
I assume you already have Java 8 installed. Download the corresponding SQL Server JDBC driver.
https://www.microsoft.com/en-us/download/details.aspx?id=11774
Download sqljdbc_6.0.8112.200_enu.tar.gz
Unzip and untar the file
$ gzip -d sqljdbc_6.0.7507.100_enu.tar.gz
$ tar -xf sqljdbc_6.0.7507.100_enu.tar
You can optionally create a soft link for your corresponding SQL JDBC under babel/lib. This will come in handy while starting Babel.
Note: Choose the right driver while creating the soft link: sqljdbc_6.0/enu/jre8/sqljdbc42.jar is the right one Java 8


Step 3: Start Babel
java -Xmx1024m -cp "babel.jar:sqljdbc42.jar" de.skelton.babel.Babel 6868 com.microsoft.sqlserver.jdbc.SQLServerDriver
You might have to substitute the actual path of babel.jar & sqljdbc42.jar (For Java 8)
You will see message something like this
Babel for kdb+ v1.34 2014.03.24

2018.05.02 10:05:22.571 Listening on localhost:6868 for connections...
2018.05.02 10:08:06.513 Authenticating balaji
2018.05.02 10:08:06.513 Accepted connection from /127.0.0.1:38126
2018.05.02 10:15:32.779 Request from /127.0.0.1:38126


Step 4: Start Q and connect to SQL Server

$ q
KDB+ 3.2 2015.05.27 Copyright (C) 1993-2015 Kx Systems
l32/ 4()core 7965MB balaji balaji 127.0.01 NONEXPIRE

Welcome to kdb+ 32bit edition
q)h:hopen 6868
q)r:h("query";"jdbc:sqlserver://sqldb-host:1433;databaseName=mysqldb;user=sqluser;password=xxxTopSecret";"SELECT  * FROM <your table>")
q)meta r
q)count r
q)r
..... Your Table shown here ....

And that's it







No comments:

Post a Comment