Extract Record Processing

A process that selects records and produces output for each record to write to a file to integrate with external systems is called an Extract Process. The base batch control Plug-in Driven Extract Template (F1-PDBEX) may be used as a template.

Fastpath:

Please read the topic Ad hoc Processes. Its information about the Select Records plug-in and some of the information about the Process Record plug-in apply to extract processes. This topic focuses on aspects that are unique to extract processes.
The system provided process includes parameters to configure the file path and file name for the created file. The file path supports all the functionality described in the topic Referencing URIs. The file name supports system substitution variables, such as run number, thread number, user and date / time. The process also supports compression if an appropriate extension is used. Refer to the File Name parameter description in the batch control for more information. Also see sections below for additional information for XML or JSON ouput formats.

Note:

Refer to Flexible File Name / Writing Multiple Files for information about support for substituting a business value into the file name.

For extract processes, the Process Record algorithm is responsible for returning the data that should be written to the file in one or more XML instances along with the schema name(s) that describes the XML instance(s). The program will write the data to the file as per the format indicated in the File Format batch parameter.

By default the service uses the OUAF format for date and time. To override this and use XSD format, configure the Date Time Format batch parameter to 'XSD''.

If an existing schema satisfies the output requirements, it may be used. Otherwise, you should define a data area to indicate the output format of the records as appropriate. Note that you should define an appropriate Field for each element in the schema. This is needed for the fixed position and for JSON / JSON lines formats, but is good practice for any output format. Refer to F1-PDBGenProcExtractRecord for an example of a data area with fields defined for each schema element.

There are two options for designing and configuring the Process Record algorithm:

  • Create a specific Process Record algorithm type that encapsulates the extract logic. The product provides a base algorithm type that illustrates the basic technique to follow. Refer to the algorithm type General Process - Sample Process Record Extract (F1-GENPROCEX) for more information.

  • Create a file integration type that defines the records to be included in the extract and configure a plug-in driven batch control that references that integration type. Refer to Extract Using File Integration for more information about implementing this type of functionality.

Your specific edge product may provide other Process Record algorithm types out of the box. Use the Algorithm Entity portal to find examples.

XML Format - Additional Considerations

When using XML format for the output, the batch process includes a parameter to define the Grouping Name. This is the root node to use to surround the whole output file. Refer to the batch parameter description for more information. When requesting a Multi-threaded Extract and using the concatenation option, the Grouping Name surrounds the full content of the concatenated output.

Each call to the Process Record plug-in returns one or more schemas with content. The content is written within a node using the schema name.

Your Process Record plug-in may return multiple schemas with information that is all part of the same overall record. For example, the output could include account information and related service agreement details:
<root>
...
<CM-AccountRecord>
<account type="group"
    <accountId>1234567890</accountId>
    ...
</account>
</CM-AccountRecord>
<CM-SAInfo>
<sa type="group"
    <saId>123457665</saId>
    ...
</sa>
</CM-SAInfo>
<CM-SAInfo>
<sa type="group"
    <saId>1234588913</saId>
    ...
</sa>
</CM-SAInfo>
...
</root>
In this case, it may be desired to wrap all that information in a grouping XML tag so that all the information for one record is grouped together. Because the Process Record plug-in could be returning header or footer records that are not part of a given selected record's extract information, the batch process doesn't know what schemas returned by the Process Record plug-in belong together logically. The output parameter 'record XML node' in the schema collection should be used to indicate the outer XML node to use to group related information together. In this example, one call to the Process Record plug-in returns results with the node 'record' populated for the account and service agreement schemas:
<SchemaInstance>
<recordXMLNode>record</recordXMLNode>
<schemaName>CM-AccountRecord</schemaName>
<schemaType>F1DA</schemaType>
<data><account><accountId>1234567890</accountId>...</data>
</SchemaInstance>
<SchemaInstance>
<recordXMLNode>record</recordXMLNode>
<schemaName>CM-SAInfo</schemaName>
<schemaType>F1DA</schemaType>
<data><sa><saId>123457665</saId>... </data>
</SchemaInstance>
<SchemaInstance>
<recordXMLNode>record</recordXMLNode>
<schemaName>CM-SAInfo</schemaName>
<schemaType>F1DA</schemaType>
<data><sa><saId>1234588913</saId>... </data>
</SchemaInstance>
All schemas returned from a single call to the Process Record plug-in that have the same record XML node will be grouped in the written output within that XML tag. This snippet shows the results of two calls to the Process Record plug-in.
<root>
<record><CM-AccountRecord>
<account>
    <accountId>1234567890</accountId>
    ...
