Hive静/动态分区

Joseph25

已于 2022-04-02 13:38:14 修改

阅读量524

点赞数 3

分类专栏： hive 文章标签：动态静态分区

于 2019-07-31 14:46:47 首次发布

本文链接：https://blog.csdn.net/Joseph25/article/details/97913933

版权

hive 专栏收录该内容

26 篇文章 0 订阅

订阅专栏

Hive分区表分为静态分区跟动态分区

1，静态分区

顾名思义，静态分区就是将分区写死，以指定的内容值分区

create external table tb_test(
id int,
name string,
age,int
)
partitioned by (year string ,month string)
row format delimited
fields terminated by ','
;


数据是
1,joseph,18
2,jackson,56
3,moon,28


导入数据
alter table tb_test add partition(year='2019',month='06') location '/user/hive/warehouse/joseph.db/tb_test/year=2019/month=06/';

这种将分区字段内容写死的，称为静态分区

2，动态分区

①set hive.exec.dynamic.partition.mode=nonstrict

//分区模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区

②set hive.exec.dynamic.partition=true //是否开启动态分区功能，默认false。使用动态分区时候，将该参数设成true;

如果是用spark动态分区插入数据，那就添加config就行

.config("hive.exec.dynamic.partition.mode","nonstrict")
.config("hive.exec.dynamic.partition","true")

create external table product_fu(
XFDJXH string comment '销货方登记序号',
DJ string comment '单价',
HWMC string comment '货物或应税劳务名称',
QDBZ string comment '清单标志',
GF_QX string comment '购方所属区县',
KPYF string comment '开票月份'
KPRQ_Y string comment '开票日期_年',
KPRQ_M string comment '开票日期_月'
)
row format SERDE 'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe' WITH SERDEPROPERTIES ("field.delim"="@@@")
tblproperties("skip.header.line.count"="1")
;


create external table product(
XFDJXH string comment '销货方登记序号',
DJ string comment '单价',
HWMC string comment '货物或应税劳务名称',
QDBZ string comment '清单标志',
GF_QX string comment '购方所属区县',
KPYF string comment '开票月份',
KPRQ_Y string comment '开票日期_年',
KPRQ_M string comment '开票日期_月'
)
partitioned by (year string ,month string)
row format delimited
fields terminated by ','
;


insert overwrite table product partition(year,month)
select
XFDJXH,
DJ,
HWMC,
QDBZ,
GF_QX,
KPYF,
KPRQ_Y,
KPRQ_M,
KPRQ_Y as year,
KPRQ_M as month from product_fu;


product_fu表里有8个字段，product创建的年月两个分区字段，
因此在向product添加数据的时候，多查的两个字段，会默认为分区字段

如果当添加动态分区时报错

Task with the most failures(4): 
-----
Task ID:
  task_1564479403909_0006_m_000009

URL:
  http://0.0.0.0:8088/taskdetails.jsp?jobid=job_1564479403909_0006&tipid=task_1564479403909_0006_m_000009
-----
Diagnostic Messages for this Task:
Error: java.lang.RuntimeException: Error in configuring object
	at org.apache.hadoop.util.ReflectionUtils.setJobConf(ReflectionUtils.java:109)
	at org.apache.hadoop.util.ReflectionUtils.setConf(ReflectionUtils.java:75)
	at org.apache.hadoop.util.ReflectionUtils.newInstance(ReflectionUtils.java:133)
	at org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:446)
	at org.apache.hadoop.mapred.MapTask.run(MapTask.java:343)
	at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:163)
	at java.security.AccessController.doPrivileged(Native Method)
	at javax.security.auth.Subject.doAs(Subject.java:422)
	at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1656)
	at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:158)
Caused by: java.lang.reflect.InvocationTargetException
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.hadoop.util.ReflectionUtils.setJobConf(ReflectionUtils.java:106)
	... 9 more
Caused by: java.lang.RuntimeException: Error in configuring object
	at org.apache.hadoop.util.ReflectionUtils.setJobConf(ReflectionUtils.java:109)
	at org.apache.hadoop.util.ReflectionUtils.setConf(ReflectionUtils.java:75)
	at org.apache.hadoop.util.ReflectionUtils.newInstance(ReflectionUtils.java:133)
	at org.apache.hadoop.mapred.MapRunner.configure(MapRunner.java:38)
	... 14 more
