hive条件查询,起完job就卡住不动了,也不报错,日志也没问题,

最新推荐文章于 2023-01-22 11:58:10 发布

小涛手记

最新推荐文章于 2023-01-22 11:58:10 发布

阅读量1k

点赞数

分类专栏： hive 文章标签： hive hadoop mapreduce yarn

本文链接：https://blog.csdn.net/weixin_47699191/article/details/116756939

版权

hive 专栏收录该内容

28 篇文章 0 订阅

订阅专栏

hive简单查询没有问题

0: jdbc:hive2://wxt01:10000>  select * from `user`;
OK
+------------+----------------------+--+
| user.guid  |       user.dt        |
+------------+----------------------+--+
| zhangsang  | 2020-03-01 11:32:22  |
| zhangsang  | 2020-03-02 11:32:22  |
| zhangsang  | 2020-03-03 11:32:22  |
| zhangsang  | 2020-03-04 11:32:22  |
| zhangsang  | 2020-03-05 11:32:22  |
| zhangsang  | 2020-03-06 11:32:22  |
| zhangsang  | 2020-03-07 11:32:22  |
| zhangsang  | 2020-03-08 11:32:22  |
| zhangsang  | 2020-03-09 11:32:22  |
| zhangsang  | 2020-03-11 11:32:22  |
| zhangsang  | 2020-03-12 11:32:22  |
| zhangsang  | 2020-03-13 11:32:22  |
| zhangsang  | 2020-03-14 11:32:22  |
| zhangsang  | 2020-03-15 11:32:22  |
| zhangsang  | 2020-03-16 11:32:22  |
| zhangsang  | 2020-03-17 11:32:22  |
| zhangsang  | 2020-03-18 11:32:22  |
| zhangsang  | 2020-03-19 11:32:22  |
| zhangsang  | 2020-03-21 11:32:22  |
| zhangsang  | 2020-03-22 11:32:22  |
| zhangsang  | 2020-03-23 11:32:22  |
| zhangsang  | 2020-03-24 11:32:22  |
| zhangsang  | 2020-03-25 11:32:22  |
| zhangsang  | 2020-03-26 11:32:22  |
| zhangsang  | 2020-03-27 11:32:22  |
| zhangsang  | 2020-03-28 11:32:22  |
| zhangsang  | 2020-03-29 11:32:22  |
| zhangsang  | 2020-03-30 11:32:22  |
| zhangsang  | 2020-03-10 11:32:22  |
| zhangsang  | 2020-03-20 11:32:22  |
| zhangsang  | 2020-03-31 11:32:22  |
| lisang     | 2020-03-01 11:32:22  |
| lisang     | 2020-03-02 11:32:22  |
| lisang     | 2020-03-03 11:32:22  |
| lisang     | 2020-03-04 11:32:22  |
| lisang     | 2020-03-07 11:32:22  |
| lisang     | 2020-03-08 11:32:22  |
| lisang     | 2020-03-09 11:32:22  |
| lisang     | 2020-03-11 11:32:22  |
| lisang     | 2020-03-12 11:32:22  |
| lisang     | 2020-03-13 11:32:22  |
| lisang     | 2020-03-14 11:32:22  |
| lisang     | 2020-03-16 11:32:22  |
| lisang     | 2020-03-21 11:32:22  |
| lisang     | 2020-03-22 11:32:22  |
+------------+----------------------+--+
45 rows selected (0.67 seconds)
0: jdbc:hive2://wxt01:10000>

复杂查询起完job就卡住不动了

0: jdbc:hive2://wxt01:10000> select count(1) from `user` group by guid;
WARNING: 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.
Query ID = root_20210513160435_199421d5-2934-4181-b7fc-515be5f7691b
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks not specified. Estimated from input data size: 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>
WARNING: 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.
Starting Job = job_1620889648372_0004, Tracking URL = http://wxt01:8088/proxy/application_1620889648372_0004/
Kill Command = /usr/app/hadoop-2.8.5//bin/hadoop job  -kill job_1620889648372_0004

yarn没问题

