TimeBase Query Language user guide and tutorials

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 db dxtick://localhost:8011
==> open

4.Download demo streams and import them to TimeBase:

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

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

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

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

oper 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.

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

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*/
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

Using TimeBase Web Admin

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

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.


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

/*Shell Format*/
select * from tickquerydemo

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

/*select first message from stream*/
select first (*) from binance 

/*select last message from stream*/
select last (*) from binance 

/*select stream messages in the reversed order*/
select * from reverse(binance)

Select Object Fields

Use select command in combination with the name of specific fields to query specific fields from objects. Enter a comma-separated list of fields to query more than one field.

Template: select field_name from stream_name

/*Shell Format*/
select price, bidPrice, offerPrice from tickquerydemo

>_,TIMESTAMP,SYMBOL,TYPE,price,bidPrice,offerPrice
0,2011-10-17 17:21:40,GREATCO,EQUITY,_,42.5,43.5
1,2011-10-17 17:21:40,XBANK,EQUITY,_,301.25,301.75
2,2011-10-17 17:21:41,XBANK,EQUITY,301.25,_,_
3,2011-10-17 17:21:42,XBANK,EQUITY,_,298.5,301.5
4,2011-10-17 17:21:43,GREATCO,EQUITY,_,43.0,45.0
5,2011-10-17 17:21:43,XBANK,EQUITY,_,295.0,299.5
6,2011-10-17 17:21:44,GREATCO,EQUITY,44.0,_,_
!end

You can use object_name.* query format to unfold all object fields into separate columns, where column names have object_name.field_name format.

select entry.* from binance

select order.* from orders

/*select from nested objects*/
select order.execution.info.* from orders

Select from Nested Objects

TimeBase objects may include one or more level of embedded objects or properties. Use the following query format to select nested properties from objects.

Template: select object.nested property from stream_name

object.nested property is a path in the object tree.

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

Working with Enums

QQL allows placing queries considering specific Enum values.


/*select info objects where side enum value is BUY*/
select order from orders
where order.info.side == BUY

/*Select Entries arrays where packageType enum value is VENDOR_SNAPSHOT*/
select entries from packages
where packageType == VENDOR_SNAPSHOT

Use Column Aliases

In QQL you can use aliases similar to SQL to have control over the returned field names. Then, you can reference the object by the alias name in your query. After AS you can use both identifiers and string literals. Embrace your entry in a single quotes to make it a string literal. This approach is considered a best practice.

Template: select field_name as 'custom_field_name' from stream_name

/*Shell Format - using string literal as alias name*/
==> 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

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.

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

/*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.

/*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.
  • 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 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.

/*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.

/*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.

/*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.


==> 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:

/*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 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 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 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.

  Int8 Int16 Int32 Int64 Decimal64 Float32 Float64
Int8 Int32 Int32 Int32 Int64 Decimal64 Float32 Float64
Int16 Int32 Int32 Int32 Int64 Decimal64 Float32 Float64
Int32 Int32 Int32 Int32 Int64 Decimal64 Float32 Float64
Int64 Int64 Int64 Int64 Int64 Decimal64 Float32 Float64
Decimal64 Decimal64 Decimal64 Decimal64 Decimal64 Decimal64 Float32 Float64
Float32 Float32 Float32 Float32 Float32 Float32 Float32 Float64
Float64 Float64 Float64 Float64 Float64 Float64 Float64 Float64

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
/*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.

  True False
True True True
False True False

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 Character Display
\' Single quotation
\" Double quotation
\\ Backslash
\t Tab
\b Backspace
\r Carriage return
\f Formfeed
\n New line

Char Constants and Arrays

  1. Char. Examples: '\''c, 'a'c, '1'c.
  2. Char arrays. Examples: ['\''c, ‘a’c, '1'c]
Special Character Display
\' Single quotation
\" Double quotation
\\ Backslash
\t Tab
\b Backspace
\r Carriage return
\f Formfeed
\n New 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.

/*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

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

/*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

/*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

/*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.

/*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

/*filter 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.

/*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

/*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

/*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

/*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

/* 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.

/*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:

timeStamp numbers characters
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
timeStamp numbers characters
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
timeStamp numbers characters
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
timeStamp numbers characters
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
timeStamp numbers characters
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.

Example for illustration purposes:

/*New field Char is added with Characters array elements as a result of a Join*/
select * from stream
array join characters as char
timeStamp numbers characters char
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
timeStamp numbers characters
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.

