Mass quering from SQL files (or DB)

Moderator: NorbertKrupa

Post Reply
linux
Newbie
Newbie
Posts: 2
Joined: Thu Feb 25, 2016 4:32 pm

Mass quering from SQL files (or DB)

Post by linux » Thu Feb 25, 2016 4:40 pm

Hello,

is this possibile to make shell script which will be quering, for example, a lot of .sql files (or DB records with SQL queries) and make log file with execution time of these queries?

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

Re: Mass quering from SQL files (or DB)

Post by JimKnicely » Thu Feb 25, 2016 5:10 pm

Hi,

One option is to use the \timing meta-command.

Example:

Code: Select all

[dbadmin@s18384357 ~]$ cat run1.sql
\timing
select count(*) from dual;

[dbadmin@s18384357 ~]$ cat run2.sql
\timing
select count(*) from dual;

[dbadmin@s18384357 ~]$ cat run3.sql
\timing
select count(*) from dual;

[dbadmin@s18384357 ~]$ cat run.sh
/opt/vertica/bin/vsql -f /home/dbadmin/run1.sql >> /home/dbadmin/run.out
/opt/vertica/bin/vsql -f /home/dbadmin/run2.sql >> /home/dbadmin/run.out
/opt/vertica/bin/vsql -f /home/dbadmin/run3.sql >> /home/dbadmin/run.out

[dbadmin@s18384357 ~]$ ./run.sh

[dbadmin@s18384357 ~]$ cat run.out
Timing is on.
 count
-------
     1
(1 row)

Time: First fetch (1 row): 12.089 ms. All rows formatted: 12.166 ms
Timing is on.
 count
-------
     1
(1 row)

Time: First fetch (1 row): 10.920 ms. All rows formatted: 10.988 ms
Timing is on.
 count
-------
     1
(1 row)

Time: First fetch (1 row): 11.957 ms. All rows formatted: 12.006 ms
Jim Knicely

Image

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

linux
Newbie
Newbie
Posts: 2
Joined: Thu Feb 25, 2016 4:32 pm

Re: Mass quering from SQL files (or DB)

Post by linux » Fri Feb 26, 2016 10:42 am

Thanks,

now I have First fetch time and All rows formated time.

Is this possibile to check this time and make some exeptions? (if,case)
For exampe: If Time > 3 minutes then break. For breaking slow queries?
Is this possibile to save this times to log file?

Secound question:
do You know how to run script frequently (every 3,5,10 minutes)?
And make log files for every run (with only times of running scripts and status: ok, warning,error)?

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Mass quering from SQL files (or DB)

Post by NorbertKrupa » Fri Feb 26, 2016 4:56 pm

How about creating cascading resource pools? You can cascade slow running queries into slower resource pools.
Checkout vertica.tips for more Vertica resources.

Post Reply

Return to “Vertica SQL”