Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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

Comments

  • 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)

  • Hi Jay,

    This post might help you.

    Shell script with Vertica

    NC
  • 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




  • 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)

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.