Kdb+ is a powerful database that can be used for streaming, real-time and historical data. Q is the SQL-like general purpose programming language built on top of kdb+ that offers high-performance, in-database analytic capabilities.
Get started to download and install q
At the shell prompt, type
q to start a q console session, where the prompt
q) will appear.
$ q q)
Create a table¶
To begin learning q, we will create a simple table. To do this please type or copy the below code into your q session. Make sure you remove the leading q) from these code snippets.
q)n:1000000; q)item:`apple`banana`orange`pear; q)city:`beijing`chicago`london`paris; q)tab:(time:asc n?0D0;n?item;amount:n?100;n?city);
tabwhich contains a million rows and 4 columns of random time-series sales data. For now, understanding these lines of code is not important.
The first query we run selects all rows from the table where the item sold is a banana.
q)select from tab where item=`banana time item amount city ------------------------------------------ 0D00:00:00.466201454 banana 31 london 0D00:00:00.712388008 banana 86 london 0D00:00:00.952962040 banana 20 london 0D00:00:01.036425679 banana 49 chicago 0D00:00:01.254006475 banana 94 beijing ..
The next query will calculate the sum of the amounts sold of all items by each city.
q)select sum amount by city from tab city | amount -------| -------- beijing| 12398569 chicago| 12317015 london | 12375412 paris | 12421447
sumwithin the q language. Please note that this returns a keyed table where the key column is
city. This key column is automatically returned in alphabetical order.
Time-series aggregate query¶
The following query shows the sum of the amount of each item sold by hour during the day.
q)select sum amount by time.hh,item from tab hh item | amount ---------| ------ 0 apple | 522704 0 banana| 506947 0 orange| 503054 0 pear | 515212 1 apple | 513723 ..
hhfor the hour and
item. The results are ordered by the keyed columns. This query extracts the hour portion from the nanosecond-precision time column by adding a
.hhto the column name.
Congratulations, you have now successfully created and queried your first q table!