Kdb+ server for ODBC3
The ODBC3 server allows applications to query q via the ODBC interface.
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.
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.
- 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 copy qodbc3.dll to the correct location – you don't need to do that yourself.
- You will now be able to add new q DSNs (data sources) in the
ODBC Data Source Administrator (64-bit)(or
32-bitif on 32-bit OS). Make sure to select
kdb+(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
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 -3 -v -k 'DSN=your_dsn_name;'
Ensure you have ps.k loaded into the q process specified in your DSN:
To use q from Tableau’s Custom SQL use the
q() function, e.g.:
q('select p,name,color,city from f') or
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.
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
MAX()don't work on strings
- q strings and bools lack nulls, therefore SQL operations on null data resulting in these types ‘erase’ nulls
COUNT DISTINCTdon’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.