Convert Type Component

Convert Type Component



Convert Type Component

Convert the data types of the input flow.

If possible, it is better to change the source data so that it already has the correct types. However, sometimes it is necessary to convert the types explicitly.

Matillion ETL uses the Redshift type system that includes: Text, Integer, Numeric, Real, Double Precision, Boolean, Date, and Datetime. The use of these database types is detailed in the Redshift documentation.

Matillion ETL uses the Snowflake type system that includes: VARCHAR, NUMBER, FLOAT, BOOLEAN, DATE, TIMESTAMP, TIME, and VARIANT. The use of these database types is detailed in the Snowflake documentation.

Matillion ETL uses the BigQuery type system that includes: String, Integer, Float, Boolean, Date, Time, Datetime, and Timestamp. The use of these database types is detailed in the BigQuery documentation.

Important Information

When appropriate, values are first rounded to the requested decimal places before being cast to the requested size.

Although syntax is checked at validation time, runtime errors may occur during type-conversion if the input data cannot fit into the requested target type.



Redshift Properties

Property Setting Description
Name String Input the descriptive name for the component.
Conversions Column The column name from the input flow. Add as many rows to the editor as you need, one per input column.
Type Select either Text, Integer, Numeric, Real, Double Precision, Boolean, Date, DateTime.
Size The Size of the output field.
This is required for Text and Numeric types.
Precision The number of decimal places.
This is only required for Numeric and Real types.
Format The DateTime format
This is only required if the Type is set to Date or DateTime.
If your input column is Text and you want to convert to Date / Datetime, specify the input format of the input column.
For an exhaustive list of possible formats, see the Amazon Redshift Documentation.

Snowflake Properties

Property Setting Description
Name String Input the descriptive name for the component.
Conversions Column The column name from the input flow. Add as many rows to the editor as you need, one per input column.
Type Select either VARCHAR, NUMBER, FLOAT, BOOLEAN, DATE, TIMESTAMP, TIME, or VARIANT as the data type for this column.
Size Specify the number of digits in the NUMBER and VARCHAR data types. Values may be between 0 and 38. If left incomplete, Snowflake will default to 38 digits.
Precision The number of decimal places in NUMBER and VARCHAR types. The default is 0, indicating an integer.

BigQuery Properties

Property Setting Description
Name String Input the descriptive name for the component.
Conversions Column The column name from the input flow. Add as many rows to the editor as you need, one per input column.
Type Select either String, Integer, Float, Boolean, Date, Time, DateTime, or Timestamp.

Strategy

Generates a select clause, casting column types.



Example

This job changes two input text columns into numeric columns.

The metadata for the input data shows all inputs are "text".

A Convert Type component is configured. Only a name and a list of conversions are required.

The conversions request two fields to be converted to "numeric", with no decimal places.

The metadata for the Convert Type component confirms the types have been converted.