We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


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

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)

  • Navin_CNavin_C Vertica Customer
    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