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.

  • 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}.

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

Variable Substitution in impala-shell

In CDH 5.7 / 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}.

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};