In Lesson 3 we've added historical database and triggered end of day processing which wrote content of
in-memory database (rdb
- tables trade
and quote
) to hdb
. In this lesson we will add a new component - accessPoint
.
This new component will load q file with custom queries enabling us to retrieve and process data from rdb
and hdb
.
It is assumed that Installation page is read, system is deployed and working. Specifically these sections should be looked at in more detail:
In this lesson we will expand system from Lesson 3 with accessPoint
:
access.ap
-accessPoint
is basically a container for business logic, it will:- load custom library with predefined queries
- establish connections to
core.hdb
andcore.rdb
components
In Lesson 3 it was already shown how components are created: by adding relevant sub-section
in the system.cfg
. It's no different in this case - including access.ap
is simply done by adding few lines
(129
- 136
).
access.ap
component is located in new section [group:access]
(line 127
) which highlights that this component belongs to different functional part of the created system.
Note:
It is up to the system designer to decide on the grouping and names of components used within the system.
--- system.cfg (Lesson 3)
+++ system.cfg (Lesson 4)
47. # List of directories with libraries.
- 48. libPath = ${EC_SYS_PATH}/bin/ec/components/${EC_COMPONENT_PKG}, ${EC_SYS_PATH}/bin/ec/libraries/
+ 48. libPath = ${EC_SYS_PATH}/bin/ec/components/${EC_COMPONENT_PKG}, ${EC_SYS_PATH}/bin/ec/libraries/, ${EC_SYS_PATH}/bin/ec/tutorial/Lesson04/src
...
+ 127. [group:access]
+ 128.
+ 129. [[access.ap]] # Component named 'access.ap' (EC_COMPONENT_ID) - access point for the users, contains business logic
+ 130. command = "q accessPoint.q" # - command line string
+ 131. libs = demolib/queries # - loading custom library "demolib/queries.q" with custom queries.
+ 132. type = q:accessPoint/accessPoint # - q component of type 'accessPoint' (EC_COMPONENT_TYPE)
+ 133. # from package 'accessPoint' (EC_COMPONENT_PKG)
+ 134. port = ${basePort} + 50 # - port
+ 135. memCap = 5000 # - q process memory cap in MB (-w q option)
+ 136. cfg.serverAux = core.rdb, core.hdb # - list of servers that will be used by the accessPoint queries
All custom queries used in this lesson are defined in demolib/queries.q
file under tutorial/Lesson04/src/
directory.
Custom libraries are loaded by setting libs
field in the component's subsection (line 131
).
In our case libs
field contains one library demolib/queries
.
System searches for demolib/queries
library in all paths defined in libPath
(line 48
).
First file matching following pattern will be loaded:
[ libPath ] + [ libs ] + [ .q ]
In our case, it will search for the following files and load the first one which is available:
${EC_SYS_PATH}/bin/ec/components/${EC_COMPONENT_PKG}/demolib/queries.q
${EC_SYS_PATH}/bin/ec/libraries/demolib/queries.q
${EC_SYS_PATH}/bin/ec/tutorial/Lesson04/src/demolib/queries.q <<<< load first existing "demolib/queries" lib
File ${EC_SYS_PATH}/bin/ec/tutorial/Lesson04/src/demolib/queries.q
will be loaded to the process.
Note:
Loading custom libraries with
libs
field can be used in all components.
DemoSystem> yak info \*
uid pid port status started stopped
----------------------------------------------------------------------------------
access.ap 11280 17050 RUNNING 2014.05.08 07:40:03
core.gen 11281 17009 RUNNING 2014.05.08 07:40:03
core.hdb 11290 17012 RUNNING 2014.05.08 07:40:04
core.rdb 11293 17011 RUNNING 2014.05.08 07:40:04
core.tick 11287 17010 RUNNING 2014.05.08 07:40:03
We use accessPoint
component for querying both rdb
and hdb
.
Therefore, it is vital that both connections are configured and the data is available.
q)/execute on process access.ap, port 17050
q).hnd.status[]
server | timeout | state | connstr | handle | ashandle | topen
--------+ --------+------------+--------------------+--------+----------+-------------------------------
core.hdb| 100 | registered | ::17012:tu:0x5... | 10i | -10i | 2014.06.02D09:24:29.134865000
core.rdb| 100 | registered | ::17011:tu:0x5f...| 11i | -11i | 2014.06.02D09:24:33.832915000
Please note state
registered
which indicates that no connection attempts have been made so far. Other possible states are:
open
- connection to client is open and ready to useclose
- connection to client was closed by server (e.g. server disconnected)lost
- connection to client was lost (e.g. client was terminated)failed
- attempt to connect to client failed (e.g. client is not running)
q)/ execute on process core.hdb, port 17012
q) .hdb.status[]
tab | format | rowsCnt | err | columns
------+------------+---------+-----+---------------------------------------------
quote | PARTITIONED | 3570 | | `date `sym `time `bid `bidSize `ask `askSize
trade | PARTITIONED | 720 | | `date `sym `time `price `size
All custom queries are defined in the ec/tutorial/Lesson04/src/demolib/queries.q
file (please compare to the line 48
of system.cfg
presented above). Code is written according to Exxeleron q coding conventions. All queries are written in the .example
namespace.
To see the full list of queries from q:
q)/ execute on process access.ap, port 17050
q) \f .example
bigBuyOrders
currentPrices
dailyVwap
Note:
The following section presents some basic q queries and it is dedicated to fresh q users. If you already have experience with q, you can omit it.
In order to execute one of the custom queries, type its name and provide all parameters in square brackets separated by semicolons. More about q functions can be found in Q for Mortals from Jeffry A. Borror.
q)/ execute on process access.ap, port 17050
q) .example.functionalLastPriceNMinutesBar[30i; `instr0]
sym | minute | price
-------+--------+--------
instr0 | 06:00 | 29.67
instr0 | 06:30 | 12.49
instr0 | 07:00 | 0.28
q) .example.vwap[]
sym | vwap
--------+------
instr0 | 50.04
instr1 | 54.12
instr10 | 47.73
Note:
For simplicity
quote
andtrade
tables are generated automatically and independently usingcore.gen
component. Therefore, inconsistencies such asprice
higher/lower thanbid
/ask
may occur.
To see the body of the query, type its name:
q)/ execute on process access.ap, port 17050
q) .example.vwap
"{[] .hnd.h[`core.rdb] "select vwap: size wavg price by sym from trade"}"
Note: For interprocess communication the
qsl/handle.q
library is used.
The number of records in quote
table by hour:
q)/ execute on process access.ap, port 17050
q).example.tradeStats[]
hh | cnt
-----+------
5 | 120
6 | 3001
Records from quote
and trade
tables are joined based on the time
and sym
columns using aj
function.
Hint:
For more details about joints available in q, please visit official Kx reference page.
q).example.tradeAndQuote[]
time | sym | price | size | bid | bidSize | ask | askSize
-------------+---------+-------+------+-------+---------+-------+---------
05:59:53.937 | instr98 | 78.94 | 25 | 95.92 | 91 | 72.69 | 99
05:59:53.937 | instr17 | 42.44 | 18 | 53.00 | 56 | 48.86 | 33
Last price
by symbol from trade
table:
q).example.currentPrices[]
sym | price
------+ --------
instr0| 65.24
instr1| 72.48
Last price
in 10 minutes bars for first symbol from trade
table:
q).example.lastPrice10MinutesBar[]
minute| price
------+ ------
05:45 | 72.67
06:00 | 80.53
Hint:
More about grouping using
xbar
can be found here.
Last price
in 30 minutes bars for symbol instr0
from trade
table:
q) .example.functionalLastPriceNMinutesBar[30i; `instr0]
sym | minute | price
-------+--------+--------
instr0 | 06:00 | 29.67
instr0 | 06:30 | 12.49
instr0 | 07:00 | 0.28
Orders for which bid
size was greater than average by symbol from quote
table:
q).example.bigBuyOrders[]
time | sym | bid | bidSize | ask | askSize
-------------+--------+-------+---------+---------+----------
05:59:48.935 | instr1 | 37.56 | 64 | 97.85 | 34
05:59:48.935 | instr73| 52.94 | 82 | 94.416 | 61
Volume-weighted average price
by symbol:
q) .example.vwap[]
sym | vwap
--------+------
instr0 | 50.04
instr1 | 54.12
instr10 | 47.73
Volume-weighted average price
by symbol and date:
q)/ execute on process access.ap, port 17050
q).example.dailyWwap[]
sym | date | vwap
-------+------------+-----
instr0 | 2014.05.28 | 51.99
instr1 | 2014.05.28 | 51.49
open
, high
, low
, close
, volume-weighted average price
from hdb
trade
table aggregated by date
, symbol
and time
bars:
q).example.ohlcVwap[`instr0`instr1;.z.d-2; 08:00:00; 20:00:00; 600]
date |sym | time | open | high | low | close | size | vwap
-----------+--------+----------|------ +-------+-------+-------+------+------
2014.05.28 | instr0 | 09:00:00 | 70.80 | 70.80 | 70.80 | 70.80 | 83 | 70.80
2014.05.28 | instr0 | 09:10:00 | 15.95 | 86.75 | 11.53 | 11.53 | 420 | 49.15
Until now, we queried rdb
and hdb
separately. In case of queries which retrieve data from both components there is a possibility to use dedicated library libraries/qsl/query.q
. A function .query.data
is defined there and detailed documentation with examples of use is provided. Below only one example is shown, the same function as above, but with data from both components.
❗ Note:
Explaining the q syntax for queries is outside of the scope of this document. In particular, there's significant difference between using q-sql for in-memory (
rdb
) and persistent databases (hdb
). More details can be obtained from thorough "Q for Mortals" from Jeffry A. Borror (rdb queries and hdb queries ).
open
, high
, low
, close
, volume-weighted average price
from hdb
and rdb
trade
table aggregated by date
, symbol
and time
bars:
q)/ execute on process access.ap, port 17050
q).example.ohlcVwap2[syms;(.z.d-2;.z.d); 06:00:00; 20:00:00; 600]
date |sym | time | open | high | low | close | size | vwap
-----------+--------+----------|------ +-------+-------+-------+------+-------
2014.05.28 | instr0 | 09:00:00 | 70.80 | 70.80 | 70.80 | 70.80 | 83 | 70.80
2014.05.28 | instr0 | 09:10:00 | 15.95 | 86.75 | 11.53 | 11.53 | 420 | 49.15
Note:
If you would like to add more custom queries, modify one of the libraries specified in the
system.cfg
file and restartaccess.ap
process.