Data Type Mapping

Fivetran HVR's mapping/conversion of data types is complex because each DBMS's data types have a specific range which seldom corresponds the range of another DBMS. For example, data type varchar(10) in SQL Server corresponds to varchar2(10 bytes) in Oracle, but varchar(8000) corresponds to clob. Note that the mapping here does not just depend on the 'name' of the data type, but also its 'attributes' like byte length, encoding, scale and precision, etc. If HVR is not accurate in mapping data types, then target tables could be created which are unable to contain the data delivered from the source DBMS.

Data types are not directly mapped/converted from the source DBMS to the target DBMS, instead they are first mapped to the HVR 'repository data types' and then these repository data types are mapped to corresponding data types in target DBMS or file format. Data type mapping/conversion happens in the following manner:

When a channel is built using the Table Selection dialog (or hvradapt), the source DBMS data types are mapped to the HVR 'repository data types'.
After the tables are added to a channel the mapping of data types in source DBMS (capture location) to the HVR repository data types can be viewed in the Tables and Table Details page.
Then during Refresh and Integrate, the HVR repository data types are mapped to corresponding data types in the target DBMS (integrate location). This mapping happens at the moment the Refresh is used to create target tables.
After performing Refresh/Integrate the mapping of the HVR repository data types to the target DBMS data types can be viewed in the Tables and Table Details page.

Customizing Data Type Mapping

If the automatic/default mapping of data type is not appropriate for a specific channel, it can be modified using parameter Datatype in action ColumnProperties.

For example, by default HVR maps a number (without scale or precision) in Oracle to numeric(38,4) in SQL Server. By defining the following action, number (without scale or precision) in Oracle is mapped to float instead:

Group	Table	Action	Parameter(s)
SRCGRP	*	ColumnProperties	DatatypeMatch="number[prec=0 && scale=0]", Datatype="float"

In the above example, parameter DatatypeMatch is used for mapping all columns with number(without scale or precision) data type into float data type.

Alternatively, parameter Name in action ColumnProperties can be used for mapping the data type of a specific column (e.g. MYCOLUMN) into float data type.

Group	Table	Action	Parameter(s)
SRCGRP	*	ColumnProperties	Name="MYCOLUMN", Datatype="float"

Extended Data Type Support

'Extended data types' are the DBMS data types (e.g. sql_variant in SQL Server or xmltype in Oracle) which are not mapped to native HVR data types. Instead, HVR's Extended Data Type Support feature should be used for such data types. For this, you need to use parameter CaptureExpression in action ColumnProperties for converting an extended data type to a supported data type during Capture, Compare or Refresh (read from source) and parameter IntegrateExpression in action ColumnProperties for converting it back to extended data type during Integrate, Compare or Refresh (write into target) respectively.

Data Type Mapping

Customizing Data Type Mapping

Extended Data Type Support

Data Type Mapping for Location Types