作者:刘书盛
热衷技术分享、编写技术文档
原创作品
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 控制文件对导入数据进行处理
#文件模板.ctl
lang=java
(
列名 字节偏移位置(可选) "预处理函数"(可选) 映射定义(可选),
列名 字节偏移位置(可选) "预处理函数"(可选) 映射定义(可选),
列名 字节偏移位置(可选) "预处理函数"(可选) 映射定义(可选),
列名 字节偏移位置(可选) "预处理函数"(可选) 映射定义(可选)
);
#举例
lang=java
server=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,555
3,44444,111111111,555
5,33336,111111111,555
6,22226,111111111,555
7,66664,111111111,555
8,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.ctl
vi /tmp/t1.ctl
lang=java
server=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] true
2023-05-24 09:57:09 [INFO] Load jdbc driver class: "com.oceanbase.jdbc.Driver" finished
2023-05-24 09:57:09 [INFO] Manifest: "/tmp/backup/data/MANIFEST.bin" has been saved
2023-05-24 09:57:10 [INFO] Parse ctrl definition: "/tmp/t1.ctl" success
2023-05-24 09:57:10 [INFO] File: "/tmp/backup/data/test/TABLE/t2.3.0.csv" is not contained, ignore it
2023-05-24 09:57:10 [INFO] Binding table: "t1" to the file: "/tmp/backup/data/test/TABLE/t1.1.0.csv" finished
2023-05-24 09:57:10 [INFO] File: "/tmp/backup/data/test/TABLE/t2.2.0.csv" is not contained, ignore it
2023-05-24 09:57:10 [INFO] File: "/tmp/backup/data/test/TABLE/t2.4.0.csv" is not contained, ignore it
2023-05-24 09:57:10 [INFO] Find 1 resources in local path: "/tmp/backup" success. Elapsed: 57.43 ms
2023-05-24 09:57:10 [INFO] Split 1 data files to 1 subfiles by 64.0 MB success. Elapsed: 20.05 ms
2023-05-24 09:57:10 [INFO] Ignore to clean any tables as --truncate-table or --delete-from-table is not specified
2023-05-24 09:57:10 [INFO] Query table entry and primary key for table: "t1" finished. Remain: 0
2023-05-24 09:57:10 [INFO] Query the leader location of "t1" finished. Remain: 0
2023-05-24 09:57:10 [INFO] Calculate leader: 10.186.63.39:2881 of table "t1", part: 0. Remain: 0
2023-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: 0
2023-05-24 09:57:10 [INFO] Refresh observer load status finished. Elapsed: 46.46 ms
2023-05-24 09:57:10 [INFO] Use c.l.d.LiteBlockingWaitStrategy as available cpu(s) is 4
2023-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 success
2023-05-24 09:57:10 [INFO] File: "/tmp/backup/data/test/TABLE/t1.1.0.csv" has been parsed finished
2023-05-24 09:57:11 [INFO] Wait for the all the workers to drain of published events then halt the workers
2023-05-24 09:57:11 [INFO] Close connection count: 5 of the DataSource. Key: BIZ
2023-05-24 09:57:11 [INFO] Close connection count: 25 of the DataSource. Key: SYS
2023-05-24 09:57:11 [INFO] Shutdown task context finished
2023-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:11
2023-05-24 09:57:11 [INFO] Load record finished. Total Elapsed: 1.891 s
2023-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,555
3,44444,111111111,555
5,33336,111111111,555
6,22226,111111111,555
7,66664,111111111,555
8,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列不写,数据为空NULL
vi /tmp/t2.ctl
lang=java
server=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" finished
2023-05-24 10:21:07 [INFO] Manifest: "/tmp/backup/data/MANIFEST.bin" has been saved
2023-05-24 10:21:07 [INFO] Parse ctrl definition: "/tmp/t2.ctl" success
2023-05-24 10:21:07 [INFO] Found 1 empty tables before executing.... Elapsed: 42.03 ms
2023-05-24 10:21:07 [INFO] File: "/tmp/backup/data/test/TABLE/t1.1.0.csv" is not contained, ignore it
2023-05-24 10:21:08 [INFO] Binding table: "t2" to the file: "/tmp/backup/data/test/TABLE/t2.2.0.csv" finished
2023-05-24 10:21:08 [INFO] Find 1 resources in local path: "/tmp/backup" success. Elapsed: 18.52 ms
2023-05-24 10:21:08 [INFO] Split 1 data files to 1 subfiles by 64.0 MB success. Elapsed: 24.21 ms
2023-05-24 10:21:08 [INFO] Ignore to clean any tables as --truncate-table or --delete-from-table is not specified
2023-05-24 10:21:08 [INFO] Query table entry and primary key for table: "t2" finished. Remain: 0
2023-05-24 10:21:08 [INFO] Query the leader location of "t2" finished. Remain: 0
2023-05-24 10:21:08 [INFO] Calculate leader: 10.186.63.38:2881 of table "t2", part: 1. Remain: 0
2023-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: 0
2023-05-24 10:21:08 [INFO] Refresh observer load status finished. Elapsed: 43.75 ms
2023-05-24 10:21:08 [INFO] Use c.l.d.LiteBlockingWaitStrategy as available cpu(s) is 4
2023-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 4
2023-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 4
2023-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 success
2023-05-24 10:21:09 [INFO] File: "/tmp/backup/data/test/TABLE/t2.2.0.csv" has been parsed finished
2023-05-24 10:21:09 [INFO] Wait for the all the workers to drain of published events then halt the workers
2023-05-24 10:21:09 [INFO] Close connection count: 5 of the DataSource. Key: BIZ
2023-05-24 10:21:09 [INFO] Close connection count: 27 of the DataSource. Key: SYS
2023-05-24 10:21:09 [INFO] Shutdown task context finished
2023-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:09
2023-05-24 10:21:09 [INFO] Load record finished. Total Elapsed: 2.214 s
2023-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.ctl
lang=java
server=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" finished
2023-05-24 11:18:47 [INFO] Manifest: "/tmp/backup/data/MANIFEST.bin" has been saved
2023-05-24 11:18:47 [INFO] Parse ctrl definition: "/tmp/t1.ctl" success
2023-05-24 11:18:48 [INFO] Binding table: "t1" to the file: "/tmp/backup/data/test/TABLE/t1.1.0.csv" finished
2023-05-24 11:18:48 [INFO] File: "/tmp/backup/data/test/TABLE/t2.2.0.csv" is not contained, ignore it
2023-05-24 11:18:48 [INFO] Find 1 resources in local path: "/tmp/backup" success. Elapsed: 42.81 ms
2023-05-24 11:18:48 [INFO] Split 1 data files to 1 subfiles by 64.0 MB success. Elapsed: 20.61 ms
2023-05-24 11:18:48 [INFO] Ignore to clean any tables as --truncate-table or --delete-from-table is not specified
2023-05-24 11:18:48 [INFO] Query table entry and primary key for table: "t1" finished. Remain: 0
2023-05-24 11:18:48 [INFO] Query the leader location of "t1" finished. Remain: 0
2023-05-24 11:18:48 [INFO] Calculate leader: 10.186.63.39:2881 of table "t1", part: 0. Remain: 0
2023-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: 0
2023-05-24 11:18:48 [INFO] Refresh observer load status finished. Elapsed: 34.41 ms
2023-05-24 11:18:48 [INFO] Use c.l.d.LiteBlockingWaitStrategy as available cpu(s) is 4
2023-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 success
2023-05-24 11:18:48 [INFO] File: "/tmp/backup/data/test/TABLE/t1.1.0.csv" has been parsed finished
2023-05-24 11:18:49 [INFO] Wait for the all the workers to drain of published events then halt the workers
2023-05-24 11:18:49 [INFO] Close connection count: 5 of the DataSource. Key: BIZ
2023-05-24 11:18:49 [INFO] Close connection count: 23 of the DataSource. Key: SYS
2023-05-24 11:18:49 [INFO] Shutdown task context finished
2023-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:49
2023-05-24 11:18:49 [INFO] Load record finished. Total Elapsed: 1.639 s
2023-05-24 11:18:49 [INFO] System exit 0
#确认数据,数据插入OK
obclient [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. 文件是定长数据,如何导入
#修改控制文件标签:24,INFO,场景,05,--,09,导入,obloader,10 From: https://www.cnblogs.com/linuxmysql/p/17427956.html
[root@10-186-63-38 bin]# cat /tmp/t1.ctl
lang=java
server=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" finished
2023-05-24 12:09:02 [INFO] Manifest: "/tmp/backup/data/MANIFEST.bin" has been saved
2023-05-24 12:09:02 [INFO] Parse ctrl definition: "/tmp/t1.ctl" success
2023-05-24 12:09:02 [INFO] Binding table: "t1" to the file: "/tmp/backup/data/test/TABLE/t1.1.0.csv" finished
2023-05-24 12:09:02 [INFO] File: "/tmp/backup/data/test/TABLE/t2.2.0.csv" is not contained, ignore it
2023-05-24 12:09:02 [INFO] Find 1 resources in local path: "/tmp/backup" success. Elapsed: 52.84 ms
2023-05-24 12:09:02 [INFO] Split 1 data files to 1 subfiles by 64.0 MB success. Elapsed: 33.91 ms
2023-05-24 12:09:02 [INFO] Ignore to clean any tables as --truncate-table or --delete-from-table is not specified
2023-05-24 12:09:03 [INFO] Query table entry and primary key for table: "t1" finished. Remain: 0
2023-05-24 12:09:03 [INFO] Query the leader location of "t1" finished. Remain: 0
2023-05-24 12:09:03 [INFO] Calculate leader: 10.186.63.39:2881 of table "t1", part: 0. Remain: 0
2023-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: 0
2023-05-24 12:09:03 [INFO] Refresh observer load status finished. Elapsed: 64.82 ms
2023-05-24 12:09:03 [INFO] Use c.l.d.LiteBlockingWaitStrategy as available cpu(s) is 4
2023-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 success
2023-05-24 12:09:03 [INFO] File: "/tmp/backup/data/test/TABLE/t1.1.0.csv" has been parsed finished
2023-05-24 12:09:04 [INFO] Wait for the all the workers to drain of published events then halt the workers
2023-05-24 12:09:04 [INFO] Close connection count: 5 of the DataSource. Key: BIZ
2023-05-24 12:09:04 [INFO] Close connection count: 24 of the DataSource. Key: SYS
2023-05-24 12:09:04 [INFO] Shutdown task context finished
2023-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:04
2023-05-24 12:09:04 [INFO] Load record finished. Total Elapsed: 1.700 s
2023-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)