Vertica Dynamic SQL Support and Alternative

Vertica dynamic SQL lets SQL statements be defined and run at run time, i.e. you can build SQL queries based on the user input and execute them to provide required output. For examples, pass a  date value or session specific values to the SQL queries dynamically.

Most of the modern day databases such as Netezz, Oracle, etc support dynamic SQL as a part of stored procedure or functions. Unfortunately, Vertica database does not provide support for dynamic SQL. Hopefully, future versions of Vertica database may provide support to the dynamic SQL. In this article, we will check alternate approach that can be used to build and execute dynamic SQL queries.

Vertica Dynamic SQL Alternative

As mentioned earlier, as of now Vertica does not provide support to dynamic SQL queries.

Below are the couple of alternate approaches you can use to build and execute dynamic queries on the Vertica analytic database.

• Create a shell script to build and execute SQL queries dynamically
• Use VSQL Variable substitution feature to pass value dynamically

Now, let us discuss these approaches with examples.

Create a Shell Script to build and Execute SQL Queries Dynamically

Vertica database is built on cent OS. It will be easy to create shell script that builds the SQL dynamically and use VSQL to execute that generated dynamic query.

The idea is to build the shell script that generates SQL queries based on the user input.  Below is the sample shell script that generates SQL dynamically and execute it:

#!/bin/sh

#######################################
#
# Usage: dynamic-SQL.sh id
#
######################################
id=$1
echo $id

# Build SQL Query
sql_str="select id, name from training.table_1 where id = $id"
echo $sql_str

# Execute dynamic queries using vsql
vsql -d vmart -U dbadmin -w password -h 192.168.239.132 -c "$sql_str"

Now execute the shell script by passing ID as an argument.

For example,

[dbadmin@localhost ~]$ sh dynamic-SQL.sh 1
1
Generated SQL file.
select id, name from training.table_1 where id = 1
 id | name
----+------
  1 | A
(1 row)

[dbadmin@localhost ~]$

VSQL Variable Substitution

Variable substitution is nothing but passing values to a variable referred in Vertical SQL queries. I have already discussed variable substitution in my other post, Run SQL Script File using Vertica vsql Variable Substitution.

Hope this helps 🙂