cancel
Showing results for 
Search instead for 
Did you mean: 

merge/copy partitioned tables efficiency

jackyyuk
New Contributor II

Hello all,

Currently, I would need to merge/copy partitioned tables from several individual folders to a common large folder:

kdbfolder_1 - <date> - trade - col_1,...,col_n

...
kdbfolder_n - <date> - trade - col_1,...,col_n

--> 

kdbcommon - <date> - trade - col_1,...,col_n

The columns of all individual tables are the same, and I did the merging by q codes, in which, for each individual folder, read the individual table data, and then "upsert" the data to the final common folder:

q) t:select from `:kdbfolder_n/<date>/trade;

q) `:kdbcommon/<date>/trade/ upsert t;

 

This works properly, but the speed seems to be slow when running "upsert", especially, when the table size in the common folder grows very large.

I would like to ask if there is a more efficient way to perform the merging/copying of the data?

1 ACCEPTED SOLUTION

megan_mcp
Community Manager Community Manager
Community Manager

Hi @jackyyuk,

You appear to be upserting at a table level, you could try doing the upserts at a column level and peach it. something like:

{colData:get .Q.dd[`:kdbfolder_n/<date>/trade;x];upsert[.Q.dd[`:kdbcommon/<date>/trade/;x];colData]}peach except[cols trade;`date]

For this you need to have threads enabled in the command line:

-s N

Let me know if this works,

Megan

View solution in original post

2 REPLIES 2

megan_mcp
Community Manager Community Manager
Community Manager

Hi @jackyyuk,

You appear to be upserting at a table level, you could try doing the upserts at a column level and peach it. something like:

{colData:get .Q.dd[`:kdbfolder_n/<date>/trade;x];upsert[.Q.dd[`:kdbcommon/<date>/trade/;x];colData]}peach except[cols trade;`date]

For this you need to have threads enabled in the command line:

-s N

Let me know if this works,

Megan

jackyyuk
New Contributor II

Hello Megan,

It's working. Thanks so much for your help