Append Missing Records Then Re-Sort and Re-Compress

Question

The original table t was missing some records. Append the following data to the appropriate date partitions, then re-sort by sym and time (with p# on sym) and re-apply compression.

N:100
missing:([] date:N?.z.d + til 3;
           time:N?100?23:59:59.999;
           sym:N?`A`B`C;
           price:N?10.0;
           qty:N?1000j)

Solution

Step 1 — append the missing rows:

N:100
missing:([] date:N?.z.d + til 3;
           time:N?100?23:59:59.999;
           sym:N?`A`B`C;
           price:N?10.0;
           qty:N?1000j)

path:`:/hdb
dts:exec distinct date from missing

appendPerDate:{[d]
  data:delete date from select from missing where date = d;
  tblPath:` sv path,`$string d,`t,`;
  tblPath upsert .Q.en[path;data]
 }
appendPerDate each dts

Step 2 — re-sort and re-compress:

.z.zd: 17 2 6

onDiskSort:{[d]
  tblPath:.Q.par[path;d;`t];
  @[`sym`time xasc tblPath;`sym;`p#]
 }
onDiskSort each dts

Explanation

upsert vs set — use upsert to append new rows to an existing splayed table. upsert extends each column file without reading the existing data back into memory. Using set would overwrite the partition entirely, losing the original rows.

tblPath upsert .Q.en[path; data]   / appends — correct
tblPath set   .Q.en[path; data]    / overwrites — wrong for appending

Why re-sort after appending — the p# attribute requires all rows for a given symbol to be contiguous. Appending new rows breaks this guarantee: the new rows are added to the end of each column file, so symbols from the new data are interleaved with symbols from the original data. The attribute becomes invalid and must be re-established after sorting.

In fact the append removes p# from the on-disk sym column (when appending to a column on disk, only the sorted attribute is kept), so until the partition is re-sorted and p# re-applied, sym queries on it fall back to scanning the whole column.

.z.zd: 17 2 6 sets the default compression parameters for all file writes in the current process. With this set, the on-disk xasc rewrites the column files compressed — no separate compression step is needed. This saves a pass compared with writing first and compressing afterwards, as in the previous question.

The final state of each partition: sorted by sym then time, p# on sym, all columns compressed at gzip level 6.