QStudio
Query kdb+ Servers and Chart Results.
kdb+ Tables
In this tutorial we will cover how to create a table, operations we can perform on tables, accessing and manipulating table data and inserting data into a table.
Contents
Create a kdb Table
In an earlier tutorial I said that lists and dictionaries were the only fundamental data structures in kdb that all others built on top of. A table is a specially formed dictionary from column names to equal length vectors of data. This simplicity is also powerful as it allows us to access and manipulate table data using all our previously learnt list/dictionary methods.
Typically when defining a table we use the q language shorthand notation. Parentheses to contain our table, with vector data assigned to column names, separated by semi-colons. (The square bracket is for defining keyed tables and we will look at this further in keyed tables).
Defining Empty Tables
Normally when defining a table, you will define it as empty and insert data later, e.g. from a feedhandler. When defining a table the columns should be set to the correct type when possible as this allows type checking inserted data.
Common Table Functions
The most common functions used with tables are shown below:
Set Operations
The set functions that we previously used on lists also work on tables:
Accessing a Table - qSQL
There are three methods for accessing an unkeyed table, qSQL, as a dictionary and as a list. qSQL is the most common method and we will look at it in much more detail later. Unlike standard SQL no * is needed to select all columns and some simple queries would include:
At the start we demonstrated a table is a dictionary from a list of column name symbols to vectors of data. We can use that method of accessing a table, by supplying a column name as a lookup, we return that columns data as a list.
Alternatively if we treat the table as a list of dictionaries, we can index into that list to retrieve multiple items. Other standard list functions work similarly, returning a number of rows from the table.
Inserting Data into a Table
To insert data into an unkeyed table, we use the insert function to append new rows to the end of the table. Insert allows multiple formats including single lists, multiple batch lists and insertion of tables.