Migrate data from a self-managed Apache Doris database to ApsaraDB for SelectDB - ApsaraDB for SelectDB

Method selection

The following table compares the available migration methods and their use cases.

Method	Use cases	Benefits	Reference
Migration using theApsaraDB for SelectDB data migration feature (Recommended)	Suitable for all scenarios.	Avoids data transfer fees. Simplifies migration configuration with a visual UI. Tracks task progress on the migration task list and details pages.	Data migration
Use an OSS bucket	For large data volumes already on Alibaba Cloud.	Avoids data transfer fees. Note If Doris and SelectDB are in the same Region, the migration uses internal network traffic. Supports large-scale data migration.	Use an OSS bucket to migrate data
Use a catalog	Data is stored on Alibaba Cloud and the data volume is small. Note This method is suitable for scenarios such as migrating data from Alibaba Cloud E-MapReduce (EMR) clusters.	Avoids data transfer fees. Note If Doris and SelectDB are in the same Region, migration uses internal network traffic. Does not depend on external components.	Use a catalog to migrate data

The following sections describe how to migrate offline data from Doris to SelectDB by using a catalog or by exporting to and importing from OSS.

Use an OSS bucket to migrate data

Prerequisites

OSS requirements:
- OSS is activated. For more information, see Activate OSS.
- The RAM user used to access OSS has read and write permissions on OSS objects. For more information about how to grant permissions, see Permissions and access control overview.
Account requirements:

You have created a database account. For more information, see Account management.

Preparations

Create a bucket in OSS. For more information, see Create buckets.

Important

Ensure that the OSS bucket and your ApsaraDB for SelectDB instance are in the same region.

In this example, the bucket is named test-selectdb.

Step 1: Export the source data

Log on to the source cluster.

For information about how to log on to a self-managed Doris database, see Connecting by MySQL Protocol - Apache Doris.

(Optional) Prepare sample data.

If you have data ready for migration, you can skip this step.

Create a database.

Run the following statement to create a database:
```
CREATE DATABASE source_db;
```

Create a table.

Run the following statement to create a table:

CREATE TABLE IF NOT EXISTS source_tb (
    `c1` int(11) NULL,
    `c2` string NULL,
    `c3` bigint NULL
)
DISTRIBUTED BY HASH(c1) BUCKETS 20
PROPERTIES("replication_num" = "1");

Insert sample data.

INSERT INTO source_tb VALUES
    (1, 'doris', 18),
    (2, 'nereids', 20),
    (3, 'pipelibe', 99999),
    (4, 'Apache', 122123455),
    (5, null, null);

Verify the data.

Run the following statement to verify the sample data:

SELECT * FROM `source_tb` limit 10;

The following result is returned:

+--------------+--------------+--------------+
| c1           | c2           | c3           |
+--------------+--------------+--------------+
| 1            | doris        | 18           |
| 3            | pipelibe     | 99999        |
| 5            |              |              |
| 2            | nereids      | 20           |
| 4            | Apache       | 122123455    |
+--------------+--------------+--------------+

Back up the CREATE TABLE statement for the source table.

You can use the SHOW CREATE TABLE statement to view the table creation statement of the target table and back it up. The following is an example.

SHOW CREATE TABLE source_tb ;

The following result is returned:

+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table           | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| source_tb       | CREATE TABLE `source_tb` (
                        `c1` int NULL,
                        `c2` text NULL,
                        `c3` bigint NULL
                      ) ENGINE=OLAP
                      DUPLICATE KEY(`c1`)
                      DISTRIBUTED BY HASH(`c1`) BUCKETS 20
                      PROPERTIES (
                      "file_cache_ttl_seconds" = "0",
                      "is_being_synced" = "false",
                      "storage_medium" = "hdd",
                      "storage_format" = "V2",
                      "inverted_index_storage_format" = "V2",
                      "light_schema_change" = "true",
                      "disable_auto_compaction" = "false",
                      "enable_single_replica_compaction" = "false",
                      "group_commit_interval_ms" = "10000",
                      "group_commit_data_bytes" = "134217728"
                      ); |
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Rows returned: [1], Time taken: [22ms]

