FAILED：解析错误：行7:19不匹配的输入'（'期望FROM from子句

问题描述

input：table1

input: table1

id1, p1, c1, t1-1
id2, p1, c1, t1-2
id3, p2, c2, t1-2
id4, p3, c3, t1-3
id5, p1, c1, t1-4
id6, p2, c2, t1-4
id7, p1, c3, t1-4
id8, p2, c2, t1-5
id9, p2, c3, t1-5
id10, p2, c4, t1-5
id11, p2, c3, t1-6
id12, p1, c1, t2-1
id13, p1, c2, t2-1
id14, p2, c2, t2-3
id15, p2, c3, t2-3
id16, p1, c1, t2-4

输出应该是

The output should be

id5, p1, c1, t1-4
id8, p2, c2, t1-5
id4, p3, c3, t1-3
id7, p1, c3, t1-4
id11, p2, c3, t1-6
id10, p2, c4, t1-5 
id16, p1, c1, t2-4
id13, p1, c2, t2-1
id14, p2, c2, t2-3
id15, p2, c3, t2-3

Hive查询：

SET mapred.reduce.tasks=1;
CREATE EXTERNAL TABLE table1(
id string,
t string,
p string,
c string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LOCATION 's3://input/';

ALTER TABLE table1 RECOVER PARTITIONS;

CREATE EXTERNAL TABLE table2 (
id string,
t string,
p string,
c string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\n'
STORED AS TEXTFILE
LOCATION 's3://output/';

错误：

Error:

hive> 
    >    
    > INSERT OVERWRITE TABLE table2
    > SELECT id, p, c, t
    >  FROM
    > (
    >  SELECT id, p, c, t,
    >  ROW_NUMBER() OVER (PARTITION BY SUBSTR(t, 1, INSTR(t, '-') - 1), p, c ORDER BY t DESC) rnum
    >  FROM table1
    > ) q
    >  WHERE rnum = 1
    > ;
FAILED: Parse Error: line 7:19 mismatched input '(' expecting FROM in from clause

>如何解决这个问题？

推荐答案

SELECT t1.id, t1.p, t1.c, t1.time_stamp,
FROM table1 t1 JOIN
( SELECT p, c, Max(UNIX_TIMESTAMP(time_stamp)) as max_epoch 
FROM table1 
GROUP BY p, c) temp 
WHERE t1.p = temp.p AND t1.c = temp.c AND UNIX_TIMESTAMP(t2.time_stamp) = max_epoch ;

这篇关于FAILED：解析错误：行7:19不匹配的输入'（'期望FROM from子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！