Caused by: java.lang.reflect.InvocationTargetException
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.hadoop.util.ReflectionUtils.setJobConf(ReflectionUtils.java:106)
	... 17 more
Caused by: java.lang.RuntimeException: Map operator initialization failed
	at org.apache.hadoop.hive.ql.exec.mr.ExecMapper.configure(ExecMapper.java:137)
	... 22 more
Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.ClassNotFoundException: Class org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe not found
	at org.apache.hadoop.hive.ql.exec.MapOperator.getConvertedOI(MapOperator.java:329)
	at org.apache.hadoop.hive.ql.exec.MapOperator.setChildren(MapOperator.java:364)
	at org.apache.hadoop.hive.ql.exec.mr.ExecMapper.configure(ExecMapper.java:106)
	... 22 more
Caused by: java.lang.ClassNotFoundException: Class org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe not found
	at org.apache.hadoop.conf.Configuration.getClassByName(Configuration.java:2060)
	at org.apache.hadoop.hive.ql.plan.PartitionDesc.getDeserializer(PartitionDesc.java:175)
	at org.apache.hadoop.hive.ql.exec.MapOperator.getConvertedOI(MapOperator.java:295)
	... 24 more

Container killed by the ApplicationMaster.
Container killed on request. Exit code is 143
Container exited with a non-zero exit code 143


2019-07-31T09:13:19,173 INFO  [HiveServer2-Background-Pool: Thread-1315]: impl.YarnClientImpl (:()) - Killed application application_1564479403909_0006
2019-07-31T09:13:19,177 ERROR [HiveServer2-Background-Pool: Thread-1315]: ql.Driver (:()) - FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask
2019-07-31T09:13:19,177 INFO  [HiveServer2-Background-Pool: Thread-1315]: ql.Driver (:()) - MapReduce Jobs Launched: 
2019-07-31T09:13:19,180 WARN  [HiveServer2-Background-Pool: Thread-1315]: mapreduce.Counters (:()) - Group FileSystemCounters is deprecated. Use org.apache.hadoop.mapreduce.FileSystemCounter instead
2019-07-31T09:13:19,180 INFO  [HiveServer2-Background-Pool: Thread-1315]: ql.Driver (:()) - Stage-Stage-1: Map: 39   HDFS Read: 0 HDFS Write: 0 FAIL
2019-07-31T09:13:19,180 INFO  [HiveServer2-Background-Pool: Thread-1315]: ql.Driver (:()) - Total MapReduce CPU Time Spent: 0 msec
2019-07-31T09:13:19,180 INFO  [HiveServer2-Background-Pool: Thread-1315]: ql.Driver (:()) - Completed executing command(queryId=root_20190731091231_c0177d39-08f6-4dd1-ae0e-5a26fe8a21ad); Time taken: 47.815 sec
onds
2019-07-31T09:13:19,182 ERROR [HiveServer2-Background-Pool: Thread-1315]: operation.Operation (:()) - Error running hive query: 
org.apache.hive.service.cli.HiveSQLException: Error while processing statement: FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask
	at org.apache.hive.service.cli.operation.Operation.toSQLException(Operation.java:387)
	at org.apache.hive.service.cli.operation.SQLOperation.runQuery(SQLOperation.java:237)
	at org.apache.hive.service.cli.operation.SQLOperation.access$300(SQLOperation.java:90)
	at org.apache.hive.service.cli.operation.SQLOperation$2$1.run(SQLOperation.java:299)
	at java.security.AccessController.doPrivileged(Native Method)

这是因为咱们以多字段分割的时候用到了contrib里的MultiDelimitSerDe，加载数据的时候系统去找hive-contrib-2.1.0.jar，

因此你把这个jar包放到hdfs上，这样就可以了，网上还有的说放到hive-site.xml里，我试了没起作用，既然走mr，我放到mapred-site.xml上也没有用，然后在你的hive窗口

0: jdbc:hive2://node03:10000> add jar hdfs://ns1/user/hive/warehouse/hive-contrib-2.1.0.jar;