Using Control Files with obloader for Data Import and Export in OceanBase

When importing or exporting data, a control file can be used together with the obloader tool to meet various business requirements.

Control File Template

lang=java(
  column_name byte_offset(optional) "preprocess_function" mapping_definition(optional),
  column_name byte_offset(optional) "preprocess_function" mapping_definition(optional),
  column_name byte_offset(optional) "preprocess_function" mapping_definition(optional)
);

Simple example:

lang=java
server=mysql/oracle
(
    c1 "nvl(c1,'not null')" map(field_position),
    c2 "none" map(field_position)
);

Key parameters:

field_position – the column position of the pre‑processed data in the input file.

Control file name must follow the convention table_name.ctl and match the case used in the database.

The order and case of column names in the control file must be identical to those defined in the table.

Usage Cases

3.1 Test Data

cat /data/test/TABLE/test.dat
1@##oceanbase@##2023-01-12 15:00:00.0@##1@##ob@##1@##ob
2@##oceanbase@##2023-01-12 15:00:00.0@##2@##ob@##2@##ob
3@##oceanbase@##2023-01-12 15:00:00.0@##3@##ob@##3@##ob

create table test01 (
id int(10) not null primary key,
name varchar(10),
time timestamp not null default '1971-01-01 01:01:01',
blank varchar(255) null
);

create table test02 (
id int(10) not null primary key,
name varchar(10) not null,
time timestamp not null,
bar varchar(255) default null,
blank varchar(255) default null,
line varchar(255) default null,
mark  varchar(255) default null,
test  varchar(255) not null
);

3.2 Case 1 – Full Column Import (table columns ≤ file columns)

Control file:

vi /data/test01.ctl
lang=java(
id "none" map(1),
name "none" map(2),
time "none" map(3),
blank "none" map(5)
);

Import command:

./obloader -h 10.186.60.94 -P 2883 -u root -p rootroot \
-c ywob -t mysql_yw_tent -D ywdb --table test01 --cut \
-f /data/test/TABLE/test.dat --log-path /data/ --external-data \
--replace-data --column-splitter '@##' --ctl-path /data/test01.ctl

Result shows SUCCESS and the data can be verified with a SELECT query.

3.3 Case 2 – Partial Column Import (table columns > file columns)

Initial control file:

vi /data/test01.ctl
lang=java(
id "none" map(1)
);

Import fails with Error:"Field 'id' doesn't have a default value" . After adding the missing column mapping:

vi /data/test01.ctl
lang=java(
id "none" map(1),
name "none" map(2)
);

The import succeeds.

3.4 Case 3 – Column Count Mismatch

Control file for test02 (table has more columns than the file):

vi /data/test02.ctl
lang=java(
id "none" map(1),
name "none" map(2),
time "none" map(3),
bar "none" map(4),
blank "none" map(5),
line "none" map(6),
mark "none" map(7)
);

Import command similar to the previous one succeeds, but on another OB environment it fails with Error: Column count doesn't match value count at row 1 because the data file has fewer columns and the last column lacks a delimiter.

Solutions:

Modify the control file to map an extra placeholder column.

Alter the table so the last column can be NULL.

Append the missing delimiter (e.g., @## ) to the data file.

3.5 Using obdumper with Control Files

The same column‑count error can also appear during export if the database name case does not match (e.g., using uppercase in the command while the actual name is lowercase).

Additional Tips

After import, verify each field’s data to ensure proper alignment and correct display of Chinese characters.

You can also use SUBSTR(char,position[,length]) for data preprocessing, e.g., SUBSTR('abc',0,3) .