I wrote this query but everytime I get the below error.

    select * from (select t2.buyer_id, t2.item_id, t2.created_time as
created_time, subq.user_id, subq.product_id, subq.timestamps as timestamps
    from
    (select user_id, prod_and_ts.product_id as product_id,
prod_and_ts.timestamps as timestamps from testingtable2 lateral view
explode(purchased_item) exploded_table as prod_and_ts) subq INNER JOIN
testingtable1 t2 on t2.buyer_id = subq.user_id
    AND subq.timestamps = unix_timestamp(t2.created_time)
    WHERE (subq.product_id <> t2.item_id)
    union all
    select t2.buyer_id, t2.item_id as item_id, t2.created_time,
subq.user_id, subq.product_id as product_id, subq.timestamps
    from
    (select user_id, prod_and_ts.product_id as product_id,
prod_and_ts.timestamps as timestamps from testingtable2 lateral view
explode(purchased_item) exploded_table as prod_and_ts) subq INNER JOIN
testingtable1 t2 on t2.buyer_id = subq.user_id
        and subq.product_id = t2.item_id
        WHERE (subq.timestamps <> unix_timestamp(t2.created_time)))
unionall;

But I always get error as -

`*FAILED: Parse Error: line 3:184 mismatched input 'INNER' expecting ) in
subquery source`*
*Raihan Jamal*

Did you try just using "join" instead of "inner join"?

On Mon, Jul 9, 2012 at 11:42 AM, Raihan Jamal <[EMAIL PROTECTED]> wrote:

> I wrote this query but everytime I get the below error.
>
>     select * from (select t2.buyer_id, t2.item_id, t2.created_time as
> created_time, subq.user_id, subq.product_id, subq.timestamps as timestamps
>     from
>     (select user_id, prod_and_ts.product_id as product_id,
> prod_and_ts.timestamps as timestamps from testingtable2 lateral view
> explode(purchased_item) exploded_table as prod_and_ts) subq INNER JOIN
> testingtable1 t2 on t2.buyer_id = subq.user_id
>     AND subq.timestamps = unix_timestamp(t2.created_time)
>     WHERE (subq.product_id <> t2.item_id)
>     union all
>     select t2.buyer_id, t2.item_id as item_id, t2.created_time,
> subq.user_id, subq.product_id as product_id, subq.timestamps
>     from
>     (select user_id, prod_and_ts.product_id as product_id,
> prod_and_ts.timestamps as timestamps from testingtable2 lateral view
> explode(purchased_item) exploded_table as prod_and_ts) subq INNER JOIN
> testingtable1 t2 on t2.buyer_id = subq.user_id
>         and subq.product_id = t2.item_id
>         WHERE (subq.timestamps <> unix_timestamp(t2.created_time)))
> unionall;
>
>
>
> But I always get error as -
>
> `*FAILED: Parse Error: line 3:184 mismatched input 'INNER' expecting ) in
> subquery source`*
>
>
>
>
> *Raihan Jamal*
>
>

Yup that worked for me. I figure that out after reading the docs, INNER
JOIN means JOIN in HiveQL.
*Raihan Jamal*

On Mon, Jul 9, 2012 at 2:48 PM, Roberto Sanabria <[EMAIL PROTECTED]>wrote:

> Did you try just using "join" instead of "inner join"?
>
>
> On Mon, Jul 9, 2012 at 11:42 AM, Raihan Jamal <[EMAIL PROTECTED]>wrote:
>
>> I wrote this query but everytime I get the below error.
>>
>>     select * from (select t2.buyer_id, t2.item_id, t2.created_time as
>> created_time, subq.user_id, subq.product_id, subq.timestamps as timestamps
>>     from
>>     (select user_id, prod_and_ts.product_id as product_id,
>> prod_and_ts.timestamps as timestamps from testingtable2 lateral view
>> explode(purchased_item) exploded_table as prod_and_ts) subq INNER JOIN
>> testingtable1 t2 on t2.buyer_id = subq.user_id
>>     AND subq.timestamps = unix_timestamp(t2.created_time)
>>     WHERE (subq.product_id <> t2.item_id)
>>     union all
>>     select t2.buyer_id, t2.item_id as item_id, t2.created_time,
>> subq.user_id, subq.product_id as product_id, subq.timestamps
>>     from
>>     (select user_id, prod_and_ts.product_id as product_id,
>> prod_and_ts.timestamps as timestamps from testingtable2 lateral view
>> explode(purchased_item) exploded_table as prod_and_ts) subq INNER JOIN
>> testingtable1 t2 on t2.buyer_id = subq.user_id
>>         and subq.product_id = t2.item_id
>>         WHERE (subq.timestamps <> unix_timestamp(t2.created_time)))
>> unionall;
>>
>>
>>
>> But I always get error as -
>>
>> `*FAILED: Parse Error: line 3:184 mismatched input 'INNER' expecting )
>> in subquery source`*
>>
>>
>>
>>
>> *Raihan Jamal*
>>
>>
>

Here timestamps is a string and I always get NULL in the second column when
I try to get date out of the timestamp. Anything wrong I am doing?
*
*
*
*
select A.timestamps, to_date(A.timestamps) from
(select user_id, prod_and_ts.product_id as product_id,
prod_and_ts.timestamps as timestamps from testingtable2 lateral view
explode(purchased_item) exploded_table as prod_and_ts) A;
This is the Output I am getting always.

