Intraday HDB queries

[JerLucid]

I've configured a WDB to write down using writedownmode:`partbyattr

where the attribute is just the usual ticker symbol, and I have 4 tables.

I'm interested in having the ability to query this WDB database intraday, but I'm

unsure of the best way to do it.

I was thinking of having a single HDB process, and have it load a different

directory depending on the table being queried. Or simple have 4 HDB instances,

each loading a table folder. 

I then came across this utility,

https://github.com/quintanar401/DCoQ#multiple-dbs-in-one-q-process

which allows a single process to load multiple DBs at once, but have yet

to test it out.

So I'm wondering what is the recommended approach for this scenario?

and if anyone has used the utility mentioned above?

Thanks

Jeremy

[James Massey]

Hi Jeremy,

This could be done by modifying how the WDB writes intraday to allow a single intraday process that loads in what the WDB writes. The data currently can't load due to the structure that it is saved in. An option for this process would be to restructure this data using integers as partitions instead of date with each sym mapped to an integer. The EOD merge logic would need adjusted to use this method as well as the intraday save logic. The mapping of syms to integers would need handled: using the existing enumerations would be the simplest approach to this. Also, queries would need to include a "where int=..." clause for efficiency. The new structure is shown below: 

Current Structure: 

/wdb

        /date 

                /trade  

                      /sym1 

                      /sym2

                /quote 

                      /sym1 

                      /sym2

         

New Structure:

/wdb

         sym

         /0  

              /trade

              /quote

          /1 

              /trade

              /quote

 

We wouldn't recommend following the steps in the link as this repository uses undocumented processes that shouldn't be replied upon in a production system.

Thanks,

James

[JerLucid]

Thanks James, that new structure looks like a good choice for my use case.

It would be the same number of writes really, just different structure, and the merge process would be similar also. 

The only question is what am I losing in moving away from the current structure?

Is it that the current structure allows more flexibility, in that you can part by multiple syms, like SYM_EXCHANGE

[James Massey]

Hi Jeremy, 

Yes, what is lost from the current structure is the potential for the data to be parted by multiple syms. 
The linked blog post explains a way to work around this drawback of the new structure. The blog linked discusses encoding multiple pieces of data in an int partition, for example, to encode sym and exchange for each int, instead of just the sym. However, implementing this would add additional complexity to the logic required to be added to the WDB.

kdb+ IoT Database Structure | AquaQ

Thanks,
James

[JerLucid]

Ok thanks for that feedback James. 

I have switched over to the writedown method you recommended and seems to be working well so far. Only minimal changes were needed

[Edward Richards]

With the new structure above, what happens at EOD? Do we delete from each partition where date<today? Or just purge everything under wdb/ and restart?

Would it be easier to have a separate intraday-db process that writes and serves this data, meaning the wdb can remain unchanged? The drawback would be then having two copies of today's data on-disk, but this might be outweighed by the benefit of not having to tweak existing WDB code.

Thanks

[JerLucid]

Sorry Edward, I didnt see this message until now.

In this setup my WDB is still only performing the task of writing, it is not loading the DB being written to.

I have a separate process which has the ability to load this intraday DB

At end of day, the sort process/sortworker merges the data into the final HDB location and the data is cleared