Lusid.RelationalDataset.Definition.Writer

The Lusid.RelationalDataset.Definition.Writer provider enables you to write a Luminesce SQL query that either creates, updates or deletes one or more relational dataset definitions (RDDs).

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

• Upsert a RDD (this is the default operation if you omit WriteAction). A RDD is created if it does not exist and updated if it does.

• Update a RDD with or without records.

• Remove fields from a RDD with records.

• Delete a RDD.

See also: Lusid.RelationalDataset.Definition

Basic usage

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

Query parameters

Lusid.RelationalDataset.Definition.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.Definition.Writer' and FieldType = 'Parameter';

Data fields

Lusid.RelationalDataset.Definition.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.Definition.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: Create a RDD

You must specify at least one field with a FieldCategory of Value and FieldRequired as True. You can specify up to 99 other SeriesIdentifier, Value or Metadata fields, which can be mandatory or optional. More information.

@fields = values
('AddressType', 'SeriesIdentifier', 'MyRDDs', 'AddressTypes', True),
('Street', 'Value', 'system', 'string', True),
('City', 'Value', 'system', 'string', False),
('Email', 'Metadata', 'system', 'string', False)
;

@data_to_write = select 
  'MyRDDs' as Scope,
  'Addresses' as Code,
  'LegalEntity,Person' as ApplicableEntityTypes,
  'Addresses' as DisplayName,
  column1 as FieldName,
  column2 as FieldCategory,
  column3 as FieldDataTypeScope,
  column4 as FieldDataTypeCode,
  column5 as FieldRequired from @fields
;
select * from Lusid.RelationalDataset.Definition.Writer
  where ToWrite = @data_to_write;

Example 2: Remove fields from a RDD with records

You cannot remove SeriesIdentifier fields, nor the last mandatory Value field. More information.

Note: To remove fields from a RDD that has no records, use 'Update' as WriteAction and specify just the fields you want to retain.

@fields = values
('City'),
('Email')
;

@data_to_write = select 
  'RemoveField' as WriteAction,
  'MyRDDs' as Scope,
  'Addresses' as Code,
  column1 as FieldName from @fields
;
select * from Lusid.RelationalDataset.Definition.Writer
  where ToWrite = @data_to_write;

Example 3: Delete a RDD

You must specify 'Delete' as WriteAction. Note any records are also deleted.

@data_to_write = select
  'Delete' as WriteAction, 
  'MyRDDs' as Scope, 
  'Addresses' as Code
;
select * from Lusid.RelationalDataset.Definition.Writer 
  where ToWrite = @data_to_write;