Transform data during load in Vertica - use variable with COPY
How can we do an average of the first two column and load it into the third column using a stored value of a Vertica vsql variable. Extract the number of column in the csv file minus the one column you will store the output and store it in a variable called count.
- our data sample is a bellow:
1,1,1
2,2,2
3,3,3set count head -1 /tmp/data.csv | sed 's/[^,]//g'| wc -c| awk {'print$1-1'}
echo :count
2set count head -1 /tmp/data.csv | sed 's/[^,]//g'| wc -c| awk {'print$1-1'}
echo :count
2
copy test(
v1 filler int ,
v2 filler int ,
v3 filler int ,
col1 as v1,
col2 as v2,
col3 as (v1+v2)/:count) from '/tmp/data.csv' delimiter ',' direct;
Rows Loaded
-------------
3
(1 row)
-- you can see that the col3 stores the avarage value of the first two columns.
(dbadmin@:5433) [dbadmin] select * from test;
col1 | col2 | col3
------+------+------
1 | 1 | 1
2 | 2 | 2
3 | 3 | 3