Complex Column Data

Community Support
1

Can you create a table with a column that is a dictionary or a table
as its’ type? It says in QforMortals that “there is no restriction
that the column vectors must be lists of simple type”. I couldn’t
declare the column with a type (e.g. 99h$) but it did do the insert.
However, errors abound after that.

q)nos:([oid:symbol$()] acct:symbol$(); hdr:())
q)nos insert (ABC1234;234563485;fld1fld2fld3!(val;
2007.01.01;12345))
'type
q)nos insert (ABC1234;234563485;fld1fld2fld3!(val; 2007.01.01;12345)) ,0 q)nos k){$[99h=@y;sublist[x;!y]!sublist[x;. y];~0\>@x;$[.Q.qp y;.Q.ind[y];y]i +!"i"$x[1]&(#y)-i:\*x;abs[x]\<#y;x#y;y]} 'type @ +accthdr!(,234563485;,fld1fld2fld3!(val;2007.01.01;12345))
,0j
q))\
q)select oid from nos
oid

ABC1234
q)select acct from nos
acct

234563485
q)select hdr from nos
k){$[99h=@y;sublist[x;!y]!sublist[x;. y];~0>@x;$[.Q.qp y;.Q.ind[y];y]i
+!“i”$x[1]&(#y)-i:*x;abs<#y;x#y;y]}
'type
@
+(,hdr)!,,fld1fld2fld3!(`val;2007.01.01;12345)
,0j
q))

2

the errors are happening inside “show”, the function used by q 2.4 to
render data for output to the interpreter shell

i recommend falling back on 2.3-style (i.e. k-style) raw data for
things like this

i’ve defined .q.unshow:{-1@-3!x;} in my q.q

q)unshow nos
(+(,oid)!,,ABC1234)!+accthdr!(,234563485;,fld1fld2fld3!(`val;2007.01.01;12345))

btw, i’m also working on a function to help figure out where exactly
you are when you find yourself in an error shell

it’s not done yet, but it works in a lot of cases

q).q.wtf:{( sv'd,'f)where not null f:(get each d: sv’,',key)?'x} q)nos k){$[99h=@y;sublist[x;!y]!sublist[x;. y];~0\>@x;$[.Q.qp y;.Q.ind[y];y]i+!"i"$x[1]&(#y)-i:\*x;abs[x]\<#y;x#y;y]} 'type @ +accthdr!(,234563485;,fld1fld2fld3!(val;2007.01.01;12345))
,0j
q))wtf .z.s
,.q.sublist q))' k){$[99h=@y;sublist[x;!y]!sublist[x;. y];~0\>@x;$[.Q.qp y;.Q.ind[y];y]i+!"i"$x[1]&(#y)-i:\*x;abs[x]\<#y;x#y;y]} ' q))wtf .z.s ,.q.sublist
q))’
k){x[1]{$[x<#y;((x-2)#y),“..”;y]}‘$[t&70>t:@z;,s1
z;99.q.sublist[(y;n-s:(y+n:x[0]-2*.Q.qt z)<#z)]z]}
’
@
k){$[99h=@y;sublist[x;!y]!sublist[x;. y];~0>@x;$[.Q.qp
y;.Q.ind[y];y]i+!“i”$x[1]&(#y)-i:x;abs[x]<#y;x#y;y]}[(0j;58)]
(+(,oid)!,,ABC1234)!+accthdr!(,234563485;,fld1fld2fld3!(val;2007.01.01;12345))<br>q))wtf .z.s<br>,.Q.S
q))‘
k){$[(::)~x;“”;`/:S[(.“\c”)-2 1;0j]x]}
’
@
k){x[1]{$[x<#y;((x-2)#y),“..”;y]}'$[t&70>t:@z;,s1
z;99.q.sublist[(y;n-s:(y+n:x[0]-2.Q.qt z)<#z)]z]}[60 207;0j]
(+(,`oid)!,`ABC1234)!+`acct`hdr!(,`234563485;,`fld1`fld2`fld3!(`val;2007.01.01;12345))
q))wtf .z.s
,`.Q.s
q))‘
’

3

But does that mean it is in fact allowed? Because if you look at the beginning of my snippet you can see that it didn’t like my first insert:

q)nos:([oid:symbol$()] acct:symbol$(); hdr:())
q)nos insert (ABC1234;234563485;fld1fld2fld3!(val;2007.01.01;12345))

'type
q)nos insert (ABC1234;234563485;fld1fld2fld3!(`val;2007.01.01;12345))

,0

4


<11a2a48c0809100630o799244d1j1d365c6b289d50fc@mail.gmail.com>

upsert works both ways

insert is weird, afaik current advice from kx is avoid it

% q
KDB+ 2.4 2008.03.31 Copyright (C) 1993-2008 Kx Systems
m32/ 2()core 2048MB