*1004941621      NULL*
*1005268799      NULL*
*1061569397      NULL*
*1005542471      NULL*

*Raihan Jamal*

You need to use from_unixtime()

On Tue, Jul 10, 2012 at 1:01 PM, Raihan Jamal <[EMAIL PROTECTED]> wrote:
> Here timestamps is a string and I always get NULL in the second column when
> I try to get date out of the timestamp. Anything wrong I am doing?
>
>
> select A.timestamps, to_date(A.timestamps) from
> (select user_id, prod_and_ts.product_id as product_id,
> prod_and_ts.timestamps as timestamps from testingtable2 lateral view
> explode(purchased_item) exploded_table as prod_and_ts) A;
>
>
> This is the Output I am getting always.
>
> 1004941621      NULL
> 1005268799      NULL
> 1061569397      NULL
> 1005542471      NULL
>
>
>
> Raihan Jamal
>

I need only the date not the hours and second, so that is the reason I was
using to_date and from_unxitime() take int as parameter and timestamps is a
string in this case.
*Raihan Jamal*

On Tue, Jul 10, 2012 at 1:28 PM, Vijay <[EMAIL PROTECTED]> wrote:

> You need to use from_unixtime()
>
> On Tue, Jul 10, 2012 at 1:01 PM, Raihan Jamal <[EMAIL PROTECTED]>
> wrote:
> > Here timestamps is a string and I always get NULL in the second column
> when
> > I try to get date out of the timestamp. Anything wrong I am doing?
> >
> >
> > select A.timestamps, to_date(A.timestamps) from
> > (select user_id, prod_and_ts.product_id as product_id,
> > prod_and_ts.timestamps as timestamps from testingtable2 lateral view
> > explode(purchased_item) exploded_table as prod_and_ts) A;
> >
> >
> > This is the Output I am getting always.
> >
> > 1004941621      NULL
> > 1005268799      NULL
> > 1061569397      NULL
> > 1005542471      NULL
> >
> >
> >
> > Raihan Jamal
> >
>

to_date(from_unixtime(cast(timestamps as int)))

On Tue, Jul 10, 2012 at 1:33 PM, Raihan Jamal <[EMAIL PROTECTED]> wrote:
> I need only the date not the hours and second, so that is the reason I was
> using to_date and from_unxitime() take int as parameter and timestamps is a
> string in this case.
>
>
>
>
> Raihan Jamal
>
>
>
> On Tue, Jul 10, 2012 at 1:28 PM, Vijay <[EMAIL PROTECTED]> wrote:
>>
>> You need to use from_unixtime()
>>
>> On Tue, Jul 10, 2012 at 1:01 PM, Raihan Jamal <[EMAIL PROTECTED]>
>> wrote:
>> > Here timestamps is a string and I always get NULL in the second column
>> > when
>> > I try to get date out of the timestamp. Anything wrong I am doing?
>> >
>> >
>> > select A.timestamps, to_date(A.timestamps) from
>> > (select user_id, prod_and_ts.product_id as product_id,
>> > prod_and_ts.timestamps as timestamps from testingtable2 lateral view
>> > explode(purchased_item) exploded_table as prod_and_ts) A;
>> >
>> >
>> > This is the Output I am getting always.
>> >
>> > 1004941621      NULL
>> > 1005268799      NULL
>> > 1061569397      NULL
>> > 1005542471      NULL
>> >
>> >
>> >
>> > Raihan Jamal
>> >
>
>

Thanks Vijay, Yes it worked. Can you also take a look into one of my other
post subject title *TOP 10.*

*Raihan Jamal*

On Tue, Jul 10, 2012 at 1:41 PM, Vijay <[EMAIL PROTECTED]> wrote:

> to_date(from_unixtime(cast(timestamps as int)))
>
> On Tue, Jul 10, 2012 at 1:33 PM, Raihan Jamal <[EMAIL PROTECTED]>
> wrote:
> > I need only the date not the hours and second, so that is the reason I
> was
> > using to_date and from_unxitime() take int as parameter and timestamps
> is a
> > string in this case.
> >
> >
> >
> >
> > Raihan Jamal
> >
> >
> >
> > On Tue, Jul 10, 2012 at 1:28 PM, Vijay <[EMAIL PROTECTED]> wrote:
> >>
> >> You need to use from_unixtime()
> >>
> >> On Tue, Jul 10, 2012 at 1:01 PM, Raihan Jamal <[EMAIL PROTECTED]>
> >> wrote:
> >> > Here timestamps is a string and I always get NULL in the second column
> >> > when
> >> > I try to get date out of the timestamp. Anything wrong I am doing?
> >> >
> >> >
> >> > select A.timestamps, to_date(A.timestamps) from
> >> > (select user_id, prod_and_ts.product_id as product_id,
> >> > prod_and_ts.timestamps as timestamps from testingtable2 lateral view
> >> > explode(purchased_item) exploded_table as prod_and_ts) A;
> >> >
> >> >
> >> > This is the Output I am getting always.
> >> >
> >> > 1004941621      NULL
> >> > 1005268799      NULL
> >> > 1061569397      NULL
> >> > 1005542471      NULL
> >> >
> >> >
> >> >
> >> > Raihan Jamal
> >> >
> >
> >
>