Shulou(Shulou.com)06/01 Report--

Vertica database copy command is to achieve data loading code how to write, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can get something.

Vertica data loading

Create external data text:

[dbadmin@verticatest] $for ((item1terteri do)

> echo "$iMingshuo" > > 0629.txt

> done

Modify data that does not meet the format

Sed-I's Universe 9998 Compact Ms Universe '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

Create an empty table:

Dbadmin= > create schema test

CREATE SCHEMA

Dbadmin= > create table test.t1 (id int,name varchar2, b varchar2)

CREATE TABLE

Dbadmin= > select count (*) from test.t1

Count

-

0

(1 row)

Load the data in the text into table test.t1:

COPY test.t1 (id,name) FROM'/ home/dbadmin/0629.txt' EXCEPTIONS'/ tmp/exp.log' DELIMITER', 'abort on error no commit

The column name is specified here

Exceptions is the reason log for importing failed data

Delemiter is the separator of the column. If you use ASCII code to indicate that you want to add e, for example: e'\ t'

Abort on error encountered error import termination

No commit is instructed not to submit data after the import is completed.

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))

The actual import found that 9998 rows of ms data do not meet the definition of the table, id is of type int, and 9998 rows are ms.

Dbadmin= > select count (*) from test.t1

Count

-

0

(1 row)

It was found that the data was not imported because abort on error is in effect.

The log at this time:

[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.

Remove abort on error and import:

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.

You can see that 9999 rows of data were successfully imported into 9998 rows. The ms line was not imported, and the data that met the definition after the ms line was successfully imported, but no error was reported. So if I feel that some of the imports have been successful and some have failed, and for some reason I have to delete the imported data and start all over again, then I can add the abort on error parameter at this time.

Verify the no commit parameter below:

Exit the client without submitting the data you just imported:

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

Log in again:

[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 rows of data.

Reload:

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)

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.