Data Type Mapping

FlyData Sync Data Type Mapping: MySQL -> Redshift (ver. 0.3.17)

There are data type differences between MySQL and Amazon Redshift. Some formats are supported, while others are not.
In order to provide a smooth replication of data from MySQL to Redshift, we automatically convert MySQL data types to match the closest equivalent data types supported in Amazon Redshift.

Below is a mapping of this data type conversion.

MySQL Data Type

Redshift Data Type

Agent version

Notes

BIGINT INT8    
BIGINT UNSIGNED NUMERIC(20, 0)   *1
BINARY VARCHAR   *2, *3
BIT INT8   *11
BLOB VARCHAR(65535)    
BOOL or BOOLEAN INT2    
CHAR VARCHAR   *3, *4
DATE DATE   *5
DATETIME TIMESTAMP   *5
DEC or DECIMAL NUMERIC   *6
DEC or DECIMAL UNSIGNED NUMERIC   *6
DOUBLE [PRECISION] FLOAT8    
DOUBLE [PRECISION] UNSIGNED FLOAT8   *1
ENUM VARCHAR   *7
FIXED NUMERIC   *6
FIXED UNSIGNED NUMERIC   *6
FLOAT FLOAT4    
INT or INTEGER INT4    
INT or INTEGER UNSIGNED INT8   *1
LONGBLOB VARCHAR   *2, *3
LONGTEXT VARCHAR(MAX)   *3, *4
MEDIUMBLOB VARCHAR   *2, *3
MEDIUMINT INT4    
MEDIUMINT UNSIGNED INT4   *1
MEDIUMTEXT VARCHAR(MAX)   *3, *4
NUMERIC NUMERIC    
SET VARCHAR   *7
SMALLINT INT2    
SMALLINT UNSIGNED INT4   *1
TEXT VARCHAR(MAX)   *3, *4
TIME TIMESTAMP   *8, *9, *10
TIMESTAMP TIMESTAMP   *5
TINYBLOB VARCHAR   *2
TINYINT INT2    
TINYINT UNSIGNED INT2    
TINYTEXT VARCHAR(MAX)   *4
VARBINARY VARCHAR(MAX)   *2, *3
VARCHAR VARCHAR   *3, *4
YEAR DATE 0.3.10 *12
GEOMETRY, POINT, LINESTRING, POLYGON (unsupported)    
MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION (unsupported)    

Notes

  • *1) Redshift does not support unsigned types
  • *2) Binary bytes get translated into a string of “0xFFFF…” format. e.g.) Binary 40bc8f => “0x40bc8f”
  • *3) Truncated at 65,535 which is the max length of Redshift VARCHAR type
  • *4) String gets converted to UTF-8
  • *5) ‘0000-00-00’ becomes ‘0001-01-01’
  • *6) maximum (precision, scale) is (38, 37)
  • *7) label text is stored as varchar string
  • *8) ‘00:00:00’ gets converted to ‘0001-01-01 00:00:00’
  • *9) Negative value gets converted as an offset from ‘0001-01-01 00:00:00’. For example, ‘-01:00:00’ becomes ‘0001-12-31 23:00:00 BC’
  • *10) Time value whose hour part is more than 23 is represented using the day part. For example, ‘25:00:00’ becomes ‘0001-01-02 01:00:00’
  • *11) ex. b’101’ becomes 5 in Redshift
  • *12) DATE(2) and DATE(4) are supported
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk