sqlDashboards are included as a bundle with qStudio, part of that package is a command line utility called sqlChart that allows generating customized sql charts from the command line.
Checkout the video to see how you can create a chart based on data from a kdb+ database in 2 minutes:
The sqlChart page has all the documentation you need, Download the qstudio.zip to try it now.
I always like to investigate new technology and this week I found a nice automatic technique for improved cache use that I had previously seen some people manually write.
Consider a database query with three steps (three SQL SELECTs), some databases may pass results of each step to temporary tables in main memory. When the first step is finished, these intermediate results are passed back into CPU cache to be transformed by the second step, then back into a new temporary table in main memory, and so on.
To eliminate this back-and-forth, vector-based statistical functions can be pipelined, with the output of one function becoming input for the next, whose output feeds a third function, etc. Intermediate results stay in the pipeline inside CPU cache, with only the full result being materialized at the end.
This technology is part of ExtremeDB, they have a video that explains it well:
This is what the actual code would look like to calculate the 5-day and 21-day moving averages for a stock and detect the points where the faster moving average (5-day) crosses over or under the slower moving average (21-day):
This approach eliminates the need to create, populate and query temporary tables outside CPU cache in main memory. One “tile” of input data is processed by each invocation of ‘mco_seq_window_agg_avg_double()’, each time producing one tile of output that is passed to ‘mco_seq_sub_double()’ which, in turn, produces one tile of output that is passed as input to mco_seq_cross_double(), which produces one tile of output that is passed to mco_seq_map_double(). When the last function, mco_seq_map_double() has exhausted its input, the whole process repeats from the top to produce new tiles for additional processing.
A very cool idea!
And yes, ExtremeDB are the same guys that posted the top Stac M3 benchmark for a while (in 2012/13 I think).
Kdb does not have built-in functions for bitwise and,or,xor operations, we are going to create a C DLL extension that provides bitwise operators.
To download the source and definitions for and/or/xor bitwise operations click here
If your new to extending Kdb+ see this tutorial on writing a DLL for Kdb+
I also thought this would be a good opportunity to look at using q-code to generate C code, to generate Kdb Dll’s. When converting an existing library to work with Kdb or writing similar functions for different types, there’s a lot of repetitive coding. Either you can use macros or you can use one language to generate another, to save a lot of typing. Let’s look at bitwise operations as an example of what I mean:
If I wanted to write a bitwise and function band that takes two lists and performs a corresponding operation, we could write the C functions like so:
Notice the similarity between the bandJJ and bandII functions. Plus if we want to handle the case where the second argument is an atom we need an entire other set of C functions similar to bandJ to handle that. This is soon beginning to spiral beyond an easy copy paste job. Instead I used the following 20 lines of q code to generate the 130 lines of C code:
This generates my C code, a .def file exporting the functions that I want to provide and creates a .q file that loads those functions into kdb. Which means any time I add a function or want to support a new type, everything is done for me. let’s look at our functions in action:
Yes the q code is messy (it could be improved) but the concept of dynamically generating your imports can be a real time saver.
This code is not intended for reliability or performance, use at your own risk! If however bitwise operations are a topic that interest you I recommend this article on: Writing a fast vectorised OR function for Kdb.
While doing the project euler programming challenges it annoyed me how verbose the java answers would have to be compared to kdb. Then I got to wondering if I could create functions like til,mod,where,asc etc. in java and use them to create really short answers. Once I had the basic functions working, I wondered if I could get a working q) prompt…
I ended up with a prompt that let me run the following valid q code within the java runtime:
where[or[ =[0; mod[til[1000];3]]; =[0; mod[til[1000];5]] ]]
Download the jkdb functional kdb code
My Jkdb class has a number of statically defined functions that accept arrays of ints/doubles and return arrays/atoms of int/double. An example is asc:
In the background each line you type has the square brackets and semi-colons converted to () and comma which are now valid java function calls. The amended string gets added to a .java file, compiled, ran and the output shown each time you press enter. Since I statically imported my class that has the til(x),max(x) etc functions, the code is valid java and works. This is why we can only use functional[x;y] form calls and not the infix notation x func y as I didn’t want to write a parser.
Almost entirely useless 🙂 I use the java class to occasionally spit out some sample data for tests I write. It was however insightful to get an idea of how kdb works under the covers. If you found this interesting you might also like kona an open source implementation of kdb.
I’d be interested in hearing:
| til where |
| equal= |
| index@ |
| choose(x,y) ? – only supports x and y as int’s to choose random numbers |
| or(x,y) and(x,y) |
| mul(x,y) add(x,y) |
| mod(x,y) |
| floor ceiling abs |
| cos sin tan |
| acos asin atan |
| sqrt |
| asc desc max min reverse |
| sum sums prd prds |
| set(String key, Object value) |
| get(String key) |
http://www.kx.com/press-releases-130610.php
Palo Alto (10 Jun 2013) – Kx Systems, the leader in high-performance database and time-series analysis, has announced today the release of kdb+ v3.1 as well as its latest benchmark results for the STAC (Securities Technology Analysis Center) summit in Chicago. The results of the STAC M3™ vendor-independent market data benchmark suite demonstrate very significant performance improvements in v3.1, with results up to 8x faster than any previous STAC M3 benchmarks.
STAC ReportThe tests highlighted Kx’s unparalleled performance in such real-world calculations as generating the complete NBBO (National Best Bid and Offer) for one day, on thousands of symbols. Compared to previous best results, v3.1 runs over 4x faster, taking about half a minute to read, calculate, and write the entire table. In other benchmarks, calculations such as VWAPs (Volume Weighted Average Prices) on tick data were more than 8x faster….
It’s great to see kx continue to make speed improvements.
Andrey Zholos announced a new version of q Math Library:
qml 0.4 is out:
http://althenia.net/qmlNow compatible with kdb+ 3.0 and whatever implementation of LAPACK your
distro provides.Thanks to everyone who prompted me to continue working on this, and
especially to Jim Schmitz for his invaluable ideas, Kim Kuen Tang
for providing an interim version, and Kx for making a binary-
incompatible change to the interfaceIf there’s a permissively-licensed numerical library you’d like to see
included in the next version, let Andrey know. Or just submit a patch.
The excellent Q For Mortals: A Tutorial In Q Programming by Jeffry Borror will soon be updated to version 3.
Jeff mentioned it at the recent NY user meeting.
You can read q for mortals online for free at: http://code.kx.com/wiki/JB:QforMortals2/contents
version 2 – added a new chapter on kdb database disk storage.
He said to get in touch if there’s anything you feel you would really like added.
UPDATE December 2015
q for mortals 3 is now out.
Q for Mortals Version 3 is a thorough presentation of the q programming language and an introduction to the kdb+ database. It is a complete rewrite of the original Q for Mortals that is current with q3.3. The presentation is derived from classes taught by the author at international financial institutions over the last decade. It is a series of tutorials based on q snippets intended to be entered interactively into the q console by the reader. The text takes its subject seriously but not itself. Technical explanations are augmented by mathematical observations, references to general programming concepts and other programming languages, and bad jokes. Coding style recommendations and advice to avoid gotchas appear liberally throughout. Examples are as simple as they can be but no simpler.
Available at all good bookstores.
http://www.amazon.com/For-Mortals-Version-Introduction-Programming/dp/0692573674
qTips is also good: http://www.amazon.co.uk/Tips-Fast-Scalable-Maintainable-Kdb/dp/9881389909
Added four new tutorials for those starting to learn kdb:
