在命令行中查询

添码座原创大约 4 分钟

使用hive命令

执行下面的命令开启Hive客户端。

> cd /home/work/hive-3.1.3
> ./bin/hive

/usr/bin/which: no hbase in (/root/.local/bin:/root/bin:.:/home/work/hadoop-3.2.0/bin:/home/work/hadoop-3.2.0/sbin:/usr/local/java/jdk1.8.0_401/bin:/usr/share/Modules/bin:/usr/local/bin:/usr/local/sbin:/usr/bin:/usr/sbin)
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/home/work/hive-3.1.3/lib/log4j-slf4j-impl-2.17.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/home/work/hadoop-3.2.0/share/hadoop/common/lib/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]

Hive Session ID = 86ca643b-ec64-4f1f-bd24-6405e64fe77a

Logging initialized using configuration in jar:file:/home/work/hive-3.1.3/lib/hive-common-3.1.3.jar!/hive-log4j2.properties Async: true
Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.Hive Session ID = d8f39576-5efc-49c7-9658-1cf0b3b3cdca

hive>

接下来，就可以通过输入SQL查询语句与Hive交互了。

创建数据表

hive> create table test(
          id int,
          name string
      );
OK
Time taken: 0.848 seconds

hive> show tables;
OK
test
Time taken: 0.176 seconds, Fetched: 1 row(s)

操作数据

hive> insert into test(id, name) values(1, "lixingyun");
Query ID = root_20230316185548_03120073-6d64-46ae-ad26-58d58871542f
Total jobs = 3
Launching Job 1 out of 3
Number of reduce tasks determined at compile time: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1720692377914_0001, Tracking URL = http://hadoop:8088/proxy/application_1720692377914_0001/
Kill Command = /home/work/hadoop-3.2.0/bin/mapred job  -kill job_1720692377914_0001
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2023-xx-xx 18:58:11,061 Stage-1 map = 0%,  reduce = 0%
2023-xx-xx 18:58:26,419 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 3.53 sec
2023-xx-xx 18:59:18,732 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 5.85 sec
MapReduce Total cumulative CPU time: 5 seconds 850 msec
Ended Job = job_1720692377914_0001
Stage-4 is selected by condition resolver.
Stage-3 is filtered out by condition resolver.
Stage-5 is filtered out by condition resolver.
Moving data to directory hdfs://hadoop:9000/user/hive/warehouse/test/.hive-staging_hive_2023-xx-xx_18-55-48_044_3810504924385202158-1/-ext-10000
Loading data to table default.test
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 5.85 sec   HDFS Read: 15185 HDFS Write: 243 SUCCESS
Total MapReduce CPU Time Spent: 5 seconds 850 msec
OK
Time taken: 18.031 seconds

hive> update test set name = "wanglin" where id = 1;
FAILED: SemanticException [Error 10294]: Attempt to do update or delete using transaction manager that does not support these operations.

可以看到，当操作Hive的时候，它在底层调用的就是MapReduce。

而且Hive并不支持更新或删除数据的操作。

删除数据表

hive> drop table test;
OK
Time taken: 0.418 seconds

hive> show tables;
OK
Time taken: 0.076 seconds

使用beeline命令

执行beeline命令，需要先执行hiveserver2命令。

> cd /home/work/hive-3.1.3
> ./bin/hiveserver2

/usr/bin/which: no hbase in (/root/.local/bin:/root/bin:.:/home/work/hadoop-3.2.0/bin:/home/work/hadoop-3.2.0/sbin:/usr/local/java/jdk1.8.0_401/bin:/usr/share/Modules/bin:/usr/local/bin:/usr/local/sbin:/usr/bin:/usr/sbin)
2023-03-16 19:52:40: Starting HiveServer2
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/home/work/hive-3.1.3/lib/log4j-slf4j-impl-2.17.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/home/work/hadoop-3.2.0/share/hadoop/common/lib/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Hive Session ID = a25cfea9-64d6-4342-9ec1-f4bc9b32905d
Hive Session ID = e676ff3b-7adf-4ce1-b1a2-ff8823771350

再打开另一个终端，然后启动beeline。

> cd /home/work/hive-3.1.3
> ./bin/beeline -u jdbc:hive2://localhost:10000

SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/home/work/hive-3.1.3/lib/log4j-slf4j-impl-2.17.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/home/work/hadoop-3.2.0/share/hadoop/common/lib/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Connecting to jdbc:hive2://localhost:10000
Connected to: Apache Hive (version 3.1.3)
Driver: Hive JDBC (version 3.1.3)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 3.1.3 by Apache Hive
0: jdbc:hive2://localhost:10000>

接下来，就可以通过输入SQL查询语句与Hive交互了。

创建数据表

0: jdbc:hive2://localhost:10000> create table test(id int, name string);
......
INFO  : OK
INFO  : Concurrency mode is disabled, not creating a lock manager
No rows affected (1.353 seconds)

0: jdbc:hive2://localhost:10000> show tables;
......
+-----------+
| tab_name  |
+-----------+
| test      |
+-----------+
1 row selected (1.648 seconds)

操作数据

0: jdbc:hive2://localhost:10000> insert into test(id, name) values(1, "lixingyun");
......
Job Submission failed with exception 'org.apache.hadoop.security.AccessControlException(Permission denied: user=anonymous, access=EXECUTE, inode="/tmp/hadoop-yarn":root:supergroup:drwx------

出现异常，退出beeline，重新指定用户后再试。

> cd /home/work/hive-3.1.3
> ./bin/beeline -u jdbc:hive2://localhost:10000 -n root

......
0: jdbc:hive2://localhost:10000>

再来执行插入数据的操作。

0: jdbc:hive2://localhost:10000> insert into test(id, name) values(1, "lixingyun");
......
INFO  : OK
INFO  : Concurrency mode is disabled, not creating a lock manager
No rows affected (12.047 seconds)

0: jdbc:hive2://localhost:10000> select * from test;
......
+----------+------------+
| test.id  | test.name  |
+----------+------------+
| 1        | lixingyun  |
+----------+------------+
1 row selected (1.473 seconds)

删除数据表

0: jdbc:hive2://localhost:10000> drop table test;
......
INFO  : OK
INFO  : Concurrency mode is disabled, not creating a lock manager
No rows affected (1.393 seconds)

0: jdbc:hive2://localhost:10000> show tables;
......
+-----------+
| tab_name  |
+-----------+
+-----------+
No rows selected (1.175 seconds)

指定IP连接

可以通过指定本机IP的方式连接beeline，这样就可以远程操作Hive。

> cd /home/work/hive-3.1.3
> ./bin/beeline -u jdbc:hive2://172.16.185.176:10000
......
0: jdbc:hive2://172.16.185.176:10000>

感谢支持

更多内容，请移步《超级个体》。