Automating SQL script

Question

I am new to programming world. I have a SQL script which needs to be automated. The automation required is as follows :

1) Script should run every sunday

2) Automatically dump the results in to DUMP_YYYYMMDDHH24MISS.txt

3) Result set is tar gziped

4) upload to SFTP URL with provided username and password.

I am using : UNIX, Vertica DB

Can the Gurus here please help ?

For (3) : for i in `ls *.txt`; do /usr/bin/gzip $i; done For (4) : sftp UNAME@IPADDRESS sftp> mput .gz No luck yet at (1) and (2) , appreciate any pointers you may have to give me directions as i am a rookie - 1st day at programming :) — Dr.Evil, Oct 21 '16 at 13:59
This is pretty broad. The short answer is, write a shell script that calls `vsql` to connect and execute sql. Use parameters like `\o` to output sql to a file and `\a` to set unaligned data and `\t` for tuples only. Use `gzip` and/or `tar` and scp or sftp client to send the file. Use `cron` to schedule it. This isn't hard, but it's a lot of different pieces if you aren't familiar with them. Start simple with getting vsql to create the file, then build a script around it. — woot, Oct 21 '16 at 14:00
@woot : Got that part covered i guess. \pset border 0 \pset format unaligned \pset footer \pset fieldsep '|' \C \o :fname in the fname : how do i insert the timestamp is the question. — Dr.Evil, Oct 21 '16 at 14:03

score 1 · Accepted Answer · edited Oct 21 '16 at 18:41

1

This is really 4 questions and should probably asked as such. To answer in the current format though:

1) Schedule a Task Automatically - Crontab

In the terminal, type crontab -e.

If you want something every Sunday at 1am, add the following line:

0 1 * * * 0 /path/to/script/script.sh

This will execute the script every Sunday.

2) Setting the output of the command

I'm only familiar with oracle. The format is probably similar. In order to get the filename as you want it, you'd use the date function as follows. (This is how I would do it in with Oracle):

d=$(date +%Y%M%D%H%M)
var=$(sqlplus -s / as blahblahblah
select * from stuff;
exit
EOF
)

file_name=DUMP_${d}MISS.txt
echo "${var}" >> ${file_name}

Note that your date command is probably different, if you do a man page on date it will tell you which parameters you'd need to get the date formatted as you like.

3) Taring the output

tar -xvf ${file_name}

4) Send over SFTP You'd have to authenticate the sftp, that is beyond the scope of what anyone can answer without more details. Once you have the machines setup to authenticate, you would do:

sftp username@server<<EOF
put ${file_name}
EOF

edited Oct 21 '16 at 18:41

woot

7,406
2
36
55

answered Oct 21 '16 at 14:11

mkingsbu

354
3
20

1

Careful. That cron will execute a lot more than one time every Sunday. You want something more like `0 1 * * 0` to run at a specific time every Sunday. – woot Oct 21 '16 at 16:09
Oh yeah duh. I haven't had to every cron off something like this. Normally the day of the week cron is used as a limiting factor, not as the executable factor. (E.g. I've always done this as, run this every day *except* Sunday). Thanks for the catch. – mkingsbu Oct 21 '16 at 17:48
1

Sorry it was still not right. I changed it for you. What you had would execute every 1 minute during the 1am hour on Sunday. ;-) – woot Oct 21 '16 at 18:42
/facepalm Doesn't look like the OP needed this advice or it didn't work for them judging by the absence of any more input after yesterday AM – mkingsbu Oct 22 '16 at 12:14

Automating SQL script

1 Answers1