Impala Dynamic SQL Support and Alternative Approaches

  • Post author:
  • Post last modified:May 29, 2019
  • Post category:BigData
  • Reading time:5 mins read

Dynamic SQL lets SQL statements be created and execute at run time, i.e. you can build SQL queries based on the user or application input and execute them to provide required output. For examples, pass a session specific value to the Impala queries dynamically during runtime. In this article, we will check how to build Cloudera Impala Dynamic SQL queries and how to execute them.

Impala Dynamic SQL Support and Alternative Approaches

Most of the relational databases like Netezza, Teradata, etc. supports stored procedure that will allow you to build and execute dynamic queries.

Impala Dynamic SQL Support

Just like Hive1.x, Cloudera impala does not support dynamic SQL queries. However, Hive version 2.x supports HPL/SQL which has EXECUTE IMMEDIATE to execute dynamic SQL. You can build the procedures and functions in Hive HPL/SQL procedural language.

As mentioned earlier, hive 1.x also does not support dynamic SQL queries. You have to use alternate approach to build and execute dynamic queries.

Related Articles

Impala Dynamic SQL Alternative

There are many methods that you can use to provide dynamic SQL supports. You can use scripting languages like Shell script or Python to generate and execute SQL queries dynamic queries.

Below are the couple of options that you can use to build and execute dynamic queries at run-time. Both method uses impala shell to execute the generated queries.  

  • Impala Dynamic SQL using Shell Script
  • Impala Shell Variable Substitution

Above mentioned methods are commonly used and easy methods. Now let us check these methods in detail. Dynamic SQL using Shell Script

Shell script is one of the widely used scripting language to interact with Linux operating system. In this method, you have to create a shell script to generate SQL query dynamically and execute using Impala shell.

Below is the sample shell script that generates SQL dynamically and execute it:

#!/bin/sh

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

# Build SQL Query
sql_str="select id, name from my_table where id = $id"
echo $sql_str

# Execute dynamic queries using impala-shell

impala-shell -q "$sql_str"

Note that, for kerberos authentication, impala-shell command will slightly change. For more information, read official page.

Now execute crated shell script to by passing an argument.

[cloudera@quickstart ~]$ sh impala-dynamic-SQL.sh 1
1
select id, name from my_table where id = 1
Starting Impala Shell without Kerberos authentication
Connected to quickstart.cloudera:21000
...
...
+----+------+
| id | name |
+----+------+
| 1  | aaa  |
+----+------+
Fetched 1 row(s) in 0.57s
[cloudera@quickstart ~]$

Impala Shell Variable Substitution

Variable substitution is nothing but passing values to a variable referred in Impala SQL queries. Prior to impala-shell version 2.5 there was no option to pass the value to script as arguments. Luckily, latest version of impala shell does support variable substitution.

I have already discussed variable substitution in my other post, Run Impala SQL Script File Passing argument and Working Example.

Hope this helps 🙂

Tags: , ,