Migrate StarRocks data

Prerequisites

• Ensure SelectDB between your StarRocks instance and your ApsaraDB for SelectDB instance.

  • All nodes of the SelectDB cluster and the ApsaraDB for SelectDB instance are in the same VPC. If they are in different VPCs, you must first establish network connectivity. For more information, see How do I establish network connectivity between an ApsaraDB for SelectDB instance and a data source?

  • Add the IP address of the StarRocks instance to the SelectDB of the ApsaraDB for SelectDB instance. For more information, see Configure a whitelist.

  • If your StarRocks instance uses a whitelist, add the CIDR block of the VPC where your SelectDB instance resides to the StarRocks whitelist.

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

• Be familiar with catalogs and their basic operations. For more information, see Data lakehouse.

Sample environment

This example demonstrates how to migrate data from the SR_t table in the starRocks_db database (StarRocks) to the test_SR2SelectDB table in the test_db database (SelectDB). Replace the parameters with your actual values. The sample environment is as follows:

• Destination database: test_db

• Destination table: test_SR2SelectDB

• Source database: starRocks_db

• Source table: SR_t

Prepare sample source data

Log in to your source StarRocks database and perform the following steps.

1. Create a database.

   CREATE DATABASE starRocks_db;

2. Create a table.

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

3. Insert data.

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

Procedure

1. Connect to your SelectDB instance. For more information, see Connect to an instance.

2. Create a StarRocks JDBC catalog. For more information, see JDBC data source.

   CREATE CATALOG starrocks_catalog PROPERTIES (
       "type"="jdbc",
       "user"="root",
       "password"="123456",
       "jdbc_url" = "jdbc:mysql://127.0.0.1:3306/demo",
       "driver_url" = "mysql-connector-java-8.0.25.jar",
       "driver_class" = "com.mysql.cj.jdbc.Driver",
       "checksum" = "fdf55dcef04b09f2eaf42b75e61ccc9a"
   )

   Parameters

   Parameter	Required	Default	Description
   user	Yes	None	StarRocks database account.
   password	Yes	None	The password for the StarRocks database.
   jdbc_url	Yes	None	The JDBC connection string. It must contain the connection address of the StarRocks database.
   driver_url	Yes	None	The name of the JDBC driver JAR package. Note Use mysql-connector-java-8.0.25.jar. To use a different JAR package, submit a ticket for support.
   driver_class	Yes	None	The class name of the JDBC driver. Set this parameter to com.mysql.cj.jdbc.Driver.
   lower_case_table_names (Renamed to 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: Lets you query databases and tables with names that are not in lowercase by maintaining a mapping from lowercase names to the actual names in the remote system. In this case, the names of databases, tables, and columns are all converted to lowercase. false: You cannot query databases and tables with names that are not in lowercase. Important For SelectDB 3.0: If the lower_case_table_names parameter of the FE is set to 1 or 2, you must set the lower_case_table_names parameter of the catalog to true. If the lower_case_table_names parameter of the FE is set to 0, the catalog parameter can be set to true or false. For SelectDB 4.0: If the lower_case_table_names parameter of the FE is set to 0 or 2, the names of databases, tables, and columns are not converted. If the lower_case_table_names parameter of the FE is set to 1, table names are converted to lowercase, but 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 in the JDBC URL.
   include_database_list	No	""	When only_specified_database=true, specifies the databases to synchronize. Separate multiple databases with commas (,). Database names are case-sensitive.
   exclude_database_list	No	""	When only_specified_database=true, specifies the databases to exclude from synchronization. Separate multiple databases with commas (,). Database names are case-sensitive.
   meta_names_mapping	No	""	If the external data source has names that differ only in case, such as DORIS and doris, querying the catalog may cause an error due to ambiguity. In this case, configure the meta_names_mapping parameter to resolve the conflict. For more information, see Case sensitivity settings. Important This parameter applies only to SelectDB 4.0.

3. View the catalog.

   SHOW CATALOGS; -- Verify that the catalog was created.

   The following result is returned.

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

4. (Optional) Switch to the SR_catalog external catalog.

   You can view and access data in the SR_catalog external catalog just as you would an internal catalog.

   Note

   Currently, SelectDB supports only read operations on data in external catalogs.

   SWITCH SR_catalog;

5. (Optional) Switch to the internal catalog.

   If you did not perform Step 4, skip this step.

   SWITCH internal;

6. (Optional) Create a database.

   If you have already created the target database, skip this step.

   CREATE database test_db;

7. Switch to the target database.

   USE test_db;

8. Create a table.

   If you have an existing destination table, check that its column types correspond to the StarRocks source data column types.

   If you do not have a destination table, ensure that its column types correspond to the StarRocks source data column types when you create the table.

   For more information about column mapping, see Type mapping.

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

9. Migrate the data.

   INSERT INTO test_SR2SelectDB SELECT *  FROM doris_catalog.SR_db.SR_t;

10. View the data import status.

    SELECT *  FROM test_SR2SelectDB;