Source datatype mappings¶

Introduction¶

The Sesam entity model defines a set of supported data types that any external types need to be mapped to. The various data sources have different schemas, and which types are supported and their current mappings to the Sesam types is listed in this document.

SQL systems¶

This section documents which native types are supported by the RDBMS systems and to what Sesam type they map to. Any unsupported column type will have to be blacklisted in the source configuration, or you will have to write a custom query that coerces the non-supported column to a supported type.

SQL Server¶

The following is the supported native types of Microsoft SQL server. We support SQL Server 2008, but most of these column types should be both forwards and backwards compatible with older and future SQL Server versions.

Native type	Sesam type	Comment
`INT`	Integer
`TINYINT`	Integer
`BIGINT`	Decimal
`NUMERIC`	Decimal
`FLOAT`	Float
`REAL`	Float
`DECIMAL`	Decimal
`CHAR`	String
`BIT`	Boolean	`0` is `false` and `1` is `true`
`VARCHAR`	String
`NVARCHAR`	String
`DATE`	Datetime	Values with timezone will be shifted into UTC timezone on import.
`DATETIME`	Datetime	Values with timezone will be shifted into UTC timezone on import.
`DATETIME2`	String	Converted by the driver to `YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+\|-]tz:offset` ISO format (0-padded year-month-day hour-minute-seconds components plus fraction of a second after the decimal marker and an optional timezone-offset at the end). You can use DTL `datetime-parse` to convert it to a native Datetime object.
`TIME`	String	Converted by the driver to `hh:mm:ss[.nnnnnnn]` format (0-padded hour-minute-seconds components plus fraction of a second after the decimal marker (which is optional))
`UNIQUEIDENTIFIER`	UUID
`XML`	String

Oracle¶

The following is the supported native types of Oracle RDBMS. The tested version of oracle is Oracle 11g, but most of the supported column types should be compatible with both older and newer versions of Oracle.

Native type	Sesam type	Comment
`INTEGER`	Integer
`NUMBER`	Float	Depending on the column definition
`BINARY_FLOAT`	Float
`BINARY_DOUBLE`	Float
`CHAR`	String
`VARCHAR`	String
`VARCHAR2`	String
`DATE`	Datetime	The range of this datatype is unlimited to nanosecond precision. Values with timezone will be shifted into UTC timezone on import.
`TIMESTAMP`	Datetime	The range of this datatype is unlimited to nanosecond precision. Values with timezone will be shifted into UTC timezone on import.
`BLOB`	Bytes
`CLOB`	String
`NCLOB`	String
`RAW`	Bytes
`LONG RAW`	Bytes
`XMLType`	String

MySQL¶

The following is the supported native types of MySQL. The tested version is 5.6, but most of the supported column types should be compatible with both older and newer versions of MySQL.

Native type	Sesam type	Comment
`INT`	Integer
`TINYINT`	Integer
`BIGINT`	Integer
`NUMERIC`	Decimal
`FLOAT`	Float
`REAL`	Decimal
`DOUBLE`	Decimal
`DECIMAL`	Decimal
`BIT`	Integer	`0` or `1`
`BOOLEAN`	Integer	`0` or `1`
`CHAR`	String
`BINARY`	Bytes
`VARCHAR`	String
`VARBINARY`	Bytes
`TEXT`	String
`TINYTEXT`	String
`DATETIME`	Datetime	The range of this datatype is unlimited to nanosecond precision. Values with timezone will be shifted into UTC timezone on import.
`DATE`	Datetime	The time part of the Datetime object is set to midnight (i.e. `00:00:00.00000`)
`YEAR`	Integer	Range is 1901``to ``2155
`TIME`	String	Converted by the driver to `hh:mm:ss[.nnnnnnn]` format (0-padded hour-minute-seconds components plus fraction of a second after the decimal marker (which is optional))
`TIMESTAMP`	Datetime
`TINYBLOB`	Bytes
`MEDIUMBLOB`	Bytes
`BLOB`	Bytes
`LONGBLOB`	Bytes
`ENUM`	Varying	Based on enum type
`SET`	Varying	List of values, based on set type

PostgreSQL¶

The following is the supported native types of PostgreSQL. The tested version is 9.5, but most of the supported column types should be compatible with both older and newer versions of PostgreSQL.

Native type	Sesam type	Comment
`INTEGER`	Integer
`SMALLINT`	Integer
`BIGINT`	Integer
`NUMERIC`	Decimal
`REAL`	Float
`DOUBLE`	Float
`DECIMAL`	Decimal
`BOOLEAN`	Boolean
`CHAR`	String
`VARCHAR`	String
`TEXT`	String
`TIMESTAMP`	Datetime	The range of this datatype is unlimited to nanosecond precision. Values with timezone will be shifted into UTC timezone on import.
`DATE`	String	Converted to `YYYY-MM-DD` format (0-padded year-month-day components)
`TIME`	String	Converted by the driver to `hh:mm:ss[.nnnnnnn][+\|-tz:offset]` ISO format (0-padded hour-minute-seconds components plus fraction of a second after the decimal marker and a timezone-offset at the end).
`BYTEA`	Bytes
`ENUM`	Varying	Based on enum type
`UUID`	String
`XML`	String

Microservices in Sesam

API Best practices