Export data to the OSS bucket.

Run the EXPORT statement to export data to the OSS bucket.

Syntax:

EXPORT TABLE table_name
[PARTITION (p1[,p2])]
[WHERE]
TO export_path
[opt_properties]
WITH S3
[s3_properties];

The following table describes the parameters.

Parameter	Required	Description
table_name	Yes	The name of the table from which you want to export data.
partition	No	Specifies the partitions of the table that you want to export.
where	No	Filters the data to be exported.
export_path	Yes	The path where the exported files are stored. This can be a directory, or a directory with a file prefix, such as `s3://path/to/my_file_`.
opt_properties	No	Specifies properties for the export job. For more information, see Syntax of opt_properties.
s3_properties	Yes	Specifies properties related to the S3 protocol. For more information, see Syntax of s3_properties.

Syntax of opt_properties:

[PROPERTIES ("key"="value", ...)]

You can specify the following parameters.

Parameter	Required	Description
label	No	A label for the export job. If you do not specify a label, the system generates one automatically.
column_separator	No	The column delimiter for the exported data. The default value is `\t`. This parameter supports multi-byte characters and is used only for the CSV file format.
line_delimiter	No	The row delimiter for the exported data. Defaults to `\n` and supports multi-byte characters. This parameter is used only for the CSV file format.
columns	No	Specifies the columns of the table that you want to export.
format	No	The file format of the exported data. Supported formats: parquet, orc, csv, csv_with_names, and csv_with_names_and_types. The default format is csv.
max_file_size	No	The maximum size of a single exported file. If the result set exceeds this size, it is split into multiple files. Value range: 5 MB to 2 GB Default value: 1 GB When you specify the export file format as ORC (that is, the format_as parameter is ORC), the actual size of the split file is `ceil (max_file_size/64) * 64` MB.
parallelism	No	The parallelism of the export job. The default value is 1. An export job starts a number of threads equal to the parallelism value to execute `select into outfile` statements. (If the parallelism value is greater than the number of tablets in the table, the system automatically sets the parallelism to the number of tablets. This means each `select into outfile` statement is responsible for one tablet).
delete_existing_files	No	Specifies whether to delete all files in the destination path. `false` (default): Does not delete files in the destination path. true: Deletes all files in the directory specified by `export_path` and then exports data to that directory. For example: If `"file_path" = "/user/tmp"`, all files and directories in `"/user/"` will be deleted. If `"file_path" = "/user/tmp/"`, all files and directories in `"/user/tmp/"` are deleted. Warning Specifying `delete_existing_files = true` is a dangerous operation. We recommend that you use this setting only in a test environment. To use the delete_existing_files parameter, you can submit a ticket to Alibaba Cloud customer service. The delete_existing_files parameter takes effect only after the SelectDB technical team adds the `enable_delete_existing_files = true` configuration to the fe.conf file and restarts the FE.
with_bom	No	The default value is `false`. If set to `true`, the exported files are encoded in UTF-8 with a Byte Order Mark (BOM). This parameter is valid only for CSV-related file formats.
timeout	No	The timeout period for the export job. The default value is 2 hours. Unit: seconds.

Syntax of s3_properties:

("key"="value"[,...])

You can specify the following parameters.

Parameter	Required	Description
s3.endpoint	Yes	The endpoint of the S3-compatible storage destination. This example uses Alibaba Cloud OSS for migration. In this case, this parameter is the OSS data access `Endpoint`. To obtain the endpoint, see Regions and Endpoints. Important Make sure the OSS bucket and your ApsaraDB for SelectDB instance are in the same region.
s3.access_key	Yes	The AccessKey ID for the S3-compatible storage destination. This example uses Alibaba Cloud OSS for migration. This is the `AccessKey ID` of the RAM user that is used to access OSS.
s3.secret_key	Yes	The AccessKey secret for the S3-compatible storage destination. This example uses Alibaba Cloud OSS for migration, and this is the `AccessKey secret` of the RAM user that accesses OSS.
s3.region	Yes	The region of the S3-compatible storage destination. Because this example uses Alibaba Cloud OSS for migration, this parameter is the region of the OSS bucket. For information about how to obtain the region, see Regions and endpoints.
use_path_style	No	The default is `false`. By default, the S3 SDK uses the virtual-hosted style for access. However, some object storage systems may not support virtual-hosted style access. You can add the `use_path_style` parameter to force path-style access. Note URIs currently support three schemes: `http://`, `https://`, and `s3://`. If you use `http://` or `https://`, the `use_path_style` parameter controls whether path-style access is used for the S3 protocol target. If you use `s3://`, the S3 target will be accessed by using the virtual-hosted style.

