LiveQL Reference

LiveQL Overview

LiveQL, the LiveView Query language, lets you retrieve and manipulate data from LiveView data tables. The query framework 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:

    • Spotfire LiveView™ Desktop

    • Spotfire LiveView™ Web

    • The lv-client command-line utility

    • A custom client application you write using the Java, .NET, REST, 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 {FOR time-delay MILLISECONDS}}
 {WHEN colname-timestamp {BETWEEN timestamp-expr1 AND timestamp-expr2}}
 {GROUP BY expression1, expression2, ... }
 {ORDER BY colname1 <DESC | ASC> , colname2 <DESC | ASC>, …}  
 {PIVOT agg-function1 {AS alias1}, agg-function2 {AS alias2} ...
    {FOR colname} VALUES [literal1, literal2 {AS alias3}, 
    boolean_expression1, boolean_expression2 {AS alias4}]
    GROUP BY expression}
 {LIMIT row-limit}
 {HAVING expression};

In the syntax description above and throughout this page:

  • colname is the exact, case-sensitive name of a column in a LiveView data table. colname-x can be a different column in the table. Colname-<datatype> must resolve to one of the data types.

  • 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. timestamp-expr1 must be earlier than timestamp-expr2.

  • aggregateExpression is a StreamBase aggregate expression.

  • valueExpression is a StreamBase expression that resolves to a Boolean true or false value.

  • expression: In the StreamBase expression language, one or more symbols (arithmetic operators, function calls, constants, field names) that may be evaluated to a single-typed value given some (optional) evaluation context in which to resolve field names and other variables. For example, an EventFlow expression that evaluates to a Boolean (true or false) value (such as: price > 100) is often used as a predicate for choosing an action to take.

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 four things:

  • A single asterisk (*). This returns all columns from the specified table. The single asterisk cannot be used in conjunction with any other projections.

  • A comma-separated list of exact column names from a LiveView table. The column name can be aliased with an AS clause.

  • A comma-separated list of simple (arbitrarily complex but no aggregate functions) StreamBase expressions, and optionally, an alias. These are also called calculated columns.

  • 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 simple StreamBase expression that resolves to the data type required by the aggregate function.

You cannot mix aggregate expressions and simple expressions in the same projection.

You cannot use aliased names in WHEN, WHERE, or ORDER BY expressions. Instead, use table field names. The HAVING clause can use table field names or alias names.

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

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

Predicates are StreamBase expressions that return a Boolean. If a query predicate expression returns true, the query returns a table row assuming any time window constraint is met.

For example, you can use a WHERE clause to affect only rows that meet specified criteria (criteria expressed in the form of predicates):

  • 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 literals to match the data type of the specified column (or expression that resolves to a column name).

    SELECT projection FROM Table 
      WHERE colname IN [literal_of_colname_datatype, 
      literal_of_colname_datatype, … ]

    For example:

    WHERE quantityRemaining IN [2,3,4]

    Or:

    WHERE category IN ["toy","book"]
  • You cannot use projection aliases in a WHERE clause. The WHERE clause defines what values are delivered to the projection, therefore you cannot have the projection provide info to the WHERE.

