Skip to main content

QQL 5.4

caution

This document is for TimeBase Enterprise Edition 5.4 and earlier.

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.

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

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.

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

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

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

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.

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

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 ENTITY

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 ENTITY 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 ENTITY

>_,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

Without the GROUP BY ENTITY clause, there is only one Query State object for all symbols. When the GROUP BY ENTITY 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 ENTITY 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.

Int8Int16Int32Int64Decimal64Float32Float64
Int8Int32Int32Int32Int64Decimal64Float32Float64
Int16Int32Int32Int32Int64Decimal64Float32Float64
Int32Int32Int32Int32Int64Decimal64Float32Float64
Int64Int64Int64Int64Int64Decimal64Float32Float64
Decimal64Decimal64Decimal64Decimal64Decimal64Decimal64Float32Float64
Float32Float32Float32Float32Float32Float32Float32Float64
Float64Float64Float64Float64Float64Float64Float64Float64

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

  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.

Varchar

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

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

Char

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

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

Boolean

Examples: true, false.

Timestamp

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

Additional Settings and Keywords

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'

Operators

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.

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]

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

Stateful Functions

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

RUNNING

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.

SELECT [RUNNING] 
function(arg1, arg2)
FROM stream

DDL

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.

DURABLE STREAM TEST (
CLASS "deltix.qsrv.hf.pub.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.qsrv.hf.pub.TradeMessage" 'Trade Message' (
"price" 'Trade Price' FLOAT DECIMAL (2),
"size" 'Trade Size' FLOAT DECIMAL (0)
);
)
OPTIONS (DF = 1; HIGHAVAILABILITY = FALSE)
COMMENT 'tickquerydemo'
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.