TorQ: .loader.loadallfiles and referential integrity leads to `cast error

Question

I have a table volatilitysurface and a detail table volatilitysurface_smile as part of the detail table I define a foreign key to the master table i.e.

volatilitysurface::([date:`datetime$(); ccypair:`symbol$()] atm_convention:`symbol$(); ...);
volatilitysurface_smile::([...] volatilitysurface:`volatilitysurface$(); ...);

When I try using AquaQ's TorQ .loader.loadallfiles to load the detail table volatilitysurface_smile I need as part of the "dataprocessfunc" function to dynamically build the foreign key field i.e.

rawdatadir:hsym `$("" sv (getenv[`KDBRAWDATA]; "volatilitysurface_smile"));
.loader.loadallfiles[`headers`types`separator`tablename`dbdir`partitioncol`partitiontype`dataprocessfunc!(`x`ccypair...;"ZS...";enlist ",";`volatilitysurface_smile;target;`date;`month;{[p;t] select date,ccypair,volatilitysurface,... from update date:x,volatilitysurface:`volatilitysurface$(x,'ccypair) from t}); rawdatadir];

Note the part:

update date:x,volatilitysurface:`volatilitysurface$(x,'ccypair) from t

The cast error is pointing to the construction of the volatilitysurface key. However, this works outside .loader.loadallfiles and the tables are globally :: and fully defined before calling the .loader.loadallfiles function.

Any ideas how to deal with this use-case? If the detail table foreign key is not initialized then the insertion will fail.

Answer 1

The error may be due to the scoping in the update. As you are running the cast/update within the .loader namespace the tablename would need to be full scoped (`..volatilitysurface).

eg. update date:x,volatilitysurface:`..volatilitysurface$(x,'ccypair) from t

Regards,

Scott

Answer 2

Are you sure that all possible x & ccypair combinations are in the volatilitysurface table? The 'cast error would seem to suggest this is not the case e.g.

q)t:([a:1 2 3;b:`a`b`c] c:"ghi")
q)update t:`t$(a,'b) from ([] a:2 3 1;b:`b`c`a)
a b t
-----
2 b 1
3 c 2
1 a 0
q)update t:`t$(a,'b) from ([] a:2 3 1 5;b:`b`c`a`d)
'cast
  [0]  update t:`t$(a,'b) from ([] a:2 3 1 5;b:`b`c`a`d)
              ^

Note in the second case I have the a-b pair of (5;`d), which isn't present in the table t, and so I get the 'cast error

You can determine if there are missing keys, and which they are, like so:

q)all (exec (a,'b) from ([] a:2 3 1;b:`b`c`a)) in key t                //check for presence, all present
1b
q)all (exec (a,'b) from ([] a:2 3 1 5;b:`b`c`a`d)) in key t            //check for presence, not all present
0b
q)k where not (k:exec (a,'b) from ([] a:2 3 1 5;b:`b`c`a`d)) in key t  //check which keys AREN'T present
5 `d

If this is the case, I guess you kind of have two options:

• Make sure the volatilitysurface table is loaded correctly - assuming you have full data coverage in your files, presumably every possible key should be present in this table
• If there is the possibility of possibly keys not being present in the volatilitysurface table, you could perhaps add dummy records to it before making the foreign key (which could be replaced if an actual record comes in later

The second option could perhaps work something like this:

q.test){if[count k:k where not (k:exec (a,'b) from x) in key `..t;@[`..t;;:;value[`..t](0N;`)]'[k]];update t:`t$(a,'b) from x}([] a:2 3 1;b:`b`c`a)
a b t
-----
2 b 1
3 c 2
1 a 0
q.test){if[count k:k where not (k:exec (a,'b) from x) in key `..t;@[`..t;;:;value[`..t](0N;`)]'[k]];update t:`t$(a,'b) from x}([] a:2 3 1 5 6;b:`b`c`a`d`e)
a b t
-----
2 b 1
3 c 2
1 a 0
5 d 3
6 e 4
q.test)value `..t //check table t, new dummy records added by previous call
a b| c
---| -
1 a| g
2 b| h
3 c| i
5 d|
6 e|

I've done these tests inside a namespace as this is how the dataprocess function will run in TorQ (i.e. at certain places you need to use `..t to access t in the root namespace.) The analogous version of this function for your setup (with some nicer formatting than the one-liners above) would be something like:

{
  if[count k:k where not (k:exec (x,'ccypair from volatilitysurface_smile) in key `..volatilitysurface;  //check for missing keys
  @[`..volatilitysurface;;:;value[`..volatilitysurface](0Nz;`)]'[k]];  //index into null key of table to get dummy record and upsert to global volatilitysurface table
  update volatilitysurface:`volatilitysurface$(x,'ccypair) from x  //create foreign key
 }