vsql error handling (No such file or directory)

Question

I'm using shell scripting to upload a query's results to my database. In the script, first I save my query's results into a csv file and then upload the file into another database. After each step, I send a notification email to the user. The problem I'm facing is that I can't do a proper error handling. For example, I use something like the command below to generate the csv file:

/apps/vertica/vertica_v5.1.6/bin/vsql -h server.my.com -U "user" -w "pass" -o "/data/test.csv" -c "select count(*), month from table1 group by month" 

If directory "data" does not exist, the exit status in Unix still returns 0 (operation successful even though an error occured) because the query results is shown on the screen. How can I handle such error? What kind of IF statement I should use in my script to capture it?

Many thanks!

Answer 1

Use mkdir -p /data to make sure the directory exists before your query.

Answer 2

This is a tricky one.

Vertica vsql has a variable, ON_ERROR_STOP which if set will stop a script and return an error code of 3.

\set ON_ERROR_STOP on

That said, using the -o option does not seem to trigger this behaviour as the error is not on the SQL side.

What I would suggest then is to cheat, and redirect the output instead of asking Vertica to do it:

/apps/vertica/vertica_v5.1.6/bin/vsql -h server.my.com -U "user" -w "pass" -A -F, -c "select count(*), month from table1 group by month" > /data/test.csv

Note the -F, and -A options to create a csv like output. You might want -q (quiet) as well to only have query output, no messages.

Then if the destination file does not exist, you do have a bash non null exit status:

[me@server ~]$/apps/vertica/vertica_v5.1.6/bin/vsql -h server.my.com -U "user" -w "pass" -A -F, -c "select count(*), month from table1 group by month" > /data/test.csv
-bash: /data/test.csv: No such file or directory
[me@server ~]$ echo $?
1

From then on, it is easy enough to check the return code, catching both bash and Vertica

# your vsql command....
STATUS=$?
if [[ $STATUS -gt 0 ]]
then
    echo oops
    exit
fi

You will probably even create a function taking the query and output file as argument if you have many of those.

Answer 3

Additionally, if you are receiving this error: ERROR 5286: Unsupported SET option ON_ERROR_STOP

You can use the vsql option: vsql -v ON_ERROR_STOP=on

Answer 4

I have similar kind of problem and found a simple way to tackle it. I noticed that when queries fail the results csv file size actually becomes 0. You can do a check to see if results csv filesize is 0.

vsql -c "your queries command" -o "/location/of/results"

if [[ -s "/location/of/results" ]]; then
    some_other_command
fi