hive insert、select组合动态插入分区表_hive insert select

使用waterdrop操作hive的时候遇到一个问题，按照sql的insert、select组合插入应该使用下面的语句：

INSERT INTO table t_ads_gsddy_jzfdl_day

SELECT a.senid AS senid,a.jlsj AS jlsj,a.v AS v,a.name AS name,'水电' AS stage ,partition_year AS partition_year FROM t_ads_gsddy_sjjzfdl_day a

运行发现报错，查看表结构之后，发现要插入的表有分区，于是修改之后：

INSERT INTO table t_ads_gsddy_jzfdl_day PARTITION(partition_year)

SELECT a.senid AS senid,a.jlsj AS jlsj,a.v AS v,a.name AS name,'水电' AS stage ,partition_year AS partition_year FROM t_ads_gsddy_sjjzfdl_day a

但是waterdrop却又报错了，提示设置partition=true或者使用静态分区

思考了一下，发现默认是静态插入，所以采用了以下语句

SET hive.exec.dynamic.partition=true;                                 --开启动态分区，默认是false

SET hive.exec.dynamic.partition.mode=nonstric;                 -- 开启允许所有分区都是动态的，否则必须要有静态分区才能使用。

INSERT INTO table t_ads_gsddy_jzfdl_day PARTITION(partition_year)

SELECT a.senid AS senid,a.jlsj AS jlsj,a.v AS v,a.name AS name,'水电' AS stage ,partition_year AS partition_year FROM t_ads_gsddy_sjjzfdl_day a

最后提示插入成功！！！

回头试了下，在开启了动态分区的情况下，不指定分区使用常规的insert、select进行插入，发现也是可以的

SET hive.exec.dynamic.partition=true;

SET hive.exec.dynamic.partition.mode=nonstric;

INSERT INTO table t_ads_gsddy_jzfdl_day

SELECT a.senid AS senid,a.jlsj AS jlsj,a.v AS v,a.name AS name,'水电' AS stage ,partition_year AS partition_year FROM t_ads_gsddy_sjjzfdl_day a