为何Hive ParseDriver无法解析含JOIN子句的先导FROM格式SQL语句

使用Hive的ParseDriver解析SQL时出现异常，简单查询可以正常解析，但以下特殊写法的SQL无法解析：

无法解析的SQL #

FROM (SELECT p.datekey datekey, p.userid userid, c.clienttype  FROM detail.usersequence_client c JOIN fact.orderpayment p ON p.orderid = c.orderid  JOIN default.user du ON du.userid = p.userid WHERE p.datekey = 20131118 ) base  INSERT OVERWRITE TABLE `test`.`customer_kpi` SELECT base.datekey,   base.clienttype, count(distinct base.userid) buyer_count GROUP BY base.datekey, base.clienttype

解析代码 #

public ASTNode getASTNode(String sql) throws IOException, ParseException {
    HiveConf hiveConf = new HiveConf();
    Configuration conf = new Configuration(hiveConf);
    Context context = new Context(conf);

    ParseDriver pd = new ParseDriver();
    ASTNode ast = pd.parse(sql, context);
    System.out.println(ast.dump());
    return ast;
}

报错信息 #

NoViableAltException(256@[])
at org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.atomjoinSource(HiveParser_FromClauseParser.java:2265)
at org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.joinSource(HiveParser_FromClauseParser.java:2475)
at org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.fromSource(HiveParser_FromClauseParser.java:1690)
at org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.fromClause(HiveParser_FromClauseParser.java:1312)
at org.apache.hadoop.hive.ql.parse.HiveParser.fromClause(HiveParser.java:42022)
at org.apache.hadoop.hive.ql.parse.HiveParser.singleFromStatement(HiveParser.java:36335)
at org.apache.hadoop.hive.ql.parse.HiveParser.fromStatement(HiveParser.java:36112)
at org.apache.hadoop.hive.ql.parse.HiveParser.queryStatementExpressionBody(HiveParser.java:35808)
at org.apache.hadoop.hive.ql.parse.HiveParser.queryStatementExpression(HiveParser.java:35710)
at org.apache.hadoop.hive.ql.parse.HiveParser.execStatement(HiveParser.java:2284)
at org.apache.hadoop.hive.ql.parse.HiveParser.statement(HiveParser.java:1333)
at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:208)
at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:166)
at com.oppo.logtrace.sql.parser.HiveQLParser.getASTNode(HiveQLParser.java:27)
at com.oppo.logtrace.utils.sql.parser.TestHiveQLParser.testGetASTNode(TestHiveQLParser.java:18)
...
org.apache.hadoop.hive.ql.parse.ParseException: line 1:6 cannot recognize input near '(' 'SELECT' 'p' in joinSource
at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:211)
at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:166)

依赖配置 #

<!-- https://mvnrepository.com/artifact/org.apache.hive/hive-exec -->
<dependency>
    <groupId>org.apache.hive</groupId>
    <artifactId>hive-exec</artifactId>
    <version>2.3.5</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.hadoop/hadoop-common -->
<dependency>
    <groupId>org.apache.hadoop</groupId>
    <artifactId>hadoop-common</artifactId>
    <version>2.7.3</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.hadoop/hadoop-mapreduce-client-core -->
<dependency>
    <groupId>org.apache.hadoop</groupId>
    <artifactId>hadoop-mapreduce-client-core</artifactId>
    <version>2.7.3</version>
</dependency>

该错误是Hive 2.x版本默认关闭顶部前置FROM语法支持导致的语法识别失败，可通过以下两种方式修复：

方案1：调整SQL写法（兼容性最强） #

将特有的前置FROM写法修改为标准INSERT开头的通用SQL格式，修改后代码如下：

INSERT OVERWRITE TABLE `test`.`customer_kpi` 
SELECT base.datekey, base.clienttype, count(distinct base.userid) buyer_count 
FROM (
    SELECT p.datekey datekey, p.userid userid, c.clienttype  
    FROM detail.usersequence_client c 
    JOIN fact.orderpayment p ON p.orderid = c.orderid  
    JOIN default.user du ON du.userid = p.userid 
    WHERE p.datekey = 20131118 
) base 
GROUP BY base.datekey, base.clienttype

修改后不需要调整任何解析代码即可正常解析。

方案2：调整解析配置（保留原SQL写法） #

如果需要保留原前置FROM的SQL写法，可在解析代码中手动开启对应语法支持，修改后解析代码如下：

public ASTNode getASTNode(String sql) throws IOException, ParseException {
    HiveConf hiveConf = new HiveConf();
    // 开启多插入语法支持
    hiveConf.setBoolean("hive.parse.allow.multiple.insert", true);
    Configuration conf = new Configuration(hiveConf);
    Context context = new Context(conf);
    // 开启顶部前置FROM语法支持
    context.setAllowTopLevelFrom(true);

    ParseDriver pd = new ParseDriver();
    ASTNode ast = pd.parse(sql, context);
    System.out.println(ast.dump());
    return ast;
}

配置修改后即可正常解析原SQL语句。

内容的提问来源于stack exchange，提问作者zebo zhuang