Lusid.RelationalDataset.x.x.Writer

LUSID automatically creates a Lusid.RelationalDataset.<rdd-scope>.<rdd-code>.Writer provider in the Luminesce catalog for each relational dataset definition (RDD) that exists:

You can use this provider to write a Luminesce SQL query that adds records to/deletes records from the dataset for that RDD. More information.

Your query should use the WriteAction field to perform one of the following operations:

• Upsert a record (this is the default operation if you omit WriteAction):

  • A new data series is created if any field in the primary key, or the EffectiveAt date, is different.

  • A new data point is added to an existing data series if just the EffectiveAt date is different.

  • An existing data series is updated if only a value or metadata field is different. More information.

• Delete a record.

See also: Lusid.RelationalDataset.<rdd-scope>.<rdd-code>

Basic usage

@table_of_data = <select-statement>;
select * from Lusid.RelationalDataset.<rdd-scope>.<rdd-code>.Writer 
  where ToWrite = @table_of_data;

Query parameters

Lusid.RelationalDataset.<rdd-scope>.<rdd-code>.Writer has parameters that help you construct a valid table of data to write.

Note: The ToWrite parameter is mandatory and used to actually write the table of data into LUSID.

To list available parameters, their data types, default values, and an explanation for each, run a query like this one using a suitable tool:

select FieldName, DataType, ParamDefaultValue, Description from Sys.Field 
  where TableName = 'Lusid.RelationalDataset.MyRDDs.Addresses.Writer' and FieldType = 'Parameter';

Data fields

Lusid.RelationalDataset.<rdd-scope>.<rdd-code>.Writer lists the fields you can populate in your table of data to write.

Depending on the operation you want to perform, the following fields are mandatory to include in the table of data:

To list all available fields, their data types, whether fields are considered 'main', and an explanation for each, run a query like this using a suitable tool:

select FieldName, DataType, IsMain, IsPrimaryKey, SampleValues, Description from Sys.Field 
  where TableName = 'Lusid.RelationalDataset.MyRDDs.Addresses.Writer' and FieldType = 'Column';

Write errors

We recommend examining the results of every write query using one or more of the WriteError, WriteErrorCode and WriteErrorDetail fields.

For each record in the table of data to write, an error code is returned. If the operation is successful, the error code is 0. If unsuccessful, a positive error code and explanation help you discover why LUSID considers the operation invalid.

Examples

Note: For more example Luminesce SQL queries, visit our Github repo.

Example 1: Add a record

For more information on constructing the primary key for a record, see this article.

@data_to_write = select 
  'MyScope' as SeriesScope, 
  'LegalEntity' as ApplicableEntityType,
  'MyIdentifiers' as ApplicableEntityIdentifierScope,
  'Banks' as ApplicableEntityIdentifierType,
  'X-12345' as ApplicableEntityIdentifierValue,
  'Registered' as AddressType,
  '1 Avenue Road' as Street,
  'AcmeTown' as City,
  #2025-01-01# as EffectiveAt
;
select * from Lusid.RelationalDataset.MyRDDs.Addresses.Writer 
  where ToWrite = @data_to_write;

Example 2: Delete a record

You must specify 'Delete' as WriteAction.

@data_to_write = select
  'Delete' as WriteAction, 
  'MyScope' as SeriesScope, 
  'LegalEntity' as ApplicableEntityType,
  'MyIdentifiers' as ApplicableEntityIdentifierScope,
  'Banks' as ApplicableEntityIdentifierType,
  'X-12345' as ApplicableEntityIdentifierValue,
  'Registered' as AddressType,
  #2025-01-01# as EffectiveAt
;
select * from Lusid.RelationalDataset.MyRDDs.Addresses.Writer 
  where ToWrite = @data_to_write;