You can use the Impala shell interactive tool (impala-shell) to set up databases and tables, insert data, and issue queries. If you have worked on Netezza or Oracle, this tool is similar to nzsql and SQLPlus. For ad hoc queries and data exploration, you can submit SQL statements in an interactive session. You can write the queries in the script file and execute those using Impala shell Command Line Options.
Read:
Impala shell Command Line Options
| Command line Options | Description |
| -i IMPALAD or — impalad=IMPALAD | Host:port of impala daemon to connect |
| -q QUERY or –query=QUERY | Execute a sql query without the shell. By Default it is none |
| -f QUERY_FILE or –query_file=QUERY_FILE | Execute the queries in the query file. Each query must be delimited by ; |
| -o OUTPUT_FILE or –output_file=OUTPUT_FILE | Query results are written to external file |
| -V or –verbose | Verbose output |
| -B or –delimited | Output rows are delimited |
| –print_header | Print column names in delimited modes |
| –quiet | Disable verbose output |
| -c or –ignore_query_failure | Continue on one or more query failure |
| -r or –refresh_after_connect | Refresh Impala catalog after connecting to that DataNode |
| -d DEFAULT_DB or –database=DEFAULT_DB | Connect to default db on start up |
| -u USER or –user=USER | Use authentication user name |
| –ssl | Enable SSL connections |
| –config_file=CONFIG_FILE | Specify config file name to load defined options |
| –var=KEYVAL | Define variable to be used within the Impala session |
| -l or –ldap | Use LDAP to authenticate with Impala if it is configured to use LDAP authentication. |
| –ldap_password_cmd=LDAP_PASSWORD_CMD | Shell command to run to retrieve the LDAP password |
| –output_delimiter=OUTPUT_DELIMITER | Field delimiter to use for output in delimited mode. Default \t |
Impala shell Command Line Options Usage Examples
Execute query using Impala-shell command line options
$ impala-shell -i <impalad:port> -q "select * from test";
Delimited Output using Impala-shell command line options
$ impala-shell -i <impalad:port> -B -q "select * from test" --output_delimiter='~' --print_header
Create flat file out of sql query
$ impala-shell -i <impalad:port> -B -q "select * from test" --output_delimiter='~' --print_header -o /home/cloudera/output.csv
Find out number of records in table
rowcnt=`impala-shell -B -q "select count(*) from test" --quiet`