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

/opt/vertica/bin/vsql -d $db_name -U $db_usr --password $db_passwd -c "\\set tbl_name '$tbl_name' \\echo :tbl_name" >> ${exec_log}
echo "\\set tbl_name Indeg" |/opt/vertica/bin/vsql -d $db_name -U $db_usr --password $db_passwd 
 /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)
and the specific code I looked at is :


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

  • Options
    Navin_CNavin_C Vertica Customer
    Hi Jay,

    This post might help you.

    Shell script with Vertica

  • Options
    Very simple : 

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

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

  • Options
    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
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file