Tables¶

A basic understanding of the internal structure of tables is needed to work with q. The structure is actually quite simple, but very different from conventional databases.

This section gives a quick overview, followed by an explanation of the sp.q script, and then a typical table for stock data. After completing this, you should read Q for Mortals 14. Introduction to kdb+, which has a detailed comparison of q and conventional RDBMS.

Tables are created out of lists. A table with no key columns is essentially a list of column names associated with a list of corresponding column values, each of which is a list. A table with key columns is built internally from a pair of tables – the key columns associated with the non-key columns.

Tables are created in memory, and then written to disk if required. When written to disk, smaller tables can be stored in a single file, while larger tables are usually partitioned in some way. The partitioning can be seen when viewing the file directories, but the table is treated as a single object within a kdb+ process.

Creating tables¶

There are two ways of creating a table. One way explicitly associates lists of column names and data; the other uses a q expression that specifies the column names and initial values. The second method also permits each column’s datatype to be given, and so is particularly useful when a table is created with no data.

create table by association:

q)tab:flip `items`sales`prices!(`nut`bolt`cam`cog;6 8 0 3;10 20 15 20)
q)tab
items sales prices
------------------
nut   6     10
bolt  8     20
cam   0     15
cog   3     20

create table by specifying column names and initial values:

q)tab2:([]items:`nut`bolt`cam`cog;sales:6 8 0 3;prices:10 20 15 20)
q)tab~tab2              / tab and tab2 are identical
1b

The form for the second method, for a table with j primary keys and n columns in total, is:

t:([c₁:v₁; … ; c_j:v_j] c_j+1:v_j+1; … ;c_n:v_n)

Here table t is defined with column names $c_{1-n}$, and corresponding values $v_{1-n}. The square brackets are for primary keys, and are required even if there are no primary keys.

4.3 Suppliers and parts¶

The script KxSystems/kdb/sp.q defines C.J. Date’s Suppliers and Parts database. You can view this script in an editor to see the definitions. Load the script.

q)\l sp.q

Table `s`¶

Table s has a primary key column, also called s, given as a list of symbols which should be unique. In this example, the name s is used both for the table and the primary key column, but this is not required.

The remaining columns are of type symbol, integer, symbol.

s:([s:`s1`s2`s3`s4`s5]
 name:`smith`jones`blake`clark`adams;
 status:20 10 30 20 30;
 city:`london`paris`paris`london`athens)

Display in q.

q)s
s | name  status city
--| -------------------
s1| smith 20     london
s2| jones 10     paris
s3| blake 30     paris
s4| clark 20     london
s5| adams 30     athens

Note that the column types are set from the data given. If this were first created as an empty table, say table t, then the column types could be defined explicitly as follows:

q)t:([s:`$()]name:`$();status:"i"$();city:`$())

Insert a row.

q)`t insert (`s1;`smith;20;`london)
,0
q)t
s | name status city
--| -------------------
s1| smith 20 london

Table `p`¶

Table p is created much like table s. As before, the table name and primary key name are both the same:

p:([p:`p1`p2`p3`p4`p5`p6]
 name:`nut`bolt`screw`screw`cam`cog;
 color:`red`green`blue`red`blue`red;
 weight:12 17 17 14 12 19;
 city:`london`paris`rome`london`paris`london)

Display in q:

q)p
p | name  color weight city
--| -------------------------
p1| nut   red   12     london
p2| bolt  green 17     paris
p3| screw blue  17     rome
p4| screw red   14     london
p5| cam   blue  12     paris
p6| cog   red   19     london

Table `sp`¶

Table sp is defined with no primary key. Columns s and p refer to tables s and p respectively as foreign keys. The syntax for specifying another table’s primary key as a foreign key is:

`tablename$data

The definition of sp is:

sp:([]
 s:`s$`s1`s1`s1`s1`s4`s1`s2`s2`s3`s4`s4`s1;
 p:`p$`p1`p2`p3`p4`p5`p6`p1`p2`p2`p2`p4`p5;
 qty:300 200 400 200 100 100 300 400 200 200 300 400)

Display in q.

q)sp
s  p  qty
---------
s1 p1 300
s1 p2 200
s1 p3 400
s1 p4 200
s4 p5 100
...

Stock data¶

The following is a typical layout populated with random data. Load the KxSystems/cookbook/start/trades.q script.

q)\l trades.q

A trade table might include: date, time, symbol, price, size, condition code.

q)trades:([]date:`date$();time:`time$();sym:`symbol$();
    price:`real$();size:`int$(); cond:`char$())

q)`trades insert (2013.07.01;10:03:54.347;`IBM;20.83e;40000;"N")
q)`trades insert (2013.07.01;10:04:05.827;`MSFT;88.75e;2000;"B")
q)trades
date       time         sym  price size  cond
---------------------------------------------
2013.07.01 10:03:54.347 IBM  20.83 40000 N
2013.07.01 10:04:05.827 MSFT 88.75 2000  B

The ? operator will generate random data.

q)syms:`IBM`MSFT`UPS`BAC`AAPL
q)tpd:100              / trades per day
q)day:5                / number of days
q)cnt:count syms       / number of syms
q)len:tpd*cnt*day      / total number of trades
q)date:2013.07.01+len?day
q)time:"t"$raze (cnt*day)#enlist 09:30:00+15*til tpd
q)time+:len?1000
q)sym:len?syms
q)price:len?100e
q)size:100*len?1000
q)cond:len?" ABCDENZ"

q)`trades:0#trades      / empty trades table
q)`trades insert (date;time;sym;price;size;cond)
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
..
q)trades:`date`time xasc trades  / sort on time within date
q)5#trades             / actual values are random
date       time         sym  price    size  cond
------------------------------------------------
2013.07.01 09:30:00.037 AAPL 14.68571 18800 Z
2013.07.01 09:30:00.431 AAPL 88.91143 87600 B
2013.07.01 09:30:00.631 IBM  46.61601 35200 N
2013.07.01 09:30:15.087 UPS  42.53144 36500 A
2013.07.01 09:30:15.142 AAPL 78.99029 42300 B