/*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.

timeStamp numbers characters
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.

/*Numbers array remain as is. Join is made by Character array elements.*/
select timeStamp, numbers, characters from stream
left array join characters
timeStamp numbers characters
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.


/*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.

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.

/*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.

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.

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.

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.


/*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.

select running entries, count{}() from kraken
limit 10 offset 5

select running entries, count{}() from kraken
limit 10, 15

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.

/*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.

/*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.

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

/*Output datset 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 datset 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.

Expression Explanation
- x negation
x + y addition
x - y subtraction
x * y multiplication
x / y division
x % y modulus
  • 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.

Expression Explanation Implementation Result
x == y equal simple types and arrays boolean, boolean array
x != y not equal simple types and arrays boolean, boolean array
x === y strict equal simple types and arrays boolean
x !== y strict not equal simple types and arrays boolean
x > y greater simple types and arrays boolean, boolean array
x < y less simple types and arrays boolean, boolean array
x >= y greater or equal simple types and arrays boolean, boolean array
x <= y less or equal simple types and arrays boolean, 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.

Expression Explanation Result
x and y conjunction boolean, boolean array
x or y disjunction boolean, boolean array
not x negation boolean, boolean array

Aggregates and QQL Functions

Stateless Functions

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

Functions for Numerics

Expression Explanation
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.

Expression Explanation
length(s) string length
uppercase(s) uppercase string
lowercase(s) lowercase string
reverse(s) reverse string
indexof(x, y) find x in y, 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 as entry

Functions for Arrays

Expression Explanation
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

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

message max{}(field)
index timestamp field
1 2021-03-19T08:07:31.373 623
2 2021-03-19T08:07:32.487 36
3 2021-03-19T08:07:32.683 237
4 2021-03-19T08:07:32.985 3467
5 2021-03-19T08:07:33.185 97
6 2021-03-19T08:07:33.200 2374
7 2021-03-19T08:07:33.375 39
8 2021-03-19T08:07:33.687 -49
9 2021-03-19T08:07:34.100 347
10 2021-03-19T08:07:36.101 -347
11 2021-03-19T08:07:37.102 4737
12 2021-03-19T08:07:38.103 499
13 2021-03-19T08:07:41.104 7
index timestamp max
1
2
3
4
5
6
7
8
9
10
11
12
13 2021-03-19T08:07:41.104 4737
message running max{}(field)
index timestamp field
1 2021-03-19T08:07:31.373 623
2 2021-03-19T08:07:32.487 36
3 2021-03-19T08:07:32.683 237
4 2021-03-19T08:07:32.985 3467
5 2021-03-19T08:07:33.185 97
6 2021-03-19T08:07:33.200 2374
7 2021-03-19T08:07:33.375 39
8 2021-03-19T08:07:33.687 -49
9 2021-03-19T08:07:34.100 347
10 2021-03-19T08:07:36.101 -347
11 2021-03-19T08:07:37.102 4737
12 2021-03-19T08:07:38.103 499
13 2021-03-19T08:07:41.104 7
index timestamp field
1 2021-03-19T08:07:31.373 623
2 2021-03-19T08:07:32.487 623
3 2021-03-19T08:07:32.683 623
4 2021-03-19T08:07:32.985 3467
5 2021-03-19T08:07:33.185 3467
6 2021-03-19T08:07:33.200 3467
7 2021-03-19T08:07:33.375 3467
8 2021-03-19T08:07:33.687 3467
9 2021-03-19T08:07:34.100 3467
10 2021-03-19T08:07:36.101 3467
11 2021-03-19T08:07:37.102 4737
12 2021-03-19T08:07:38.103 4737
13 2021-03-19T08:07:41.104 4737
message max{}(field) over count(5)
index timestamp field
1 2021-03-19T08:07:31.373 623
2 2021-03-19T08:07:32.487 36
3 2021-03-19T08:07:32.683 237
4 2021-03-19T08:07:32.985 3467
5 2021-03-19T08:07:33.185 97
6 2021-03-19T08:07:33.200 2374
7 2021-03-19T08:07:33.375 39
8 2021-03-19T08:07:33.687 -49
9 2021-03-19T08:07:34.100 347
10 2021-03-19T08:07:36.101 -347
11 2021-03-19T08:07:37.102 4737
12 2021-03-19T08:07:38.103 499
13 2021-03-19T08:07:41.104 7
index timestamp field
1
2
3
4
5 2021-03-19T08:07:33.185 3467
6
7
8
9
10 2021-03-19T08:07:36.101 2374
11
12
13 2021-03-19T08:07:41.104 4737
message max{}(field) trigger over count(5)
index timestamp field
1 2021-03-19T08:07:31.373 623
2 2021-03-19T08:07:32.487 36
3 2021-03-19T08:07:32.683 237
4 2021-03-19T08:07:32.985 3467
5 2021-03-19T08:07:33.185 97
6 2021-03-19T08:07:33.200 2374
7 2021-03-19T08:07:33.375 39
8 2021-03-19T08:07:33.687 -49
9 2021-03-19T08:07:34.100 347
10 2021-03-19T08:07:36.101 -347
11 2021-03-19T08:07:37.102 4737
12 2021-03-19T08:07:38.103 499
13 2021-03-19T08:07:41.104 7
index timestamp field
1
2
3
4
5 2021-03-19T08:07:33.185 3467
6
7
8
9
10 2021-03-19T08:07:36.101 3467
11
12
13 2021-03-19T08:07:41.104 4737
message max{}(field) trigger over time(1m)
index timestamp field
1 2021-03-19T08:07:31.373 623
2 2021-03-19T08:07:32.487 36
3 2021-03-19T08:07:32.683 237
4 2021-03-19T08:07:32.985 3467
5 2021-03-19T08:07:33.185 97
6 2021-03-19T08:07:33.200 2374
7 2021-03-19T08:07:33.375 39
8 2021-03-19T08:07:33.687 -49
9 2021-03-19T08:07:34.100 347
10 2021-03-19T08:07:36.101 -347
11 2021-03-19T08:07:37.102 4737
12 2021-03-19T08:07:38.103 499
13 2021-03-19T08:07:41.104 7
timestamp field
2021-03-19T08:07:32 623
2021-03-19T08:07:33 3467
2021-03-19T08:07:34 3467
2021-03-19T08:07:35 3467
2021-03-19T08:07:37 3467
2021-03-19T08:07:38 4737
2021-03-19T08:07:39 4737
2021-03-19T08:07:42 4737
message running max{}(field) over count(5)
index timestamp field
1 2021-03-19T08:07:31.373 623
2 2021-03-19T08:07:32.487 36
3 2021-03-19T08:07:32.683 237
4 2021-03-19T08:07:32.985 3467
5 2021-03-19T08:07:33.185 97
6 2021-03-19T08:07:33.200 2374
7 2021-03-19T08:07:33.375 39
8 2021-03-19T08:07:33.687 -49
9 2021-03-19T08:07:34.100 347
10 2021-03-19T08:07:36.101 -347
11 2021-03-19T08:07:37.102 4737
12 2021-03-19T08:07:38.103 499
13 2021-03-19T08:07:41.104 7
index timestamp field
1 2021-03-19T08:07:31.373 623
2 2021-03-19T08:07:32.487 623
3 2021-03-19T08:07:32.683 623
4 2021-03-19T08:07:32.985 3467
5 2021-03-19T08:07:33.185 3467
6 2021-03-19T08:07:33.200 2374
7 2021-03-19T08:07:33.375 2374
8 2021-03-19T08:07:33.687 2374
9 2021-03-19T08:07:34.100 2374
10 2021-03-19T08:07:36.101 2374
11 2021-03-19T08:07:37.102 4737
12 2021-03-19T08:07:38.103 4737
13 2021-03-19T08:07:41.104 4737
message max{}(field) over every time(1m)
index timestamp field
1 2021-03-19T08:07:31.373 623
2 2021-03-19T08:07:32.487 36
3 2021-03-19T08:07:32.683 237
4 2021-03-19T08:07:32.985 3467
5 2021-03-19T08:07:33.185 97
6 2021-03-19T08:07:33.200 2374
7 2021-03-19T08:07:33.375 39
8 2021-03-19T08:07:33.687 -49
9 2021-03-19T08:07:34.100 347
10 2021-03-19T08:07:36.101 -347
11 2021-03-19T08:07:37.102 4737
12 2021-03-19T08:07:38.103 499
13 2021-03-19T08:07:41.104 7
timestamp field
2021-03-19T08:07:32 623
2021-03-19T08:07:33 3467
2021-03-19T08:07:34 2374
2021-03-19T08:07:35 347
2021-03-19T08:07:36 NULL
2021-03-19T08:07:37 -347
2021-03-19T08:07:38 4737
2021-03-19T08:07:39 NULL
2021-03-19T08:07:40 NULL
2021-03-19T08:07:41 NULL
2021-03-19T08:07:42 7
message running max{}(field) reset over time(1m)
index timestamp field
1 2021-03-19T08:07:31.373 623
2 2021-03-19T08:07:32.487 36
3 2021-03-19T08:07:32.683 237
4 2021-03-19T08:07:32.985 3467
5 2021-03-19T08:07:33.185 97
6 2021-03-19T08:07:33.200 2374
7 2021-03-19T08:07:33.375 39
8 2021-03-19T08:07:33.687 -49
9 2021-03-19T08:07:34.100 347
10 2021-03-19T08:07:36.101 -347
11 2021-03-19T08:07:37.102 4737
12 2021-03-19T08:07:38.103 499
13 2021-03-19T08:07:41.104 7
timestamp field
2021-03-19T08:07:31.373 623
2021-03-19T08:07:32.487 36
2021-03-19T08:07:32.683 237
2021-03-19T08:07:32.985 3467
2021-03-19T08:07:33.185 97
2021-03-19T08:07:33.200 2374
2021-03-19T08:07:33.375 2374
2021-03-19T08:07:33.687 2374
2021-03-19T08:07:34.100 347
2021-03-19T08:07:36.101 -347
2021-03-19T08:07:37.102 4737
2021-03-19T08:07:38.103 499
2021-03-19T08:07:41.104 7
message running max{}(field) over every time(1m)
index timestamp field
1 2021-03-19T08:07:31.373 623
2 2021-03-19T08:07:32.487 36
3 2021-03-19T08:07:32.683 237
4 2021-03-19T08:07:32.985 3467
5 2021-03-19T08:07:33.185 97
6 2021-03-19T08:07:33.200 2374
7 2021-03-19T08:07:33.375 39
8 2021-03-19T08:07:33.687 -49
9 2021-03-19T08:07:34.100 347
10 2021-03-19T08:07:36.101 -347
11 2021-03-19T08:07:37.102 -347
12 2021-03-19T08:07:38.103 499
13 2021-03-19T08:07:41.104 7
timestamp field
2021-03-19T08:07:31.373 623
2021-03-19T08:07:32 623
2021-03-19T08:07:32.487 36
2021-03-19T08:07:32.683 237
2021-03-19T08:07:32.985 3467
2021-03-19T08:07:33 3467
2021-03-19T08:07:33.185 97
2021-03-19T08:07:33.200 2374
2021-03-19T08:07:33.375 2374
2021-03-19T08:07:33.687 2374
2021-03-19T08:07:34 2374
2021-03-19T08:07:34.100 347
2021-03-19T08:07:35 347
2021-03-19T08:07:36 NULL
2021-03-19T08:07:36.101 -347
2021-03-19T08:07:37 -347
2021-03-19T08:07:37.102 4737
2021-03-19T08:07:38 4737
2021-03-19T08:07:38.103 499
2021-03-19T08:07:39 NULL
2021-03-19T08:07:40 NULL
2021-03-19T08:07:41 NULL
2021-03-19T08:07:41.104 7
2021-03-19T08:07:42 7
message max{}(field) trigger over every time(1m)
index timestamp field
1 2021-03-19T08:07:31.373 623
2 2021-03-19T08:07:32.487 36
3 2021-03-19T08:07:32.683 237
4 2021-03-19T08:07:32.985 3467
5 2021-03-19T08:07:33.185 97
6 2021-03-19T08:07:33.200 2374
7 2021-03-19T08:07:33.375 39
8 2021-03-19T08:07:33.687 -49
9 2021-03-19T08:07:34.100 347
10 2021-03-19T08:07:36.101 -347
11 2021-03-19T08:07:37.102 4737
12 2021-03-19T08:07:38.103 499
13 2021-03-19T08:07:41.104 7
timestamp field
2021-03-19T08:07:32 623
2021-03-19T08:07:33 3467
2021-03-19T08:07:34 3467
2021-03-19T08:07:35 3467
2021-03-19T08:07:36 3467
2021-03-19T08:07:37 3467
2021-03-19T08:07:38 4737
2021-03-19T08:07:39 4737
2021-03-19T08:07:40 4737
2021-03-19T08:07:41 4737
2021-03-19T08:07:42 4737


/*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)

List of Functions

ID Init args Args Returns Description
COUNT     INT64 counts messages
MAX   BOOLEAN?, INT8?, INT16?, INT32?, INT64?, FLOAT32?, FLOAT64?, DECIMAL64?, TIMESTAMP?, TIMEOFDAY?, VARCHAR? BOOLEAN?, INT8?, INT16?, INT32?, INT64?, FLOAT32?, FLOAT64?, DECIMAL64?, TIMESTAMP?, TIMEOFDAY?, VARCHAR? computes maximum value
MAX timePeriod: INT64 FLOAT64? FLOAT64? computes maximum over time window with given timePeriod
MAX period: INT64 FLOAT64? computes maximum over count window with given period  
MIN   BOOLEAN?, INT8?, INT16?, INT32?, INT64?, FLOAT32?, FLOAT64?, DECIMAL64?, TIMESTAMP?, TIMEOFDAY?, VARCHAR? BOOLEAN?, INT8?, INT16?, INT32?, INT64?, FLOAT32?, FLOAT64?, DECIMAL64?, TIMESTAMP?, TIMEOFDAY?, VARCHAR? computes minimum value
MIN timePeriod: INT64 FLOAT64? FLOAT64? computes minimum over time window with given timePeriod
MIN period: INT64 FLOAT64? FLOAT64? computes minimum over count window with given period
SUM   INT8?, INT16?, INT32?, INT64?, DECIMAL64? DECIMAL64? computes sum of given values
SUM   FLOAT32?, FLOAT64? FLOAT32?, FLOAT64? computes sum of given values
SUM timePeriod: INT64 DECIMAL64? DECIMAL64? computes sum of values in time window with given time period
SUM period: INT32 DECIMAL64? DECIMAL64? computes sum of values in count window with given period
AVG   INT8?, INT16?, INT32?, INT64?, DECIMAL64? DECIMAL64? computes avg of given values
AVG   FLOAT32?, FLOAT64? FLOAT32?, FLOAT64? computes avg of given values
SMA timePeriod: INT64 FLOAT64? FLOAT64? computes moving average over time window with given timePeriod
SMA period: INT64 FLOAT64? FLOAT64? computes moving average over count window with given period
CMA   FLOAT64? FLOAT64? computes cumulative moving average
EMA period: INT32 FLOAT64? FLOAT64? computes exponential moving average with given period
EMA factor: FLOAT64 FLOAT64? FLOAT64? computes exponential moving average with given factor

DDL

In TimeBase Shell it is possible to create and drop 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 every single detail of it, including not only stream schema, but such properties as a transient stream’s buffer limits, or a durable stream’s distribution and high availability. To get stream’s details run desc - describe stream command in TimeBase Shell.

Describe Stream

TimeBase Shell provides a command desc to reverse-engineer an existing stream back into a QQL statement that creates it. This is called “describing” a stream.

/*Shell Format*/
==> desc tickquerydemo
DURABLE STREAM "tickquerydemo" (
    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 'tickquerydemo'

Create Stream

To create a stream, once you have the syntax, you simply append the keyword CREATE in front of whatever the desc command printed out. NOTE, you cannot create a stream with 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.

/*Shell Format*/
==> 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'
/
>deltix.timebase.api.messages.ErrorMessage,TIMESTAMP,SYMBOL,TYPE,type,level,messageText,details
0,2012-03-06 20:55:57.785,,SYSTEM,SUCCESS,INFO,Stream created,_
==>

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 completed successfully. You can just read the message text to mak sure everything is alright.

Modify Stream

To modify a stream, once you have the syntax, you simply append the keyword MODIFY in front of whatever the desc command printed out. Specify all classes that will be present in stream - see example ==> MODIFY STREAM TEST.

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

To remove (“drop”) a stream, you issue the DROP STREAM statement.


==> drop stream test
>deltix.timebase.api.messages.ErrorMessage,TIMESTAMP,SYMBOL,TYPE,type,level,messageText,details
0,2012-03-06 21:16:44.278,,SYSTEM,SUCCESS,INFO,Stream dropped,_