Oozie工作流中组合Insert语句与CTE的写法及执行失败问题咨询

原代码语法错误点 #

• 语序错误：Hive SQL语法规定WITH开头的CTE定义必须放在INSERT语句之前，原代码把WITH写在INSERT和SELECT中间，会直接触发语法解析失败
• 冗余标点：CTE定义块test as (select * from B)后多写了一个逗号，SQL解析器会认为后面还有其他CTE定义，和后续的SELECT语句冲突
• SELECT语法错误：查询列时重复写了SELECT关键字，多列查询只需要在开头写一次SELECT，列之间用逗号分隔即可；另外列名col A/col B包含空格，如果不是带反引号转义的特殊命名，会被判定为非法标识符
• 动态分区字段缺失：开启了非严格动态分区模式，写入分区表时要求SELECT语句返回的最后一列必须和PARTITION()中声明的分区字段date对应，原代码查询结果没有携带date字段，运行时会报分区列数不匹配错误
• 冗余关键字：from table B中table是多余关键字，除非表名本身就叫table B（这种情况需要用反引号包裹转义），否则直接写表名即可

修正后可直接在Oozie中运行的代码 #

-- 参数配置统一放在SQL最头部，Oozie会按顺序逐行提交执行
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

-- CTE定义必须放在INSERT语句之前
with test as (
    select * from B
)
insert into table A partition(date)
select
    colA as name1,
    colB as name2,
    date -- 动态分区字段必须放在SELECT列的最后一位，和分区声明顺序对应
from test;

通用编写规范 #

• 固定语句顺序：CTE搭配INSERT的语法顺序必须为SET会话参数 -> WITH CTE定义块 -> INSERT写入语句 -> SELECT查询逻辑，WITH子句不能插在INSERT和SELECT中间
• CTE编写规则：如果需要定义多个CTE，CTE之间用逗号分隔，最后一个CTE定义结束后不要加逗号，直接衔接后续的INSERT/SELECT语句
• 动态分区匹配规则：写入动态分区时，SELECT返回的字段顺序必须和目标表的非分区字段、分区字段顺序完全一致，分区字段必须放在SELECT返回列的末尾，数量和PARTITION()中声明的分区数完全对齐
• 标识符规范：表名、列名尽量不要使用空格、SQL关键字，如果必须使用，要用反引号`包裹做转义，避免解析失败
• Oozie适配注意：Oozie的Hive Action解析SQL时，每条完整语句要以分号结尾，不要在语句中间插入无意义的特殊分隔符，参数设置类语句统一放在SQL最开头，减少解析异常概率

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