Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive, mail # user - Invalid Table Alias


Copy link to this message
-
Re: Invalid Table Alias
Vijay 2012-07-23, 02:02
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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> wrote:
>>
>> In hive we cannot calculate the difference between dates in minutes?
>>
>>
>>
>>
>> On Fri, Jul 20, 2012 at 6:06 PM, Techy Teck <[EMAIL PROTECTED]>
>> 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.
>>
>>
>