Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 Search Hadoop and all its subprojects:

Switch to Plain View
Hive >> mail # user >> Invalid Table Alias


+
Techy Teck 2012-07-21, 01:06
+
Techy Teck 2012-07-21, 01:47
+
Techy Teck 2012-07-22, 18:28
Copy link to this message
-
Re: Invalid Table Alias
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.
>>
>>
>
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB