ETL Transformers

When OrientDB runs the ETL module, transformer components execute in a pipeline to modify the data before it gets loaded into the OrientDB database. The operate on received input and return output.

Before execution, it always initalizes the $input variable, so that if you need to you can access it at run-time.

• CSV
• FIELD
• MERGE
• VERTEX
• CODE
• LINK
• EDGE
• FLOW
• LOG
• BLOCK
• COMMAND

CSV Transformer

Beginning with version 2.1.4, the CSV Transformer has been deprecated in favor of the CSV Extractor.

Converts a string in a Document, parsing it as CSV

Component description.

• Component name: csv
• Supported inputs types: [String]
• Output: ODocument

Syntax

For the "columns" parameter, specifying type guarantees better performance.

Example

• Transform a row in CSV (as ODocument class), using commas as the separator, considering NULL as a null value and skipping rows two through four.

  { "csv": { "separator": ",", "nullValue": "NULL",
             "skipFrom": 1, "skipTo": 3 } }

Field Transformer

When the ETL module calls the Field Transformer, it executes an SQL transformer against the field.

Component description.

• Component name: vertex
• Supported inputs types: [ODocument]
• Output: ODocument

Syntax

The "fieldName" parameter was introduced in version 2.1.

Examples

• Transform the field class into the ODocument class, by prefixing it with _:

  { "field": 
    { "fieldName": "@class", 
      "expression": "class.prefix('_')"
    } 
  }

• Apply the class name, based on the value of another field:

  { "field": 
    { "fieldName": "@class", 
      "expression": "if( ( fileCount >= 0 ), 'D', 'F')"
    }
  }

• Assign the last part of a path to the name field:

  { "field": 
    { "fieldName": "name",
      "expression": "path.substring( eval( '$current.path.lastIndexOf(\"/\") + 1') )" 
    }
  }

• Asign the field a fixed value:

  { "field": 
    { "fieldName": "counter", 
      "value": 0
    }
  }

• Rename the field from salary to renumeration:

  { "field": 
    { "fieldName": "remuneration", 
      "expression": "salary"
    } 
  },
  { "field": 
    { "fieldName": "salary", 
      "operation": "remove"
    } 
  }

• Rename multiple fields in one call.

  { "field": 
    { "fieldNames": 
      [ "remuneration", "salary" ], 
      "operation": "remove"
    } 
  }

  This feature was introduced in version 2.1.

Merge Transformer

When the ETL module calls the Merge Transformer, it takes input from one ODocument instance to output into another, loaded by lookup. THe lookup can either be a lookup against an index or a SELECT query.

Component description.

• Component name: merge
• Supported inputs types: [ODocument, OrientVertex]
• Output: ODocument

Syntax

For the "unresolvedLinkAction" parameter, the supported actions are:

Example

• Merge the current record against the record returned by the lookup on index V.URI, with the value contained in the field URI of the input document:

  { "merge": 
    { "joinFieldName": "URI", 
      "lookup":"V.URI" 
    } 
  }

Vertex Transformer

When the ETL module runs the Vertex Transformer, it transforms ODocument input to output OrientVertex.

Component description.

• Component name: vertex
• Supported inputs types: [ODocument, OrientVertex]
• Output: OrientVertex

Syntax

The "skipDuplicates" parameter was introduced in version 2.1.

Example

• Transform ODocument input into a vertex, setting the class value to the $classname variable:

  { "vertex": 
    { "class": "$className", 
      "skipDuplicates": true 
    } 
  }

Edge Transformer

When the ETL modules calls the Edge Transformer, it converts join values in one or more edges between the current vertex and all vertices returned by the lookup. The lookup can either be made against an index or a SELECT.

Component description.

• Component name: EDGE
• Supported inputs types: [ODocument, OrientVertex]
• Output: OrientVertex

Syntax

The "targetVertexFields" andx "edgeFields" parameter were introduced in version 2.1.

For the "unresolvedLinkAction" parameter, the following actions are supported:

Examples

• Create an edge from the current vertex, with the class set to Parent, to all vertices returned by the lookup on the D.inode index with the value contained in the filed inode_parent of the input’s vertex:

  { "edge": 
    { "class": "Parent", 
      "joinFieldName": "inode_parent",
      "lookup":"D.inode",    
      "unresolvedLinkAction": "CREATE"
    } 
  }

• Transformer a single-line CSV that contains both vertices and edges:

  { "source": 
    { "content": 
      { "value": "id,name,surname,friendSince,friendId,friendName,friendSurname\n0,Jay,Miner,1996,1,Luca,Garulli"
      }
    },
    "extractor": 
      { "row": {} },
    "transformers": 
      [ 
        { "csv": {} },
        { "vertex": 
          { "class": "V1" }
        },
        { "edge": 
          { "unresolvedLinkAction": "CREATE",
            "class": "Friend",
            "joinFieldName": "friendId",
            "lookup": "V2.fid",
            "targetVertexFields": 
              { "name": "${input.friendName}",
                 "surname": "${input.friendSurname}"
              },
              "edgeFields": 
                { "since": "${input.friendSince}" }
                }
          },
          { "field": 
            { "fieldNames": 
              [ "friendSince",
                "friendId",
                "friendName",
                "friendSurname"
              ],
              "operation": "remove"
            }
          }
        ],
        "loader": 
          { "orientdb": 
            { "dbURL": "memory:ETLBaseTest",
              "dbType": "graph",
              "useLightweightEdges": false
            }
        }
    }

Flow Transformer

When the ETL module calls the Flow Transformer, it modifies the flow through the pipeline. Supported operations are skip and halt. Typically, this transformer operates with the if attribute.

Component description.

• Component name: flow
• Supported inputs types: Any
• Output: same type as input

Syntax

Example

• Skip the current record if name is null:

  { "flow": 
    { "if": "name is null", 
      "operation" : "skip" 
    } 
  }

Code Transformer

When the ETL module calls the Code Transformer, it executes a snippet of code in any JVM supported language. The default is JavaScript. The last object in the code is returned as output.

In the execution context:

• input The input object received.
• record The record extracted from the input object, when possible. In the event that input object is a vertex or edge, it assigns the underlying ODocument to the variable.

Component description.

• Component name: code
• Supported inputs types: [Object]
• Output: Object

Syntax

Example

• Display the current record and return the parent:

  { "code": 
    { "language": "Javascript",
      "code": "print('Current record: ' + record); record.field('parent');"
    }
  }

Link Transformer

When the ETL module calls the Link Transformer, it converts join values into links within the current record, using the result of the lookup. The lookup can be made against an index or a SELECT.

Component description.

• Component name: link
• Supported inputs types: [ODocument, OrientVertex]
• Output: ODocument

Syntax

For the "linkFieldType" parameter, supported link types are: LINK, LINKSET and LINKLIST.

For the "unresolvedLinkAction" parameter the following actions are supported:

Example

• Transform a JSON value into a link within the current record, set as parent of the type LINK, with the result of the lookup on the index D.node with the value contained in the field inode_parent on the input document.

  { "link": 
    { "linkFieldName": "parent", 
      "linkFieldType": "LINK",
      "joinFieldName": "inode_parent", 
      "lookup":"D.inode",  
      "unresolvedLinkAction":"CREATE"
    } 
  }

Log Transformer

When the ETL module uses the Log Transformer, it logs the input object to System.out.

Component description.

• Component name: log
• Supported inputs types: Any
• Output: Any

Syntax

Examples

• Log the current value:

  { "log": {} }

• Log the currnt value with -> as the prefix:

  { "log": 
    { "prefix" : "-> " } 
  }

Block Transformer

When the ETL module calls the Block Transformer, it executes an ETL Block component as a transformation step.

Component description.

• Component name: block
• Supported inputs types: [Any]
• Output: Any

Syntax

Example

• Log the current value:

  { "block": 
    { "let": 
      { "name": "id",
        "value": "={eval('$input.amount * 2')}"
      }
    }
  }

Command Transformer

When the ETL module calls the Command Transformer, it executes the given command.

Component description.

• Component name: command
• Supported inputs types: [ODocument]
• Output: ODocument

Syntax

Example

• Execute a SELECT and output an edge:

  { "command" : 
    { "command" : "SELECT FROM E WHERE id = ${edgeid}",
      "output" : "edge"
    }
  }