hadoop - Hive查询中的ParseException

Question

我正在运行以下Hive查询(MapR版本0.12):

SELECT A.ID, A.AMT1, A.AMT2
FROM (
  SELECT ID as ID, NET_AMOUNT as AMT1
  FROM TEST_TABLE
  WHERE DATE_BY >='2012-10-01' AND DATE_BY <='2012-10-31') as q
  JOIN (
    SELECT ID as ID, NET_AMOUNT as AMT2
    FROM TEST_TABLE
    WHERE DATE_BY >='2013-10-01' AND DATE_BY <='2013-10-31') as r  
  ON q.ID=r.ID ) A

但我收到此错误:

Error : FAILED: ParseException line 2:2 cannot recognize input near '(' 'SELECT' 'ID' in subquery source

Answer 1

您的查询似乎存在一些问题，我认为应该是这样的:

SELECT q.ID,q.AMT1,q.AMT2 FROM  
(SELECT ID as ID, NET_AMOUNT as AMT1 FROM TEST_TABLE WHERE DATE_BY >='2012-10-01' AND DATE_BY <='2012-10-31')  q 
JOIN (SELECT ID as ID ,NET_AMOUNT as AMT2 FROM TEST_TABLE WHERE DATE_BY >='2013-10-01' AND DATE_BY <='2013-10-31')  r
on (q.ID=r.ID )

但是，当您比较DATES时，以及@Antariksha退出时，您需要进行转换。
但是我更喜欢一个更好的选择，以时间戳格式比较DATE。因此，在这种情况下，您的查询将如下所示:

SELECT q.ID,q.AMT1,q.AMT2 FROM  
(SELECT ID as ID, NET_AMOUNT as AMT1 FROM TEST_TABLE WHERE unix_timestamp(DATE_BY,"<your date patern>") >=unix_timestamp('2012-10-01',"YYYY-MM-DD") AND unix_timestamp(DATE_BY,"<your date patern>") <=unix_timestamp('2012-10-31',"YYYY-MM-DD"))  q 
JOIN (SELECT ID as ID ,NET_AMOUNT as AMT2 FROM TEST_TABLE WHERE unix_timestamp(DATE_BY,"<your date patern>") >=unix_timestamp('2013-10-01',"YYYY-MM-DD") AND unix_timestamp(DATE_BY,"<your date patern>") <=unix_timestamp('2013-10-31',"YYYY-MM-DD"))  r
on (q.ID=r.ID )

希望能帮助到你...!!!