Running Native kdb Queries in Spring JDBC

In my last post on accessing a kdb+ server from spring, we configured Spring to listen to a kdb+ gateway via jdbc. One drawback of using jdbc is queries will now have to be written as single-line sql strings to match the type of JdbcTemplate query arguments. This strips away some advantages c.java style querying has. In this post we’ll go over a way to use the native kdb executor method in spring jdbc so that we can use c.java style queries and at the same time retain some advantages of jdbc, such as, simple connection configuration, effortless connection management etc.

Let’s take an example. In a trading system, quotes data is periodically updated with new of snapshots received from exchanges. Let’s make a barebones kdb quote store server. We will define a simple bid-ask quote table and a upd function to insert list of records into it. The update time will also be recorded along with the new values.

/q/server.q:

1
2
3
4
5
6
7
8
quote:flip `time`sym`bid`ask!()

upd:{[arg]
 t:arg[0];                   /saves the table name to t
 x:arg[1];                   /saves the data received to x
 z:(count x)#.z.T;           /creates a list with current time (time of receiving x)
 t insert (enlist z),flip x; /insert time and data into t
 }

kdb Server

First let’s start the server. The java client will connect to it over TCP port 7000.

1
2
3
4
5
$ q server.q -p 7000
KDB+ 3.4 2016.12.08 Copyright (C) 1993-2016 Kx Systems
m32/ 4()core 8192MB subha tuchanka.local 192.168.0.104 NONEXPIRE

q)

Poking inside the JDBC Class

Within the code of jdbc.jar you will find that the connection to kdb gateway is handled by the sub-class co and in that class there is pretty interesting method which is actually managing executing JdbcTemplte’s query calls.

1
2
3
4
5
6
7
8
public Object ex(String s,Object[]p)throws SQLException{
    try {
        return 0<c.n(p)?c.k(s,p):c.k(".o.ex",s.toCharArray());
    } catch(Exception e) {
        q(e);
        return null;
    }
}

So, If h is a handle of a kdb gateway ex(String arg0,Object[] arg1) is essentially equivalent to running the following kdb command:

1
q) h "{value of arg0} {value of arg1}"

