kdb+ »

kdb lj ij uj joins and upgrading 2.6 to 3.x

December 21st, 2017 by Ryan Hamilton

A quick post to highlight something a lot of people are bumping into with upgrades. The joins in 3.x for uj/ij and lj all changed how they treat nulls from the keyed table. In particular nulls now by default overwrite existing values. In the past nulls from the joining table did not overwrite and left the original value in the column. See the difference in the 3/three row shown below:

q)t:([] a:1 2 3; b:`one`two`three; c:1.0 2.0 3.0) q)u:([a:2 3 4] b:`j``l; c:100 200 300.0)


q)t

a b     c

---------

1 one   1

2 two   2

3 three 3
q)u

a| b c

-| -----

2| j 100

3|   200

4| l 300
q)t lj u    / v3.x The null from u overwrites previous value in column b

a b   c

---------

1 one 1

2 j   100

3     200

q)t ljf u / v2.0 or ljf - The original 3 value not overwritten by null a b c ----------- 1 one 1 2 j 100 3 three 200

Other than the int/long indexing change this is one of the biggest breaking changes in migrating kdb 2.x to 3.x.

You may also enjoy our full kdb joins article.

Tags: joins, kdb+. kdb+ 1 Comment

qStudio 1.43 Released – mac save bug fixed

April 13th, 2017 by Ryan Hamilton

qStudio 1.43 Released. This:

Adds stack traces to kdb 3.5+
Fixes the mac bug where the filename wasn’t shown when trying to save a file.
Fixes a number of multi-threading UI problems

Download it now.

Tags: qstudio, qstudio release. kdb+,qStudio Add a comment

kdb 3.5 Released with Stack Trace

April 13th, 2017 by Ryan Hamilton

kdb+ 3.5 had a significant number of changes:

Debugger – At long last we can finally get stack traces when errors occur.
Concurrent Memory Allocator – Supposedly better performance when returning large results from peach
Port Reuse – Allow multiple processes to listen on same port. Assuming Linux Support
Improved Performance – of Sorting and Searching
Additional ujf function – Similar to uj from v2.x fills from left hand side

kdb Debugger

The feature that most interests us right now is the Debugging functionality. If you are not familiar with how basic errors, exceptions and stack movement is handled in kdb see our first article on kdb debugging here. In this short post we will only look at the new stack trace functionality.

Now when you run a function that causes an error at the terminal you will get the stack trace. Here’s a simple example where the function f fails:

Whatever depth the error occurs at we get the full depth stack trace, showing every function that was called to get there using .Q.bt[]:

qstudio-stack-trace-error

The good news is that this same functionality is availabe in qStudio 1.43. Give it a try: qStudio.

Note: the ability to show stack traces relies on qStudio wrapping every query you send to the server with its own code to perform some analysis and return those values. By default wrapping is on as seen in preferences. If you are accessing a kdb server ran by someone else you may have to turn wrapping off as that server may limit which queries are allowed. Unfortunately stack tracing those queries won’t be easily possible.

That’s just the basics, there are other new exposed functions and variables, such as .Q.trp – for trapping calls and accessing traces that we are going to look at in more detail in future.

kdb+,qStudio Add a comment

qUnit adds an HTML Diff Report

February 27th, 2017 by Ryan Hamilton

qUnit has added a new HTML report to allow visually easily seeing the difference between expected kdb results and actual results. To generate a report you could call:


.qunit.generateReport[.qunit.runTests[]; `:html/qunit.html]

It’s also added a


.qunit.assertKnown[actualResult; expectedFilename; msg]

call to allow comparing an actual results to a file on disk. While allow easy updating of that file and avoiding naming collisions.

Tags: qunit, testing, unit test. kdb+ Add a comment

Drawing the DeathStar bmp with kdb+

February 7th, 2017 by admin

This post is a walkthrough of my implementation in Q of the RosettaCode task ‘Death Star’.

The code is organized as general-purpose bitmap generator which can be used in other projects, and a client specific to the task of deathstar-drawing. The interface is a function which passes a map of pixel position to pixel value. The map can be a mapping function, or alternatively a 2D array of pixel values. The bitmap generator raster-scans the image, getting pixel values from either a mapping function or a mapped array.

genheader follows directly from the referenced BMP Wikipedia article.

genbitmap and genrow perform a raster scan of the image to be constructed. genbitmap steps along the vertical axis, calling genrow, which steps along the pixels of the current line, in turn calling fcn, the pixel-mapping function passed in by the client.

A sample client is included in comments, the simplest possible demonstration of shape and color (a circular mask selecting between two fill colors):

Conveniently, functions and arrays can be equivalently accessed in Q.
Here is an array-passing client which replicates the first example in the Wikipedia article on BMP format.

Element ordering can be confusing at first glance: Byte order for RGB pixels is B,G,R. Also, rows are indexed from bottom to top, and since bitmaps are in row-major order, the first and second array indices designated x and y correspond to the y and x image axes respectively.

After centering the image fcn applies several masks:

is calculates the orientation of a point on a sphere, and then a pixel value for that point, using the dot product of l, the light source direction, and s, the surface orientation. A correction of (1+value)/2 is applied, to achieve the ‘soft’ appearance of a space object in a movie. Alternatively we might have suppressed negative illumination values, to get the high-contrast appearance of an actual space object.

We might want to generate images of the death star at different rotations, however due to some simplifying assumptions we can’t rotate the weapon face to the side without glitching. We calculate z1 and z2 to select between the forward surface of the death star and the rearward surface of the weapon face. We should also calculate z3, the forward surface of the weapon face sphere, and z4, the rearward surface of the death star:

z3:170+z[x2;y;r];
z4:-z1;

Then the masks can be modified so that when z3 > z1 > z4 > z2, an additional bit of background is visible through the carved-out chunk of the deathstar.

TODO: discuss limitations of mask-and-fill; alternative approaches; display-list; …
TODO: discuss animation; …
TODO: discuss three-component architecture: orchestrator, world, bitmap generator
TODO: … conclusions

Tags: image, kdb+. kdb+ Add a comment

qStudio 1.41 Release with Custom Security

June 25th, 2016 by Ryan Hamilton

qStudio 1.41 is now available to download.

It adds the ability to use custom Security Authentications and custom JDBC drivers.
By automatically loading .jar plugins from libs folder.

After a few users reported issues around “watched expressions” we are removing the ctrl+w shortcut as it was often getting used by mistake. The last change was some internal work to improved startup/shutdown logging for debugging purposes..

kdb+,qStudio Add a comment

Standard SQL sucks and this is Why

April 26th, 2016 by Ryan Hamilton

dbfire

Recently there was a post on SQL tips by the JOOQ guys. I love their work but I think standard SQL is not the solution to many of these problems. What we need is something new or in this case old, that is built for such queries. What do I mean, well let’s look through their examples reimplemented in qsql and I’ll show you how much shorter and simpler this could be.

Everything is a table

In kdb we take this a step further and make tables standard variables, no special notation/treatment, it’s a variable like any other variable in your programming language. Instead of messing about with value()() we define a concise notation to define our variables like so:

q)person:([] a:1 2 3)
q)person
a
-
1
2
3
q)select from person
a
-
1
2
3
q)person+2
a
-
3
4
5

Data Generation with Recursive SQL

This is the example syntax they have used to define two tables and then join them:

WITH
  t1(v1, v2) AS (SELECT 1, 2),
  t2(w1, w2) AS (
    SELECT v1 * 2, v2 * 2
    FROM t1
  )
SELECT *
FROM t1, t2

What to hell! If I want variables, let’s have proper variables NOT “Common Table Expressions”.

q)a:([] v1:1 2; v2:3 4)
q)b:([] v3:5 6; v4:7 8)
q)a
v1 v2
-----
1  3
2  4
q)b
v3 v4
-----
5  7
6  8
q)a,'b
v1 v2 v3 v4
-----------
1  3  5  7
2  4  6  8

I created two tables a and b then I joined them sideways. See how simple that was.

Running Total Calculations

Oh dear SQL how badly you have chosen your examples. Running calculations are to APL/qSQL as singing is to Tom Jones, we do it everyday all day and we like it. In fact the example doesn’t even give the full code. See this SO post for how these things get implemented. e.g. Standard SQL Running Sum

select a.ICode, 
       sum(sum(isnull(a.qty, 0))) OVER (partition by a.icode order by a.icode) AS total
from t_Stock a

qSQL table Definition and Running Sum:

q)t:([] a:0 0 1 1 1 1 1 1 0 0; b:2 3 5 7 9 11 13 15 1 100)
q)update runningSumB:sums b by a from t
a b   runningSumB
-----------------
0 2   2
0 3   5
1 5   5
1 7   12
1 9   21
1 11  32
1 13  45
1 15  60
0 1   6
0 100 106

Finding the Length of a Series

This is their code:

SELECT 1 + hi - lo AS length,
  trx.*,
  coalesce(last_value (lo) IGNORE NULLS OVER (
    ORDER BY id DESC
    ROWS BETWEEN UNBOUNDED PRECEDING 
    AND CURRENT ROW), rn) AS lo,
  coalesce(first_value(hi) IGNORE NULLS OVER (
    ORDER BY id DESC
    ROWS BETWEEN CURRENT ROW 
    AND UNBOUNDED FOLLOWING), rn) AS hi
FROM trx

This is KDB:

q)update length:count i by sums 0<>deltas 0<amount from t
amount length
-------------
24     1
-32    2
-13    2
27     1
-22    2
-24    2
34     1
0      3
-13    3
-20    3
25     3
11     3
6      3
-27    2
-4     2

In 1974 Ken Iverson gave a talk on APL. He described how he reduced it down to a core set of operations that everything could be made from. Using these simple building blocks you could make some really cool things. It’s sad to think we may not have came that far.

qSQL/kdb is a database based on the concept of ordered lists, carrying over many ideas from APL that make array operations shorter and simpler. If you like what you see we provide tutorials on kdb to get started, this intro is a good place to get started.

We also have free online kdb training for students.

Tags: qsql, sql. kdb+ 3 Comments

kdb standard SQL support s)

February 24th, 2016 by admin

First, in case you haven’t heard about it kdb has a standard SQL mode, you can send queries prefixed with s) and they will be interpreted as standard SQL like so:

t:([] a:til 10; b:10?9.0; c:10#`a`b`c)

q)select * from t where a>5 and b<5
'rank
q)s)select * from t where a>5 and b<5
a b        c
------------
6 4.765327 a
8 2.066954 c


q)select from t where a>5,b<5
a b        c
------------
6 4.765327 a
8 2.066954 c

Notice how the standard “and” syntax worked when I used s) but without it, q’s right to left evaluation causes problems. It’s about now that a lot of people get very excited, they think great I can skip learning that q-sql and use my standard SQL. Sometimes the look of joy on their faces transforms to frustration once they start using it. So let’s look at what works:

Operation	Works?
Standard SQL Inserts	Yes
ORDER BY	half works
COUNT	Yes
DELETE	Yes
UPDATE	Yes
String matching	slightly works
NOT	NO
IN	Yes
GROUP BY	Yes
LIMIT / TOP	NO
Date Times	NO

Standard SQL Inserts work

q)s)insert into t (a, b, c) values (6, 9.0, 'symboll');
q)s)insert into t (a, b) values (6, 9.0);
q)t
a b         c
-------------------
0 4.438651  a
1 5.206683  b
2 0.7549972 c
3 1.763917  a
4 3.380742  b
5 5.523707  c
6 4.765327  a
7 6.224489  b
8 2.066954  c
9 6.227578  a
6 9         a
6 9         symboll
6 9

ORDER BY half works

“ORDER BY” will sort the columns in ascending order, attempting to use DESC has no effect.

q)s)SELECT * FROM t ORDER BY b DESC;
a b         c
-------------------
2 0.7549972 c
3 1.763917  a
8 2.066954  c
4 3.380742  b
0 4.438651  a
6 4.765327  a
1 5.206683  b
5 5.523707  c
7 6.224489  b
9 6.227578  a
6 9         a
6 9         symboll
6 9

q)s)SELECT * FROM t ORDER BY b ASC;
a b         c
-------------------
2 0.7549972 c
3 1.763917  a
8 2.066954  c
4 3.380742  b
0 4.438651  a
6 4.765327  a
1 5.206683  b
5 5.523707  c
7 6.224489  b
9 6.227578  a
6 9         a
6 9         symboll
6 9

COUNT works

q)s)SELECT count(*) FROM t
i
--
13

DELETE works

q)s)DELETE FROM t WHERE a>=6
q)t
a b         c
-------------
0 4.438651  a
1 5.206683  b
2 0.7549972 c
3 1.763917  a
4 3.380742  b
5 5.523707  c

UPDATE works

q)s)UPDATE t SET a=999 WHERE b>5;
q)t
a   b         c
---------------
0   4.438651  a
999 5.206683  b
2   0.7549972 c
3   1.763917  a
4   3.380742  b
999 5.523707  c

String matching slightly works

q)s)SELECT * FROM t where c='a'
a b        c
------------
0 4.438651 a
3 1.763917 a

q)s)SELECT * FROM t where c like '%a%'
a b        c
------------
0 4.438651 a
3 1.763917 a

// This is valid for SQL server etc.
q)s)SELECT * FROM t where c like '_'
a   b         c
---------------
0   4.438651  a
999 5.206683  b
2   0.7549972 c
3   1.763917  a
4   3.380742  b
999 5.523707  c

// This should not return results in standard SQL
q)s)SELECT * FROM t where c like '?'
a   b         c
---------------
0   4.438651  a
999 5.206683  b
2   0.7549972 c
3   1.763917  a
4   3.380742  b
999 5.523707  c

NOT fails

q)s)SELECT * FROM t where c NOT LIKE '%a%'
'type

Modifying our String query slightly by adding NOT throws an error. My guess is that the interpreter has got confused.

IN works

