Writers

Writers are terminated components of graph that writes data to output source. The source can be for example a file placed on local disk, ftp, ldap, jms or database tables, etc. Graph must contain at least one of these components or more.

Common attributes

File writers

Database writers

Reads data from input port and writes them to binary file in Clover internal format. With records can be saved indexes of records in binary file (for reading not all records afterward) and metadata definition. If compress level is not set to zero, data are saved in zip file with the structure:

DATA/fileName, INDEX/fileName.idx, METADATA/fileName.fmt
Input ports:

• one input port defined/connected.

Output ports: none

Xml attributes:

Example:

  <Node id="CLOVER_WRITER0" fileURL="customers.clv" type="CLOVER_WRITER"/>
 
  <Node id="CLOVER_WRITER0" 
        type="CLOVER_WRITER"
        fileURL="customers.clv" 
        compressLevel="0" 
        saveIndex="true" 
        saveMetadata="true" 
  />

All records from input port [0] are formatted to the delimited or fixlen form and written to specified file. Type of formatting is taken from metadata specified for port[0] data flow.

Input ports:

• one input port defined/connected.

Output ports: * one optional output port defined/connected (port protocol see fileURL).

Xml attributes:

Example:

  <Node id="Writer" type="DATA_WRITER" fileURL="/tmp/transfor.out" />
 
  <Node id="Writer" 
        type="DATA_WRITER"
        fileURL="/tmp/transfor.out"
        append="true"
        charset="UTF-8"
        outputFieldNames="true"
        bytesPerFile="1000000"
        recordsPerFile="10000"
        recordSkip="10"
        recordCount="100"
  />
 
Partition usage examples:
 
 - without lookup table
   <Node fileURL="${DATAOUT_DIR}/orders" id="DATA_WRITER0" partitionKey="ShippedDate" type="DATA_WRITER"/>
 
 - with lookup table
   <Metadata id="Metadata2">
      <Record fieldDelimiter="|" name="data" recordDelimiter="\n" type="delimited">
        <Field name="dataKey" type="integer"/>
        <Field name="data" type="string"/>
      </Record>
   </Metadata>
   <Metadata id="Metadata1">
      <Record fieldDelimiter="|" name="lookup" recordDelimiter="\n" type="delimited">
        <Field name="lookupKey" type="integer"/>
        <Field name="value" type="string"/>
      </Record>
   </Metadata>
   <LookupTable id="LookupTable0" key="lookupKey" metadata="Metadata1" name="Files" type="simpleLookup"/>
       .
       .
       .
   <Node fileURL="${DATAOUT_DIR}/" id="DATA_WRITER0" partition="LookupTable0" partitionFileTag="keyNameFileTag" partitionKey="dataKey" partitionOutFields="value" type="DATA_WRITER"/>

This component performs specified DML operation (insert/update/delete) on specified database table. The metadata describing data comming in through input port[0] must be in the same structure as the target table. Parameter placeholder in DML statemet is [?] - questionmark.

Input ports:

• one input port defined/connected.

Output ports: * 0 - records rejected by database. If in this metadata there is more fields then in input metadata and last field is of type string, this field is filled by error message. Since 2.6 version component finds fields with autofilling=“ErrCode” and autofilling=“ErrText” and fills them with requested value.

• 1 - autogenerated columns (supported only for INSERT statement to Oracle, MySQL, Db2 and Informix databases, not supported in batch mode at all) and/or number of updated records. Output record is generated for each input record. In case that statement execution fails, requested fields are not filled.

Xml attributes:

insert into mytable [(f1,f2,...,fn)] values (val1, $field2, ...,$fieldm ) returning $key := dbfield1, $field := dbfield2

insert into mytable [(f1,f2,...,fn)] values (val1, $field2, ...,$fieldm ) returning $key := auto_generated, $field := infield

delete from mytable where f1 = $field1 and ... fn = $fieldn

update mytable set f1 = $field1,...,fn=$fieldn

update mytable set f1 = $field1,...,fn=$fieldn returning $outField:=update_count

  * For Oracle or Db2 database: names of database columns to be returned (for Db2 - entity columns)
  * For MySQL or Informix database: names of input record fields plus special field called "AUTO_GENERATED" to be returned  

