cancel
Showing results for 
Search instead for 
Did you mean: 

How to add a column to the Partition table?

hzadonis369
New Contributor III

Hi, Masters:

  Because a new field should be added to the Partition table. But it seems there's problem that I met.

  First of all, the trade table was paritioned by:

q)`:E:/testfolder/pdb/2023.06.01/trade/ set .Q.en[`:E:/testfolder/pdb;] ([] ti:09:30:00 09:31:00; s:`ibm`msft; p:101 33f);
q)`:E:/testfolder/pdb/2023.06.02/trade/ set .Q.en[`:E:/testfolder/pdb;] ([] ti:09:30:00 09:31:00; s:`ibm`msft; p:101.5 33.5);
q)\l E:/testfolder/pdb

  It works while I select data from the trade table. And secondly, I added a filed(rk) to the trade table:

q)`:E:/testfolder/pdb/2023.06.05/trade/ set .Q.en[`:E:/testfolder/pdb;] ([] rk:1 2; ti:09:30:00 09:31:00; s:``ibm`msft; p:108.6 26.2);
q)`:E:/testfolder/pdb/2023.06.06/trade/ set .Q.en[`:E:/testfolder/pdb;] ([] rk:1 2; ti:09:30:00 09:31:00; s:`ibm`msft; p:110.1 25.6);

  The new field(rk) can be input, but once I select from trade table with date specified, there're different output:

1. q)select from trade where date within (2023.06.05, 2023.06.06)

   It works fine.

2. q)select from trade where date = 2023.06.01

    There's error message:
    './2023.06.01/trade/rk. OS reports: can't find directory
    [0] select from trade where date = 2023.06.01

 

  Do you konw the reason and how to fix it? Thanks!

  • Hi
1 ACCEPTED SOLUTION

cillianreilly
New Contributor III

You haven't added the rk column to the 2023.06.01 partition.

 

q){x!`rk in/:get each` sv/:(hsym`$string x),\:`trade`.d}date
2023.06.01| 0
2023.06.02| 0
2023.06.05| 1
2023.06.06| 1

 

You can reference dbmaint.q - it contains utility functions for working on partitioned databases. For this case, the fixtable function would help you here - adding the rk column into the partitions that don't currently contain it. 

In general, better practice is to add the empty column to all partitions first (addcol), and then write down the partitions containing data. This keeps the HDB in a good state throughout the operation.

View solution in original post

4 REPLIES 4

hzadonis369
New Contributor III

Does it mean I must insert historical data for rk field? I accept the historical data can be empty.

cillianreilly
New Contributor III

You haven't added the rk column to the 2023.06.01 partition.

 

q){x!`rk in/:get each` sv/:(hsym`$string x),\:`trade`.d}date
2023.06.01| 0
2023.06.02| 0
2023.06.05| 1
2023.06.06| 1

 

You can reference dbmaint.q - it contains utility functions for working on partitioned databases. For this case, the fixtable function would help you here - adding the rk column into the partitions that don't currently contain it. 

In general, better practice is to add the empty column to all partitions first (addcol), and then write down the partitions containing data. This keeps the HDB in a good state throughout the operation.

Hi, cillianreilly, Thank you!

I need to understand the code you mentioned. 

 

I have another method to resolve my problem, maybe looks stupid.

For the table that doesn't contain the rk field, because rk is the rank sorted by p, I generated it and then re-partition the table. So, a function:

repartbl:{
`trd set select rk:i+1, ti, s, p from(`p xdesc select from trade where date=x);
.Q.dpft[mydbdir; x; `rk; `trd];
}

and invoke it by "repartbl each date". the output looks fine and I can use the rk for historical data.

Thanks so much! The dbmaint.q is more useful and easier than my funciton.