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.
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
withselect "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;
- Sample code:
- 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 theOFFSET 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, theDESC 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;
- Sample code:
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;
- Problem description:
- 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;
- Problem description:
- 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.