Understanding the Luminesce SQL query syntax

Views:

Related resources:

Luminesce SQL queries are written in the SQL dialect of SQLite, with the syntactical extensions and limitations described below. See which tools are available to write queries.

Note a query can be written either for a data provider or a direct provider, and there are some syntactical differences between them. In particular, direct providers have an arbitrary syntax that may differ for each.

Summary

Luminesce SQL supports most of the syntax of the SQLite SELECT statement. It does not support data definition language (DDL) syntax for creating or modifying database objects.

Common table expressions (CTE) are not supported in Luminesce SELECT statements but a re-usable variable syntax is available instead.
TYPES statements can be used before Luminesce SELECT statements to force type conversion on columns.
FOR-EACH loops can be emulated using APPLY statements.
Most types of JOIN clause are supported.
A WAIT modifier is available to pause query execution.
A number of common SQL functions are supported, and we've supplemented these with sets of custom functions and statistical functions.
A number of PRAGMA statements specific to Luminesce are supported.
A number of ways to convert to and from JSON are supported.
Dates must be encapsulated in # rather than ' characters, so for example #2023-11-15 15:59:59# (though other date formats can also be understood; read more here).
Strings must be encapsulated in ' rather than " characters, so for example PortfolioScope = 'Finbourne-Examples'. Note SQLite interprets a column name encapsulated in " characters as a string literal if it doesn't match a valid column name. For this reason, we recommend encapsulating column names in [] characters. Read more on this in the SQLite documentation.
Multiple lines can be commented out using /* and */ characters at the beginning and end respectively.

See the full list of valid keywords (both inherited from SQLite and proprietary to FINBOURNE).

SELECT statement examples	Explanation
select * from Some.Provider	Selects all the columns from `Some.Provider`
select x, y from Some.Provider	Selects the X and Y columns
select * (except x, y) from Some.Provider	Selects all the columns except X and Y. Can use the `-` character in place of `except`
select ^ from Some.Provider	Selects the main columns (those considered most important by the provider author)
select ^ (except x, y) from Some.Provider	Selects the main columns except X and Y
select * from Some.Provider limit 5	Returns only the first 5 records

For a Some.Provider that is authored by FINBOURNE, the fields (columns) available to return or filter can be determined by running the following query:

select FieldName, DataType, IsMain, IsPrimaryKey, SampleValues, Description from Sys.Field where TableName = 'Some.Provider' and FieldType = 'Column';

Parameters

Data providers may have parameters that can be used to filter a Luminesce query as part of a standard WHERE clause.

Note: Direct providers do not support parameters.

You must assign values to parameters unambiguously (not under an OR clause), and only with the = operator. For example:

select * from Some.Provider where SomeParameter = 12

For a Some.Provider that is authored by FINBOURNE, available parameters can be determined by running the following query:

select FieldName, DataType, ParamDefaultValue, Description from Sys.Field where TableName = 'Some.Provider' and FieldType = 'Parameter';

Variables

You can use variables to create and populate arbitrary tables of data that can then be used as part of a Luminesce query for either a data provider or a direct provider.

This is similar to a combination of the standard SQL CREATE TABLE...INSERT INTO statements.

Variable type	Explanation	Examples
`@tablevariable`	Stores a table of any size	@abc = select TableName, FieldName from Sys.Field order by 1, 2;
`@@scalarvariable`	Only stores a scalar value (that is, the `SELECT` statement must resolve to exactly one column and one row of data)	@@abc = select strftime('%Y%m%d', 'now'); @@portfolioscope = select 'Finbourne-Examples';

Type conversion

You can use the TYPES statement before any SELECT statement in a Luminesce query to force type conversion on columns. Note that this overrides the default SQLite behaviour of flexible typing.

Valid types are BIGINT, INT, DOUBLE, DECIMAL, BOOLEAN, TEXT, DATE, TIME and DATETIME.

