(资料图片)

作者：刘书盛

热衷技术分享、编写技术文档

原创作品

oceanbase 数据库

原创内容未经授权不得随意使用，转载请联系小编并注明来源,谢谢！

预处理函数：

函数签名	返回类型	描述
LOWER(char)	String	对参数值中的字母转换为小写。 参数值可以是列名、常量或者嵌套表达式。当参数值是常量时，请使用单引号。 示例：LOWER("A")。
LTRIM(char[,set])	String	对参数值从左到右进行匹配并截断操作。 参数 char的值可以是列名、常量或函数表达式。参数 set的值是常量。若省略参数 set，则默认按照空格进行匹配。 示例：LTRIM(" abc ")。
RTRIM(char[,set])	String	对参数值从右到左进行匹配并截断操作。 参数 char的值可以是列名、常量或函数表达式，参数 set的值是常量。若省略参数 set，则默认按照空格进行匹配。 示例：RTRIM(" abc ")。
SUBSTR(char,position[,length ])	String	对参数值根据起始位置与长度进行截断。 参数 char的值可以是列名、常量或函数表达式，参数 position的值是数值型常量，参数 length的值是数值型常量。 示例：SUBSTR("abc",0,3)。
TRIM(char)	String	对参数值的左右两端进行空格截断。参数 char的值可以是列名、常量或函数表达式。 示例：TRIM(" abc ")。
REVERSE(char)	String	对参数值进行逆序操作。 示例：REVERSE("C1")将颠倒 C1 列值的字符串顺序。
UPPER(char)	String	对参数值中的字母转换为大写。 参数 char的值可以是列名、常量或者嵌套表达式。当值为常量时，请使用单引号。 示例：UPPER("a")。
NANVL(char, default)	String	对参数值进行数值合法验证，若验证失败，则返回默认值。 参数 char的值可以是列名、常量或函数表达式，参数 default的值是常量。 示例：NANVL("1","1")。
REPLACE(char,search[,replacement ])	String	对参数值根据搜索条件进行替换。 参数 char的值可以是列名、常量或函数表达式，参数 search的值是常量，参数 replacement的值是常量。 示例：REPLACE("abc","a","A")。
NVL(char, default)	String	对参数值进行判空，若为空，则返回默认值。 参数 char的值可以是列名、常量或函数表达式，参数 default的值是常量。 示例：NVL("a","--")。
TO_TIMESTAMP(char,fmt1[,fmt2])	String	对参数值进行日期格式化，若转换失败返回 NULL。 参数 char的值可以是列名、常量或函数表达式，参数 fmt1的值是日期解析模板，可选参数 fmt2的值是日期格式化模板。 参数 fmt2返回值的默认格式：yyyy-MM-dd HH:mm:ss.SSS。
LENGTH(char)	String	对参数值进行长度计算。 参数 char的值可以是列名、常量或函数表达式。
LPAD(char,length[,pad_string])	String	对参数值从左侧追加指定长度的字符，若指定的长度小于参数长度则截断。 参数 char的值可以是列名、常量或函数表达式，参数 length和 pad_string的值是常量，且参数 pad_string的值要求传入单字节字符。 示例：LPAD("a",1,"x")。
RPAD(char,length[,pad_string])	String	对参数值从右侧追加指定长度的字符，指定的长度小于参数长度则截断。 参数 char的值可以是列名、常量或函数表达式，参数 length和 pad_string的值是常量，且参数 pad_string的值要求传入单字节字符。 示例：RPAD("a",1,"x")。
CONVERT(char,charset1[,charset2])	String	对参数值进行字符编码转换。 参数 char的值可以是列名、常量或函数表达式，参数 charset1和 charset2的值是常量。 示例：CONVERT("a","utf-8","gbk")。
CONCAT(char1,char2)	String	对参数值进行拼接。 参数 char1和 char2的值可以是列名、常量或函数表达式。 示例：CONCAT("a","b")。
NONE	String	对参数不作任何处理，直接返回参数值。
SYSTIMESTAMP	String	对参数不作任何处理，直接返回当前机器的时间戳。 默认格式：yyyy-MM-dd HH:mm:ss.SSS。
SYSDATE	String	对参数不作任何处理，直接返回当前机器的日期。 默认格式：yyyy-MM-dd HH:mm:ss。
CONSTANT(char)	String	对参数不作任何处理，直接返回定义的常量值。参数 char的值是常量。 示例：CONSTANT("1")。
TMSFMT(char,fmt1,default,fmt2)	String	对参数进行日期格式验证，若验证失败，则返回默认的日期格式。 示例：TMSFMT(c1,"yyyyMMddHHmmssSSS","2021-03-10 00:00:00.000","yyyy-MM-dd HH:mm:ss.SSS")。
LPADB(char,byte_size[,pad_char])	String	对参数值从左侧追加指定长度的字节，指定的长度小于参数长度则截断。 参数 char的值可以是列名、常量或函数表达式，参数 byte_size和 pad_char的值是常量。 示例：LPADB("a",1,"x")。
RPADB(char,byte_size[,pad_char])	String	对参数值从右侧追加指定长度的字节，指定的长度小于参数长度则截断。 参数 char的值可以是列名、常量或函数表达式，参数 byte_size和 pad_char的值是常量。 示例：RPADB("a",1,"x")。
MASK(string str[, string upper[, string lower[, string number]]])	String	对字段中的大小写字母、数字进行脱敏操作。 示例：MASK("C1"，A，a，b)，将 C1 列的大写字母转换为字母 A，小写字母转化为 a，数字转换为 b。
MASK_FIRST_N(string str[, int n])	String	指定对字段值的前 n 个字符进行脱敏操作。 示例：MASK_FIRST_N("C1"，"A","a","b"，5)，将 C1 列的前 5 个字符中的大写字母转换为字母 A，小写字母转化为 a，数字转换为 b。
MASK_LAST_N(string str[, int n])	String	指定对字段值的后 n 个字符进行脱敏操作。 示例：MASK_LAST_N("C1"，"A","a","b"，5)，将 C1 列的后 5 个字符中的大写字母转换为字母 A，小写字母转化为 a，数字转换为 b。
MASK_SHOW_FIRST_N(string str[, int n])	String	指定对除前 n 个字符之外的所有字符进行脱敏操作。 示例：MASK_SHOW_FIRST_N("C1"，"A","a","b"，5)，将 C1 列的除前 5 个字符之外的所有字符中的大写字母转换为字母 A，小写字母转化为 a，数字转换为 b。
MASK_SHOW_LAST_N(string str[, int n])	String	指定对除后 n 个字符之外的所有字符进行脱敏操作。 示例：MASK_SHOW_LAST_N("C1"，"A","a","b"，5)，将 C1 列的除后 5 个字符之外的所有字符中的大写字母转换为字母 A，小写字母转化为 a，数字转换为 b。
SEQUENCE(int m,int n)	String	对指定的列生成递增的序列值，m 代表初始值，n 代表递增值。 示例：SEQUENCE(100,1)生成初始值为 100，递增值为 1 的序列。
DB_SEQUENCE(string sequence)	String	指定数据库中已定义的序列导入数据。 示例：DB_SEQUENCE(seq01)，指定序列值为 seq01。说明OBLOADER 4.1.0 及之后的版本支持该功能。

