1. Home
  2. Hadoop
  3. Hive：简单查询不启用Mapreduce job而启用Fetch task

Hive：简单查询不启用Mapreduce job而启用Fetch task

如果你想查询某个表的某一列，Hive默认是会启用MapReduce Job来完成这个任务，如下：

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
hive> SELECT id, money FROM m limit 10;
Total MapReduce jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Cannot run job locally: Input Size (= 235105473) is larger than
hive.exec.mode.local.auto.inputbytes.max (= 134217728)
Starting Job = job_1384246387966_0229, Tracking URL =
http://l-datalogm1.data.cn1:9981/proxy/application_1384246387966_0229/
Kill Command = /home/q/hadoop-2.2.0/bin/hadoop job 
-kill job_1384246387966_0229
hadoop job information for Stage-1: number of mappers: 1;
number of reducers: 0
2013-11-13 11:35:16,167 Stage-1 map = 0%,  reduce = 0%
2013-11-13 11:35:21,327 Stage-1 map = 100%,  reduce = 0%,
 Cumulative CPU 1.26 sec
2013-11-13 11:35:22,377 Stage-1 map = 100%,  reduce = 0%,
 Cumulative CPU 1.26 sec
MapReduce Total cumulative CPU time: 1 seconds 260 msec
Ended Job = job_1384246387966_0229
MapReduce Jobs Launched:
Job 0: Map: 1   Cumulative CPU: 1.26 sec  
HDFS Read: 8388865 HDFS Write: 60 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 260 msec
OK
1       122
1       185
1       231
1       292
1       316
1       329
1       355
1       356
1       362
1       364
Time taken: 16.802 seconds, Fetched: 10 row(s)

我们都知道，启用MapReduce Job是会消耗系统开销的。对于这个问题，从Hive0.10.0版本开始，对于简单的不需要聚合的类似SELECT <col> from <table> LIMIT n语句，不需要起MapReduce job，直接通过Fetch task获取数据，可以通过下面几种方法实现：
方法一：

01
02
03
04
05
06
07
08
09
10
11
12
13
14
hive> set hive.fetch.task.conversion=more;
hive> SELECT id, money FROM m limit 10;
OK
1       122
1       185
1       231
1       292
1       316
1       329
1       355
1       356
1       362
1       364
Time taken: 0.138 seconds, Fetched: 10 row(s)

上面 set hive.fetch.task.conversion=more;开启了Fetch任务，所以对于上述简单的列查询不在启用MapReduce job！
方法二：

1
bin/hive --hiveconf hive.fetch.task.conversion=more

方法三：
上面的两种方法都可以开启了Fetch任务，但是都是临时起作用的；如果你想一直启用这个功能，可以在${HIVE_HOME}/conf/hive-site.xml里面加入以下配置：

01
02
03
04
05
06
07
08
09
10
11
12
13
<property>
  <name>hive.fetch.task.conversion</name>
  <value>more</value>
  <description>
    Some select queries can be converted to single FETCH task
    minimizing latency.Currently the query should be single
    sourced not having any subquery and should not have
    any aggregations or distincts (which incurrs RS),
    lateral views and joins.
    1. minimal : SELECT STAR, FILTER on partition columns, LIMIT only
    2. more    : SELECT, FILTER, LIMIT only (+TABLESAMPLE, virtual columns)
  </description>
</property>

这样就可以长期启用Fetch任务了，很不错吧，也赶紧去试试吧！

 

hive.fetch.task.conversion
  • Default Value: minimal in Hive 0.10.0 through 0.13.1, more in Hive 0.14.0 and later
  • Added In: Hive 0.10.0 with HIVE-2925; default changed in Hive 0.14.0 with HIVE-7397

Some select queries can be converted to a single FETCH task, minimizing latency. Currently the query should be single sourced not having any subquery and should not have any aggregations or distincts (which incur RS – ReduceSinkOperator, requiring a MapReduce task), lateral views and joins.

Supported values are none, minimal and more.

