Hologres:Migrate data from MySQL to Hologres

Data migration methods

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

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.

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.