CODEWITHSUNDEEP
bashshgreenplum
ExelIQ
ExelIQ

Reputation: 49

Creating a sh script that runs a sql script with user defined parameters

I am new to creating sh scripts. I am trying to create a .sh script that would take in user input and execute a SQL script by updating the parameter as given by the user.

The user input is just limited to a single parameter. The parameter to be passed is a job id -

Enter job id: 321
Confirm job id: 321

This job id is used in different parts of the SQL code, an excerpt of the code is below -

CREATE READABLE EXTERNAL TEMP TABLE JOBID_USER (
    cola numeric,
    colb bigint,
    )
LOCATION (
    'gpfdist://.../JOBID_USER_INFO_321.txt' --the job id is at the end of the file
)
FORMAT 'text' (header delimiter e',' null '' escape 'off') 
ENCODING 'latin9';
-------------------------------------------------------------------------------------------

CREATE TABLE AS 
SELECT * FROM schema.deliverytable_321 -- the job id is used to identify a table
        WHERE JOB_ID= 321              -- the job id is used as a condition
        AND asset_1 = 9999
DISTRIBUTE RANDOMLY;

The DB is Postgres and I execute the script manually in Greenplum. Any help would be appreciated. Thanks.

Upvotes: 0

Views: 208

Answers (3)

mah454
mah454

Reputation: 1928

This is only a simple idea.
wrote your sql queries in a file named sample.sql or something else :
Note: Replaced JobID to MY_VAR

CREATE READABLE EXTERNAL TEMP TABLE JOBID_USER (
    cola numeric,
    colb bigint,
    )
LOCATION (
    'gpfdist://.../JOBID_USER_INFO_MY_VAR.txt' --the job id is at the end of the file
)
FORMAT 'text' (header delimiter e',' null '' escape 'off') 
ENCODING 'latin9';
-------------------------------------------------------------------------------------------

CREATE TABLE AS 
SELECT * FROM schema.deliverytable_MY_VAR -- the job id is used to identify a table
        WHERE JOB_ID= MY_VAR              -- the job id is used as a condition
        AND asset_1 = 9999
DISTRIBUTE RANDOMLY;

now use this script named like sql_executor.sh:

#!/bin/bash 
sed "s/MY_VAR/$1" sample.sql | psql -U [USERNAME] -d [DATABASE_NAME]

Usage :

sql_executor.sh 321

Upvotes: 2

Jon Roberts
Jon Roberts

Reputation: 2106

psql has "variables" that you can use for parameters. For example:

cat foo.sql 
select :param1

The colon in the SQL file indicates it is a variable.

To use this variable, you just pass a value to it with psql.

psql -f foo.sql -v param1="'bar'"
 ?column? 
----------
 bar
(1 row)

To execute this from a bash script, you can use a bash variable.

cat runme.sql 
#!/bin/bash
param1="$1"
psql -f foo.sql -v param1="'$param1'"

Note: be sure to chmod runme.sql so that you can execute it.

chmod 755 runme.sql

And now running it.

./runme.sql foobar
 ?column? 
----------
 foobar
(1 row)

Upvotes: 1

Francesco Gasparetto
Francesco Gasparetto

Reputation: 1963

This is how you get input interactively in bash

#!/bin/bash

printf "Enter job id:"
read -r JOBID
printf "Confirm job id:"
read -r JOBIDCONFIRM

[ "$JOBID" != "$JOBIDCONFIRM" ] && {
        echo "Jobid inputs have not the same value"
        exit
}

echo "Now you can use your jobid $JOBID anywhere in double quoted strings, like this sentence"

EXPLANATION:

printf: echo a string
read [varname]: wait for user input and assign it to [varname] variable

Upvotes: 1

Related Questions