cancel
Showing results for 
Search instead for 
Did you mean: 

Segmented tables

skuvvv
New Contributor
Hello.
I read about segmentation and it looks good for me.
I want to segment by intraday time, for hour eg.
Approach in documentation looks like:
                `:/dbdata/9/2009.01.01/t2/ set ([] ti:09:30:00 09:31:00; s:`:/db2/sym?`ibm`t; p:101 17f)
                `:/dbdata/10/2009.01.01/t2/ set ([] ti:10:30:00 10:31:00; s:`:/db2/sym?`ibm`t; p:101.5 17.5)
                `:/dbdata/9/2009.01.02/t2/ set ([] ti:09:30:00 09:31:00; s:`:/db2/sym?`ibm`t; p:103 16.5f)
                `:/dbdata/10/2009.01.02/t2/ set ([] ti:10:30:00 10:31:00; s:`:/db2/sym?`ibm`t; p:102 17f)
                `:/db2/par.txt 0: ("/dbdata/9"; "/dbdata/10")

date       ti       s   p    -----------------------------2009.01.01 09:30:00 ibm 101  2009.01.01 09:31:00 t   17   2009.01.01 10:30:00 ibm 101.52009.01.01 10:31:00 t   17.5 2009.01.02 09:30:00 ibm 103  2009.01.02 09:31:00 t   16.5 2009.01.02 10:30:00 ibm 102  2009.01.02 10:31:00 t   17  

I tried change it this way:
                `:/dbdata/2009.01.01/9/t2/ set ([] ti:09:30:00 09:31:00; s:`:/db2/sym?`ibm`t; p:101 17f)
                `:/dbdata/2009.01.01/10/t2/ set ([] ti:10:30:00 10:31:00; s:`:/db2/sym?`ibm`t; p:101.5 17.5)
                `:/dbdata/2009.01.02/9/t2/ set ([] ti:09:30:00 09:31:00; s:`:/db2/sym?`ibm`t; p:103 16.5f)
                `:/dbdata/2009.01.02/10/t2/ set ([] ti:10:30:00 10:31:00; s:`:/db2/sym?`ibm`t; p:102 17f)
                `:/db2/par.txt 0: ("/dbdata/2009.01.01"; "/dbdata/2009.01.02")
But at result I have additional Column(Hour) and I haven't Date column when loading table:
int ti       s   p    ----------------------9   09:30:00 ibm 101  9   09:31:00 t   17   9   09:30:00 ibm 103  9   09:31:00 t   16.5 10  10:30:00 ibm 101.510  10:31:00 t   17.5 10  10:30:00 ibm 102  10  10:31:00 t   17   

1)Can it possible to store data by my scheme?
2)How can I load chunk/segment etc(eg I want to load date=2009.01.02 and segment = 9, 10)?


2 REPLIES 2

l_belshaw01
New Contributor
Hi Vadim,

So firstly, there are four types which you can partition by - year, month, date, and integer. You could write a function which casts the timestamp from your date-time columns to an integer, and rounds to the nearest hour. This would allow you to have the table partitioned on the hour in each day. Basically, something like this:

    f:{`h xcols update h:`int$(`timestamp$date+time)% 0D01 from x}

    table: ([] date:2009.01.01 2009.01.01; time: 09:30:00 09:31:00; s:`imb`t;p:101 17)
    f[table]
    h     date       time     s   p     
    ---------------------------------
    78922 2009.01.01 09:30:00 imb 101
    78922 2009.01.01 09:31:00 t   17

You could then partition your database on the field h. This is going to give a lot of partitions though.

What could be an easier solution is just to partition your table on date, and then use something like this as your select -

    select from table where date=2009.01.01, 9=`hh$time

Something maybe closer to what you had previously would be like this, partitioning on the integer hour and then on date:

    `:/db/9/2009.01.01/t/ set update seg:`hh$ti from ([] ti:09:30:00 09:31:00; s:`:/db/sym?`ibm`t; p:101 17f)
    `:/db/10/2009.01.01/t/ set update seg:`hh$ti from ([] ti:10:30:00 10:31:00; s:`:/db/sym?`ibm`t; p:101.5 17.5)
    `:/db/9/2009.01.02/t/ set update seg:`hh$ti from ([] ti:09:30:00 09:31:00; s:`:/db/sym?`ibm`t; p:103 16.5f)
    `:/db/par.txt 0: ("/db/9"; "/db/10")

Hope this helps.

Louise.

-- 
Dr Louise Belshaw
louise.belshaw@aquaq.co.uk

Thank you for solutions!
I think I will compose my approach from it.