A provider is a component that 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.
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 FINBOURNE 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:
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 we consider 'main' (or key) for instruments:
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.Writer, which as the name suggests upserts instruments into LUSID's instrument master from a table of data).
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).
A Luminesce query for a direct provider uses an arbitrary syntax, which may differ for each provider. It accepts a set of tabular or scalar variable inputs and returns a table of results. The general form is as follows:
select * from @variable
Note the following:
- The WITH keyword is optional but useful for passing either tabular or scalar variable inputs into the query.
- The arbitrary syntax between the USE and ENDUSE keywords is determined by the provider author but in general can contain any code providing it does not itself start with a WITH or contain another ENDUSE.
For example, the following query for the FINBOURNE 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:
@table_of_results = use Drive.Csv with @@today
select * from @table_of_results