Vertica: File to Table

Vertica: File to Table

Vertica provides a way to load data from a delimited file to Vertica table. This is very helpful in case of cross database data movement.

VSQL client is needed to load data from file to Vertica cluster. This can be downloaded from vertica site at https://www.vertica.com/download/vertica/client-drivers/

Sample Table

create table tutorial_db.vertica_load_test(id int,price float,sale_ts timestamp(6))

Example 1: Simple load

/Users/username/Desktop/opt/vertica/bin/vsql -A -e -U tutorial_user -w vertica_user_password -h cluster_host_name -p 5433 -c "copy tutorial_db.vertica_load_test FROM LOCAL '/Users/username/data.csv'  DELIMITER '|' ;"

Example 2: Loading data from file to Vertica with exceptions and rejected data

/Users/username/Desktop/opt/vertica/bin/vsql -A -e -U tutorial_user -w vertica_user_password -h cluster_host_name -p 5433 -c "copy tutorial_db.vertica_load_test FROM LOCAL '/Users/username/data.csv'  DELIMITER '|' EXCEPTIONS '/Users/username/Desktop/load.log' REJECTED DATA '/Users/username/Desktop/reject.log' direct ;"

Example 3: Loading from file without specifying local, it will fail with below error.

/Users/username/Desktop/opt/vertica/bin/vsql -A -e -U tutorial_user -w vertica_user_password -h cluster_host_name -p 5433 -c "copy tutorial_db.vertica_load_test FROM '/Users/username/data.csv'  DELIMITER '|' direct";
copy tutorial_db.vertica_load_test FROM '/Users/username/data.csv'  DELIMITER '|' direct
ERROR 4368:  Permission denied for storage location [/Users/username/data.csv]

Example 4: Loading date from file to Vertica using stdin (cat)

cat /Users/username/data.csv | /Users/username/Desktop/opt/vertica/bin/vsql -A -e -U tutorial_user -w vertica_user_password -h cluster_host_name -p 5433 -c "copy tutorial_db.vertica_load_test from local stdin DELIMITER '|' EXCEPTIONS 'load.log' REJECTED DATA 'reject.log' direct ;"

Example 5: Load Using variables

/Users/username/Desktop/opt/vertica/bin/vsql -A -e -U tutorial_user -w vertica_user_password -h cluster_host_name -p 5433
\set excepts '''/Users/username/Desktop/load_exception.log'''
\set rejects '''/Users/username/Desktop/reject.log'''
copy tutorial_db.vertica_load_test FROM LOCAL '/Users/username/data.csv'  DELIMITER '|' EXCEPTIONS :excepts REJECTED DATA :rejects direct ;