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 LiveView Desktop or the lv-client utility connected to a LiveView Server instance.
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:

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

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 field name from a LiveView table. The field name can be aliased with an AS clause.
A single asterisk (*). This returns all fields from the 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.

Projection syntax, shown on multiple lines for publication clarity:

fieldname1 [AS alias1], fieldname2 [AS alias2], ..., 
    aggregation_function1(fieldname | expression) [AS alias], 
    aggregation_function2(fieldname | expression) [AS alias] ...

Predicates

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

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:
```
WHEN expression BETWEEN timestamp-expression1 AND timestamp-expression2
```
Time-delay modifiers let you limit results to conditions that are true for a specified amount of time.
```
WHERE expression FOR number MILLISECONDS
```

Query Modifiers

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

Top-N queries let you sort query results and limit results to a specified number of rows. Top-N query modifiers have the following syntax:
```
Predicate-Expr ORDER BY Field1 [asc|desc][, Field2 [asc|desc]], ... LIMIT N
```
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 field name in the query projections. Queries with GROUP BY clauses have the following syntax, which is shown here on two lines for publication clarity:
```
SELECT fieldname1, fieldname2, ... aggregate_expression1 AS alias, 
    ... FROM Table WHERE expression GROUP BY fieldname1, fieldname2, ...
```

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 Predicates.
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 publish 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 fields 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 fieldname1, fieldname2,...] 
   [ORDER BY fieldname 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.