Updating point-in-time (bitemporal) table

Question

Let's have a table storing point-in-time (bitemporal) data:

pit:([]dt:`date$();sym:`symbol$();val:`float$();stamp:`timestamp$())

Sample data might look like:

`pit insert (2015.01.05 2015.01.06 2015.01.05;`IBM`IBM`MSFT;105.11 106.6 35.3; 2015.02.01D05:01:25.0 2015.02.01D05:01:25.0 2015.02.01D05:01:25.0)
pit
dt         sym  val    stamp                        
----------------------------------------------------
2015.01.05 IBM  105.11 2015.02.01D05:01:25.000000000
2015.01.06 IBM  106.6  2015.02.01D05:01:25.000000000
2015.01.05 MSFT 35.3   2015.02.01D05:01:25.000000000

For example, on the stamp point in time, we've recorded for IBM symbol a value of 105.11 valid for 2015.01.05

There are new data coming in continuously and some of them are to be inserted as new records into the pit table, but only if they convey new information. No existing records in pit may be deleted nor updated/overwritten. I.e. we want to keep track of outdated (if any) values for auditing or authenticity purposes. Think of earnings estimate updates over time.

For example, later on we might receive:

new:([]dt:`date$();sym:`symbol$();val:`float$())
`new insert (2015.01.05 2015.01.06;`IBM`IBM;105.22 106.6)
new
dt         sym val   
---------------------
2015.01.05 IBM 105.22
2015.01.06 IBM 106.6

After incorporating new information into pit, the latter shall look like:

pit
dt         sym  val    stamp                        
----------------------------------------------------
2015.01.05 IBM  105.11 2015.02.01D05:01:25.000000000
2015.01.05 IBM  105.22 2015.03.10D15:43:50.000000000
2015.01.06 IBM  106.6  2015.02.01D05:01:25.000000000
2015.01.05 MSFT 35.3   2015.02.01D05:01:25.000000000

Note the "new" 105.22 value for IBM symbol entered the pit with the current timestamp (2015.03.10D15:43:50 at the time of writing). Also, the 106.6 value from new did not update the timestamp in the pit in any way, because we had had that value already reflected in the pit carrying older timestamp.

How to write the respective insert statements using q?

Note: For what it's worth, pit is to be partitioned by additional column source not shown here for the sake of simplicity. Also, partitions will have `g#sym(possibly `p#sym instead) and `s#dt attributes.

score 2 · Accepted Answer · edited Nov 30 '17 at 00:57

2

You can use 'upsert' in following way:

reference: http://code.kx.com/q/ref/qsql/#upsert

Step 1. I am adding status column in 'new' table (otherwise it will be null for new rows in final table)

       q) new:update stamp:`timestamp$.z.z from new

Step 2. Make those columns of 'new' as primary keys whose modification is criteria for addition of row. In your case it's all columns except stamp column.

       q)new:except[cols new;`stamp] xkey new

Step 3: Upsert tables as (check the order: pit is on right hand side)

       q) pit: 0!new upsert pit

I have removed primary keys attribute from result.

Step 4: Order the table according to stamp (or any other column).

       q) pit: `stamp xasc pit

Here is one function to do all those steps:

    q) myinsert:{[pit;new] `stamp xasc 0!(except[cols new;`stamp] xkey new:update stamp:`timestamp$.z.z from new) upsert pit}

edited Nov 30 '17 at 00:57

Thomas Smyth - Treliant

4,993
6
25
36

answered Mar 11 '15 at 05:56

Rahul

3,914
1
14
25

Nice! I was obviously looking at `upsert`, but in wrong order: `pit upsert new` instead of `new upsert pit`. I was under false impression that from the documentation syntax `r:table upsert new_records` , the argument `new_records` is actually my `new` table. Thanks! – Daniel Krizian Mar 11 '15 at 13:29
I am now wondering whether upserting the whole, potentially very big `pit` table to the fractional `new` table, will create significant performance/memory issues. But that's a separate topic – Daniel Krizian Mar 11 '15 at 13:33
One question: why does `pit myinsert new` not work while `myinsert[pit;new]` does? Could I make the first syntax work somehow? – Daniel Krizian Mar 14 '15 at 17:59
'pit insert new ' uses infix form which is applicable to kdb inbuilt functions. myinsert is user defined function which should be called as normal function call. Following site suggests one way to use it as infix form : http://www.kdbfaq.com/kdb-faq/how-can-i-define-my-own-infix-functions.html – Rahul Mar 15 '15 at 09:36
@Daniel You are right about performance issue in case of very big pit table. Have to consider other solution in case of tight memory/cpu situation. – Rahul Mar 15 '15 at 09:37

Updating point-in-time (bitemporal) table

1 Answers1