Partitioned table -> not created for existing partitions fails

Community Support
1

Having partition created for table test_bars in partition 2017.05.25, however not having any in another partition e.g. in 2015.01.02. where I have by the chance other tables but not this one… select fails on table.

structure (2017.05.25 contains test_bars as there is no data for other partitions):

equitysym

   -> 2017.05.25

         -> test_bars

         -> quote

   -> 2017.05.24

         -> quote

   …

   ->2015.01.02

         -> quote

code:

\l /hdb/equitysim

select from test_bars 

Obviously there is solution to create empty tables for every partition but this is tricky and very uncomfortable. Is there anything else I might use? ( defining window with .Q.view is not to be under consideration) 

Thank you.

Jakub

error:

'./2015.01.02/test_bars/tstamp. OS reports: The system cannot find the path specified.

  [0]  .Q.s .st.tmp:select from test_bars

2

.Q.bv

3

What is the issue with using .Q.chk? 

4

I think its just personal choice tbh - but I cant vouch for bv enough from my side. Some reasons for it over chk:

faster - bv creates in memory dictionaries/keyed tabs of missing partitions-tables rather than on disk files that chk does - thus much faster to run, much faster to access during select,

pointless files - the empty files created as a result of chk are pointless, imo, causing unnecessary reads and files to live in the db, (forever btw!),

cpu/disk - hundreds if not thousands of these files, depending on how many columns you have in total across your database, can cause a)open fd limits to be hit quicker, b)cpus to be used needlessly (due to compression, serialisation, reads etc of these files), disks to be used neddlessly more due to the unnecessary reads and d) backups to be slower. Of course several factors at play here, but mostly still stands.

compression - compressed empty files in cases take more space then non-compressed empty files, and certain compression scripts simply go over the database and compress every partition, or, certain partitions based on a compression strategy,

permissions - chk requires write permissions on hdbs - It cant be run if a) the hdb q process is started up using a different user id than that of the owner of the files in the hdb (as can the case at times to avoid system forks having the ability to delete data), b) if reval or read only mode is in place on the hdb (although bv would be blocked here too with 'noupdate),

redundant tables - I dont like the idea of retiring a table at some date and still creating empty files for it (daily/parition-ly) several years in the future (in my experience we have always kept the retired tables in the database, but never actually deleted the data),

reload - A reload is required after .Q.chk has been ran, bv doesnt require a reload. Reload time always matters!!

I am sure there are reasons for chk, such as keeping the structure strict within the file system etc , but its not for me.

Sean

5

Thank you Sean. 

Additionally is there anything that would do it in reverse order. Let’s say I delete my partitioned-splayed table files and I would like q to recognize it. Under normal conditions there is table in the list of loaded tables so \a returns test_bars. I would like to simply drop all tables not having any binding any more.  (I am aware that I may do deletion of table/s from `. and potentially load all back, but any simpler function would be of help)  

6

If it is only partitioned tables you are concerned about then after deleting the table[s] (from every directory) you can simply reload, and delete the tables from the root namespace that are no longer existent in .Q.pt. .Q.pt is modified after reload so it is the list of tables you can use to check the ‘bindings’.

q){.Q.dd[hsym `$string x;(`t;`)] set (10?10) } each 2017.01.01+til 3

:2017.01.01/t/:2017.01.02/t/`:2017.01.03/t/

q){.Q.dd[hsym `$string x;(`t2;`)] set (10?10) } each 2017.01.01+til 3

:2017.01.01/t2/:2017.01.02/t2/`:2017.01.03/t2/

q)\l .

q).Q.pt

s#t`t2

q)\a

s#t`t2

q)\rm -rf */t2

q)\l .

q)\a

s#t`t2

q).Q.pt

,`t

q)![`.;();0b;tables except .Q.pt]

`.

q)\a

,`t

q).Q.pt

,`t

The logic will slightly change if you have flat and/or splayed at root too.

HTH,

Sean