LiveQL Reference

< Previous		Next >

LiveQL Overview

LiveQL, the LiveView Query language, lets you retrieve and manipulate data from LiveView data tables. The query framework derives from and is and similar to StreamSQL, and has specialized structures for handling streaming data.

Use LiveQL in different ways, depending on the client you are using and the type of table you are querying. You can use LiveQL statements:

Interactively, using a LiveView client application connected to a LiveView Server instance. LiveView clients include:
- TIBCO LiveView™ Desktop
- TIBCO LiveView™ Web
- The lv-client command-line utility
- A custom client application you write using the Java, .NET, or JavaScript LiveView Client APIs.
In LiveView configuration files, as part of a dynamic or author-time aggregation table definition.

LiveQL Syntax Reference

The command tokens of LiveQL are case insensitive, but are shown in UPPERCASE for clarity. The basic syntax for the query language is shown here, where braces represent optional clauses:

SELECT projection 
FROM source_table
 {WHERE predicate-expr}
 {WHERE colname|colexpr IN ['string1','string2']}
 {WHERE colname|colexpr {FOR time-delay MILLISECONDS}}
 {WHEN expression BETWEEN timestamp-expr1 AND timestamp-expr2}
 {GROUP BY colname1, colname2, ... }
 {ORDER BY colname LIMIT row-limit};

In the syntax description above and throughout this page:

colname is the exact, case-sensitive name of a column in a LiveView data table.
colexpr is a StreamBase expression that resolves to the name of a column in a LiveView data table.
predicate-expr is a StreamBase expression that resolves to a Boolean true or false value.
timestamp-expr is a StreamBase expression that resolves to a timestamp value.

LiveQL queries have the following parts:

Projections

The projection component of a LiveQL query specifies the data to be returned by the query. A projection in LiveQL can be one of three things:

An exact column name from a LiveView table. The column name can be aliased with an AS clause.
A single asterisk (*). This returns all columns from the specified table. The single asterisk cannot be used in conjunction with any other projections.
A LiveView aggregation expression. The aggregation expression consists of an aggregate function, the arguments for the aggregate function, and, optionally, an alias. Aggregate expressions may be aliased with an AS clause. The arguments of LiveView aggregate functions can be an exact column name or a StreamBase expression that resolves to an existing column name in the table to which the SELECT is applied.

StreamBase expression language functions outside the context of a LiveView aggregation expression are explicitly not allowed in LiveQL projections.

The projection syntax follows, shown on multiple lines for clarity:

colname1 {AS alias1}, colname2 {AS alias2}, ..., 
  aggregation_function1 (colname | colexpr) {AS alias3}, 
  aggregation_function2 (colname | colexpr) {AS alias4} ...

Predicates

Predicates are StreamBase expressions that return a Boolean. If a query predicate expression returns true, the query returns a table row.

Query Modifiers

Query modifiers let you limit, sort, and group the query results:

Use the ORDER BY keyword to sort query results in ascending or descending order. Use the LIMIT keyword to limit query results to a specified number of rows. These query modifiers have the following syntax:
```
SELECT projection FROM Table
WHERE predicate-expr ORDER BY colname1 {asc|desc} {, 
  colname {asc|desc}, ... LIMIT N
```
Use the IN keyword to specify multiple values in a WHERE clause, using the following syntax. The square brackets are a required part of the syntax. Specify strings to match members of the specified column (or expression that resolves to a column name).
```
SELECT projection FROM Table 
  WHERE colname|colexpr IN ['string1','string2',...]
```
GROUP BY clauses let you collect data from multiple rows and group the results by query projection. A GROUP BY entry is required for any raw column name in the query projection. Queries with GROUP BY clauses have the following syntax:
```
SELECT projection FROM Table 
  WHERE predicate-expr 
  GROUP BY colname1, colname2, ...
```

Time-Based Data Modifiers

Time-based data modifiers are extensions to standard SQL that let you select streaming data by time-based criteria. Time-based data modifiers are the following types:

Time-window modifiers let you limit results to a specific time window. Time windows have the following syntax:
```
SELECT projection FROM Table 
  WHEN colname|colexpr BETWEEN timestamp-expr1 
  AND timestamp-expr2
```
Time-delay modifiers let you limit results to conditions that are true for a specified amount of time.
```
SELECT projection FROM Table 
  WHERE colname|colexpr FOR number MILLISECONDS
```

In both cases, the column name (or expression that resolves to a column name) must refer to a LiveView data table column that contains timestamp values.

LiveQL Query Types

There are four types of LiveQL queries:

SNAPSHOT: A query that executes once and returns a single result set, containing all records from the target table that satisfy the query at the time of execution.
CONTINUOUS: A query that provides to a client a continuously updating view of the data that satisfies the query.
SNAPSHOT_AND_CONTINUOUS: A query that first executes a snapshot query and then continues to return results as records that satisfy the query are created, updated, or deleted in the target table.
DELETE: A query that deletes table rows that satisfy a query predicate.