To run a kdb function using ex you need to pass the function’s name to arg0, and all the required arguments for that function as a list to arg1. For example, arguments for ex to run the q expression count (`SYM;99.0;101.0) is:

1
2
3
// conn is the connection object
Long ret = (Long) conn.ex("count", new Object[]{"SYM",new Double(99),new Double(101)}); 
System.out.print(ret);

Extracting the underlying `co` object from jdbcTemplate

JdbcTemplate uses org.apache.tomcat.jdbc.pool.PooledConnection to manage connections. So we need to get a connection from the pool and then extract the co connection object within. Be careful to use try-with-resources with the pooled connection, because then at the end of the statement the underlying kdb connection will be returned to the pool and it will be reused in the next loops.

1
2
Connection c = jdbcTemplate.getDataSource().getConnection()
co conn = (co) c.getMetaData().getConnection();

I am using the jdbc.jar from kx site, in which, for some reason, the jdbc class resides in a default package. That means we cannot import jdbc with an import statement. We’ll have to use reflection to get a handle to the ex method and invoke that. The class co is inside jdbc class, so the fully qualified name for it would be jdbc$co.

1
2
3
4
Method executeQuery = Class.forName("jdbc$co").getMethod("ex", String.class, Object[].class);
Connection c = jdbcTemplate.getDataSource().getConnection()
Object conn = c.getMetaData().getConnection();
executeQuery.invoke(conn, arg0, arg1); 

Spring Java Clent

Maven configurations are same as the previous post. In application.properties we need to add tomcat datapool specific configurations.

/src/main/resources/application.properties:

1
2
3
4
5
6
7
8
9
spring.datasource.url=jdbc:q:localhost:7000
spring.datasource.driverClassName=jdbc
spring.datasource.tomcat.initSql=q)1
spring.datasource.tomcat.validationQuery=q)1
spring.datasource.tomcat.testOnBorrow=true
spring.datasource.tomcat.testOnConnect=true
spring.datasource.tomcat.testOnIdle=true
spring.datasource.tomcat.validationInterval = 30000
spring.datasource.tomcat.maxIdle=10

We’ll make an CLI application by implementing CommandLineRunner interface.

We are going to connect to the gateway and use the upd function defined in server.q to insert records into quote. For this example, I’ll create dummy datafeed using a list of price and stocks (example below) and randomizing it a bit to create bid/ask columns.

stocks.list:

1
2
3
4
5
GOOG=808.38
GOOGL=829.88
AAPL=132.04
FB=134.2
MSFT=63.34

Here is the complete code incorporating all the things we have discussed: src/main/java/hello/Application.java:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
package hello;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.core.JdbcTemplate;

import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;

@SpringBootApplication
public class Application implements CommandLineRunner {

    @Autowired
    JdbcTemplate jdbcTemplate;

    private Map<String, Double> stocksList;
    private Method executeQuery;

    public static void main(String args[]) {
        SpringApplication.run(Application.class, args);
    }



    @Override
    public void run(String... strings) {

        stocksList = getStockList();

        try {
            executeQuery = Class.forName("jdbc$co").getMethod("ex", String.class, Object[].class);
        } catch (NoSuchMethodException | ClassNotFoundException e) {
            System.err.println("kdb+ JDBC driver has not loaded properly");
            e.printStackTrace();
            System.exit(1);
        }

        while(true) {


            try(Connection c = jdbcTemplate.getDataSource().getConnection()){
                executeQuery.invoke(c.getMetaData().getConnection(), "upd", new Object[]{"quote", getDummyData()});
                Thread.sleep(1000);

            } catch (  IllegalAccessException | InvocationTargetException | InterruptedException | SQLException e) {
                e.printStackTrace();
            }
        }

    }

    private Object[] getDummyData() {
        List<Object[]> l = new ArrayList<>();
        stocksList.forEach((k,v) -> l.add(new Object[]{k, v-2*Math.round(100*Math.random())/100.0, v+2*Math.round(100*Math.random())/100.0}));
        return l.toArray();
    }

    private Map<String, Double> getStockList() {
        Map<String, Double> ret = new HashMap<>();
        try {
            Properties props = new Properties();
            FileInputStream in = new FileInputStream(new ClassPathResource("stocks.list").getFile());
            props.load(in);
            props.forEach((k,v) -> ret.put((String)k, Double.parseDouble((String)v)));
            in.close();

        } catch (IOException e) {
            e.printStackTrace();
        }

        return ret;
    }
}

Run Instructions

Open the project in IntelliJ IDEA/Eclipse. Add this jar to your build path and run the client.

Now if you query quote you should see new data coming in every second

  ~/Workspace/sping-kdb-native/q $ rlwrap q server.q -p 7000
  KDB+ 3.4 2016.12.08 Copyright (C) 1993-2016 Kx Systems
  m32/ 4()core 8192MB subha tuchanka.local 192.168.0.101 NONEXPIRE
      
  q)quote
  time         sym   bid    ask
  --------------------------------
  20:19:51.463 CERN  52.93  53.93
  20:19:51.463 VMW   87.76  90.76
  20:19:51.463 GOOGL 829.88 829.88
  20:19:51.463 AAPL  132.04 132.04
  20:19:51.463 YHOO  44.07  45.07
  20:19:51.463 ADI   77.48  78.48
  20:19:51.463 BIDU  181.5  181.5
  20:19:51.463 INFO  38.71  40.71
  20:19:51.463 ITW   127.12 128.12
  20:19:51.463 ADP   95.58  96.58
  20:19:51.463 STM   13.94  14.94
  20:19:51.463 CHKP  99.56  100.56
  20:19:51.463 PNR   57.96  58.96
  20:19:51.463 STX   44.8   46.8
  20:19:51.463 WDC   76.08  78.08
  20:19:51.463 XLNX  58.23  59.23
  20:19:51.463 AMD   13.56  14.56
  20:19:51.463 CTSH  56.45  57.45
  20:19:51.463 DVMT  64.84  65.84
  20:19:51.463 INFY  13.11  15.11
  ..
  q)count quote
  192225
  q)

You can find the complete project here.

Concatenating kdb Columns

Suppose in a query you need to concatenate two kdb columns into one; for example, to join date and time into one field - kdb has nifty features to do it easily.

You can join(,) two or more column into one using '(each-both). The columns do not need to be of the same type. The type of the returned column is list.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
q)tab:([]firstname:`John`Jia`Jai`Jac;lastname:`James`Jain`Jadeja`Jones;age:1+4?30)
q)tab
firstname lastname age
----------------------
John      James    19
Jia       Jain     24
Jai       Jadeja   25
Jac       Jones    19
q)
q)select name:(firstname,'lastname) from tab
name
----------
John James
Jia  Jain
Jai  Jadeja
Jac  Jones

A mixed list is formed if the columns are of different type. If you cast the columns to string before each-both join, a concatenated string is formed. You can also insert a character using ,'.
1 2 3 4 5 6 7
q)select col:((string firstname),'(string lastname),'"/",'(string age)) from tab col -------------- "JohnJames/19" "JiaJain/24" "JaiJadeja/25" "JacJones/19"

If you need multi character delimiters, use sv. A nice advantage of this method is you don’t have to individually convert each column to string.

1
2
3
4
5
6
7
q)select col:({"--" sv x} each string (firstname,'lastname,'age)) from tab
col
-----------------
"John--James--19"
"Jia--Jain--24"
"Jai--Jadeja--25"
"Jac--Jones--19"

To add a string as prefix and postfix to a column, you can use /:(each right) and \:(each left) respectively.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
q)select ("Dear ",/:(string firstname)) from tab
firstname
-----------
"Dear John"
"Dear Jia"
"Dear Jai"
"Dear Jac"
q)select ((string age),\:" years") from tab
x
----------
"19 years"
"24 years"
"25 years"
"19 years"
q)select col:((string firstname),'(string lastname),'"/Age",/:(string age)) from tab
col
--------------
"JohnJames/Age19"
"JiaJain/Age24"
"JaiJadeja/Age25"
"JacJones/Age19"

Lets conclude by creating a JavaScript style array by joining all three columns.

1
2
3
4
5
6
7
q)select ("['",/:(({"','" sv x} each string (firstname,'lastname,'age)),\:"']")) from tab
x
-----------------------
"['John','James','19']"
"['Jia','Jain','24']"
"['Jai','Jadeja','25']"
"['Jac','Jones','19']"

Interfacing Java Spring with kdb

The classic way to access a kdb server from java is via the c class. The jdbc implementation makes it easy to interface with a kdb database providing higher level methods to establish a database connection, parse the returned object. One drawback is that it does not support retrieving result that is not a table. Let’s go over a short guide on how to use jdbc to connect to kdb.

kdb Server

First let’s start a kdb process to which the java client will connect over TCP port 7000. You can choose any port you like.

1
2
3
4
5
6
$ ./q
KDB+ 3.3 2016.03.14 Copyright (C) 1993-2016 Kx Systems
m32/ 4()core 8192MB subha tuchanka.local 192.168.0.101 NONEXPIRE

q)\p 7000
q)

Spring Java Clent

Maven Configuration

We are going to use Spring Boot with jdbc starter. Spring boot further simplifies the code by configuring Spring automatically wherever possible. spring-boot-starter-jdbc provides support for jdbc.

pom.xml:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
                        http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.example</groupId>
    <artifactId>spring-kdb</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>spring-kdb</name>
    <description>Demo project for Spring kdb Integration</description>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.3.3.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

Configuring Datasource

The jdbc driver for kdb not available in maven. It can be downloaded from kx repository. You can add it as a build dependency or add to your local maven repo and use it as a maven dependency. I am going to use the first approach.

Configure the driver class name and server URL in the application.properties file.

/src/main/resources/application.properties:

1
2
spring.datasource.driverClassName=jdbc
spring.datasource.url=jdbc:q:localhost:7000

Connecting to kdb

Spring boot will automatically configure the datasource class from the application.properties and you can use @Autowired annotation to pass it to a jdbcTemplate.

Let’s make a kdb terminal in Java.

src/main/java/hello/Application.java:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
package hello;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.jdbc.core.JdbcTemplate;

import java.io.BufferedReader;




import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
//tab:flip `items`sales`prices!(`nut`bolt`cam`cog;6 8 0 3;10 20 15 20)
@SpringBootApplication
public class Application implements CommandLineRunner {

    @Autowired
    JdbcTemplate jdbcTemplate;

    public static void main(String args[]) {
        SpringApplication.run(Application.class, args);
    }

    @Override
    public void run(String... strings) throws IOException {
        InputStreamReader isr = new InputStreamReader(System.in);
        BufferedReader br = new BufferedReader(isr);
        while (true) {
            System.out.print("q) ");
            String sql = "q)" + br.readLine();
            try {
                jdbcTemplate.query(sql, (ResultSet rowSet) -> {
                        ResultSetMetaData m;
                        m = rowSet.getMetaData();
                        int columnCount = m.getColumnCount();
                        String fmt[] = new String[columnCount + 1];
                        int width = 0;

                        for (int i = 1; i <= columnCount; i++) {
                            fmt[i] = "%-" + m.getColumnDisplaySize(i) + "s";
                            System.out.format(fmt[i], m.getColumnLabel(i));
                            width += rowSet.getMetaData().getColumnDisplaySize(i);
                        }
                        System.out.print("\n");

                        for (int i = 1; i <= width; i++) {
                            System.out.print("-");
                        }
                        System.out.print("\n");

                        do{
                            for (int i = 1; i <= columnCount; i++) {
                                System.out.format(fmt[i], rowSet.getString(i));
                            }
                            System.out.print("\n");
                            if (rowSet.getRow() > 10) {
                                System.out.println("...");
                                rowSet.afterLast();
                            }
                        }while (rowSet.next());
                    });
            } catch (RuntimeException e) {
                if(e.getCause() instanceof SQLException) {
                    System.out.println(e.getCause().getMessage());
                }
            }
        }
    }
}

We are using RowCallbackHandler as we shall be processing the rows immediately. You can do away with all the SQL cuteness and run q statements directly by prefixing q statements with q). All the exceptions from the jdbc class are thrown as runtime exceptions. An SQLException is generated if execution of the query fails. Apart from that NullPointerException is generated if a statement returns nothing upon execution such as assignment expressions somevar:10; and ClassCastException is thrown if you try to retrieve anything other than a table. Here I am handling only the SQLExceptions and masking all the others - which generally is not a good practice.

You need to first start the kdb server. You can run the client within eclipse or as a jar:

You can find the complete project here.

Running Native kdb Queries in Spring JDBC

kdb Server

Poking inside the JDBC Class

Extracting the underlying co object from jdbcTemplate

Spring Java Clent

Run Instructions

Concatenating kdb Columns

Interfacing Java Spring with kdb

kdb Server

Spring Java Clent

Maven Configuration

Configuring Datasource

Connecting to kdb

Extracting the underlying `co` object from jdbcTemplate