Write commands

Write commands are used to emit output to a destination. Multiple write commands may be used within a pipeline. The WriteCommand is a filter, it also outputs the input record to the next FilterCommand.

The following diagram shows an illustration of Write Commands.

Writing records

Writing records is accomplished through the WriteCommand, which writes records out to an endpoint. Several writers may exist in a single pipeline since a writer is a type of filter and so passes objects along to the next filter in the pipeline as well as writing out to the endpoint. Output from the writer are the same as the input, unless metadata is pinned upon output.

The following table lists the write command parameters.

Some WriteEndpoints require WriteFormat to be specified (for example, Write-File must specify EIWM, TEXT and so on), whereas others do not require this (for example, Write-SqlServer).

A write endpoint such as a WriteFileEndpoint usually sorts input into an order suitable for efficient output (to avoid writing many files at the same time). This sorting is completely internal, however, outputs from any filter will be in the same order they came in, unless that filter specifically changes that order for the stream (such as a Sort).

Write endpoints

File

The file endpoint writes records out to 0-n files.

Folder and file are evaluated to determine if writing to a single file or many files, and the output format will help determine whether all fields need to be identified before data may be written out. A writing strategy will be determined as appropriate to the expected output, and to maintain efficiency this endpoint will work out enough information to write out entire files sequentially rather than skipping from file to file. This means it will not open many file handles and will avoid associated negative disk performance impact.

The following table lists the write file endpoint parameters.

When processing a larger set of data, too large for the buffer size of the write file operation to hold, data will be serialised in memory, and subsequently to disk if the memory store limit is exceeded.

In general, a memory store limit of 50 MB is expected to be enough for 1 million tags with 50 attributes data, since data is compressed.

Write to several folders and files by rules

Example:

<write>

<file folder="output\{{ @area_.fallback('general') }}\{{ @discipline.fallback('general') }}"

file="{{ @class.fallback('unknown') }}.csv">

<text template="csv" />

</file>

</write>

Creates one folder per area, beneath that one folder per discipline, and within each discipline folder one CSV file per class, with the specified default values where area, discipline or class is not available.

This can be used with a read command to read many EIWM files and convert them to this output.

The following table lists the write file endpoint templates.

SQL Server

The SQL Server endpoint performs bulk updates to 0-n SQL database tables.

The following table lists the write SQL server endpoint parameters.

The following table lists the KeyFieldsConfig Parameters in detail.

The following table lists the KeyFieldConfig Parameters in detail.

Batch update SQL server using write SQL server endpoint

Example:

<write>

<sqlserver

name="Update_EDW_Rep_Attributes"

timeout="{{ $[sql-timeout-seconds] }}"

buffer-size="{{ $[sql-buffer-size] }}"

connection-string="{{ $[!reporting database] }}"

update-timestamp="{{ $[update-timestamp] }}"

target-table="EDW_{{ $[table-type] }}_Attributes"

date-created-field="CreatedDate"

date-updated-field="LastModified">

<keys>

<key name="ObjectID" />

<key name="AttributeID" />

</keys>

</sqlserver>

</write>

Following is the default method used to update:

1. Open SQL Connection.

2. For each Target-Table that results from the evaluated expression, bulk Copy N records to a #temp table (expected performance < 1 ms for 10,000 records).

3. When completed, run SQL MERGE UPDATE to update main table from #temp table.

   If capturing history, capture changes records.

4. Close SQL Connection.

   This should keep all SQL operations to a reasonable time.

Waypoint

Write Waypoint Endpoint updates a waypoint with received records.

When new record data is received, if the original record is not updated (that is, the new record version has the same fields and attributes as the existing record), then the existing record will not be updated.

When a record is updated in the waypoint, the last update timestamp for the record is updated.

If the same input is loaded successfully twice in a row, the second update will check existing data and determine that no changes have been made, so will perform no updates.

The following table lists the Write Waypoint Endpoint parameters.

