Transforms

Tranforms are part of the steps of an IO job, which are used to alter the data in a specific way. They work with any input (e.g. APIs, databases, and files). There are many types of transforms. See the examples below:

ExtraColumns Transform

The ExtraColumnsTranform is used to learn the columns to include in the output table. It creates sdls for each column it encounters so that they will be created when the table is created.

You can specify whether or not to preserve source column case by adding the rename_columns parameter. Setting to false will preserve the case, and setting to true will lowercase the column name.

You can ignore a field by adding one or multiple ignores lines to the ExtraColumnsTransform:

Here is an example:

steps: [
  {
    use: mitto.iov2.steps#Input
    transforms: [
      {
        ignores: [
          $.*.<field to ignore #1>
          $.*.<field to ignore #2>
        ]
        use: mitto.iov2.transform#ExtraColumnsTransform
        rename_columns: false
      }
      {
        use: mitto.iov2.transform#ColumnsTransform
      }
    ]
  }
  {
    use: mitto.iov2.steps#CreateTable
  }
  {
    transforms: [
      {
        use: mitto.iov2.transform#FlattenTransform
      }
    ]
    use: mitto.iov2.steps#Output
  }
]

Exclude Transform

The ExcludeTransform allows you to specify keys to indicate what fields from the source data to omit from your output. Multiple fields should be on separate lines with no comma between them. Simply add the following to the transforms section prior to any other transforms:

Here is an example:

{
use: mitto.iov2.transform#ExcludeTransform
keys: [
 <column to remove>
 ]
}

Here is an example using an ExcludeTransform to remove a column called “Category”:

steps: [
 {
 use: mitto.iov2.steps#Input
 transforms: [
 {
   use: mitto.iov2.transform#ExcludeTransform
   keys: [
     city
     profit_ratio
   ]
 }
 {
   use: mitto.iov2.transform#ExtraColumnsTransform
 }
 {
   use: mitto.iov2.transform#ColumnsTransform
 }
 ]
 }
 {
 use: mitto.iov2.steps#CreateTable
 }
 {
 use: mitto.iov2.steps#Output
 transforms: [
 {
   use: mitto.iov2.transform#FlattenTransform
 }
 ]
 }
 {
 use: mitto.iov2.steps#CollectMeta
 }
]

FilterType Transform

FilterTypeTransform filters out all record types that are not of the specified type. Here is an example which only includes records of type revenueArrangement:

steps: [
  {
    column: last_modified_date
    use: mitto.iov2.steps#MaxTimestamp
  }
  {
    use: mitto.iov2.steps.upsert#SetUpdatedAt
  }
  {
    transforms: [
      {
        type: revenueArrangement
        use: netsuite.io#FilterTypeTransform
      }
      {
        use: netsuite.io#AppendExtraColumnsTransform
      }
      {
        ignores: [
          $.extra_columns
        ]
        jpath: $
        use: mitto.iov2.transform#ExtraColumnsTransform
      }
      {
        jpath: $.extra_columns
        use: mitto.iov2.transform#ExtraColumnsTransform
      }
      {
        use: mitto.iov2.transform#ColumnsTransform
      }
    ]
    use: mitto.iov2.steps#Input
  }
  {
    use: mitto.iov2.steps#CreateTable
  }
  {
    use: mitto.iov2.steps.upsert#CreateTempTable
  }
  {
    transforms: [
      {
        use: mitto.iov2.transform#FlattenTransform
      }
    ]
    use: mitto.iov2.steps#Output
  }
  {
    key: internal_id
    use: mitto.iov2.steps.upsert#SyncTempTable
  }
  {
    use: mitto.iov2.steps#CollectMeta
  }
]
store: {
  key: [
    $.internalId
  ]
  updated_at: $.lastModifiedDate
}

Flatten Transform

A FlattenTransform is used to flatten the data using SDL. Example to be added.

Pluck Transform

A PluckTransform retrieves data from a nested array and associates it to the appropriate parent identifier.

Here is an example which obtain nested tax_line information and associates it to the parent order_id of 6444275957783 and the line item IDs of 6444275990551 and 6444275957783.

steps: [
  {
    transforms: [
      {
        jpath: $.tax_lines[*],
        members: [
          {
          /* Reference to the input member node above*/
            name: order_id
            value: $.order_id
          }
          {
          /* Reference to the input member node above*/
            name: order_updated_at
            value: $.order_updated_at
          }
          {
          /* New Reference the line item id ie $.line_items[*].$.id*/
            name: order_line_item_id
            value: $.id
          }
        ]
        use: mitto.iov2.transform#PluckV2Transform
      }
      {
        use: mitto.iov2.transform#ExtraColumnsTransform
      }
      {
        use: mitto.iov2.transform#ColumnsTransform
      }
    ]
    use: mitto.iov2.steps#Input
  }
  {
    use: mitto.iov2.steps#CreateTable
  }
  {
    transforms: [
      {
        use: mitto.iov2.transform#FlattenTransform
      }
    ]
    use: mitto.iov2.steps#Output
  }
  {
    use: mitto.iov2.steps#CollectMeta
  }
]

Reflect Transform

A ReflectTransform copies the specified data exactly from your source to a new destination. It is commonly used to replicate a database table to a different system. It does not use the ExtraColumnsTransform to learn the data, so it is significantly faster.

Note: You job configuration’s input section must use the same dbo, schema and tablename as is used by the ReflectTransform (?)

Here is an example:

steps: [
  {
    transforms: [
      {
        dbo: postgresql://db/mitto
        schema: test_schema
        tablename: shortorders
        use: mitto.iov2.transform#ReflectTransform
      }
    ]
    use: mitto.iov2.steps#Input
  }
  {
    use: mitto.iov2.steps#CreateTable
  }
  {
    transforms: [
      {
        use: mitto.iov2.transform#FlattenTransform
      }
    ]
    use: mitto.iov2.steps#Output
  }
  {
    use: mitto.iov2.steps#CountTable
  }
]

Slice Transform

The SliceTransform allows you to pull a number of rows from your source data by specifying a start and stop point. Specifying stop: 1 skips the header at row 0. Specifying start: -1 skips the footer at the last row.

Here is an example using an SliceTransform to start from the first row and skip the footer:

steps: [
  {
    use: mitto.iov2.steps#Input
    transforms: [
      {
        use: mitto.iov2.transform.builtin#SliceTransform
        stop: -1
        start: 1
      }
      {
        use: mitto.iov2.transform#ExtraColumnsTransform
      }
      {
        use: mitto.iov2.transform#ColumnsTransform
      }
    ]
  }
  {
    use: mitto.iov2.steps#CreateTable
  }
  {
    use: mitto.iov2.steps#Output
    transforms: [
      {
        use: mitto.iov2.transform#FlattenTransform
      }
    ]
  }
  {
    use: mitto.iov2.steps#CollectMeta
  }
]