The following code provides an example:

EXPORT TABLE  source_tb  -- Export data from the source_tb table.
            TO "s3://test-selectdb/test/"  -- Export data to the test folder in the test-selectdb bucket.
            PROPERTIES (
                "label" = "test_export", -- A label for the job.
                "format" = "orc",   -- The storage format of the file is ORC.
                "max_file_size" = '2048MB', -- The size for splitting files.
                'parallelism' = '10'  -- The export parallelism is 10.
            ) WITH s3 (
                "s3.endpoint" = "oss-cn-hangzhou-internal.aliyuncs.com",  -- The URL for accessing the OSS bucket.
                "s3.region" = "oss-cn-hangzhou",  -- The region of the OSS bucket.
                "s3.secret_key"="yourAccessKeySecret",  -- The AccessKey secret of the RAM user for accessing OSS.
                "s3.access_key" = "LTAI****************"  -- The AccessKey ID of the RAM user for accessing OSS.
            );

Verify that the data is exported.

You can verify the data export in one of the following ways:
- Run a statement.
  
  In a client connected to Doris, run the following statement to check the export status:
```
SHOW export;
```
  - Export successful: If the State is FINISHED, the export succeeded.
  - Export failed: If the State in the result is CANCELLED, the data export failed. You can use the information in ErrorMsg to troubleshoot the failure.
- Use the OSS console.
  
  Log on to the OSS console and check if the corresponding files were generated in the specified export path.

Step 2: Import data

Log on to the destination cluster. For more information, see Connect to an ApsaraDB for SelectDB instance by using DMS.
Create the destination table.
Create a database.
1. (Optional) Create a database.
  
  If you have a destination database, you can skip this step.
  
  Run the following statement to create a database:
```
CREATE DATABASE aim_db;
```
2. Create a table.
  
  Run the CREATE TABLE statement that you backed up in the export step.
  
  The following code provides an example:
```
CREATE TABLE IF NOT EXISTS aim_tb (
    `c1` int(11) NULL,
    `c2` string NULL,
    `c3` bigint NULL
) ENGINE=OLAP
DUPLICATE KEY(`c1`)
DISTRIBUTED BY HASH(c1) BUCKETS 20
PROPERTIES("replication_num" = "1");
```

Import the data.

Use S3 load to import OSS data to SelectDB. For the S3 load syntax and more examples, see OSS Load.

The following code provides an example:

 LOAD LABEL label_1  -- A unique identifier for the job. You can specify a custom name.
                (
                    DATA INFILE("s3://test-selectdb/test/59ab2e9dc4ec4c04-9e50e45a6fda2c8e_0.orc")  -- The OSS path of the data file exported in the previous step.
                    INTO TABLE aim_tb  -- The name of the table to which you want to import data.
                    FORMAT AS ORC   -- The format of the import file, which must be the same as the exported file format.
                )
                WITH S3
                (
                    "AWS_PROVIDER" = "OSS",
                    "AWS_REGION" = "oss-cn-hangzhou",  -- The region of the OSS bucket.
                    "AWS_ENDPOINT" = "oss-cn-hangzhou-internal.aliyuncs.com",  -- The URL for accessing the OSS bucket.
                    "AWS_ACCESS_KEY" = "LTAI****************",  -- The AccessKey ID of the RAM user for accessing OSS.
                    "AWS_SECRET_KEY"="yourAccessKeySecret"  -- The AccessKey secret of the RAM user for accessing OSS.
                )
                PROPERTIES
                (
                    "timeout" = "3600"   -- The timeout period for the import job.
                );

