Author Archives: Milan Krivanek

Container Types: List and Map Metadata Fields

Leave a reply

List and map metadata fields are part of CloverETL’s effort to enhance its support for both rich data structures and unstructured data. This enhancement eases the previously rigid metadata model, which required each processed data record to be exactly described to the last item. With the new version, data coming from systems where the structure of record varies from record to record (as are CRMs, social networking systems) can now be easily processed in CloverETL by utilizing the variable length lists – or even more flexible maps.

This new feature called container types was added in 3.3.0 M2. Any field can now operate in three modes:

  1. single value: normal field, stores one value of the selected type
  2. list: the field can hold a list of values of the selected type
  3. map: the field is a map from strings to the selected type

Container fields can be read or written to in CTL, just like with any other list or map. In addition, some components can read or write data into container fields or use them in transformations.

XMLReader and XMLWriter Examples

For example, XMLReader can map multiple occurrences of a sub-element to a list field. That means that the XML snippet below:

<?xml version="1.0" encoding="ISO-8859-1"?>
<root>
    <ParentElement>
        <Number>-62.13571388346442</Number>
        <Number>13.465953975177856</Number>
        <Number>-188.61795040912824</Number>
        <String>lists</String>
        <String>in</String>
        <String>metadata</String>
        <String>work</String>
        <String>now</String>
    </ParentElement>
    <ParentElement>
        <Number>106.86947999682504</Number>
        <Number>78.20039253465931</Number>
        <Number>-40.87408457269909</Number>
        <String>string list</String>
        <String>element</String>
    </ParentElement>
</root>

with the following mapping:

<Context xpath="/root/ParentElement" outPort="0">
    <Mapping xpath="Number" cloverField="Numbers"/>
    <Mapping xpath="String" cloverField="Strings"/>            
</Context>

will produce the following output:

Similarly, the lists can be written out using XMLWriter.

Container fields can also be processed in CTL:

function integer transform() {
    $out.0.concatenatedStrings = join("+", $in.0.Strings);

    return OK;
}

Thus the map and list metadata fields give you the needed flexibility to process more complex and variable input than before.

Download a CloverETL project with this example

You Name It!: Metadata Field Labels

Leave a reply

Have you ever wanted to name a metadata field using more than one word? Or give it a name with national characters, perhaps? Or any other non-alphanumeric characters? Now you can, with labels for metadata fields introduced in CloverETL 3.2.

Metadata Extraction

As was already mentioned, the names of fields in CloverETL are only allowed to contain basic characters A-Z (both upper and lower case), digits 0-9, and underscores. In addition, the names cannot start with a digit and must be unique. When trying to import metadata with other characters (e.g. from a delimited flat file or a spreadsheet), the names are normalized to satisfy the requirements above.

In CloverETL 3.2, a new attribute was added to metadata fields: the Label. The attribute is used to preserve the original names of fields as they were before the normalization. Some writers can then use the labels when writing out the data (e.g. UniversalDataWriter or XLSWriter).

In the picture below, the names of fields were normalized to the “Clover-friendly” format after metadata extraction, while the labels contain the original names of columns, as they were in the DBF file. The same principle works for any metadata source (database, XSD, XLS…). Some labels were manually deleted – those are now displayed in light gray to indicate that the default values of labels are identical to the values of field names.

Usage of Field Labels

Having extracted metadata with labels from some source, what can we do with it? Let us illustrate it in a few examples.

To copy data from a database to a delimited flat file, extract metadata from the database, use UniversalDataWriter to write out the data, and enable the “Write field names” property.

To copy data from a flat file to a XLS spreadsheet, extract metadata from the flat file, use a XLSDataWriter, and set the “Metadata row” attribute to “1”. The same applies to the upcoming SpreadsheetDataWriter components (as of CloverETL 3.3).

To load data from a spreadsheet into a database (preserving the names of columns), first extract metadata from the spreadsheet. Then create a new table in the database by right-clicking the metadata and selecting “Create database table”.

Then just use a DBOutputTable to insert the data into the table.

CTL

