Run Impala SQL Script File Passing argument and Working Example

If you are porting Hive SQL scripts to Impala, you may come across passing variable to sql script as argument in Impala. You may get challenge to run Impala SQL script file passing argument. Prior to impala-shell version 2.5 there was no option to pass the value to script as arguments.

Read:

• Impala Dynamic SQL Support and Alternative Approaches
• Run Hive Script File Passing Parameter and Working Example

CDH 5.7/Impala shell version 2.5 and higher run Impala SQL Script File Passing argument. You can make use of the –var=variable_name option in the impala-shell command line option to pass the value. And refer that variable inside SQL script by using statement like ${var:variable_name}. The Impala will resolve the variable in run-time and execute the script by passing actual value.

Impala SQL Script File passing argument Example

Consider the below query that you want to add to sql file:

select id, name, dep_id from test
where dep_id = ?;

where ? — Value to be passed as argument to script.

You can use –var=variable_name command line option in impala-shell.

$impala-shell -i <impalad:port> -f test_fl.sql --var=variable_name=100

Now refer the variable_name inside the sql script.

select id, name, dep_id from test
where dep_id = ${var:val};

Impala SQL Script File Passing Argument Query Output

The complete outputs of the query when you run Impala SQL script file passing argument is as given below.

$impala-shell -i <impalad:port> -f test_fl.sql --var=val=100
Starting Impala Shell without Kerberos authentication
Connected to quickstart.cloudera:21000
Server version: impalad version 2.6.0-cdh5.8.0 RELEASE (build 5464d1750381b40a7e7163b12b09f11b891b4de3)
Query: select id, name, dep_id from test
where dep_id = 100
+----+------+--------+
| id | name | dep_id |
+----+------+--------+
| 1 | abc | 100 |
+----+------+--------+
Fetched 1 row(s) in 7.17s
$

Be advised that this option is available only in Impala-shell version 2.5 and above. It is not available in lower version of Impala shell. If you have lower version of Impala-shell version then consider upgrading that to latest version to enjoy these features.