TimeStored Blog

[John Dempster]

Self-Contained Example:

Run this on SQLServer

CREATE OR REPLACE TABLE customer (
    ticker VARCHAR,
    cid INTEGER PRIMARY KEY,
    clientName VARCHAR,
    clientShortName VARCHAR,
    country VARCHAR,
    industry VARCHAR,
    annualRevenue DECIMAL(15, 2)
);

-- Insert 10 rows into the customer table
INSERT INTO customer (ticker, cid, clientName, clientShortName, country, industry, annualRevenue) VALUES
('JPM', 1, 'JPMorgan Chase & Co.', 'JPMC', 'USA', 'Banking', 480000000000.00),
('BAC', 2, 'Bank of America Corporation', 'BofA', 'USA', 'Banking', 390000000000.00),
('C', 3, 'Citigroup Inc.', 'Citi', 'USA', 'Banking', 250000000000.00),
('WFC', 4, 'Wells Fargo & Company', 'Wells Fargo', 'USA', 'Banking', 200000000000.00),
('GS', 5, 'Goldman Sachs Group, Inc.', 'Goldman', 'USA', 'Investment Banking', 100000000000.00),
('MS', 6, 'Morgan Stanley', 'Morgan Stanley', 'USA', 'Investment Banking', 110000000000.00),
('HSBC', 7, 'HSBC Holdings plc', 'HSBC', 'UK', 'Banking', 250000000000.00),
('BCS', 8, 'Barclays plc', 'Barclays', 'UK', 'Banking', 150000000000.00),
('DB', 9, 'Deutsche Bank AG', 'Deutsche', 'Germany', 'Banking', 120000000000.00),
('UBS', 10, 'UBS Group AG', 'UBS', 'Switzerland', 'Investment Banking', 90000000000.00);

Create a KDB+ table

trades:([]
   time:7?.z.t;
   pair:<code>AUDUSD<code>USDHKD</code>NZDUSD<code>USDCNY</code>USDINR<code>USDRUB</code>USDCHF;
   price:5+7?50.0;
   size:100+7?100;
   cid:4 2 8 4 5 2 1;
   tradeCounterpary:<code>JPMoragn</code>BOFA<code>Wells</code>GS<code>Barclays</code>BOFA

Wells)

Join via BabelDB

select * from query_db('localhost:5000',select from trades) T
                      LEFT JOIN query_db('SQLServer', select * from customer)   C
                      ON C.cid=T.cid

The output would look like this:

time	pair	price	size	cid	tradeCounterpary	ticker	cid	clientName	clientShortName	country	industry	annualRevenue
07:48:42	USDCHF	13.480580186005682	154	1	Wells	JPM	1	JPMorgan Chase & Co.	JPMC	USA	Banking	480000000000.000
00:07	USDRUB	54.90540718426928	165	2	BOFA	BAC	2	Bank of America Corporation	BofA	USA	Banking	390000000000.000
03:25:13	USDCNY	14.399725147522986	134	4	GS	WFC	4	Wells Fargo & Company	Wells Fargo	USA	Banking	200000000000.000
03:33:41	USDINR	15.484824748709798	133	5	Barclays	GS	5	“Goldman Sachs Group, Inc.”	Goldman	USA	Investment Banking	100000000000.000
08:28:05	NZDUSD	46.74112024484202	162	8	Wells	BCS	8	Barclays plc	Barclays	UK	Banking	150000000000.000
08:12	USDHKD	19.0155200031586	160	2	BOFA	BAC	2	Bank of America Corporation	BofA	USA	Banking	390000000000.000
09:06:57	AUDUSD	29.631798604968935	110	4	JPMoragn	WFC	4	Wells Fargo & Company	Wells Fargo	USA	Banking	200000000000.000

This example works in both BabelDB in Pulse and qduckdb in qStudio:

[John Dempster]

Unfortunately we can only currently offer workarounds and compromises:

