Running Commands and SQL Statements in impala-shell

The following are a few of the key syntax and usage rules for running commands and SQL statements in impala-shell.

For information on available impala-shell commands, see impala-shell Command Reference.

Variable Substitution in impala-shell

In Impala 2.5 and higher, you can define substitution variables to be used within SQL statements processed by impala-shell.
  1. You specify the variable and its value as below.
    • On the command line, you specify the option --var=variable_name=value
    • Within an interactive session or a script file processed by the -f option, use the SET VAR:variable_name=value command.
  2. Use the above variable in SQL statements in the impala-shell session using the notation: ${VAR:variable_name}.
Note: Because this feature is part of impala-shell rather than the impalad backend, make sure the client system you are connecting from has the most recent impala-shell. You can use this feature with a new impala-shell connecting to an older impalad, but not the reverse.

For example, here are some impala-shell commands that define substitution variables and then use them in SQL statements executed through the -q and -f options. Notice how the -q argument strings are single-quoted to prevent shell expansion of the ${var:value} notation, and any string literals within the queries are enclosed by double quotation marks.

$ impala-shell --var=tname=table1 --var=colname=x --var=coltype=string -q 'CREATE TABLE ${var:tname} (${var:colname} ${var:coltype}) STORED AS PARQUET'

The below example shows a substitution variable passed in by the --var option, and then referenced by statements issued interactively. Then the variable is reset with the SET command.

$ impala-shell --quiet --var=tname=table1

[impala] > SELECT COUNT(*) FROM ${var:tname};

[impala] > SET VAR:tname=table2;
[impala] > SELECT COUNT(*) FROM ${var:tname};