beeline: 新版连接Hive server的工具

梦里梦外; 2023-05-22 10:23 1阅读 0赞

HiveServer2 支持一个新的命令行Shell，称为Beeline，它是基于SQLLine CLI的JDBC客户端。它是从 Hive 0.11版本引入的,是Hive新的命令行客户端工具。Hive客户端工具后续将使用Beeline 替代HiveCLI ，并且后续版本也会废弃掉HiveCLI 客户端工具。

Beeline支持嵌入模式(embedded mode)和远程模式(remote mode)。在嵌入式模式下，运行嵌入式的Hive(类似Hive CLI)，而远程模式可以通过Thrift连接到独立的HiveServer2进程上。从Hive 0.14版本开始，Beeline使用HiveServer2工作时，它也会从HiveServer2输出日志信息到STDERR。  
下面是beeline 的常用参数

Usage: java org.apache.hive.cli.beeline.BeeLine 
       -u <database url>               the JDBC URL to connect to
       -n <username>                   the username to connect as
       -p <password>                   the password to connect as
       -d <driver class>               the driver class to use
       -i <init file>                  script file for initialization
       -e <query>                      query that should be executed
       -f <exec file>                  script file that should be executed
       -w (or) --password-file <password file>  the password file to read password from
       --hiveconf property=value       Use value for given property
       --hivevar name=value            hive variable name and value
                                       This is Hive specific settings in which variables
                                       can be set at session level and referenced in Hive
                                       commands or queries.
       --color=[true/false]            control whether color is used for display
       --showHeader=[true/false]       show column names in query results
       --headerInterval=ROWS;          the interval between which heades are displayed
       --fastConnect=[true/false]      skip building table/column list for tab-completion
       --autoCommit=[true/false]       enable/disable automatic transaction commit
       --verbose=[true/false]          show verbose error messages and debug info
       --showWarnings=[true/false]     display connection warnings
       --showNestedErrs=[true/false]   display nested errors
       --numberFormat=[pattern]        format numbers using DecimalFormat pattern
       --force=[true/false]            continue running script even after errors
       --maxWidth=MAXWIDTH             the maximum width of the terminal
       --maxColumnWidth=MAXCOLWIDTH    the maximum width to use when displaying columns
       --silent=[true/false]           be more silent
       --autosave=[true/false]         automatically save preferences
       --outputformat=[table/vertical/csv2/tsv2/dsv/csv/tsv]  format mode for result display
                                       Note that csv, and tsv are deprecated - use csv2, tsv2 instead
      --truncateTable=[true/false]    truncate table column when it exceeds length
       --delimiterForDSV=DELIMITER     specify the delimiter for delimiter-separated values output format (default: |)
       --isolation=LEVEL               set the transaction isolation level
       --nullemptystring=[true/false]  set to true to get historic behavior of printing null as empty string
       --addlocaldriverjar=DRIVERJARNAME Add driver jar file in the beeline client side
       --addlocaldrivername=DRIVERNAME Add drvier name needs to be supported in the beeline client side
       --help                          display this message
    Beeline version 1.2.1.spark2 by Apache Hive

中文意思为：

The Beeline CLI 支持以下命令行参数:    
    Option    
    Description    
    --autoCommit=[true/false] ---进入一个自动提交模式：beeline --autoCommit=true    
    --autosave=[true/false]   ---进入一个自动保存模式：beeline --autosave=true    
    --color=[true/false]    ---显示用到的颜色：beeline --color=true    
    --delimiterForDSV= DELIMITER ---分隔值输出格式的分隔符。默认是“|”字符。    
    --fastConnect=[true/false]  ---在连接时，跳过组建表等对象：beeline --fastConnect=false    
    --force=[true/false]    ---是否强制运行脚本：beeline--force=true    
    --headerInterval=ROWS   ---输出的表间隔格式，默认是100: beeline --headerInterval=50    
    --help ---帮助  beeline --help    
    --hiveconf property=value  ---设置属性值，以防被hive.conf.restricted.list重置：beeline --hiveconf prop1=value1     
    --hivevar name=value   ---设置变量名：beeline --hivevar var1=value1    
    --incremental=[true/false]  ---输出增量  
    --isolation=LEVEL  ---设置事务隔离级别：beeline --isolation=TRANSACTION_SERIALIZABLE    
    --maxColumnWidth=MAXCOLWIDTH ---设置字符串列的最大宽度：beeline --maxColumnWidth=25    
    --maxWidth=MAXWIDTH ---设置截断数据的最大宽度：beeline --maxWidth=150    
    --nullemptystring=[true/false]  ---打印空字符串：beeline --nullemptystring=false    
    --numberFormat=[pattern]     ---数字使用DecimalFormat：beeline --numberFormat="#,###,##0.00"    
    --outputformat=[table/vertical/csv/tsv/dsv/csv2/tsv2] ---输出格式：beeline --outputformat=tsv     
    --showHeader=[true/false]   ---显示查询结果的列名：beeline --showHeader=false    
    --showNestedErrs=[true/false] ---显示嵌套错误：beeline --showNestedErrs=true    
    --showWarnings=[true/false] ---显示警告：beeline --showWarnings=true    
    --silent=[true/false]  ---减少显示的信息量：beeline --silent=true    
    --truncateTable=[true/false] ---是否在客户端截断表的列       
    --verbose=[true/false]  ---显示详细错误信息和调试信息：beeline --verbose=true    
    -d <driver class>  ---使用一个驱动类：beeline -d driver_class    
    -e <query>  ---使用一个查询语句：beeline -e "query_string"    
    -f <file>  ---加载一个文件：beeline -f filepath  多个文件用-e file1 -e file2  
    -n <username>  ---加载一个用户名：beeline -n valid_user    
    -p <password>  ---加载一个密码：beeline -p valid_password    
    -u <database URL> ---加载一个JDBC连接字符串：beeline -u db_URL

