How to join kdb+ and SQL Server data using babeldb?

[City Slicker]

I have a kdb+ server and an SQL server.
In kdb+ I have all the orders the company has placed.
I want to join the customers sector and full name from the customer table in SQL server.
They should match on customerid=cid

How to join kdb+ and SQL Server data using babeldb?

[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:

[Author]

Posts