beeline连接Hive

HiveServer2提供了一个新的命令行工具Beeline，它基于SQLLine CLI的JDBC客户端。
Beeline工作模式有两种，即本地嵌入模式和远程模式。嵌入模式下，它返回一个嵌入式的Hive（类似于Hive CLI）；而远程模式则是通过Thrift协议与某个单独的HiveServer2进程进程连接通信。

权限设置
Beeline使用JDBC连接到HiveServer2时，如果显示User: root is not allowed to impersonate hive，是因为远程访问Hadoop授权异常导致的，则需要修改Hadoop系统的配置文件core-site.xml，添加如下属性
<property>
  <name>hadoop.proxyuser.root.hosts</name>
  <value>*</value>
</property>
<property>
  <name>hadoop.proxyuser.root.groups</name>
  <value>*</value>
</property>

注意：这里登陆的是root用户。若登录的是hadoop用户，则配置文件中的root需要修改为hadoop

当上述文件配置不正确时会可能会引起beeline连接Hive时报如下异常

 User: hadoop is not allowed to impersonate root), serverProtocolVersion:null) (state=08S01,code=0)

先部署安装使用的是hadoop用户，故而需要修改core-site.xml配置文件

修改完毕之后重启hadoop服务进程和Hive服务进程
$/opt/soft/hadoop/sbin/stop-all.sh
[hadoop@master ~]$ stop-all.sh
This script is Deprecated. Instead use stop-dfs.sh and stop-yarn.sh
Stopping namenodes on [master]
master: stopping namenode
slave2: stopping datanode
master: stopping datanode
slave1: stopping datanode
Stopping secondary namenodes [slave1]
slave1: stopping secondarynamenode
stopping yarn daemons
stopping resourcemanager
slave2: stopping nodemanager
master: stopping nodemanager
slave1: stopping nodemanager
slave2: nodemanager did not stop gracefully after 5 seconds: killing with kill -9
master: nodemanager did not stop gracefully after 5 seconds: killing with kill -9
slave1: nodemanager did not stop gracefully after 5 seconds: killing with kill -9
no proxyserver to stop
[hadoop@master ~]$
杀掉Hive服务进程
[root@slave1 ~]# jps -l -m
6432 org.apache.hadoop.yarn.server.nodemanager.NodeManager
24033 org.apache.spark.deploy.worker.Worker --webui-port 8081 spark://master:7077
6228 org.apache.hadoop.hdfs.server.datanode.DataNode
2407 org.apache.hive.beeline.BeeLine -u jdbc:hive2://localhost:10000/default -n hadoop
12250 org.apache.hadoop.util.RunJar /opt/soft/hive-2.3.8/lib/hive-metastore-2.3.8.jar org.apache.hadoop.hive.metastore.HiveMetaStore
11018 org.apache.hadoop.util.RunJar /opt/soft/hive-2.3.8/lib/hive-service-2.3.8.jar org.apache.hive.service.server.HiveServer2
17165 sun.tools.jps.Jps -l -m
6302 org.apache.hadoop.hdfs.server.namenode.SecondaryNameNode
[root@slave1 ~]# kill -9 12250
[root@slave1 ~]# kill -9 11018
关闭spark服务进程
 $/opt/soft/spark/sbin/stop-all.sh
[hadoop@master ~]$ /opt/soft/spark/sbin/stop-all.sh
slave2: stopping org.apache.spark.deploy.worker.Worker
slave1: stopping org.apache.spark.deploy.worker.Worker
stopping org.apache.spark.deploy.master.Master
[hadoop@master ~]$ jps
18536 Jps
[hadoop@master ~]$