q)nos:([oid:symbol$()] acct:symbol$(); hdr:())
q)unshow nos upsert
(ABC1234;234563485;fld1fld2fld3!(val;2007.01.01;12345))
(+(,oid)!,,ABC1234)!+accthdr!(,234563485;+fld1fld2fld3!(,val;,2007.01.01;,12345))<br>q)nos upsert (ABC1234;234563485;fld1fld2fld3!(val;2007.01.01;12345))
nos<br>q)unshow nos<br>(+(,oid)!,ABC1234)!+accthdr!(,234563485;+fld1fld2fld3!(,val;,2007.01.01;,12345))

5

Thanks for the help. When you say “insert is weird, afaik current advice from kx is avoid it”, were you saying to avoid “insert” or dictionary columns?

On Wed, Sep 10, 2008 at 11:37 AM, Aaron Davies <aaron.davies@gmail.com> wrote:

upsert works both ways

insert is weird, afaik current advice from kx is avoid it

% q
KDB+ 2.4 2008.03.31 Copyright (C) 1993-2008 Kx Systems
m32/ 2()core 2048MB

q)nos:([oid:symbol$()] acct:symbol$(); hdr:())

q)unshow nos upsert

(ABC1234;234563485;fld1fld2fld3!(val;2007.01.01;12345))

(+(,oid)!,,ABC1234)!+accthdr!(,234563485;+fld1fld2fld3!(,`val;,2007.01.01;,12345))

q)nos upsert (ABC1234;234563485;fld1fld2fld3!(val;2007.01.01;12345)) nos

q)unshow nos
(+(,oid)!,,ABC1234)!+accthdr!(,234563485;+fld1fld2fld3!(,`val;,2007.01.01;,12345))

6


<11a2a48c0809100630o799244d1j1d365c6b289d50fc@mail.gmail.com>

<11a2a48c0809110633y2c4e8d99vf5891b5f140028e1@mail.gmail.com>

i was saying to avoid insert

of course, complex columns are also not the best idea in the world if
there’s any other way to do things–there’s not a lot of q that
won’t break in weird and hard-to-recognize ways when you use them

lots of things are possible w/o being good ideas

e.g. you can have a table with a column whose name is a symbol of an
int, but it makes selecting awfully hard

KDB+ 2.4 2008.07.22 Copyright (C) 1993-2008 Kx Systems
l64/ 8(8)core 32135MB

q)t:flip(enlist`1)!enlist 1 2
q)t
1
-
1
2
q)select 1 from t
'type

do you have a real need to store these data types in a table, or are
you exploring q?

7

What I’ve settled on are columns containing a list (no dictionary). This seems to work ok in most circumstances (except insert of course). As a test, I’m trying to store order messages which can contain fields of fields. Rather than flatten things out and expand the table, I wanted to limit the number of columns and mimic the on the wire format if possible. These are infrequently used values and if new subfields are added it wouldn’t really change the structure of consuming functions. Thoughts?

8


<11a2a48c0809100630o799244d1j1d365c6b289d50fc@mail.gmail.com>

<11a2a48c0809110633y2c4e8d99vf5891b5f140028e1@mail.gmail.com>

<11a2a48c0809110726p74b8ea5eh7d28ee50c73ba11c@mail.gmail.com>

it’s doable if there’s good reason

have a look at the “nest” case in http://kx.com/q/tick/c.q

it stores incoming trades as a table keyed on sym w/time, price, size,
etc. stored as list cols

arbitrary-keyed data is hard to deal with

you can add it with uj, but your performance goes to hell

what’s the read/write ratio likely to be? maybe you can just store a
raw list and define a table as a view on that, recomputed only on read

or have i completely misunderstood what you’re doing?

adavies@lx2-kdb-sng:/kdbdata> q
KDB+ 2.4 2008.07.22 Copyright (C) 1993-2008 Kx Systems
l64/ 8(8)core 32135MB

q)l:((ab!1 2);ac!(1;“foo”))
q)l
ab!1 2
ac!(1;“foo”)
q)t::(uj/)enlist each l
q)t
a b c
---------
1 2 “”
1 “foo”
q)l,:ad!(5;`e)
q)t
a b c d
-----------
1 2 “”
1 “foo”
5 “” e

9

I’m sorry, i wasn’t clear about this. These messages have 15-30 fields of which only 2 or 3 are nested lists, e.g. headers, options, flags etc. So I key on order number and have account, time, symbol, price, size amongst others. So if I were to expand the nested fields I would get a bunch of extra columns that aren’t necessarily interesting most of the time. Or I could put them in a separate lookup table if I were doing this in a traditional rdbms. But since this is kdb+ I thought I could have a more graceful solution.