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)

ITPub博客

vertica数据库copy命令实现数据加载

最新文章