StreamBase Expressions in LiveQL Queries

LiveQL clauses can incorporate expressions composed in the StreamBase expression language, as documented in StreamBase Expression Language Features and StreamBase Expression Language Functions.

LiveQL supports only simple functions from the StreamBase expression language, and not the StreamBase aggregate functions. LiveView aggregate expressions instead use a particular list of LiveView-specific aggregate functions.

Expression language simple functions in LiveView have no state and take in one tuple value and emit one value.

StreamBase expression language simple functions that fit the limitations stated in this section are supported:

In all LiveQL predicate expressions.
In LiveQL Projections, only In the context of a LiveView aggregate function.

StreamBase expressions in LiveQL support only the simple data types as listed in the next section. A function must take only the supported types as input and return only the supported types as output.

Note

Functions that take capture, list, named schemas, and tuple data types are not supported for use in LiveView queries or configurations. To use these functions with LiveView, you can instead configure an EventFlow application that outputs a LiveView-supported data type, and publishes the output to a LiveView table.

Data Types Supported in LiveView Tables

LiveQL uses the capabilities of the simple functions from the StreamBase expression language to manage the supported LiveView data types. The data types supported for table columns in LiveView are shown in the following table:

Data Type	Description
blob	Binary data in a tuple. Blobs are designed to efficiently process large data objects such as video frames or other multimedia data, although performance might diminish with larger sizes. The blob data type is not supported for table indexes.
bool	Boolean true or false.
double	8-byte numeric.
int	4-byte numeric
long	8-byte integer.
string	Field of text characters
timestamp	Absolute or interval timestamp.

Example: Queries with LiveView Desktop

Syntax: Select Field

The Select window in LiveView Desktop takes the query projection, with the SELECT keyword itself presumed. LiveView Desktop supports SNAPHOT_AND_CONTINUOUS queries for LiveView data tables.

By choosing a table name in the Tables view, you designate that table's name equivalent to specifying it with a FROM clause of a SQL statement. The initial table selection and the projection work together to implement the equivalent of a SELECT * FROM Table statement in SQL.

projection1 [, projection2...]

Syntax: Query Field

The Query field can contain query predicates, streaming data modifiers, or query modifiers. The WHERE keyword is optional:

[[WHERE]] expression1 [OR expression2] [AND expression3] 
    [FOR time-delay MILLISECONDS]
     AND WHEN TimeStampField BETWEEN older-time-expr AND newer-time-expr
     ORDER BY field_identifier1 [asc|desc][,field_identifier2 [asc|desc]], ... 
     LIMIT N

Example: Command-Line Queries with lv-client

The lv-client utility supports LiveQL Syntax as described above. lv-client can issue SNAPSHOT queries, CONTINUOUS queries, SNAPSHOT_AND_CONTINUOUS queries, and DELETE queries.

To start lv-client against a LiveView Server running on the default port on the same machine, follow these steps:

Open a StreamBase Command prompt on Windows, or a shell prompt on OS X or Linux that is configured with sb-config --env.
Enter lv-client to start the command-line client. This starts an LV> command prompt.
Enter connect;

This connects lv-client to a LiveView Server instance running on port 10080 on localhost.

The basic syntax of a SNAPSHOT query is similar to standard SQL. All statements must end with a semicolon (;).

The following query syntax is shown on six lines for readability. The lv-client utility treats multiline queries as a single query as long as the last line terminates with a semicolon.

SELECT projection ... 
FROM source_table 
   [WHERE expression [FOR time-delay MILLISECONDS]]
   [WHEN expression BETWEEN time-expression1 AND time-expression2]
   [GROUP-BY colname1, colname2,...] 
   [ORDER BY colname LIMIT row-limit ];

To issue a SNAPSHOT_AND_CONTINUOUS query, use the LIVE prefix in front of any valid SNAPSHOT query:

LIVE SELECT projection1, projection2, ... FROM table ... ;

To issue a CONTINUOUS query, use the CONTINUOUS prefix in front of any valid SNAPSHOT query:

CONTINUOUS SELECT projection1, projection2, ... FROM table ... ;

To issue a DELETE query, use the following syntax, where table-name specifies the table to delete from and where-predicate is a predicate clause that selects the rows to delete.

DELETE [from] table-name [where-predicate];

Deleting from a table without specifying a predicate clause deletes all rows of the specified table. The predicate clause can be any of the WHERE and WHEN syntaxes:

DELETE [from] table-name
   [WHERE expression [FOR time-delay MILLISECONDS]]
   [WHEN expression BETWEEN time-expression1 AND time-expression2]

A LIMIT clause (without an ORDER BY) is allowed in a delete query but only deletes the requested number of rows and then terminates the query.

Caution

Deleted data cannot be recovered, so use this command with care.