3

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!

woot
  • 7,406
  • 2
  • 36
  • 55
saghar
  • 67
  • 1
  • 3
  • 10

4 Answers4

3

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.

woot
  • 7,406
  • 2
  • 36
  • 55
Guillaume
  • 2,325
  • 2
  • 22
  • 40
0

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

clyfish
  • 10,240
  • 2
  • 31
  • 23
  • 1
    yes, but my question is more about how to do the error handling properly and if vsql passes any message to Unix. there might be some other errors as well and I want to know how to manage them. – saghar Sep 11 '13 at 03:04
  • You can manage the errors according to the output of `vsql`. Use `output=$(your command)` to capture the output of the `vhost`. – clyfish Sep 11 '13 at 03:24
  • 1
    then, $output will be the query results. how could it help in error handling? – saghar Sep 11 '13 at 03:29
0

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
stupidbodo
  • 466
  • 1
  • 5
  • 14
0

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

MrMauricioLeite
  • 383
  • 3
  • 10