relational.write
Write into a SQL-compatible data store
Properties
| Name | Type | Description | Required |
|---|---|---|---|
| connection (The connection to use for loading) | string | Logical connection name as defined in the connections.dy.yaml | yes |
| schema (The table schema of the target table) | string | If not specified, no specific schema will be used when connecting to the database. | no |
| table (The target table name) | string | Target table name | yes |
| keys (Business keys to use in case of `load_strategy` is UPSERT or working with `opcode_field`) | array | no | |
| mapping (Fields to write) | array | no | |
| foreach (Split a column into multiple records with a JMESPath expression) | string | Use a JMESPath expression to split a column into multiple records. The expression should be in the format column: expression. Pattern: ^(?!:).*:.*(?<!:)$ | no |
| opcode_field | string | Name of the field in the payload that holds the operation (c - create, d - delete, u - update) for this record in the DB | no |
| load_strategy | string | type of target Default: "APPEND"Enum: "APPEND", "REPLACE", "UPSERT", "TYPE2" | no |
| active_record_indicator | string | Used for TYPE2 load_strategy. An SQL expression used to identify which rows are active | no |
| inactive_record_mapping (Used for `TYPE2` load_strategy. The columns mapping to use to close out an active record) | array | A list of columns to use. Use any valid SQL expression for the source. If ‘target’ is omitted, will default to the name of the source column Default: | no |
Additional Properties: not allowed
No properties.
Not [required1]: No properties.
Example
id: load_snowflake
type: relational.write
properties:
connection: eu_datalake
table: employees
schema: dbo
load_strategy: APPEND
keys[]: Business keys to use in case of `load_strategy` is UPSERT or working with `opcode_field`
Items: name of column
No properties.
Example
- fname
- lname: last_name
mapping[]: Fields to write
Items: name of column
No properties.
Example
- fname
- lname: last_name
- address
- gender
inactive_record_mapping[]: Used for `TYPE2` load_strategy. The columns mapping to use to close out an active record
A list of columns to use. Use any valid SQL expression for the source. If ‘target’ is omitted, will default to the name of the source column
No properties.
Example
- source: CURRENT_DATE
target: deletedAt
- source: '''Y'''
target: is_active