Skip to main content

QQL 5.5

caution

This document is for TimeBase Community Edition and TimeBase Enterprise Edition 5.5

QuantQuery Language (QQL) is a query language designed to extract, filter, aggregate and transform data in TimeBase streams.

While QQL is very similar to SQL, there are very significant differences. QQL is designed to query polymorphic time-series data, while SQL is designed primarily for querying monomorphic (fixed-structure) data, without any time-series properties.

Getting Started

We suggest using Demo streams for demonstration purposes to guide you through QQL language. Most of examples in this tutorial are based on demo streams.

Import Demo Streams

To quickly get started with QQL, download and unzip the below archive file to any folder on your computer. Launch TimeBase and import demo streams.

  1. Launch TimeBase and TimeBase Web Admin. You can use either TimeBase Installer or run in Docker or see other deployment options.

  2. Run TimeBase Shell by double-clicking tickdb.cmd from bin folder.

  3. Set database:

    Set database
    ==> set db dxtick://localhost:8011
    ==> open
  4. Download demo streams and import them to TimeBase:

    Run this in TimeBase Shell to import demo streams to TimeBase server
    open rw
    set src C:\path to a stream file\binance.qsmsg.gz
    set tz GMT
    import binance

    open rw
    set src C:\path to a stream file\orders.qsmsg.gz
    set tz GMT
    import orders

    open rw
    set src C:\path to a stream file\tickquerydemo.qsmsg.gz
    set tz GMT
    import tickquerydemo

    open rw
    set src C:\path to a stream file\packages.qsmsg.gz
    set tz GMT
    import packages

    open rw
    set src C:\path to a stream file\securities.qsmsg.gz
    set tz GMT
    import securities
  5. Use TimeBase Shell console to run QQL commands.

  6. Use also TimeBase Web Admin application to run QQL commands.

note

In this tutorial we will provide, for demonstration purposes, query examples in TimeBase Shell format and screenshots from TimeBase Web Admin application.

info

Refer to Quick Start to learn how to start TimeBase in a few simple steps.

Using TimeBase Shell

Run TimeBase Shell by double-clicking tickdb.cmd from bin folder. After you start TimeBase Shell, it displays a console window with a prompt. You can type or paste in various commands, and the Shell will respond. Use TimeBase Shell Guide to learn more about TimeBase Shell available commands.

QQL queries return a sequence of stream messages ordered by time. Every time the type of message changes, you see a heading row, such as:

>deltix.timebase.api.messages.BestBidOfferMessage,TIMESTAMP,SYMBOL,TYPE,offerPrice,offerSize,bidPrice,bidSize

In the first cell, you see the "greater" sign, followed by the class name of the message. Next, you see standard field headings TIMESTAMP, SYMBOL and TYPE. The following headings, if any, contain field names declared in the message class.

Lines following the header line contain message data. The first column contains the sequential number of a message (not counting header lines, of course). The rest of the columns contain data values according to the headings.

Every query returns messages, and every message in TimeBase has the class name, the timestamp, and the symbol/type combination, collectively called the entity identity. See Messages to learn more.

Query example in TimeBase Shell format
#query
select price, bidPrice, offerPrice from tickquerydemo

#output
>_,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
info

See TimeBase Shell Guide for more information.

Using TimeBase Web Admin

TimeBase Web Admin application allows viewing streams, stream schemas, creating and editing streams, placing QQL queries and many other operations.

info

See TimeBase Web Admin for more information on running QQL queries in Web Admin application.

Querying Data

QQL allows running various select queries to extract data from TimeBase streams.

Synopsis
[WITH expr_list]
SELECT [DISTINCT|RUNNING] expr_list
[TYPE type_name]
[FROM stream_name|REVERSE(stream_name)|LIVE(stream_name)]
[[LEFT] ARRAY JOIN expr_list]
[[TRIGGER|RESET] OVER [EVERY] [count_expr|time_expr]]
[WHERE expr]
[GROUP BY expr_list]
[LIMIT limit [OFFSET offset]]|[LIMIT offset, limit]
[UNION ...]
info

Refer to QQL Introduction for general overview.

Simple Select

Make simple expression selects not related to specific TimeBase streams.

Examples of simple expressions
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 first/last messages from the stream - see examples below in text.

Template: select * from stream_name

Select objects
#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

Select specific fields from an object
#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 objects
SELECT entry.* 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.

You will receive an error in these two cases if more than one object has algoId of the same type.
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 algoId from AlgoInstrumentConfig object
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.

Select from nested objects
/*returns Size values from the object Info that is a nested object in the Order object*/
SELECT order.info.size FROM orders

Working with Enums

QQL allows placing queries considering specific Enum values.

Select by the enum value
#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 VENDOR_SNAPSHOT
SELECT entries FROM packages
WHERE packageType == VENDOR_SNAPSHOT

# in case there is more than one Enum with the same value, you can specify the specific one
SELECT entries FROM packages
WHERE packageType == "deltix.timebase.api.messages.universal.PackageType":VENDOR_SNAPSHOT

# specify that the FX value belongs to a specific enum InstrumentType
SELECT * FROM "securities"
where type == "deltix.timebase.api.messages.InstrumentType":FX

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.

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

Using string literals as alias names
#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
Use alias name as object reference
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:

  1. 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.
  2. 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.

Data Filtering

Basic Filtering

Use WHERE clause to filter messages. Enclose in single quotes the filtering value - see attached example.

tip

WHERE clause uses Boolean values as an entry value. See Operators to learn more about available operators.

Using WHERE for filtering
#Shell format

==> SELECT * FROM tickquerydemo WHERE symbol == 'XBANK'

>deltix.timebase.api.messages.BestBidOfferMessage,TIMESTAMP,SYMBOL,TYPE,offerPrice,offerSize,bidPrice,bidSize
0,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
1,2011-10-17 17:21:41,XBANK,EQUITY,301.25,800.0
>deltix.timebase.api.messages.BestBidOfferMessage,TIMESTAMP,SYMBOL,TYPE,offerPrice,offerSize,bidPrice,bidSize
2,2011-10-17 17:21:42,XBANK,EQUITY,301.5,60000.0,298.5,800.0
3,2011-10-17 17:21:43,XBANK,EQUITY,299.5,40000.0,295.0,300.0

Filtering by Time

To filter messages by timestamp, you need to include relational conditions comparing the special field timestamp with, typically, a date literal.

Filter by time
#Shell format

==> SELECT * FROM tickquerydemo WHERE timestamp > '2011-10-17 17:21:40'd

>deltix.timebase.api.messages.TradeMessage,TIMESTAMP,SYMBOL,TYPE,price,size
0,2011-10-17 17:21:41,XBANK,EQUITY,301.25,800.0
>deltix.timebase.api.messages.BestBidOfferMessage,TIMESTAMP,SYMBOL,TYPE,offerPrice,offerSize,bidPrice,bidSize
1,2011-10-17 17:21:42,XBANK,EQUITY,301.5,60000.0,298.5,800.0
2,2011-10-17 17:21:43,GREATCO,EQUITY,45.0,100.0,43.0,400.0
3,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
4,2011-10-17 17:21:44,GREATCO,EQUITY,44.0,100.0

==> SELECT * FROM tickquerydemo WHERE timestamp BETWEEN '2011-10-17 17:21:41'd AND '2011-10-17 17:21:42'd

>deltix.timebase.api.messages.TradeMessage,TIMESTAMP,SYMBOL,TYPE,price,size
0,2011-10-17 17:21:41,XBANK,EQUITY,301.25,800.0
>deltix.timebase.api.messages.BestBidOfferMessage,TIMESTAMP,SYMBOL,TYPE,offerPrice,offerSize,bidPrice,bidSize
1,2011-10-17 17:21:42,XBANK,EQUITY,301.5,60000.0,298.5,800.0

TimeBase will perform timestamp filtering against literals very efficiently, using the internal time index. For example, the above query does not cause TimeBase to read all data from the beginning of the stream, while testing each message against the filter. Instead, TimeBase will use the internal time index to start reading stream at the exact point in the stream where messages with the first timestamp, larger than 2011-10-17 17:21:40, begin.

Meet the below conditions in order for TimeBase to recognize your intent and perform this optimization:

  • Directly compare timestamp with a date literal (or parameter) using a relational operator <, >, <=, >= or between. See Operators to learn more about available operators. See Constants to learn more about available time constants formats.
  • There must be either the only condition of the select statement, or one of several connected by the conjunction operator and conditions.
tip

Unless you specify a timezone all timestamp literals assume GMT timestamp:

Filter selection by the time zone
SELECT * FROM gaps WHERE timestamp > '2015-03-04 10:55:00 America/New_York'd
  • It does not matter whether timestamp is on the left or right side of the relational operator.
  • You can have any number of conditions connected by and and intermixed with any number of unrelated conditions, as long as time conditions are at the top level.

As mentioned above, you can also use the between expression. Remember that between is an inclusive condition:

Select from the time range using BETWEEN/AND
SELECT * FROM tickquerydemo WHERE timestamp BETWEEN '2011-10-17 17:21:41'd AND '2011-10-17 17:21:42'd

Filtering by Object Type

Use THIS keyword to filter queries by object type.

