动态分区
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