Skip to content

kdb+ server for ODBC3

The ODBC3 driver allows applications to query kdb+ through 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

kdb+ configuration

The kdb+ process should be listening on port which relates to the port choosen and defined in the odbc configuration.

Unzip qodbc3.zip, and copy ps.k to $QHOME. Ensure you have ps.k loaded into the kdb+ process:

q)\l ps.k

Run .s.ver to check that ps.k has been loaded and for the current version e.g. 

q).s.ver
1.15

The following is an example of starting kdb+ with ps.k listening on port 5000

q ps.k -p 5000

ODBC driver and DSN configuration

Software that uses ODBC connections refer to individual connections using a DSN(Data Source Name). The DSN details the ODBC driver (e.g. qodbc3) and connection details specific to the data source (e.g. hostname, username, etc).

The following details adding a new DSN to connect to kdb+:

Windows

The following details installing the ODBC driver and configuring a user or system DSN as an administrator.

  1. Close Tableau or anything that uses ODBC
  2. Extract qodbc3.zip to temporary location. Go to the directory corresponding to your OS architecture (w64 or w32)
  3. Run d0.exe as Administrator. This will copy qodbc3.dll to the correct location – you don’t need to do that yourself.
  4. You will now be able to add new kdb+ DSNs (data sources) in the ODBC Data Source Administrator (64-bit) (or 32-bit if 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.
  5. Use Test Connection within the DSN configuration screen to check that the connection to kdb+ is working.

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

The ODBC driver should be installed in the corresponding system ini file and a DSN entry created in either the system data sources (for all users) or user data source (current user). The linux odbcinst tool can be used to find these locations if not known, for example:

$ odbcinst -j
unixODBC 2.3.7
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Add the driver to your list of drivers by editing the driver ini file ( /etc/odbcinst.ini). An example of the definition follows (note: the driver location should be altered to the location chosen on your system):

[kdb+(odbc3)]
Description     = ODBC for kdb+
Driver          = /src/qodbc3/l64/qodbc3.so

After adding, this can be verified by listing the system ODBC drivers:

$ odbcinst -q -d
[PostgreSQL]
[MySQL]
[FreeTDS]
[MariaDB]
[kdb+(odbc3)]

Add a DSN entry to your system data source file (/etc/odbc.ini) or user data source file (~/.odbc.ini), with the appropriate settings to communicate with the kdb+ server. For example:

[your_dsn_name]
Description=kdb+
Driver=kdb+(odbc3)
HOST=localhost:5000
UID=username
PWD=password

You should now be able to connect to kdb+ represented by the DSN with isql:

$ isql -3 -v -k 'DSN=your_dsn_name;'

Tableau configuration

If there is a requirement to connect Tableau with kdb+, copy q.tdc to the appropriate directory for your application as detailed here.

Follow the Tableau directions for restarting Tableau Desktop/Server after the file is copied to the appropriate location.

The q.tdc file is a Tableau Datasource Customization (TDC) file to customize Tableau-specific settings for the kdb+ ODBC connection. The driver name within the file must match the name of the kdb+ driver installed (kdb+(odbc3)).

The destination directory can be different depending on whether Tableau Desktop or Tableau Server is being used

Tableau 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.

Compatibility

The driver translates SQL expressions into q and inherits q’s data model. This gives rise to the following SQL compatibility issues:

  1. SQL string literals are trimmed like q symbols
  2. MIN() and MAX() don't work on strings
  3. q strings and bools lack nulls, therefore SQL operations on null data resulting in these types ‘erase’ nulls
  4. COUNT and COUNT 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.

test.q provides additional examples of SQL usage, including the create/insert/update/delete statement syntax.

Custom authentication

Custom authentication is supported, allowing the username and password specified in a DSN or connection string to be transformed by a user-defined function. See customauth.txt in the qodbc3.zip for details.

Debugging

ODBC implementations provide a tracing capability to log interactions with an ODBC driver. This can aid in diagnosing any issues. Tracing can have a detremental effect to ODBC performance.

This is a feature of the ODBC system, and not a feature unique to the kdb+ driver. Associated documentation, features, and troubleshooting can be found online for your OS.

Connection details can be logged, therefore the log file should be stored in a private location

Windows

In the ODBC data source administrator, click Start Tracing on the Tracing tab.

See below an example of the data recorded to the SQL.LOG:

powershell      da8-9a0 ENTER SQLExecDirectW
                HSTMT               0x00000256C37A6920
                WCHAR *             0x00000256AA98112C [      -3] "SELECT * FROM t\ 0"
                SDWORD                    -3
...
powershell      da8-9a0 EXIT  SQLGetData  with return code 0 (SQL_SUCCESS)
                HSTMT               0x00000256C37A6920
                UWORD                        2
                SWORD                       -8 <SQL_C_WCHAR>
                PTR                 0x00000256A79BB1D0 [       2] "1"
                SQLLEN                  4092
                SQLLEN *            0x000000DAA198E160 (2)

Note that Windows ODBC tracing can present a misleading invalid length error for connections that are null-terminated. The -3 (SQL_NTS) length value is used by ODBC to indicate a null-terminated string.

powershell      127c-12c4   EXIT  SQLDriverConnectW  with return code 0 (SQL_SUCCESS)
        HDBC                0x0000028E2F1EDAD0
        HWND                0x0000000000000000
        WCHAR *             0x00007FF9A40772C0 [      -3] "******\ 0"
        SWORD                       -3
        WCHAR *             0x00007FF9A40772C0 <Invalid buffer length!> [-3]
        SWORD                       -3
        SWORD *             0x0000000000000000
        UWORD                        0 <SQL_DRIVER_NOPROMPT>

Please ensure tracing is disabled after debugging complete.

Linux

In the [ODBC] section of the driver ini file (/etc/odbcinst.ini), set Trace to 1 to enable tracing and TraceFile to the location of the log file to create. Additional configuration options are available. For example:

[ODBC]
Trace           = 1
TraceFile       = /tmp/odbc.log
TraceOptions    = 3
ODBCTraceFlush  = 1

An example of the data recorded: 

[ODBC][194][1729684200.251390][SQLPrepare.c][196]
                Entry:
                        Statement = 0x55ba1f4cdb30
                        SQL = [select * from t][length = 15]
...
[ODBC][194][1729684200.252562][SQLGetData.c][237]
                Entry:
                        Statement = 0x55ba1f4cdb30
                        Column Number = 1
                        Target Type = 1 SQL_CHAR
                        Buffer Length = 301
                        Target Value = 0x55ba1f4cfd10
                        StrLen Or Ind = 0x7ffe4093ef20

Please ensure tracing is disabled after debugging complete.