Q1 . How can I pass the variables through bash? Q2. Also is it possible to declare variable from th
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
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
0
Comments
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)
This post might help you.
Shell script with Vertica
NC
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
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)