Select from a specific object
#returns all records from TradeMessage object type that meet the condition
SELECT * FROM tickquerydemo WHERE price > 300 AND THIS IS deltix.timebase.api.messages.TradeMessage
Please Note
  • this means current message in this case
  • is <class_name> creates an object type check
  • In type names you can use the dot character without enclosing the entire identifier in quotes.
  • The capitalization of the type name is irrelevant, because QQL performs case-insensitive matching of identifiers, unless existing identifiers differ in case only.

Nullability

Any data field in QQL may be declared as nullable, regardless of its data type. If so declared, it may contain the special out-of-band value of NULL, which basically means "no data". Additionally, NULL values are generated by queries in special cases. A NULL value is formatted as an underscore character: "_", so it can be differentiated from an empty string. Unlike ORACLE, an empty string value is distinctly different from a NULL value.

Array elements can as well be nullable and not nullable.

Skip NULL values
#filter out NULL values
SELECT offerPrice AS 'price' FROM tickquerydemo
WHERE symbol == 'GREATCO' AND offerPrice IS NOT NULL

Predicates: getting object attributes

You can use THIS variable to iterate through object elements, filter objects by type, filter object fields against a specified condition.

Using THIS for filtering
#select just size that meets the condition
SELECT order.info.size [this > 500] FROM orders

#select just LimitOrder types or Orders
SELECT order[this is deltix.orders.LimitOrder].info.* FROM orders

#select correlationId where source is LO_SOURCE, otherwise return NULL
SELECT order.id[source == 'LO_SOURCE'].correlationId FROM orders

Working with Polymorphic Data

Polymorphism in TimeBase is the ability of a single stream to contain multiple message types. See Basic Concepts and Messages to learn more.

In the following example we query tickquerydemo stream for all records. As the result, we get messages of different types: TradeMessage and BestBidOfferMessage.

#Shell format

==> SELECT * FROM tickquerydemo

>deltix.timebase.api.messages.TradeMessage,TIMESTAMP,SYMBOL,TYPE,price,size
0,2011-10-17 17:21:41,XBANK,EQUITY,301.25,800.0
>deltix.timebase.api.messages.BestBidOfferMessage,TIMESTAMP,SYMBOL,TYPE,offerPrice,offerSize,bidPrice,bidSize
1,2011-10-17 17:21:42,XBANK,EQUITY,301.5,60000.0,298.5,800.0
2,2011-10-17 17:21:43,GREATCO,EQUITY,45.0,100.0,43.0,400.0
3,2011-10-17 17:21:43,XBANK,EQUITY,299.5,40000.0,295.0,300.0

Polymorphic Union Model and GROUP BY SYMBOL

Selecting * means simply selecting the current message - alone. Selecting any other combination of fields implies the construction of a new message type.

For example, we can try and select the combination of price, bidPrice, offerPrice. Even though these fields are picked from two different message types, this is a legal request. Its meaning is "give me the last price and quote values as of the time of each event". The result of such a query is not very useful in our scenario, since it mixes the data of different symbols:

Select from different classes
#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

While executing this query, the QQL engine creates an object called Query State, containing, in our example, three fields: price, bidPrice, offerPrice Initially these fields are set to NULL. As each message comes into the query engine, the corresponding fields are updated. As we know, the first underlying message is: [from the select * query]. This first message causes the first update to the Query State, which we see in the output:

#Shell output format
>_,TIMESTAMP,SYMBOL,TYPE,price,bidPrice,offerPrice
0,2011-10-17 17:21:40,GREATCO,EQUITY,_,42.5,43.5

As you see, price is still NULL, because we have not seen a TradeMessage yet. The next message in the source data set is another BestBidOfferMessage, this time for XBANK. This new message again updates the bidPrice and offerPrice fields of the Query State, while price is still NULL. The third message (a TradeMessage) finally updates the price field, and so on. In QQL terminology, the Query State object represents a Polymorphic Union Model (PUM) of the source data.

The following diagram illustrates the flow of data in the query we just ran:

As we mentioned, such a query is useless on market data, as it mixes price from one symbol with quote values from another, as seen in messages #4 and #6:

#Shell format
4,2011-10-17 17:21:43,GREATCO,EQUITY,301.25,43.0,45.0
6,2011-10-17 17:21:44,GREATCO,EQUITY,44.0,295.0,299.5

What we want instead is to track the Query State independently for each symbol. Fortunately, there is a very simple way to make the QQL engine behave the way we want, by specifying the GROUP BY SYMBOL clause. While in relational SQL the GROUP BY clause is only valid when aggregate values are being selected (such as MAX(), COUNT(), etc.), in QQL this clause has the more general effect of determining how many Query States are created, and how they are indexed.

#Shell format
SELECT price, bidPrice, offerPrice FROM tickquerydemo GROUP BY SYMBOL

>_,TIMESTAMP,SYMBOL,TYPE,price,bidPrice,offerPrice
0,2011-10-17 19:21:44,GREATCO,EQUITY,44.0,_,_
1,2011-10-17 19:21:43,XBANK,EQUITY,_,295.0,299.5
!end

Without the GROUP BY SYMBOL clause, there is only one Query State object for all symbols. When the GROUP BY SYMBOL clause is present, there is a separate Query State object message created for each distinct symbol. Each new source message updates the fields of the corresponding Query State:

Adding GROUP BY SYMBOL to our polymorphic query instantly makes the result look the way we expect.

Data Types

Conversions

The following is a matrix of supported by QQL data types for numerics and their conversions. The general conversion principle is that the higher priority always beats the lower priority: Int8 + Int16 = Int16.

tip

Note, that casting from higher to lower precision types causes data loss. For example cast from Int16 to Int8.

Int8Int16Int32Int64Decimal64Float32Float64
Int8Int32Int32Int32Int64Decimal64Float32Float64
Int16Int32Int32Int32Int64Decimal64Float32Float64
Int32Int32Int32Int32Int64Decimal64Float32Float64
Int64Int64Int64Int64Int64Decimal64Float32Float64
Decimal64Decimal64Decimal64Decimal64Decimal64Decimal64Float32Float64
Float32Float32Float32Float32Float32Float32Float32Float64
Float64Float64Float64Float64Float64Float64Float64Float64

Operations with Arrays

When performing various operations with arrays of different types, the following principles apply:

  • Array(a) ◦ b = Array(type(a ◦ b))
  • a ◦ Array(b) = Array(type(a ◦ b))
  • Array(a) ◦ Array(b) = Array(type(a ◦ b))

where

  • a/b - data types
  • - any operation
Using operators with arrays
#select all Entries array elements with Price fields and add 3 to each Price value
SELECT entries.price +3

Nullability Conversions

Any data field in QQL may be declared as nullable, regardless of its data type. If so declared, it may contain the special out-of-band value of NULL, which basically means "no data". Additionally, NULL values are generated by queries in special cases. A NULL value is formatted as an underscore character: "_", so it can be differentiated from an empty string. Unlike ORACLE, an empty string value is distinctly different from a NULL value.

Array elements can as well be nullable and not nullable.

When performing operations with two data types with different nullability, the following principle apply:

isNullable=true + isNullable=false = isNullable=true.

TrueFalse
TrueTrueTrue
FalseTrueFalse

Constants

QQL allows using various types of constants with different operations with simple types and/or arrays. See Data Types for more information on conversions.

Numeric Constants

  1. Integer constants. Examples: 123, 1669, -362367, 8238 -> Compiles to Int32 type.
  2. Long constants. Examples: 123L, 1669L, -23643787L, 237237723777L -> Compiles to Int64 type.
  3. Decimal constants. Examples: 236.2377, 2366236.23773, -0.237237, 1.36e-5, -1.238e10 -> Compiles to Decimal64 type.
  4. Float64 constants. Examples: 236.2377f, 2366236.23773f, -0.237237f, 1.36e-5f, -1.238e10f -> Compiles to Float64 type.
  5. Time Interval constant. Template {days}d{hours}h{minutes}m{seconds}s{milliseconds}ms. Examples: 1h, 1d10h20m42s500ms, 3000ms -> Compiles to Int64.

Numeric Arrays

  1. Integer arrays. Examples: [126, 2388, -2377], [42] -> Compiles to Array(Int32).
  2. Long arrays. Examples: [126L, 2388, -2377], [126L, 23882377L, -2377L] -> Compiles to Array(Int64)
  3. Decimal64 arrays. Examples: [126.4366, -3277, 2388.238], [0.2388, -0.00002377236, -1.238e10] -> Compiles to Array(Decimal64)
  4. Float64 arrays. Examples: [126.4366f, -3277, 2388.238f], [0.2388f, -0.00002377236, -1.238e10f] -> Compiles to Array(Float64)
  5. Time Interval arrays. Examples: [1h, 5m30s, 1201200L], [2370ms, 1000000L] -> Compiles to Array(Int64)

Varchar and Varchar Arrays

  1. Varchar (UTF8). Examples: 'Hello, it\'s me!, Don\'t panic!, 'How are you?'.

  2. Varchar arrays. Examples: ['Hello, it\'s me!', 'Don\'t panic!', 'How are you?'], ['Hello', 'World'].

    Special CharacterDisplay
    \'Single quotation
    \"Double quotation
    \\Backslash
    \tTab
    \bBackspace
    \rCarriage return
    \fFormfeed
    \nNew line

Char Constants and Arrays

  1. Char. Examples: '\''c, 'a'c, '1'c.

  2. Char arrays. Examples: ['\''c, 'a'c, '1'c]

    Special CharacterDisplay
    \'Single quotation
    \"Double quotation
    \\Backslash
    \tTab
    \bBackspace
    \rCarriage return
    \fFormfeed
    \nNew line

