You can request a Sql.Db.Mine
provider from FINBOURNE and run it locally to create and publish a custom provider in your LUSID domain that is able to read from, write to, or configure your own SQL database.
Note: You can connect to the following databases: MySql, Odbc, Oracle, PostgreSql, SqLite, SqlServer, GoogleBigQuery, AwsAthena.
The goal is to enable end users to write simplified or more performant queries using the custom provider available from your domain's Luminesce catalog rather than interacting with the database directly, which they might not have the knowledge, infrastructure or permissions to do.
Note: To query your custom provider, an end user must have a valid user account in your LUSID domain, and suitable access control permissions to use that custom provider. More on permissions.
Consider the example of a CustomProviders.RetrieveInternalTransactionData
custom provider you publish that:
- Retrieves particular transaction data from your database in a highly performant manner, for example instrument names, unique identifiers, settlement dates, quantities and prices.
- Calculates the cost of each transaction on-the-fly by multiplying quantity by price.
An end user can then write a simple Luminesce SQL query that retrieves data from your SQL database and processes it, perhaps by upserting it to LUSID. For example, the following query uses CustomProviders.RetrieveInternalTransactionData
to retrieve all the transactions that occurred on 22 April 2022, and then the Lusid.Portfolio.Holding.Writer provider (supplied by FINBOURNE) to set them as holdings in LUSID.
Note: The column names retrieved from your database must match those expected by the FINBOURNE provider (hence some have been renamed).
@table_of_data = select 'Finbourne-Examples' as PortfolioScope, 'UK-Equities' as PortfolioCode, SettleDate as EffectiveAt, Quantity as Units, Currency as CostCurrency, Cost as CostPrice, Figi, 'Set' as WriteAction from CustomProviders.RetrieveInternalTransactionData where SettleDate = #2022-4-22#; select * from Lusid.Portfolio.Holding.Writer where toWrite = @table_of_data;
Contents:
- Hosting the Sql.Db.Mine provider
- Connecting to your SQL database
- Understanding the Sql.Db.Mine provider syntax
- Reading from your SQL database
- Publishing your custom provider
- Managing your live custom provider
- Deleting your custom provider
Hosting the Sql.Db.Mine provider
To run Sql.Db.Mine
locally:
- Make sure your computer has .NET 6 installed.
- Contact us to request an archive and client certificate for each LUSID domain you wish to enable.
- Upon receipt, unzip the archive to a suitable folder and copy the client certificate to the same location. Note it might be preferable to locate each domain's archive on a separate computer.
- Navigate to the
tools/net6.0/any
folder and run the following command:dotnet --additional-deps Finbourne.Honeycomb.DatabaseProviders.deps.json Finbourne.Honeycomb.Host.dll --quiet --typeFilter='^((?!SetupViewDirectProvider).)*$' --config "ElasticSearch:ElasticSearchUri=>" "Metrics:Enabled=>false"
- Authenticate to LUSID when prompted.
With Sql.Db.Mine
running locally, you can now write a Luminesce SQL query using any tool, for example the LUSID web app:
Note: FINBOURNE host a Sql.Db
provider that may also appear in your Luminesce catalog. Do not use this. Use Sql.Db.Mine
instead, which you can optionally rename (see below).
To stop Sql.Db.Mine
, press CTRL-C
. To start it again, rerun the dotnet
command (it should be faster subsequent times). Your LUSID credentials are remembered for one hour, after which you must authenticate again.
Understanding access control permissions
You, and any other LUSID administrative user wishing to run queries against Sql.Db.Mine
in order to publish custom providers for end users, must have access control permissions to use the Sql.Db.Mine
provider. The general principle of creating policies for Luminesce providers, assigning them to roles and roles to users, is explained in this article. Note you should automatically have permission if you are the LUSID domain owner with the built-in lusid-administrator
role.
You, and any other LUSID administrative user wishing to run queries, should also have access control permissions to use connection string aliases in order to connect Sql.Db.Mine
to your SQL database.
Your end users should not have access control permissions to use Sql.Db.Mine
. Instead, they require access control permissions just for those published custom providers you wish them to use.
Renaming Sql.Db.Mine
You can optionally rename the Mine
suffix (though not the Sql.Db
prefix). To do this, open <install-loc>/api-settings.json
and change:
"ProviderNameFormat": "{0}.Mine",
to (for example):
"ProviderNameFormat": "{0}.Oracle",
The new name is reflected in the Luminesce catalog the next time you sign in. Note you will need to restart Sql.Db.Mine
if you change this file while the service is running.
Connecting to your SQL database
You can connect to any supported SQL database for which you have a valid connection string.
You can hard-code the connection string in the provider (using the --connection
option), and this might be more convenient when creating and testing your provider.
For publication, however, we recommend creating a connection string alias in <install-loc>/database-provider-settings.json
, for example:
"ConnectionStrings": [ { "Name":"MyDb", "ConnectionString":"DataSource=./Tests/TestData/sakila.sqlite", "LicenceCode": "honeycomb-standard-access" } ],
You can then use the alias Name
with the --connection
option. Note you will need to restart Sql.Db.Mine
if you change this file while the service is running.
To connect via aliases you must also create a policy consisting of the following JSON, assign it to a suitable role, and assign that role to your LUSID user (or any LUSID administrative user wishing to run queries against Sql.Db.Mine
):
{ "description": "Enable the Sql.Db.Mine provider to use connection string aliases", "applications": [ "Honeycomb" ], "grant": "Allow", "selectors": [ { "idSelectorDefinition": { "identifier": { "code": "Connection/*", "scope": "Honeycomb" }, "actions": [ { "scope": "Honeycomb", "activity": "Execute", "entity": "Feature" } ], "name": "Luminesce connection string aliases", "description": "Enable the Sql.Db.Mine provider to use connection string aliases" } } ], "when": { "activate": "2022-03-03T00:00:00.0000000+00:00", "deactivate": "9999-12-31T23:59:59.9999999+00:00" } }
Getting help
You can access the latest documentation in the help page for the Sql.Db.Mine
provider at any time by running this query:
@x = use Sql.Db.Mine
--help
enduse;
Alternatively, run the following query to retrieve the help content as a table:
select distinct CustomSyntax from Sys.Registration where Name = 'Sql.Db.Mine'
Understanding the Sql.Db.Mine provider syntax
Sql.Db.Mine
is a direct provider, and therefore follows these syntactic conventions.
The precise syntax is as follows:
@x = use Sql.Db.Mine [with @@scalar_var1 [, @@scalar_var2...]] <options-section> ---- <sql-for-reading-section> ---- <sql-for-writing-section> ---- <sql-for-configuring-section> enduse; -- The following statement is optional but useful while creating the provider to test it returns meaningful data select * from @x
Note the following:
- Each
<section>
must be separated by a line consisting of at least four hyphens (and no other characters). <options-section>
An option takes the form--<option>=<value>
, for example--provider=Provider.MyProvider
. Note no spaces are allowed either side of the=
operator. If an option takes a boolean value, then specifying that option (for example--allDomains
) sets it to True; omitting the option specifies False.
The--type
and--connection
options are mandatory. To list all the options, access the help page and examine the table at the bottom. The options in the following table are particularly relevant:Option Explanation --type
Mandatory. Valid arguments are: MySql
,Odbc
,Oracle
,PostgreSql
,SqLite
,SqlServer
,GoogleBigQuery
,AwsAthena
--connection
Mandatory. This can either be:
- A connection string, for example
--connection="Data Source=./Tests/TestData/sakila.sqlite;"
- The
Name
of a connection string alias, for example--connection="MyDb"
--provider=<name>
Mandatory in order to publish your custom provider with the specified name in the Luminesce catalog, for suitably-permissioned end users to discover and query. Note it may be more performant to omit this option while creating and testing your provider. --withWriter
Mandatory if you include a <sql-for-writing-section>
in order to write to your SQL database.--parameters
<name>,<type>,<value>
,true/false[,description]Defines scalar variables that can be passed into the custom provider by end users. Each declaration:
- Must be on a separate line after
--parameters
, so if specified this option should always be last in the list. - Must have a name and data type (either
Boolean
,Int
,BigInt
,Double
,Decimal
,Text
,Date
,DateTime
). - Must have an initial value appropriate to the data type. To include quote or comma characters in a
Text
value, encapsulate it in double quote marks (see the example below). Specifytrue
as the fourth argument to make the initial value also the default value; specifyfalse
to use the initial value only when setting up the provider, and not when end users query the published version. - Can have a description that appears as a tooltip for end users in the Luminesce catalog. To include quote characters in the tooltip, encapsulate them in double quote marks:
@x = use Sql.Db.Mine --type=SqLite --connection="MyDb" --description="This is a tooltip for the provider as a whole" --parameters AssetClass,Text,Equity,true Isin,Text,GB124892739,true BondType,Text,"Vanilla, Complex and Inflation-Linked",true,"This is a parameter tooltip" AsAt,DateTime,0001-01-01,false,"This will ""appear"" as a parameter tooltip" ----
See also the
#PARAMETERVALUE
macro in the section below for inserting parameter values into the provider at the appropriate location.--deleteProvider
Removes a published provider from the Luminesce catalog. More information. --limit=<n>
--offset=<n>--groupBy=<pipe-delimited(|)-set-of-expressions-to-group-by>
--select=<pipe-delimited(|)-set-of-expressions-to-filter-by>
--filter=<filter-expression>
--distinct
Specifying these options optimises performance in conjunction with the appropriate macros (see the section below) while you are creating and testing the custom provider. Consider the following example: @x = use Sql.Db.Mine
--type=SqLite
--connection="MyDb"
--distinct
--filter=Isin is not null
--limit=42
----
select
#DISTINCT
#SELECT { { Isin : Isin } }
from my_database_table where
#RESTRICT { { Isin : Isin } }
#LIMIT
enduse;
select * from @xEach time you run this query during the development phase, a maximum of 42 different bonds with ISIN identifiers is returned, enabling you to test the provider returns meaningful data in a reasonable timeframe.
Note these options have no effect on a published provider, so you can either retain or omit them when you are ready to add the--provider
option and go live.- A connection string, for example
<sql-for-reading-section>
<sql-for-writing-section>
<sql-for-configuring-section>
Nominally optional, at least one of these sections is mandatory in order for your custom provider to perform a useful operation. They must be in the specified order. AnENDUSE
statement must terminate whichever is the last of these sections.
Reading from your SQL database
You must write a SQL query in the <sql-for-reading-section>
that is valid for the SQL database you are connecting to.
We recommend including the macros in the following table in your SQL query to optimise performance.
Note: For more information on each macro, and for available macros that are not included below (such as #CHECKACCESS
, #USERID
and #CLIENTDOMAIN
), access the help page.
Macro | Use # of times | Explanation |
#SELECT{{<column>}, [{<column>}…]} | Many | Optimises the performance of retrieving data from your SQL database. Each @x = use Sql.Db.Mine ---- select #SELECT { { Instrument: i.DisplayName }, } from my_database_table i enduse; …specifies that an end user can write a query returning an You could omit the @x = use Sql.Db.Mine ---- select DisplayName as Instrument from my_database_table enduse; …but this would impair performance. The custom provider will always retrieve the Note you can append one or more characters to a column name to distinguish that column in the custom provider:
For example: select #SELECT { { some-column-name+^~Int|"This is an important column" : sql-for-special-column }, { next-column-name : sql-for-ordinary-column }, } |
#SELECT_AGG{{<column>}, [{<column>}…]} | One | Use this macro once instead of #SELECT in order to pass any aggregation operations that an end user performs (such as count, min, max or average) down to your SQL database, further improving performance. |
#RESTRICT{{<column>}, [{<column>}…]} | Many | Optimises the performance of any WHERE clause filters specified by an end user on the named set of columns, passing the filters down to your SQL database. |
#RESTRICT_AGG | One | Use this macro once instead of #RESTRICT and in conjunction with #SELECT_AGG to optimise the performance of filters specified by the end user on the same set of columns as #SELECT_AGG (which is why this macro has no <column> arguments). |
#PARAMETERVALUE(<NAME>) | Many | Inserts the named --parameters value specified by an end user at an appropriate point in the custom provider. For example:@x = use Sql.Db.Mine
--parameter
AssetClass,Text,Equity
----
select * from my_database_table where Type is #PARAMETERVALUE(AssetClass)
enduse; Note parameters with a data type of Date or DateTime must be assigned to an intermediate scalar variable; see this example from the similar Sys.Admin.SetupView provider. This is a temporary restriction that may be removed shortly. |
#LIMIT | One | Inserts a LIMIT clause specified by an end user at an appropriate point in the custom provider, optimising performance. Since this macro has no effect if the end user does not specify a LIMIT clause, it is typically always useful to include this macro. |
#OFFSET | One | Fills in the OFFSET clause as required by the SQL dialect. |
#DISTINCT | One | Inserts a SELECT DISTINCT statement specified by an end user at an appropriate point in the custom provider, optimising performance. Since this macro has no effect if the end user does not specify a SELECT DISTINCT statement, it is typically always useful to include this macro. |
#GROUPBY | One | Inserts a GROUP BY statement specified by an end user at an appropriate point in the custom provider, optimising performance. Since this macro has no effect if the end user does not specify a GROUP BY statement, it is typically always useful to include this macro. |
#ORDER_BY | One | Allows more limits to be applied, and in the case where they exist can significantly optimise performance. Requires the presence of both #SELECT_AGG and #RESTRICT_AGG in the query. |
For example, to create our example CustomProviders.RetrieveInternalTransactionData
provider:
@x = use Sql.Db.Mine --provider=CustomProviders.RetrieveInternalTransactionData --type=SqLite --connection="MyDb" ---- -- Note the syntax in this section depends on the SQL database you are connecting to select #DISTINCT -- Using the #SELECT_AGG macro here (as opposed to #SELECT): -- 1. Avoids duplicating the work of the #RESTRICT macro -- 2. Improves performance by passing aggregate queries down to the SQL database. -- Here we rename database columns for end users, as well as defining how the Cost column is calculated #SELECT_AGG { { Name: instrument }, { Figi: figi }, { SettleDate: txn_date}, { Currency: currency }, { Quantity: units }, { Price: price }, { Cost: price * units }, } from my_database_table where -- Enables filtering on all the columns specified in the #SELECT_AGG macro. #RESTRICT_AGG #GROUP_BY #LIMIT enduse; select * from @x
Note the following:
- When run, this query publishes a
CustomProviders.RetrieveInternalTransactionData
provider in the Luminesce catalog. - An end user can filter/return seven columns from the SQL database table (
Name
,Figi
,SettleDate
,Currency
,Quantity
,Price
andCost
). - Values in the virtual
Cost
column are calculated on-the-fly by multiplying the databaseprice
byunits
columns. - If an end user writes a query that aggregates any of the columns in the
SELECT_AGG
macro, the aggregation is passed down by theRESTRICT_AGG
macro to the SQL database, improving performance. An example of such a query might be:select SettleDate, count(*) TransactionCount, max(Cost) as MostExpensive from CustomProviders.RetrieveInternalTransactionData where Currency = 'GBP' group by 1
Writing to your SQL database
Coming soon.
Configuring your SQL database
Coming soon.
Publishing your custom provider
Each time you run a query with the --provider
option, a custom provider with the specified name is automatically created and made available in the Luminesce catalog (note you may need to click the highlighted Refresh button to see it):
Note: It is more performant to omit the --provider
option while you are creating and testing your provider.
To grant end users the necessary permissions to query a custom provider, create a suitable access control policy and assign it to each LUSID end user.
Once published, an end user can write Luminesce SQL queries using any of the data fields published in the catalog, so for example:
select Name, Figi, Cost from CustomProviders.RetrieveInternalTransactionData where Currency = 'USD' and SettleDate > #2022-01-01#;
Managing your live custom provider
Once published, you can manage a custom provider by running queries, for example:
- To retrieve the original query used to create the view:
select Sql, At from sys.logs.hcquery where Sql like '%Sql.Db%' and Sql like '%RetrieveInternalTransactionData%' and Sql not like '%HcQuery%' and ShowAll = true order by At desc limit 1
- To examine the current content of the view:
select Content from Sys.File where Name like '%RetrieveInternalTransactionData%' and Extension = '.sql'
Deleting your custom provider
To remove a custom provider from the Luminesce catalog (which means it will no longer be available for end users to query), run your query with the --deleteProvider
option. For example:
@x = use Sql.Db.Mine --provider=CustomProviders.RetrieveInternalTransactionData --type=SqLite --connection="Data Source=./Tests/TestData/sakila.sqlite;" --deleteProvider ---- select 1 as deleting enduse; select * from @x
Note if using Oracle, you must supply a from
for your select 1 as deleting
statement. For example:
@x = use Sql.Db.Mine --provider=CustomProviders.RetrieveInternalTransactionData --type=Oracle --connection="Data Source=<data-source>;" --deleteProvider ---- select 1 as deleting from dual enduse; select * from @x