q)s)SELECT * FROM t where c in ('a','b');
a   b        c
--------------
0   4.438651 a
999 5.206683 b
3   1.763917 a
4   3.380742 b
q)s)SELECT * FROM t where a in (0,3);
a b        c
------------
0 4.438651 a
3 1.763917 a

GROUP BY works

q)s)SELECT a,min(b) FROM t GROUP BY a
a   b
-------------
0   4.438651
2   0.7549972
3   1.763917
4   3.380742
999 5.206683

LIMIT / TOP does not work

q)s)select TOP 2 * FROM t;
'from
q)s)select * FROM t LIMIT 2;
a   b         c
---------------
0   4.438651  a
999 5.206683  b
2   0.7549972 c
3   1.763917  a
4   3.380742  b
999 5.523707  c

Date Times Don’t Work Right

q)a:.z.p + 1000000000*til 10
q)s:([] p:a; t:`time$a; n:`timespan$a; d:`date$a)
q)s
p                             t            n                    d
--------------------------------------------------------------------------
2016.02.24D14:27:53.095171000 14:27:53.095 0D14:27:53.095171000 2016.02.24
2016.02.24D14:27:54.095171000 14:27:54.095 0D14:27:54.095171000 2016.02.24
2016.02.24D14:27:55.095171000 14:27:55.095 0D14:27:55.095171000 2016.02.24
2016.02.24D14:27:56.095171000 14:27:56.095 0D14:27:56.095171000 2016.02.24
2016.02.24D14:27:57.095171000 14:27:57.095 0D14:27:57.095171000 2016.02.24
2016.02.24D14:27:58.095171000 14:27:58.095 0D14:27:58.095171000 2016.02.24
2016.02.24D14:27:59.095171000 14:27:59.095 0D14:27:59.095171000 2016.02.24
2016.02.24D14:28:00.095171000 14:28:00.095 0D14:28:00.095171000 2016.02.24
2016.02.24D14:28:01.095171000 14:28:01.095 0D14:28:01.095171000 2016.02.24
2016.02.24D14:28:02.095171000 14:28:02.095 0D14:28:02.095171000 2016.02.24

q)s)SELECT *  FROM  s WHERE d > '2016-02-23'
'type
// The below does work:
// q)s)SELECT *  FROM  s WHERE d > 2016.02.23

Overall standard SQL support in kdb has got much better. However I would still recommend only using the s) syntax for plugging into an existing jdbc/odbc visualization tool and getting some immediate simple results. For any form of complex queries on strings, joins etc. support is either not there or the result may not be what you expect.

kdb+ 3 Comments

qStudio kdb IDE 1.40 Released

February 15th, 2016 by admin

qStudio 1.40 is now available to download.

The latest changes include:

No need to save changes before shutdown, unsaved changes stored till reopened.
Add sqlchart to system path.
Fix display of tables with underscore in the name.
Database documenter/report enhancements
Improved code printing
FileTreePanel much more efficient at displaying large number of files.

kdb+,qStudio Add a comment

KX closes down commercial 32 bit kdb, open alternatives?

November 5th, 2015 by John Dempster

Previously on our blog we had a lively debate about a possibly Open Sourced kdb+ , unfortunately kx now seems to be moving the opposite direction. In a recent announcement they are now restricting “32-bit kdb+ for non-commercial use only”. The timing is particularly unfortunate as:

More column oriented databases are becoming available.
One of those databases: Greenplum just announced that it is going open source

Alternative (far less enterprise proven) solutions are available:

MAN AHL have released Arctic an open source Market Data platform based on python and MongoDB
Kerf Database – A DB aimed at the same market as kdb has now partnered with Briarcliff-Hall and is making greater sales inroads

This renewed interest in kdb alternatives hasn’t so far delivered a kdb+ killer but I fear in time it will.

kdb+,timeseries 3 Comments

TimeStored Blog

Archive for the 'kdb+' Category

kdb lj ij uj joins and upgrading 2.6 to 3.x

qStudio 1.43 Released – mac save bug fixed

kdb 3.5 Released with Stack Trace

kdb Debugger

qUnit adds an HTML Diff Report

Drawing the DeathStar bmp with kdb+

qStudio 1.41 Release with Custom Security

Standard SQL sucks and this is Why

Everything is a table

Data Generation with Recursive SQL

Running Total Calculations

Finding the Length of a Series

kdb standard SQL support s)

Standard SQL Inserts work

ORDER BY half works

COUNT works

DELETE works

UPDATE works

String matching slightly works

NOT fails

IN works

GROUP BY works

LIMIT / TOP does not work

Date Times Don’t Work Right

qStudio kdb IDE 1.40 Released

KX closes down commercial 32 bit kdb, open alternatives?