Boolean Constants and Arrays

  1. Boolean. Examples: true, false.
  2. Boolean arrays. Examples: [true, true, false, false], [true].

Timestamp Constants and Arrays

  1. Timestamp. Examples: '2020-10-13 11:22:48'd, '2021-01-01 11:42:48.553'd
  2. Timestamp arrays. Examples: ['2020-10-13 11:22:48'd, '2021-01-01 11:42:48.553'd]

Date Literals

The QQL syntax of date literals is different from SQL. Instead of something like DATE '...', the syntax is '...'d.

Date specification format:

<year>[-<month>[-<day>[ <hour (24)>[:<minute>[:<second>[.<fraction>]]]]]][ <time zone>]

This means that you can specify either a year only, or a year and a month, or a year, a month, and a day, and so on. However, the time zone can be specified or omitted independently. Any omitted values take reasonable defaults, such as January, 1st day of month, the GMT time zone and zeros for all other components.

Examples of valid date literals:

  • '2008'd
  • '2008 America/New_York'd
  • '2008-03-28'd
  • '2008-03-28 America/New_York'd
  • '2008-03-28 14'd
  • '2008-03-28 14:02:59.1'd
  • '2008-03-28 14:02:59.317859261'd

String Literals

In QQL requests you can embrace a string in a single quotes to make it a string literal. For example, you can use both identifiers and string literals after a keyword AS; therefore, embrace your entry in a single quotes to make it a string literal. In the below example we use a string literal to set the alias for the SYMBOL column.

Using string literals
#Shell format
==> SELECT symbol AS '*Symbol*' FROM securities

>_,TIMESTAMP,SYMBOL,TYPE,*Symbol*
0,_,,SYSTEM,ESZ11
1,_,,SYSTEM,NQZ11
2,_,,SYSTEM,ESU11
3,_,,SYSTEM,NQU11
4,_,,SYSTEM,AAPL
5,_,,SYSTEM
info

Refer to VARCHAR functions for more string literals usage examples.

Working with Arrays

Select Entire Array

Enter a specific array name instead of * with select command to query the entire array by it's name. In case you query a not existing array, you get NULL in the response.

Template: select array_name from stream_name

Selecting arrays by the name
#returns all Entires arrays from binance2 stream
SELECT entries FROM binance2

Select Array Elements

Use dot separator to specify a specific array element.

Template: select array_name.field_name from stream_name

Picking elements from arrays
#select all Entries arrays
SELECT entries FROM bitfinex

#return an array of Price fields from all Entries arrays
SELECT entries.price FROM bitfinex

#check all array Price elements against the specified condition and return an array of boolean elements
SELECT entries.price > 10 FROM bitfinex

#checks array values and returns a single true/false per array
SELECT ANY(entries.price > 10) FROM bitfinex

#use alias name as object reference
SELECT
(entries[THIS IS deltix.timebase.api.messages.universal.L1Entry] AS trades).price AS 'Price',
trades.size AS 'Size'
FROM kraken
WHERE size(trades) > 0

#returns arrays of Price values that match the condition
SELECT entries.price AS 'price' FROM bitfinex
WHERE ANY(entries.price > 10)

#selecting Bid and Ask prices and sizes from Entries array in the L1entry object
WITH
entries AS array(deltix.timebase.api.messages.universal.L1entry) AS l1
SELECT
l1[side == ASK].price AS 'askPrice',
l1[side == ASK].size AS 'askSize',
l1[side == BID].price AS 'bidPrice',
l1[side == BID].size AS 'bidSize'
FROM binance
WHERE size(l1) > 0

#select all Prices from Entries array in the L1Entry object that are both > 0 and > 20
SELECT entries[THIS IS deltix.timebase.api.messages.universal.L1Entry].price AS 'Price' FROM bitfinex
WHERE size(entries[THIS IS deltix.timebase.api.messages.universal.L1Entry]) > 0 AND ALL(entries[THIS IS deltix.timebase.api.messages.universal.L1Entry].price > 20)

Select Embedded Arrays

QQL allows selecting arrays within arrays. Such queries return arrays with elements from embedded arrays.

Template: select array_name.embedded_array_name.field_name from stream_name

Selecting embedded arrays
#returns all Attributes arrays that are embedded in the Entries array
SELECT entries.attributes FROM packages

#returns an array with all Value fields from all Attributes arrays that are embedded in the Entries array
SELECT entries.attributes.value FROM packages

Filter Arrays

Filter by Type

Use keyword THIS to filter arrays by type.

Filter arrays using THIS keyword
#select Entries arrays from L1Entry object type that meet the condition
SELECT entries[THIS IS deltix.timebase.api.messages.universal.L1Entry] AS 'entry' FROM bitfinex
WHERE size(entries[THIS IS deltix.timebase.api.messages.universal.L1Entry]) > 0

#select specific fields from Entries array within the L1Entry object
SELECT entries[THIS IS deltix.timebase.api.messages.universal.L1Entry].price AS 'Price', entries[THIS IS deltix.timebase.api.messages.universal.L1Entry].size AS 'Size' FROM bitfinex
WHERE size(entries[THIS IS deltix.timebase.api.messages.universal.L1Entry]) > 0

Nullability Check

Filtering out NULL values
SELECT entries AS array(deltix.entries.TradeEntry)[THIS IS NOT NULL] FROM packages

Predicates

You can use THIS variable to iterate through array elements, filter objects by type, filter object fields against a specified condition.

Using THIS to iterate through array elements
#returns just Entries array elements of a Trade type. In this case we check all Entries array elements one by one and select just the ones of a Trade type.
SELECT entries[THIS IS Trade] FROM stream

#In this case we check all Size values in all Entries array elements and return only those that meet the query condition (>10). Query returns an arrays of Size values that are greater than 10.
SELECT entries.size[THIS > 10] FROM packages
SELECT entries[THIS.size > 10].size FROM packages
SELECT entries[size > 10].size FROM packages

#returns all Entries array elements where Price field value is greater than 2000
SELECT entries[THIS.price > 2000] FROM packages

#same as previous - THIS variable can be skipped in this case
SELECT entries[price > 2000] FROM packages

#take Entry objects where Price is greater than 4000 and select Price fields that are less than 5000
SELECT entry[price > 4000].price[THIS < 5000] FROM packages
ARRAY JOIN entries AS entry

Select by Element Index

You can select array elements by their indices.

Template: select array_name[array_element_index] from stream_name

Selecting by the index
#select Entries array with index = 10
SELECT entries[10] FROM packages

#select symbol and max Price with index = 0 from Entries array from binance2 stream and aggregate all by symbol
SELECT symbol, max(entries.price [0]) FROM binance2
GROUP BY symbol

Selecting array records with negative indices.

Template: select array_name[array_element_index] from stream_name

Selecting negative indices
#returns records in the reversed order. This query returns the first array record from the tail.
SELECT entries[-1] FROM packages

Selecting an array of records by their indices.

Template: select array_name[[index_array]] from stream_name

Selecting array elements by their indices
#returns an array of Entries records according to their indices
SELECT entries[[2, 3]] FROM packages

Selecting array elements using an array of Boolean values.

Each Boolean element corresponds to the array element index.

Template: select array_name[[boolean_values_array]] from stream_name

#returns array elements with indices 0 and 2
SELECT entries[[True, False, True]] FROM packages

#returns all records with price greater than 2000. [entries.price > 2000] returns an array of boolean values.
#See Comparison Operations further in this manual.
SELECT entries[entries.price > 2000] FROM packages

Slicing Queries

Slicing queries use index ranges to query array elements.

Template: select array_name[index_range] from stream_name

#select all Entries array elements between indices 1 and 2
SELECT entries[1:2] FROM packages

#select all Entries array elements with index less than 2
SELECT entries[:2] FROM packages

#select all Entries array elements with index greater than 2
SELECT entries[2:] FROM packages

Slicing with Steps

Template: select array_name[index_range_with_step] from stream_name

Slicing examples
#select all Entries array elements
SELECT entries[::] FROM packages

#select all Entries array elements in the reversed order
SELECT entries[::-1] FROM packages

#select Entries array elements with indices from 1 to 5 with step=2
SELECT entries[1:5:2] FROM packages

Position

Position function can be applied only within square brackets [] and allows checking each array element one by one by their indices against a specified condition.

Using Position function
#select Price from Entries array with indices that are greater than 3
SELECT entries[position() > 3].price FROM stream

#select Price from Entries array with both index that is greater than 3 and Price value that is less than 1000
SELECT entries[price < 1000 and position() > 3].price FROM stream

Array Join

Array Join function allows executing Join operation on nested arrays.

Array Join unfolds nested arrays and joins every array element with the existing stream message. Empty elements are skipped.

Example for illustration purposes:

Consider the following stream:

timeStampnumberscharacters
1,[1,2,3],[]
2,[3,4,5],[]
3,[],[a,b,c]
4,[],[e,f,g]
  1. Let's make Array Join by Characters:

    #Numbers arrays are NULL, because we Join by Characters arrays
    SELECT * FROM stream
    ARRAY JOIN characters
    timeStampnumberscharacters
    3,NULL,a
    3,NULL,b
    3,NULL,c
    4,NULL,e
    4,NULL,f
    4,NULL,g
  2. We can get the same result a bit different way:

    SELECT timestamp, numbers, characters FROM stream
    ARRAY JOIN characters
    timeStampnumberscharacters
    3,NULL,a
    3,NULL,b
    3,NULL,c
    4,NULL,e
    4,NULL,f
    4,NULL,g
  3. Let's make Array Join by Numbers:

    #Characters arrays are NULL, because we Join by Numbers arrays
    SELECT timestamp, numbers, characters FROM stream
    ARRAY JOIN numbers
    timeStampnumberscharacters
    1,1,NULL
    1,2,NULL
    1,3,NULL
    2,3,NULL
    2,4,NULL
    2,5,NULL
  4. Now, let's perform Array Join by both types of arrays:

    SELECT timestamp, numbers, characters FROM stream
    ARRAY JOIN numbers, characters
    timeStampnumberscharacters
    1,1,NULL
    1,2,NULL
    1,3,NULL
    2,3,NULL
    2,4,NULL
    2,5,NULL
    3,NULL,a
    3,NULL,b
    3,NULL,c
    4,NULL,e
    4,NULL,f
    4,NULL,g

Close to reality example 1:

#In this example we filtered Entries array by type to output just L2EntryNew type.
#We use Array Join to unfold Entries array and create a new field called Entry, where each element is Entries array element.
SELECT entry FROM binance2
ARRAY JOIN entries[THIS IS deltix.timebase.api.messages.universal.L2EntryNew] AS entry

Close to reality example 2:

#select Price and Size from Entries array from KrakenTradeEntry message type
#cast Entries array to KrakenTradeEntry type
#make array join by Price and Size
SELECT entry.price AS 'TradePrice', entry.size AS 'TradeSize'
FROM kraken
ARRAY JOIN (entries AS array(deltix.qsrv.hf.plugins.data.kraken.types.KrakenTradeEntry))[THIS IS NOT NULL] AS entry

Close to reality example 3:

#select BID Price/Size and ASK Price/Size from Entry object
#cast to L1entry object type
#array join to output BID Price/Size and ASK Price/Size as individual fields
SELECT
entry[side == ASK].price AS askPrice,
entry[side == ASK].size AS askSize,
entry[side == BID].price AS bidPrice,
entry[side == BID].size AS bidSize
FROM kraken
ARRAY JOIN (entries as array(deltix.timebase.api.messages.universal.L1entry))[THIS IS NOT NULL] AS entry

Using Array Join with Asterisk

You can use * in combination with aliases to make Array Join as a new field. In such cases a new fields with Join result is added to the existing set of fields.

#New field Char is added with Characters array elements as a result of a Join
SELECT * FROM stream
ARRAY JOIN characters AS char
timeStampnumberscharacterschar
3,NULL,[a,b,c],a
3,NULL,[a,b,c],b
3,NULL,[a,b,c],c
4,NULL,[e,f,g],e
4,NULL,[e,f,g],f
4,NULL,[e,f,g],g
#We Join Characters as Numbers and as a result Numbers type is changed to string.
SELECT * FROM stream
ARRAY JOIN characters AS numbers
timeStampnumberscharacters
3,a,[a,b,c]
3,b,[a,b,c]
3,c,[a,b,c]
4,e,[e,f,g]
4,f,[e,f,g]
4,g,[e,f,g]

Close to reality example:

In this example we unfold Entries array and create a new field called Joined_Field, where each entry is Entries array element.

Operations with Array Join

You can apply other, mentioned in this tutorial, operations in combinations with Array Joins.

#select and Joins just Price > 2000 as Entry from the Entries array
SELECT price FROM stream
ARRAY JOIN entries AS entry
WHERE entry.price > 2000

#select and Join Price as TradePrice just from L1Entry array type
SELECT price FROM stream
ARRAY JOIN entries.price AS TradePrice
WHERE entry IS L1Entry

#Join with selected fields from nested arrays. In this example we select just the Value field from the Attributes array and Join it as Attribute with the remaining message fields.
SELECT attributes.value FROM stream
ARRAY JOIN entries.attributes AS attribute

Join Multiple Arrays

You can use Array Join with more than one type of array.

Join multiple arrays
#use Array Join to unfold Entries polymorphic array and output various array types as separate fields
SELECT entry, num FROM packages
ARRAY JOIN entries AS entry, enumerate(entries) AS num

Left Array Join

You can use Left Array Join to display elements that are not joined. In this case, not joined elements are returned as is. Joined elements are joined the regular way.

Example for illustration purposes:

Let's consider the following stream.

timeStampnumberscharacters
1,[1,2,3],[]
2,[3,4,5],[]
3,[],[a,b,c]
4,[],[e,f,g]

Let's make Left Join by Characters. In this case Numbers are returned without changes and Characters are unfolded into 6 messages.

Using LEFT ARRAY JOIN
#Numbers array remain as is. Join is made by Character array elements.
SELECT timeStamp, numbers, characters FROM stream
LEFT ARRAY JOIN characters
timeStampnumberscharacters
1,[1,2,3],[]
2,[3,4,5],[]
3,NULL,a
3,NULL,b
3,NULL,c
4,NULL,e
4,NULL,f
4,NULL,g

Casts

Use AS keyword to convert selection to the target object type.

tip

Please note, that it is possible to use a keyword as with both aliases and casts. The following principle applies:

  1. 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.
  2. 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.
Casting examples
#select Info objects of a LimitOrderInfo type and convert output objects to LimitOrderInfo object type*/
SELECT order.info AS deltix.orders.LimitOrderInfo

#take a polymorphic object Entry and convert it to the fixed type object L1Entry
#query returns NULL in case conversion is not possible.
SELECT entry AS deltix.entries.L1Entry FROM packages
ARRAY JOIN entries AS entry

#in this case we convert Entry object to a polymorphic object type that includes objects deltix.entries.L1Entry and deltix.entries.L2Entry
#object keyword in this case allows us to specify a comma-separated list of objects.
SELECT entry AS object(deltix.entries.L1Entry, deltix.entries.L2Entry) FROM packages
ARRAY JOIN entries AS entry

In previous examples we used existing object types. For example deltix.entries.L1Entry and deltix.entries.L2Entry are existing object types in the Entry polymorphic object. Therefore, cast to any of them would reduce the source object. Nevertheless, you can cast to not existing object types and, thus, extend the source object.

Casting to a new object type
SELECT entry AS object(deltix.entries.L1Entry, deltix.entries.L2Entry, deltix.entries.TradeEntry) FROM packages
ARRAY JOIN entries AS entry

In this example query will return NULL for all deltix.entries.TradeEntry, because Entry object does not have this object type. Hence, fields with NULL values will have deltix.entries.TradeEntry object type.

Cast Arrays

It is as well possible to use cast with arrays. Use cats to create a fixed type and polymorphic arrays.

Using Casting with arrays
#takes a polymorphic array Entries and creates a fixed type array with only deltix.entries.TradeEntry type of array, returns NULL for other array types
SELECT entries AS array(deltix.entries.TradeEntry) FROM packages

#filter out NULL values
SELECT entries AS array(deltix.entries.TradeEntry)[THIS IS NOT NULL] FROM packages

#cast to a polymorphic array
SELECT entries AS array(deltix.entries.L1Entry, deltix.entries.L2Entry) FROM packages

