This topic describes the methods that you can use to smoothly migrate data from MySQL to Hologres. This topic also describes the differences between MySQL and Hologres in query syntax and functions. This helps you migrate data more efficiently.

Data migration methods

The following table describes the applicable scenarios and migration methods based on your migration type.

Note If extract, transform, load (ETL) processing is involved, you can use Flink to read MySQL data and then write the data to Hologres.
Migration type Scenario and migration method Reference
Single-table batch synchronization You can use a batch sync node to synchronize data from a single MySQL table to Hologres. Use batch sync nodes of DataWorks to synchronize data from databases to Hologres
Single-table real-time synchronization You can enable the binary logging feature of MySQL to synchronize data from a single MySQL table to Hologres in real time. Configure a data source (MySQL)
Real-time database synchronization You can use a real-time sync node to synchronize all data in a MySQL database to Hologres in real time. Configure and manage a real-time data synchronization node
Data synchronization solution The Data Integration service of DataWorks allows you to configure a sync solution to synchronize data from a source to a destination in real time.

You can use a sync solution to synchronize data from multiple tables at a time or synchronize both full and incremental data. If you want to synchronize both full and incremental data, you can synchronize the incremental data after the full data is synchronized.

Configure a source MySQL data source

Data type mappings

The following table lists the data type mappings between MySQL and Hologres after data migration. For more information, see Data types.

When you migrate data from MySQL to Hologres, take note of the following items about data type mappings:

  • Hologres supports three integer types: SMALLINT (2 bytes), INTEGER (4 bytes), and BIGINT (8 bytes). MySQL supports five integer types: TINYINT (1 byte), SMALLINT (2 bytes), MEDIUMINT (3 bytes), INT (4 bytes), and BIGINT (8 bytes). You must map an integer type of MySQL to an integer type of Hologres with the same or more bytes in size.
  • Hologres does not support unsigned integer types. When you map data types, you must consider data overflow caused by fields of unsigned integer types. If the value range of such a field exceeds the value range that can be represented by the mapped data type, you must consider mapping the data type of the field to an integer type with a wider value range.
  • You can map the TINYTEXT, TEXT, MEDIUMTEXT, or LONGTEXT type of MySQL to the TEXT type of Hologres.
  • You can map a floating-point type of MySQL, such as the DECIMAL, NUMERIC, DOUBLE, or FLOAT type, to the corresponding floating-point type of Hologres.
  • The DATETIME type of MySQL excludes time zone information and is in the format of YYYY-MM-DD HH:MM:SS. You can map the DATETIME type of MySQL to the TIMESTAMP WITHOUT TIME ZONE type of Hologres.
Data type of MySQL Data type of Hologres
BIGINT BIGINT
BINARY(n) BYTEA
BIT BOOLEAN
CHAR(n) or CHARACTER(n) CHAR(n) or CHARACTER(n)
DATE DATE
DATETIME TIMESTAMP [WITHOUT TIME ZONE]
DECIMAL(p,s) or DEC(p,s) DECIMAL(p,s) or DEC(p,s)
DOUBLE DOUBLE PRECISION
FLOAT REAL
INT or INTEGER INT or INTEGER
MEDIUMINT INTEGER
NUMERIC(p,s) NUMERIC(p,s)
SMALLINT SMALLINT
TINYBLOB, BLOB, MEDIUMBLOB, or LONGBLOB BYTEA
TINYINT SMALLINT
TINYTEXT, TEXT, MEDIUMTEXT, or LONGTEXT TEXT
TIME TIME [WITHOUT TIME ZONE]
TIMESTAMP TIMESTAMP [WITH TIME ZONE]
VARBINARY(n) or VARBINARY(max) BYTEA
VARCHAR(n) VARCHAR(n)
VARCHAR(max) TEXT

Query syntax

