Running commands from vSQL

Moderator: NorbertKrupa

Post Reply
DataWhisperer
Newbie
Newbie
Posts: 16
Joined: Fri May 11, 2012 12:25 pm

Running commands from vSQL

Post by DataWhisperer » Sat May 12, 2012 8:56 am

Haven't seen much here, but it is possible to run bash commands from the vsql prompt.

Why is this handy? Well updating your SVN repo to execute new code w/o having to leave vsql or open another prompt.

Example 1: Get your working directory

Code: Select all

datawhisperer=> \! pwd
/home/datawhisperer
Example 2: SVN up some code

Code: Select all

datawhisperer=> \! svn up /home/datawhisperer/sql
Authentication realm: <https://svn.datawhisperer.com:443> Datawhisperer.com Subversion server
Password for 'datawhisperer': 

-----------------------------------------------------------------------
Store password unencrypted (yes/no)? no
U    /home/datawhisperer/sql/scheduled-scripts/script-to-execute.sql
You can even run vim if it tickles your fancy.

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Running commands from vSQL

Post by id10t » Sat May 12, 2012 9:23 am

Did you see Vertica extensions on github? There are interesting shell extension.

PS `` quotes - executes bash command too. Useful to set vsql variables

Code: Select all

test_db=> \set current_dir `pwd`
test_db=> \echo :current_dir
/home/dbadmin
test_db=> \unset current_dir
test_db=> \echo :current_dir

DataWhisperer
Newbie
Newbie
Posts: 16
Joined: Fri May 11, 2012 12:25 pm

Re: Running commands from vSQL

Post by DataWhisperer » Sat May 12, 2012 9:39 am

I haven't played much with extensions. I should spend some time checking them out.

Very true, I should have mentioned that. Backticks are great for that if you need values from bash. Toss in some grep, awk and sed, you can do some scripting work to set variables.

Code: Select all

datawhisperer=> \set vertica_pid `ps aux | grep -i _catalog | grep -v grep | awk '{print $2}'`
datawhisperer=> \echo :vertica_pid
4124
Though YMMV. :-)

Other useful things to track would be number of open files (for the 5.1.x bug) if you're plagued by that which randomly kills nodes. We have a cronjob that runs every second to log that information on our dev environment.

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Running commands from vSQL

Post by id10t » Sat May 12, 2012 5:29 pm

Off topic
I very often see such shell expressions:

Code: Select all

ps aux | grep -i <pattern> 

Code: Select all

... | grep -i <pattern> | grep -v grep 
1. there are exists utilits like `pgrep` (= process grep) and `pkill` (= process kill).

Code: Select all

dbadmin@suse:~> ps x | grep -iP "[v]((ertica)|(sql))"
 3228 pts/0    S+     0:00 /opt/vertica/oss/python/bin/python /opt/vertica/bin/admin
 3270 ?        Sl    18:16 /opt/vertica/bin/vertica -C test_db -D /vertica/test_db/v
 3362 pts/0    S+     0:00 /opt/vertica/bin/vsql -p 5433 -h suse.vertica test_db

Code: Select all

dbadmin@suse:~> pgrep vertica
3270
dbadmin@suse:~> pgrep vsql
3362
2. to exclude `grep` from search:

Code: Select all

dbadmin@suse:~> ps x | grep firefox
 1637 ?        S      0:00 /bin/sh /usr/bin/firefox
 1642 ?        Sl   106:00 /usr/lib64/firefox/firefox-bin
 1713 ?        SLl  451:13 /usr/lib64/firefox/plugin-container /home/dbadmin/.mozill
 4569 pts/2    S+     0:00 grep --color=auto firefox

dbadmin@suse:~> ps x | grep [f]irefox
 1637 ?        S      0:00 /bin/sh /usr/bin/firefox
 1642 ?        Sl   106:00 /usr/lib64/firefox/firefox-bin
 1713 ?        SLl  451:13 /usr/lib64/firefox/plugin-container /home/dbadmin/.mozill
PS

Code: Select all

test_db=> \echo `pgrep vsql`
3362
test_db=> select client_pid from v_monitor.current_session;
 client_pid 
------------
       3362
(1 row)

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Running commands from vSQL

Post by JimKnicely » Mon May 14, 2012 12:37 pm

PS `` quotes - executes bash command too. Useful to set vsql variables
sKwa - Thanks for this tip!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Running commands from vSQL

Post by id10t » Mon May 14, 2012 6:23 pm

Thanks to the topic starter @DataWhisperer :-)

User avatar
becky
Intermediate
Intermediate
Posts: 118
Joined: Sat Apr 28, 2012 11:37 am

Re: Running commands from vSQL

Post by becky » Mon May 14, 2012 6:33 pm

Yeah! I'm learning stuff on this site that I'm not reading in the docs, that's for sure! Thanks guys!
THANKS - BECKSTER

Post Reply

Return to “vSQL”