Queries
QQL allows running various select queries to extract data from TimeBase streams.
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 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
Select Ambiguous Fields
You may as well come across the situation, when there will be fields with the same name and dat 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 Column 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.
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 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.