Configuring Connections: Kx Systems kdb+

Versions: 8.0+

Versions:7.0+

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.

Note: For full instructions on driver deployment, see this article for Windows Agents or this article for Linux Agents.

kdb+ Setup

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:
  

  q)\p 5001
  q)\p
  5001i
  q)

• 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"
  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 [e-mail] KOD #9999999
  
  q)

  Note: If the output of the \p command shows 0i then no port is exposed.

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:

jdbc

The kdb+ JDBC URL follows this format:

jdbc:q:<host>:<port>

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.