a) Convert the column to a string, sorting should then work alphabetically. This does mean any chart will show the time taegorically rather than proportional distance, generating the other missing bars would correct this a little.

b) Aggregate the data yourself (though this will squash events together.

Javascript and most charting libraries do not support nanoseconds out of the box. We would need to write a full end-to-end data format -> data type -> charting system to show nanoseconds. It is something some of the team have done before elsewhere but it’s a very large effort which we are not currently considering.

May I ask your exact use-case? Were you wanting to see the data in a table or a particular chart?
Does aggregation to e.g. minute bars solve the problem?

[John Dempster]

Hi,

The colours are based on a hash of the text using the code below. We haven’t thought of a reasonable way to expose these tags for customization as they don’t use a palette. Your best option may be to generate a similar span tag yourself but setting your own colours programmatically.

<span class='bp4-tag .modifier' style='background-color:" + stringToColour(value) + "'>" + value + "</span>"

 private static stringToColour(str: string) {
	 let hash = 0;
	 let i = 0;
	 for (i = 0; i < str.length; i++) {
		 hash = str.charCodeAt(i) + ((hash << 5) - hash);
	 }
	 let colour = '#';
	 for (i = 0; i < 3; i++) {
		 const value = (hash >> (i * 8)) & 0xFF;
		 colour += ('00' + value.toString(16)).substr(-2);
	 }
	 return colour + "AA";
 }
 

[John Dempster]

https://www.timestored.com/pulse/help/faq/timeseries-zoom

Left click and drag to select area = Zoom in
Double click = Zoom out

If chart has a zoom bar shown.
Mouse-wheel Up = Zoom in
Mouse-wheel Down = Zoom out

[John Dempster]

Set one of the columns to the right hand side axis and multiple axis will automatically be created.
A new expander will appear allowing customization of the right and left axis:

See https://www.timestored.com/pulse/help/chart-customization#multiAxis

[John Dempster]

For a full list of available date time types see here:
http://www.timestored.com/b/forums/topic/kdb-date-time-types/

[John Dempster]

The date time types available in kdb are:

char	size	num	literal	null	name	sql	java	.net
p	8	12	dateDtimespan	0Np	timestamp		Timestamp	DateTime (r/w)
m	4	13	2000.01m	0Nm	month
d	4	14	2000.01.01	0Nd	date	date	Date
z	8	15	dateTtime	0Nz	datetime	timestamp	Timestamp	DateTime*(read only)
n	8	16	00:00:00.000000000	0Nn	timespan		Timespan	TimeSpan
u	4	17	00:00	0Nu	minute
v	4	18	00:00:00	0Nv	second
t	4	19	00:00:00.000	0Nt	time	time	Time	TimeSpan

Our cheat sheet includes the full data types table:
http://www.timestored.com/kdb-guides/kdb-reference-card

[John Dempster]

In qStudio right click on the result panel and choose an export option.

[John Dempster]

From official customer list:

Goldman Sachs
Morgan Stanley
Merrill Lynch
J.P. Morgan
Deutsche Bank
Oppenheimer Capital Markets
UniCredit Bank AG
DekaBank
ConvergEx
Commerzbank AG
RBC Capital Markets
Connor, Clark & Lunn Financial Group
GSA Capital
Total Gas & Power UK

Other ones include:
Numerous hedge funds
Nomura
MUSI
UBS
Barclays Bank
Citi Bank

[John Dempster]

Many people use cron.tab or autosys for scheduled tasks. From a shell script they can then run q scripts and return exit codes to log success.

[John Dempster]

Or you can mangle strings together and use eval:

q)f:{value “select “,(“,” sv string x),” from t”}
q)t:([] q:til 9; w:til 9; e:9?9)
q)t
q w e
—–
0 0 4
1 1 2
2 2 7
3 3 0
4 4 1
5 5 2
6 6 1
7 7 8
8 8 8
q)f:{value “select “,(“,” sv string x),” from t”}
q)f[`q`e]
q e
—
0 4
1 2
2 7
3 0
4 1
5 2
6 1
7 8
8 8
q)