Examples:

  <Node id="OUTPUT" type="DB_OUTPUT_TABLE" dbConnection="NorthwindDB" dbTable="employee_z"/>
 
  - example shows how to populate only selected fields within target DB table. 
    It can be used for skipping target fields which are automatically populated by DB (such as autoincremented fields).
  <Node id="OUTPUT" type="DB_OUTPUT_TABLE" dbConnection="NorthwindDB" dbTable="employee_z" dbFields="f_name;l_name;phone"/>
 
  - example shows how to simply map Clover's LastName and FirstName fields onto f_name and l_name DB table fields. 
    The order in which these fields appear in Clover data record is not important.   
  <Node id="OUTPUT"
        type="DB_OUTPUT_TABLE"
        dbConnection="NorthwindDB"
        dbTable="employee_z"
        dbFields="f_name;l_name"
        cloverFields="LastName;FirstName">
  </Node>
 
  <Node id="OUTPUT" 
        type="DB_OUTPUT_TABLE" 
        dbConnection="NorthwindDB" 
        sqlQuery="insert into myemployee2 (FIRST_NAME,LAST_NAME,DATE,ID) values (?,?,sysdate,123)"
        cloverFields="FirstName;LastName">
  </Node>
 
  <Node id="OUTPUT" type="DB_OUTPUT_TABLE" dbConnection="NorthwindDB" cloverFields="FirstName;LastName">
    <attr name="SQLCode">insert into myemployee2 (FIRST_NAME,LAST_NAME,DATE,ID) values (?,?,sysdate,123)</attr>
  </Node>
 
  - example below shows how to delete records in table using DBOutputTable component
  <Node id="OUTPUT" type="DB_OUTPUT_TABLE" dbConnection="NorthwindDB" cloverFields="FirstName;LastName">
    <attr name="SQLCode">delete from myemployee2 where FIRST_NAME = ? and LAST_NAME = ?</attr>
  </Node>
 
  - examples below show usage of "fieldMap" attribute
  <Node id="OUTPUT" 
        type="DB_OUTPUT_TABLE"
        dbConnection="DBConnection0" 
        dbTable="employee_tmp" 
        fieldMap="EMP_NO=emp_no;FIRST_NAME=first_name;LAST_NAME=last_name;PHONE_EXT=phone_ext">
  </Node>
 
  <Node id="OUTPUT" 
        type="DB_OUTPUT_TABLE"
        dbConnection="DBConnection0" 
        dbTable="employee_tmp" 
        fieldMap="$EMP_NO:=emp_no;$FIRST_NAME:=first_name;$LAST_NAME:=last_name;$PHONE_EXT:=phone_ext">
  </Node>
 
   - example below shows how to get aoutogenerated columns
  <Node dbConnection="Connection1" id="OUTPUT" maxErrors="10" sqlQuery=
      "INSERT INTO USER (U_ID,NAME ,CREATED) values ($EMP_NO, $FULL_NAME,  $HIRE_DATE);
       INSERT INTO MYEMPLOYEE (EMP_NO, FIRST_NAME, LAST_NAME,COUNTRY, SALARY, FULL_NAME) VALUES 
                              ($EMP_NO, $FIRST_NAME, $LAST_NAME, $COUNTRY, $SALARY, $FULL_NAME) 
                              RETURNING $id:=auto_generated, $Field2:=full_name;" type="DB_OUTPUT_TABLE"/>
 
  <Node dbConnection="Connection1" id="OUTPUT" maxErrors="10" sqlQuery=
      "DELETE FROM USER WHERE U_ID = $EMP_NO;
       INSERT INTO MYEMPLOYEE (ID, EMP_NO, FIRST_NAME, LAST_NAME,COUNTRY, SALARY, FULL_NAME) VALUES 
                              (id_seq.nextval, $EMP_NO, $FIRST_NAME, $LAST_NAME, $COUNTRY, $SALARY, $FULL_NAME) 
                              RETURNING $id:=ID, $Field2:=FIRST_NAME;" type="DB_OUTPUT_TABLE" errorAction="ROLLBACK"/>
 
  <Node autoGeneratedColumns="ID;FIRST_NAME;" 
        dbConnection="DBConnection2" 
        id="OUTPUT" 
        maxErrors="10" 
	sqlQuery="INSERT INTO myemployee VALUES (id_seq.nextval, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" 
        type="DB_OUTPUT_TABLE">
  </Node>

