vertica数据库copy命令是实现数据加载的代码怎么写,很多新手对此不是很清楚,为了帮助大家解决这个难题,下面小编将为大家详细讲解,有这方面需求的人可以来学习下,希望你能有所收获。
vertica数据加载
创建外部数据文本:
[dbadmin@verticatest ~]$ for((i=1;i<10000;i++))
> do
> echo "$i,mingshuo">>0629.txt
> done
修改不满足格式数据
sed -i 's/9998/ms/' 0629.txt
[dbadmin@verticatest ~]$ tail 0629.txt
9990,mingshuo
9991,mingshuo
9992,mingshuo
9993,mingshuo
9994,mingshuo
9995,mingshuo
9996,mingshuo
9997,mingshuo
ms,mingshuo
9999,mingshuo
创建空表:
dbadmin=> create schema test;
CREATE SCHEMA
dbadmin=> create table test.t1(id int,name varchar2(100),b varchar2(100));
CREATE TABLE
dbadmin=> select count(*) from test.t1;
count
——-
0
(1 row)
将文本中的数据加载到表test.t1中:
COPY test.t1(id,name) FROM '/home/dbadmin/0629.txt' EXCEPTIONS '/tmp/exp.log' DELIMITER ',' abort on error no commit;
这里指定了列名
exceptions是导入失败的数据的原因日志;
delemiter是列的分割符。如果用ASCII码表示要加e,比如:e'/t'
abort on error遇到报错导入终止;
no commit是指导入完成后不提交数据。
dbadmin=> COPY test.t1(id,name) FROM '/home/dbadmin/0629.txt' EXCEPTIONS '/tmp/exp.log' DELIMITER ',' abort on error no commit;
ERROR 2035: COPY: Input record 9998 has been rejected (Invalid integer format 'ms' for column 1 (id))
实际导入发现9998行ms数据不符合表的定义,id是int类型,而9998行是ms。
dbadmin=> select count(*) from test.t1;
count
——-
0
(1 row)
发现数据没有导入,这是因为abort on error生效了。
这时候的日志:
[dbadmin@verticatest tmp]$ more exp.log
COPY: Input record 9998 has been rejected (Invalid integer format 'ms' for column 1 (id)). Please see /home/dbadmin/firstvdb/v_fir
stvdb_node0001_catalog/CopyErrorLogs/t1-0629.txt-copy-from-rejected-data, record 1 for the rejected record.
去掉abort on error再导入:
dbadmin=> COPY test.t1(id,name) FROM '/home/dbadmin/0629.txt' EXCEPTIONS '/tmp/exp.log' DELIMITER ',' no commit;
Rows Loaded
————-
9998
(1 row)
[dbadmin@verticatest tmp]$ more exp.log
COPY: Input record 9998 has been rejected (Invalid integer format 'ms' for column 1 (id)). Please see /home/dbadmin/firstvdb/v_fir
stvdb_node0001_catalog/CopyErrorLogs/t1-0629.txt-copy-from-rejected-data, record 1 for the rejected record.
COPY: Loaded 9998 rows, rejected 1 rows.
可以看到9999行数据成功导入9998行。ms行没有导入,ms行后面符合定义的数据也成功导入,但是没有报错。所以如果觉得一部分导入成功了,一部分失败了,因为有些原因我还要再删除导入的数据重新来,那么这个时候就可以加入abort on error参数。
下面验证no commit参数:
刚刚导入的数据没有提交就退出客户端:
dbadmin=> COPY test.t1(id,name) FROM '/home/dbadmin/0629.txt' EXCEPTIONS '/tmp/exp.log' DELIMITER ',' no commit;
Rows Loaded
————-
9998
(1 row)
dbadmin=> /q
再次登入:
[dbadmin@verticatest ~]$ vsql
Password:
vsql: FATAL 3781: Invalid username or password
[dbadmin@verticatest ~]$ vsql
Password:
Welcome to vsql, the Vertica Analytic Database interactive terminal.
Type: /h or /? for help with vsql commands
/g or terminate with semicolon to execute query
/q to quit
dbadmin=> select count(*) from test.t1;
count
——-
0
(1 row)
0行数据。
重新加载:
dbadmin=> COPY test.t1(id,name) FROM '/home/dbadmin/0629.txt' EXCEPTIONS '/tmp/exp.log' DELIMITER ',' no commit;
Rows Loaded
————-
9998
(1 row)
dbadmin=> commit;
COMMIT
dbadmin=> /q
[dbadmin@verticatest ~]$ vsql
Password:
Welcome to vsql, the Vertica Analytic Database interactive terminal.
Type: /h or /? for help with vsql commands
/g or terminate with semicolon to execute query
/q to quit
dbadmin=> select count(*) from test.t1;
count
——-
9998
(1 row)
看完上述内容是否对您有帮助呢?如果还想对相关知识有进一步的了解或阅读更多相关文章,请关注亿速云行业资讯频道,感谢您对亿速云的支持。
原创文章,作者:kirin,如若转载,请注明出处:https://blog.ytso.com/227728.html