Connector Improvement: Add mechanism of data mapping customization
To add possibility to customize data mapping. For the moment it is not possible
Case (default logic):
- Connector field: Provider:SQLServer, field type: NVARCHAR(MAX),value: JSON
- Destination field:Provider:Snowflake. field type:NVARCHAR. value: string
What is the issue:
- Snowflake must store json, xml, any semy-structured types in Variant type https://docs.snowflake.com/en/sql-reference/data-types-semistructured.html#semi-structured-data-types
- Happens because default logic for connector and target mapping VARCHAR(N) -> STRING -> VARCHAR. But has to be set VARCHAR(N) -> JSON -> VARIANT. Moreover same issue will be for XML type of SQLServer
- This lead to json parsing issue in Snowflake
What is proposed:
To add mechanism which allow to customize default type mapping for following level:
- Fivetran group(general rule for setup)
- Connector(rule for specific database)
- Column(if some columns have to have custom mapping)
in order to add to be able to specify for SQLServer-Snowflake mapping like:
- for some columns which stores string: VARCHAR(N) -> STRING -> VARCHAR
- for some columns which stores JSON values: VARCHAR(N) -> JSON -> VARIANT
- for some columns which stores XML: XML -> JSON\STRING -> VARIANT
-
Could we also add a Customisation to Oracle connector for Oracle to Snowflake data type mapping where an Oracle Date is mapped to a Snowflake Date.
The problem is that an Oracle Date does not just use it’s 7 bytes to store a calendar date, because those bytes are used to store century, decade, year, month, day, hour, minute and seconds. Snowflake supports a single DATE data type for storing dates (with no time elements) and Timestamp which include times up to nanoseconds. FiveTran converts Oracle Dates to both Timestamp & Date depending on the source values which means migrated ETL needs to be changed accordingly. Can this mapping be customisable so that all Oracle dates are converted to Snowflake Timestamps
Please sign in to leave a comment.
Comments
1 comment