Hive Impala Count 效率比对

Hive 主要是跑批处理的，Impala主要是做准实时的内存查询，我拿他俩做比较～～感觉我好没意思阿～～

一、count 比较

十万数量级

Imapal

[cdh-node2:21000] > select count(1) from userinfo;Query: select count(1) from userinfo+----------+| count(1) |+----------+| 124850   |+----------+Fetched 1 row(s) in 2.39s[cdh-node2:21000] > select count(1) from userinfo;Query: select count(1) from userinfo+----------+| count(1) |+----------+| 124850   |+----------+Fetched 1 row(s) in 0.57s[cdh-node2:21000] > 

Hive

    > select count(1) from userinfo;Total jobs = 1Launching Job 1 out of 1Number of reduce tasks determined at compile time: 1In 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_1422624309453_0060, Tracking URL = http://cdh-node1:8088/proxy/application_1422624309453_0060/Kill Command = /opt/cloudera/parcels/CDH-5.3.0-1.cdh5.3.0.p0.30/lib/hadoop/bin/hadoop job  -kill job_1422624309453_0060Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 12015-01-31 18:06:57,974 Stage-1 map = 0%,  reduce = 0%2015-01-31 18:07:06,297 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 3.33 sec2015-01-31 18:07:15,518 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 5.74 secMapReduce Total cumulative CPU time: 5 seconds 740 msecEnded Job = job_1422624309453_0060MapReduce Jobs Launched: Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 5.74 sec   HDFS Read: 27936154 HDFS Write: 7 SUCCESSTotal MapReduce CPU Time Spent: 5 seconds 740 msecOK124850Time taken: 32.968 seconds, Fetched: 1 row(s)hive> 

百万数量级

Impala

[cdh-node2:21000] > select count(1) from softuser where statdate='2015-01-30';Query: select count(1) from softuser where statdate='2015-01-30'+----------+| count(1) |+----------+| 1550308  |+----------+Fetched 1 row(s) in 1.81s[cdh-node2:21000] > 

Hive

hive> select count(1) from softuser where statdate='2015-01-30';Total jobs = 1Launching Job 1 out of 1Number of reduce tasks determined at compile time: 1In 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_1422624309453_0061, Tracking URL = http://cdh-node1:8088/proxy/application_1422624309453_0061/Kill Command = /opt/cloudera/parcels/CDH-5.3.0-1.cdh5.3.0.p0.30/lib/hadoop/bin/hadoop job  -kill job_1422624309453_0061Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 12015-01-31 18:13:33,047 Stage-1 map = 0%,  reduce = 0%2015-01-31 18:13:43,250 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 4.88 sec2015-01-31 18:13:54,495 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 6.82 secMapReduce Total cumulative CPU time: 6 seconds 820 msecEnded Job = job_1422624309453_0061MapReduce Jobs Launched: Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 6.82 sec   HDFS Read: 253135783 HDFS Write: 8 SUCCESSTotal MapReduce CPU Time Spent: 6 seconds 820 msecOK1550308Time taken: 34.27 seconds, Fetched: 1 row(s)hive> 

千万数量级

Impala

[cdh-node2:21000] > select count(1) from base_bluesoft where statdate='2015-01-30';Query: select count(1) from base_bluesoft where statdate='2015-01-30'+----------+| count(1) |+----------+| 21796907 |+----------+Fetched 1 row(s) in 1.81s[cdh-node2:21000] > 

Hive

hive> select count(1) from base_bluesoft where statdate='2015-01-30';Total jobs = 1Launching Job 1 out of 1Number of reduce tasks determined at compile time: 1In 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_1422624309453_0062, Tracking URL = http://cdh-node1:8088/proxy/application_1422624309453_0062/Kill Command = /opt/cloudera/parcels/CDH-5.3.0-1.cdh5.3.0.p0.30/lib/hadoop/bin/hadoop job  -kill job_1422624309453_0062Hadoop job information for Stage-1: number of mappers: 13; number of reducers: 12015-01-31 18:15:46,294 Stage-1 map = 0%,  reduce = 0%2015-01-31 18:15:50,395 Stage-1 map = 8%,  reduce = 0%, Cumulative CPU 1.28 sec2015-01-31 18:15:57,548 Stage-1 map = 11%,  reduce = 0%, Cumulative CPU 6.91 sec2015-01-31 18:15:58,571 Stage-1 map = 15%,  reduce = 0%, Cumulative CPU 33.92 sec2015-01-31 18:15:59,595 Stage-1 map = 16%,  reduce = 0%, Cumulative CPU 46.56 sec2015-01-31 18:16:01,643 Stage-1 map = 27%,  reduce = 0%, Cumulative CPU 52.31 sec2015-01-31 18:16:02,665 Stage-1 map = 29%,  reduce = 0%, Cumulative CPU 64.81 sec2015-01-31 18:16:03,687 Stage-1 map = 38%,  reduce = 0%, Cumulative CPU 66.03 sec2015-01-31 18:16:04,710 Stage-1 map = 46%,  reduce = 0%, Cumulative CPU 67.7 sec2015-01-31 18:16:05,732 Stage-1 map = 64%,  reduce = 0%, Cumulative CPU 71.47 sec2015-01-31 18:16:06,753 Stage-1 map = 73%,  reduce = 0%, Cumulative CPU 72.45 sec2015-01-31 18:16:07,776 Stage-1 map = 78%,  reduce = 0%, Cumulative CPU 73.2 sec2015-01-31 18:16:08,798 Stage-1 map = 82%,  reduce = 0%, Cumulative CPU 74.25 sec2015-01-31 18:16:11,861 Stage-1 map = 83%,  reduce = 0%, Cumulative CPU 74.64 sec2015-01-31 18:16:14,927 Stage-1 map = 95%,  reduce = 0%, Cumulative CPU 76.13 sec2015-01-31 18:16:15,948 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 76.56 sec2015-01-31 18:16:24,114 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 78.68 secMapReduce Total cumulative CPU time: 1 minutes 18 seconds 680 msecEnded Job = job_1422624309453_0062MapReduce Jobs Launched: Stage-Stage-1: Map: 13  Reduce: 1   Cumulative CPU: 78.68 sec   HDFS Read: 3638967781 HDFS Write: 9 SUCCESSTotal MapReduce CPU Time Spent: 1 minutes 18 seconds 680 msecOK21796907Time taken: 51.858 seconds, Fetched: 1 row(s)hive> 

亿量级

Impala

[cdh-node2:21000] > select count(1) from base_bda;Query: select count(1) from base_bda+-----------+| count(1)  |+-----------+| 192308367 |+-----------+Fetched 1 row(s) in 186.77s[cdh-node2:21000] > 

Hive

MapReduce Total cumulative CPU time: 13 minutes 58 seconds 340 msecEnded Job = job_1422624309453_0063MapReduce Jobs Launched: Stage-Stage-1: Map: 120  Reduce: 1   Cumulative CPU: 838.34 sec   HDFS Read: 33852466492 HDFS Write: 10 SUCCESSTotal MapReduce CPU Time Spent: 13 minutes 58 seconds 340 msecOK192308367Time taken: 252.212 seconds, Fetched: 1 row(s)hive>