vertica數(shù)據(jù)庫copy命令是實現(xiàn)數(shù)據(jù)加載的代碼怎么寫

vertica數(shù)據(jù)庫copy命令是實現(xiàn)數(shù)據(jù)加載的代碼怎么寫，很多新手對此不是很清楚，為了幫助大家解決這個難題，下面小編將為大家詳細講解，有這方面需求的人可以來學(xué)習(xí)下，希望你能有所收獲。

vertica數(shù)據(jù)加載

創(chuàng)建外部數(shù)據(jù)文本：
[dbadmin@verticatest ~]$ for((i=1;i<10000;i++))
> do
> echo "$i,mingshuo">>0629.txt
> done

修改不滿足格式數(shù)據(jù)
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

創(chuàng)建空表：
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)

將文本中的數(shù)據(jù)加載到表test.t1中：
COPY test.t1(id,name) FROM '/home/dbadmin/0629.txt' EXCEPTIONS '/tmp/exp.log' DELIMITER ',' abort on error no commit;

這里指定了列名
exceptions是導(dǎo)入失敗的數(shù)據(jù)的原因日志；
delemiter是列的分割符。如果用ASCII碼表示要加e，比如：e'\t'
abort on error遇到報錯導(dǎo)入終止；
no commit是指導(dǎo)入完成后不提交數(shù)據(jù)。


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))
實際導(dǎo)入發(fā)現(xiàn)9998行ms數(shù)據(jù)不符合表的定義，id是int類型，而9998行是ms。
dbadmin=> select count(*) from test.t1;
 count
-------
     0
(1 row)
發(fā)現(xiàn)數(shù)據(jù)沒有導(dǎo)入，這是因為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再導(dǎo)入：
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行數(shù)據(jù)成功導(dǎo)入9998行。ms行沒有導(dǎo)入，ms行后面符合定義的數(shù)據(jù)也成功導(dǎo)入，但是沒有報錯。所以如果覺得一部分導(dǎo)入成功了，一部分失敗了，因為有些原因我還要再刪除導(dǎo)入的數(shù)據(jù)重新來，那么這個時候就可以加入abort on error參數(shù)。

下面驗證no commit參數(shù)：
剛剛導(dǎo)入的數(shù)據(jù)沒有提交就退出客戶端：
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行數(shù)據(jù)。

重新加載：
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)

看完上述內(nèi)容是否對您有幫助呢？如果還想對相關(guān)知識有進一步的了解或閱讀更多相關(guān)文章，請關(guān)注億速云行業(yè)資訊頻道，感謝您對億速云的支持。