Segmented tables

Community Support
1

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?ibmt; p:101 17f)

                :/dbdata/10/2009.01.01/t2/ set ([] ti:10:30:00 10:31:00; s::/db2/sym?ibmt; p:101.5 17.5)

                :/dbdata/9/2009.01.02/t2/ set ([] ti:09:30:00 09:31:00; s::/db2/sym?ibmt; p:103 16.5f)

                :/dbdata/10/2009.01.02/t2/ set ([] ti:10:30:00 10:31:00; s::/db2/sym?ibmt; 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?ibmt; p:101 17f)

                :/dbdata/2009.01.01/10/t2/ set ([] ti:10:30:00 10:31:00; s::/db2/sym?ibmt; p:101.5 17.5)

                :/dbdata/2009.01.02/9/t2/ set ([] ti:09:30:00 09:31:00; s::/db2/sym?ibmt; p:103 16.5f)

                :/dbdata/2009.01.02/10/t2/ set ([] ti:10:30:00 10:31:00; s::/db2/sym?ibmt; 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

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:imbt;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?ibmt; 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?ibmt; 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?ibmt; p:103 16.5f)     :/db/par.txt 0: (“/db/9”; “/db/10”)

Hope this helps.

Louise.

--

Dr Louise Belshawlouise.belshaw@aquaq.co.uk

3

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