概要描述
在plsql块或者存储过程中执行对分区表的动态插入,通过set_env("hive.exec.dynamic.partition",'true')
设置了动态插入参数,但是执行insert
语句时仍然会报该参数未设置的错误。
详细说明
问题现象
在plsql中通过set_env设置前置参数,开启动态插入,但是实际执行失败,报错显示未设置该参数!
DECLARE
BEGIN
SET_ENV("hive.exec.dynamic.partition",'true');
INSERT INTO PEOPLE_SINGLE_PARTITION PARTITION(borndate)
SELECT name,
sex,
nation,
id,
phonenumber,
email,
homeaddress,
borndate
FROM PEOPLE_TEXT LIMIT 10;
END
SQL 错误 [10095] [42000]: COMPILE FAILED: Semantic error: [Error 10095]
ANONYMOUS BLOCK (LINE 7, COLUMN 127, TEXT "INSERT INTO PEOPLE_SINGLE_PARTITION PARTITION(borndate) SELECT name,sex,nation,id,phonenumber,email,homeaddress,borndate FROM PEOPLE_TEXT LIMIT 10;"): Line 7:47 Dynamic partition is disabled. Either enable it by setting hive.exec.dynamic.partition=true or specify partition column values. Error encountered near token ‘borndate’
原因在于,在PLSQL编译期,语义检查的时候并没能将SET_ENV的参数设置进去(后续版本解决),可以考虑手动设置该参数为session级,或者关闭掉编译期对SQL语句语义的检查开关(plsql.compile.dml.check.semantic)
解决方法
方案1:
将SET hive.exec.dynamic.partition=true;
单独拿出来提前执行,再执行plsql块
方案2:
修改insert语句为EXECUTE IMMEDIATE
动态执行
DECLARE
BEGIN
SET_ENV("hive.exec.dynamic.partition",'true');
EXECUTE IMMEDIATE 'INSERT INTO PEOPLE_SINGLE_PARTITION PARTITION(borndate)
SELECT name,sex,nation,id,phonenumber,email,homeaddress,borndate FROM PEOPLE_TEXT LIMIT 10';
END
方案3:
执行SET plsql.compile.dml.check.semantic=false;
,再执行plsql块
注: 关于plsql.compile.dml.check.semantic参数的解释说明
Oracle中会在PLSQL编译期对其中的SQL语句做语义检查,诸如table not found之类的错误就会在编译PLSQL时就报出来,在我们TDH4.6版本之前,编译一个PLSQL,我们对其中的SQL语句只做语法检查,语义检查是在正真运行这个PLSQL时才做的。
在TDH4.6版本中,我们引入了编译期对SQL语句语义的检查,由开关plsql.compile.dml.check.semantic (true/false)控制,默认true开启。
关于该参数影响的另外一个场景,PLSQL会依赖EXECUTE IMMEDIATE语句创建表,这时编译期的检查会报INSERT语句table not found导致整个PLSQL编译不过,所以这时需要手动将上述开关关闭
SET plsql.compile.dml.check.semantic=true;
DECLARE
a STRING ;
BEGIN
execute immediate 'create table abc(id int);';
insert into abc SELECT 1 FROM system.dual;
END
java.sql.SQLException: COMPILE FAILED: Semantic error: [Error 10001] Line 5:13 Table not found. Error encountered near token ‘abc’