Records are persisted with attributes and all metadata except $[this.#temp] and $[this.waypoint.*] metadata.

If a load fails halfway through, for example, if the server crashes or is rebooted, it may have updated some data. To recover, the same load sho uld be rerun, which will detect earlier updates (and not do anything) and apply any outstanding updates.

The Waypoint update mechanism favors updates over additions, and for a managed execution environment, folder store takes out a pessimistic insert lock as soon as a new object key is identified. This may result in an insert lock of 20-30 min for 1,000,000 objects. However, update locks are taken out at a block level (a batch of for example, 10,000 objects) and updates to a block should be sub second. Hence, an update which does not add new objects should complete swiftly.

Write to a Waypoint

Example:

<read name="read_existing">

<waypoint

name="read_existing"

folder="{{$[doc_waypoint_path]}}"

include-content="false" />

<pin>

<metadata name="#sort-order" value="1!!" />

<metadata

name="id"

value="{{ coalesce($[this.id], $[this.waypoint.key]) }}" />

<metadata name="#existing" value="y" />

</pin>

</read>

<read name="read_latest">

<file

name="read_latest"

folder="{{ $[working-area] }}"

file="register_null.xml">

<eiwm />

</file>

<pin>

<metadata name="id" value="{{ @DocumentNumber }}" />

<metadata

name="#sort-order"

value="2!!{{ $[index].padnumbers(9) }}" />

<metadata name="#changed" value="y" />

</pin>

</read>

<sort key="{{ $[this.id] }}!!{{ $[this.#sort-order] }}" />

<merge

key="{{ $[this.id] }}"

int

key="{{ $[this.id] }}"

delete="{{ $[this.#changed] not equals 'y' or $[this.#deleted] equals 'y' }}"

folder="{{$[doc_waypoint_path]}}" />

</write>

Console

The write console endpoint will write records to the console standard out. This is included for testing in a console application.

This should be modified to write to the pipeline host’s UI rather than specifically the Console, which could then be used to emit output for example, into a web page.

The following table lists the write console endpoint parameters.

Write formats

This section describes about Write Formats, such as excel, binary, and text delimited.

Text delimited

Writes a delimited text file such as CSV or TAB delimited.

The following table lists the write text format parameters.

The following table provides the descriptions about the Write text format templates.

Excel

You can write the Excel (OpenXML) files with cell or row coloring or formatting options. By inserting an expression into the target folder, file and sheet names, data may be organised very flexibly.

Scenarios for use are as follows:

• Export of data (where rules may divide data into multiple sheets such as one sheet per discipline).

• Export to other system which accepts Excel.

Binary

The Binary format allows writing out binary content to files.

The following lists about write binary format parameters.

Extract DWG files from AVEVA P&ID database and write to folder

Example:

<pipeline id="get_drawings">

<variable name="!dbServer" value="{{ '' }}" overridable="true" />

<variable name="!dbName" value="{{ '' }}" overridable="true" />

<read>

<sql-server

connection-string="Server={{ $!dbServer }};Database={{ $!dbName }};Trusted_Connection=True;"

include-binary="true">

<query>

<![CDATA[select * from SynchroniseDetails]]>

</query>

</sql-server>

</read>

<write>

<file

folder=".\drawings_{{ $prj }}"

file="{{ @[DrawingName] }}.dwg">

<binary include-attribute="{{ $[attr.name] equals 'DrawingData' }}" />

</file>

</write>

</pipeline>

Extract DWG symbols from AVEVA P&ID and write to folder

Example:

<pipeline id="get_project_symbols">

<variable name="prj" value="OSBL" />

<read>

<sql-server

connection-string="Server=localhost;Database=SHELL_TRIDENT_PID_{{ $prj }}_ADMIN;Trusted_Connection=True;"

include-binary="true">

<query>

<![CDATA[

select * from prjSymbols

]]>

</query>

</sql-server>

</read>

<write>

<file folder=".\project_symbols_{{ $prj }}"

file="{{ @[symbol_name] }}.bmp">

<binary include-attribute="[

select * from prjSymbols>

</read>

<write>

-attribute="{{ $[attr.name] equals 'pic_binary' }}" />

</file>

</write>

<file="{{ @[symbol_name] }}.dwg">

<binary include-attribute="{{ $[attr.name] equals 'symbol_binary' }}" />

<pin>

<metadata name="filename" value="{{ $[file.name] }}" />

<metadata name="filepath" value="{{ $[file.path] }}" />

</pin>

</file>

</write>

<select>

<fields where="{{ $[attr.name] not contains 'binary' }}" />

</select>

<write>

<file

folder="."

file="project_symbols_{{ $prj }}.csv">

<text template="csv" />

</file>

</write>

</pipeline>

The EIWM template meets the standards and conventions of AIM.

For example, you want to publish OBJECT REGISTRATION details (that is, ID, Name, and Classification) without any attributes or associations. The Registers Gateway publishes attributes and associations without classification and name metadata.

The Registers Gateway requires some control over specifying dataset details, though in the most configurations this may be determined by, for example, source or some prior processing, so being able to map the data model 'dataset' for attribute values to a dataset easily will make a lot of configuration simple.

Note: This may be used without knowledge of the EIWM format.

The following table lists the write EIWM format parameters.

The following table lists the write EIWM format templates.