vertica數據庫copy命令是實現數據加載的代碼怎么寫

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)

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