The add_time filter plugin for Data Connector allows users to add a new time-based column to the schema either by copying the value from another existing column in the schema or by specifying the value.

This plugin provides two options as explained below:

Option	Description
to_column	defines the new column name and type.
from_column	maps an existing column in the data to the new column defined in to_column. (OR from_value)
from_value	maps a fixed or incremental value to the new column. (OR from_column)

add_time filter works with all the Arm Treasure Data Data Connectors.

 

Configuration

Add the filter section with type add_time to your load.yml, for example:

in:
...
filters:
- type: add_time
  to_column:
    name: time
    type: timestamp
  from_column: # or from_value to use fixed or incremental time value
    name: created_at
    timestamp_format: "%Y-%m-%d %H:%M:%S"
out:
...

Here are detailed descriptions for different options available: options

Below are few use cases in which add_time can be used:

Use Case 1: Fill 0s for time column

When you have a table which doesn’t have a time-based column, you may want to fill it with 0s. In that case, please add this section to your .yml.

filters:
- type: add_time
  to_column: {name: time}
  from_value: {value: 0, unix_timestamp_unit: sec}

Use Case 2: Add a new time column with incremental value using the from_value configuration

You can add a new time-column with a fixed or incremental value using the from_value option. from_value supports the following modes:

• fixed_time(default mode): A new column name defined in to_column section is added with fixed value specified by the value parameter.

• incremental_time: A new column name defined in to_column section is added with value that increments by 1 second for each record, starting at from timestamp until to timestamp, after which it wraps around and starts with from timestamp again.

• upload_time: A new column name defined in to_column section is added to the set with the fixed value corresponding to the time the import job upload has started. This mode does not require additional parameters. It’s useful for a scheduled execution. The scheduled connector with upload_time mode is able to insert the defined scheduled time into the ‘time’ column.

from_value example with incremental_time mode

filters:
- type: add_time
  to_column:
    name: time
    type: timestamp
  from_value:
    mode: incremental_time
    from: "2016-01-01 00:00:00 UTC"
    to: "2016-01-01 01:00:00 UTC" 

from_value example with upload_time mode

filters:
- type: add_time
  to_column:
    name: time
    type: timestamp
  from_value:
    mode: upload_time # insert a scheduled time into the `time` column

Further details of the from_value configuration with more examples are here.

Use Case 3: Map an existing unixtime(ms) column in data to new column using the from_column configuration

The from_column configuration specifies the name of one of the columns found in the data and the format to be used to parse and map values to the to_column configuration. This configuration makes a copy of the values from the column specified by name, instead of renaming the source column itself.

from_column example

filters:
- type: add_time
  to_column:
    name: time
    type: long
    unix_timestamp_unit: sec
  from_column:
    name: created_at # created_at is the column in data
    unix_timestamp_unit: milli #with type unixtime in milliseconds

Further details of the from_column configuration with more examples are here