Verify the import results.

You can check the import results in one of the following ways:
- Run a statement.
  
  On a client connected to SelectDB, you can execute the following statement to check the export status.
```
SHOW load;
```
  Import successful: If the State in the result is FINISHED, the data import is successful.
- Query the destination table. The following code provides an example:
```
SELECT * FROM `aim_tb` limit 10;
```
  The following result is returned:
```
+--------------+--------------+--------------+
| c1           | c2           | c3           |
+--------------+--------------+--------------+
| 1            | doris        | 18           |
| 3            | pipelibe     | 99999        |
| 5            |              |              |
| 2            | nereids      | 20           |
| 4            | Apache       | 122123455    |
+--------------+--------------+--------------+
```
  The data in the destination table matches the source data from Step 1: Export the source data, confirming a successful import.

Use a catalog to migrate data

Prerequisites

Ensure network connectivity between the Doris instance and the SelectDB instance.
- The Doris instance and the SelectDB instance must be in the same VPC. If they are in different VPCs, you must first resolve the network connectivity issue. For more information, see How do I resolve network connectivity issues between a SelectDB instance and a data source?
- The IP address of the Doris instance has been added to the whitelist of SelectDB. For more information, see Set a whitelist.
- If the Doris instance uses a whitelist, you have added the IP address range of the SelectDB instance to the whitelist of the Doris instance.
  - To get the IP range of the VPC where your SelectDB instance resides, see How do I find the IP CIDR block of the VPC where my ApsaraDB for SelectDB instance is located?
  - To get the public IP address of your SelectDB instance, use the ping command on its public endpointSelectDB.
The SelectDB version cannot be lower than the Doris version.

Note
SelectDB is a cloud-native real-time data warehouse built on Doris. For the relationship between their versions, see the Kernel Release Notes.
You are familiar with catalogs and their basic operations. For more information, see Data lakehouse.

Sample environment

This example migrates data from the doris_t table in the doris_db database of Doris to the test_doris2SelectDB table in the test_db database of SelectDB. Modify the parameters based on your actual requirements. The example environment is as follows:

Destination database: test_db
Destination table: test_doris2SelectDB
Source database: doris_db
Source table: doris_t

Prepare the source data

Log on to your source Doris database and perform the following operations:

Create a database.
```
CREATE DATABASE doris_db;
```

Create a table.

CREATE TABLE doris_t
(
    id int,
    name string,
    age int
)
DISTRIBUTED BY HASH(id) BUCKETS 4
PROPERTIES("replication_num" = "1");

Insert data.

INSERT INTO doris_t VALUES
(1, 'Alice', 25),
(2, 'Bob', 30),
(3, 'Charlie', 35),
(4, 'David', 40),
(5, 'Eve', 45);

Procedure

Connect to the SelectDB instance. For more information, see Connect to a Cloud Database SelectDB instance by using a MySQL client.

Create a JDBC catalog for the Doris database.

CREATE CATALOG doris_catalog PROPERTIES (
 "type"="jdbc",
 "user"="root",
 "password"="123456",
 "jdbc_url" = "jdbc:mysql://127.0.0.1:9030/doris_db",
 "driver_url" = "mysql-connector-java-8.0.25.jar",
 "driver_class" = "com.mysql.cj.jdbc.Driver"
)

