How to Skip a column when loading data in Vertica
With the help of the options that copy offers we have the ability to skip a column or ever a part of a column.
In this examples will demonstrate how this can be done.
We will use a csv data files called data.csv as our loading data.
1,1,1
2,2,2
3,3,3
And the table we load data into is called test and is has 3 columns.
create table test(col1 int, col2 int, col3 int);Let's see how we can skip a column when loading data.
copy test(
v1 filler int ,
v2 filler int ,
v3 filler int ,
col1 as v1,
col3 as v3) from '/home/dbadmin/data.csv' delimiter ',' direct;
-- query the table
(dbadmin@:5433) [dbadmin] select * from test;
col1 | col2 | col3
------+------+------
1 | | 1
2 | | 2
3 | | 3
(3 rows)- you can see that no data was loaded into column col2, also is important that the col2 should accept null values.
How can we skip a part of a column
- The data we will use for this example:
1x1,1,1
1x2,2,2
1x3,3,3- And the same table test:
create table test(col1 int, col2 int, col3 int); copy test(
v1 FILLER varchar(10) delimiter 'x',
v2 FILLER int,
v3 FILLER int,
v4 FILLER int,
col1 as v2,
col2 as v3,
col3 as v4
)
from '/home/dbadmin/data.csv' delimiter ',' direct;
Rows Loaded
-------------
3
(1 row)
-- query table
(dbadmin@:5433) [dbadmin] select * from test;
col1 | col2 | col3
------+------+------
1 | 1 | 1
2 | 2 | 2
3 | 3 | 3
(3 rows)
-- see content of the load file
(dbadmin@:5433) [dbadmin] * ! cat /home/dbadmin/data.csv
1x1,1,1
1x2,2,2
1x3,3,3- you see that we "declare" 4 variables that will receive the input data from the data.csv file but we only pass to the load command only 3 of them. Since we have established that the separator between the first and second variable data set is 'x' , the data will separated into 4 columns.
