MapR 5.0 Documentation : Impala-Shell Commands and Command Line Options

You can specify impala-shell options from the command line or in the $HOME/.impalarc configuration file. You can define a set of default options for your impala-shell environment in the $HOME/.impalarc configuration file. For every command line option, there is an equivalent setting in the $HOME/.impalarc configuration file. Options specified from the impala-shell command line override corresponding options in the configuration file.

The configuration file must contain a header label [impala], followed by the options specific to impala-shell. This is a standard convention for configuration files that enables a single file to hold configuration options for multiple applications. To specify a different file name or path for the configuration file, specify the argument --config_file=path_to_config_file on the impala-shell command line.

Impala-Shell Commands

After you connect to an instance of impalad from the impala-shell, you can issue certain commands from within the shell. Enter a command at the prompt or use the –q option to pass it as an argument. The impala-shell passes most of the commands to impalad as SQL statements. 

Command

Description

alter

Changes table schema in an Impala table, or a table shared between Impala and Hive.

compute statsGathers critical, statistical information about each table when you enable join optimizations.

connect

Connects impala-shell to a specific instance of impalad. The default port is 21000 unless modified. You can connect to any host in your cluster running impalad. If you connect to an instance of impalad that was started with an alternate port specified by the --fe_port flag, provide the alternate port.

describe

Shows the columns, column data types, and column comments for the table specified. DESCRIBE FORMATTED shows additional information, such as the MapR-FS data directory, partitions, and internal table properties. You can use desc as an abbreviated alternative for describe.

drop

Removes a schema object and its associated data files.

explain

Provides the query execution plan and represents a query as a series of steps.

help

Provides a list of available commands and options.

history

Maintains an enumerated cross-session command history. The history is stored in ~/.impalahistory.

insert

Writes query results to the table specified. Overwrites table data or appends data to the existing table content.

invalidate metadata

Updates impalad metadata. Use this command after creating, dropping, or altering databases, tables, or partitions in Hive.

profile

Displays low-level information about the most recent query. Used for performance diagnosis and tuning.

quit

Exits the shell. Include the final semicolon to ensure that the shell recognizes the end of the command.

refresh

Refreshes impalad metadata. Issue the REFRESH command to refresh metadata after you make database changes, such as adding or removing a table.

select

Specifies the data set to complete an action upon. You can send the information returned from select to an output, such as the console or a file. You can also use it to complete another query element.

set

Manages query options for an impala-shell session. Issue query options to Impala using the SET command. Issue SET without arguments to see display a current list query options. Use these options for query tuning and troubleshooting. SET has been promoted to an SQL statement and can be used in client applications through the JDBC and ODBC APIs. To modify option values, issue commands with the syntax SET option=value. To restore an option to its default, use the UNSET command. Some options take Boolean values of true and false. Others take numeric arguments, or quoted string values.

shell

Executes commands specified in the operating system shell without exiting impala-shell. Use !  as an abbreviation for the shell command.

show

Displays metastore data for schema objects created and accessed through Impala and Hive. Use show to gather information about databases or tables.

summarySummarizes the work performed in various stages of a query. It provides a high-level view of the information displayed by the EXPLAIN command.

unset

Removes user-specified values for a query option and returns the option to its default value.

use

Indicates the database against which to run subsequent commands. Avoid using fully qualified names when referring to tables in databases other than default. Do not use with the -q option.

version

Returns Impala version information.

Impala-Shell Command Line Options

Specify the command-line options to change how shell commands run when you start the impala-shell. These options do not apply to impalad configuration.

Option

Configuration File Setting

Description

-B or --delimited

write_delimited=true

Prints query results in plain text format. Specify the delimiter character with the --output_delimiter option. Store query results in a file instead of printing to the screen with the -B option.

--print_header

print_header=true

Prints a header.

-o filename or --output_file filename

output_file=filename

Stores all query results in the file specified.

--output_delimiter=delimiter_character

output_delimiter=character

Defaults to tab ('\t'), and specifies the character to use as a delimiter between fields when query results are printed in plain text format by the -B option.

-p or --show_profiles

show_profiles=true

Displays the query execution plan. Provides the same output as the EXPLAIN statement with a detailed itemization of execution steps, for every query executed by the shell.

-h or --help

N/A

Displays help information.

-i hostname or --impalad=hostname

impalad=hostname[:portnum]

Connects to impalad on the host specified. The default port of 21000 is assumed unless you provide another value. If you connect to an instance of impalad that was started with an alternate port specified by the --fe_port flag, provide that port.

-q query or --query=query

query=query

Passes a query or shell command from the command line. The shell immediately exits after processing the statement. You can use the following statements with the command:

  • SELECT 
  • CREATE TABLE
  • SHOW TABLES
  • Any other statement recognized in impala-shell

Fully qualify the names for tables outside the default database. You can also issue the -f option to pass a file with a USE statement followed by other queries.

-f query_file or --query_file=query_file

query_file=path_to_query_file

Passes a SQL query from a file. The file must be semicolon (;) delimited.

-k or --kerberos

use_kerberos=true

Uses Kerberos authentication when the shell connects to impalad. If Kerberos is not enabled on the impalad instance, an error displays.

-s kerberos_service_name or --kerberos_service_name=namekerberos_service_name=nameInstructs theimpala-shell to authenticate to a particular impalad service principal. If a kerberos_service_name is not specified, impala is used by default. Using this option in conjunction with a connection in which Kerberos is not supported returns erros.

-V or --verbose

verbose=true

Enables verbose output.

--quiet

verbose=false

Disables verbose output.

-v or --version

version=true

Displays version information.

-c

ignore_query_failure=true

Continues on query failure.

-r or --refresh_after_connect

refresh_after_connect=true

Refreshes Impala metadata upon connection. Equivalent to running the REFRESH statement after connecting.

-d default_db or --database=default_db

default_db=default_db

Specifies the database to use on startup. Equivalent to running the USE statement after connecting. If you do not specify a name, a database named default is used.

-sslssl=trueEnables SSL for impala-shell.
Default: ssl=true
--ca_cert=path_to_certificateca_cert=path_to_certificateThe local pathname that points to the third-party CA certificate or a copy of the server certificate for self-signed server certificates. If --ca_cert is not set, impala-shell enables SSL, but does not validate the server certificate. This is useful for connecting to a known-good Impala that is only running over SSL, when a copy of the certificate is not available (such as when debugging customer installations).
-luse_ldap=trueEnables LDAP authentication.
-u user=user_nameSets the user. The impala-shell prompts you for the password. Per Active Directory, the user is the short username, not the full LDAP distinguished name.
--config_file=path_to_config_fileN/ASpecifies the path to the file that contains the impala-shell configuration settings. The default path is $HOME/.impalarc. You can only specify this setting on the command line.

The --strict_unicode option no longer exists. To avoid problems with Unicode values in impala-shell, define the following setting before running impala-shell:

export LC_CTYPE=en_US.UTF-8