启动Hadoop服务进程
[hadoop@master ~]$ start-all.sh
This script is Deprecated. Instead use start-dfs.sh and start-yarn.sh
Starting namenodes on [master]
master: starting namenode, logging to /opt/soft/hadoop/logs/hadoop-hadoop-namenode-master.out
master: starting datanode, logging to /opt/soft/hadoop/logs/hadoop-hadoop-datanode-master.out
slave2: starting datanode, logging to /opt/soft/hadoop/logs/hadoop-hadoop-datanode-slave2.out
slave1: starting datanode, logging to /opt/soft/hadoop/logs/hadoop-hadoop-datanode-slave1.out
Starting secondary namenodes [slave1]
slave1: starting secondarynamenode, logging to /opt/soft/hadoop/logs/hadoop-hadoop-secondarynamenode-slave1.out
starting yarn daemons
starting resourcemanager, logging to /opt/soft/hadoop/logs/yarn-hadoop-resourcemanager-master.out
slave1: starting nodemanager, logging to /opt/soft/hadoop/logs/yarn-hadoop-nodemanager-slave1.out
slave2: starting nodemanager, logging to /opt/soft/hadoop/logs/yarn-hadoop-nodemanager-slave2.out
master: starting nodemanager, logging to /opt/soft/hadoop/logs/yarn-hadoop-nodemanager-master.out
[hadoop@master ~]$ jps
18690 NameNode
19587 Jps
19124 ResourceManager
19240 NodeManager
18842 DataNode
启动spark服务进程
[hadoop@master ~]$ /opt/soft/spark/sbin/start-all.sh
starting org.apache.spark.deploy.master.Master, logging to /opt/soft/spark/logs/spark-hadoop-org.apache.spark.deploy.master.Master-1-master.out
slave2: starting org.apache.spark.deploy.worker.Worker, logging to /opt/soft/spark/logs/spark-hadoop-org.apache.spark.deploy.worker.Worker-1-slave2.out
slave1: starting org.apache.spark.deploy.worker.Worker, logging to /opt/soft/spark/logs/spark-hadoop-org.apache.spark.deploy.worker.Worker-1-slave1.out
[hadoop@master ~]$ jps
18690 NameNode
19124 ResourceManager
19702 Jps
19240 NodeManager
18842 DataNode
19629 Master
[hadoop@master ~]$ jps -l -m
18690 org.apache.hadoop.hdfs.server.namenode.NameNode
19124 org.apache.hadoop.yarn.server.resourcemanager.ResourceManager
19240 org.apache.hadoop.yarn.server.nodemanager.NodeManager
18842 org.apache.hadoop.hdfs.server.datanode.DataNode
19723 sun.tools.jps.Jps -l -m
19629 org.apache.spark.deploy.master.Master --host master --port 7077 --webui-port 8080
[hadoop@master ~]$ hadoop fs -ls /
Found 14 items
-rw-r--r--   3 hadoop supergroup        148 2020-12-02 21:30 /HelloWorld.java
drwxrwx---   - hadoop supergroup          0 2021-02-23 10:47 /data
-rw-r--r--   3 hadoop supergroup         71 2020-11-25 08:45 /datasd
drwxr-xr-x   - hadoop supergroup          0 2020-07-08 17:16 /dataset
-rw-r--r--   3 hadoop supergroup        263 2020-07-08 13:43 /employee.json
drwxr-xr-x   - hadoop supergroup          0 2021-02-22 14:39 /hive
-rw-r--r--   3 hadoop supergroup         71 2020-11-25 11:53 /ssld
-rw-r--r--   3 hadoop supergroup         20 2020-11-30 09:31 /test.txt
drwxrwx---   - hadoop supergroup          0 2020-07-16 10:11 /tmp
drwxr-xr-x   - hadoop supergroup          0 2021-02-20 15:01 /user
-rw-r--r--   3 hadoop supergroup         71 2020-07-14 15:07 /usr
[hadoop@master ~]$ vi /opt/soft/hadoop/etc/hadoop/core-site.xml
[hadoop@master ~]$ which start-all.sh
/opt/soft/hadoop/sbin/start-all.sh
[hadoop@master ~]$
启动Hive服务进程
[hadoop@slave1 ~]$ hive --service metastore &
[hadoop@slave1 ~]$ hiveserver2 &

使用Beeline测试
[hadoop@slave1 ~]$ beeline
Beeline version 1.2.1.spark2 by Apache Hive
beeline> ?
!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 ???
beeline> !connect jdbc:hive2://slave1:10000
Connecting to jdbc:hive2://slave1:10000
Enter username for jdbc:hive2://slave1:10000: hadoop                       【操作系统hdfs用户】
Enter password for jdbc:hive2://slave1:10000: ********                      【操作系统密码】
21/02/25 12:50:56 INFO jdbc.Utils: Supplied authorities: slave1:10000
21/02/25 12:50:56 INFO jdbc.Utils: Resolved authority: slave1:10000
21/02/25 12:50:56 INFO jdbc.HiveConnection: Will try to open client transport with JDBC Uri: jdbc:hive2://slave1:10000
Connected to: Apache Hive (version 2.3.8)
Driver: Hive JDBC (version 1.2.1.spark2)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://slave1:10000> show databases;
OK
+----------------+--+
| database_name  |
+----------------+--+
| default        |
+----------------+--+
2 rows selected (1.623 seconds)
0: jdbc:hive2://slave1:10000>