Define control files

You can define rules in a control file by which OBLOADER can preprocess data before the data is imported.

File template

Template of a defined control file:

lang=java 
(
  Column name Position of byte offsets (optional) "Preprocessing function" (optional) Mapping definition (optional),
  Column name Position of byte offsets (optional) "Preprocessing function" (optional) Mapping definition (optional),
  Column name Position of byte offsets (optional) "Preprocessing function" (optional) Mapping definition (optional),
  Column name Position of byte offsets (optional) "Preprocessing function" (optional) Mapping definition (optional)
);

Sample control file:

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

Notice

• field_position indicates the column position in the data file.
• The name of the control file is table name.ctrl.
• The order and case of columns defined in the control file must be consistent with that defined in the table.
• If you import data in the --pos format, you must define the positions of byte offsets.
• If you import data in the --cut, --csv, --sql format, you do not need to define the positions of byte offsets.
• For more information about preprocessing functions, see Preprocessing functions.

Usage notes

• If the column name defined in the table contains database keywords, you must add a grave accent (`) both before and after the column name to escape the column name. Sample code:

  lang=java
  (
      `WHEN` "lower(`WHEN`)",
      c2 "nanvl(c2,'0')"
  );
  

• In OBLOADER 3.x, the field mapping feature of map files is integrated into control files. Map files are no longer supported. Sample code:

  lang=java(
      C1 "NONE" map(1),
      C2 "NONE" map(1), // Multi-column mapping.
      C3 "CONSTANT('xxx')", // Specify xxx as the CONSTANT.
      C4 "SEQUENCE(100, 1)" // 100 is the start value, and 1 is the step size.
  );
  

Notice

• field_position indicates the position in the data file.
• The name of the control file is table name.ctrl.
• The order and case of columns defined in the control file must be consistent with that defined in the table.
• If you import data in the POS format, you must define the positions of byte offsets.
• If you import data in the --cut, --csv, --sql format, you do not need to define the positions of byte offsets.
• For more information about preprocessing functions, see Preprocessing functions.

Example

• Import data in the POS format. Sample code:

  lang=java
  (
    c1 position(1:5) "trim(c1)",                   -- Extract the first to fifth bytes of characters from values in Column c1 and truncate the leading and trailing spaces of the result.
    c2 position(7:25) "replace(trim(c2),'',' ')"   -- Extract the 7th to 25th bytes of characters from values in Column c2, truncate the leading and trailing spaces of the result, and replace empty characters with spaces.
  );
  

• Import data in the CUT, CSV, SQL formats. Sample code:

  lang=java
  (
    c1 "lower(c1)",                                                                           -- Convert letters of values in Column c1 to lowercase.
    c2 "ltrim(c2)",                                                                           -- Truncate leading spaces of values in Column c2.
    c3 "rtrim(c3)",                                                                           -- Truncate trailing spaces of values in Column c3.
    c4 "substr(c4,0,5)",                                                                      -- Extract a substring of five characters from values in Column c4. The extraction starts from the first byte of each value.
    c5 "trim(c5)",                                                                            -- Truncate leading and trailing spaces of values in Column c5.
    c6 "upper(c6)",                                                                           -- Convert letters of values in Column c6 to uppercase.
    c7 "nanvl(c7,'0')",                                                                       -- Verify values in Column c7 and return 0 for non-numeric values.
    c8 "replace(c8,'a','A')",                                                                 -- Replace Letter 'a' of values in Column c8 with Letter 'A'.
    c9 "nvl(c9,'nill')",                                                                      -- Verify whether values in Column c9 are null and return nill for null values.
    c10 "to_timestamp(c10,'yyyyMMddHHmmssSSS')",                                              -- Convert values in Column c10 to the yyyy-MM-dd HH:mm:ss.SSS format. Return null if formatting fails.
    c11 "length(c11)",                                                                        -- Calculate the length of values in Column c11.
    c12 "lpad(c12,5,'x')",                                                                    -- Append a string of five 'x' to the left of values in Column c12.
    c13 "rpad(c13,5,'x')",                                                                    -- Append a string of five 'x' to the right of values in Column c13.
    c14 "convert(c14,'utf-8','gbk')",                                                         -- Convert the character set of values in Column c14 from GBK to UTF-8.
    c15 "concat(c15, '_suffix')",                                                             -- Concatenate values in Column c15 with a specific constant.
    c16 "none",                                                                               -- Do not process values in Column c16. Return the value of the corresponding column.
    c17 "systimestamp",                                                                       -- Do not process values in Column c17. Return the timestamp of the current server without processing the parameter value.
    c18 "constant('1')",                                                                      -- Do not process values in Column c18. Return a constant 1.
    c19 "tmsfmt(c19,'yyyyMMddHHmmssSSS','20210310000000000','yyyyMMddHHmmssSSS')",            -- Verify the dates of values in Column c19. If the verification fails, return the default value.
    c20 "lpadb(c20,5,'x')",                                                                   -- Append five single-byte 'x' to the left of values in Column c20.
    c21 "lpadb(c21,5,'x')",                                                                   -- Append five single-byte 'x' to the left of values in Column c21.
    c22 "case when length(trim(c22))<18 then 'Y' else 'N' end",                               -- Verify whether values in Column c22 match the specified condition. If yes, return 'Y'.
    c23 "case length(trim(c23)) when '1' then 'one' when '2' then 'two' else 'unknown' end",  -- Verify whether values in Column c23 are equal to the specified value. If yes, return the corresponding value.
    C24 "SYSDATE",                                                                            -- Set values in Column C24 to the current date.
    C25 "MASK(C25)",                                                                          -- Mask values in Column C25 by replacing uppercase letters, lowercase letters, and numbers (with X, x, and n by default respectively).
    C26 "MASK_FIRST_N(C26,'A','a','b',3)",                                                    -- Mask the first N characters of values in Column C26 by replacing uppercase letters, lowercase letters, and numbers with respective masking characters. The default value of N is 0, indicating to start from the first character.
    C27 "MASK_LAST_N(C27,'A','a','b',3)",                                                     -- Mask the last N characters of values in Column C27 by replacing uppercase letters, lowercase letters, and numbers with respective masking characters. The default value of N is 0, indicating to start from the last character.
    C28 "MASK_SHOW_FIRST_N(C28,'A','a','b',3)",                                               -- Mask all characters except the first N characters of values in Column C28. The default value of N is 0, indicating to start from the first character.
    C29 "MASK_SHOW_LAST_N(C29,'A','a','b',3)",                                                -- Mask all characters except the last N characters of values in Column C29. The default value of N is 0, indicating to start from the last character.
    C30 "REVERSE(C30)",                                                                       -- Reverse characters of values in Column C30.
  )
  

  Notice
  OBLOADER versions later than 3.0.0 no longer support --null-replacer,--empty-replacer, --default-date , and --date-format options.