since 2.8.0

This component can be used to send email messages from a graph. Messages to be sent are read as input records. Each message can have one or more attachments.

Input ports:

• One input port containing message data

Output ports:

• Optional output port 0 - contains messages that are sent successfully
• Optional output port 1 - contains rejected messages (see Debugging options)

This component reads input records, extracts recipient address(es), subject and message body from the record and sends the message out. Default values for all headers can be specified so common e-mail attributes can be specified at design time. Successfully sent messages are passed through the component to port 0. Incomplete or rejected messages are sent to port 1 for debugging (see Debugging options).

Xml attributes:

Notes

• message attribute is a set of header fields: From, To, Cc, Bcc, Subject, additional headers and MessageBody. All these properties can be set either as field values or static text which is used when field is not set or its value is empty. See Setting properties.
• attachments attribute is a list of attachments - either specified by file name or by triplet [ fieldName, name, mimeType ] for attachments whose data come from record field - in this case `name` a `mimeType` must to be specified.
• There can be any number of attachments both from files and record fields
• You may encounter “Can't verify identity of server” error message when using smtpTLS and/or smtpSSL, especially for self-signed SMTP server. You have two options - either add the server's certificate to Java default keystore which might be a lot of work or just name your smtpServer in trustedHosts attribute. This ways TLS/SSL layer will treat the server's certificate as valid even if it is not.

Debugging options

EmailSender can detect messages that are rejected by the underlying transport layer and send notification about these failures to output port 1. There can be arbitrary metadata on output port 1. However, an attempt to copy input record fields into output port 1 record is made. Additionally, if the metadata contain special field errorMessage, then text of the error message is copied into this field.

Setting properties

Properties in message (and attachments) can contain static text and values extracted from record fields. A field value is denoted by $<field_name> construct.

For example: Let a property value be “I am $name” and field `name` in a record has a value “Peter”. Then resulting value for that property would be “I am Peter”.

Property values can have alternatives. An alernative is used when previous one contains field values which resolve to empty or null strings. Alternatives are separated by | (pipe) character (which can be escaped \| in which case it is treated as usually).

Let's see an example: Let a property be defined as “I am $name1|My other name is $name2”. If record field `name` resolves to “Peter” then resulting value is again “I am Peter”. But if `name` is empty, then the other alternative is used (in case `name2` resolves to “Jones”) and result is “My other name is Jones”. Good practise is to add a last alternative without field value so that it will always resolve to a meaningful value - e.g. “I am $name1|My other name is $name2|I don't know my name”

Attachments

Attachments are specified as a list separated by ; semicolon) of files or record fields which hold attachment data. Each entry in the list can be either a filename (e.g. /home/peter/myfile.pdf) or a triplet containing field name, attachment file name and its mime type. Example show how attachments might look like:

Examples

 
Attachments Example 1 - two files from a file system
attachments="/home/peter/myfile.pdf;/tmp/generatedfile.txt"
 
Attachments Example 2 - two files from a file system, one being passed as a record field value
attachments="/home/peter/myfile.pdf;$recordFileNameField"
 
Attachments Example 3 - two files from a file system, one being passed as a record field value with an alternative (see Setting properties)
attachments="/home/peter/myfile.pdf;$recordFileNameField|/share/defaultFile.pdf"
 
Attachments Example 4 - using data in record field `recordFileData`, specify file name as static text "myfile.pdf" and appropriate mime type
attachments="[$recordFileData,myfile.pdf,application/pdf]"
 
Attachments Example 5 - using data in record field - specify all attachment properties in record field
attachments="[$attachmentDataField,$attachmentNameField,$attachmentMimeTypeField]"
 
Attachments Example 6 - combined example
attachments="/share/staticAttachment.pdf;$fileSystemFileField,[$attachmentDataField,$attachmentNameField,$attachmentMimeTypeField]"
 
 
Simple example - possibly insecure password, To: and message body are read from record, other parts are static
 
  <Node id="EMAIL_SENDER0" type="EMAIL_SENDER"
     smtpServer="smtp.yourisp.com" smtpUser="yourid@yourisp.com" smtpPassword="mypass"
	 message="From=CloverETL test <mytest@yourisp.com>&#10;To=$fieldRecipient&#10;Subject=Hello, this is a test message&#10;body=$fieldMessageBody"/>
 
