Next: , Previous: , Up: Top  


12 Tables

k9 has the ability to store data in a tabular format containing named columns and rows of information as tables. If the data to be stored and queried is large, then you should use tables. This chapter introduces the different types of data tables available in k9. Table, utable and ntable are very similar and as you’ll see in the kSQL chapter are easy to query. In the Benchmark chapter, you’ll see that tables are fast to save, read, and query.

 table t:[[]i:2 3;f:2.3 4]
utable u:[[x:..]y:..]
ntable n:`..![[]y:..]

12.1 table

The table is the most basic of the three types. A table consists of columns and rows of information where each column has a name. Tables can be created in three different ways (1) specification via table format, (2) flipping a dictionary, or (3) reading in from a file.

12.1.1 Table format

Tables can be created with the table square bracket notation.

As an example, let’s create a table with two columns named “a” and “col2” having three rows. The syntax is to surround the definition with square brackets and then have a first element of empty square brackets. Following those brackets comes first column name, colon, and the list of values, then the second column, and continuing for all the columns. For keyed tables, the initial square brackets will contain key column names as we will discuss later.

 [[] a:1 20 3; col2: 3.6 4.8 0.1]
a  col2
-- ----
 1 3.6 
20 4.8 
 3 0.1

 [[] a:1; col2:3.6]   / will error :class as lists required
[[] a:1; col2:3.6]

:class

 [[] a:,1; col2:,3.6] / using list will succeed
[[]a:,1;col2:,3.6]

12.1.2 Dictionary format

Tables can also be created by flipping a dictionary into a table.

 +`a`col2!(1 20 3;  3.6 4.8 0.1)      / +columnnames!(values)
a  col2
-- ----
 1 3.6 
20 4.8 
 3 0.1 

12.1.3 File import

Tables can also be created by reading in a file.

t.csv

a, col2
1, 3.6
20, 4.8
3, 0.1

Use load file 2:x which returns a table.

 2:`t.csv
a   col2
-- -----
 1  3.6 
20  4.8 
 3  0.1 

12.2 utable

utable (or key table) is a table where some of the columns are keyed. The combination of those columns should not have two rows with the same values. This must be enforced by the application.

 [[d:2020.09.08 2020.09.09 2020.09.10]p:140 139 150]
d         |p  
----------|---
2020.09.08|140
2020.09.09|139
2020.09.10|150

 `d^ [[]d:2020.09.08 2020.09.09 2020.09.10;p:140 139 150]
d         |p  
----------|---
2020.09.08|140
2020.09.09|139
2020.09.10|150

12.3 ntable

A ntable is a collection of tables stored in a dictionary where the keys are symbols and the values are tables. Thus, it has both the data and schema of a set of tables, much like a data dictionary in a conventional relational database. Below is an example where the keys are symbols and the values are end-of-day prices.

 x1:+`d`p!(2020.09.08 2020.09.09 2020.09.10;140 139 150)
 x2:+`d`p!(2020.09.08 2020.09.10;202 208)

eod:`AB`ZY!(x1;x2);eod  / ntable
AB|[[]d:2018.06.27 2018.06.27 2018.06.27;p:140 139 150]
ZY|[[]d:2018.06.27 2018.06.27;p:202 208]               

 eod`AB                  / table
d          p  
---------- ---
2018.06.27 140
2018.06.27 139
2018.06.27 150

Next: , Previous: , Up: Top