In a Reformat and other components which use CTL, the label of a field can be obtained with one of the CTL functions for dynamic field access: getFieldLabel().

The CTL source snippet above can be used to produce the following output:

As you can see, labels can be used to preserve the original names coming from data sources that support arbitrary text for a field name (Excel spreadsheets, databases, etc.). In future versions, the plan is to integrate labels more into components and the Designer for an even better user experience.

Processing Heterogeneous Data with ComplexDataReader

Leave a reply

ComplexDataReader – Example How-to

ComplexDataReader is a new component for reading heterogeneous data (data which contains multiple types of records that can also depend on each other) without the need of hard coding. Instead, the component is driven by a state machine which can be set-up using the GUI.

The following example will present some of the capabilities of ComplexDataReader, as well as guide you through the design of a simple automaton, which is used for processing a text file containing two types of shipments grouped into batches. Each batch starts with a batch header; the number of items in a batch is variable and it is part of the header.

Input Data

Input Data for ComplexDataReader

What We Want to Achieve

For every parcel and every letter, send to the output the address and the charge to the output, also add the batch ID, customer ID, and the date from the respective batch header.

The first element of a batch header determines the type of its elements, and the third element contains the number of items in the batch.

Preparation

Before starting the configuration of the component, all the required metadata should be defined. Also, the component should be connected to the succeeding component(s) and the output edge(s) should have metadata assigned.

You may also set the “File URL” property of the component to point to the input file.

Internal metadata (used for parsing the input):

Batch Metadata

Parcel Metadata

Letter Metadata

Output metadata (used for output mapping):

Shipment Metadata

ComplexDataReader Configuration

First, we have to design an automaton, which will guide the component through parsing the input. The automaton may look like this:

ComplexDataReader Automaton

The idea behind it is that we start by reading a batch header, therefore the initial state is set to “$0 – Batch”. Then we can decide, depending on the value of the “type” field, whether to proceed to “$1 – Letter” or “$2 – Parcel”. In either of these states, we read as many records as specified in the “count” field of the previous batch header, then return to “$0 – Batch” and expect a new batch header.

To start building the automaton, open the configuration dialog by double clicking the component and then its “Transform” property.

Create three states by dragging the “batch”, “letter” and “parcel” metadata, respectively, from the list of Available Metadata on the left to the list of States on the right. You can also edit the labels of the states. Set the Initial state to “State $0″ by selecting it from the drop-down list.

Optionally, you may switch to the Overview tab and press the Undock button to get an interactive overview of the automaton being built.

Switch to the State $0 tab. This state represents a new batch. Set the automaton to reset the counters for state $1 and $2 by pressing the Actions button and ticking Reset counter for “State $1″ and “State $2″. Add two rows to the Transition table. Set the condition of the first row to $batch.type == "LETTERS" and the condition of the second row to $batch.type == "PARCELS". Set their target states to “State $1″ and “State $2″, respectively. You may also set the target of the default transition to Fail to detect unexpected batch types.

Note that in state $0, no output mapping is defined; hence no data will be sent to the output.

The configuration of state $1 and $2 will be very similar. In these states we want to produce output, therefore we have to define output mapping. For example, in state $1 we need to send to the output “address” and “charge” fields from internal record $1 (last letter record) and “batchID”, “customerID” and “date” from internal record $0 (last batch header record).

For state $1, define Output mapping by dragging row “$1″ from the left table onto “Port 0″ in the right table. Then expand row $0 on the left and Port 0 on the right and drag “batchID”, “customerID” and “date” from the left onto “$0.batchID”, “$0.customerID” and “$0.date” on the right, respectively.

Add one row to the Transition table and set its condition to counter1 < $batch.count and its target to “State $1″. Also set the target of the default transition to “State $0″.

Letter State

Similarly, for state $2, drag row “$2″ onto “Port 0″ and “batchID”, “customerID” and “date” from row $0 onto “$0.batchID”, “$0.customerID” and “$0.date”. Add one row to the Transition table and set its condition to counter2 < $batch.count and its target to “State $2″. Again, set the target of the default transition to “State $0″.

Parcel State

Download the transformation graph with data