Programming/Kdb/Labs/Big data in kdb

Example table

Let us create and populate the trades table:

trades : ([] code:`symbol$(); date:`date$(); time:`time$(); price:`float$(); volume:`short$(); mktflag:`symbol$(); comments:());
.[`trades; (); ,; (
    (`CLM16; 2016.04.07; 10:20:00.329; 38.3  ; 4h; `E; "NYMEX/CME Group trade");
    (`GCM16; 2016.04.07; 12:00:00.055; 1239.7; 6h; `E; "");
    (`GCM16; 2016.04.07; 12:37:02.106; 1240.5; 1h; `E; "");
    (`CLM16; 2016.04.07; 13:00:00.128; 38.04 ; 3h; `E; "NYMEX/CME Group trade");
    (`VXK16; 2016.04.07; 13:22:05.617; 18.85 ; 5h; `E; "");
    (`GCM16; 2016.04.07; 14:35:01.241; 1241.2; 1h; `E; "");
    (`GCM16; 2016.04.08; 10:13:01.048; 1240f ; 3h; `E; "");
    (`VXK16; 2016.04.08; 11:34:53.417; 18.53 ; 1h; `E; "Transaction represents a trade in two contract months in the same class");
    (`CLM16; 2016.04.08; 12:00:00.227; 40.61 ; 3h; `E; "NYMEX/CME Group trade");
    (`VXK16; 2016.04.08; 12:44:00.684; 18.44 ; 2h; `E; "Transaction represents a trade in two contract months in the same class");
    (`VXK16; 2016.04.08; 12:45:33.130; 18.49 ; 1h; `E; "Transaction represents a trade in two contract months in the same class");
    (`CLM16; 2016.04.08; 15:20:02.000; 40.78 ; 3h; `E; "NYMEX/CME Group trade");
    (`CLM16; 2016.04.11; 11:00:00.105; 41.43 ; 2h; `E; "NYMEX/CME Group trade");
    (`VXK16; 2016.04.11; 14:00:00.829; 18.35 ; 1h; `E; "");
    (`VXK16; 2016.04.11; 15:14:58.775; 19.05 ; 2h; `E; "");
    (`GCM16; 2016.04.11; 16:00:00.044; 1257.9; 1h; `E; "");
    (`GCM16; 2016.04.11; 16:28:34.311; 1258.7; 1h; `E; ""))]

Each row represents a trade in a futures contract. The value in the code column identifies that contract. The date and time columns give the dates and times of the trades as reported by the exchange. The price column contains the trade prices per contract. The volume column contains the number of contracts traded. The name of the mktflag column stands for "market flag", which distinguishes the pit trades (in which case the value in the column is `P) from the electronic trades (in which case the value is `E). In our table, which represents a hypothetical subsample from some large data set, all the trades are electronic trades. The comments column contains arbitrary comments on the data rows as strings — not as symbols. In practice, the specific comments in our example ("NYMEX/CME Group trade", "Transaction represents a trade in two contract months in the same class") would be implemented as symbol or numeric flags rather than as strings. We chose to use the string data type to demonstrate its use in a table.

Saving a table as a single binary file

We can use the following command to save this table as a binary file:

`:tradesfile set trades

The resulting file will have 965 bytes and will reside in the current directory, which you can check with

\cd

By default this will be the directory containing the q.exe executable. To save it in a different directory, we could use something like this:

(`$":C:/Users/Paul Bilokon/Documents/tradesfile") set trades

The brackets are needed as

`$":C:/Users/Paul Bilokon/Documents/tradesfile" set trades

is interpreted by q, from right to left, as

`$(":C:/Users/Paul Bilokon/Documents/tradesfile" set trades)

which isn't what we want.

In the case of success the return is the file path as a symbol:

q)(`$":C:/Users/Paul Bilokon/Documents/tradesfile") set trades
`$":C:/Users/Paul Bilokon/Documents/tradesfile"

Loading a table from a single binary file

We can then load the table from a file either using get, in which case we can specify the new table name (by assigning to a variable with that name the result of get):

trades1:get`$":C:/Users/Paul Bilokon/Documents/tradesfile"

or using load, in which ase the loaded table will be assigned to a new variable, that variable's name matching the name of the file; in this particular case, tradesfile:

load `$":C:/Users/Paul Bilokon/Documents/tradesfile"

Splayed tables

Instead of saving the table as a single binary file, we could save it splayed, i.e. so that each column is saved in a separate binary file.

Queries often refer to a small subset of such a table's columns. Storing the columns in separate files allows kdb+ to save time by loading only some of them.

To achieve this, we would normally call set with a directory path, rather than a file path, as its first argument.

However, the following will fail with `type:

(`$":C:/Users/Paul Bilokon/Documents/tradesdir/")set trades

Unenumerated columns

set will disallow us to splay the table trades since it contains unenumerated columns of type symbol. (The same error would be thrown if the table were keyed; we would need to remove the key with

()xkey trades

before saving the table.)

We can remove those columns and save the table in one line with

(`$":C:/Users/Paul Bilokon/Documents/tradesdir/")set `code`mktflag _ trades

Notice the traling / in the directory path.

As a result of running this, the directory tradesdir will be created under C:/Users/Paul Bilokon/Documents. It will contain the files with names

• .d,
• comments,
• comments#,
• date,
• price,
• time, and
• volume.

Enumerating the columns

We couldn't save the original trades table because the columns of type symbol, namely code and mktflag, weren't enumerated. We shall now show how to enumerate them. Before proceeding, delete the directory tradesdir with all its contents, in case you created that directory by following the steps outlined above, so that we start with a clean slate. To enumerate the symbol columns in trades, we use .Q.en:

tradesenum:.Q.en[`$":C:/Users/Paul Bilokon/Documents/tradesdir/";trades]

This will create the directory tradesdir with a single binary file in it, named sym. That file enumerates the values found in the columns of type symbol, namely `CLM16, `GCM16, `VXK16, and `E.