**参数说明：**

这里比较相近的两个参数是 -i 与 -f  其中两个参数的主要区别从字面上就可以很好的区分了

\-f 执行SQL文件后就直接退出Beeline客户端，一般编写需要执行的 HQL

\-i  执行SQL文件后进行Beeline客户端。一般为初始化的参数设置  hive 中的变量。。

看下参数说明

-i <init file>                  script file for initialization  
 -f <exec file>                  script file that should be executed

**beeline 使用示例：**

beeline 的用法基本与 hive-cli 用法相同，下面是一个连接示例： 以cloudera-scm 的身份 以jdbc 的方式连接远程的 hiveserver2。

执行脚本功能如下：

nohup beeline -u jdbc:hive2://10.180.0.26:10000  -n cloudera-scm 
    --color=true --silent=false  
    --hivevar p_date=${partitionDate} --hivevar f_date=${fileLocDate} 
    -f hdfs_add_partition_dmp_clearlog.hql  >> $logdir/load_${curDate}.log

通过jdbc 连接到beeline 就可以对 hive 进行操作了。

过程示意图如下：

![2018061321560170][]

我们再来看看看beeline 中除了SQL 操作，还支持什么别的操作。

执行help，则输出如下

!addlocaldriverjar  Add driver jar file in the beeline client side.
    !addlocaldrivername Add driver name that needs to be supported in the beeline
                        client side.
    !all                Execute the specified SQL against all the current connections
    !autocommit         Set autocommit mode on or off
    !batch              Start or execute a batch of statements
    !brief              Set verbose mode off
    !call               Execute a callable statement
    !close              Close the current connection to the database
    !closeall           Close all current open connections
    !columns            List all the columns for the specified table
    !commit             Commit the current transaction (if autocommit is off)
    !connect            Open a new connection to the database.
    !dbinfo             Give metadata information about the database
    !describe           Describe a table
    !dropall            Drop all tables in the current database
    !exportedkeys       List all the exported keys for the specified table
    !go                 Select the current connection
    !help               Print a summary of command usage
    !history            Display the command history
    !importedkeys       List all the imported keys for the specified table
    !indexes            List all the indexes for the specified table
    !isolation          Set the transaction isolation for this connection
    !list               List the current connections
    !manual             Display the BeeLine manual
    !metadata           Obtain metadata information
    !nativesql          Show the native SQL for the specified statement
    !nullemptystring    Set to true to get historic behavior of printing null as
                        empty string. Default is false.
    !outputformat       Set the output format for displaying results
                        (table,vertical,csv2,dsv,tsv2,xmlattrs,xmlelements, and
                        deprecated formats(csv, tsv))
    !primarykeys        List all the primary keys for the specified table
    !procedures         List all the procedures
    !properties         Connect to the database specified in the properties file(s)
    !quit               Exits the program
    !reconnect          Reconnect to the database
    !record             Record all output to the specified file
    !rehash             Fetch table and column names for command completion
    !rollback           Roll back the current transaction (if autocommit is off)
    !run                Run a script from the specified file
    !save               Save the current variabes and aliases
    !scan               Scan for installed JDBC drivers
    !script             Start saving a script to a file
    !set                Set a beeline variable
    !sh                 Execute a shell command
    !sql                Execute a SQL command
    !tables             List all the tables in the database
    !typeinfo           Display the type map for the current connection
    !verbose            Set verbose mode on
     
    Comments, bug reports, and patches go to ???

常用的几个指令：

1、!connect url –连接不同的Hive2服务器    
2、!exit –退出shell    
3、!help –显示全部命令列表    
4、!verbose –显示查询追加的明细

[2018061321560170]: /images/20230521/595f8c401295420dbcd106693956241d.png