Advanced example - with password in a parameter and default values for missing record field values
 
  <Node id="EMAIL_SENDER1" type="EMAIL_SENDER"
     smtpServer="smtp.yourisp.com" smtpUser="yourid@yourisp.com" smtpPassword="${SMTP_PASSWORD}"
	 message="From=$fieldFrom|CloverETL test <mytest@yourisp.com>&#10;To=$fieldRecipient&#10;Subject=$fieldSubject|This is default subject&#10;body=$fieldMessageBody|Message body seems empty"/>

Transforms incoming data records to JMS messages using user-specified transformation class (so-called processor). The processor is supposed to implement interface DataRecord2JmsMsg. The processor implements a DataRecord2JmsMsg interface or inherits from a DataRecord2JmsMsgBase class. The processor may be specified either by class name or by inline Java code.

Default implementation of the processor org.jetel.component.jms.DataRecord2JmsMsgProperties is sufficient in most cases. It produces javax.jms.TextMessage. Body of the message is filled by content of field which is specified by bodyField component attribute. The other fields from input record are added to the message as properties. Properties have the same names as fields.

Input ports:

• one input port defined/connected.

Output ports: none

Xml attributes:

Example:

  <Node id="JmsWriter" type="JMS_WRITER" connection="dest" />

Provides the logic to update information on an LDAP directory. An update can be add/delete entries, add/replace/remove attributes. Metadata MUST match LDAP object attribute name. “DN” metadata attribute is mandatory. String and byte are the only metadata type supported. Most of the LDAP tpyes are compatible with clover string, however for instance the userPassword LDAP type is necessary to populate from byte data field. LDAP rules are applied : to add an entry, mandatory attribute (even object classe) are requiered in metadata.

Comment:
LDAP attribute may be multivaluated. Default value separator is “|” and is reasonable only for string data fields.

Input ports:

• one input port defined/connected.

Output ports: * one optional port defined/connected, for rejected records.

Xml attributes:

Example:

<Node enabled="enabled" id="LdapWriter" type="LDAP_WRITER" action="add_entry" />

commercial component since 2.8.0

