Q1 . How can I pass the variables through bash? Q2. Also is it possible to declare variable from th

Jay_Yoon_Lee

 Q1) How can I pass the variables through bash?

Is it possible to set variables on the shell script (so I can change table names in the for loop to deal with multiple tables with one script) something like this?

export tbl_name=$1

##(Attempt1)
/opt/vertica/bin/vsql -d $db_name -U $db_usr --password $db_passwd -c "\\set tbl_name '$tbl_name' \\echo :tbl_name" >> ${exec_log}
##(Attempt2)
echo "\\set tbl_name Indeg" |/opt/vertica/bin/vsql -d $db_name -U $db_usr --password $db_passwd 
##(Attempt3)
 /opt/vertica/bin/vsql -d $db_name -U $db_usr --password $db_passwd --variable tbl_name $1

FYI, Q2) I found answer on a code at a git repository. (although I think there's a documentation for it as well)
https://github.com/DougHarmon/v-sql-tb/tree/master/bin
and the specific code I looked at is :
https://github.com/DougHarmon/v-sql-tb/blob/master/bin/ahm_lag_alert.sh

Jay_Yoon_Lee

I actually found out a way, the following code assigns variable a=1

vsql -d $db_nameOGM -U $db_usrdbadmin --password $db_passwd --variable a=1 

but this shell script will make me enter to vsql interactive shell, how can I prohibit this?
(rather than putting \q command on the next line)

Navin_C

Hi Jay,

This post might help you.

Shell script with Vertica

NC

eli_revach

Very simple : 

Q1 - 
table_name="tables"
vsql -U dbadmin -w dbadmin <<!
select count(*) from $table_name;
!

Q2 -
table_name="tables"
Var='vsql -U dbadmin -w dbadmin <<!select count(*) from $table_name;
!'
echo $Var

Jay_Yoon_Lee

Thanks all. 

I think I should specify my expression more clearly. (For Q2, thanks for the answers!)

Short story:
For Q1, it was more for using the "\set" operation outside the sql interactive environment.
\set tbl_name 'some_name'
and use :tbl_name to refrer this table in the sql script.

And I have managed to do this by the vsql --set option, however, this makes my execution enter the interactive sql command-line mode and I'll have to hit \q myself in order for the shell script execute the line after --set operation.

Long story:
I have set of sql codes that I used variable :tbl_name in the code and was hoping to change that variables in the shell and just run the .sql script. ( If I knew how to pass the parameter into the sql script in Vertica, I wouldn't have needed to do this.)

I saw those approaches that wirtes sql scripts inside shell script with vsql -c or vsql -w option,which Navin and Eli has given, but then I would have to bring all the sql code into shell script. For me I have written sql code for its functionality module, (For example, each graph mining algorithms such as getting triangle counts, radius and all)

In my last resort, I could convert set of sql codes into set of shell scripts and then main shell script calling each shell script, but if possible I would like to stick to my current sql codes! (As I can use that for other sql frameworks as well ! (by just changing the variable name with some parameter passing or so)