8

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?

Robert Kubrick
  • 8,413
  • 13
  • 59
  • 91
  • Just reading this question taught me that I could add attributes to tables columns with an `update` statement. Thanks! – kevinarpe Dec 15 '16 at 10:19

2 Answers2

7

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

Thomas Smyth - Treliant
  • 4,993
  • 6
  • 25
  • 36
user1895961
  • 1,176
  • 9
  • 22
  • 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. – user1895961 Jun 04 '13 at 19:51
  • 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? – Robert Kubrick Jun 04 '13 at 20:59
  • 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. – Robert Kubrick Jun 04 '13 at 21:02
  • can you give the call you make and error you get when you try to apply the sorted attribute? – user1895961 Jun 04 '13 at 21:16
  • `q)h "setattrcol[\`:/data/mddb;\`pmd;\`ts;\`s]" 's-fail` – Robert Kubrick Jun 04 '13 at 21:30
  • 1
    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](http://code.kx.com/wiki/JB:KdbplusForMortals/splayed_tables#1.2.5.3_Sorting_by_a_Column_on_Disk) to work on partioned tables. – user1895961 Jun 04 '13 at 21:47
  • \`s requires the column sorted in ascending order, shouldn't `idesc` in the example be replaced by `iasc`? – Robert Kubrick Jun 04 '13 at 22:47
  • The example given is just concerned with sorting, and not applying the attribute. – user1895961 Jun 04 '13 at 23:44
5

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    
  ...
David Hall
  • 341
  • 2
  • 8