Tables


Overview


Tables form the basis for kdb+. A table is a collection of named columns implemented as a dictionary. Consequently, q tables are column-oriented, in contrast to row-oriented tables in relational databases. Moreover, a column's values in q comprise an ordered list; this contrasts to SQL, in which the order of rows is undefined. The fact that q tables comprise ordered column lists makes kdb+ very efficient at storing, retrieving and manipulating sequenced data. One important example is data that arrives in time sequence.

Kdb+ handles relational and time series data in the unified environment of q tables. There is no separate data definition language, no separate stored procedure language and no need to map internal representations to a separate form for persistence. Just q tables, expressions and functions.

Tables are built from dictionaries, so it behooves the cursory reader to review Dictionaries before proceeding.

Table Definition


Table is the flip of Column Dictionary

You undoubtedly realized at the end of Dictionaries that a table is implemented as a column dictionary that has been flipped (i.e., transposed). The only effect of flipping the column dictionary is to reverse the order of its indices; no data is rearranged under the covers.

Note: All tables have type 98h.

For example,


In [0]:
d:`name`iq!(`Dent`Beeblebrox`Prefect;98 42 126)
d[`iq;]


98 42 126

In [1]:
d[;2]


name| `Prefect
iq  | 126

In [2]:
d[`iq; 2]


126

In [3]:
t: flip `name`iq!(`Dent`Beeblebrox`Prefect;98 42 126)
t[;`iq]


98 42 126

In [4]:
t[2;]


name| `Prefect
iq  | 126

In [5]:
t[2;`iq]


126

To access items in a table t created by flipping a column dictionary d, simply reverse the order of the arguments in the projections of d. We also reverse the roles of i and j compared to dictionaries to make things more natural from the table perspective.

  • t[i;] / row i is dictionary mapping column names to values
  • t[i] / ith element of list t...same as previous
  • t[;cj] / vector of column values for column cj

This validates the implementation of a table as a flipped column dictionary. Retrieving rows and columns conforms to conventional matrix notation in which the first index denotes the row and the second index the column.

Table Display

Observe that rows and columns of a table display are indeed the transpose of the dictionary display, even though the internal data layout is the same.


In [6]:
d


name| Dent Beeblebrox Prefect
iq  | 98   42         126    

In [7]:
t


name       iq 
--------------
Dent       98 
Beeblebrox 42 
Prefect    126