场景1. 文件中的字段比数据表中的字段多

通过Obloader 控制文件对导入数据进行处理#文件模板.ctllang=java (  列名 字节偏移位置(可选) "预处理函数"（可选） 映射定义（可选）,  列名 字节偏移位置(可选) "预处理函数"（可选） 映射定义（可选）,  列名 字节偏移位置(可选) "预处理函数"（可选） 映射定义（可选）,  列名 字节偏移位置(可选) "预处理函数"（可选） 映射定义（可选）);​#举例lang=javaserver=mysql|oracle (      c1 "nvl(c1, "not null")" map(field_position),      c2 "none" map(field_position));注意：field_position 为导入的数据文件中预处理数据的列位置。控制文件的命名规范为 <表名>.ctrl。对于 --cut, --csv, --sql 格式数据，无需定义字节偏移位置。预处理函数定义请参考 预处理函数#导入文件[root@10-186-63-38 bin]# cat /tmp/backup/data/test/TABLE/t1.1.0.csv"a","b","c","d"1,66662,111111111,5553,44444,111111111,5555,33336,111111111,5556,22226,111111111,5557,66664,111111111,5558,88882,111111111,555​#t1表定义obclient [test]> desc t1;+-------+---------+------+-----+---------+-------+| Field | Type   | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| a   | int(11) | NO  | PRI | NULL   |    || b   | int(11) | YES  | MUL | NULL   |    || c   | int(11) | YES  |   | NULL   |    |+-------+---------+------+-----+---------+-------+3 rows in set (0.037 sec)​#定义obloader控制文件t1.ctl   文件路径：/tmp/t1.ctlvi /tmp/t1.ctllang=javaserver=mysql|oracle (        a "none" map(1),      b "none" map(2),   c "none" map(4));​控制文件解释：server  指定数据库为mysql或者oracle，不写也行a、b、c为表定义实际列map(1)、map(2)、map(3) 为导入数据文件中实际列，1为第一列，2位第二列，3位第三列#导入数据，导入数据文件列多余数据库表列确认数据为空obclient [test]> select * from t1;Empty set (0.025 sec)​导入数据，--column-splitter ./obloader -h 10.186.63.38 -P 2883 -uroot -p "aaAA11__" --sys-password="Wa33Wa33?*" -c demo -t ten_mysql -D test --csv --column-splitter "," --replace-data --ctl-path "/tmp/t1.ctl" --table "t1" -f /tmp/backup/#参数解释--column-splitter  指定列分隔符--replace-data    标识替换重复的数据--ctl-path      控制文件位置​[root@10-186-63-38 bin]# ./obloader -h 10.186.63.38 -P 2883 -uroot -p "aaAA11__" --sys-password="Wa33Wa33?*" -c demo -t ten_mysql -D test --csv --column-splitter "," --replace-data  --ctl-path "/tmp/t1.ctl" --table "t1" -f /tmp/backup/2023-05-24 09:57:09 [INFO] Parsed args:[--csv] true[--ctl-path] /tmp/t1.ctl[--file-path] /tmp/backup/[--column-splitter] ,[--host] 10.186.63.38[--port] 2883[--user] root[--tenant] ten_mysql[--cluster] demo[--password] ******[--database] test[--sys-user] root[--sys-password] ******[--table] [t1][--replace-data] true2023-05-24 09:57:09 [INFO] Load jdbc driver class: "com.oceanbase.jdbc.Driver" finished2023-05-24 09:57:09 [INFO] Manifest: "/tmp/backup/data/MANIFEST.bin" has been saved2023-05-24 09:57:10 [INFO] Parse ctrl definition: "/tmp/t1.ctl" success2023-05-24 09:57:10 [INFO] File: "/tmp/backup/data/test/TABLE/t2.3.0.csv" is not contained, ignore it2023-05-24 09:57:10 [INFO] Binding table: "t1" to the file: "/tmp/backup/data/test/TABLE/t1.1.0.csv" finished2023-05-24 09:57:10 [INFO] File: "/tmp/backup/data/test/TABLE/t2.2.0.csv" is not contained, ignore it2023-05-24 09:57:10 [INFO] File: "/tmp/backup/data/test/TABLE/t2.4.0.csv" is not contained, ignore it2023-05-24 09:57:10 [INFO] Find 1 resources in local path: "/tmp/backup" success. Elapsed: 57.43 ms2023-05-24 09:57:10 [INFO] Split 1 data files to 1 subfiles by 64.0 MB success. Elapsed: 20.05 ms2023-05-24 09:57:10 [INFO] Ignore to clean any tables as --truncate-table or --delete-from-table is not specified2023-05-24 09:57:10 [INFO] Query table entry and primary key for table: "t1" finished. Remain: 02023-05-24 09:57:10 [INFO] Query the leader location of "t1" finished. Remain: 02023-05-24 09:57:10 [INFO] Calculate leader: 10.186.63.39:2881 of table "t1", part: 0. Remain: 02023-05-24 09:57:10 [INFO] Waiting to refresh observer load status ......2023-05-24 09:57:10 [INFO] Refresh observer load status success. Table: "t1". Remain: 02023-05-24 09:57:10 [INFO] Refresh observer load status finished. Elapsed: 46.46 ms2023-05-24 09:57:10 [INFO] Use c.l.d.LiteBlockingWaitStrategy as available cpu(s) is 42023-05-24 09:57:10 [INFO] Create 16 slots for ring buffer finished. [10.186.63.39:2881]2023-05-24 09:57:10 [INFO] Start 8 database writer threads finished. [10.186.63.39:2881]2023-05-24 09:57:10 [INFO] Start 8 record file reader threads success2023-05-24 09:57:10 [INFO] File: "/tmp/backup/data/test/TABLE/t1.1.0.csv" has been parsed finished2023-05-24 09:57:11 [INFO] Wait for the all the workers to drain of published events then halt the workers2023-05-24 09:57:11 [INFO] Close connection count: 5 of the DataSource. Key: BIZ2023-05-24 09:57:11 [INFO] Close connection count: 25 of the DataSource. Key: SYS2023-05-24 09:57:11 [INFO] Shutdown task context finished2023-05-24 09:57:11 [INFO] ----------  Finished Tasks: 1    Running Tasks: 0     Progress: 100.00%    ----------2023-05-24 09:57:11 [INFO]All Load Tasks Finished:----------------------------------------------------------------------------------------------------------------------------     No.#     |     Type     |       Name       |       Count       |    Status----------------------------------------------------------------------------------------------------------------------------     1      |    TABLE     |        t1        |      6 -> 6       |    SUCCESS----------------------------------------------------------------------------------------------------------------------------Total Count: 6      End Time: 2023-05-24 09:57:112023-05-24 09:57:11 [INFO] Load record finished. Total Elapsed: 1.891 s2023-05-24 09:57:11 [INFO] System exit 0​#查看导入数据obclient [test]> select * from t1;+---+-------+------+| a | b   | c   |+---+-------+------+| 1 | 66662 | 555 || 3 | 44444 | 555 || 5 | 33336 | 555 || 6 | 22226 | 555 || 7 | 66664 | 555 || 8 | 88882 | 555 |+---+-------+------+6 rows in set (0.002 sec)

场景2. 文件中的字段比数据表中的字段少

#导入数据[root@10-186-63-38 bin]# cat /tmp/backup/data/test/TABLE/t2.2.0.csv"a","b","c","d"1,66662,111111111,5553,44444,111111111,5555,33336,111111111,5556,22226,111111111,5557,66664,111111111,5558,88882,111111111,555#表结构obclient [test]> desc t2;+-------+---------+------+-----+---------+-------+| Field | Type   | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| c1   | int(11) | NO  | PRI | NULL   |    || c2   | int(11) | YES  |   | NULL   |    || c3   | int(11) | YES  |   | NULL   |    || c4   | int(11) | YES  |   | NULL   |    || c5   | int(11) | YES  |   | NULL   |    |+-------+---------+------+-----+---------+-------+5 rows in set (0.100 sec)#编写控制文件,导入数据列少于表定义列，如果对多不处理，则c5列不写，数据为空NULLvi /tmp/t2.ctllang=javaserver=mysql (   c1 "none" map(1),   c2 "none" map(2),   c3 "none" map(4),   c4 "none" map(3),   c5 "none" map(2));#确认数据为空obclient [test]> select * from t2;Empty set (0.097 sec)​#导入数据 cd /root/ob-loader-dumper-4.2.1-RELEASE/bin ./obloader -h 10.186.63.38 -P 2883 -uroot -p "aaAA11__" --sys-password="Wa33Wa33?*" -c demo -t ten_mysql -D test --csv --column-splitter "," --replace-data --ctl-path "/tmp/t2.ctl" --table "t2" -f /tmp/backup/[root@10-186-63-38 bin]# ./obloader -h 10.186.63.38 -P 2883 -uroot -p "aaAA11__" --sys-password="Wa33Wa33?*" -c demo -t ten_mysql -D test --csv --column-splitter "," --replace-data  --ctl-path "/tmp/t2.ctl" --table "t2" -f /tmp/backup/2023-05-24 10:21:07 [INFO] Parsed args:[--csv] true[--ctl-path] /tmp/t2.ctl[--file-path] /tmp/backup/[--column-splitter] ,[--host] 10.186.63.38[--port] 2883[--user] root[--tenant] ten_mysql[--cluster] demo[--password] ******[--database] test[--sys-user] root[--sys-password] ******[--table] [t2][--replace-data] true​2023-05-24 10:21:07 [INFO] Load jdbc driver class: "com.oceanbase.jdbc.Driver" finished2023-05-24 10:21:07 [INFO] Manifest: "/tmp/backup/data/MANIFEST.bin" has been saved2023-05-24 10:21:07 [INFO] Parse ctrl definition: "/tmp/t2.ctl" success2023-05-24 10:21:07 [INFO] Found 1 empty tables before executing.... Elapsed: 42.03 ms2023-05-24 10:21:07 [INFO] File: "/tmp/backup/data/test/TABLE/t1.1.0.csv" is not contained, ignore it2023-05-24 10:21:08 [INFO] Binding table: "t2" to the file: "/tmp/backup/data/test/TABLE/t2.2.0.csv" finished2023-05-24 10:21:08 [INFO] Find 1 resources in local path: "/tmp/backup" success. Elapsed: 18.52 ms2023-05-24 10:21:08 [INFO] Split 1 data files to 1 subfiles by 64.0 MB success. Elapsed: 24.21 ms2023-05-24 10:21:08 [INFO] Ignore to clean any tables as --truncate-table or --delete-from-table is not specified2023-05-24 10:21:08 [INFO] Query table entry and primary key for table: "t2" finished. Remain: 02023-05-24 10:21:08 [INFO] Query the leader location of "t2" finished. Remain: 02023-05-24 10:21:08 [INFO] Calculate leader: 10.186.63.38:2881 of table "t2", part: 1. Remain: 02023-05-24 10:21:08 [INFO] Waiting to refresh observer load status ......2023-05-24 10:21:08 [INFO] Refresh observer load status success. Table: "t2". Remain: 02023-05-24 10:21:08 [INFO] Refresh observer load status finished. Elapsed: 43.75 ms2023-05-24 10:21:08 [INFO] Use c.l.d.LiteBlockingWaitStrategy as available cpu(s) is 42023-05-24 10:21:08 [INFO] Create 4 slots for ring buffer finished. [10.186.63.38:2881]2023-05-24 10:21:08 [INFO] Start 8 database writer threads finished. [10.186.63.38:2881]2023-05-24 10:21:08 [INFO] Use c.l.d.LiteBlockingWaitStrategy as available cpu(s) is 42023-05-24 10:21:08 [INFO] Create 4 slots for ring buffer finished. [10.186.63.40:2881]2023-05-24 10:21:08 [INFO] Start 8 database writer threads finished. [10.186.63.40:2881]2023-05-24 10:21:08 [INFO] Use c.l.d.LiteBlockingWaitStrategy as available cpu(s) is 42023-05-24 10:21:08 [INFO] Create 4 slots for ring buffer finished. [10.186.63.39:2881]2023-05-24 10:21:08 [INFO] Start 8 database writer threads finished. [10.186.63.39:2881]2023-05-24 10:21:08 [INFO] Start 8 record file reader threads success2023-05-24 10:21:09 [INFO] File: "/tmp/backup/data/test/TABLE/t2.2.0.csv" has been parsed finished2023-05-24 10:21:09 [INFO] Wait for the all the workers to drain of published events then halt the workers2023-05-24 10:21:09 [INFO] Close connection count: 5 of the DataSource. Key: BIZ2023-05-24 10:21:09 [INFO] Close connection count: 27 of the DataSource. Key: SYS2023-05-24 10:21:09 [INFO] Shutdown task context finished2023-05-24 10:21:09 [INFO] ----------  Finished Tasks: 1    Running Tasks: 0     Progress: 100.00%    ----------2023-05-24 10:21:09 [INFO]All Load Tasks Finished:----------------------------------------------------------------------------------------------------------------------------     No.#     |     Type     |       Name       |       Count       |    Status----------------------------------------------------------------------------------------------------------------------------     1      |    TABLE     |        t2        |      6 -> 6       |    SUCCESS----------------------------------------------------------------------------------------------------------------------------Total Count: 6      End Time: 2023-05-24 10:21:092023-05-24 10:21:09 [INFO] Load record finished. Total Elapsed: 2.214 s2023-05-24 10:21:09 [INFO] System exit 0​#确认数据obclient [test]> select * from t2;+----+-------+------+-----------+-------+| c1 | c2   | c3  | c4     | c5   |+----+-------+------+-----------+-------+| 7 | 66664 | 555 | 111111111 | 66664 || 1 | 66662 | 555 | 111111111 | 66662 || 6 | 22226 | 555 | 111111111 | 22226 || 3 | 44444 | 555 | 111111111 | 44444 || 8 | 88882 | 555 | 111111111 | 88882 || 5 | 33336 | 555 | 111111111 | 33336 |+----+-------+------+-----------+-------+6 rows in set (0.022 sec)