Example	Explanation
TYPES bigint, int, text, boolean; SELECT 1, 2, 3, 4;	Returns columns as Int64, Int32, String and Boolean types respectively.
TYPES bigint, int, , boolean; SELECT 1, 2, 3, 4;	Returns columns 1, 2 and 4 as Int64, Int32 and Boolean types respectively, leaving column 3 to be inferred in the normal way.
TYPES text; SELECT 1, 2, 3, 4;	Returns column 1 as text and leaves all the other columns to be inferred in the normal way.
TYPES double; SELECT SUM(Cost) as X FROM Products;	Returns the result of the aggregate function as a double.
@data = TYPES text; values ('018802','NR',#2000-07-13T00:00:00Z#,'LegalEntity','moodysLongRating');	Returns column 1 as text, leaves all the other columns to be inferred in the normal way, and assigns the result to a table variable.

FOR-EACH loops

You can use the CROSS APPLY or OUTER APPLY statement to emulate a FOR-EACH loop. This is useful to execute a Luminesce query in parallel on a range of parameter inputs.

Statement	Explanation
`CROSS APPLY`	Similar to `INNER JOIN`, in that only records that have matching values on both sides are returned.
`OUTER APPLY`	Similar to `LEFT OUTER JOIN`, in that all records on the left side are returned, even when there are no matching records on the right side.

For example, the following query uses OUTER APPLY with the Lusid.Portfolio.Holding provider to generate a holdings report for each of the last 3 days:

@days = select date('now', '-' || Value || ' days') as dt
  from Tools.[Range] where number = 3        -- go back N days from today
  and Start = 0;                             -- subtract 0 days, to include today (1 would exclude today)

select d.dt, results.*                       -- outer query
  from @days as d
  outer apply (
    select h.^                               -- inner query
      from lusid.portfolio.holding as h
      where h.EffectiveAt = d.dt
      and h.PortfolioScope = 'EMEA'
      and h.PortfolioCode = 'Equities'
  ) as results ;

Note the following:

Tables used in the inner or outer queries must be aliased, so for example as d in the outer query above and as h in the inner query.
Columns used in the where clause of the inner query must be explicitly selected in the outer query, so for example d.dt in the inner must be referenced as select d.dt in the outer. Referencing as select d.* would result in an error.
There must be whitespace before the closing ; character, for example as results ;
Variables (whether @table or @@scalar) are not expanded in the inner query.
There is no ON clause for either CROSS APPLY or OUTER APPLY.

JOIN clauses

Luminesce supports the following JOIN clauses: INNER JOIN, LEFT (OUTER) JOIN, CROSS JOIN, FULL (OUTER) JOIN.

Luminesce does not currently support RIGHT (OUTER) JOIN.

Pausing query execution using WAIT

You can use the WAIT modifier immediately after a select * from x statement at any point in a query to wait for all the preceding code to execute before continuing, and optionally for a further specified number of seconds.

For example, if you create a custom view you may want to wait 5 seconds for it to appear in the Luminesce catalog before querying it:

@model_portfolios_view = use Sys.Admin.SetupView
--provider=Test.Example.TestHoldings
----
select PortfolioScope, PortfolioCode, BaseCurrency
from
  Lusid.Portfolio
where
  PortfolioType = 'Reference'
enduse;
--- Create the Test.Example.TestHoldings view
@result = select * from @model_portfolios_view;
--- Query the view, waiting 5 seconds for Luminesce to register it
select * from Test.Example.Testholdings wait 5 limit 1;

PRAGMA statements

You can use one or more of the PRAGMA statements in the following table at any point in a query to override default Luminesce settings for that query. For example, to allow a query to run for 30 minutes before timing out, specify:

pragma TimeoutSec = 1800;

PRAGMA	Explanation
`DryRun = True \| False`	When true, parses the query, verifies access to provider(s) and, where possible, returns dummy data so you can examine the shape. However, does not execute the query, so (for example) write providers do not modify data. Designed for use in CI/CD pipelines where there may be downstream implications.
`MaxApplyParallelization = <number>`	Defines the maximum number of parallel provider calls within a CROSS APPLY or OUTER APPLY statement in a query. Defaults to 5.
`MaxParallelVariableCalculations = <number>`	Defines the maximum number of variable calculations that may be performed in a query. Note if you experience a `"database is locked"` error, adding `pragma MaxParallelVariableCalculations = 1` to your query may help to resolve the issue.
`ProviderRetryNonResponsiveAttempts = <number>`	Specifies the number of times to retry a provider if it is determined to be non-responsive. Defaults to 1.
`TimeoutSec = <seconds>`	Specifies a number of seconds to wait before a query times out.
`QueryName = <name>`	Names a query.
`TypeDeterminationRowCount = <rows>`	Specifies the number of rows to use to determine the data type on returned data sets (defaults to 500). Note that for custom views it is better to prefer the `~<DataType>` syntax.
`WebApiCacheForSeconds = <seconds>`	For use with the Luminesce `/api/SqlBackground/*` REST APIs in the Sql Background Execution collection. Re-uses the results from a previous character-identical query for up to the number of seconds when run by the same user. This must be specified on the 1st and 2nd+ usages of the query.
`QueryDiagnostics = <flag> [, <flag>...]`	Specifies flags that can be given to the query coordinator to perform during execution for additional diagnostics. The following flags can be specified: `TableVariables` dumps the column structure and first few rows of every table variable that requires calculation immediately after it is calculated. `ScalarVariables` dumps the value of every scalar variable that requires calculation immediately after it is calculated. `DependencyTree` outputs the dependency tree within the query. `All` performs all possible additional diagnostics which are enabled through using this PRAGMA.

Converting to and from JSON

Luminesce supports the functions exposed by the JSON1 extension library.

You can use the dedicated Tools.JsonExpand provider to emulate the SQLite json.tree function and convert a JSON document into a table of data objects.

To convert a table of data objects into JSON, you can use the SQLite json_group_array and json_object functions. This is required when using a provider that has a field with a Json suffix, for example the PeriodsJson field in the Lusid.Instrument.SimpleCashFlowLoan.Writer provider. The PeriodsJson field is a text field that expects a flattened array of JSON objects as a value, each representing a loan period with PaymentDate, Notional and InterestAmount fields. For example:

@periods = values
(#2022-03-01#, 100, 5),
(#2022-06-01#, 90, 4),
(#2022-09-01#, 50, 4.5),
(#2022-12-01#, 30, 2.0);

@@periodsArray = select
    json_group_array(
        json_object(
            'PaymentDate', column1,
            'Notional', column2,
            'InterestAmount', column3
        )
    ) from @periods;

@table_of_data = select 'MySimpleCashFlowLoan' as DisplayName, 'SimpleCashFlowLoan' as ClientInternal,
#2022-01-01# as StartDate, #2023-01-01# as MaturityDate, 'GBP' as DomCcy, @@periodsArray as PeriodsJson;

select * from Lusid.Instrument.SimpleCashFlowLoan.Writer where ToWrite = @table_of_data;

Keywords: luminesce, sql