（九）hive动态分区

动态分区

 

 

create table cost_dynamic_partition(

 

input_time string,

money double

)

partitioned by (name string)

ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

动态分区 分区字段不能再 表中存在

得把name去掉

动态分区列必须在SELECT语句的列中最后指定，并且与它们在PARTITION（）子句中出现的顺序相同。

SemanticException [Error 10035]: Column repeated in partitioning columns

load data local inpath '/root/cost.txt' overwrite into table cost_dynamic_partition

SemanticException [Error 10062]: Need to specify partition columns because the destination table is partitioned直接加载报错

insert into table cost_dynamic_partition PARTITION (name)

select name,input_time ,money from cost;

 

 

还得设置 set hive.exec.dynamic.partition.mode=nonstrict

Dynamic partition strict mode requires at least one static partition column. To turn this off set hive.exec.dynamic.partition.mode=nonstrict

ParseException line 1:0 cannot recognize input near 'nsert' 'into' 'table'

分区字段得写在最后

insert overwrite table cost_dynamic_partition PARTITION (name)

select input_time ,money,name from cost;

insert overwrite table cost_dynamic_partition PARTITION (name)

select input_time ,money,name as name from cost;

查询报错

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.MoveTask.

发现是mysql字符集问题 mysql 分区相关字段的字符集是latin1  不支持中文 需要设置utf-8