Tables¶
Tables are first-class objects in q.
Construct¶
Construct a small table using table notation.
q)ec1:([]city:`Istanbul`Moscow`London`StPetersburg;country:`Turkey`Russia`UK`Russia;pop:15067724 12615279 9126366 5383890)
q)ec1
city country pop
-----------------------------
Istanbul Turkey 15067724
Moscow Russia 12615279
London UK 9126366
StPetersburg Russia 5383890
Equals means equals
In q names are assigned values with the colon.
The equals sign =
is the Equals operator. It returns a boolean.
q)a:5
q)a+2 / a gets 5
7
q)a=2 / no, it is not
0b
Unlike classical relational databases, q tables are ordered. You can index them. A table is a list of dictionaries. Any single row is a dictionary.
q)ec1 2
city | `London
country| `UK
pop | 9126366
And a list of dictionaries with the same keys is – a table.
q)ec1 2 0
city country pop
-------------------------
London UK 9126366
Istanbul Turkey 15067724
Flipping a table gets you its columns as a dictionary of vectors.
q)flip ec1
city | Istanbul Moscow London StPetersburg
country| Turkey Russia UK Russia
pop | 15067724 12615279 9126366 5383890
Flipping it again puts you back where you started.
q)flip flip ec1
city country pop
-----------------------------
Istanbul Turkey 15067724
Moscow Russia 12615279
London UK 9126366
StPetersburg Russia 5383890
So another way to construct a table:
q)ec2:flip`city`country`pop!(`Berlin`Kyiv`Madrid;`Germany`Ukraine`Spain;3748148 3703100 3223334)
q)ec2
city country pop
----------------------
Berlin Germany 3748148
Kyiv Ukraine 3703100
Madrid Spain 3223334
CSVs are a common source of tables.
Work¶
There are two ways to work with tables and you can mix them to suit yourself.
QSQL queries are very like SQL. (Perhaps a little less verbose.)
q)select city,pop from ec2 upsert ec1
city pop
---------------------
Berlin 3748148
Kyiv 3703100
Madrid 3223334
Istanbul 15067724
Moscow 12615279
London 9126366
StPetersburg 5383890
Or you can think in terms of the underlying q objects.
The Join operator ,
catenates lists.
q)1 2 3,10 20
1 2 3 10 20
q)"abc","def"
"abcdef"
Two tables are two lists of dictionaries.
q)ec2,ec1
city country pop
-----------------------------
Berlin Germany 3748148
Kyiv Ukraine 3703100
Madrid Spain 3223334
Istanbul Turkey 15067724
Moscow Russia 12615279
London UK 9126366
StPetersburg Russia 5383890
Keys¶
Setting one or more columns of a table as its key divides it into two tables (the keyed and non-keyed columns) and from them makes a dictionary.
The dictionary’s key is the key column/s of the table. Its value is the unkeyed column/s. Both key and value are tables.
Persist¶
Any object can be persisted to a file.
q)conts:`Africa`Asia`Australia`Europe`NorthAmerica`SouthAmerica
q)`:path/to/continents set conts
`:path/to/continents
q)get `:path/to/continents
`Africa`Asia`Australia`Europe`NorthAmerica`SouthAmerica
q)`:path/to/ec set ec
`:path/to/ec
q)select from `:path/to/ec where pop>5000000
city country pop
-----------------------------
Istanbul Turkey 15067724
Moscow Russia 12615279
London UK 9126366
StPetersburg Russia 5383890
Go large¶
Flat tables are limited by the absolute maximum size of a vector in kdb+.
Tables up to 100 million rows can be splayed (one file for each column) across directories.
If your table is larger – or grows – you can partition it; usually by time period.
If your table exceeds disk size, you can segment it. (This can also improve I/O performance of a partitioned table.)
get
, set
,
save
Splayed tables,
Partitioned tables
Q for Mortals §8. Tables,
§14. Introduction to kdb+