Multi-database import

OBLOADER V4.3.5 and later versions support importing database objects to multiple databases or schemas in a single import task, while maintaining compatibility with the original single-database import behavior.

Overview

The import feature for multiple databases allows you to specify the range of objects to be imported using an object expression in the format <schema.object>, such as test.table1, test1.*, *.*, test[0-9].tbl, test*.table, or test?.tbl.

When you use the multi-database import feature, the system will print an import list and ask for confirmation. You can use the -y option to skip the confirmation.

Implementation

Single-database mode

If the object parameters such as --table and --view do not contain a specific database or schema (i.e., they do not contain a period (.)), the system operates in single-database mode, maintaining its original behavior.

Multi-database mode

If any object parameter value contains a specific database or schema (i.e., it contains a period (.)), the system switches to multi-database mode. Examples include test.table1, test1.*, or *.*.

In multi-database mode, a single multi-database import command is split into multiple single-database import subtasks. Here's how it works:

• The system parses the object parameters such as --table and --view, and distributes the <schema.object> expressions to corresponding subtasks for each database or schema, specifying the import range for each.

• If an object does not contain a specific database or schema (e.g., tbl2 in --table 'db1.tbl1, tbl2' -D 'db2'), the system uses the default database specified by -D for that object, while keeping other options unchanged.

In multi-database mode, importing objects to different databases or schemas is equivalent to executing multiple single-database import tasks. Here's an example:

obloader -D test --table 'test1.tbl1,test2.tbl2' ...
-> obloader -D test1 --table tbl1 ...
-> obloader -D test2 --table tbl2 ...

obloader -D test --table 'tbl1,test2.tbl2' ...
-> obloader -D test --table tbl1 ...
-> obloader -D test2 --table tbl2 ...

By default, the import directory structure matches the dataset generated by multi-database export, which is organized by database/schema levels. Based on the source and characteristics of the directory structure of the data to be imported, multi-database import can be categorized into the following three scenarios.

• The directory structure to be imported is generated by OBDUMPER.

  Assume that you specify -f ./outputs when you use OBLOADER for multi-database import, and the outputs directory has the following structure:

  ./outputs/
  └── data
      ├── test
      │   ├── TABLE
      │   │   ├── sample_tbl1.csv
      │   │   ├── sample_tbl1-schema.sql
      │   │   ├── sample_tbl2.csv
      │   │   └── sample_tbl2-schema.sql
      │   └── VIEW
      │       ├── sample_tbl1_view-schema.sql
      │       └── sample_tbl2_view-schema.sql
      └── test1
          ├── TABLE
          │   ├── sample_tbl1.csv
          │   ├── sample_tbl1-schema.sql
          │   ├── sample_tbl2.csv
          │   └── sample_tbl2-schema.sql
          └── VIEW
              ├── sample_tbl1_view-schema.sql
              └── sample_tbl2_view-schema.sql
  
  7 directories, 12 files
  

  In this case, if you specify the following parameters for multi-database import: --table 'test*.*' --view 'test*.*' -f ./outputs, the system will locate the data in the corresponding database/schema directories based on the <schema.object> expressions and import it.

  Assume that the target OceanBase Database has three databases named test, test1, and test2. In this case, multi-database import will import the objects in the ./outputs/data/test directory to the test database, and the objects in the ./outputs/data/test1 directory to the test1 database. The test2 database does not have a corresponding directory structure, so no data will be imported to it.

• The directory structure to be imported is not generated by OBDUMPER.

  This directory structure is created by the user or another program. If you perform multi-database import in this directory structure, the behavior is equivalent to executing multiple single-database import tasks in the same directory.

  Assume that you specify --table 'test*.*' --view 'test*.*' -f ./outputs, and the outputs directory has the following structure:

  ./outputs/
  ├── sample_tbl1.csv
  ├── sample_tbl1-schema.sql
  ├── sample_tbl2.csv
  └── sample_tbl2-schema.sql
  
  0 directories, 4 files
  

  Assume that the target OceanBase Database has three databases named test, test1, and test2. Since the import program cannot identify the database names from the files, multi-database import will import all objects in the ./outputs directory to the test, test1, and test2 databases in the target OceanBase Database.

• The directory structure to be imported is not generated by OBDUMPER, and the --file-regular-expression parameter is used.

  The --file-regular-expression parameter allows you to use regular expressions to define rules for matching specific files for import. It also allows you to use capture groups to extract schema and table names from the file names.

  Assume that you specify --table 'test*.*' --file-regular-expression '(?<schema>[a-z0-9]+)\.(?<table>[a-z_0-9]+)\.csv' -f ./outputs for import, and the outputs directory has the following structure:

  ./outputs/
  ├── test1.sample_tbl2.csv
  └── test.sample_tbl1.csv
  
  0 directories, 2 files
  

  Assume that the target OceanBase Database has three databases named test, test1, and test2. In this case, multi-database import will import the ./outputs/test1.sample_tbl2.csv file to the test1 database, and the ./outputs/test.sample_tbl1.csv file to the test database. The test2 database does not have a corresponding directory structure, so no data will be imported to it.

Examples

Example 1: Single-database export + single-database import (original behavior)

• Import the test database exported from /outputs back to the test database.

  ./obloader -h xxx.xxx.xxx.xxx -P 2883 -u test@mysql#cluster_a -p ****** -D test --csv --table '*' -f /outputs
  

• Import the test database exported from /outputs to another database named test1.

  ./obloader -h xxx.xxx.xxx.xxx -P 2883 -u test@mysql#cluster_a -p ****** -D test1 --csv --table '*' -f /outputs
  

Example 2: Multi-database export + multi-database import (import each database as is)

Before you proceed, you run the following command to export the test1, test2, and test3 databases.

./obdumper -h xxx.xxx.xxx.xxx -P 2883 -u test@mysql#cluster_a -p ****** --csv --table 'test[1-3].*' -f /outputs

• Import all databases

  • Method 1

    ./obloader -h xxx.xxx.xxx.xxx -P 2883 -u test@mysql#cluster_a -p ****** --csv --table 'test[1-3].*' -f /outputs
    

  • Method 2

    ./obloader -h xxx.xxx.xxx.xxx -P 2883 -u test@mysql#cluster_a -p ****** --csv --table '*.*' -f /outputs
    

  • Method 3

    ./obloader -h xxx.xxx.xxx.xxx -P 2883 -u test@mysql#cluster_a -p ****** --csv --table 'test1.*, test2.*, test3.*' -f /outputs
    

• Import only the test1 and test2 databases

  • Method 1

    ./obloader -h xxx.xxx.xxx.xxx -P 2883 -u test@mysql#cluster_a -p ****** --csv --table 'test[1-2].*' -f /outputs
    

  • Method 2

    ./obloader -h xxx.xxx.xxx.xxx -P 2883 -u test@mysql#cluster_a -p ****** --csv --table 'test1.*,test2.*' -f /outputs
    

  • Method 3

    ./obloader -h xxx.xxx.xxx.xxx -P 2883 -u test@mysql#cluster_a -p ****** --csv --table 'test1.*' --table 'test2.*' -f /outputs
    

Example 3: Multi-database export + single-database import (select one database for import)

After you export multiple databases, you can import them to the same database by using the -f parameter to specify the input directory.

Before you proceed, you run the following command to export the test1, test2, and test3 databases.

./obdumper -h xxx.xxx.xxx.xxx -P 2883 -u test@mysql#cluster_a -p ****** --csv --table 'test[1-3].*' -f /outputs

• Import the test2 database exported from /outputs to the test2 database. This scenario executes the multi-database import logic. The test2.* parameter specifies the database, and the -D test parameter is ignored.

  ./obloader -h xxx.xxx.xxx.xxx -P 2883 -u test@mysql#cluster_a -p ****** -D test --csv --table 'test2.*' -f /outputs
  

• Import the test1 subdirectory as a single database to the target database test. This scenario executes the single-database import logic.

  ./obloader -h xxx.xxx.xxx.xxx -P 2883 -u test@mysql#cluster_a -p ****** -D test --csv --table '*' -f /outputs/data/test1
  

• Point to the root directory and use --table '*' to import the test1, test2, and test3 databases as a single database to the test database. This scenario executes the original single-database import logic.

  ./obloader -h xxx.xxx.xxx.xxx -P 2883 -u test@mysql#cluster_a -p ****** -D test --csv --table '*' -f /outputs
  

  Note

  When you import multiple databases to a single database, the original single-database import logic is executed. The input directory is specified by using the -f parameter.

Example 4: Tenant-level import (import each database as is)

Import all objects of all databases, including definitions and data.

./obloader -h xxx.xxx.xxx.xxx -P 2883 -u test@mysql#cluster_a -p ****** --table '*.*' --ddl --csv --all -f /outputs