How to Pass shell variable to vsql

[shaz001]

Hi,

What is the best way to pass the shell variable to vsql query? I'm trying to create a generic vsql query using "bash" and trying to
pass the shell variable to vsql. How can I do it? Any example would be appreciated.

Thanks in advance!

[JimKnicely]

Hi,

It is easy to pass shell variables into Vertica!

Simple example:

Code: Select all

[dbadmin@vertica01 ~]$ cat test.sh
export LOOKUP="X"
export RESULT=$(vsql -t -c "SELECT COUNT(*)FROM dual WHERE dummy = '$LOOKUP'")
echo $RESULT
[dbadmin@vertica01 ~]$ ./test.sh
1

[shaz001]

Thanks for your reply. Another question I've is this the same way you store query output in a variable i.e.

I need to save schema_name from the following query output in a variable.

export schemaname=" "
export schemaname=$(vsql -t -c "SELECT schema_name FROM v_catalog.all_tables WHERE table_name='MY_TABLE'")

echo $schemaname

Thanks,

[JimKnicely]

is this the same way you store query output in a variable

Yes.

Keep in mind that if the table exists in multiple schemas then your environment variable with include them all...

Code: Select all

dbadmin=> create schema test1;
CREATE SCHEMA
dbadmin=> create schema test2;
CREATE SCHEMA
dbadmin=> create table test1.test (col1 int);
CREATE TABLE
dbadmin=> create table test2.test (col1 int);
CREATE TABLE
dbadmin=> \q
[dbadmin@vertica01 ~]$ export schemaname=$(vsql -t -c "SELECT schema_name FROM v_catalog.all_tables WHERE table_name='test'")
[dbadmin@vertica01 ~]$ echo $schemaname
test1 test2

[shaz001]

How about multiple columns? If the query contains multiple columns, how do you store them in an environment variable?

SELECT schema_name, table_id, table_type, table_name FROM v_catalog.all_tables WHERE table_name='test'

[JimKnicely]

How about multiple columns?

Here's one way:

Code: Select all

[dbadmin@vertica01 ~]$ vsql -c "SELECT schema_name, table_id, table_type, table_name FROM v_catalog.all_tables WHERE table_name='test';"
 schema_name |     table_id      | table_type | table_name
-------------+-------------------+------------+------------
 testdata    | 45035996316435254 | TABLE      | test

(1 row)

[dbadmin@vertica01 ~]$ export var=$(echo $(vsql -At -c "SELECT schema_name, table_id, table_type, table_name FROM v_catalog.all_tables WHERE table_name='test';") | awk -F"|" '{print $1,$2,$3,$4}')

[dbadmin@vertica01 ~]$ export schema_name=$1
[dbadmin@vertica01 ~]$ export table_id=$2
[dbadmin@vertica01 ~]$ export table_type=$3
[dbadmin@vertica01 ~]$ export table_name=$4

[dbadmin@vertica01 ~]$ echo $schema_name
testdata
[dbadmin@vertica01 ~]$ echo $table_id
45035996316435254
[dbadmin@vertica01 ~]$ echo $table_type
TABLE
[dbadmin@vertica01 ~]$ echo $table_name
test

[id10t]

Hi!

[DELETED]