You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Techy Teck <co...@gmail.com> on 2012/07/21 03:06:39 UTC
Invalid Table Alias
Whenever I am running the below query
SELECT TT.BUYER_ID , COUNT(*) FROM
(SELECT testingtable1.buyer_id, testingtable1.item_id,
testingtable1.created_time 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 where
to_date(from_unixtime(cast(prod_and_ts.timestamps as BIGINT))) =
'2012-07-09') prod_and_ts RIGHT OUTER JOIN (SELECT buyer_id, item_id,
rank(buyer_id), created_time, UNIX_TIMESTAMP(created_time)
FROM (
SELECT buyer_id, item_id, created_time
FROM testingtable1
where to_date(from_unixtime(cast(UNIX_TIMESTAMP(created_time) as int))) =
'2012-07-09'
DISTRIBUTE BY buyer_id
SORT BY buyer_id, created_time desc
) a
WHERE rank(buyer_id) < 5) testingtable1 ON (testingtable1.item_id =
prod_and_ts.product_id AND testingtable1.BUYER_ID = prod_and_ts.USER_ID AND
*abs(datediff(mi,
testingtable1.created_time,FROM_UNIXTIME(cast(prod_and_ts.timestamps as
BIGINT)))) <= 15)* where prod_and_ts.product_id IS NULL ORDER BY
testingtable1.buyer_id, testingtable1.created_time desc) TT GROUP BY
TT.BUYER_ID;
I am getting below exception as - Its happening in red color in above query
*FAILED: Error in semantic analysis: line 10:157 Invalid Table Alias mi.*
*
*
But the same thing works fine in SQL Server. Anything wrong I am doing in
the red line? I am currently trying to see by that red line is if
difference between date is within 15 minutes.
Re: Invalid Table Alias
Posted by Vijay <te...@gmail.com>.
There seems to be something wrong with the query (the red highlighted
portion). What is mi in "abs(datediff(mi"?
On Sun, Jul 22, 2012 at 11:28 AM, Techy Teck <co...@gmail.com> wrote:
> Can anyone give some sort of pointers or suggestions why the below query is
> giving me exception and the same query works fine in SQL server. Below is
> the SQL fiddle that works in SQL Server-
>
> http://sqlfiddle.com/#!3/d87b2/4
>
>
>
> On Fri, Jul 20, 2012 at 6:47 PM, Techy Teck <co...@gmail.com> wrote:
>>
>> In hive we cannot calculate the difference between dates in minutes?
>>
>>
>>
>>
>> On Fri, Jul 20, 2012 at 6:06 PM, Techy Teck <co...@gmail.com>
>> wrote:
>>>
>>> Whenever I am running the below query
>>>
>>> SELECT TT.BUYER_ID , COUNT(*) FROM
>>> (SELECT testingtable1.buyer_id, testingtable1.item_id,
>>> testingtable1.created_time 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 where
>>> to_date(from_unixtime(cast(prod_and_ts.timestamps as BIGINT))) =
>>> '2012-07-09') prod_and_ts RIGHT OUTER JOIN (SELECT buyer_id, item_id,
>>> rank(buyer_id), created_time, UNIX_TIMESTAMP(created_time)
>>> FROM (
>>> SELECT buyer_id, item_id, created_time
>>> FROM testingtable1
>>> where to_date(from_unixtime(cast(UNIX_TIMESTAMP(created_time) as int))) =
>>> '2012-07-09'
>>> DISTRIBUTE BY buyer_id
>>> SORT BY buyer_id, created_time desc
>>> ) a
>>> WHERE rank(buyer_id) < 5) testingtable1 ON (testingtable1.item_id =
>>> prod_and_ts.product_id AND testingtable1.BUYER_ID = prod_and_ts.USER_ID AND
>>> abs(datediff(mi,
>>> testingtable1.created_time,FROM_UNIXTIME(cast(prod_and_ts.timestamps as
>>> BIGINT)))) <= 15) where prod_and_ts.product_id IS NULL ORDER BY
>>> testingtable1.buyer_id, testingtable1.created_time desc) TT GROUP BY
>>> TT.BUYER_ID;
>>>
>>>
>>> I am getting below exception as - Its happening in red color in above
>>> query
>>>
>>> FAILED: Error in semantic analysis: line 10:157 Invalid Table Alias mi.
>>>
>>> But the same thing works fine in SQL Server. Anything wrong I am doing in
>>> the red line? I am currently trying to see by that red line is if difference
>>> between date is within 15 minutes.
>>
>>
>
Re: Invalid Table Alias
Posted by Techy Teck <co...@gmail.com>.
Can anyone give some sort of pointers or suggestions why the below query is
giving me exception and the same query works fine in SQL server. Below is
the SQL fiddle that works in SQL Server-
http://sqlfiddle.com/#!3/d87b2/4
On Fri, Jul 20, 2012 at 6:47 PM, Techy Teck <co...@gmail.com> wrote:
> In hive we cannot calculate the difference between dates in minutes?
>
>
>
>
> On Fri, Jul 20, 2012 at 6:06 PM, Techy Teck <co...@gmail.com>wrote:
>
>> Whenever I am running the below query
>>
>> SELECT TT.BUYER_ID , COUNT(*) FROM
>> (SELECT testingtable1.buyer_id, testingtable1.item_id,
>> testingtable1.created_time 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 where
>> to_date(from_unixtime(cast(prod_and_ts.timestamps as BIGINT))) =
>> '2012-07-09') prod_and_ts RIGHT OUTER JOIN (SELECT buyer_id, item_id,
>> rank(buyer_id), created_time, UNIX_TIMESTAMP(created_time)
>> FROM (
>> SELECT buyer_id, item_id, created_time
>> FROM testingtable1
>> where to_date(from_unixtime(cast(UNIX_TIMESTAMP(created_time) as int))) =
>> '2012-07-09'
>> DISTRIBUTE BY buyer_id
>> SORT BY buyer_id, created_time desc
>> ) a
>> WHERE rank(buyer_id) < 5) testingtable1 ON (testingtable1.item_id =
>> prod_and_ts.product_id AND testingtable1.BUYER_ID = prod_and_ts.USER_ID AND
>> *abs(datediff(mi,
>> testingtable1.created_time,FROM_UNIXTIME(cast(prod_and_ts.timestamps as
>> BIGINT)))) <= 15)* where prod_and_ts.product_id IS NULL ORDER BY
>> testingtable1.buyer_id, testingtable1.created_time desc) TT GROUP BY
>> TT.BUYER_ID;
>>
>>
>> I am getting below exception as - Its happening in red color in above
>> query
>>
>> *FAILED: Error in semantic analysis: line 10:157 Invalid Table Alias mi.*
>> *
>> *
>> But the same thing works fine in SQL Server. Anything wrong I am doing in
>> the red line? I am currently trying to see by that red line is if
>> difference between date is within 15 minutes.
>>
>
>
Re: Invalid Table Alias
Posted by Techy Teck <co...@gmail.com>.
In hive we cannot calculate the difference between dates in minutes?
On Fri, Jul 20, 2012 at 6:06 PM, Techy Teck <co...@gmail.com> wrote:
> Whenever I am running the below query
>
> SELECT TT.BUYER_ID , COUNT(*) FROM
> (SELECT testingtable1.buyer_id, testingtable1.item_id,
> testingtable1.created_time 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 where
> to_date(from_unixtime(cast(prod_and_ts.timestamps as BIGINT))) =
> '2012-07-09') prod_and_ts RIGHT OUTER JOIN (SELECT buyer_id, item_id,
> rank(buyer_id), created_time, UNIX_TIMESTAMP(created_time)
> FROM (
> SELECT buyer_id, item_id, created_time
> FROM testingtable1
> where to_date(from_unixtime(cast(UNIX_TIMESTAMP(created_time) as int))) =
> '2012-07-09'
> DISTRIBUTE BY buyer_id
> SORT BY buyer_id, created_time desc
> ) a
> WHERE rank(buyer_id) < 5) testingtable1 ON (testingtable1.item_id =
> prod_and_ts.product_id AND testingtable1.BUYER_ID = prod_and_ts.USER_ID AND
> *abs(datediff(mi,
> testingtable1.created_time,FROM_UNIXTIME(cast(prod_and_ts.timestamps as
> BIGINT)))) <= 15)* where prod_and_ts.product_id IS NULL ORDER BY
> testingtable1.buyer_id, testingtable1.created_time desc) TT GROUP BY
> TT.BUYER_ID;
>
>
> I am getting below exception as - Its happening in red color in above query
>
> *FAILED: Error in semantic analysis: line 10:157 Invalid Table Alias mi.*
> *
> *
> But the same thing works fine in SQL Server. Anything wrong I am doing in
> the red line? I am currently trying to see by that red line is if
> difference between date is within 15 minutes.
>