Parameter	Required	Default	Description
user	Yes	None	The username for the Doris database account.
password	Yes	None	The password for the Doris database account.
jdbc_url	Yes	None	The JDBC connection string, which must include the connection address of the Doris database. Format: `jdbc:mysql://<host>:<port>/<database>` `host`: The IP address of the Doris database. `port`: The port number of the Doris database. `database`: The name of the database that you want to access. Example: `jdbc:mysql://127.0.0.1:9030/doris_db`
driver_url	Yes	None	The name of the JDBC driver JAR file. Note We recommend that you use `mysql-connector-java-8.0.25.jar`. If you want to use a different JAR file, please submit a support ticket.
driver_class	Yes	None	The class name of the JDBC driver. The recommended setting is `com.mysql.cj.jdbc.Driver`.
lower_case_table_names Note This parameter is renamed `lower_case_meta_names` in version 4.0.	No	"false"	Specifies whether to synchronize the database and table names from the external JDBC data source in lowercase. `true`: Allows you to query databases and tables with non-lowercase names by maintaining a mapping from lowercase names to their actual names in the remote system. In this case, database, table, and column names are all converted to lowercase. `false`: Does not allow you to query databases and tables with non-lowercase names. Important For SelectDB 3.0: When the `lower_case_table_names` parameter of the FE is set to `1` or `2`, the `lower_case_table_names` parameter of the Catalog must be set to `true`. When the `lower_case_table_names` parameter of the FE is set to `0`, the parameter for the Catalog can be `true` or `false`. For SelectDB 4.0: When the `lower_case_table_names` parameter of the FE is set to `0` or `2`, database, table, or column names are not converted. When the `lower_case_table_names` parameter of the FE is set to `1`, table names are converted to lowercase, while database and column names are not.
only_specified_database	No	"false"	Specifies whether to synchronize only the specified database. `true`: Synchronizes only the database specified in the JDBC URL. `false`: Synchronizes all databases accessible through the JDBC URL.
include_database_list	No	""	When `only_specified_database=true`, specify one or more databases to synchronize, separated by commas. Database names are case-sensitive.
exclude_database_list	No	""	When `only_specified_database=true`, specify the databases to exclude from synchronization, separated by commas. Database names are case-sensitive.
meta_names_mapping	No	""	If an external data source has names that are identical except for their case, such as DORIS and doris, Doris reports an error when querying the Catalog due to ambiguity. To resolve this conflict, configure the`meta_names_mapping` parameter. For more information, see Lowercase name synchronization. Important This parameter applies only to SelectDB version 4.0.

View the catalogs.

SHOW CATALOGS; -- Check whether the catalog was created successfully.

The following result is returned:

+--------------+--------------+----------+-----------+-------------------------+---------------------+------------------------+
| CatalogId    | CatalogName  | Type     | IsCurrent | CreateTime              | LastUpdateTime      | Comment                |
+--------------+--------------+----------+-----------+-------------------------+---------------------+------------------------+
| 436009309195 | doris_catalog | jdbc      |           | 2024-08-06 17:09:08.058 | 2024-07-19 18:04:37 |                        |
|            0 | internal     | internal | yes       | UNRECORDED              | NULL                | Doris internal catalog |
+--------------+--------------+----------+-----------+-------------------------+---------------------+------------------------+

(Optional) Switch to the external catalog doris_catalog.

You can view and access data in the external catalog doris_catalog in the same way you access data in the internal catalog.

Note
Currently, ApsaraDB for SelectDB supports only read operations on data in an External Catalog.
```
SWITCH doris_catalog;
```
(Optional) Switch to the internal catalog.

If you did not perform step 4, skip this step.
```
SWITCH internal;
```
(Optional) Create a database.

If you have a destination database, you can skip this step.
```
CREATE database test_db;
```
Switch to the destination database.
```
USE test_db;
```
Create a table.

If the destination table exists, ensure its column data types map correctly to those of the source table.

If you do not have a destination table, ensure that the column data types map correctly to the Doris source table when you create the table.

For more information about column mapping, see Data type mappings.
```
CREATE TABLE test_doris2SelectDB
(
    id int,
    name string,
    age int
)
DISTRIBUTED BY HASH(id) BUCKETS 4
PROPERTIES("replication_num" = "1");
```

Migrate the data.

INSERT INTO test_doris2SelectDB SELECT *  FROM doris_catalog.doris_db.doris_t;

Verify the data import.
```
SELECT *  FROM test_doris2SelectDB;
```

Migrate incremental data

In production, Doris data includes offline and incremental data. To migrate incremental data to SelectDB for query acceleration, consider the following methods:

You can write a copy of data to SelectDB in parallel while producing Doris data.
You can use periodic jobs to read partitioned data from Doris and write it to SelectDB.