0. none:  Disable hive.fetch.task.conversion (value added in Hive 0.14.0 with HIVE-8389)
1. minimal:  SELECT *, FILTER on partition columns (WHERE and HAVING clauses), LIMIT only
2. more:  SELECT, FILTER, LIMIT only (including TABLESAMPLE, virtual columns)

“more” can take any kind of expressions in the SELECT clause, including UDFs.
(UDTFs and lateral views are not yet supported – see HIVE-5718.)

15 Responses so far.

  1. 易路营销软件 says:
    January 13, 2016 at 2:58 am
    到此一游!
    Reply
  2. quest bars says:
    February 8, 2016 at 11:34 pm
    This is a really good tip particularly to those new to the blogosphere.
    Short but very accurate info… Thanks for sharing this one.
    A must read article!
    Reply
  3. quest bars says:
    February 9, 2016 at 9:23 pm
    I am actually happy to read this weblog posts which carries plenty of valuable information,
    thanks for providing these data.
    Reply
  4. minecraft.exe says:
    February 21, 2016 at 4:37 pm
    Thanks for the marvelous posting! I seriously enjoyed reading it, you could be a great author.
    I will make certain to bookmark your blog and will eventually come back down the road.

    I want to encourage you to definitely continue your
    great posts, have a nice holiday weekend!

    Reply
  5. minecraft.exe says:
    February 22, 2016 at 6:26 am
    Hi! Quick question that’s totally off topic.
    Do you know how to make your site mobile friendly? My site looks weird
    when viewing from my iphone 4. I’m trying to find a theme or plugin that might be able
    to resolve this problem. If you have any recommendations, please share.
    Thanks!
    Reply
  6. plenty of fish dating site of free dating says:
    February 23, 2016 at 2:26 pm
    I always spent my half an hour to read this weblog’s content everyday along with a cup of coffee.
    Reply
  7. Plenty of fish dating site of free dating says:
    February 23, 2016 at 6:35 pm
    Thanks for sharing your thoughts on plenty of fish dating site of free dating.
    Regards
    Reply
  8. Limewire Free Music Downloads says:
    March 7, 2016 at 6:50 am
    My relatives always say that I am wasting my time here at web, but I know
    I am getting familiarity daily by reading such fastidious articles or reviews.
    Reply
  9. Limewire Free Music Downloads says:
    March 8, 2016 at 4:40 am
    Appreciate this post. Will try it out.
    Reply
  10. Limewire Free Music Downloads says:
    March 8, 2016 at 10:27 am
    Informative article, just what I wanted to find.
    Reply
  11. bernie sanders says:
    March 31, 2016 at 5:17 am
    Please let me know if you’re looking for a article writer for your blog.
    You have some really great articles and I believe I would be a good asset.
    If you ever want to take some of the load off, I’d absolutely love to write some material for
    your blog in exchange for a link back to mine. Please shoot me an email
    if interested. Regards!
    Reply
  12. bernie sanders says:
    March 31, 2016 at 7:42 am
    I do not even know how I stopped up right here, but I believed this publish used to
    be good. I don’t recognise who you’re however certainly you’re going
    to a well-known blogger for those who are not already.

    Cheers!

    Reply
  13. bernie sanders says:
    March 31, 2016 at 9:29 pm
    With havin so much content and articles do you ever run into any problems of plagorism or copyright violation? My blog has
    a lot of completely unique content I’ve either authored myself or outsourced but it appears a lot
    of it is popping it up all over the internet without my agreement.
    Do you know any ways to help protect against content from being ripped off?
    I’d certainly appreciate it.
    Reply
  14. bernie sanders says:
    April 1, 2016 at 12:03 pm
    Hello, i think that i saw you visited my site so
    i came to “return the favor”.I am attempting to find things to enhance my site!I
    suppose its ok to use a few of your ideas!!
    Reply
  15. bernie sanders says:
    April 1, 2016 at 10:23 pm
    Howdy exceptional blog! Does running a blog like this require a large amount of work?
    I have very little expertise in coding however I had been hoping to start my own blog soon. Anyways, should you have any recommendations or tips for new blog owners please share.
    I understand this is off topic however I just needed to ask.
    Many thanks!
    Reply
Leave a Comment

LEAVE A COMMENT