Csv file to partitioned table

Community Support
1

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 symbolsystypmomentidactionpricevolumeid_dealprice_dealownaccount!(“SCC*JIFIJFXS”;“,”)0:x}][`:c:/Q/orderlog.txt;100000000]

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

 

Thank you.

2

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

   flipsymbolsystypmomentidactionpricevolumeid_dealprice_dealownaccount!(“SCC*JIFIJFXS”;“,”)0:x;

 {t;dupsert 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 xmoment],"/db/")set x;}each t group date$t`moment;

3

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?

 

4

right, sorry.

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

5

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

 

:(

 

6

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

7

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?

8

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 ;-)

9

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

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

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

q).[:.;ttsym;p#];

q)meta select from get`:tt/

c   | t f a

----| -----

sym | s   p

time| t 

10

charset=us-ascii
X-Mailer: iPad Mail (10B329)
In-Reply-To:
Message-Id: <4E83E91F-2D6D-43BC-A503-255B4A8B437F@gmail.com>
Date: Tue, 16 Apr 2013 16:26:12 -0400
To: “personal-kdbplus@googlegroups.com”

Mime-Version: 1.0 (1.0)

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,

11

To: personal-kdbplus@googlegroups.com
X-Mailer: Apple Mail (2.1085)

select n from t where d=max d

12

charset=us-ascii
X-Mailer: iPad Mail (10B329)
In-Reply-To:
Message-Id: <27F8E761-16D9-4152-BC6A-AAE5561DBC81@gmail.com>
Date: Tue, 16 Apr 2013 16:51:56 -0400
To: “personal-kdbplus@googlegroups.com”

Mime-Version: 1.0 (1.0)

Thanks!

13

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