Copy CSV to Vertica

Question

I have a csv file with headers col1, col2, col3

I also have table in Vertica v_col1, v_col2, v_col3

How I can copy and mapping the data from CSV file into my table (mean col1 -> v_col1 , col2 -> v_col2 etc...)

Thanks

Answer 1

The COPY command in Vertica will map the CSV columns to the table by itself.

Suppose the csv file /tmp/t1.csv looks like below:

col1,col2,col3
1,100,a
2,200,b
3,300,c

Then you can create a table in Vertica and use COPY with DELIMITER ',':

=> create table table1(v_col1 int, v_col2 int, v_col3 varchar);
CREATE TABLE
=> copy table1 from '/tmp/t1.csv' delimiter ',';
 Rows Loaded
-------------
           3
=> select * from table1;
 v_col1 | v_col2 | v_col3
--------+--------+--------
      1 |    100 | a
      2 |    200 | b
      3 |    300 | c
(3 rows)

Please refer to below document for more COPY examples: https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Statements/COPY/COPYExamples.htm

(Edit to the comments) I don't have an answer to the "mapping" requirement, but I think there is a workaround to achieve that.

After the csv file was copied into a table with the same number of columns, you can create a new table by selecting those columns you want.

For example, you can create a new table (named as table2) which only has the first 2 columns from table1:

=> create table table2 as select v_col1,v_col2 from table1;
CREATE TABLE
=> select * from table2;
 v_col1 | v_col2
--------+--------
      2 |    200
      1 |    100
      3 |    300
(3 rows)

Answer 2

copy {vertica_table_name}
from local '{path}'
delimiter ','
enclosed by '"';

e.g.

copy test_table
from local 'D:\test\test.csv'
delimiter ','
enclosed by '"';