You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Bejoy Ks <be...@yahoo.com> on 2011/03/04 16:13:50 UTC
Date function unix_timestamp() with input values null doen't work as desired
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
Re: Date function unix_timestamp() with input values null doen't work as desired
Posted by Bejoy Ks <be...@yahoo.com>.
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 <vi...@gmail.com>
To: user@hive.apache.org
Cc: Bejoy Ks <be...@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 <be...@yahoo.com> 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
>
>
>
>
Re: Date function unix_timestamp() with input values null doen't work
as desired
Posted by Viral Bajaria <vi...@gmail.com>.
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 <be...@yahoo.com> 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
>
>
>
>