The add_time filter plugin for Integrations allows users to add a new time-based column to the schema by copying the value from another existing column in the schema or specifying the value.
This plugin provides two options as explained in the following table.
| 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) |
The add_time filter works with all the Treasure Data Integrations.
This plugin can be added to Source when the guess operation cannot detect a time column in the source data. The guess operation may fail to find a time column under the following conditions:
- The time column does not exist in the data source.
- The data source contains a time column, but its type is not Timestamp or Long
This topic includes:
- Configuration
- Use Cases
- Use Case 1: Add 0s for time-based column
- Use Case 2: Add a new time column with incremental value using the from_value configuration
- from_value example with incremental_time mode
- from_value example with upload_time mode
- Use Case 3: Map an existing unixtime(ms) column in data to a new column using the from_column configuration
- from_column example
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:
...Learn about the different configuration options available. It is recommended to use add_time filter after other filter plugins are defined like rename to avoid any problems.
Below are a few use cases in which add_time can be used:
When you have a table that doesn’t have a time-based column, you may want to fill it with 0s. In that case, add this section to your .yml.
filters:
- type: add_time
to_column: {name: time}
from_value: {value: 0, unix_timestamp_unit: sec}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 the to_column section is added with the fixed value specified by the value parameter.
incremental_time: A new column name defined in the to_column section is added with a value that increments by 1 second for each record, starting from the timestamp until the timestamp, after which it wraps around and starts from the timestamp again.
upload_time: A new column name defined in the 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 planned connector with upload_time mode can insert the defined scheduled time into the ‘time’ column.
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" filters:
- type: add_time
to_column:
name: time
type: timestamp
from_value:
mode: upload_time # insert a scheduled time into the `time` columnLearn more details about the from_value configuration and view additional examples.
The from_column configuration specifies the name of one of the columns found in the data and the format 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.
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 millisecondsLearn more details about the from_column configuration and view additional examples.