Appending to Splayed Tables

Writing a Splayed Table

A splayed table is created by setting a path that ends with /. The trailing slash tells kdb+ to write each column as a separate file inside the directory. Symbol columns must be enumerated with .Q.en before writing:

trade: ([]
  time:   09:30:00.000 09:30:01.000 09:30:02.000;
  sym:    `AAPL`GOOG`AAPL;
  price:  150.5 2840.1 150.7;
  size:   100 50 200)

/ Write as splayed table (creates trade/ directory with column files)
`:trade/ set .Q.en[`:.] trade

After running this, the trade/ directory contains:

trade/
  .d       ← column order file
  time     ← time column binary
  sym      ← sym column binary (enumerated integers)
  price    ← price column binary
  size     ← size column binary

The sym file at the database root contains the master symbol list. All sym columns in all tables reference integer positions in this file.

Appending Rows to a Splayed Table

Use upsert to append new rows to an existing splayed table. The new rows are appended to each column file without loading the full table into memory:

newTrades: ([]
  time:   09:30:03.000 09:30:04.000;
  sym:    `MSFT`AAPL;
  price:  310.2 150.9;
  size:   75 150)

/ Append to existing splayed table on disk
`:trade/ upsert .Q.en[`:.] newTrades

Only the new data is written: the existing column files are extended, and the prior data is not rewritten. This is the mechanism a WDB uses to flush intraday data to disk.

Writing to a Date-Partitioned HDB

In a date-partitioned HDB, the path includes the date directory. .Q.par constructs the correct path based on the database root, date, and table name:

hdbRoot: `:/data/hdb

/ Write trade data for 2025-01-01
path: .Q.par[hdbRoot; 2025.01.01; `trade]    / `:/data/hdb/2025.01.01/trade — note: no trailing slash
(` sv path,`) set .Q.en[hdbRoot] trade       / add the trailing slash so set splays the table

/ Or use .Q.dpft, which also sorts by a column and applies p# to it
.Q.dpft[hdbRoot; 2025.01.01; `sym; `trade]

.Q.par returns the path without a trailing slash, so ` sv path,` adds one — without it, set would write the whole table into a single file instead of splaying it.

.Q.dpft[root; partition; column; tableName] writes the named global table to the partition, enumerating its symbols, sorting it by column and applying the parted attribute to that column. .Q.hdpf, used by the RDB at end of day, calls it for every table in the root namespace (and then empties them and tells the HDB to reload).

Maintaining the .d File

The .d file in a splayed table directory records the column order. If you add or remove columns from an on-disk splayed table, you must update .d to reflect the new structure:

/ Add a new column to an existing splayed table
`:trade/newCol set newValues

/ Update .d to include the new column
`:trade/.d set cols[trade], `newCol    / append to column list

/ Or reorder columns
`:trade/.d set `time`sym`price`size`newCol

If .d lists a column that has no file, reading the table fails; a column file that isn't listed in .d is simply ignored. Always update .d after structural changes.

Loading a Splayed Table

Load a splayed table by pointing \l at the parent directory, or get the table directly:

/ Load all tables in the current directory (including splayed ones)
\l .

/ Or reference a specific table — columns are memory-mapped lazily
t: get `:trade/

/ For a partitioned database, load the root
\l /data/hdb

After loading, the table behaves like any other q table. The difference is that column data is fetched from disk on demand rather than all sitting in RAM.