Queries
QQL allows running various select queries to extract data from TimeBase streams.
[WITH expr_list]
SELECT [DISTINCT|RUNNING]
simple_expr_list|record_expr_list
[FROM stream_name|REVERSE(stream_name)|LIVE(stream_name)|(SELECT ...)]
[[LEFT] ARRAY JOIN expr_list]
[[TRIGGER|RESET] OVER [EVERY] [count_expr|time_expr]]
[WHERE expr]
[GROUP BY expr_list]
[HAVING expr]
[LIMIT limit [OFFSET offset]]|[LIMIT offset, limit]
[UNION ...]
expr_list [TYPE type_name]
[RECORD expr_list TYPE type_name WHEN expr, RECORD expr_list TYPE type_name WHEN expr, ...]
info
Refer to QQL Introduction for general overview.
caution
Inner selects (select from (select ...)) available since 5.6.78.
Simple Select
Make simple expression selects not related to specific TimeBase streams.
SELECT 42
SELECT 1==1
SELECT 2+2
SELECT 2 + 2, 1 == 1, 42
Select Entire Objects
You can run select * command to query entire stream objects. You can select data from a stream in direct or reversed order or select just the first/last messages from the stream - see examples below in text.
Template: select * from stream_name
# Shell format
select * from tickquerydemo
>deltix.timebase.api.messages.BestBidOfferMessage,TIMESTAMP,SYMBOL,TYPE,offerPrice,offerSize,bidPrice,bidSize
0,2011-10-17 17:21:40,GREATCO,EQUITY,43.5,100.0,42.5,200.0
1,2011-10-17 17:21:40,XBANK,EQUITY,301.75,40000.0,301.25,800.0
>deltix.timebase.api.messages.TradeMessage,TIMESTAMP,SYMBOL,TYPE,price,size
2,2011-10-17 17:21:41,XBANK,EQUITY,301.25,800.0
>deltix.timebase.api.messages.BestBidOfferMessage,TIMESTAMP,SYMBOL,TYPE,offerPrice,offerSize,bidPrice,bidSize
3,2011-10-17 17:21:42,XBANK,EQUITY,301.5,60000.0,298.5,800.0
4,2011-10-17 17:21:43,GREATCO,EQUITY,45.0,100.0,43.0,400.0
5,2011-10-17 17:21:43,XBANK,EQUITY,299.5,40000.0,295.0,300.0
>deltix.timebase.api.messages.TradeMessage,TIMESTAMP,SYMBOL,TYPE,price,size
6,2011-10-17 17:21:44,GREATCO,EQUITY,44.0,100.0
-- select first message from stream
SELECT first (*) FROM binance
-- select last message from stream
SELECT last (*) FROM binance
-- select stream messages in the reversed order
SELECT * FROM REVERSE(binance)
Select Object Fields
Use select command in combination with the name of specific fields to query specific fields from objects. Enter a comma-separated list of fields to query more than one field.
Template: select field_name from stream_name
# Shell format
select price, bidPrice, offerPrice from tickquerydemo
>_,TIMESTAMP,SYMBOL,TYPE,price,bidPrice,offerPrice
0,2011-10-17 17:21:40,GREATCO,EQUITY,_,42.5,43.5
1,2011-10-17 17:21:40,XBANK,EQUITY,_,301.25,301.75
2,2011-10-17 17:21:41,XBANK,EQUITY,301.25,_,_
3,2011-10-17 17:21:42,XBANK,EQUITY,_,298.5,301.5
4,2011-10-17 17:21:43,GREATCO,EQUITY,_,43.0,45.0
5,2011-10-17 17:21:43,XBANK,EQUITY,_,295.0,299.5
6,2011-10-17 17:21:44,GREATCO,EQUITY,44.0,_,_
!end
You can use object_name.* query format to unfold all object fields into separate columns, where column names have object_name.field_name format.
SELECT entries[0].* FROM binance
SELECT order.* FROM orders
Select Ambiguous Fields
You may as well come across the situation, when there will be fields with the same name and data type in different objects (classes). In this case, if you query such a field just by itself, you will receive an ambiguous identifier error.
select * from stream_name
WHERE algoId == 14
select algoId from stream_name
Use the below example to specify the object to take algoId from.
select * from stream_name
WHERE "AlgoInstrumentConfig":algoId == 14
You can also use casts to convert the object to the required type.
select * from stream_name
WHERE (this as "AlgoInstrumentConfig").algoId == 14
tip
Refer to Casts to learn more about them.
Select from Nested Objects
TimeBase objects may include one or more level of embedded objects or properties. Use the following query format to select nested properties from objects.
Template: select object.nested property from stream_name
object.nested property is a path in the object tree.
-- returns Size values from the object Info that is a nested object in the Order object
SELECT order.info.size FROM orders
-- returns price and size from the first element of PackageHeader entries array
SELECT entries[0].price, entries[0].size FROM binance
Working with Enums
QQL allows placing queries considering specific Enum values.
-- select info objects where side enum value is BUY
SELECT order FROM orders
WHERE order.info.side == BUY
-- select Entries arrays where packageType enum value is PERIODICAL_SNAPSHOT
SELECT entries FROM binance
WHERE packageType == PERIODICAL_SNAPSHOT
-- in case there is more than one Enum with the same value, you can specify the specific one
SELECT entries FROM binance
WHERE packageType == "deltix.timebase.api.messages.universal.PackageType":INCREMENTAL_UPDATE
-- specify that the FX value belongs to a specific enum InstrumentType
SELECT * FROM "securities"
where type == "deltix.timebase.api.messages.InstrumentType":FX
Use Expression Aliases
In QQL, you can use aliases similar to SQL to have control over the returned field names. Then, you can reference the object by the alias name in your query. After AS you can use both identifiers and string literals. Embrace your entry in a single quotes to make it a string literal. This approach is considered a best practice.
info
Refer to FIELD to learn how to assign names to columns in the SELECT expression.
tip
As a best practice, we advise using string literals as aliases to distinguish it from casts.
Template: SELECT field_name AS 'custom_field_name' FROM stream_name
#Shell format
==> SELECT symbol AS 'sym' FROM securities
>_,TIMESTAMP,SYMBOL,TYPE,SYM
0,_,,SYSTEM,ESZ11
1,_,,SYSTEM,NQZ11
2,_,,SYSTEM,ESU11
3,_,,SYSTEM,NQU11
4,_,,SYSTEM,AAPL
5,_,,SYSTEM,GOOG
SELECT order.info.size AS 'size' FROM orders
WHERE size > 1000
tip
Please note, that it is possible to use a keyword as with both aliases and casts. The following principle applies:
- as works as alias in case a variable after as does not match any existing object and such a variable is used to name a column.
- as works as cast in case a variable after as matches an existing object. Embrace a variable name after as in single quotes to treat it as alias for column naming.
Select Distinct Values
Run select distinct to select distinct values from streams.
Template: select distinct field_name from stream_name
#Shell format
==> SELECT DISTINCT symbol FROM securities
>_,TIMESTAMP,SYMBOL,TYPE,$SYMBOL
0,_,,SYSTEM,ESZ11
1,_,,SYSTEM,NQZ11
2,_,,SYSTEM,ESU11
3,_,,SYSTEM,NQU11
4,_,,SYSTEM,AAPL
5,_,,SYSTEM,GOOG
If you look at the attached example output, you will notice that:
- The timestamp of each returned message is NULL. This makes sense, because each returned line represents a number of records, each with its own timestamp.
- The symbol of each message is an empty string (the special SYMBOL field is non-nullable in TimeBase).
- The instrument type of each message is SYSTEM.
- The class name of what was returned is NULL (represented by the underscore character). This makes sense, because we never named this class. It was created on-the-fly from the query.
- The class created for representing query results contains a single field, called $SYMBOL. The reason it is not called SYMBOL is because the keyword SYMBOL is reserved for the entity key.