Babel

From Kx Wiki
Jump to: navigation, search

Contents

Babel for kdb+

Babel for kdb+ is a SQL gateway process that allows kdb+ to query other vendors databases via jdbc.

Babel.png


Download

Babel for kdb+ can be downloaded from the contrib section here

https://github.com/CharlesSkelton/babel/blob/master/dist/babel.zip

unzip babel.zip retaining the directory structure.

Invocation

It can be started with

java -Xmx512m -jar babel.jar 6868 com.mysql.jdbc.Driver
which is of the pattern
java -jar babel.jar listeningport jdbcdriver1 jdbcdriver2 ... jdbcdriverN

If you don't specify the port 6868 it will default to listening on port 9999. It will accept connections from localhost only, and logs to stdout.

Alternately, you can ignore that babel.jar is an executable jar file and run with full command line options. For Oracle, this could be:

java -Xmx1024m -Doracle.jdbc.defaultRowPrefetch=10000 -cp "babel.jar:lib/ojdbc6.jar" de.skelton.babel.Babel 6868 oracle.jdbc.OracleDriver

Building a connection and issuing a query

From kdb+ you connect as

q)h:hopen 6868

and send queries as a sync request using the following format

q)handle("query|update|string";"jdbc url";"query text")
e.g.
q)r:h("query";"jdbc:mysql://ensembldb.ensembl.org:3306?user=anonymous&zeroDateTimeBehavior=convertToNull";"show databases")

Replacing "query" with "string" will force all columns to be interpreted as varchar type; this is intended for debug of type mappings only, and not recommended as being used for regular queries.

Async Requests

In addition to sync requests, Babel can process async requests in the form

q)neg[handle]([`callbackFn`callbackTag!`myfn`mytag;]"query|update";"jdbc url";"query text")

e.g. Babel will callback myfn[`mytag;(0|1;errorText|resultSet)]
q)myfn:{0N!(x;y);}
q)neg[h](`callbackFn`callbackTag!`myfn`mytag;"query";"jdbc:mysql://ensembldb.ensembl.org:3306?user=anonymous&zeroDateTimeBehavior=convertToNull";"show databases")

or collect the result via h[]

q)neg[h]("query";"jdbc:mysql://ensembldb.ensembl.org:3306?user=anonymous&zeroDateTimeBehavior=convertToNull";"show databases")
q)r:h[] / (0|1;errorText|resultSet)


Also see babel readme.txt

Drivers for the following databases are loaded by default (jars found in lib subdir relative to babel.jar) -

http://csvjdbc.sourceforge.net

http://hsqldb.org

http://www.mysql.com

http://www.microsoft.com/en-us/sqlserver/default.aspx

Specifying your jdbc driver and repacking the jar

If you want to continue to launch the executable JAR file directly, and you need to add your driver to the path, do the following:

download and unzip the latest babel.zip

then extract the jar to its raw components

jar -xvf babel.jar

then edit META-INF/MANIFEST.MF

adding your database jar file path to the classpath in that manifest file

then update the jar file with the modified file

jar umf META-INF/MANIFEST.MF babel.jar

If you know of a better way to achieve this please let us know.

Default Row Prefetch

When extracting data from some oracle databases, users have discovered that the default fetch size for the Oracle jdbc driver is too small and impacts performance very badly. This can be influenced by setting the default fetch size as a command line option to java, using -Doracle.jdbc.defaultRowPrefetch=10000 e.g.

$java -Doracle.jdbc.defaultRowPrefetch=10000 -Xmx512m -jar babel.jar 6868 ....

Data Type Conversion for NUMERIC and DECIMAL types

The default type for NUMERIC and DECIMAL is a String. However, the data will be converted into an actual number if the following criteria are met:

Precision Scale Data Type
<= 9 0 or -127 Integer
<= 18 0 or -127 Long
<= 7 not 0 or -127 Float
<= 15 not 0 or -127 Double
Personal tools
Namespaces
Variants
Actions
Navigation
Print/export
Toolbox