一, 测试语句
语句一:
select count(distinct order_id) ,count(1) from d_common_wlt_info
语句二:
1 select count(order_id), count(one_num) 2 from 3 ( 4 select order_id ,count(1) one_num 5 from d_common_wlt_info 6 group by order_id 7 )t
二, 执行日志及表说明
表大小信息
Partition Parameters: COLUMN_STATS_ACCURATE true numFiles 9 numRows 28176219 rawDataSize 8785300782 totalSize 1820024671 transient_lastDdlTime 1551631895
(1)语句一日志
Hadoop job information for Stage-1: number of mappers: 3; number of reducers: 1 2019-03-04 13:10:24,719 Stage-1 map = 0%, reduce = 0% 2019-03-04 13:10:39,289 Stage-1 map = 33%, reduce = 0%, Cumulative CPU 23.87 sec 2019-03-04 13:10:58,984 Stage-1 map = 58%, reduce = 0%, Cumulative CPU 76.04 sec 2019-03-04 13:11:39,220 Stage-1 map = 68%, reduce = 0%, Cumulative CPU 191.24 sec 2019-03-04 13:11:50,532 Stage-1 map = 78%, reduce = 0%, Cumulative CPU 212.97 sec 2019-03-04 13:11:56,726 Stage-1 map = 89%, reduce = 0%, Cumulative CPU 228.88 sec 2019-03-04 13:12:06,036 Stage-1 map = 91%, reduce = 0%, Cumulative CPU 247.36 sec 2019-03-04 13:12:09,118 Stage-1 map = 98%, reduce = 0%, Cumulative CPU 250.66 sec 2019-03-04 13:12:12,205 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 253.8 sec 2019-03-04 13:12:34,851 Stage-1 map = 100%, reduce = 68%, Cumulative CPU 274.25 sec 2019-03-04 13:12:37,942 Stage-1 map = 100%, reduce = 70%, Cumulative CPU 277.76 sec 2019-03-04 13:12:41,023 Stage-1 map = 100%, reduce = 73%, Cumulative CPU 280.93 sec 2019-03-04 13:12:44,103 Stage-1 map = 100%, reduce = 75%, Cumulative CPU 284.14 sec 2019-03-04 13:12:47,185 Stage-1 map = 100%, reduce = 77%, Cumulative CPU 287.42 sec 2019-03-04 13:12:50,267 Stage-1 map = 100%, reduce = 80%, Cumulative CPU 290.65 sec 2019-03-04 13:12:53,353 Stage-1 map = 100%, reduce = 82%, Cumulative CPU 293.78 sec 2019-03-04 13:12:56,432 Stage-1 map = 100%, reduce = 84%, Cumulative CPU 296.95 sec 2019-03-04 13:12:59,547 Stage-1 map = 100%, reduce = 86%, Cumulative CPU 300.13 sec 2019-03-04 13:13:02,639 Stage-1 map = 100%, reduce = 88%, Cumulative CPU 303.33 sec 2019-03-04 13:13:05,728 Stage-1 map = 100%, reduce = 91%, Cumulative CPU 306.51 sec 2019-03-04 13:13:08,837 Stage-1 map = 100%, reduce = 93%, Cumulative CPU 309.67 sec 2019-03-04 13:13:11,917 Stage-1 map = 100%, reduce = 95%, Cumulative CPU 312.88 sec 2019-03-04 13:13:14,996 Stage-1 map = 100%, reduce = 97%, Cumulative CPU 315.99 sec 2019-03-04 13:13:17,052 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 318.29 sec MapReduce Total cumulative CPU time: 5 minutes 18 seconds 290 msec Ended Job = job_1546585330012_16354054 MapReduce Jobs Launched: Stage-Stage-1: Map: 3 Reduce: 1 Cumulative CPU: 318.29 sec HDFS Read: 805206007 HDFS Write: 68 SUCCESS Total MapReduce CPU Time Spent: 5 minutes 18 seconds 290 msec OK 21205531 27866943
Time taken: 218.373 seconds, Fetched: 1 row(s)
(2) 语句二日志
Hadoop job information for Stage-1: number of mappers: 3; number of reducers: 1 2019-03-04 13:12:08,331 Stage-1 map = 0%, reduce = 0% 2019-03-04 13:12:19,715 Stage-1 map = 33%, reduce = 0%, Cumulative CPU 6.48 sec 2019-03-04 13:12:42,875 Stage-1 map = 46%, reduce = 0%, Cumulative CPU 69.34 sec 2019-03-04 13:12:43,906 Stage-1 map = 58%, reduce = 0%, Cumulative CPU 72.54 sec 2019-03-04 13:13:16,847 Stage-1 map = 68%, reduce = 0%, Cumulative CPU 162.12 sec 2019-03-04 13:13:19,930 Stage-1 map = 79%, reduce = 0%, Cumulative CPU 168.67 sec 2019-03-04 13:13:23,060 Stage-1 map = 89%, reduce = 0%, Cumulative CPU 174.94 sec 2019-03-04 13:13:32,311 Stage-1 map = 97%, reduce = 0%, Cumulative CPU 192.25 sec 2019-03-04 13:13:35,397 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 195.41 sec 2019-03-04 13:13:54,977 Stage-1 map = 100%, reduce = 22%, Cumulative CPU 204.97 sec 2019-03-04 13:13:58,058 Stage-1 map = 100%, reduce = 67%, Cumulative CPU 210.04 sec 2019-03-04 13:14:01,135 Stage-1 map = 100%, reduce = 69%, Cumulative CPU 213.98 sec 2019-03-04 13:14:04,236 Stage-1 map = 100%, reduce = 71%, Cumulative CPU 217.42 sec 2019-03-04 13:14:07,345 Stage-1 map = 100%, reduce = 74%, Cumulative CPU 220.64 sec 2019-03-04 13:14:10,432 Stage-1 map = 100%, reduce = 77%, Cumulative CPU 223.88 sec 2019-03-04 13:14:13,518 Stage-1 map = 100%, reduce = 79%, Cumulative CPU 227.09 sec 2019-03-04 13:14:16,602 Stage-1 map = 100%, reduce = 81%, Cumulative CPU 230.22 sec 2019-03-04 13:14:19,678 Stage-1 map = 100%, reduce = 83%, Cumulative CPU 233.48 sec 2019-03-04 13:14:22,759 Stage-1 map = 100%, reduce = 85%, Cumulative CPU 236.69 sec 2019-03-04 13:14:25,841 Stage-1 map = 100%, reduce = 87%, Cumulative CPU 239.9 sec 2019-03-04 13:14:28,920 Stage-1 map = 100%, reduce = 89%, Cumulative CPU 242.94 sec 2019-03-04 13:14:32,011 Stage-1 map = 100%, reduce = 91%, Cumulative CPU 246.05 sec 2019-03-04 13:14:35,103 Stage-1 map = 100%, reduce = 93%, Cumulative CPU 249.11 sec 2019-03-04 13:14:38,181 Stage-1 map = 100%, reduce = 95%, Cumulative CPU 252.12 sec 2019-03-04 13:14:41,261 Stage-1 map = 100%, reduce = 97%, Cumulative CPU 255.26 sec 2019-03-04 13:14:43,312 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 256.88 sec MapReduce Total cumulative CPU time: 4 minutes 16 seconds 880 msec Ended Job = job_1546585330012_16354401 Launching Job 2 out of 2 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_1546585330012_16354794, Tracking URL = http://tjtx-81-187.58os.org:9088/proxy/application_1546585330012_16354794/ Kill Command = /usr/lib/software/hadoop/bin/hadoop job -kill job_1546585330012_16354794 Hadoop job information for Stage-2: number of mappers: 1; number of reducers: 1 2019-03-04 13:15:10,988 Stage-2 map = 0%, reduce = 0% 2019-03-04 13:15:21,338 Stage-2 map = 100%, reduce = 0%, Cumulative CPU 2.15 sec 2019-03-04 13:15:30,634 Stage-2 map = 100%, reduce = 100%, Cumulative CPU 4.56 sec MapReduce Total cumulative CPU time: 4 seconds 560 msec Ended Job = job_1546585330012_16354794 MapReduce Jobs Launched: Stage-Stage-1: Map: 3 Reduce: 1 Cumulative CPU: 256.88 sec HDFS Read: 805205212 HDFS Write: 123 SUCCESS Stage-Stage-2: Map: 1 Reduce: 1 Cumulative CPU: 4.56 sec HDFS Read: 5330 HDFS Write: 68 SUCCESS Total MapReduce CPU Time Spent: 4 minutes 21 seconds 440 msec OK 21205531 21205532 Time taken: 225.271 seconds, Fetched: 1 row(s)
三, 总结
在测试使用的集群中2000w的数据量级在效率上差别不大.
为什么慎用呢? 因为hive的distinct底层使用了HashSet去重.
即然效率上差不多,还是能不用就不用的好.
--