</account>
</CM-AccountRecord>
<CM-SAInfo>
<sa>
    <saId>123457665</saId>
    ...
</sa>
</CM-SAInfo>
<CM-SAInfo>
<sa>
    <saId>1234588913</saId>
    ...
</sa>
</CM-SAInfo>
</record>
<record>
<CM-AccountRecord>
<account>
    <accountId>987654320</accountId>
    ...
</account>
</CM-AccountRecord>
<CM-SAInfo>
<sa>
    ...
</sa>
</CM-SAInfo>
</record>
</root>

JSON / JSON Lines Format - Additional Considerations

The extract process supports two types of JSON output.

  • JSON Document. This format produces a valid JSON document. This output format supports returning a single JSON object with the records defined as an array or as an array of records.
  • JSON Lines. The format produces a series of JSON objects. Each output record is a separate JSON object.

Note:

These output formats do not support including a header or footer as part of the extract.

Note:

The recommendation is to return only one schema instance for each call to the Process Record plug-in spot. This is because the service that converts from XML to JSON must have a schema name that describes the payload. If multiple schema instances are returned, it treats each returned instance independently and doesn't support nesting or other relationships between the data. For example, if the output returns one instance with Account data and then 3 instances with Service Agreement (SA) data (related to the account), in delimited, fixed position and XML, those four instances can be written one after the other. However, in JSON, the data for the 3 SA instances should be returned as an array within the Account object. In order to support this, the Process Record plug-in should have a governing schema that has the Account and the SA list all defined within it.

JSON Document - Single JSON Object

If you provide a Grouping Name in the batch parameter, the program will produce a JSON object which contains an array of the output records using the grouping name as the array name.

For example, imagine the Grouping Name is 'records' and the output from one call to Process Records is this:

<SchemaInstance>
<schemaName>CM-FileInfo</schemaName>
<schemaType>F1DA</schemaType>
<data><files><fileName>file1.txt</fileName><externalReferenceId>1234</externalReferenceId></files></data>
</SchemaInstance>

Now imagine three calls to the Process Record plug-in with separate results. In this case the 'files' node is the group node for the content of each call. This is mapped to the 'records' array name.

{
 "records": [
  {"fileName": "file1.txt", "externalReferenceId": "1234"},
  {"fileName": "file2.txt", "externalReferenceId": "3456"},
  {"fileName": "file3.txt", "externalReferenceId": "5678"}
 ]
}

The following are points related to how the schema returned by Process Records may be defined and how the JSON output format handles the use case. In all cases, the assumption is that the batch parameter Grouping Name is defined as 'records'. And in each case, the Resulting File Output imagines three calls to Process Records.

Table - JSON Output

Use Case Schema Definition Resulting File Output

If the output schema contains a single group, output returned by the plug-in is grouped by the a group node. This node does not have importance when writing the record to file and the group node is removed. The record is written directly inside the JSON array.

 
<schema>
    <group1 type="group">
        <fileName dataType="string"/>
        <externalReferenceId dataType="number"/>
    </group1>
</schema>

The 'group1' node is dropped and its contents are mapped to the 'records' array.

{
"records": [
{"fileName":"file1.txt","externalReferenceId":"1,234"},
{"fileName":"file2.txt","externalReferenceId":"1,234"},
{"fileName":"file3.txt","externalReferenceId":"1,234"}
]
}

If there is an outer group node in the schema which contains nested group(s) within it, the program skips the topmost group node.

 
<schema>
    <group1 type="group">
        <group2 type="group">
        	<fileName dataType="string"/>
        	<externalReferenceId dataType="number"/>
    	</group2>
    </group1>
</schema>
 
{
"records": [
{"group2":{"fileName":"file1.txt","externalReferenceId":"1,234"}},
{"group2":{"fileName":"file2.txt","externalReferenceId":"1,234"}},
{"group2":{"fileName":"file3.txt","externalReferenceId":"1,234"}}
]
}

If schema contains more than one group or a group along with other types like list, field or raw then it does not skip any group node name.

 
<schema>
    <group1 type="group">
        <fileName dataType="string"/>
        <externalReferenceId dataType="number"/>
    </group1>
    <list1 type="list">
        <fileDetails dataType="string"/>
    </list1>
	<fileSize dataType="number"/>
</schema>

All nodes are included in each entry in the array.

{
"records": [
{"group1":{"fileName":"file1.txt","externalReferenceId":"1,234"},"list1":[{"fileDetails":"Test file"}],"fileSize":"1,024"},
{"group1":{"fileName":"file2.txt","externalReferenceId":"1,234"},"list1":[{"fileDetails":"Test file"}],"fileSize":"1,024"},
{"group1":{"fileName":"file3.txt","externalReferenceId":"1,234"},"list1":[{"fileDetails":"Test file"}],"fileSize":"1,024"}
]
}

JSON Document - Array of Output

If you choose the JSON document output format and populate 'suppress' for the Grouping Name, the program will produce an array instead of an object.

To illustrate, we assume the same definitions used in the above Single JSON object format examples. The following is the output of one call to the Process Records plug-in.

<SchemaInstance>
<schemaName>CM-FileInfo</schemaName>
<schemaType>F1DA</schemaType>
<data><files><fileName>file1.txt</fileName><externalReferenceId>1234</externalReferenceId></files></data>
</SchemaInstance>

Now imagine three calls to the Process Record plug-in with separate results. In this case the 'files' node is the group node for the content of each call. This node is dropped and the output from each call is an object within the array. 

[
  {"fileName": "file1.txt", "externalReferenceId": "1234"},
  {"fileName": "file2.txt", "externalReferenceId": "3456"},
  {"fileName": "file3.txt", "externalReferenceId": "5678"}
 ]

The same considerations in the JSON Output table above apply to this configuration.

JSON Lines

For the JSON Lines output format, the Grouping Name is not applicable (and ignored). Each call to the Process Record plug-in spot is written as an object. All the considerations in the JSON Output table above apply to this configuration.

To illustration, here is the output for a single call to the Process Record plug-in spot.

<SchemaInstance>
<schemaName>CM-FileInfo</schemaName>
<schemaType>F1DA</schemaType>
<data><files><fileName>file1.txt</fileName><externalReferenceId>1234</externalReferenceId></files></data>
</SchemaInstance>

Then this is an example after three calls to Process Record. The output is written out as objects with no commas. 

{"fileName": "file1.txt", "externalReferenceId": "1234"}
{"fileName": "file2.txt", "externalReferenceId": "3456"}
{"fileName": "file3.txt", "externalReferenceId": "5678"}

Multi-threaded Extract

When extracting data, it's possible that the volume of data warrants running the job multi-threaded. In this case, a separate file is produced with the thread number included in the file name. You may proactively include the thread number as a substitution variable when indicating the file name. If not, the system appends the thread number.

As a convenience, the system supports concatenating the extract files produced by the various threads at the end. The system does this by finding files whose file names match except for the thread number. As such, the file name may not contain date or time if the concatenation parameter is true. Please note the following with respect to the concatenation feature:

  • The content of each file is concatenated together "as is". If the individual files have header, footer or summary information, there is no logic to consolidate that information in the concatenated file. If you want a single header, footer or summary, you must run the extract single threaded.
  • Concatenation is not supported if the file name indicates the zip compression should be used.
  • The individual thread files are retained.
  • If the file format is XML, the files written for the individual threads will be written with an extension of ".tmp". The final concatenated file is surrounded by the Grouping Name defined on the batch job as a parameter (if not suppressed).
  • If the file format is JSON Document, the files written for the individual threads will be written with an extension of ".tmp". The final concatenated file is treated as an object with an array defined by the Grouping Name, or treated as a single array (if the grouping name is configured as "suppress").

Skipping Records

By default, the extract process expects one or more schemas to be returned by each call to the Process Record plug-in. (Ideally when designing any process, the Select Records algorithm only selects records that should be processed and the Process Record algorithm does not need to confirm whether the record should be part of the processing or not.) If however there is a need to check a condition in the Process Record plug-in and it is found that no data needs to be extracted for this record, the process record algorithm should return the 'is skipped' output parameter to true so that the program doesn't write an empty row.

Note that the base process records algorithm for file integration types (F1-FILEX-PR) will set the ‘is skipped’ parameter to true if the file integration record extract record algorithm returns no data.

Configuring a New Process

The points documented in the topic related to configuring a new process with respect to the Select Records algorithm apply to extract processes.

For the Process Records algorithm, it is responsible for returning one or more schema instances populated with information that should be written to the file. If your edge product does not deliver a suitable plug-in, create a plug-in script where the algorithm entity is Batch Control - Process Record. Note that the plug-in receives all the information selected in the SQL defined in the Select Records plug-in.

Refer to Configuring an Extract Process Using File Integration Type for information on designing extracts using this configuration.

If a new processing script is required, define the algorithm type and algorithm for the newly created script. Create a batch control and refer to the batch template Plug-in Driven Extract (F1-PDBEX). Plug in the algorithms created above and configure the parameters as appropriate. Note that you may configure custom ad hoc parameters on the batch control if required. Both base and custom batch parameter values are available to the Select Records and Process Records plug in algorithms.

Parent topic: Plug-in Driven Background Processes