Running Commands and SQL Statements in Impala Shell

This topic provides the commonly used syntax and shortcut keys in impala-shell.

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

To see the full set of available commands, press TAB twice.
To cycle through and edit previous commands, click the up-arrow and down-arrow keys.
Use the standard set of keyboard shortcuts in GNU Readline library for editing and cursor movement, such as Ctrl-A for the beginning of line and Ctrl-E for the end of line.
Commands and SQL statements must be terminated by a semi-colon.
Commands and SQL statements can span multiple lines.
Use -- to denote a single-line comment and /* */ to denote a multi-line comment.
A comment is considered part of the statement it precedes, so when you enter a -- or /* */ comment, you get a continuation prompt until you finish entering a statement ending with a semicolon. For example:
```
[impala] > -- This is a test comment
                  > SHOW TABLES LIKE 't*';
```
If a comment contains the ${variable_name} and it is not for a variable substitution, the $ character must be escaped, e.g. -- \${hello}.

Variable Substitution in impala-shell🔗

You can define substitution variables to be used within SQL statements processed by impala-shell.

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.
Use the above variable in SQL statements in the impala-shell session using the notation: ${VAR:variable_name}.

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'
Query: CREATE TABLE table1 (x STRING) 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};

When you run a query, the live progress bar appears in the output of a query. The bar shows roughly the percentage of completed processing. When the query finishes, the live progress bar disappears from the console output.

Running Commands and SQL Statements in Impala Shell

Variable Substitution in impala-shell🔗

We want your opinion

How can we improve this page?