场景3. 生成自定义字段数据

#修改控制文件[root@10-186-63-38 bin]# cat  /tmp/t1.ctllang=javaserver=mysql (   a "none" map(1),   b "replace("33336","3","9")" map(2),   c "concat("888","666")" map(3),   d "lower("AAA")" map(4)  #参数解释：b "replace("33336","3","9")" map(2)  #导入b列数据为33336,将3用9替换插入b列c "concat("888","666")" map(3)     #拼接888666插入c列中  d "lower("AAA")" map(4)        #将d列中所有AAA转换为aaa插入d列​#导入数据文件[root@10-186-63-38 bin]# cat  /tmp/backup/data/test/TABLE/t1.1.0.csv"a","b","c","d"1,66662,111111111,"AAA"3,44444,111111111,"AAA"5,33336,111111111,"AAA"6,22226,111111111,"AAA"7,66664,111111111,"AAA"8,88882,111111111,"AAA"​#导入数据[root@10-186-63-38 bin]# ./obloader -h 10.186.63.38 -P 2883 -uroot -p "aaAA11__" --sys-password="Wa33Wa33?*" -c demo -t ten_mysql -D test --csv --column-splitter "," --replace-data  --ctl-path "/tmp/t1.ctl" --table "t1" -f /tmp/backup/2023-05-24 11:18:47 [INFO] Parsed args:[--csv] true[--ctl-path] /tmp/t1.ctl[--file-path] /tmp/backup/[--column-splitter] ,[--host] 10.186.63.38[--port] 2883[--user] root[--tenant] ten_mysql[--cluster] demo[--password] ******[--database] test[--sys-user] root[--sys-password] ******[--table] [t1][--replace-data] true​2023-05-24 11:18:47 [INFO] Load jdbc driver class: "com.oceanbase.jdbc.Driver" finished2023-05-24 11:18:47 [INFO] Manifest: "/tmp/backup/data/MANIFEST.bin" has been saved2023-05-24 11:18:47 [INFO] Parse ctrl definition: "/tmp/t1.ctl" success2023-05-24 11:18:48 [INFO] Binding table: "t1" to the file: "/tmp/backup/data/test/TABLE/t1.1.0.csv" finished2023-05-24 11:18:48 [INFO] File: "/tmp/backup/data/test/TABLE/t2.2.0.csv" is not contained, ignore it2023-05-24 11:18:48 [INFO] Find 1 resources in local path: "/tmp/backup" success. Elapsed: 42.81 ms2023-05-24 11:18:48 [INFO] Split 1 data files to 1 subfiles by 64.0 MB success. Elapsed: 20.61 ms2023-05-24 11:18:48 [INFO] Ignore to clean any tables as --truncate-table or --delete-from-table is not specified2023-05-24 11:18:48 [INFO] Query table entry and primary key for table: "t1" finished. Remain: 02023-05-24 11:18:48 [INFO] Query the leader location of "t1" finished. Remain: 02023-05-24 11:18:48 [INFO] Calculate leader: 10.186.63.39:2881 of table "t1", part: 0. Remain: 02023-05-24 11:18:48 [INFO] Waiting to refresh observer load status ......2023-05-24 11:18:48 [INFO] Refresh observer load status success. Table: "t1". Remain: 02023-05-24 11:18:48 [INFO] Refresh observer load status finished. Elapsed: 34.41 ms2023-05-24 11:18:48 [INFO] Use c.l.d.LiteBlockingWaitStrategy as available cpu(s) is 42023-05-24 11:18:48 [INFO] Create 16 slots for ring buffer finished. [10.186.63.39:2881]2023-05-24 11:18:48 [INFO] Start 8 database writer threads finished. [10.186.63.39:2881]2023-05-24 11:18:48 [INFO] Start 8 record file reader threads success2023-05-24 11:18:48 [INFO] File: "/tmp/backup/data/test/TABLE/t1.1.0.csv" has been parsed finished2023-05-24 11:18:49 [INFO] Wait for the all the workers to drain of published events then halt the workers2023-05-24 11:18:49 [INFO] Close connection count: 5 of the DataSource. Key: BIZ2023-05-24 11:18:49 [INFO] Close connection count: 23 of the DataSource. Key: SYS2023-05-24 11:18:49 [INFO] Shutdown task context finished2023-05-24 11:18:49 [INFO] ----------  Finished Tasks: 1    Running Tasks: 0     Progress: 100.00%    ----------2023-05-24 11:18:49 [INFO]​All Load Tasks Finished:​----------------------------------------------------------------------------------------------------------------------------     No.#     |     Type     |       Name       |       Count       |    Status----------------------------------------------------------------------------------------------------------------------------     1      |    TABLE     |        t1        |      6 -> 6       |    SUCCESS----------------------------------------------------------------------------------------------------------------------------Total Count: 6      End Time: 2023-05-24 11:18:492023-05-24 11:18:49 [INFO] Load record finished. Total Elapsed: 1.639 s2023-05-24 11:18:49 [INFO] System exit 0​#确认数据，数据插入OKobclient [test]> select * from t1;+---+-------+--------+------+| a | b   | c    | d   |+---+-------+--------+------+| 1 | 99996 | 888666 | aaa  || 3 | 99996 | 888666 | aaa  || 5 | 99996 | 888666 | aaa  || 6 | 99996 | 888666 | aaa  || 7 | 99996 | 888666 | aaa  || 8 | 99996 | 888666 | aaa  |+---+-------+--------+------+6 rows in set (0.002 sec)#参数解释：b "replace("33336","3","9")" map(2)  #导入b列数据为33336,将3用9替换插入b列c "concat("888","666")" map(3)     #拼接888666插入c列中  d "lower("AAA")" map(4)        #将d列中所有AAA转换为aaa插入d列

