CODEWITHSUNDEEP
kdb+torq
SkyWalker
SkyWalker

Reputation: 14317

TorQ: How to use dataloader partitioning with separate tables that have different date ranges?

I'm trying to populate a prices and quotes database using AquaQ's TorQ. For this purpose I use the .loader.loadallfiles function. The difference being that prices is daily data and quotes is more intraday e.g. FX rates.

I do the loading as follows: 

/- check the location of database directory
hdbdir:hsym `$getenv[`KDBHDB]
/hdbdir:@[value;`hdbdir;`:hdb]
rawdatadir:hsym `$getenv[`KDBRAWDATA]

target:hdbdir;

rawdatadir:hsym `$("" sv (getenv[`KDBRAWDATA]; "prices"));
.loader.loadallfiles[`headers`types`separator`tablename`dbdir`partitioncol`partitiontype`dataprocessfunc!(`date`sym`open`close`low`high`volume;"DSFFFFF";enlist ",";`prices;target;`date;`year;{[p;t] `date`sym`open`close`low`high`volume xcols update volume:"i"$volume from t}); rawdatadir];

rawdatadir:hsym `$("" sv (getenv[`KDBRAWDATA]; "quotes"));
.loader.loadallfiles[`headers`types`separator`tablename`dbdir`partitioncol`partitiontype`dataprocessfunc!(`date`sym`bid`ask;"ZSFF";enlist ",";`quotes;target;`date;`year;{[p;t] `date`sym`bid`ask`mid xcols update mid:(bid+ask)%2.0 from t}); rawdatadir];

and this works fine. However when loading the database I get errors attemping to select from either table. The reason is that for some partitions there aren't any prices or or there aren't any quotes data. e.g. attempting to:

quotes::`date`sym xkey select from quotes;

fails with an error saying the the partition for year e.g. hdb/2000/ doesn't exist which is true, there are only prices for year 2000 and no quotes

As I see there are two possible solutions but neither I know how to implement:

  1. Tell .loader.loadallfiles to create empty schema for quotes and prices in partitions for which there isn't any data.
  2. While loading the database, gracefully handle the case where there is no data for a given partition i.e. select from ... where ignore empty partitions

Upvotes: 2

Views: 224

Answers (1)

CiaranAquaq
CiaranAquaq

Reputation: 61

Try using .Q.chk[`:hdb]

Where `:hdb is the filepath of your HDB

This fills in missing tables, which will then allow you to preform queries.

Alternatively you can use .Q.bv, where the wiki states:

If your table exists in the latest partition (so there is a prototype for the schema), then you could use .Q.bv[] to create empty tables on the fly at run-time without having to create those empties on disk.

Upvotes: 2

Related Questions