kdb+ server for ODBC3¶
The ODBC3 server allows applications to query kdb+ via the ODBC interface.
KxSystems/kdb/c/qodbc3.zip
Currently the applications may run on the following platforms: w64, w32, l64, l32. Primary compatibility target has been Tableau, although other uses are welcome.
Requirements: V3.2 or later.
Reporting problems
When reporting a problem (e.g. SQL error, wrong results, slowness, segfault etc.) make sure to include steps to reproduce along with your ODBC trace.
Data visualization with kdb+ using ODBC: a Tableau case study
Installation¶
Windows¶
- Close Tableau or anything that uses ODBC
- Extract qodbc3.zip to temporary location. Go to the directory corresponding to your OS architecture (w64 or w32)
- Run
d0.exe
as Administrator. This will copyqodbc3.dll
to the correct location – you don’t need to do that yourself. - You will now be able to add new kdb+ DSNs (data sources) in the
ODBC Data Source Administrator (64-bit)
(or32-bit
if on 32-bit OS). Make sure to selectkdb+(odbc3)
in the list of drivers. You will be prompted for DSN name, hostname, port, username and password. - In the ODBC data source administrator, click Start Tracing on the Tracing tab.
- Copy
q.tdc
to My Documents\My Tableau Repository\Datasources - Copy
ps.k
to$QHOME
Linux¶
Requirements: unixODBC 2.3.4, Binutils (ld)
Download KxSystems/kdb/l64/c.o to qodbc/l64
$ cd qodbc3/l64
$ ld -o qodbc3.so -shared qodbc3.o c.o -lodbc -lodbcinst -lm
Add a DSN entry to your ~/.odbc.ini
file:
[your_dsn_name]
Description=kdb+
Driver=/path/to/qodbc3.so
HOST=your.host:port
UID=username
PWD=password
You should now be able to connect to your DSN with isql
:
$ isql -3 -v -k 'DSN=your_dsn_name;'
Running¶
Ensure you have ps.k
loaded into the kdb+ process specified in your DSN:
q)\l ps.k
Notes¶
To use q from Tableau’s Custom SQL use the q()
function, e.g.:
q('select p,name,color,city from f[]')
or
q('functionname',<parameter1>,<parameter2>)
or
q('{f[x;y]}',<parameter1>,<parameter2>)
Parameters can be supplied by Tableau. Note that Tableau’s string type corresponds to q’s symbol and datetime corresponds to timestamp.
test.q
provides additional examples of SQL usage, including the create/insert/update/delete statement syntax.
Compatibility¶
The driver translates SQL expressions into q and inherits q’s data model. This gives rise to the following SQL compatibility issues:
- SQL string literals are trimmed like q symbols
MIN()
andMAX()
don't work on strings- q strings and bools lack nulls, therefore SQL operations on null data resulting in these types ‘erase’ nulls
COUNT
andCOUNT DISTINCT
don’t ignore nulls
Also, SQL selects from partitioned tables are not supported – one should pre-select from a partitioned table using the q()
function instead.