KX Community

Find answers, ask questions, and connect with our KX Community around the world.
KX Community Guidelines

Home Forums kdb+ merge/copy partitioned tables efficiency

  • merge/copy partitioned tables efficiency

    Posted by jackyyuk on November 21, 2023 at 12:00 am

    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?

    jackyyuk replied 8 months, 2 weeks ago 2 Members · 2 Replies
  • 2 Replies
  • megan_mcp

    Administrator
    November 21, 2023 at 12:00 am

    Hi ,

    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

    Member
    November 27, 2023 at 12:00 am

    Hello Megan,

    It’s working. Thanks so much for your help

Log in to reply.