ApsaraDB for SelectDB: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.

Example environment

This example shows how to migrate data from the SR_t table in the starRocks_db database of StarRocks to the test_SR2SelectDB table in the test_db database of SelectDB. When you perform the migration, change the parameters as needed. The example environment is as follows:

• Destination database: test_db

• Destination table: test_SR2SelectDB

• Source database: starRocks_db

• Source table: SR_t

Example: Prepare source data

Log on to your source StarRocks instance and complete 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 the SelectDB instance. For more information, see Connect to an instance.

   Note

   The SWITCH command does not work when you log on using Data Management Service (DMS). Connect using a MySQL client.

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 value	Description
   user	Yes	None	The account for the StarRocks database.
   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; -- Check if the catalog was created successfully.

   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 external catalog SR_catalog.

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

   Note

   Currently, Alibaba Cloud 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 destination database, skip this step.

   CREATE database test_db;

7. Switch to the destination database.

   USE test_db;

8. Create a table.

   If you already have a destination table, check that the column types in the destination table correspond to the column types in the StarRocks source data.

   If you do not have a destination table, ensure that the column types correspond to the column types in the StarRocks source data 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. Check the data import status.

    SELECT *  FROM test_SR2SelectDB;