This section describes the differences between MySQL and Hologres in query syntax.

  • Quotation marks

    Hologres is not case-sensitive. If you need to query case-sensitive content, enclose the content to be queried in quotation marks (" ").

    For example, replace select `A` from b with select "A" from b.

  • Conditional filtering

    When you specify a filter condition in Hologres, the specified data types must match. By default, Hologres does not perform implicit type conversions. The following example shows you how to resolve the issue if the specified data types do not match:

    • Sample code:
      select * from business_module where ds = 20210329;
    • Problem description:
      The ds field is of the TEXT type in the Hologres table, whereas the value 20210329 is of the INTEGER type. A type mismatch error occurs. The following error message is returned:
      operator does not exist: text = integer;
    • Solution:

      Hologres allows you to customize the conversion between data types. The following sample code provides examples on how to customize the conversion between data types:

      CREATE CAST (TEXT  AS INTEGER) WITH INOUT AS IMPLICIT; 
      CREATE CAST (TEXT  AS BIGINT) WITH INOUT AS IMPLICIT; 
      CREATE CAST (TEXT  AS DECIMAL) WITH INOUT AS IMPLICIT; 
      CREATE CAST (TEXT  AS TIMESTAMP ) WITH INOUT AS IMPLICIT; 
      CREATE CAST (NUMERIC  AS TEXT ) WITH INOUT AS IMPLICIT;
  • Paging

    MySQL allows you to use the LIMIT 0,10 syntax to display the query results by page. After data migration, Hologres allows you to use the OFFSET 0 LIMIT 10 syntax for paging.

  • Sorting

    In MySQL, the DESC NULLS FIRST ASC NULLS FIRST syntax is used to sort the query results by default. In Hologres, the DESC NULLS FIRST ASC NULLS LAST syntax is used for sorting by default.

    To ensure consistent experience, Hologres allows you to adjust the sorting syntax to ORDER BY XXX DESC NULLS LAST.

  • Grouping

    By default, Hologres does not allow you to use the GROUP BY clause for fields of approximate numeric types such as the FLOAT or DOUBLE type. You can convert such data types to DECIMAL or set the hg_experimental_enable_double_equivalent parameter to enable the use of the GROUP BY clause for fields of approximate numeric types.

    Note The hg_experimental_enable_double_equivalent parameter is supported only in Hologres V0.10 and later. If the version of your Hologres instance is earlier than V0.10, submit a ticket and request the technical support to update your instance.
    set hg_experimental_enable_double_equivalent=on; -- Enable the feature for the current session.
    alter database XXX set hg_experimental_enable_double_equivalent=on; -- Enable the feature for the database.
  • Union

    When you use the UNION operator in Hologres, the data types of the specified fields must be the same. The following example shows you how to resolve the issue if the specified data types are different:

    • Sample code:
      SELECT project_id FROM tableA union ALL  select project_id from tableB;
    • Problem description:

      The project_id field is of the BIGINT type in the tableA table. The project_id field is of the TEXT type in the tableB table. If you execute this SQL statement in MySQL, MySQL performs an implicit type conversion before it returns the query results. If you execute this SQL statement in Hologres, an error occurs. The following error message is returned:

      UNION types bigint and text cannot be matched;
    • Solution:

      When you use the UNION operator in Hologres, make sure that the data types of the specified fields are the same. For example, convert the data type of the project_id field in the tableB table to BIGINT:

      SELECT project_id FROM tableA union ALL  select cast(project_id as bigint) from tableB; 

Functions

Hologres is compatible with most PostgreSQL functions. For more information, see Functions. This section describes the differences between MySQL and Hologres in functions.

  • Use zero as the divisor
    • Problem description:

      If the divisor is 0 in MySQL, the NULL value is returned. If the divisor is 0 in Hologres, the following error message is returned:

      ERROR: division by zero;
    • Solution:
      select a/ b from table;
      Modify the preceding statement to: select a/ NULLIF(b,0) from table;
  • Divide an integer by another integer
    • Problem description:

      If a remainder is left when you divide an integer by another integer, MySQL returns a decimal, whereas Hologres discards the remainder and returns an integer.

      For example, when you divide 5 by 2, MySQL returns 2.5, whereas Hologres returns 2.

    • Solution:

      If you want Hologres to be compatible with the division method of MySQL, you must perform explicit type conversions in Hologres.

      select 1/2::FLOAT;
  • IF function

    Hologres does not support the IF function. You must change the IF function to the CASE WHEN function.

  • IFNULL function

    The IFNULL function of MySQL corresponds to the COALESCE(x,y) function of Hologres.

  • LENGTH function

    The LENGTH function of MySQL corresponds to the CHAR_LENGTH(string) function of Hologres.