[root@wxt01 logs]# yarn application -list
21/05/13 16:15:18 INFO client.RMProxy: Connecting to ResourceManager at wxt01/192.168.133.3:8032
Total number of applications (application-types: [] and states: [SUBMITTED, ACCEPTED, RUNNING]):1
                Application-Id      Application-Name        Application-Type          User           Queue                   State             Final-State             Progress                        Tracking-URL
application_1620889648372_0004  select count(1) from `user` group by guid(Stage-1)                   MAPREDUCE          root         default                ACCEPTED               UNDEFINE             0%                                  N/A

检查hive的日志也没有出错

[root@wxt01 logs]# tail -f hive.log
2021-05-13T16:50:07,581  INFO [HiveServer2-Handler-Pool: Thread-41] session.SessionState: Resetting thread name to  HiveServer2-Handler-Pool: Thread-41
2021-05-13T16:50:07,581  INFO [HiveServer2-Handler-Pool: Thread-41] conf.HiveConf: Using the default value passed in for log id: d8e22533-d07d-4b00-ab13-bf4a6920770b
2021-05-13T16:50:07,582  INFO [HiveServer2-Handler-Pool: Thread-41] session.SessionState: Updating thread name to d8e22533-d07d-4b00-ab13-bf4a6920770b HiveServer2-Handler-Pool: Thread-41
2021-05-13T16:50:07,582  INFO [d8e22533-d07d-4b00-ab13-bf4a6920770b HiveServer2-Handler-Pool: Thread-41] conf.HiveConf: Using the default value passed in for log id: d8e22533-d07d-4b00-ab13-bf4a6920770b
2021-05-13T16:50:07,582  INFO [HiveServer2-Handler-Pool: Thread-41] session.SessionState: Resetting thread name to  HiveServer2-Handler-Pool: Thread-41
2021-05-13T16:50:07,582  INFO [HiveServer2-Handler-Pool: Thread-41] conf.HiveConf: Using the default value passed in for log id: d8e22533-d07d-4b00-ab13-bf4a6920770b
2021-05-13T16:50:07,582  INFO [HiveServer2-Handler-Pool: Thread-41] session.SessionState: Updating thread name to d8e22533-d07d-4b00-ab13-bf4a6920770b HiveServer2-Handler-Pool: Thread-41
2021-05-13T16:50:07,582  INFO [d8e22533-d07d-4b00-ab13-bf4a6920770b HiveServer2-Handler-Pool: Thread-41] operation.OperationManager: Closing operation: OperationHandle [opType=EXECUTE_STATEMENT, getHandleIdentifier()=6900a63e-9582-4cd4-8251-2e3affd3fb5a]
2021-05-13T16:50:07,587  INFO [d8e22533-d07d-4b00-ab13-bf4a6920770b HiveServer2-Handler-Pool: Thread-41] conf.HiveConf: Using the default value passed in for log id: d8e22533-d07d-4b00-ab13-bf4a6920770b
2021-05-13T16:50:07,587  INFO [HiveServer2-Handler-Pool: Thread-41] session.SessionState: Resetting thread name to  HiveServer2-Handler-Pool: Thread-41

查看集群内存充足

[root@wxt01 logs]# free
             total       used       free     shared    buffers     cached
Mem:       1906432    1776696     129736          4       1324      72972
-/+ buffers/cache:    1702400     204032
Swap:      2097148     157260    1939888

重启集群解决，然后就可以了

