使用CTE执行INSERT OVERWRITE DIRECTORY时无法创建文件问题

问题背景 #

尝试通过CTE查询结果将数据写入指定用户目录，单独使用INSERT OVERWRITE时能正常创建文件，但结合CTE后，无论把INSERT块放在语句开头还是WITH子句的SELECT之前，都触发相同的解析错误。

示例数据 #

CREATE TABLE IF NOT EXISTS emp.employee (
 id int,
 name string,
 age int,
 gender string )
 COMMENT '员工表'
 ROW FORMAT DELIMITED
 FIELDS TERMINATED BY ',';

CREATE TABLE IF NOT EXISTS emp.salary (
 id int,
 salary int)
 COMMENT '员工薪资表'
 ROW FORMAT DELIMITED
 FIELDS TERMINATED BY ',';

INSERT INTO emp.employee values(1,'scott',23,'M');
INSERT INTO emp.employee values(2,'raman',50,'M');
INSERT INTO emp.salary values(1,30000);
INSERT INTO emp.salary values(2,40000);

错误的查询语句 #

WITH cte_age AS
(
SELECT 
    lowest_age = MIN(age)  -- Hive不支持这种赋值方式
,   id

FROM emp.employee 

GROUP BY
    id
)

INSERT OVERWRITE DIRECTORY '/user/doe/lowest_age'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
STORED AS TEXTFILE;

SELECT
    la.id
,   la.lowest_age
,   s.salary

FROM cte_lowest_age as la  -- CTE定义的是cte_age，这里引用名称错误
INNER JOIN emp.salary AS s ON
    la.id = s.id;

报错信息 #

即使把INSERT OVERWRITE块放在语句开头，仍出现以下错误：

Error while compiling statement: FAILED: ParseException line 20:18 cannot recognize input near '<EOF>' '<EOF>' '<EOF>' in statement

翻译：编译语句时出错：失败：解析异常，第20行第18位无法识别语句中的'' '' ''输入

问题分析与修正 #

错误根源有三点：

• CTE引用名称不匹配：定义的CTE是cte_age，但查询时用了cte_lowest_age，名称不一致。
• Hive列别名语法错误：Hive不支持列名=聚合函数的赋值方式，必须用聚合函数 AS 列名。
• INSERT OVERWRITE语法结构错误：Hive要求INSERT OVERWRITE DIRECTORY必须直接跟查询语句，不能将INSERT块和SELECT块分开写，CTE需要放在INSERT语句之前作为整体的一部分。

正确的查询语句 #

WITH cte_age AS
(
    SELECT 
        MIN(age) AS lowest_age,
        id
    FROM emp.employee 
    GROUP BY id
)
INSERT OVERWRITE DIRECTORY '/user/doe/lowest_age'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
STORED AS TEXTFILE
SELECT
    la.id,
    la.lowest_age,
    s.salary
FROM cte_age as la
INNER JOIN emp.salary AS s ON la.id = s.id;

或者把CTE放在INSERT块内的SELECT之前，效果一致：

INSERT OVERWRITE DIRECTORY '/user/doe/lowest_age'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
STORED AS TEXTFILE
WITH cte_age AS
(
    SELECT 
        MIN(age) AS lowest_age,
        id
    FROM emp.employee 
    GROUP BY id
)
SELECT
    la.id,
    la.lowest_age,
    s.salary
FROM cte_age as la
INNER JOIN emp.salary AS s ON la.id = s.id;

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