Run SQL Script File using Vertica vsql Variable Substitution

Variable substitution is nothing but passing value to a variable referred in Vertical SQL queries. Let me explain this using an example. Imagine you have written a general SQL script to generate monthly aggregated data for a particular report, you have to provide a current month values to a filter variable referred in SQL script. Another instance could be executing Vertica SQL queries from Linux shell script and passing shell variable value to a variable referred in SQL script. You can accomplish this task using Vertica vsql variable substitution method.

Run SQL Script File using Vertica vsql Variable Substitution

The variable substitution is very important when you are calling the Vertica SQL scripts from shell or any other scripting language. You can pass the values to query that you are calling using various vsql command line options.

vsql Variable Substitution Options

There are three vsql command line options that you can use to pass variable to a SQL script on command line:

–v variable=value
–set variable=value
–variable variable=value

You can use any of the above-mentioned methods to substitute the variable value within Vertica SQL scripts.

Below is the sample syntax that you can use to run SQL script file using vsql variable substitution:

[dbadmin@localhost ~]$ vsql -d vmart -U dbadmin -w password -v -h vertica_host -v name=val -f '/path/script.sql'

Above command is the sample example to set vsql variable ‘name’ to value ‘val’.

Vertica vsql Variable Substitution Example

Below are the working examples to run SQL script file using Vertica vsql variable substitution:

Using –v Variable Substitution method

This is one of the easy and widely used variable substitution methods. Syntax and usage is same as other databases such as Netezza, Redshift etc.

[dbadmin@localhost ~]$ cat test.sql
select * from testdb.patient_1
where dept = :dpt;

[dbadmin@localhost ~]$ vsql -d vmart -U dbadmin -w password -h localhost -v dpt=101 -f test.sql;
 id | name | dept
----+------+------
  1 | val  |  101
(1 row)

Use –variable Substitution method

Some of the database developer use more expressive variable substitution methods.

[dbadmin@localhost ~]$ vsql -d vmart -U dbadmin -w password -h localhost --variable dpt=101 -f test.sql;
 id | name | dept
----+------+------
  1 | val  |  101
(1 row)

Using –set Variable Substitution method

You can also use the variable substitution feature similar to Linux command line shells.

[dbadmin@localhost ~]$ vsql -d vmart -U dbadmin -w password -h localhost --set dpt=101 -f test.sql;
 id | name | dept
----+------+------
  1 | val  |  101
(1 row)

You can use the any scripting program to dynamically pass the parameter to the Vertica SQL script file.