Can I set column attributes for a kdb partitioned table?

Question

Is it possible to set column attributes for a partitioned table?

q)h "update `g#ticker from `pmd"
'par
q)h "update `s#ts from `pmd"
'par
q)

Should I set the attributes on the memory table, before I run the partitioning? Will the attributes be preserved after the partitioning?

Cape

Just reading this question taught me that I could add attributes to tables columns with an update statement. Thanks! – Compilation

Answer 1

Take a look at the setattrcol in dbmaint.q. This script is very very useful when working with partitioned databases.

Endomorphism

In order to apply the sorted attribute, the data must be in ascending order. Applying the s# does not sort the data, it just indicates the list is sorted in ascending order. – Endomorphism

Thanks. Also it looks like after I ran setattrcol() all dates I'm adding to the database include that attribute. That would be consistent, since the attribute is set at the table level, not just for the currently partitioned dates. There must be an indicator stored somewhere that tells kdb to build the group indexing anytime I'm inserting new dates? – Cape

Concerning `s, the ts column should be sorted, it's a local timestamp that increments with each row. Also the error code is returned immediately. I wonder how does kdb check that the column is not sorted in no time given I have millions of entries for each date. – Cape

can you give the call you make and error you get when you try to apply the sorted attribute? – Endomorphism

q)h "setattrcol[`:/data/mddb;`pmd;`ts;`s]" 's-fail – Cape

This suggests the data isn't in fact sorted. My guess is q is clever enough to kick out an error immediately once an element is found out of order, hence why it doesn't take a large amount of time. You could attempt to sort the on data first. You could modify the functionality here to work on partioned tables. – Endomorphism

`s requires the column sorted in ascending order, shouldn't idesc in the example be replaced by iasc? – Cape

The example given is just concerned with sorting, and not applying the attribute. – Endomorphism

Answer 2

In order for the partitions on disk to be sorted, you need to iterate through the partitions and use xasc as follows: for each partition .. assuming you have a quote table partitioned by date to sort by `timestamp

{`timestamp xasc `$":./2014.04.20/quote/"}

Once you've finished sorting each partition, the s attribute will appear ontimestamp column..

q)meta quote
  c        | t f a
  ---------| -----
  date     | d    
  timestamp| p   s
  pair     | s    
  side     | c    
  ...

Vostok