#select Key fields as an array just from FixAttribute types of arrays and cast them to FixAttribute array type
SELECT (entries.attributes[THIS IS deltix.FixAttribute] AS array(deltix.FixAttribute).key FROM packages

#cast arrays of entries to L1entry format*/
SELECT
entry[side == ASK].price AS askPrice,
entry[side == ASK].size AS askSize,
entry[side == BID].price AS bidPrice,
entry[side == BID].size AS bidSize
FROM kraken
ARRAY JOIN (entries AS array(deltix.timebase.api.messages.universal.L1entry))[THIS IS NOT NULL] AS entry

#in this example we select Prices from Entries array and cast it to TradeEntry object type
SELECT (entries AS array(deltix.timebase.api.messages.universal.TradeEntry)).price AS Price FROM binance2
WHERE size(entries[THIS IS deltix.timebase.api.messages.universal.TradeEntry])>0

Cast Primitives

Use cast to convert primitives data types.

tip

Please note, that casting from higher to lower precision types causes data loss. For example cast from Int16 to Int8.

Casting primitive data types
SELECT byteField AS INT8, byteField AS INT16, byteField AS INT32, byteField AS INT64,
byteField AS DECIMAL, byteField AS FLOAT32, byteField AS FLOAT64, byteField AS CHAR, byteField AS BOOLEAN FROM alltypes

Additional Settings and Keywords

Keyword WITH

Use WITH keyword to declare aliases, variables, expressions. You can use the declared elements in the rest of the query.

Examples with a keyword WITH
WITH
entries AS array(deltix.timebase.api.messages.universal.L2EntryNew) AS 'l2',
max(l2[side == BID].price) AS 'maxBid',
min(l2[side == ASK].price) AS 'minAsk'
SELECT
max{}(maxBid) AS 'highBid',
min{}(minAsk) AS 'lowAsk'
FROM kraken
OVER time(1m)
WHERE packageType == PERIODICAL_SNAPSHOT AND symbol == 'BTCUSD'

Keyword TYPE

Use keyword TYPE to set the type name of the query result. Can be used to map the query output to the specific class type.

Examples with a keyword TYPE
SELECT
bbo[side == ASK].price AS "offerPrice",
bbo[side == ASK].size AS "offerSize",
bbo[side == BID].price AS "bidPrice",
bbo[side == BID].size AS "bidSize"
TYPE "deltix.timebase.api.messages.BestBidOfferMessage"
FROM kraken
ARRAY JOIN (entries AS array(deltix.timebase.api.messages.universal.L1entry))[THIS IS NOT NULL] AS bbo

Keyword THIS

Use keyword THIS to reference the current message as object.

Examples with a keyword THIS
#returns the entire message as object as a single field
SELECT THIS FROM packages

#returns entries array from the current message
SELECT THIS.entries FROM packages

Case and Special Characters

Identifiers in QQL are not case sensitive and get converted to upper case in case not embraced in double quotes. The following rule applies:

  • test = Test = TEST
  • "test" != "Test" != "TEST"

You do not have to embrace identifiers in quotes, but mind the following use cases:

  • use double quotes with identifier's name in case it includes special characters
  • use double quotes with identifier's name in case it starts with a numerical

QQL processor performs case-insensitive matching of fields, classes, streams.

QQL supports backslash quotation, such as \', \", \\, \n, \r, \t, \b, \f.

Example: s4'2 in QQL query write as 's4\'2'

Keywords LIMIT and OFFSET

LIMIT keyword sets the number of records returned by the query. For example, adding limit 10 to the query will return just 10 records. OFFSET keyword applies to LIMIT and sets the starting record number. For example, offset 100 added to the query will return all records starting from the 101th record (when counting from 1).

Follow these rules to add LIMIT and OFFSET to QQL queries:

  1. limit <limit count>
  2. limit <limit count> offset <offset count>
  3. limit <offset count>, <limit count>

As a use case example, a combination of LIMIT and OFFSET can be applied to creating pagination.

Examples of LIMIT and OFFSET keywords
SELECT RUNNING entries, count{}() FROM kraken
LIMIT 10 OFFSET 5

SELECT RUNNING entries, count{}() FROM kraken
LIMIT 10, 15
tip

Please note, that a query with the OFFSET will still read all the records on the server side and return just the records considering the specified offset.

UNION

UNION keyword is used to combining several queries together. As a result, you will get a dataset with fields returned by all included in the UNION queries chronologically arranged by their timestamps. UNION can be applied to querying from the same or different streams.

You will get a polymorphic dataset as a result of a UNION in case it combines messages of different classes.

Examples with UNION keyword
#UNION of these two queries will return a polymorphic dataset with fields of both classes TradeMessage and BestBidOfferMessage
SELECT
trade.price AS "price",
trade.size AS "size"
TYPE "TradeMessage"
FROM kraken
ARRAY JOIN entries[THIS IS KrakenTradeEntry] AS trade

UNION

SELECT
bbo[side == ASK].price AS "offerPrice",
bbo[side == ASK].size AS "offerSize",
bbo[side == BID].price AS "bidPrice",
bbo[side == BID].size AS "bidSize"
TYPE "BestBidOfferMessage"
FROM kraken
ARRAY JOIN (entries AS array(L1entry))[THIS IS NOT NULL] AS bbo
#UNION of these two queries will return a polymorphic dataset with fields of both classes Bar1min and Bar5min
WITH entries[THIS IS TradeEntry] AS 'entries'
SELECT
sum{}(sum(entries.size)) AS 'volume',
first{}(entries[0].price) AS 'open',
last{}(entries[-1].price) AS 'close',
max{}(max(entries.price)) AS 'high',
min{}(min(entries.price)) AS 'low'
TYPE "Bar1min"
FROM bitfinex
OVER TIME(1m)
WHERE symbol == 'BTCUSD'
AND notEmpty(entries)

UNION

WITH entries[THIS IS TradeEntry] AS 'entries'
SELECT
sum{}(sum(entries.size)) AS 'volume',
first{}(entries[0].price) AS 'open',
last{}(entries[-1].price) AS 'close',
max{}(max(entries.price)) AS 'high',
min{}(min(entries.price)) AS 'low'
TYPE "Bar5min"
FROM bitfinex
OVER TIME(5m)
WHERE symbol == 'BTCUSD'
AND notEmpty(entries)

In case UNION combines messages of the same class, you will get a fixed type dataset with fields from all the involved queries merged in one class. In case of identical column names, UNION will place results in one column chronologically.

tip

Please note, that it is not possible to UNION two fields with the same Name but different data types. For example, field Price of Integer and String data types cannot be combined by UNION in one field.

#UNION of these two queries will return a fixed type dataset with fields from both queries' results combined in one TradeAndBBO class.
SELECT
trade.price AS "price",
trade.size AS "size"
TYPE "TradeAndBBO"
FROM kraken
ARRAY JOIN entries[THIS IS KrakenTradeEntry] AS trade

UNION

SELECT
bbo[side == ASK].price AS "offerPrice",
bbo[side == ASK].size AS "offerSize",
bbo[side == BID].price AS "bidPrice",
bbo[side == BID].size AS "bidSize"
TYPE "TradeAndBBO"
FROM kraken
ARRAY JOIN (entries AS array(L1entry))[THIS IS NOT NULL] AS bbo

You can take messages of two different types, lets say type "TradeMessage" and type "BestBidOfferMessage" and UNION them under one common message type for example type "NewType". As the result of such a query, you will get a fixed type dataset with fields from both classes combined in one new class NewType.

GROUP BY

Use group by to aggregate data by a specific value(s). In case group by is included in the query, the output dataset will include just the last records aggregated by the specified value. In the below examples, we use aggregate function count() to get a number of records for each aggregated group for demonstration purposes.

tip

QQL supports group by by BOOLEAN, CHAR, DATETIME, TIMEOFDAY, INTEGER, VARCHAR, ENUM.

For example, let's group by by a specific object type:

Examples with GROUP BY keyword
#Output dataset includes the last records aggregated by symbol.
SELECT * FROM binance GROUP BY symbol

#aggregates the output by the entry.level
#count() aggregate function provides a records count for each aggregated group
SELECT entry.*, entry.level, count{}()
FROM binance
ARRAY JOIN entries AS entry
GROUP BY entry.level

You can group by by more than one value:

#Output dataset includes the last records aggregated by entry.level and symbol, count() aggregate function provides a records count for each aggregated group.
SELECT entry.*, entry.level, count{}()
FROM binance
ARRAY JOIN entries AS entry
GROUP BY entry.level, symbol

group by does not have to be performed by a specific column name, like in previous examples. You can group by a calculated value:

SELECT THIS.*, avg{}(totalQuantity) FROM infoA GROUP BY (infoIdA % 3)

There may be a case, depending on each server configurations, when there is not enough server memory to process all the created groups at once, when their count is very large. In this case, groups are cached on disk, which may cause a significant query performance downgrade. There is a TimeBase server java vm system property called maxGroupsCount which is set to one million by default: -DTimeBase.qql.maxGroupsCount=1000000. When the groups count reaches the maxGroupsCount value groups start to get cached on disk. On top of that, the system performs a server memory check before the caching procedure each time the groups count overpasses maxGroupsCount value by 5%. To release the server memory, query may be performed in several steps, processing maxGroupsCount in each step, which may cause delays in query processing. Please keep it in mind when creating your queries.

Operators

Operator .?

Queries that include arrays of Boolean values in combination with other select queries may not always output the result you have expected. Let's take a closer look at the following examples:

Let's consider the following polymorphic array ENTRIES with objects LIMIT and MARKET.

entries: [
Limit{price:10, size:1},
Market{size:2},
Limit{price:20, size:3}
]

In this example, let's create a QQL query that returns only objects with Price > 15, which is Limit{price:20, size:3}.

This query returns the desired output Limit{price:20, size:3}.

SELECT entries[price > 15]

This query returns the wrong output [Market{size:2}].

SELECT entries[entries.price > 15]

Let's take this query apart to explain:

This part of the query as it is will return just price values 10 and 20.

SELECT entries.price

If we put it like this, it will output an array of true/false values [false, true], based just on the previous query output, which was 10 and 20 (20 is greater than 15, this is why we are getting true for the second value).

SELECT entries.price > 15

If we put it like this, we are getting the unexpected result of [Market{size:2}], because it picks the second element in the entries array based on [false, true] output of the previous query.

SELECT entries[entries.price > 15] == SELECT entries[[false, true]]

We can rewrite the above queries using .? operator to output the desired value Limit{price:20, size:3}.

SELECT entries[entries.?price > 15]

Let's take this query apart to explain:

This query alone will return an array [10, null, 20]. We get NULL because the second entries array element [Market{size:2}] does not meet the condition.

SELECT entries.?price

If we rewrite it the following way it will return the array of true/false values: [false, false, true], based on the previous query output (20 is greater than 15, this is why we are getting true for the third value).

SELECT entries.?price > 15

This is why this query will return the third element in the entries array, which is Limit{price:20, size:3}, based on [false, false, true] from the previous query output.

SELECT entries[entries.?price > 15] == SELECT entries[[false, false, true]]

In this example we used .? operator. Queries with this operator return objects not meeting the specified criteria as NULL values. In our example it was Market{size:2}. This enables us getting a correct dataset in combination with select queries that return an array of Boolean values such as entries.?price > 15 in our example.

Basic

Below, there is a list of basic operations supported by QQL for simple objects and arrays. For arrays we use per-element calculations.

ExpressionExplanation
- xnegation
x + yaddition
x - ysubtraction
x * ymultiplication
x / ydivision
x % ymodulus
  • x % y - works only for integer types.
  • x / y - works as integer division in case of integer types. In case at least one of the operands is a float (decimal), works as a regular division.
#returns a new field Mult as a result of stream element multiplication
SELECT (entries.price * entries.size) AS Mult FROM packages

Comparison

Comparison operators. For arrays and constants we use per-element calculations.

ExpressionExplanationImplementationResult
x == yequalsimple types and arraysboolean, boolean array
x != ynot equalsimple types and arraysboolean, boolean array
x === ystrict equalsimple types and arraysboolean
x !== ystrict not equalsimple types and arraysboolean
x > ygreatersimple types and arraysboolean, boolean array
x < ylesssimple types and arraysboolean, boolean array
x >= ygreater or equalsimple types and arraysboolean, boolean array
x <= yless or equalsimple types and arraysboolean, boolean array
list1:[1,2,3] === list2[1,2,3] -> true
list1:[1,2,3] == list2:[1,2,3] -> [true, true, true]
list1:[1,2,3] === int1:3 -> false
list1:[1,2,3] == int1:3 -> [false, false, true]

#Check all Entries array Price elements against the condition. Return boolean array.
SELECT entries.price > 2000 FROM packages

IN and LIKE Operators

Use IN operator to check whether the query output matches the specified set of values.

Use LIKE operator to check whether String matches the defined template. Template may include special characters like % (any number of any symbols) and _ (just specific symbols)

#select all stream messages where symbol is either LTCUSD or BTCUSD
SELECT * FROM binance
WHERE symbol IN ('LTCUSD', 'BTCUSD')

SELECT * FROM binance
ARRAY JOIN entries AS entry
WHERE entry.exchangeId IN ('KRAKEN', 'GDAX')

#select all stream messages where symbol name starts with BTC..., for example BTCUSD
SELECT * FROM binance
WHERE symbol LIKE 'BTC%'

#will not return BTCUSDT
SELECT * FROM binance
WHERE symbol LIKE 'BTC___'

Logical

Logical operators. For arrays and constants we use per-element calculations. Consumes only booleans or arrays of booleans, produces only booleans or arrays of booleans.

ExpressionExplanationResult
x and yconjunctionboolean, boolean array
x or ydisjunctionboolean, boolean array
not xnegationboolean, boolean array

Aggregates and QQL Functions

Stateless Functions

Stateless Functions work with fields of one specific class/field.

Functions for Numerics

ExpressionExplanation
max(x,y)maximum
min(x,y)minimum
abs(x)absolute value
#Shell format

==> select max(1,23)

>_,TIMESTAMP,SYMBOL,TYPE,"MAX (1, 23)"
0,_,,CUSTOM,23

==> select abs(-10)

>_,TIMESTAMP,SYMBOL,TYPE,ABS (-10)
0,_,,CUSTOM,10

==> select float32(10.1123456789)

>_,TIMESTAMP,SYMBOL,TYPE,FLOAT32 (101123456789E-10)
0,_,,CUSTOM,10.112346

Functions for VARCHAR

Functions for VARCHAR datatype.

ExpressionExplanation
length(s)string length
uppercase(s)uppercase string
lowercase(s)lowercase string
reverse(s)reverse string
indexof(x, y)find y in x, return index
substr(x, start, end)substring
#Shell format

==> select length('hello')

>_,TIMESTAMP,SYMBOL,TYPE,LENGTH (hello)
0,_,,CUSTOM,5

==> select uppercase('hello')

>_,TIMESTAMP,SYMBOL,TYPE,UPPERCASE (hello)
0,_,,CUSTOM,HELLO

==> select lowercase('HeLlo')

>_,TIMESTAMP,SYMBOL,TYPE,LOWERCASE (HeLlo)
0,_,,CUSTOM,hello

==> select reversed('hello')

>_,TIMESTAMP,SYMBOL,TYPE,REVERSED (hello)
0,_,,CUSTOM,olleh

==> select indexof('h', 'hello')

>_,TIMESTAMP,SYMBOL,TYPE,"INDEXOF (h, hello)"
0,_,,CUSTOM,-1

==> select substr('Hello, World!', 7, 14)

>_,TIMESTAMP,SYMBOL,TYPE,"SUBSTR (Hello, World!, 7, 14)"
0,_,,CUSTOM,World!
SELECT length(entry.exchangeid) AS length
FROM bittrex
ARRAY JOIN entries IN entry

Functions for Arrays

ExpressionExplanation
empty(arr)is array empty
notempty(arr)is array not empty
size(arr)array size
max(arr)array maximum
min(arr)array minimum
mean(arr)array mean
sum(arr)array sum
enumerate(arr)array indices
sort(arr)array sort
indexof(arr, el)find index of element
any(arr)if any element is true
all(arr)if all elements are true
Examples with functions for arrays
SELECT
sum(entries.price + entries.size) AS SUM
FROM bittrex

SELECT
avg(entries.price) AS AVG
FROM bittrex

SELECT sort(entries.price) FROM bittrex

SELECT size(entries.price) FROM bittrex

SELECT
ANY(entries.price > 200)
FROM bittrex

SELECT * FROM bitfinex WHERE notEmpty(entries[THIS IS deltix.timebase.api.messages.universal.TradeEntry])

Stateful Functions

Keywords

Time template:

SELECT [RUNNING] 
function{arg1: value1, arg2: value2}(arg1, arg2)
FROM stream [TRIGGER/RESET] OVER [EVERY] TIME(5m)

Count template:

SELECT [RUNNING] 
function{arg1: value1, arg2: value2}(arg1, arg2)
FROM stream [TRIGGER] OVER COUNT(100)
  • RUNNING - the result is returned for every input message. For example we compute running max, it means, that for each message we receive max for this message and all previous messages in a group.
  • OVER [EVERY] TIME(5m) - computes functions separately for each (5 minutes) interval and delivers updates at the end of every (5 minutes) interval. If we add EVERY, we expect empty result for empty 5m intervals when no messages were published.
  • TRIGGER OVER [EVERY] TIME(5m) - receive function results every 5 minutes and function is computed over the entire stream. If we add EVERY, we expect update for empty 5m intervals when no messages were published.
  • OVER COUNT(100) - compute function separately for each 100 messages group and receive update every 100 messages.
  • TRIGGER OVER COUNT(100) - compute function over the entire stream, but receive updates every 100 messages.
  • RESET - is used to reset function based on provided conditions. For example, reset running calculation for each time period instead of carrying on with the cumulative counting.

Functions Syntax

function{initArg1: value1, initArg2: value2, ...}(argValue1, argValue2)

OR

function{value1, value2, ...}(argValue1, argValue2, ...)

Init arguments could be passed to function like named args and also like position arguments in {...} braces. Init arguments are constant, so you cannot pass here selectors or anything similar. Arguments are passed to function in (...) parentheses as positional arguments.

Examples

messagemax(field)
indextimestampfield
12021-03-19T08:07:31.373623
22021-03-19T08:07:32.48736
32021-03-19T08:07:32.683237
42021-03-19T08:07:32.9853467
52021-03-19T08:07:33.18597
62021-03-19T08:07:33.2002374
72021-03-19T08:07:33.37539
82021-03-19T08:07:33.687-49
92021-03-19T08:07:34.100347
102021-03-19T08:07:36.101-347
112021-03-19T08:07:37.1024737
122021-03-19T08:07:38.103499
132021-03-19T08:07:41.1047
indextimestampmax
1
2
3
4
5
6
7
8
9
10
11
12
132021-03-19T08:07:41.1044737
messagemax(field) over every time(1m)
indextimestampfield
12021-03-19T08:07:31.373623
22021-03-19T08:07:32.48736
32021-03-19T08:07:32.683237
42021-03-19T08:07:32.9853467
52021-03-19T08:07:33.18597
62021-03-19T08:07:33.2002374
72021-03-19T08:07:33.37539
82021-03-19T08:07:33.687-49
92021-03-19T08:07:34.100347
102021-03-19T08:07:36.101-347
112021-03-19T08:07:37.1024737
122021-03-19T08:07:38.103499
132021-03-19T08:07:41.1047
timestampfield
2021-03-19T08:07:32623
2021-03-19T08:07:333467
2021-03-19T08:07:342374
2021-03-19T08:07:35347
2021-03-19T08:07:36NULL
2021-03-19T08:07:37-347
2021-03-19T08:07:384737
2021-03-19T08:07:39NULL
2021-03-19T08:07:40NULL
2021-03-19T08:07:41NULL
2021-03-19T08:07:427
#max price for the entire stream
SELECT
max{} (max(entries.price)) AS MAX
FROM bittrex

#max for every hour time period
SELECT
max{} (max(entries.price)) AS MAX
FROM bittrex
OVER TIME (1h)

#max from N prior messages for each message*/
SELECT
RUNNING
max{} (max(entries.price)) AS MAX
FROM bittrex

#incremental update for every message and MAX for the time interval
SELECT RUNNING
max{} (max(entries.price)) AS MAX
FROM bittrex
OVER TIME (10m)

#max value from prior 10 messages for each interval
SELECT
max{} (max(entries.price)) AS MAX
FROM binance2
OVER COUNT (10)

#max from N prior messages for every interval
SELECT
max{} (max(entries.price)) AS MAX
FROM binance2
TRIGGER OVER COUNT (10)

#every 10 min returns max for prior N messages
SELECT
max{} (max(entries.price)) AS MAX
FROM bittrex
TRIGGER OVER TIME (10m)

#returns max for every message, resets every 30 min but does not return a snapshot
SELECT RUNNING
max{} (max(entries.price)) AS MAX
FROM bittrex
RESET OVER TIME (30m)

#incremental update for every message and a snapshot for 10 messages
SELECT RUNNING
max{} (max(entries.price)) AS MAX
FROM binance2
OVER COUNT (10)

#select BID and ASK price FROM L2EntryNew
#cast entries array to L2EntryNew type
#return max for every selection every 1 min
#filter by packageType and symbol
WITH (entries AS array(deltix.timebase.api.messages.universal.L2EntryNew)) AS 'entries',
entries[side == BID].price AS 'bidPrices',
entries[side == ASK].price AS 'askPrices'
SELECT
entries,
max{}(max(askPrices)) AS 'highAsk',
max{}(max(bidPrices)) AS 'highBid',
min{}(min(askPrices)) AS 'lowAsk',
min{}(min(bidPrices)) AS 'lowBid'
FROM bitfinex
OVER TIME(1m)
WHERE packageType == PERIODICAL_SNAPSHOT
AND symbol == 'BTCUSD'

#one-minute bars based on trades
WITH entries[THIS IS deltix.timebase.api.messages.universal.TradeEntry] AS 'entries'
SELECT
sum{}(sum(entries.size)) AS 'volume',
first{}(entries[0].price) AS 'open',
last{}(entries[-1].price) AS 'close',
max{}(max(entries.price)) AS 'high',
min{}(min(entries.price)) AS 'low'
FROM bitfinex
OVER TIME(1m)
WHERE symbol == 'BTCUSD'
AND size(entries) > 0

#different price indicators
WITH entries[THIS IS deltix.timebase.api.messages.universal.TradeEntry].price AS 'prices'
SELECT
sma{timePeriod: 1h}(price) AS 'sma',
(bollinger{timeWindow: 1h}(price) AS 'bollinger').*,
cma{}(price) AS 'cma'
FROM bitfinex
ARRAY JOIN prices AS 'price'
OVER TIME(10m)
WHERE symbol == 'BTCUSD'
AND size(entries) > 0

#simple moving average for Prices for 1 min time period returned every 1 hour
SELECT
sma{timePeriod: 1m}(max(entries.price))
FROM bittrex
TRIGGER OVER TIME(1h)

# counts the number of unique FX instruments in the securities stream
SELECT size(collect_unique{}(symbol)) FROM "securities"
where type == "deltix.timebase.api.messages.InstrumentType":FX

List of Functions

IDInit argsArgsReturnsDescription
COUNTINT64counts messages
MAXBOOLEAN?, INT8?, INT16?, INT32?, INT64?, FLOAT32?, FLOAT64?, DECIMAL64?, TIMESTAMP?, TIMEOFDAY?, VARCHAR?BOOLEAN?, INT8?, INT16?, INT32?, INT64?, FLOAT32?, FLOAT64?, DECIMAL64?, TIMESTAMP?, TIMEOFDAY?, VARCHAR?computes maximum value
MAXtimePeriod: INT64FLOAT64?FLOAT64?computes maximum over time window with given timePeriod
MAXperiod: INT64FLOAT64?computes maximum over count window with given period
MINBOOLEAN?, INT8?, INT16?, INT32?, INT64?, FLOAT32?, FLOAT64?, DECIMAL64?, TIMESTAMP?, TIMEOFDAY?, VARCHAR?BOOLEAN?, INT8?, INT16?, INT32?, INT64?, FLOAT32?, FLOAT64?, DECIMAL64?, TIMESTAMP?, TIMEOFDAY?, VARCHAR?computes minimum value
MINtimePeriod: INT64FLOAT64?FLOAT64?computes minimum over time window with given timePeriod
MINperiod: INT64FLOAT64?FLOAT64?computes minimum over count window with given period
SUMINT8?, INT16?, INT32?, INT64?, DECIMAL64?DECIMAL64?computes sum of given values
SUMFLOAT32?, FLOAT64?FLOAT32?, FLOAT64?computes sum of given values
SUMtimePeriod: INT64DECIMAL64?DECIMAL64?computes sum of values in time window with given time period
SUMperiod: INT32DECIMAL64?DECIMAL64?computes sum of values in count window with given period
AVGINT8?, INT16?, INT32?, INT64?, DECIMAL64?DECIMAL64?computes avg of given values
AVGFLOAT32?, FLOAT64?FLOAT32?, FLOAT64?computes avg of given values
SMAtimePeriod: INT64FLOAT64?FLOAT64?computes moving average over time window with given timePeriod
SMAperiod: INT64FLOAT64?FLOAT64?computes moving average over count window with given period
CMAFLOAT64?FLOAT64?computes cumulative moving average
EMAperiod: INT32FLOAT64?FLOAT64?computes exponential moving average with given period
EMAfactor: FLOAT64FLOAT64?FLOAT64?computes exponential moving average with given factor
COLLECT_UNIQUEVARCHAR?ARRAYS OF VARCHARScollects and returns an array of unique strings

DDL

caution

This document is for TimeBase Community Edition and TimeBase Enterprise Edition 5.5

TimeBase Data Definition Language (DDL) allows to create, drop, and modify streams in TimeBase by issuing QQL statements, similar to CREATE TABLE ... in SQL. In QQL the equivalent statement begins with CREATE ... STREAM, and, thus, is called the Create Stream statement.

When you create a stream, you can describe it's properties in stream OPTIONS.

  • FIXEDTYPE (boolean) - a stream capable of containing messages of a single specified type.
  • POLYMORPHIC (boolean) - a polymorphic stream is capable of containing messages of several specified types.
  • LOSSLESS (boolean) - lossless stream. Durable streams are always lossless.
  • LOSSY (boolean) - lossy stream.
  • HIGHAVAILABILITY (boolean) - high availability durable streams are cached on startup.
  • PERIODICITY (varchar) - indicate a known stream periodicity.
  • DF (numeric) - distribution factor value.
  • INITSIZE (numeric) - initial size of the write buffer in bytes.
  • MAXSIZE (numeric) - the limit on buffer growth in bytes. Default is 64K.
  • MAXTIME (numeric) - the limit on buffer growth as difference between first and last message time. Default is Long.MAX_VALUE.
CREATE TRANSIENT|DURABLE STREAM stream_name [title]
(class_expr|enum_expr [; ...])
[OPTIONS (identifier [= expr] [; ...])]
[COMMENT comment]
MODIFY STREAM stream_name [title]
(class_expr|enum_expr [; ...])
[OPTIONS (identifier [= expr] [; ...])]
[COMMENT comment]
[CONFIRM NO_CONVERSION|CONVERT_DATA|DROP_ATTRIBUTES|DROP_TYPES|DROP_DATA]
DROP STREAM stream_name

where classes and enumerations are described as follows:

CLASS type_name [title] [UNDER type_name]
(static_attribute|attribute [, ...])
[AUXILIARY|NOT AUXILIARY]
[INSTANTIABLE|NOT INSTANTIABLE]
[COMMENT comment]
ENUM enum_name [title]
(identifier [= expr] [, ...])
[FLAGS]
[COMMENT comment]

and where static and non-statics attributes are determined as follows:

STATIC identifier [title] type [NOT NULL] [encoding] [BETWEEN min_expr AND max_expr] = expr
[TAGS (identifier:expr [, ...])]
[COMMENT comment]
identifier [title] type [NOT NULL] [encoding] [BETWEEN min_expr AND max_expr] [RELATIVE TO identifier] [DEFAULT expr]
[TAGS (identifier:expr [, ...])]
[COMMENT comment]
info

Refer to QQL Introduction for general overview.

Describe Stream

Run desc to get stream details. You can use it to reverse-engineer an existing stream back into a QQL statement that creates it. This is called describing a stream.

Coinbase stream schema example
//example of a coinbase stream

DURABLE STREAM "coinbase" (
CLASS "deltix.timebase.api.messages.MarketMessage" (
"currencyCode" INTEGER SIGNED (16) COMMENT,
"originalTimestamp" TIMESTAMP COMMENT,
"sequenceNumber" INTEGER COMMENT,
"sourceId" VARCHAR ALPHANUMERIC (10) COMMENT
) AUXILIARY;

ENUM "deltix.timebase.api.messages.universal.PackageType" (
"VENDOR_SNAPSHOT" = 0,
"PERIODICAL_SNAPSHOT" = 1,
"INCREMENTAL_UPDATE" = 2
);

CLASS "deltix.timebase.api.messages.universal.PackageHeader" UNDER "deltix.timebase.api.messages.MarketMessage" (
"packageType" "deltix.timebase.api.messages.universal.PackageType" NOT NULL COMMENT
) AUXILIARY;

CLASS "deltix.timebase.api.messages.universal.BaseEntry" (
"contractId" VARCHAR ALPHANUMERIC (10) COMMENT,
"exchangeId" VARCHAR ALPHANUMERIC (10) COMMENT,
"isImplied" BOOLEAN COMMENT
) AUXILIARY;

CLASS "deltix.timebase.api.messages.universal.BasePriceEntry" UNDER "deltix.timebase.api.messages.universal.BaseEntry" (
"numberOfOrders" INTEGER COMMENT,
"participantId" VARCHAR COMMENT,
"price" FLOAT DECIMAL64 COMMENT,
"quoteId" VARCHAR COMMENT,
"size" FLOAT DECIMAL64 COMMENT
) AUXILIARY;

ENUM "deltix.timebase.api.messages.QuoteSide" ("BID" = 0, "ASK" = 1);

CLASS "deltix.timebase.api.messages.universal.L1Entry" UNDER "deltix.timebase.api.messages.universal.BasePriceEntry" (
"isNational" BOOLEAN COMMENT,
"side" "deltix.timebase.api.messages.QuoteSide" NOT NULL COMMENT
) AUXILIARY;

CLASS "deltix.timebase.api.messages.universal.L2EntryNew" UNDER "deltix.timebase.api.messages.universal.BasePriceEntry" (
"level" INTEGER NOT NULL SIGNED (16) COMMENT,
"side" "deltix.timebase.api.messages.QuoteSide" NOT NULL COMMENT
) AUXILIARY;

ENUM "deltix.timebase.api.messages.BookUpdateAction" ("INSERT" = 0, "UPDATE" = 1, "DELETE" = 2);

CLASS "deltix.timebase.api.messages.universal.L2EntryUpdate" UNDER "deltix.timebase.api.messages.universal.BasePriceEntry" (
"action" "deltix.timebase.api.messages.BookUpdateAction" NOT NULL COMMENT,
"level" INTEGER NOT NULL SIGNED (16) COMMENT,
"side" "deltix.timebase.api.messages.QuoteSide" COMMENT
) AUXILIARY;

ENUM "deltix.timebase.api.messages.AggressorSide" ("BUY" = 0, "SELL" = 1);

ENUM "deltix.timebase.api.messages.TradeType" (
"REGULAR_TRADE" = 0,
"AUCTION_CLEARING_PRICE" = 1,
"CORRECTION" = 2,
"CANCELLATION" = 3,
"UNKNOWN" = 4
);

CLASS "deltix.timebase.api.messages.universal.TradeEntry" UNDER "deltix.timebase.api.messages.universal.BaseEntry" (
"buyerNumberOfOrders" INTEGER COMMENT,
"buyerOrderId" VARCHAR COMMENT,
"buyerParticipantId" VARCHAR COMMENT,
"condition" VARCHAR COMMENT,
"matchId" VARCHAR COMMENT,
"price" FLOAT DECIMAL64 COMMENT,
"sellerNumberOfOrders" INTEGER COMMENT,
"sellerOrderId" VARCHAR COMMENT,
"sellerParticipantId" VARCHAR COMMENT,
"side" "deltix.timebase.api.messages.AggressorSide" COMMENT,
"size" FLOAT DECIMAL64 COMMENT,
"tradeType" "deltix.timebase.api.messages.TradeType" COMMENT
) AUXILIARY;

ENUM "deltix.timebase.api.messages.DataModelType" (
"LEVEL_ONE" = 0,
"LEVEL_TWO" = 1,
"LEVEL_THREE" = 2,
"MAX" = 3
);

CLASS "deltix.timebase.api.messages.universal.BookResetEntry" UNDER "deltix.timebase.api.messages.universal.BaseEntry" (
"modelType" "deltix.timebase.api.messages.DataModelType" NOT NULL COMMENT,
"side" "deltix.timebase.api.messages.QuoteSide" COMMENT
) AUXILIARY;

CLASS "deltix.qsrv.hf.plugins.data.coinbase.types.CoinbasePackageHeader" UNDER "deltix.timebase.api.messages.universal.PackageHeader" (
"entries" ARRAY(
OBJECT(
"deltix.timebase.api.messages.universal.L1Entry",
"deltix.timebase.api.messages.universal.L2EntryNew",
"deltix.timebase.api.messages.universal.L2EntryUpdate",
"deltix.timebase.api.messages.universal.TradeEntry",
"deltix.timebase.api.messages.universal.BookResetEntry"
) NOT NULL
) NOT NULL
);

ENUM "deltix.timebase.api.messages.service.DataConnectorStatus" (
"INITIAL" = 0,
"CONNECTED_BY_USER" = 1,
"AUTOMATICALLY_RESTORED" = 2,
"DISCONNECTED_BY_USER" = 3,
"DISCONNECTED_BY_COMPLETED_BATCH" = 4,
"DISCONNECTED_BY_VENDOR_AND_RECONNECTING" = 5,
"DISCONNECTED_BY_VENDOR_AND_HALTED" = 6,
"DISCONNECTED_BY_ERROR_AND_RECONNECTING" = 7,
"DISCONNECTED_BY_ERROR_AND_HALTED" = 8,
"RECOVERING_BEGIN" = 9,
"LIVE_BEGIN" = 10
);

CLASS "deltix.timebase.api.messages.service.ConnectionStatusChangeMessage" (
"cause" VARCHAR COMMENT,
"status" "deltix.timebase.api.messages.service.DataConnectorStatus" COMMENT
);

ENUM "deltix.timebase.api.messages.status.SecurityStatus" (
"FEED_CONNECTED" = 0,
"FEED_DISCONNECTED" = 1,
"TRADING_STARTED" = 2,
"TRADING_STOPPED" = 3
);

CLASS "deltix.timebase.api.messages.status.SecurityStatusMessage" UNDER "deltix.timebase.api.messages.MarketMessage" (
"cause" VARCHAR COMMENT,
"exchangeId" VARCHAR ALPHANUMERIC (10) COMMENT,
"originalStatus" VARCHAR COMMENT ,
"status" "deltix.timebase.api.messages.status.SecurityStatus" COMMENT
);

) OPTIONS (
POLYMORPHIC;

PERIODICITY = 'IRREGULAR';

HIGHAVAILABILITY = FALSE
) COMMENT 'coinbase'
info

Refer to TimeBase Shell instruction.

Create Stream

You can use the desc output to create a stream. Simply append a keyword CREATE in front of whatever the desc command has printed out.

tip

It is not allowed to create a stream with a duplicate key (or name), so if you want to try and create a stream just like tickquerydemo, you have to change the key, as well.

CREATE DURABLE STREAM TEST (
CLASS "deltix.timebase.api.messages.BestBidOfferMessage" 'Quote Message' (
"offerPrice" 'Offer Price' FLOAT DECIMAL (2),
"offerSize" 'Offer Size' FLOAT DECIMAL (0),
"bidPrice" 'Bid Price' FLOAT DECIMAL (2) RELATIVE TO "offerPrice",
"bidSize" 'Bid Size' FLOAT DECIMAL (0)
);
CLASS "deltix.timebase.api.messages.TradeMessage" 'Trade Message' (
"price" 'Trade Price' FLOAT DECIMAL (2),
"size" 'Trade Size' FLOAT DECIMAL (0)
);
)
OPTIONS (DF = 1; HIGHAVAILABILITY = FALSE)
COMMENT 'QQL is awesome'
/

When you begin entering the create command into tickdb shell, it recognizes the keyword and enters the multi-line input mode. To finish entering a multi-line command, you type a single forward slash on a separate line.

The system response looks just like the result of a SELECT statement. This is because any statement in QQL returns a message stream, including data definition statements such as CREATE STREAM.

Also note that the message type returned is deltix.timebase.api.messages.ErrorMessage. This does not mean there has been an error! When the message type is SUCCESS, it means that the activity has been completed successfully. You can just read the message text to make sure everything is alright.

tip

Remember, that if you don't enclose the new stream key in double quotes, then it will be converted to upper case, no matter how you specified it. For example, even if you start the command with CREATE DURABLE STREAM test, the key of the newly created stream will still be TEST. That is why the desc command encloses identifiers in double quotes, unless they only contain capital letters.

Modify Stream

To modify a stream, simply append the keyword MODIFY in front of whatever the desc command has printed out. Specify all classes that will be present in a stream.

MODIFY STREAM TEST (
CLASS "deltix.timebase.api.messages.BestBidOfferMessage" 'Quote Message' (
"offerPrice" 'Offer Price' FLOAT DECIMAL (2),
"offerSize" 'Offer Size' FLOAT DECIMAL (0),
"offerExchange" 'Offer Exchange' VARCHAR ALPHANUMERIC (10),
"bidPrice" 'Bid Price' FLOAT DECIMAL (2) RELATIVE TO "offerPrice",
"bidSize" 'Bid Size' FLOAT DECIMAL (0),
"bidExchange" 'Bid Exchange' VARCHAR ALPHANUMERIC (10)
);
CLASS "deltix.timebase.api.messages.TradeMessage" 'Trade Message' (
"price" 'Trade Price' FLOAT DECIMAL (2),
"size" 'Trade Size' FLOAT DECIMAL (0)
);
)
OPTIONS (DF = 1; HIGHAVAILABILITY = FALSE)
COMMENT 'Modified stream' CONFIRM DROP_DATA
/

Drop Stream

Issue a drop stream statement to remove a stream.

DROP STREAM test
tip

Drop command is single-line by default, so forward slash is not required. Also you will notice that the capitalization of the stream key is not important. That is because in all cases QQL searches for existing identifiers in a case-insensitive manner, as long as they are unambiguous.