Grokbase Groups Hive user March 2011
FAQ
Hi Everyone
I'm facing an issue with hive on a relatively larger query which involves
joins on six hive tables. My query is running fine without any errors, all the
map reduce jobs run to completion but unfortunately it is not showing up any
results. I tried debugging the query and to investigate the root cause, When i
removed one of the last conditions from the query it is showing up results, this
is the condition I removed
and ((unix_timestamp(r4.flight_datetime) <= unix_timestamp(r8.removal_datetime
))or R8.REMOVAL_DATETIME IS NULL)
I have used a similar comparison operation within my working query on dates
using the unix_timestamp method which is working as desired. But here it is not,
I'm assuming this would be due to the fact that r8.removal_datetime has null
values hence here the evaluation would happen against a valid unix stamp and
null.
ie (unix_timestamp(r4.flight_datetime) <= unix_timestamp(r8.removal_datetime ))
would inturn lead to valid timestamp<= NULL

Has any one faced similar situations before? How can i get around this hurdle?
Please advise.


Regards
Bejoy.K.S

Search Discussions

  • Viral Bajaria at Mar 4, 2011 at 7:48 pm
    Bejoy,

    you should use COALESCE() whenever you are comparing values that could have
    NULL values.

    -Viral
    On Fri, Mar 4, 2011 at 7:13 AM, Bejoy Ks wrote:

    Hi Everyone
    I'm facing an issue with hive on a relatively larger query which
    involves joins on six hive tables. My query is running fine without any
    errors, all the map reduce jobs run to completion but unfortunately it is
    not showing up any results. I tried debugging the query and to investigate
    the root cause, When i removed one of the last conditions from the query it
    is showing up results, this is the condition I removed
    and ((unix_timestamp(r4.flight_datetime) <=
    unix_timestamp(r8.removal_datetime ))or R8.REMOVAL_DATETIME IS NULL)
    I have used a similar comparison operation within my working query on dates
    using the unix_timestamp method which is working as desired. But here it is
    not, I'm assuming this would be due to the fact that r8.removal_datetime has
    null values hence here the evaluation would happen against a valid unix
    stamp and null.
    ie (unix_timestamp(r4.flight_datetime) <=
    unix_timestamp(r8.removal_datetime )) would inturn lead to valid
    timestamp<= NULL

    Has any one faced similar situations before? How can i get around this
    hurdle? Please advise.

    Regards
    Bejoy.K.S


  • Bejoy Ks at Mar 8, 2011 at 8:27 am
    Thanks Viral. That was a good piece of info.COALESCE() was some thing new to me
    as I'm not from a db background. I googled more on COALESCE() and found it
    really good,However that didn't resolve my problem. I got it resolved by re
    framing my query this way

    and ((unix_timestamp(r4.flight_datetime) <= unix_timestamp(r8.removal_datetime
    )) OR upper(R8.REMOVAL_DATETIME) = 'NULL')

    I had to do an equality check for the string 'NULL' rather than a null check.
    Any clues why I had to go this way.
    I'm using SQOOP to import data to Hive tables from teradata environment first
    then processing the same using Hive QL.

    Regards
    Bejoy.K.S





    ________________________________
    From: Viral Bajaria <viral.bajaria@gmail.com>
    To: user@hive.apache.org
    Cc: Bejoy Ks <bejoy_ks@yahoo.com>
    Sent: Sat, March 5, 2011 1:17:43 AM
    Subject: Re: Date function unix_timestamp() with input values null doen't work
    as desired


    Bejoy,
    you should use COALESCE() whenever you are comparing values that could have NULL
    values.

    -Viral



    On Fri, Mar 4, 2011 at 7:13 AM, Bejoy Ks wrote:

    Hi Everyone
    I'm facing an issue with hive on a relatively larger query which involves
    joins on six hive tables. My query is running fine without any errors, all the
    map reduce jobs run to completion but unfortunately it is not showing up any
    results. I tried debugging the query and to investigate the root cause, When i
    removed one of the last conditions from the query it is showing up results, this
    is the condition I removed
    and ((unix_timestamp(r4.flight_datetime) <= unix_timestamp(r8.removal_datetime
    ))or R8.REMOVAL_DATETIME IS NULL)
    I have used a similar comparison operation within my working query on dates
    using the unix_timestamp method which is working as desired. But here it is not,
    I'm assuming this would be due to the fact that r8.removal_datetime has null
    values hence here the evaluation would happen against a valid unix stamp and
    null.
    ie (unix_timestamp(r4.flight_datetime) <= unix_timestamp(r8.removal_datetime ))
    would inturn lead to valid timestamp<= NULL

    Has any one faced similar situations before? How can i get around this hurdle?
    Please advise.


    Regards
    Bejoy.K.S


Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupuser @
categorieshive, hadoop
postedMar 4, '11 at 3:14p
activeMar 8, '11 at 8:27a
posts3
users2
websitehive.apache.org

2 users in discussion

Bejoy Ks: 2 posts Viral Bajaria: 1 post

People

Translate

site design / logo © 2021 Grokbase