Batch data record writer to QuickBase online database (http://quickbase.intuit.com/). This component wrapps the API_ImportFromCSV HTTP interaction (https://www.quickbase.com/up/6mztyxu8/g/rc7/en/#_Toc126580055).

Input ports:

• input port - written out data

Output ports:

• output port (optional) - first field has to be numeric; there generated rids (record ids) will be passed
• error port (optional) - metadata consists from - copy of input fields; error code (numeric); error message (string); batch number (numeric)

Xml attributes:

Example:

<Node id="QUICKBASE_RECORD_WRITER0" 
        type="QUICKBASE_RECORD_WRITER"
        connection="QuickBaseConnection0"
        tableId="begz47brm"
        batchSize="10"
        errorCodeField="errorCode"
        errorMessageField="errorMessage"
        batchNumberField="batchNumber"/>

commercial component since 2.8.0

Simple data record writer to QuickBase online database (http://quickbase.intuit.com/). This component wrapps the API_AddRecord HTTP interaction (https://www.quickbase.com/up/6mztyxu8/g/rc7/en/#_Toc126579962).

Input ports:

• input port - written out data

Output ports:

• error port (optional) - fields contain - copy of input fields; error code (numeric); error message (string)

Xml attributes:

Example:

<Node id="QUICKBASE_RECORD_WRITER0" 
        type="QUICKBASE_RECORD_WRITER"
        connection="QuickBaseConnection0"
        tableId="begz47brm"
        mapping="7;9;13;6;10;8"
        errorCodeField="errorCode"
        errorMessageField="errorMessage"/>

All records from input port [0] are formatted due to given mask and written to specified file. Records can be preceded by some text (header) or be trailed by a text (footer)

Input ports:

• one input port defined/connected.

Output ports:

• one optional output port defined/connected (port protocol see fileURL).

Xml attributes:

Example:

  <Node id="STRUCTURE_WRITER0" type="STRUCTURE_WRITER" fileURL="structured_customers.txt">
 
  <Node id="STRUCTURE_WRITER0" 
        type="STRUCTURE_WRITER"
        fileURL="structured_customers.txt"
        append="true">
        <attr name="header">$Filedname0; $Filedname1;</attr>
        <attr name="mask">
          <![CDATA[
          <Customer id=$customer_id>
              <last name = $lname>
              <first name = $fname>
          </Customer>
          ]]>
        </attr>
        <attr name="footer">end of file</attr>
  </Node>

All records from input port [0] are formatted to table and written to specified file or on screen.

Input ports:

• one input port defined/connected.

Output ports:

• one optional output port defined/connected (port protocol see fileURL).

Xml attributes:

Example:

  <Node id="TEXT_TABLE_WRITER0" type="TEXT_TABLE_WRITER" fileURL="customers.txt" mask="Field0;Field1"/>

All records from input port:0 are discarded. This component is deemed for debugging !

Input ports:

• one input port defined/connected.

Output ports: none

Xml attributes:

Example:

  <Node id="TRASH0" type="TRASH"/>
 
  <Node id="TRASH0" 
        type="TRASH"
        debugPrint="true"
        debugFilename="file.log"
  />

Reads data from input port and writes them to given xls sheet in xls file. If in one graph you want to write to the same file but to different sheets each XLSWriter has to have another phase
JExcel can handle with files up to ~5.7MB in flat file - for more data you have to set more memory for jvm.

Input ports:

• one input port defined/connected.

Output ports:

• one optional output port defined/connected (port protocol see fileURL).

Xml attributes:

Example:

  <Node id="XLS_WRITER0" type="XLS_WRITER" fileURL="orders.xls"/>
 
  <Node id="XLS_WRITER0"
        type="XLS_WRITER"
        fileURL="orders.xls"
        firstColumn="f"
        namesRow="2"
        sheetName="via1" 
  />
 
  <Node id="XLS_WRITER0" 
        type="XLS_WRITER"
        fileURL="orders.xls"
        firstColumn="f"
        namesRow="2"
        sheetName="via1"
  />
 
  <Node id="XLS_WRITER1"
        type="XLS_WRITER"
        fileURL="orders.xls"
        firstDataRow="10"
        sheetName="via2"
  />
 
  <Node id="XLS_WRITER2"
        type="XLS_WRITER"
        append="true"
        fileURL="orders.xls"
        namesRow="1"
        firstDataRow="3"
        sheetName="via3"
  />
 
  <Node append="true" 
        fileURL="out.xls" 
        firstDataRow="3" 
        id="XLS_WRITER0" 
        namesRow="1" 
        sheetName="$Field1;$Field2" 
        type="XLS_WRITER"
  />

Since version 2.4

Reads data records from any number of input ports and creates structured XML file(s), according to ports mapping definition. Overwrites existing files.

Input ports: * any number of input port defined/connected. Output ports:

• one optional output port defined/connected (port protocol see fileURL).

Xml attributes:

<!DOCTYPE [rootElement] PUBLIC "[dtdPublicId]" "[dtdSystemId]">

“mapping” attribute itself contains hierarchically organized XML elements “Mapping” which describe relations between data from input ports in output XML.

Child element has field which is primary key of parent element. In this example, element “customer” will contain 0..n elements “order”.

   <Mapping 
      inPort="1" 
      element="customer" >
         <Mapping 
            inPort="0" 
            element="order" 
            key="CustomerID" 
            parentKey="CUSTOMERID" >
            ...
         </Mapping> 
      ...
   </Mapping>

Parent element has field which is primary key of child element. In this example, element “order” will contain 0..1 element “employee”.

   <Mapping 
      inPort="0" 
      element="order" >
         <Mapping 
            inPort="1" 
            element="employee" 
            key="EmployeeID" 
            parentKey="EmployeeID" >
            ...
         </Mapping>
      ...
   </Mapping>

Examples

Only 1 input port and single “flat” output XML file.

<Node id="XML_OUT" 
        type="XML_WRITER" 
        fileUrl="output/xmlOut.xml" >
   <attr name="mapping"><![CDATA[
   <Mapping 
      inPort="0" 
      element="customer" > 
   </Mapping>
   ]]></attr>
</Node>

4 input ports and structured output XML files (for each record from input port 2 (root mapping) will be created its own XML out file). Using attributes key, parentKey:

<Node id="XML_OUT" 
        type="XML_WRITER" 
        recordsPerFile="1" 
        recordSkip="0" 
        recordCount="100" 
        rootElement="root"
        fileUrl="output/xmlOut_$$.xml"
        charset="UTF-8" 
	rootDefaultNamespace="http://cloveretl.org/ns/def/" 
	rootNamespaces="data=&quot;http://cloveretl.org/ns/data/&quot;;cust=&quot;http://cloveretl.org/ns/cust/&quot;" >
 
   <attr name="mapping"><![CDATA[
   <Mapping 
      inPort="2" 
      element="cust:customer" 
      fieldsAs="elements" 
      fieldsAsExcept="CUSTOMERID;CompanyName" 
      fieldsIgnore="CompanyName;State" >
         <Mapping 
            inPort="0" 
            element="data:order" 
            parentKey="CUSTOMERID" 
            key="CustomerID" 
            fieldsAs="elements" 
            fieldsAsExcept="OrderID">
               <Mapping 
                  inPort="3" 
                  element="data:address" 
                  parentKey="OrderID" 
                  key="OrderID" 
                  fieldsAs="attributes" >
               </Mapping>
               <Mapping 
                  inPort="1" 
                  element="data:employee" 
                  parentKey="EmployeeID" 
                  key="EmployeeID" 
                  fieldsAs="elements" >
               </Mapping>
         </Mapping>
   </Mapping>
   ]]></attr>
</Node>

Deprecated way … using attributes key, keyToParent, keyFromParent

<Node id="XML_OUT" 
        type="XML_WRITER" 
        recordsPerFile="1" 
        recordSkip="0" 
        recordCount="100" 
        rootElement="root"
        fileUrl="output/xmlOut_$$.xml"
        charset="UTF-8" 
	rootDefaultNamespace="http://cloveretl.org/ns/def/" 
	rootNamespaces="data=&quot;http://cloveretl.org/ns/data/&quot;;cust=&quot;http://cloveretl.org/ns/cust/&quot;" >
 
   <attr name="mapping"><![CDATA[
   <Mapping 
      inPort="2" 
      element="cust:customer" 
      key="CUSTOMERID" 
      fieldsAs="elements" 
      fieldsAsExcept="CUSTOMERID;CompanyName" 
      fieldsIgnore="CompanyName;State" >
         <Mapping 
            inPort="0" 
            element="data:order" 
            key="OrderID" 
            keyToParent="CustomerID" 
            fieldsAs="elements" 
            fieldsAsExcept="OrderID">
               <Mapping 
                  inPort="3" 
                  element="data:address" 
                  key="AddressID" 
                  keyToParent="OrderID" 
                  fieldsAs="attributes" >
               </Mapping>
               <Mapping 
                  inPort="1" 
                  element="data:employee" 
                  key="EmployeeID" 
                  keyFromParent="EmployeeID" 
                  fieldsAs="elements" >
               </Mapping>
         </Mapping>
   </Mapping>
   ]]></attr>
</Node>

• *1) protocol for a field mapping. You need only a port and a field to which you want to output data to be stored/written. The new protocol has this syntax: port:$port.field[:processingType]. The processingType can be only “discrete” type and this value is also default. The field's metadata type can be only string, byte or cbyte.
• *1) the protocol can be used for DataWriter, DelimitedDataWriter, FixLenDataWriter, StructureWriter, XLSWriter and XMLWriter

• *2) protocol for a dictionary mapping. You need only a dictionary and a keyName to where you want to write. The protocol has this syntax: dictionary:keyName[:processingType]. The processingType is optional and can be: “discrete” or “stream”. The “stream” type is the default value. The discrete mode uses byte array to where stores all records. The stream mode uses an output stream that must be created before you run your graph (from java code).

Proxy specification for a URL in the fileURL attribute. The URL can have three proxy protocols:

• direct - represents a direct connection, or the absence of a proxy.
• proxy - represents proxy for high level protocols such as HTTP or FTP.
• proxysocks - represents a SOCKS (V4 or V5) proxy.