Query Vertica through Shell script

[sach]

Hi,

Is there any sample example to connect to Vertica and execute sql's through shell script ?.I tried with option \i <file.sql>, but the vertica ignores the arguments...

Thanks in Advance,
Sach

[nnani]

Sach,

If you are trying to connect Vertica and then query vertica through script,
try doing this:


SQL File - vertica_query.sql ( with no unix commands )

1. Connect Vertica
2. \i vertica_query.sql
3. You can see the results there itself

Through Shell Script
shell script file - vertica_query.sh( use "vsql -c" command)
run the shell script there itself without connecting vertica.

[sach]

Hi,

I was able to connect to vertica as below

vsql -U ${VERTICA_USR} -d ${VERTICA_DB} -c "<sql query>' -At -o <output_file> -F ',' -q.

The above resulted the output without header and comma separated

Thanks
Sach

[banurajadurai]

i tried using \i command but i got an error as below

dbadmin=> \i vmart_queries_explain.sql
vmart_queries_explain.sql: No such file or directory

the sql file is in this path /opt/vertica/examples/VMart_Schema

what is to be done ?

[id10t]

Hi!

>> what is to be done ?

Code: Select all

dbadmin=> \i /opt/vertica/examples/VMart_Schema/vmart_queries_explain.sql

[nnani]

Hello Sach

An example for you on using vsql through shell scripts

Code: Select all

#!/bin/bash
tab_nm='test'
echo $tab_nm
var=`vsql -At -h hostname -d databasename -U username -w xxxx -c "select count(column_name)from columns where upper(table_name)='$t
ab_nm' group by table_name;"`
not_null_count=`vsql -At -h hostname -d databasename -U username -w xxxx  -c "select count(is_nullable) from columns where upper(tab
le_name)='$tab_nm' and is_nullable='f'"
echo $var
echo $not_null_count

If you want to run this file directly then just do it through bash terminal
If you want to run it through vsql then , vsql has to be in the path of this script
check your current path in vsql like this

Code: Select all

nnani=> \set CUR_PATH `pwd`
nnani=>\!echo :CUR_PATH

Or you can try this

nnani=>\! pwd

Place your script in this path and use \I option with script name to run it.

Hope this helps..