Instance Metadata
The instance metadata stored for each pattern in a project is made available in the meta database via views. Each pattern has a view through which the metadata of all instances of a pattern can be read. The view's schema contains the pattern's instance properties and a few technical attributes. The technical attributes include:
| Attribute | Data Type | Description |
|---|---|---|
| @PATTERN | string | The name of the pattern |
| @FOLDER | string | The folder path to the instance |
| @INSTANCE | string | The name of the instance to which the metadata belongs |
| @RELEASE | string | Name of the release in which the metadata is stored |
| @POS_NO | integer | Position number of the metadata record within the instance. Corresponds to the order of the records in the instance table |
| @DELETED | integer | A flag indicating whether the instance is deleted(1) or not(0) |
As you can see, the technical attributes have the prefix @. This is because they must be distinguished from the instance properties. Since instance properties cannot have an @ in their identifier, this ensures that they can be distinguished.
If you have enabled release management for a pattern, the @RELEASE column takes on a special meaning. If an instance exists in two releases, the instance metadata view will output both states and display the name of the corresponding release under @RELEASE. The state of the metadata is always the current working state within the respective release, i.e. the state you see when you open the instance in a release.
Inserting Instance Metadata
Unlike other views of the information schema, it is possible to insert data into the instance metadata views using an INSERT statement. In this case, the inserted records are grouped according to @FOLDER and @INSTANCE, and a new instance is created in the specified folder with the specified name. If an instance already exists at this location, it is overwritten, i.e. the previous instance metadata is deleted and the newly inserted instance metadata is transferred to the instance.
INSERT Syntax
INSERT INTO @INSTANCES.pattern_name (@FOLDER, @RELEASE, @INSTANCE, @POS_NO, FUNCTIONAL_ROLE, NAME, list_of_instance_properties...)
queryArguments
pattern_name: The name of the pattern for which you want to insert the instance metadata.
column list: A list of all inserted columns. The list must at least contain the technical columns
| Column name | Description |
|---|---|
| @FOLDER | Name of the target folder of the instance |
| @RELEASE | Name of the target release |
| @INSTANCE | Name of the instance |
| @POS_NO | Position Number of the inserted metadata row inside an instance, defines the order of the metadata in an instance |
| FUNCTIONAL_ROLE | The functional role of a metadata row |
| NAME | The name of the metadata record (defines the key together with the role) |
As you can see, the instance properties FUNCTIONAL_ROLE and NAME are required in the column list. Further, the remaining instance properties of the pattern could be listed, but this list does not have to be complete.
list_of_instance_properties: The list of the instance properties for the pattern.
query: A valid select statement which returns the instance metadata which should be inserted. The order of the selected expressions must match to the list of insert columns defined in the column list.
Insert Merge Strategies
However, the behaviour of overwriting metadata can be configured individually for each functional role of the pattern. With the so-called merge strategy of a functional role, it is possible to change the behaviour when an instance is present. There are four different merge strategies:
| Merge Strategy | Behaviour |
|---|---|
| Overwrite | This is the default strategy; in this case, the metadata is overwritten. |
| Retain | If Retain is selected, the metadata rows of the previous instance that are also assigned this functional role are not overwritten but retained. The metadata rows of the inserted data set that have this functional role are ignored. |
| Merge | With this strategy, all metadata rows are inserted, but all previous rows with this functional role are also retained. If there was already an existing entry with the same key FUNCTIONAL_ROLE + NAME for a new row, the values of the newly inserted data record are adopted in this case. |
| Add New | With this strategy, only metadata rows for which there is no previous entry in the instance for the key FUNCTIONAL_ROLE + NAME are inserted. |
Below is an example illustrating the behaviour of an insert with merge strategies other than overwrite.
OVERWRITE Option
If you want to deactivate the merge strategies for an insert in order to insert an instance from scratch, you can do so with the OVERWRITE option in an INSERT statement. In this case, the merge strategy Overwrite applies to all functional roles and the instance metadata is completely overwritten.
There is an example of the OVERWRITE option below.
PARTIAL Option
Furthermore, it is possible to partially supplement an instance for individual functional roles. This can be done using the PARTIAL option. In this case, only the metadata rows of the instance whose functional roles are contained in the inserted data set are overwritten. All other metadata rows of the instance are retained. The merge strategy also applies here, i.e. if you insert rows with a functional role for which Add New is configured, the new rows are also supplemented and the previous rows remain unchanged. An INSERT PARTIAL can be used to perform an update on instance metadata. However, a dedicated UPDATE statement is not possible on the instance metadata view.
Below is an example of the PARTIAL option.
Examples
For the following examples, let us assume that we already have an instance named “ACCOUNT” in a folder named “/SRC”. The corresponding pattern is called “LDG_PATTERN”. The following shows the instance metadata for the instance so far:
| FUNCTIONAL_ROLE | NAME | DATA_TYPE | EXPRESSION |
|---|---|---|---|
| SRC_TABLE | Account_Data | ||
| RELATED_TICKET | 4711 | ||
| SRC_FIELD | ACC_NAME | string | |
| SRC_FIELD | ACC_ADDRESS | string | |
| SRC_FIELD | REGISTRATION | timestamp | |
| DRV_TGT_FIELD | LOWER_NAME | string | LOWER(ACC_NAME) |
The following screenshot shows the instance in MetaKraftwerk:
As you can see, there are four different functional roles in the instance metadata. In the example, these have the following merge strategies:
| Functional Role | Merge Strategy | Comment |
|---|---|---|
| SRC_TABLE | Retain | Once the user has defined a source table, it should be retained, even when inserting instance metadata. |
| RELATED_TICKET | Add New | Related tickets should be added so that new tickets (e.g. Jira, etc.) are added to the instance over the course of development. |
| SRC_FIELD | Overwrite | The source fields should be completely overwritten with the new metadata. |
| DRV_TGT_FIELD | Merge | Derived target fields should be merged, i.e. new ones added and existing ones merged or retained. |
SELECT Example
The following query retrieves the instance metadata for the pattern LDG_PATTERN.
SELECT * FROM @INSTANCES.LDG_PATTERN;| FUNCTIONAL_ROLE | NAME | DATA_TYPE | EXPRESSION |
|---|---|---|---|
| SRC_TABLE | Account_Data | | |
| RELATED_TICKET | 4711 | | |
| SRC_FIELD | ACC_NAME | string | |
| SRC_FIELD | ACC_ADDRESS | string | |
| SRC_FIELD | REGISTRATION | timestamp | |
| DRV_TGT_FIELD | LOWER_NAME | string | LOWER(ACC_NAME) |