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

FlyData handles real-time replication for Amazon RDS and Aurora, MySQL and PostgreSQL.

Get set up in minutes. Start uncovering data to make faster, better business decisions today.

By using this website you agree to accept our Privacy Policy and Terms & Conditions