Views:

Related resources:

Explanation

Tutorials

Reference

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 a data set and processes it, perhaps by upserting 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:

@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;


Note: The column names retrieved from your database must match those expected by the FINBOURNE provider (hence some have been renamed).

Hosting the Sql.Db.Mine provider

To run Sql.Db.Mine locally:

  1. Make sure your computer has .NET 6 installed.
  2. Contact us to request an archive and client certificate for your LUSID domain(s).
  3. Upon receipt, unzip the archive for each domain to a folder and copy the client certificate to the same location (note it might be preferable to locate each archive on a separate computer).
  4. 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 --config "Logging:ElasticSearchUri=>" "Metrics:Enabled=>false"
  5. 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 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:
    OptionExplanation
    --typeMandatory. 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.
    --withWriterMandatory if you include a <sql-for-writing-section> in order to write to your SQL database.
    --parameters
    <name>,<type>,<value>,[false]

    Defines scalar variables that can be passed into the custom provider. Each definition must consist of a name, data type, and an initial value that is also the default value (unless you specify false as the final argument, in which case the value is only used when setting up the provider, and not when an end user queries the published version). The name is case insensitive but camel case with initial capitals is recommended. Available data types are: Boolean, Int, BigInt, Double, Decimal, Text, Date, DateTime.

    Note each definition must be on a separate line after --parameters, so if specified this option must always be last in the list. For example:

    @x = use Sql.Db.Mine
    --type=SqLite
    --connection="MyDb"
    --parameters

    AssetClass,Text,Equity,false
    Isin,Text,GB124892739
    ----

    See also the #PARAMETERVALUE macro in the section below for inserting variables into the provider.

    --deleteProviderRemoves 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 @x

    Each 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.

  • <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. An ENDUSE 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.

MacroUse # of timesExplanation
#SELECT{{<column>}, [{<column>}…]}Many

Optimises the performance of retrieving data from your SQL database.
 

Each <column> argument consists of a column name and an SQL statement that defines how that column is populated, separated by a colon. For example:

@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 Instrument column that is populated with values retrieved from the DisplayName column in my_database_table. If the end user does not explicitly request the Instrument column in a particular query, the custom provider does not retrieve the DisplayName column from your SQL database.
 

You could omit the #SELECT macro and create this view as follows:

@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 DisplayName column from your SQL database whether the end user requests the Instrument column or not, increasing bandwidth and round-trip time unnecessarily.
 

Note you can append one or more characters to a column name to distinguish that column in the custom provider:

  • Append + to make the column a primary key.
  • Append ^ to make the column a 'main' field (one returned by a query that starts select ^ from...)
  • Append ~ followed by either Boolean, Int, BigInt, Double, Decimal, Text, Date or DateTime to explicitly set the data type.
  • Append |"<string>" to give the column a description in the catalog (only " may not appear in that description).

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>}…]}OneUse 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>}…]}ManyOptimises 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_AGGOneUse 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>)ManyInserts 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.

#LIMITOneInserts 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.
#OFFSETOneFills in the OFFSET clause as required by the SQL dialect.
#DISTINCTOneInserts 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.
#GROUPBYOneInserts 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_BYOneAllows 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 and Cost).
  • Values in the virtual Cost column are calculated on-the-fly by multiplying the database price by units 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 the RESTRICT_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 a 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 a published 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 a published 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