Kx Systems' kdb+ is a time-series relational and columnar database. QuerySurge users often need to validate data between kdb+ and other data stores as part of their data testing scheme for the broader ETL/LT architecture. This article explains how to connect QuerySurge to your kdb+ instance.
Deploying the kdb+ JDBC Driver
As for all QuerySurge connections, a JDBC driver is required to interface with kdb+. You can download the driver from this github. Place the jar in your Agent jdbc/ directory located at <QuerySurge Install Dir>/agent/jdbc/. Repeat this step for all Agents and restart each Agent service for the change to take effect.
kdb+ requires some basic setup in order for it to accept JDBC connections. These steps may already have been completed, so check with a kdb+ admin before following these directions:
- The server name or IP address where the kdb+ database is running. This will be displayed when kdb+ is started.
- By default there is no open port for kdb+, and an exposed port is required to connect to kdb+. You can open a port using the kdb+ shell (with the the \p command) or by using the -p flag when starting the instance.
In the kdb+ shell, \p <port> sets the listening port, while \p prints listening ports:
- If the instance is secured with the -u flag or .z.pw hook, you will need a valid username and password.
In the example below, a kdb+ server is started with port 5001 open. It is secured with the password definition file users.txt.
C:\>q -p 5001 -u "C:\users.txt"Note: If the output of the \p command shows 0i then no port is exposed.
KDB+ 3.6 2018.12.06 Copyright (C) 1993-2018 Kx Systems
w64/ 4(16)core 8182MB sampleuser computername 192.168.0.140 EXPIRE 2020.01.01 firstname.lastname@example.org KOD #9999999
Create a QuerySurge Connection
Creating a Connection to kdb+ in QuerySurge is done using the Connection Extensibility feature of the QuerySurge Connection Wizard. To create a connection, you will need the following information:
- The server name or IP address where the kdb+ database is running, and the kdb+ port. This will be displayed when kdb+ is started.
To create a Connection, log into QuerySurge with a QuerySurge admin login. Navigate to: Administration > Connections, and click on the Add button (lower left, main panel). Provide a name for your Connection, and choose "* All Other JDBC Connections (Connection Extensibility)" in the Data Source dropdown.
Note: For details on using Connection Extensibility, see this Knowledge Base article.
Click Next. Enter the Driver Class for the kdb+ JDBC driver. The kdb+ driver's class is:
The kdb+ JDBC URL follows this format:
The <host> can either be the hostname or the IP address of the kdb+ server; the <port> is the port number that the database is available on. You may need to run the \p command in the kdb+ shell to obtain the port (see the section kdp+ Setup above), or you may need to check with an Admin to verify the port number.
Note: There is no default port for kdb+. The port is defined when starting the instance using the -p flag or set in the kdb+ shell using the \p <port> command.
If you wish to enter a test query, enter the query in the Test Query edit box and click Next. Note that kdb+ queries have a SQL-like syntax which differs from ANSI SQL in many details. A notable example is that kdb+ queries are always preceded with `q)`:
q)select sum amount by city from tab
Note: Your test query need only return a single row and a single column.
Note: At least one Agent with the kdb+ driver deployed must be running in order to test the connection.
Once the connection parameters are complete, click Next. The Connection Wizard shows a summary of the information you have entered.
Once reviewed, click Test Connection to validate your connection details. A popup will show your Connection status:
Click on the Wizard's Save button (lower right) to save the Connection. Congratulations! you have successfully configured a QuerySurge connection to a kdb+ instance. You can find documentation for the SQL dialect that kdb+ uses here.