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,3
 Extract the column count
set count head -1 /tmp/data.csv | sed 's/[^,]//g'| wc -c| awk {'print$1-1'}
echo :count
2
Run the full script
set 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
Done, happy transforming :)