Control file guide

This topic has been verified against the actual behavior of OBLOADER & OBDUMPER V4.3.7.

A control file defines column-level processing rules for a table during data import or export. You can use a control file when the column order in a data file differs from the table schema, when you need to skip fields, generate constant or sequence values, clean or mask data, or process a fixed-length file.

This topic applies to both OBLOADER and OBDUMPER. However, their data flows differ:

• OBLOADER: file field → column mapping or position parsing → preprocessing → target table column.
• OBDUMPER: database table column → column selection and ordering → preprocessing → output file field.

To get started quickly, read Quick start and Control file activation rules first. For more complex scenarios, see the sections about column mapping, position definitions, preprocessing functions, and conditional expressions.

For detailed information about each tool, see OBLOADER control files and OBDUMPER control files.

Features and scope

Control files can be used with data in the CSV, CUT, SQL, POS, ORC, Parquet, and Avro formats. position applies only to fixed-length POS files. When OBLOADER imports a POS file by using --pos, you must provide a control file. To export a POS file, OBDUMPER still uses --cut. Set the column delimiter to an empty string and define column lengths in a control file. Control files are not used to process data in DDL or MIX mode.

Quick start

Import data: reorder and clean columns, and populate a default value

Assume that the target table customer contains the following columns:

id, name, status, created_at, source

Each row in the import file contains four fields in the following order: id, name, status, and created_at. The following processing is required:

• Remove leading and trailing spaces from id, name, and status.
• Convert name to lowercase.
• Convert the status values A and I to ACTIVE and INACTIVE, respectively, and convert all other status values to UNKNOWN.
• Convert a time value from the yyyyMMddHHmmss format to the yyyy-MM-dd HH:mm:ss format.
• Write the constant legacy to the target column source.

Create customer.ctrl with the following content:

lang=java
(
  id "trim(id)" map(1),
  name "lower(trim(name))" map(2),
  status "case upper(trim(status)) when 'A' then 'ACTIVE' when 'I' then 'INACTIVE' else 'UNKNOWN' end" map(3),
  created_at "to_timestamp(created_at,'yyyyMMddHHmmss','yyyy-MM-dd HH:mm:ss')" map(4),
  source "constant('legacy')"
);

Place the control file in the /data/controls directory and specify the directory by using --ctl-path:

./obloader \
  -h xx.x.x.x -P 2883 -u 'test@mysql#cluster_a' -p '******' \
  -D USERA --table customer \
  --csv -f /data/customer.csv \
  --ctl-path /data/controls

Export data: select, order, and mask columns

When you export the customer table, assume that you want to export only the id, name, status, and created_at columns. You also want to retain the first character of name while masking the other characters and convert status to uppercase:

lang=java
(
  id "none",
  name "mask_show_first_n(name,'X','x','n',1)",
  status "upper(status)",
  created_at "none"
);

./obdumper \
  -h xx.x.x.x -P 2883 -u 'test@mysql#cluster_a' -p '******' \
  -D USERA --table customer \
  --csv -f /data/output \
  --ctl-path /data/controls

Control file activation rules

Control file and table names

When --ctl-path points to a directory, control files must be named in the <table_name>.ctrl format. A control file base name is matched with a table name based on the following rules:

• In MySQL compatible mode, matching follows the lower_case_table_names setting of the target database. Matching is case-sensitive when the value is 0 and case-insensitive when the value is 1 or 2.

• In Oracle compatible mode, unquoted table names are normalized to uppercase before matching.

• The tool recursively searches for .ctrl files in the directory specified by --ctl-path and all its subdirectories. Therefore, you can organize control files by schema or business module.

• Within the same task, make sure that only one control file matches each table. If multiple control files in different directories match the same table, the configuration read later may overwrite the configuration read earlier. The tool does not report the duplicate files, which may cause an unintended configuration to take effect.

When OBLOADER imports only one table and --ctl-path points directly to a .ctrl file, OBLOADER binds the file directly to the table without validating the control file name against the table name.

Startup options

Use --ctl-path <file_or_directory> to enable a control file. Take note of the following conflicts:

• For OBLOADER, --ctl-path and --auto-column-mapping cannot be used together.
• --ctl-path cannot be used together with command-line options that include or exclude columns or exclude columns by data type. To select columns, define all column selection rules in the control file.
• When OBLOADER imports a POS file by using --pos, you must specify --ctl-path. To export a POS file, OBDUMPER uses --cut. Set the column delimiter to an empty string and use --ctl-path to specify the control file.

Verify that a control file takes effect

After the task starts, check the logs. If a control file is successfully parsed, a log similar to the following one is generated:

Parse ctrl definition: "/data/controls/customer.ctrl" success

Common logs indicating that a control file did not take effect include:

The control file: "{}" is unexpected, ignore it
The control file: "{}" is invalid, ignore it.
No valid control file was defined for the table: "..."

If one of these logs appears, check whether the control file base name follows the table-name matching rules of the target database. Also check the target table scope, file extension, syntax, and the actual path specified by --ctl-path.

Basic syntax

A control file has the following basic structure:

lang=java
(
  <column> [position(<start>:<end>) | position(<length>)] ["<function>"] [map(<index>)],
  ...
);

Observe the following rules:

1. lang is required. We recommend that you use lang=java. The syntax also accepts lang=sql. Both settings use the built-in function execution engine of the tool and do not push expressions down to the database for execution.
2. server is optional. You can set it to server=mysql or server=oracle to select the semantics of a small number of functions that behave differently in the two compatibility modes. If you specify this setting, place it after the lang setting. If you omit it, MySQL semantics are used.
3. Enclose column definitions in a pair of parentheses and separate them with commas. Do not add a comma after the last column definition.
4. End the file with a semicolon (;).
5. Enclose preprocessing functions or expressions in double quotation marks and string constants in single quotation marks.
6. Keywords and function names are case-insensitive. For readability, function names in the examples are lowercase.
7. Control files do not support inline comments starting with -- or //. Do not put explanatory text in a .ctrl file.
8. In MySQL compatible mode, control file column names are always matched in a case-insensitive manner. In Oracle compatible mode, column names that are not enclosed in square brackets or backticks are normalized to uppercase before matching. Column names enclosed in square brackets or backticks preserve their case and must exactly match the database column names, for example, `Order` or [Order].

Column definition semantics

Meaning of column names

In OBLOADER, the column name in each column definition is the name of a target table column. In OBDUMPER, it is the name of a source table column to query and export.

Only columns listed in a control file participate in data processing for the current task. Therefore, use the control file to manage column selection, exclusion, and output order.

Sequential mapping by default

During import, if you do not specify map(n), ordinary columns consume file fields in the order in which the columns are defined in the control file. Generated columns do not consume file fields.

For example, assume that a file contains the fields id,name,status in this order:

lang=java
(
  id,
  name,
  status,
  source "constant('legacy')"
);

The id, name, and status columns read the first, second, and third file fields, respectively. The source column is assigned a generated constant.

Explicit mapping by using map(n)

map(n) applies only to OBLOADER. It specifies that the current target column reads the n-th field in the file, where n starts from 1.

For example, assume that file fields are ordered as name,unused,id and the target table requires id,name,name_copy,source:

lang=java
(
  id map(3),
  name map(1),
  name_copy map(1),
  source "constant('legacy')"
);

This example also demonstrates that:

• You can reorder file fields.
• You can skip an unnecessary file field, such as the second field unused.
• Multiple target columns can map to the same file field.
• Generated columns do not require map(n).

If you need to reorder, skip, or reuse fields, explicitly specify map(n) for all ordinary columns. This prevents implicit mapping errors if column definitions are reordered later.

Preprocessing reads only the current column

A column name in a preprocessing expression represents the input value of the current column. Cross-column references are not supported. Therefore, the following expression cannot concatenate the first_name and last_name columns:

full_name "concat(first_name,last_name)"

The arguments to concat must be transformations of the current column or constants. For example, use the following expression to add a prefix to the current name column:

name "concat('user_',trim(name))"

Fixed-length POS files

Absolute positions

position(start:end) specifies the start and end byte positions of a field in a row. Both start and end start from 1, and both positions are included.

Assume that each row has the following structure:

0001ALICE     XXCN

• Bytes 1 to 4: id.
• Bytes 5 to 14: name.
• Bytes 15 to 16: placeholder bytes that are not imported.
• Bytes 17 to 18: region.

lang=java
(
  id position(1:4) "trim(id)",
  name position(5:14) "rtrim(name)",
  _FILLER position(15:16),
  region position(17:18) "upper(region)"
);

_FILLER is a placeholder field for POS import. OBLOADER reads the field but does not write it to the target table. You can define multiple _FILLER fields in the same control file.

Relative lengths

position(length) specifies that the current field is length bytes long and starts immediately after the preceding field. Example:

lang=java
(
  id position(4) "trim(id)",
  name position(10) "rtrim(name)",
  region position(2) "upper(region)"
);

To reduce maintenance costs, use either absolute positions or relative lengths consistently in the same control file. Do not mix the two forms.

Truncation and padding during POS export

When OBDUMPER exports a POS file, position also determines the field width:

• If the processed value exceeds the specified number of bytes, the tool truncates the value without splitting a multibyte character.
• If the value is shorter than the specified width, the tool pads spaces on the right.
• If the value is NULL, the tool fills the entire field with spaces.

POS positions are measured in bytes, not characters. If data contains Chinese characters, emoji, or other multibyte characters, calculate the width based on the file encoding specified for the task.

Generated columns

The value of a generated column does not come from the current input field. During import, a generated column does not consume a file field.

DB_SEQUENCE is intended mainly for import in Oracle compatible mode. Before you use it, make sure that the sequence exists in the target database and that the import account has permission to access it. Enclose the sequence name in single quotation marks.

An in-memory SEQUENCE guarantees that generated values are unique within the same task. However, do not rely on a strict ordering relationship between values generated by parallel tasks and row numbers in the source file.

Preprocessing functions

This section describes commonly used functions and their key behaviors. For complete function signatures and parameter descriptions, see OBLOADER preprocessing functions and OBDUMPER preprocessing functions.

Function composition

Preprocessing functions can be nested. The innermost function is executed first. Examples:

name "lower(trim(name))"
code "replace(upper(trim(code)),'-','')"

The first expression removes leading and trailing spaces before converting the value to lowercase. The second expression executes trim, upper, and replace in sequence.

A column name in a function must refer to the current column. Constants can also be used as function arguments, for example, concat('CN-',trim(code)).

Null, empty, and invalid values

• NULL indicates no value. An empty string '' and a string that contains only spaces are not necessarily equivalent to NULL.
• NVL(value,default) returns the default value only when the input is NULL.
• NANVL(value,default) returns the default value when the input is NULL, a blank string, or a value that cannot be recognized as a number. For a valid numeric value, it returns the value after removing leading and trailing spaces.
• Some functions throw conversion exceptions if a date format, numeric argument, or hexadecimal string is invalid. During import, the row typically enters the bad-data handling process. Check the error logs and bad file when troubleshooting the issue.

General and string functions

Null and numeric functions

Date and time functions

Date formats use Java date format strings. Common fields include yyyy for year, MM for month, dd for day, HH for hour in the 24-hour format, mm for minute, ss for second, and SSS for millisecond. Note that MM and mm have different meanings.

In the current version, YEAR, MONTH, HOUR, MINUTE, WEEK, and the DAY_OF_* functions expect the default input format yyyy-MM-dd HH:mm:ss. If a source value uses a different format, use TO_TIMESTAMP to convert it to this format before extracting the required part.

When OBLOADER uses TO_TIMESTAMP and cannot convert a non-null value, the function throws an exception. OBLOADER marks the record as bad and writes it to ob-loader-dumper.bad. Whether the task fails depends on configurations such as the maximum number of errors.

Examples:

created_at "to_timestamp(created_at,'yyyyMMddHHmmss','yyyy-MM-dd HH:mm:ss')"
created_at "tmsfmt(created_at,'yyyyMMddHHmmss','1970-01-01 00:00:00','yyyy-MM-dd HH:mm:ss')"
expire_date "date_add(expire_date,30,'DAY','yyyy-MM-dd')"
created_at "timestamp_trunc(created_at,'DAY','yyyy-MM-dd HH:mm:ss')"

SYSDATE and SYSTIMESTAMP use the clock and time zone of the host where OBLOADER or OBDUMPER runs, rather than those of the target database server. If time precision is important, verify the host clock and time zone settings first.

Masking functions

Masking functions replace uppercase letters, lowercase letters, and digits based on their character types. The default replacement characters are X, x, and n, respectively. Symbols, spaces, and other characters remain unchanged.

Examples:

phone "mask_show_last_n(phone,'X','x','*',4)"
name "mask_show_first_n(name,'X','x','n',1)"
id_card "mask_first_n(id_card,'X','x','*',14)"

For functions whose names contain N, explicitly specify all replacement characters and the n argument. This prevents the actual masking range from differing from the expected range because an argument is omitted.

Digest, encryption, and decryption functions

Examples:

id_card "sm3_digest(id_card,'HEX')"
secret "sm4_encrypt(secret,'MDEyMzQ1Njc4OWFiY2RlZg==','BASE64')"
secret "sm4_decrypt(secret,'MDEyMzQ1Njc4OWFiY2RlZg==','BASE64')"

Do not store encryption or decryption keys in public repositories, chat records, or configurations without access control. Before use, verify the key length, Base64 encoding, and ciphertext encoding.

Conditional expressions

Conditional expressions select different conversion results based on the value of the current column. Simple CASE and searched CASE expressions are supported.

For separate reference topics about conditional expressions, see OBLOADER conditional expressions and OBDUMPER conditional expressions.

Simple CASE

A simple CASE expression compares the result of an expression with multiple constants in sequence:

CASE <expression>
  WHEN <constant> THEN <result>
  [WHEN <constant> THEN <result> ...]
  [ELSE <result>]
END

Example:

lang=java
(
  status "case upper(trim(status)) when 'A' then 'ACTIVE' when 'I' then 'INACTIVE' else 'UNKNOWN' end"
);

Searched CASE

A searched CASE expression evaluates multiple Boolean conditions in sequence:

CASE
  WHEN <condition> THEN <result>
  [WHEN <condition> THEN <result> ...]
  [ELSE <result>]
END

The following example converts a null value to UNKNOWN, retains the values A and B, and converts all other values to OTHER:

lang=java
(
  code "case when code is null then 'UNKNOWN' when code in ('A','B') then code else 'OTHER' end"
);

The following example performs a numeric comparison:

lang=java
(
  score "case when nanvl(score,'0')>=60 then 'PASS' else 'FAIL' end"
);

Conditional operators

Take note of the following considerations when you use conditional expressions:

• You can use only the current column value, constants, and function results derived from the current column. Cross-column conditions are not supported.
• If no WHEN clause matches and no ELSE clause is specified, the result is NULL.
• Relational comparisons use long-integer semantics. If a value can be blank or non-numeric, use NANVL to convert it first.
• Integer division discards the fractional part. Division by zero causes processing of the record to fail.
• XOR, BETWEEN, and <=> are not supported.
• Explicit grouping of complex Boolean conditions with parentheses is not supported. Split a complex rule into multiple simple WHEN clauses and verify the result with a small data sample.

Common scenarios

Scenario 1: The file column order differs from the table column order

Explicitly specify map(n) for all ordinary columns:

lang=java
(
  id map(3),
  name "trim(name)" map(1),
  status "upper(status)" map(4),
  created_at "to_timestamp(created_at,'yyyyMMddHHmmss','yyyy-MM-dd HH:mm:ss')" map(2)
);

Scenario 2: Discard fields from a file

For delimited text, you do not need to define dummy columns for fields that you want to discard. Skip the corresponding field indexes directly:

lang=java
(
  id map(1),
  name map(3),
  status map(5)
);

For a POS file, use _FILLER position(...) to explicitly identify a placeholder region.

Scenario 3: Use a default time when the date format is invalid

If an invalid date must be written to the bad file, use TO_TIMESTAMP:

created_at "to_timestamp(created_at,'yyyyMMddHHmmss','yyyy-MM-dd HH:mm:ss')"

If your business requirements allow the import to continue with a fixed time value, use TMSFMT:

created_at "tmsfmt(created_at,'yyyyMMddHHmmss','1970-01-01 00:00:00','yyyy-MM-dd HH:mm:ss')"

These two strategies produce different data-quality outcomes. Select a strategy based on your business requirements. Do not use a default value for invalid dates solely to reduce the number of reported errors.

Scenario 4: Mask sensitive data during export

lang=java
(
  customer_id,
  customer_name "mask_show_first_n(customer_name,'X','x','n',1)",
  phone "mask_show_last_n(phone,'X','x','*',4)",
  id_card "sm3_digest(id_card,'HEX')"
);

Before a full export, run the export task with a small data sample and verify the masking result. In addition to checking whether the task succeeds, verify that sensitive columns are transformed as expected.

Limitations and considerations

• A control file provides column-level processing. It is not a SQL query engine. Expressions are executed locally by OBLOADER or OBDUMPER and are not pushed down to the database.
• An expression can read only the current column. Cross-column calculations are not supported.
• Inline comments are not supported in control files.
• Do not use Groovy script expressions. The current version does not expose them as a supported public capability.
• The matching rules for column names and for control file base names and table names depend on the database compatibility mode and identifier notation. For more information, see Control file activation rules and Basic syntax.
• LPAD and RPAD measure values in characters. LPADB, RPADB, and POS position measure values in bytes.
• SYSDATE and SYSTIMESTAMP use the clock of the host where OBLOADER or OBDUMPER runs.
• Relational comparisons and arithmetic operations in conditional expressions use long-integer semantics and do not apply to decimal-precision calculations.
• Before using a new or modified control file for a full task, verify it with a small data sample.

Troubleshooting

Pre-production checklist

1. The control file base name follows the table-name matching rules of the target database, and the file name has the .ctrl extension.
2. --ctl-path points to the correct file or directory.
3. The file starts with lang=java, column definitions are separated by commas, and the file ends with );.
4. The file does not contain comments starting with -- or //.
5. Column selection, exclusion, and ordering rules are defined only in the control file, without conflicting command-line options.
6. When fields are reordered, skipped, or reused during import, map(n) is explicitly specified for all ordinary columns.
7. Positions in a POS file are verified in bytes based on the actual file encoding.
8. All date formats are verified with real samples, and either the error or default-value strategy is selected explicitly.
9. Conditional expressions do not contain cross-column references, decimal relational comparisons, or unsupported operators.
10. Masking, digest, encryption, and decryption output is verified with a small sample, and keys are not exposed in public locations.
11. The control file parsing success log appears in the task logs for the small data sample.
12. Actual import or export results are inspected instead of judging success only from the final task status.