Overview on data migration

This topic describes the concepts, methods, and tools for migrating data from your local server or other cloud environments to OceanBase Database. In this topic, the target database is an OceanBase database.

Application scenarios

Data migration is a common operation in database O&M and is typical in the following scenarios:

• Cluster load adjustment and data center relocation
• Database replacement
• Logical database replication, including read-write splitting, database disaster recovery, and multi-site high availability
• Data replication

Migration paths

This topic defines the following migration paths:

• Data migration from other databases to OceanBase Database
• Data migration from files to OceanBase Database

Data migration from other databases to OceanBase Database

We recommend that you use OceanBase Migration Service (OMS) to migrate data from a database to OceanBase Database. OMS supports the following data sources:

• MySQL
• Oracle
• DB2 LUW
• TiDB
• PostgreSQL

If OMS does not support your database, choose another migration path. You can dump data from your database as files and then use OMS to migrate the data from the files to OceanBase Database.

Data migration from files to OceanBase Database

You can import data from CSV files to OceanBase Database in the following ways:

• LOAD DATA statements in MySQL tenants
• DataX or DataX Web
• obloader

You can import data from SQL files to OceanBase Database in the following ways:

• obloader
• MySQL or OceanBase Command-Line Client (OBClient) commands
• Subtopics
• Database clients

You can import data from Parquet files to OceanBase Database in the following ways:

• obloader
• LOAD DATA statements

You can import data from ORC files to OceanBase Database by using obloader.

Migration tools

OceanBase Database provides a wide range of data migration methods, including:

• OceanBase solutions

  • OMS, which is designed for large-scale data migration
  • obloader, which is designed for data import from files

• CLI tools

  • Standard SQL statements LOAD DATA and INSERT, which are easy to use and suitable for small-scale data import
  • CREATE EXTERNAL TABLE statement, which allows you to directly query external data files for more flexible data analysis
  • CREATE TABLE AS SELECT statement

• Third-party integrated tools

  Flink OceanBase Connector, DataX OceanBase Writer Plugin, Kafka, and Canal

OMS

Characteristics:

OMS has the following characteristics:

• Real-time data migration without interrupting or affecting business applications
• High-performance, secure, and reliable data migration
• All-in-one interaction

Scenarios:

OMS is suitable for large-scale data migration between databases.

References:

For more information about OMS, see OMS documentation.

obloader

Characteristics:

obloader has the following characteristics:

• Allows you to import database object definitions and table data from local disks, Network Attached Storage (NAS), Hadoop Distributed File System (HDFS), Alibaba Cloud Object Storage Service (OSS), Tencent Cloud Object Storage (COS), Huawei Cloud Object Storage Service (OBS), Apache Hadoop, and Amazon Simple Storage Service (S3).
• Allows you to import files in the INSERT SQL format that are exported by using mysqldump.
• Allows you to import data files in standard formats, such a CSV, INSERT SQL, Apache ORC, and Apache Parquet.
• Allows you to set data preprocessing rules and configure field mappings between files and tables.
• Supports features such as import throttling, memory exhaustion prevention, resumption after an interruption, and automatic retries.
• Allows you to specify a custom log directory to store bad data and conflicting data during import.
• Automatically splits large files without consuming additional storage space.
• Supports encryption of sensitive parameters specified in commands, such as the database account and password and the cloud storage account and password.

Scenarios:

obloader is suitable for large-scale data import.

References:

For more information about obloader, see obloader documentation.

LOAD DATA statements

LOAD DATA LOCAL

Characteristics:

The LOAD DATA LOCAL statement has the following characteristics:

• Allows you to import and insert local files to OceanBase Database as network streams.
• Supports importing only a small amount of data at a time.
• Supports importing only one file at a time.
• Allows you to import CSV, SQL, and Parquet files.

Scenarios:

The LOAD DATA LOCAL statement is suitable for small-scale data import.

References:

For more information about the LOAD DATA LOCAL statement, see Import data by using the LOAD DATA statement.

LOAD DATA FROM OSS

Characteristics:

The LOAD DATA FROM OSS statement has the following characteristics:

• Allows you to import files from Alibaba Cloud OSS to OceanBase Database.
• Allows you to import multiple files from Alibaba Cloud OSS at a time.
• Supports importing only CSV files.

Scenarios:

The LOAD DATA FROM OSS statement is suitable for large-scale data import.

References:

For more information about the LOAD DATA FROM OSS statement, see LOAD DATA (Oracle mode) and LOAD DATA (MySQL mode).

INSERT SQL

Scenarios:

• The INSERT INTO VALUES statement is suitable for writing a small amount of data to an internal table.
• The INSERT INTO SELECT FROM <table_name> statement is suitable for writing the query result of another internal or external table to the target table. In other words, it is suitable for data migration between tables.
• The INSERT /*+ [APPEND |direct(need_sort,max_error,'full')] enable_parallel_dml parallel(N)*/ INTO table_name select_sentence statement is suitable for full and incremental direct load.

References:

For more information about the INSERT statement, see INSERT (Oracle mode) and INSERT (MySQL mode).

CREATE EXTERNAL TABLE

Scenarios:

External tables are a key feature in a database management system. Generally, the data of a table in a database is stored in the storage space of the database, while an external table has data stored in an external storage service.

References:

For more information about external tables, see Overview (Oracle mode) and Overview (MySQL mode).

Flink

Scenarios:

Flink OceanBase Connector is suitable for importing data from Flink in real time.

References:

For more information, see Use Flink CDC to synchronize data from a MySQL database to OceanBase Database.

Canal

Scenarios:

Canal is suitable for importing data to OceanBase Database in real time.

References:

For more information, see Use Canal to synchronize data from a MySQL database to OceanBase Database.

Kafka

Scenarios:

CloudCanal is suitable for migrating or synchronizing data from MySQL, Oracle, and PostgreSQL databases to OceanBase Database.

References:

For more information, see Best practices for integrating OceanBase Database with Kafka.

Select a migration solution

This section describes the solutions of migrating data from common data sources, in order to help you quickly select an appropriate migration strategy as needed. The migration solutions are introduced by data source type.

Object storage services

The following table describes the solutions for importing data from object storage services of cloud service providers to OceanBase Database.

File systems

The following table describes the solutions for importing data from local or distributed file systems to OceanBase Database.

Streaming systems

The following table describes the solutions for importing data from streaming systems to OceanBase Database.

Databases

The following table describes the solutions for importing data from other databases to OceanBase Database.