• This reply was modified 10 years, 9 months ago by John Dempster.

[John Dempster]

Someone has actually written up a guide to importing a regular expression library:
http://code.kx.com/wiki/Cookbook/regex

kdb+ has some builtin regex features, for use with like and ssr.

For those who need something more flexible, it’s possible to leverage regex libs such as re2, described below.

The home for re2 can be found at [1] The code below was compiled for kdb+v3.1 with this release [2] The k.h file can be downloaded from [3] For 64bit linux, this can be compiled as

g++ -m64 -O2 re2.cc -o re2.so -I . re2/obj/libre2.a -DKXVER=3 -shared -static

and the resulting re2.so should be copied into $QHOME/l64 subdirectory.

It can then be loaded and called in kdb+ via

q)f:`re2 2:(`FullMatch;2) / bind FullMatch to f
q)f[“hello world”;”hello ..rld”]

#include
#include
#include //malloc
#include
#include”k.h”

using namespace re2;

extern “C” {
Z S makeErrStr(S s1,S s2){Z __thread char b[256];snprintf(b,256,”%s – %s”,s1,s2);R b;}
Z __inline S c2s(S s,J n){S r=(S)malloc(n+1);R r?memcpy(r,s,n),r[n]=0,r:(S)krr((S)”wsfull (re2)”);}
K FullMatch(K x,K y){
S s,sy;K r;
P(x->t&&x->t!=KC&&x->t!=KS&&x->t!=-KS||y->t!=KC,krr((S)”type”))
U(sy=c2s((S)kC(y),y->n))
RE2 pattern(sy,RE2::Quiet);
free(sy);
P(!pattern.ok(),krr(makeErrStr((S)”bad regex”,(S)pattern.error().c_str())))
if(!x->t||x->t==KS){
J i=0;
K r=ktn(KB,x->n);
for(;in;i++){
K z=0;
P(!x->t&&(z=kK(x)[i])->t!=KC,(r0(r),krr((S)”type”)))
s=z?c2s((S)kC(z),z->n):kS(x)[i];P(!s,(r0(r),(K)0))
kG(r)[i]=RE2::FullMatch(s,pattern);
if(z)free(s);
}
R r;
}
s=x->t==-KS?x->s:c2s((S)kC(x),x->n);
r=kb(RE2::FullMatch(s,pattern));
if(s!=x->s)free(s);
R r;
}
}

[John Dempster]

The uppercase letters return local date time e.g. .z.Z

While the lowercase letters return GMT time

The offset is based on the OS settings.

[John Dempster]

kdb+ does not have a scheduler, it provides a timer which can be used to schedule simple regular tasks.

The \t timer is specified in milliseconds, saying how long the delay between firings are.
The event handler .z.ts can then be defined to perform an action every X milliseconds.

e.g.

\t can be used to either find the current timer setting (0 is off) or set the between
`
q)\t
0i
q) / Set our timer handler to show the current time
q).z.ts:{show .z.t}
q)\t 11000
q)15:40:58.353
15:41:09.353
15:41:20.352

q)\t 5000
q)15:41:23.724
15:41:28.723
15:41:33.723`

Turn it off, then set timer to increment a variable, restart timer to trigger every 100 milliseconds.
`
q)\t 0
q)a:1
q).z.ts:{a::a+1}
q)\t 100
q)a
10
q)a
17
q)a
25
q)a
32`

[John Dempster]

Most programming languages follow the substitution principle that assigning a value to a variable, means anywhere that variable is used the user can consider it as if the value was directly entered in place. Breaking this common rule to allow simplification of the parser at the expense of user friendliness is not a choice many other languages have made.

 

Because this is such uncommon and non-intuitive behavior for what is a common piece of code, I would call it a bug.

[Author]

Posts