Csv file to partitioned table

[Ivan]

Hello all,

 

here are my questions again

 

1. Is there a way to create a partitioned table from csv file as easy as splayed table? Like in following:

 

.Q.fsn[{`:d:/Q/tst/db/ upsert .Q.en[`:d:/Q/tst/] update "Z"$((8#'moment),'"T",/:(-9#'moment)) from flip `symbol`sys`typ`moment`id`action`price`volume`id_deal`price_deal`own`account!("SCC*JIFIJFXS";",")0:x}][`:c:/Q/orderlog.txt;100000000]

2. How can I modify string above to get only specific dates (or any other condition) into my splayed table?

 

Thank you.

[Charles Skelton]

For #1, just create/append to your tables under d:/Q/tst/yyyy.mm.dd/db

.Q.fsn[{

 t:.Q.en[`:d:/Q/tst/] update "Z"$((8#'moment),'"T",/:(-9#'moment)) from

   flip`symbol`sys`typ`moment`id`action`price`volume`id_deal`price_deal`own`account!("SCC*JIFIJFXS";",")0:x;

 {[t;d](`$":d:/Q/tst/",string[d],"/db/")upsert select from t where d=`date$moment;}[t;]each exec distinct `date$moment from t; 

 }][`:c:/Q/orderlog.txt;100000000]

or you could do

 {(`$":d:/Q/tst/",string[`date$first x`moment],"/db/")set x;}each t group `date$t`moment;

--
You received this message because you are subscribed to the Google Groups "Kdb+ Personal Developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to personal-kdbpl...@googlegroups.com.
To post to this group, send email to personal...@googlegroups.com.
Visit this group at http://groups.google.com/group/personal-kdbplus?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Ivan]

Thank you,

 

but kfm says "

Since a partition directory name factors out the common value for all records in its slice, do not include the partition column when you splay a partition slice. Instead, kdb+ infers the name, value and type from the partition directory name (‼) and creates a virtual column from this information. The name of the virtual column is set by q and can not be changed."

 

How to not include this partition column in my case?

[Charles Skelton]

right, sorry.

{(`$":d:/Q/tst/",string[`date$first t`moment],"/db/")set update `time$moment from x;}each t group `date$t`moment

[Ivan]

(attempt to use variable t without defining/assigning first (or user-defined signal))

 

:(

[Charles Skelton]

{(`$":d:/Q/tst/",string[`date$first x`moment],"/db/")set update `time$moment from x;}each t group `date$t`moment

[Ivan]

Now according to kfm I should be able to select data like this:

 

select from `:d:/Q/tst/ where date in 2013.03.15 2013.03.15

 

but I get 'type error. Am I getting it wrong?

[Charles Skelton]

you can either start kdb+ with that dir as a cmd line option

$q d:/Q/tst

or inside kdb+ as

q)\l d:/Q/tst

make sure you have only kdb+ data files & scripts in that dir that you want loaded.

then select as

select from db where date in 2013.03.15 2013.03.15

eventually you might want to change the name db to something that represents your table name ;-)

[Charles Skelton]

n.b. you should organize your data so that on disk it is sorted by `sym`time and has `p# on the sym column.

q)`:tt/ set .Q.en[`:.;([]sym:10#`a`b`c;time:10:00:00.000+til 10)];

q)`sym`time xasc `:tt/; / there are faster ways to do this if you're not on an ssd

q).[`:.;`tt`sym;`p#];

q)meta select from get`:tt/

c   | t f a

----| -----

sym | s   p

time| t 

[Ray]

If I have a table t:
n d
1 4.04
2 8.54
3 2.11

How do I retrieve n as I would in SQL:
Select n from t where d = (select max(d) from t)

n
2

Thanks,

[Manish Patel]

select n from t where d=max d

[Ray]

Thanks!

[Ramon Torkelson]

This is all very helpful. Now suppose I have two tables to cross which will not fit in memory when joined. How can I walk or chunk the join into a partitioned table. The resultant table will have two ints as a key and a double. I want to partition on i0 and have i1 rows per partition.

Thanks,

Ray