Tutorials/Tables I

From Kx Wiki
Jump to: navigation, search

Prerequisites: The Basics, Datatypes, Lists, Functions, Dictionaries.

Tables form the basis of a kdb+ database and are consequently one of the most important data structures in q. This tutorial deals with creating and manipulating tables, including inserts and upserts. Tables II is about queries and joins.

Contents

Tables: keyed and unkeyed

We saw in Tutorials/Dictionaries how to create a table by flipping a dictionary. There is another (perhaps simpler) syntax for defining tables.

q)family:([]name:`John`Mary`David; age:52 49 18; hair:`brown`black`blonde; eyes:`blue`brown`blue)
q)family
name    age hair   eyes 
------------------------
John    52  brown  blue 
Mary    49  black  brown
David   18  blonde blue 
q)type family
98h
q)([]a:1 2 3;b:4 5 6 7)			/ columns must be the same length
'length

The table above was defined within parentheses, with columns separated by a semicolon. The square brackets at the beginning are empty because the table is unkeyed. Unkeyed tables have a type of 98h. So what is a keyed table? We can define some of the columns within the square brackets, which give the following result:

q)keyedtab:([a:`f`y`i] b:3 4 5; c:6 7 8)
q)keyedtab
a| b c
-| ---
f| 3 6
y| 4 7
i| 5 8
q)type keyedtab
99h

See the vertical line separating the first column from the others, similar to how dictionaries are displayed? Notice too that the type of the keyed table is 99h – just like dictionaries!

A keyed table is a dictionary whose keys and values are both tables.

Keyed and unkeyed tables act differently in different situations, which we will see later, so it is important to be able to key and unkey tables. There are two syntaxes for this:

q)2!family		/ keys on the first 2 columns
name    age| hair   eyes 
-----------| ------------
John    52 | brown  blue 
Mary    49 | black  brown
David   18 | blonde blue 
q)3!family		/ keys on the first 3 columns
name    age hair  | eyes 
------------------| -----
John    52  brown | blue 
Mary    49  black | brown
David   18  blonde| blue 
q)`age`eyes xkey family	/ specifies columns to key on
age eyes | name    hair  
---------| --------------
52  blue | John    brown 
49  brown| Mary    black 
18  blue | David   blonde

Have a look at the reference pages for ! and xkey for more information about these functions.

Creating an unkeyed table from a keyed one uses exactly the same syntaxes, you just 'key on nothing'.

q)0!keyedtab
a b c
-----
f 3 6
y 4 7
i 5 8
q)() xkey keyedtab       / () is an empty list
a b c
-----
f 3 6
y 4 7
i 5 8

Indexing tables

In the q/kdb+ world, 'indexing' usually refers to accessing elements of lists or other structures,not speeding up access, which is done using attributes.

Unkeyed tables are like lists: rows are numbered starting with 0.

q)family 0 2
name  age hair   eyes
---------------------
John  52  brown  blue
David 18  blonde blue
q)family 1
name| `Mary
age | 49
hair| `black
eyes| `brown

Notice that a single row is returned as a dictionary, but multiple rows make a table. (A table is a list of dictionaries.)

Keyed tables are dictionaries, and so their rows are indexed by keys instead of numbers.

q)keyedtab`y
b| 4
c| 7

We can also access the columns of an unkeyed table as if it were a dictionary:

q)family`eyes
`blue`brown`blue

Inserts and upserts

How do you add rows to a table? Using insert or upsert. The name of the table is passed into these functions as a symbol.

q)`family insert (`Jessica;13;`black;`blue)
,3
q)family
name    age hair   eyes 
------------------------
John    52  brown  blue 
Mary    49  black  brown
David   18  blonde blue 
Jessica 13  black  blue 

The result from insert is a list of the row indices that have been added to the table:

q)family[3]
name| `Jessica
age | 13
hair| `black
eyes| `blue

The result from upsert, however, is simply the name of the modified table:

q)`family upsert (`Avery;30;`black;`green)
`family
q)family
name    age hair   eyes 
------------------------
John    52  brown  blue 
Mary    49  black  brown
David   18  blonde blue 
Jessica 13  black  blue 
Avery   30  black  green

For the unkeyed table family, insert and upsert, though returning different results, produced the same outcome – they both added a row to the family table.

Type out the following code and see what happens when you insert or upsert into a keyed table.

`keyedtab insert (`p;10;20)
`keyedtab insert (`i;11;12)
`keyedtab upsert (`i;11;12)
`keyedtab upsert (`j;13;20)

The first statement `keyedtab insert (`p;10;20) inserted a new record under the key `p, and returned its row: 3.

The second statement `keyedtab insert (`i;11;12) attempted to insert a new record with the key `i. But that key already existed. So insert failed, and returned its own name as an error message.

The third statement `keyedtab upsert (`i;11;12) found its key `i already in use and updated the corresponding record, returning the name of the modified table.

The fourth statement `keyedtab upsert (`j;13;20) inserted a record under the new key `j.

In a keyed table, insert creates new table records, while upsert inserts or creates them.

Exercises

1. Make an unkeyed table with 3 columns: one for the days of the week (as symbols), one for the temperature (integers) and the last with string describing the weather e.g. "cloudy".

2. Key the your table on the day columnn.

3. Add a row to your (keyed) table with a fictional eight day of the week.

4. Replace the entry for Monday with a temperature of 1000 and the description "appallingly wet".

Solutions

Personal tools
Namespaces
Variants
Actions
Navigation
Print/export
Toolbox