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!
How to Pass shell variable to vsql
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: How to Pass shell variable to vsql
Hi,
It is easy to pass shell variables into Vertica!
Simple example:
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
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: How to Pass shell variable to vsql
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,
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
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: How to Pass shell variable to vsql
Yes.is this the same way you store query output in a variable
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
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: How to Pass shell variable to vsql
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'
SELECT schema_name, table_id, table_type, table_name FROM v_catalog.all_tables WHERE table_name='test'
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: How to Pass shell variable to vsql
Here's one way:How about multiple columns?
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
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: How to Pass shell variable to vsql
Hi!
[DELETED]
[DELETED]
Last edited by id10t on Fri May 08, 2015 9:16 pm, edited 1 time in total.