Page MenuHomePhabricator

Hive query fails with local join
Closed, ResolvedPublic

Description

It looks like this Hive query is failing because of an errant automatic local join optimization. We believe this because uncommenting the first line lets the task run:

--set hive.auto.convert.join = false;

 select ar_id, ar_namespace, ar_title, NULL as ar_text, NULL as ar_comment, NULL as ar_comment_id,
        case when ar_deleted&4 != 0 then null when ar_actor = 0
        then ar_user else COALESCE( actor_user, 0 ) END AS ar_user,
        case when ar_deleted&4 != 0 then null when ar_actor = 0
        then ar_user_text else actor_name END AS ar_user_text,
        if(ar_deleted&4 <> 0,0,ar_actor) as ar_actor, ar_timestamp, ar_minor_edit, NULL as ar_flags, ar_rev_id,
        case when ar_deleted&1 != 0 then null when content_id is NULL then ar_text_id
        else content_id end as ar_text_id,
        ar_deleted, if(ar_deleted&1 <> 0,null,ar_len) as ar_len,
        ar_page_id, ar_parent_id, if(ar_deleted&1 <> 0,null,ar_sha1) as ar_sha1,
        case when ar_deleted&1 != 0 then null when model_name is NULL then ar_content_model
        else model_name end as ar_content_model,
        case when ar_deleted&1 != 0 then null when model_name IS NULL then ar_content_format
        else NULL end as ar_content_format

   from archive
            left join
        (select slot_revision_id,
                content_id,
                model_name
           from slots
                    join
                slot_roles      on slot_role_id = role_id
                                and role_name = 'main'
                    join
                content         on slot_content_id = content_id
                    join
                content_models  on content_model = model_id

        ) slot_with_content_meta    on ar_rev_id = slot_revision_id
            left join
        actor                       on ar_actor = actor_id

  limit 100
;

Event Timeline

Ping @elukey, @Ottomata : I think we should apply hive.auto.convert.join = false in hive-site.xml so that map-side joins are never done automatically.

Thanks for working on this, @JAllemandou! I've just started suffering this on a lot of queries, including ones that previously worked fine. The error message is "OperationalError: Error while processing statement: FAILED: Execution Error, return code 134 from org.apache.hadoop.hive.ql.exec.mr.MapredLocalTask".

As you note, the workaround of SET hive.auto.convert.join=false; still works, but of course it's a bit annoying to have to do it on (nearly) every query 😛

Is this related to T206279 ?

I don't think the root-cause is the same (one problem happens on HiveServer2, the other on Hive CLI). The solution however is the same, as the issues are happening on the same portion of process:
Issues happen when a MapRedLocalTask is executed. Those tasks are an optimization over joins when data size is small enough. When hive.auto.convert.join=true hive automatically converts joins to map-join when it thinks it's good.
By setting hive.auto.convert.join=false by default, we'll lose some small optimization (because data is small).

Is this related to T206279 ?

I don't think the root-cause is the same (one problem happens on HiveServer2, the other on Hive CLI). The solution however is the same, as the issues are happening on the same portion of process:
Issues happen when a MapRedLocalTask is executed. Those tasks are an optimization over joins when data size is small enough. When hive.auto.convert.join=true hive automatically converts joins to map-join when it thinks it's good.
By setting hive.auto.convert.join=false by default, we'll lose some small optimization (because data is small).

Hmm, the big uptick in errors I described above happened using HiveServer2. Do you mean that this happens on both, whereas T206279 happens on HiveServer2 only?

In T209536#4754461, @Neil_P._Quinn_WMF wrote:

Hmm, the big uptick in errors I described above happened using HiveServer2. Do you mean that this happens on both, whereas T206279 happens on HiveServer2 only?

I haven't try HiveServer2, but I'm not surprised it happens in there as well.

Ping @elukey, @Ottomata to move forward with suggested solution.

fdans moved this task from Incoming to Operational Excellence on the Analytics board.

Change 474907 had a related patch set uploaded (by Elukey; owner: Elukey):
[operations/puppet/cdh@master] Set hive.auto.convert.join to false in hive-site.xml

https://gerrit.wikimedia.org/r/474907

Change 474907 merged by Elukey:
[operations/puppet/cdh@master] Set hive.auto.convert.join to false in hive-site.xml

https://gerrit.wikimedia.org/r/474907

Mentioned in SAL (#wikimedia-analytics) [2018-11-20T14:02:09Z] <elukey> restart hive-server2 to pick up new settings - T209536

Change 531866 had a related patch set uploaded (by Elukey; owner: Elukey):
[operations/puppet@production] cdh::hive: re-allow localjoins in hiveserver2's jvm

https://gerrit.wikimedia.org/r/531866

After @elukey found the reason for which local-tasks were failing ( thanks for the awesome troubleshoutting!), I devised this to be able to test configuration:

SELECT h.hostname, u.nb_users
FROM (
  SELECT wiki_db, COUNT(distinct user_id) as nb_users
  FROM wmf.mediawiki_user_history
  WHERE snapshot = '2019-07'
  GROUP BY wiki_db
) u
    INNER JOIN (
  SELECT DISTINCT hostname, dbname
  FROM wmf_raw.mediawiki_project_namespace_map
  WHERE snapshot = '2019-07'
) h
      ON u.wiki_db = h.dbname
ORDER BY nb_users DESC
LIMIT 10
;

This query is executed in 4 jobs, the first 2 are regular mapreduce running in parallel, the third one is the one that can be converted to local-join (depending on settings).

Test results:

systemsettingresult
hivedefaultsuccess - no no local-map
hivehive.auto.convert.join = truesuccess - local-map
beelinedefaultsuccess - no local-map
beelinehive.auto.convert.join = truesuccess - BUT local-map failed and fall back on mapreduce map (I didn't know it was possible)
beelinehive.auto.convert.join = true AND hive.exec.submit.local.task.via.child = falsesuccess - local-map succeeded !

Change 531866 merged by Elukey:
[operations/puppet@production] cdh::hive: re-allow localjoins in hiveserver2's jvm

https://gerrit.wikimedia.org/r/531866

Mentioned in SAL (#wikimedia-analytics) [2019-08-23T09:38:20Z] <elukey> restart hive-server2 on an-coord1001 to pick up new settings - T209536

Test after update using

SELECT h.hostname, u.nb_users
FROM (
  SELECT wiki_db, COUNT(distinct user_id) as nb_users
  FROM wmf.mediawiki_user_history
  WHERE snapshot = '2019-07'
  GROUP BY wiki_db
) u
    INNER JOIN (
  SELECT DISTINCT hostname, dbname
  FROM wmf_raw.mediawiki_project_namespace_map
  WHERE snapshot = '2019-07'
) h
      ON u.wiki_db = h.dbname
ORDER BY nb_users DESC
LIMIT 10
;
systemsettingresult
hivedefaultsuccess - local join
beelinedefaultsuccess - local join

Looks good to me !