Hologres:Migrate data from MySQL to Hologres

Data migration methods

The following table describes the 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 follows 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 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 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 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 change 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 configure 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, join a DingTalk group to apply for an instance upgrade. For more information about how to join a DingTalk group, see Obtain online support for Hologres.

  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

  If 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 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

    If 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 Query the storage sizes of tables and databases. 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, an error occurs. The following error message is returned:

    ERROR: division by zero;

  • Solution

    • select a/ b from table;
      Change the preceding statement to select a/ NULLIF(b,0) from table;

      .

    • In Hologres V1.3.21 and later, you can specify the GUC parameter to allow division by 0. If you want to upgrade your Hologres instance, follow the instructions in Instance upgrades to perform the operation. You can also join the Hologre DingTalk group for technical support. For more information, see Obtain online support for Hologres.

      -- Install an extension that is compatible with MySQL as a superuser. You need to install the extension only once for each database.
      create extension if not exists mysql_compatible;
      -- Set the mysql_compatible.enable parameter to on. After the parameter is configured, the divisor can be 0 in data query language (DQL) statements.
      set mysql_compatible.enable = on;

      The following example shows how to resolve the issue:

      -- Install an extension that is compatible with MySQL as a superuser. You need to install the extension only once for each database.
      create extension if not exists mysql_compatible;
      
      -- Scenario 1: The divisor and dividend are constants of the same type.
      set mysql_compatible.enable = on;
      select 1/0;
      
      
      -- Scenario 2: The divisor and dividend are constants of different types.
      set mysql_compatible.enable = on;
      select 1.0/0;
      
      -- Scenario 3: The dividend is a variable.
      set mysql_compatible.enable = on;
      select sum(c) / 0 from (select generate_series(1,100) as c) as t;
      
      
      -- Scenario 4: The divisor is a variable.
      set mysql_compatible.enable = on;
      select max(c)/sum(d) from (select generate_series(1,101) as c, generate_series(-50,50) as d) as t;
      
      -- Scenario 5: The divisor is 0 when the INSERT statement is executed.
      create table if not exists test_insert_divide_by_zero(c1 int);
      set mysql_compatible.strict_mode = off;
      set mysql_compatible.enable = on;
      insert into test_insert_divide_by_zero select 100 / 0.0;

• 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.

    select1/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.

FAQ

What do I do if the results of the COUNT DISTINCT operation are different in MySQL and Hologres?

• Cause

  In MySQL, the count(distinct column_1, column_2, ...) statement is executed on multiple columns for data deduplication. If a column contains the null value, MySQL records the result of the DISTINCT operation as null for the row that contains the null value. The null value is not counted.

  In Hologres, the count(distinct column_1, column_2, ...) statement is executed on multiple columns for data deduplication. The null value does not affect the calculation result and is counted.

• Solution

  If you want the results to be the same in MySQL and Hologres, change the statement in Hologres to count(distinct column_1 || column_2 || ...).

• Example

  CREATE TABLE count_distinct_test (
    a text,
    b text
  );
  
  INSERT INTO count_distinct_test VALUES ('a', 'b'), ('a', NULL), (NULL, 'b'), ('a', 'b');
  
  -- Perform the COUNT DISTINCT operation on multiple columns in Hologres.
  SELECT count(distinct(a, b)::text) FROM count_distinct_test;
  
  -- The following result is returned:
   count 
  -------
       3
  (1 row)
  
  -- Enable Hologres to return the same result as MySQL for the COUNT DISTINCT operation on multiple columns.
  SELECT count(distinct a||b) FROM count_distinct_test;
  
  -- The following result is returned:
   count 
  -------
       1
  (1 row)