场景4. 文件是定长数据，如何导入

#修改控制文件[root@10-186-63-38 bin]# cat /tmp/t1.ctllang=javaserver=mysql (   a "none" map(1),   b "substr(b,1,2)" map(2),   c "concat("999","555")" map(3),   d "substr(d,1,1)" map(4));​  #参数解释：b "substr(b,1,2)" map(2),       #截取b列数据第一位开始，截取2位c "concat("999","555")" map(3)     #拼接888666插入c列中  d "substr(d,1,1)" map(4)        #截取d列数据第一位开始，截取2位​#导入数据文件[root@10-186-63-38 bin]# cat  /tmp/backup/data/test/TABLE/t1.1.0.csv"a","b","c","d"1,66662,111111111,"AAA"3,44444,111111111,"AAA"5,33336,111111111,"AAA"6,22226,111111111,"AAA"7,66664,111111111,"AAA"8,88882,111111111,"AAA"​#导入数据[root@10-186-63-38 bin]# ./obloader -h 10.186.63.38 -P 2883 -uroot -p "aaAA11__" --sys-password="Wa33Wa33?*" -c demo -t ten_mysql -D test --csv --column-splitter "," --replace-data  --ctl-path "/tmp/t1.ctl" --table "t1" -f /tmp/backup/2023-05-24 12:09:02 [INFO] Parsed args:[--csv] true[--ctl-path] /tmp/t1.ctl[--file-path] /tmp/backup/[--column-splitter] ,[--host] 10.186.63.38[--port] 2883[--user] root[--tenant] ten_mysql[--cluster] demo[--password] ******[--database] test[--sys-user] root[--sys-password] ******[--table] [t1][--replace-data] true​2023-05-24 12:09:02 [INFO] Load jdbc driver class: "com.oceanbase.jdbc.Driver" finished2023-05-24 12:09:02 [INFO] Manifest: "/tmp/backup/data/MANIFEST.bin" has been saved2023-05-24 12:09:02 [INFO] Parse ctrl definition: "/tmp/t1.ctl" success2023-05-24 12:09:02 [INFO] Binding table: "t1" to the file: "/tmp/backup/data/test/TABLE/t1.1.0.csv" finished2023-05-24 12:09:02 [INFO] File: "/tmp/backup/data/test/TABLE/t2.2.0.csv" is not contained, ignore it2023-05-24 12:09:02 [INFO] Find 1 resources in local path: "/tmp/backup" success. Elapsed: 52.84 ms2023-05-24 12:09:02 [INFO] Split 1 data files to 1 subfiles by 64.0 MB success. Elapsed: 33.91 ms2023-05-24 12:09:02 [INFO] Ignore to clean any tables as --truncate-table or --delete-from-table is not specified2023-05-24 12:09:03 [INFO] Query table entry and primary key for table: "t1" finished. Remain: 02023-05-24 12:09:03 [INFO] Query the leader location of "t1" finished. Remain: 02023-05-24 12:09:03 [INFO] Calculate leader: 10.186.63.39:2881 of table "t1", part: 0. Remain: 02023-05-24 12:09:03 [INFO] Waiting to refresh observer load status ......2023-05-24 12:09:03 [INFO] Refresh observer load status success. Table: "t1". Remain: 02023-05-24 12:09:03 [INFO] Refresh observer load status finished. Elapsed: 64.82 ms2023-05-24 12:09:03 [INFO] Use c.l.d.LiteBlockingWaitStrategy as available cpu(s) is 42023-05-24 12:09:03 [INFO] Create 16 slots for ring buffer finished. [10.186.63.39:2881]2023-05-24 12:09:03 [INFO] Start 8 database writer threads finished. [10.186.63.39:2881]2023-05-24 12:09:03 [INFO] Start 8 record file reader threads success2023-05-24 12:09:03 [INFO] File: "/tmp/backup/data/test/TABLE/t1.1.0.csv" has been parsed finished2023-05-24 12:09:04 [INFO] Wait for the all the workers to drain of published events then halt the workers2023-05-24 12:09:04 [INFO] Close connection count: 5 of the DataSource. Key: BIZ2023-05-24 12:09:04 [INFO] Close connection count: 24 of the DataSource. Key: SYS2023-05-24 12:09:04 [INFO] Shutdown task context finished2023-05-24 12:09:04 [INFO] ----------  Finished Tasks: 1    Running Tasks: 0     Progress: 100.00%    ----------2023-05-24 12:09:04 [INFO]All Load Tasks Finished:----------------------------------------------------------------------------------------------------------------------------     No.#     |     Type     |       Name       |       Count       |    Status----------------------------------------------------------------------------------------------------------------------------     1      |    TABLE     |        t1        |      6 -> 6       |    SUCCESS----------------------------------------------------------------------------------------------------------------------------Total Count: 6      End Time: 2023-05-24 12:09:042023-05-24 12:09:04 [INFO] Load record finished. Total Elapsed: 1.700 s2023-05-24 12:09:04 [INFO] System exit 0#确认数据obclient [test]> select * from t1;+---+------+--------+------+| a | b   | c    | d   |+---+------+--------+------+| 1 |  66 | 999555 | A   || 3 |  44 | 999555 | A   || 5 |  33 | 999555 | A   || 6 |  22 | 999555 | A   || 7 |  66 | 999555 | A   || 8 |  88 | 999555 | A   |+---+------+--------+------+6 rows in set (0.002 sec)