Home Forums Hortonworks Sandbox HIVE outer join query not working in version 2.0

This topic contains 6 replies, has 4 voices, and was last updated by  Rafal Janik 8 months ago.

  • Creator
    Topic
  • #41037

    Ramesh Joshi
    Member

    Following query is not working in 2.0 sandbox but works in 1.3 sandbox.
    Its working for only one level of OJ..

    The table is created in HCatalog

    select *
    from belk_view_reg_no_tran_no o
    left outer join belk_view_store_no_tran_no a
    on (o.Transaction_no = a.Transaction_no and o.register = a.register )
    left outer join belk_view_temp_input_file_as_table c
    on (o.Transaction_no = c.Transaction_no and o.register = c.register and c.line_type = ‘ 13′ )
    left outer join belk_view_transaction_type d
    on (o.Transaction_no = d.Transaction_no and o.register = d.register)
    left outer join belk_view_tran_no_assoc_name e
    on (o.Transaction_no = e.Transaction_no and o.register = e.register)
    left outer join belk_view_tran_no_tender f
    on (o.Transaction_no = f.Transaction_no and o.register = f.register)
    left outer join belk_view_tran_no_cust_phone_no g
    on (o.Transaction_no = g.Transaction_no and o.register = g.register)

Viewing 6 replies - 1 through 6 (of 6 total)

You must be logged in to reply to this topic.

  • Author
    Replies
  • #49081

    Rafal Janik
    Participant

    hi,
    I’m currently trying to pass spring-xd tutorial (http://hortonworks.com/hadoop-tutorial/using-spring-xd-to-stream-tweets-to-hadoop-for-sentiment-analysis/) using sanbox 2.0: Sandbox Build a9da906 14:38 10-28-13 (V-irtualBox version) .
    Everything works fine apart from the last step : creating a table with left outer joining:

    CREATE TABLE cyrustweetsanalysis
    STORED AS RCFile
    AS
    SELECT
    t.*,
    case s.sentiment
    when 'positive' then 2
    when 'neutral' then 1
    when 'negative' then 0
    end as sentiment
    FROM cyrustweets_clean t
    LEFT OUTER JOIN tweets_sentiment s on t.id = s.id;

    So is this “outer-join” issue still active or am I doing something wrong?

    Collapse
    #42265

    Cheryle Custer
    Moderator

    Hi,

    The Sandbox 2.0 GA is now available. Can you please download it and try again and let us know if the issue has been resolved?

    Collapse
    #42052

    Ramesh Joshi
    Member

    Did not hear any feedback on the HIVE outerjoing issue with the HDP 2.0

    If we assume the issue is because of the sandbox beta doe not have full functionality , then when can you provide he usable sandbox of 2.0.

    Again, it’s impacting our POC.

    Collapse
    #41705

    Ramesh Joshi
    Member

    ERROR from the log.. the log file is too big to paste here..

    Error occurred executing hive query: Unknown exception.

    [24/Oct/2013 03:31:44 +0000] middleware INFO Processing exception: Error occurred executing hive query: Unknown exception.: Traceback (most recent call last):
    File “/usr/lib/hue/build/env/lib/python2.6/site-packages/Django-1.2.3-py2.6.egg/django/core/handlers/base.py”, line 100, in get_response
    response = callback(request, *callback_args, **callback_kwargs)
    File “/usr/lib/hue/apps/beeswax/src/beeswax/views.py”, line 553, in execute_query
    return execute_directly(request, query, query_server, design, on_success_url=on_success_url, download=download)
    File “/usr/lib/hue/apps/beeswax/src/beeswax/views.py”, line 1238, in execute_directly
    raise PopupException(_(‘Error occurred executing hive query: ‘ + error_message))
    PopupException: Error occurred executing hive query: Unknown exception.
    [24/Oct/2013 03:31:44 +0000] thrift_util DEBUG Thrift call .get_log returned in 9ms: “13/10/24 03:31:39 WARN conf.HiveConf: DEPRECATED: Configuration property hive.metastore.local no longer has any effect. Make sure to provide a valid value for hive.metastore.uris if you are connecting to a remote metastore.\n13/10/24 03:31:39 WARN conf.HiveConf: DEPRECATED: Configuration property hive.metastore.local no longer has any effect. Make sure to provide a valid value for hive.metastore.uris if you are connecting to a remote metastore.\n13/10/24 03:31:40 WARN conf.HiveConf: DEPRECATED: Configuration property hive.metastore.local no longer has any effect. Make sure to provide a valid value for hive.metastore.uris if you are connecting to a remote metastore.\n13/10/24 03:31:40 INFO ql.Driver: \n13/10/24 03:31:40 INFO ql.Driver: \n13/10/24 03:31:40 INFO ql.Driver: \n13/10/24 03:31:40 INFO parse.ParseDriver: Parsing command: use default\n13/10/24 03:31:40 INFO parse.ParseDriver: Parse Completed\n13/10…
    [24/Oct/2013 03:31:44 +0000] thrift_util DEBUG Thrift call: .get_log(args=(‘a67be5ed-9a1c-4872-aaf8-29350da52a61′,), kwargs={})
    [24/Oct/2013 03:31:44 +0000] dbms ERROR BeeswaxException(errorCode=0, log_context=’a67be5ed-9a1c-4872-aaf8-29350da52a61′, message=None, SQLState=’ ‘, handle=QueryHandle(log_context=’a67be5ed-9a1c-4872-aaf8-29350da52a61′, id=’a67be5ed-9a1c-4872-aaf8-29350da52a61′))
    Traceback (most recent call last):
    File “/usr/lib/hue/apps/beeswax/src/beeswax/server/dbms.py”, line 295, in execute_and_watch
    handle = self.client.query(query, query_history.statement_number)
    File “/usr/lib/hue/apps/beeswax/src/beeswax/server/beeswax_lib.py”, line 138, in query
    handle = self.db_client.query(thrift_query)

    Collapse
    #41697

    Dave
    Moderator

    Hi Ramesh,

    What are the errors you are seeing?
    If you run the query in Hive directly does it work, also bear in mind that HDP 2.0 Sandbox is built on the Beta

    Thanks

    Dave

    Collapse
    #41673

    Ramesh Joshi
    Member

    any update on the HIVE multiple outer join issue… Its impacting our Hadoop POC.

    Collapse
Viewing 6 replies - 1 through 6 (of 6 total)