< Previous		Next >

LiveQL Reference

Contents

LiveQL Language Features
Example: Command-Line Queries with lv-client
Example: Queries with LiveView Desktop

This topic provides links to reference descriptions for the StreamBase LiveView query language, LiveQL.

LiveQL Language Features

LiveQL, the LiveView Query language, lets you retrieve and manipulate data from LiveView tables. The query framework is similar to StreamSQL; it has specialized structures to handle streaming data. You use the query language different ways depending on which client you are using and the type of table you are querying.

The command tokens of LiveQL are case insensitive, but are shown in ALL CAPS for clarity. The basic syntax for the query language is given here. The query syntax is shown on six lines for readabililty.

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 let you select what data is returned.

A query projection 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 may be aliased with the AS clause.
- A dynamic aggregation expression. The dynamic aggregation expression consists of an aggregate function, an aggregate function argument (which may be an exact field name or a LiveView expression), and an alias. Aggregate expressions must be aliased with an AS clause.
- *. This returns all fields from the table. * cannot be used in conjunction with any other projections.
Projection syntax:
```
fieldname1 [AS alias], fieldname2 [AS alias], ..., aggregation_function1(fieldname | expression) AS alias, ...
```
Predicates are expressions that return a Boolean. If the query predicate returns TRUE, the query returns a table row.
Time-Based Data Modifiers are extensions to SQL-like clauses that let you select streaming data by time-based criteria. Time-based data modifiers are the following types:
- Time-window modifiers allow you to limit results to a specific time window. Time-windows have the following syntax:
```
WHEN expression BETWEEN timestamp-expression1 AND timestamp-expression2
```
- Time-delay modifiers allow you to limit results to conditions that are true for a specified amount of time.
```
WHERE expression FOR number MILLISECONDS
```
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:
```
SELECT fieldname1, fieldname2, ... aggregate_expression1 AS alias, ... FROM Table WHERE expression GROUP BY fieldname1, fieldname2, ...
```

LiveQL queries come in four types:

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 a table or table rows that satisfy a query predicate.

Example: Command-Line Queries with lv-client

The lv-client utility provides SQL-like query syntax. lv-client can issue SNAPSHOT queries, CONTINUOUS queries, SNAPSHOT_AND_CONTINUOUS queries, and DELETE queries.

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

Open a StreamBase Command prompt.
Enter lv-client to start the command-line client. This opens an LV> command prompt.
Enter connect;

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

The basic syntax of the SNAPSHOT query is similar to SQL. All statements end with a semicolon (;). The following query syntax is shown on five lines for readabililty. The lv-client treats multiline queries as one query.

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:

DELETE table [CLAUSE];

Deleting a table with no predicate deletes all rows of the table. The clause can be any of the WHERE and WHEN syntaxes:

DELETE table
   [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 will only delete the requested number of rows and then terminate the query.

Example: Queries with LiveView Desktop

Syntax: Select Field

The Select window in LiveView Desktop takes the query projection. LiveView Desktop provides SNAPHOT_AND_CONTINUOUS queries for LiveView Data tables.

By choosing a table name in the Tables view, you designate that value equivalent to the FROM clause of a SQL statement. Together, these two selections work similarly to a SELECT * FROM Table statement in SQL.

SELECT projection1 [, projection2...]

Syntax: Query Field

The Query field can contain query predicates, Streaming data modifiers, or query modifiers. The WHERE 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