Query Modifiers 

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

  • You can either use ORDER BY or GROUP BY in a single LiveQL statement, but not both.

  • Use the ORDER BY keyword to sort query results in ascending or descending order. Use the LIMIT keyword to define the maximum query result size. These query modifiers have the following syntax:

    SELECT projection FROM Table
    WHERE predicate-expr 
    {WHEN colname-timestamp {BETWEEN timestamp-expr1 AND timestamp-expr2}}
    ORDER BY colname1 {asc|desc} {, 
      colname {asc|desc}, ...} LIMIT N

    LIMIT has a special meaning in ORDER BY queries. In ORDER BY queries, the limit defines the maximum size of the result set. The result set will grow to size LIMIT and then the correctly ordered rows will be added/removed or updated from the result set regardless of how many total rows meet the predicate.

  • For non ORDER BY queries, LIMIT acts as a mechanism for clients to optionally use to protect themselves from unexpectedly large result sets. If the result set grows to LIMIT size, the query is closed and the client gets an exception.

  • 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 
      {WHEN colname-timestamp {BETWEEN timestamp-expr1 AND timestamp-expr2}}
      GROUP BY expression1 AS Alias1, expression2 AS Alias2, ...

    Note that top-level functions in the projection must be aggregate functions, however aggregate functions can take simple expressions as arguments. GROUP BY expressions can be any simple expression. Aliasing is optional, but common as the GROUP BY values are usually included in the projection and are referenced by their aliased name.

  • HAVING allows a query that uses aggregation to have further filtering after the aggregation. HAVING is typically used with a GROUP BY clause. Clauses in simple expressions are converted internally to WHERE clauses to reduce performance impact; using overly restrictive HAVING clauses in aggregate queries can affect performance.

    The HAVING clause must always be used at end of a query. For example:

    SELECT projection 
    FROM source_table
     {WHERE predicate-expr {FOR time-delay MILLISECONDS}}
     {WHEN colname-timestamp {BETWEEN timestamp-expr1 AND timestamp-expr2}}
     {GROUP BY expression1, expression2, ... }
     {ORDER BY colname1 <DESC | ASC> , colname2 <DESC | ASC>, …  
      LIMIT row-limit}
     {HAVING expression}; 

    HAVING expression examples:

    SELECT category, count(), avg(lastSoldPrice) FROM ItemsSales GROUP BY 
      category HAVING count() > 5
    SELECT category, count() FROM ItemsSales GROUP BY category 
      HAVING avg(lastSoldPrice)> 5
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 
      {WHERE predicate-expr}
      WHEN colname-timestamp BETWEEN timestamp-expr1 
      AND timestamp-expr2
      {GROUP BY expression1, expression2, … | ORDER BY colname1 {asc|desc}, ...} LIMIT N
  • Time-delay modifiers let you limit results to conditions that are true for a specified amount of time.

    SELECT projection FROM Table 
      WHERE predicate-expr FOR number MILLISECONDS
      {GROUP BY expression1, expression2, … | ORDER BY colname1 {asc|desc}, ...} LIMIT N
Pivot Modifier

To issue a PIVOT query, you can select a column in the source table as a pivot column and create columns in a destination table for each value in the source pivot column.

The following describes sample syntax that can be used for a pivot query, including various supported options used in the VALUES clause. Literals are replaced by columnName=literal where columnName is the one used in the FOR clause. Additionally, every aggregate expression in the PIVOT clause is computed for all the values in the VALUES clause.

Long lines below wrap to the next for legibility.

SELECT * 
FROM Table
PIVOT aggregate_function1 {AS alias1}, aggregate_function2 {AS alias2} ...
{FOR colname1}
VALUES [literal1, literal2 {AS alias3}, boolean_expression1, boolean_expression2 
  {AS alias4}]
GROUP BY expression1;

See Pivot Queries for additional pivot query examples and a detailed explanation of the supported pivot query syntax.

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 specialized query that provides to a client a continuously updating view of the data beginning the moment the query started and that satisfies the predicate. Because no history is provided by design, results are not coherent in that clients can see removes and updates for tuples never before seen.

SNAPSHOT_AND_CONTINUOUS

A query that first executes a snapshot query and then transitions coherently 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 simple functions from the StreamBase expression language, and a subset of StreamBase aggregate functions. See particular list for the subset of aggregate functions. As noted above there are rules about when and where functions can be used.

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.

StreamBase expressions in LiveQL support only the simple data types as listed in the next section. A function can take any supported LiveView data types as input and can return any StreamBase data type, including lists and tuples.

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

  1. Open a StreamBase Command prompt on Windows, or a shell prompt on macOS or Linux that is configured with sb-config --env.

  2. Enter lv-client to start the command-line client. This starts an LV> command prompt.

  3. Enter connect;

    This connects lv-client to a LiveView Server instance running on port 11080 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 expression1, expression2,...] 
   [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.