dmfldr数据快速加载
1.外部数据(文本)
[dmdba@shard1 ~]$ cat prod.dat ProdID,ProdName,Code,ReOrder,Cost,Price,PriSrc,SecSrc,ObsoletedDate 1001,DaMeng Database,Server,25,45.45,14.32,Redwood Shores,Reston, 1002,DaMeng Database,Server,15,55.78,50.10,Redwood Shores,Reston,2014-02-14
2.控制文件
[dmdba@shard1 ~]$ cat prod.ctl load data infile '/home/dmdba/prod.dat' into table test_load fields ','
3.数据库规划表
create table test_load (prodid int, prodname varchar(40), code varchar(30), reorder int, cost int, price int, prisrc varchar(30), secsrc varchar(30), obsoleteddate date );
4.加载数据
[dmdba@shard1 ~]$ dmfldr sysdba/xxzx7817600 control='/home/dmdba/prod.ctl' dmfldr V7.1.6.46-Build(2018.02.08-89107)ENT error occured during getting control input.Make sure the input is embraced by charactor '
控制文件输入参数出错，请确认以符号’作为首尾字符
[dmdba@shard1 ~]$ dmfldr sysdba/xxzx7817600 control=\'/home/dmdba/prod.ctl\' skip=1 dmfldr V7.1.6.46-Build(2018.02.08-89107)ENT dmfldr: Copyright (c) 2011, 2015, Dameng. All rights reserved. Control file： Loaded rows:All Rows per commit to server: 50000 Rows to skip: 1 Errors count allowed: 100 Whether to load direct: Yes Whether insert self-increase col: No Whether data has sort by gather index: No Character sets:GBK Data file counts: 1 /home/dmdba/prod.dat Error file :fldr.bad Dest table :TEST_LOAD Column Name Packed data type End PRODID CHARACTER , PRODNAME CHARACTER , CODE CHARACTER , REORDER CHARACTER , COST CHARACTER , PRICE CHARACTER , PRISRC CHARACTER , SECSRC CHARACTER , OBSOLETEDDATE CHARACTER , row buffer number is 4 task thread number is 4 2 rows committed. Dest table :TEST_LOAD 2 Rows loaded success Due to data format error, 0 rows abandon Due to data error, 0 rows not loaded Skip logic record counts: 1 Read logic record counts: 2 Refuse logic record counts: 0 The total time used: 26.677(ms)
或者
[dmdba@shard1 ~]$ dmfldr sysdba/xxzx7817600 control=\'\/home\/dmdba\/prod.ctl\' skip=1 dmfldr V7.1.6.46-Build(2018.02.08-89107)ENT dmfldr: Copyright (c) 2011, 2015, Dameng. All rights reserved. Control file： Loaded rows:All Rows per commit to server: 50000 Rows to skip: 1 Errors count allowed: 100 Whether to load direct: Yes Whether insert self-increase col: No Whether data has sort by gather index: No Character sets:GBK Data file counts: 1 /home/dmdba/prod.dat Error file :fldr.bad Dest table :TEST_LOAD Column Name Packed data type End PRODID CHARACTER , PRODNAME CHARACTER , CODE CHARACTER , REORDER CHARACTER , COST CHARACTER , PRICE CHARACTER , PRISRC CHARACTER , SECSRC CHARACTER , OBSOLETEDDATE CHARACTER , row buffer number is 4 task thread number is 4 2 rows committed. Dest table :TEST_LOAD 2 Rows loaded success Due to data format error, 0 rows abandon Due to data error, 0 rows not loaded Skip logic record counts: 1 Read logic record counts: 2 Refuse logic record counts: 0 The total time used: 30.680(ms)