A provider enables you to write a Luminesce SQL query for a data source in situ, without first having to extract, transform or load data from that source. Understanding the Luminesce platform architecture.
We supply providers for numerous data sources, including for the investment data stored in LUSID itself and for files stored in Drive, or you can create your own provider for a proprietary data source.
There are two types of provider: a data provider and a direct provider.
Data provider
A data provider is designed to query a data source whose shape is known. It therefore returns a table of results with a fixed number of fields (columns).
Most of the providers we supply to query the investment management data stored in LUSID itself are data providers. These are typically simpler to use and follow the standard SQL patterns more closely.
Generally speaking, a Luminesce query for a data provider uses the standard SQLite SELECT syntax, with a few extensions and limitations. For example, the following query for the Lusid.Instrument data provider returns a full table of results (that is, every available field) for all the instruments in LUSID's instrument master that are not currencies:
select * from Lusid.Instrument where LusidInstrumentId not like 'CCY_%'Substituting * for ^ in this query returns a subset of fields for the same instruments (namely the LusidInstrumentId, DisplayName and IsActive fields), which are the ones FINBOURNE (as provider author) consider 'main' (or key) for instruments:
select ^ from Lusid.Instrument where LusidInstrumentId not like 'CCY_%'A data provider is often thought of as 'reading' data, but this is not exclusive; the only hard requirement is that it 'returns' data. For example, we supply providers that perform calculations on demand (such as Lusid.Portfolio.Valuation, where the data returned doesn't actually exist anywhere but rather is calculated each time), or those whose primary role is to 'write' data (such as Lusid.Instrument.Equity.Writer, which as the name suggests upserts equity instruments into LUSID's Security Master from a table of data).
Direct provider
A direct provider is designed to query a data source whose shape is not known, and thus cannot return a table of results with a fixed number of fields (columns). You can tell a direct provider because it has N/A instead of a list of fields when you open the provider in the Luminesce catalog:
A Luminesce query for a direct provider uses an arbitrary syntax, which may differ for each. It accepts a set of tabular or scalar variable inputs and returns a table of results. The general form is as follows:
@variable = use Some.Provider [wait] [with <variable>, [<variable>], ...] [limit N];
<arbitrary-syntax>
enduse;
select * from @variableNote the following:
The
WITHkeyword is optional but useful for passing either tabular or scalar variable inputs into the query.The arbitrary syntax between the
USEandENDUSEkeywords is determined by the provider author but in general can contain any code providing it does not itself start with aWITHor contain anotherENDUSE.
For example, the following query for the Drive.Csv direct provider accepts today's date and uses it to assemble a table of results extracted from data stored in a CSV file with a matching name in Drive. The arbitrary syntax accepted by this provider happens to be a set of options each preceded by -- characters:
@@today = select strftime('%Y%m%d', 'now');
@table_of_results = use Drive.Csv with @@today
--file=/trade-files/eod-{@@today}.csv
--noHeader
--names=Equity,Units,Cost
enduse;
select * from @table_of_resultsAll direct providers authored by FINBOURNE have online help listing the options available (mandatory and otherwise) in the <arbitrary-syntax> section. To access this, simply run a query for the provider with an option in the <arbitrary-syntax> section that is not handled by the provider, for example:
@x = use Drive.Csv
--NotARealOption
enduse;
select * from @xAt the time of writing, the online help for Drive.Csv looks like this:
