Impala-Shell常用操作命令

一、外部Shell

选项    描述
-h, --help    显示帮助信息
-v or --version    显示版本信息
-i hostname, --impalad=hostname    指定连接运行 impalad 守护进程的主机。默认端口是 21000。
-q query, --query=query    从命令行中传递一个shell 命令。执行完这一语句后 shell 会立即退出。
-f query_file, --query_file= query_file    传递一个文件中的 SQL 查询。文件内容必须以分号分隔
-o filename or --output_file filename    保存所有查询结果到指定的文件。通常用于保存在命令行使用 -q 选项执行单个查询时的查询结果。
-c    查询执行失败时继续执行
-d default_db or --database=default_db    指定启动后使用的数据库，与建立连接后使用use语句选择数据库作用相同，如果没有指定，那么使用default数据库
-r or --refresh_after_connect    建立连接后刷新 Impala 元数据
-p, --show_profiles    对 shell 中执行的每一个查询，显示其查询执行计划
-B（--delimited）    去格式化输出
--output_delimiter=character    指定分隔符
--print_header    打印列名

impala-shell -h:

[root@ai-etl-c2-13 ~]# impala-shell -h
Usage: impala_shell.py [options]

Options:
  -h, --help            show this help message and exit
  -i IMPALAD, --impalad=IMPALAD
                        <host:port> of impalad to connect to
                        [default: ai-etl-c2-13:21000]
  -b KERBEROS_HOST_FQDN, --kerberos_host_fqdn=KERBEROS_HOST_FQDN
                        If set, overrides the expected hostname of the
                        Impalad's kerberos service principal. impala-shell
                        will check that the server's principal matches this
                        hostname. This may be used when impalad is configured
                        to be accessed via a load-balancer, but it is desired
                        for impala-shell to talk to a specific impalad
                        directly. [default: none]
  -q QUERY, --query=QUERY
                        Execute a query without the shell [default: none]
  -f QUERY_FILE, --query_file=QUERY_FILE
                        Execute the queries in the query file, delimited by ;.
                        If the argument to -f is "-", then queries are read
                        from stdin and terminated with ctrl-d. [default: none]
  -k, --kerberos        Connect to a kerberized impalad [default: False]
  -o OUTPUT_FILE, --output_file=OUTPUT_FILE
                        If set, query results are written to the given file.
                        Results from multiple semicolon-terminated queries
                        will be appended to the same file [default: none]
  -B, --delimited       Output rows in delimited mode [default: False]
  --print_header        Print column names in delimited mode when pretty-
                        printed. [default: False]
  --output_delimiter=OUTPUT_DELIMITER
                        Field delimiter to use for output in delimited mode
                        [default: \t]
  -s KERBEROS_SERVICE_NAME, --kerberos_service_name=KERBEROS_SERVICE_NAME
                        Service name of a kerberized impalad [default: impala]
  -V, --verbose         Verbose output [default: True]
  -p, --show_profiles   Always display query profiles after execution
                        [default: False]
  --quiet               Disable verbose output [default: False]
  -v, --version         Print version information [default: False]
  -c, --ignore_query_failure
                        Continue on query failure [default: False]
  -d DEFAULT_DB, --database=DEFAULT_DB
                        Issues a use database command on startup
                        [default: none]
  -l, --ldap            Use LDAP to authenticate with Impala. Impala must be
                        configured to allow LDAP authentication.
                        [default: False]
  -u USER, --user=USER  User to authenticate with. [default: root]
  --ssl                 Connect to Impala via SSL-secured connection
                        [default: False]
  --ca_cert=CA_CERT     Full path to certificate file used to authenticate
                        Impala's SSL certificate. May either be a copy of
                        Impala's certificate (for self-signed certs) or the
                        certificate of a trusted third-party CA. If not set,
                        but SSL is enabled, the shell will NOT verify Impala's
                        server certificate [default: none]
  --config_file=CONFIG_FILE
                        Specify the configuration file to load options. The
                        following sections are used: [impala],
                        [impala.query_options]. Section names are case
                        sensitive. Specifying this option within a config file
                        will have no effect. Only specify this as an option in
                        the commandline. [default: /root/.impalarc]
  --history_file=HISTORY_FILE
                        The file in which to store shell history. This may
                        also be configured using the IMPALA_HISTFILE
                        environment variable. [default: ~/.impalahistory]
  --live_summary        Print a query summary every 1s while the query is
                        running. [default: False]
  --live_progress       Print a query progress every 1s while the query is
                        running. [default: False]
  --auth_creds_ok_in_clear
                        If set, LDAP authentication may be used with an
                        insecure connection to Impala. WARNING: Authentication
                        credentials will therefore be sent unencrypted, and
                        may be vulnerable to attack. [default: none]
  --ldap_password_cmd=LDAP_PASSWORD_CMD
                        Shell command to run to retrieve the LDAP password
                        [default: none]
  --var=KEYVAL          Defines a variable to be used within the Impala
                        session. Can be used multiple times to set different
                        variables. It must follow the pattern "KEY=VALUE", KEY
                        starts with an alphabetic character and contains
                        alphanumeric characters or underscores. [default:
                        none]
  -Q QUERY_OPTIONS, --query_option=QUERY_OPTIONS
                        Sets the default for a query option. Can be used
                        multiple times to set different query options. It must
                        follow the pattern "KEY=VALUE", KEY must be a valid
                        query option. Valid query options  can be listed by
                        command 'set'. [default: none]
  -t CLIENT_CONNECT_TIMEOUT_MS, --client_connect_timeout_ms=CLIENT_CONNECT_TIMEOUT_MS
                        Timeout in milliseconds after which impala-shell will
                        time out if it fails to connect to Impala server. Set
                        to 0 to disable any timeout. [default: 60000]

二、内部Shell

选项    描述
help    显示帮助信息
explain <sql>    显示执行计划
profile (查询完成后执行）    查询最近一次查询的底层信息
shell <shell>    不退出impala-shell执行shell命令
version    显示版本信息（同于impala-shell -v）
connect    连接impalad主机，默认端口21000（同于impala-shell -i）
refresh <tablename>    增量刷新元数据库
invalidate metadata    全量刷新元数据库（慎用）（同于 impala-shell -r）
history    历史命令

[root@ai-etl-c2-13 ~]# impala-shell :help
 

[root@ai-etl-c2-13 ~]# impala-shell
Starting Impala Shell without Kerberos authentication
Opened TCP connection to ai-etl-c2-13:21000
Connected to ai-etl-c2-13:21000
Server version: impalad version 3.2.0-cdh6.3.0 RELEASE (build 495397101e5807c701df71ea288f4815d69c2c8a)
***********************************************************************************
Welcome to the Impala shell.
(Impala Shell v3.2.0-cdh6.3.0 (4953971) built on Thu Jul 18 10:24:11 PDT 2019)

When pretty-printing is disabled, you can use the '--output_delimiter' flag to set
the delimiter for fields in the same row. The default is '\t'.
***********************************************************************************
[ai-etl-c2-13:21000] default> help;

Documented commands (type help <topic>):
========================================
compute   exit     history  rerun   shell  unset   version
connect   explain  profile  select  show   use     with   
describe  help     quit     set     tip    values

Undocumented commands:
======================
alter   delete  drop    load    src      update
create  desc    insert  source  summary  upsert

[ai-etl-c2-13:21000] default>