0: jdbc:hive2://wxt01:10000> select
0: jdbc:hive2://wxt01:10000> guid
0: jdbc:hive2://wxt01:10000> from
0: jdbc:hive2://wxt01:10000> (
0: jdbc:hive2://wxt01:10000> select
0: jdbc:hive2://wxt01:10000> guid,
0: jdbc:hive2://wxt01:10000> n,
0: jdbc:hive2://wxt01:10000> count(1) as days
0: jdbc:hive2://wxt01:10000> from
0: jdbc:hive2://wxt01:10000> (
0: jdbc:hive2://wxt01:10000> select
0: jdbc:hive2://wxt01:10000> guid,
0: jdbc:hive2://wxt01:10000> date_sub(dt,rn) as n
0: jdbc:hive2://wxt01:10000> from
0: jdbc:hive2://wxt01:10000> (
0: jdbc:hive2://wxt01:10000> select
0: jdbc:hive2://wxt01:10000> guid,
0: jdbc:hive2://wxt01:10000> dt,
0: jdbc:hive2://wxt01:10000> row_number() over(partition by guid order by dt) rn
0: jdbc:hive2://wxt01:10000> from
0: jdbc:hive2://wxt01:10000> (
0: jdbc:hive2://wxt01:10000> select
0: jdbc:hive2://wxt01:10000> guid,
0: jdbc:hive2://wxt01:10000> date_format(dt,'Y-MM-dd') AS dt
0: jdbc:hive2://wxt01:10000> from `user` where date_format(dt,'Y-MM-dd') between '2020-03-01' and '2020-03-31'
0: jdbc:hive2://wxt01:10000> ) t
0: jdbc:hive2://wxt01:10000> group by guid,dt
0: jdbc:hive2://wxt01:10000> ) o
0: jdbc:hive2://wxt01:10000> ) tmp
0: jdbc:hive2://wxt01:10000> group by guid,n
0: jdbc:hive2://wxt01:10000> ) l
0: jdbc:hive2://wxt01:10000> where days='31'
0: jdbc:hive2://wxt01:10000> ;
WARNING: 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.
Query ID = root_20210513164925_82784334-28a1-4fe9-8953-4b9c2b7fe6f8
Total jobs = 2
Launching Job 1 out of 2
Number of reduce tasks not specified. Estimated from input data size: 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>
WARNING: 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.
Starting Job = job_1620895554382_0001, Tracking URL = http://wxt01:8088/proxy/application_1620895554382_0001/
Kill Command = /usr/app/hadoop-2.8.5//bin/hadoop job  -kill job_1620895554382_0001
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2021-05-13 16:49:32,251 Stage-1 map = 0%,  reduce = 0%
2021-05-13 16:49:37,494 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 2.54 sec
2021-05-13 16:49:42,620 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 4.78 sec
MapReduce Total cumulative CPU time: 4 seconds 780 msec
Ended Job = job_1620895554382_0001
Launching Job 2 out of 2
Number of reduce tasks not specified. Estimated from input data size: 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_1620895554382_0002, Tracking URL = http://wxt01:8088/proxy/application_1620895554382_0002/
Kill Command = /usr/app/hadoop-2.8.5//bin/hadoop job  -kill job_1620895554382_0002
Hadoop job information for Stage-2: number of mappers: 1; number of reducers: 1
2021-05-13 16:49:55,237 Stage-2 map = 0%,  reduce = 0%
2021-05-13 16:50:00,366 Stage-2 map = 100%,  reduce = 0%, Cumulative CPU 0.75 sec
2021-05-13 16:50:06,512 Stage-2 map = 100%,  reduce = 100%, Cumulative CPU 2.97 sec
MapReduce Total cumulative CPU time: 2 seconds 970 msec
Ended Job = job_1620895554382_0002
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 4.78 sec   HDFS Read: 12470 HDFS Write: 267 SUCCESS
Stage-Stage-2: Map: 1  Reduce: 1   Cumulative CPU: 2.97 sec   HDFS Read: 6976 HDFS Write: 109 SUCCESS
Total MapReduce CPU Time Spent: 7 seconds 750 msec
OK
+------------+--+
|    guid    |
+------------+--+
| zhangsang  |
+------------+--+
1 row selected (42.376 seconds)
0: jdbc:hive2://wxt01:10000>

小涛手记

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
hive条件查询,起完job就卡住不动了,也不报错,日志也没问题,

hive简单查询没有问题0: jdbc:hive2://wxt01:10000> select * from `user`;OK+------------+----------------------+--+| user.guid | user.dt |+------------+----------------------+--+| zhangsang | 2020-03-01 11:32:22 || zhangsang | 2020-03-02 1
复制链接

扫一扫