Shulou(Shulou.com)06/03 Report--

This article introduces the relevant knowledge of "the difference between static and dynamic partitions of Hive". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Static partition: if the value of the partition is determined, it is called a static partition. When you add a partition or load partition data, the partition name is already specified.

Create table if not exists day_part1 (

Uid int

Uname string

)

Partitioned by (year int,month int)

Row format delimited fields terminated by'\ t'

# # loading data to specify Partition

Load data local inpath'/ root/Desktop/student.txt' into table day_part1

Partition (year=2017,month=04)

# # adding Partition to specify Partition name

Alter table day_part1 add partition (year=2017,month=1)

Partition (year=2016,month=12)

two。 Dynamic partitioning: the value of the partition is indeterminate and is determined by input data

2.1 relevant properties of dynamic partitions:

Hive.exec.dynamic.partition=true: whether dynamic partitioning is allowed

Hive.exec.dynamic.partition.mode=strict: partition mode settin

Strict: at least one static partition is required

Nostrict: can be all dynamic partitions

Hive.exec.max.dynamic.partitions=1000: the maximum number of dynamic partitions allowed

Hive.exec.max.dynamic.partitions.pernode = 100

Maximum partition allowed to be created by mapper/reducer on a single node

2.2 Operation of dynamic partitioning

# # creating temporary tables

Create table if not exists tmp

(uid int

Commentid bigint

Recommentid bigint

Year int

Month int

Day int)

Row format delimited fields terminated by'\ t'

# # loading data

Load data local inpath'/ root/Desktop/comm' into table tmp

# # creating dynamic Partition Table

Create table if not exists dyp1

(uid int

Commentid bigint

Recommentid bigint)

Partitioned by (year int,month int,day int)

Row format delimited fields terminated by'\ t'

# # strict mode

Insert into table dyp1 partition (year=2016,month,day)

Select uid,commentid,recommentid,month,day from tmp

# # non-strict mode

# # setting non-strict Mode dynamic Partition

Set hive.exec.dynamic.partition.mode=nostrict

# # creating dynamic Partition Table

Create table if not exists dyp2

(uid int

Commentid bigint

Recommentid bigint)

Partitioned by (year int,month int,day int)

Row format delimited fields terminated by'\ t'

# # loading data for non-strict Mode dynamic Partition

Insert into table dyp2 partition (year,month,day)

Select uid,commentid,recommentid,year,month,day from tmp

3. Pay attention to the details of the partition

(1), try not to use dynamic partitioning, because dynamic partitioning will allocate the number of reducer for each partition, when the number of partitions is large, the number of reducer will increase, which is a disaster for the server.

(2), the difference between dynamic partitions and static partitions, static partitions will create the partition with or without data, dynamic partitions will be created with the result set, otherwise they will not be created.

(3) the strict mode of hive dynamic partition and the strict mode of hive.mapred.mode provided by hive.

Hive provides us with a strict model: to prevent users from accidentally submitting malicious hql

Hive.mapred.mode=nostrict: strict

If the schema value is strict, the following three types of queries are blocked:

(1) for the query of partition table, the filter field in where is not a partition field.

(2) Cartesian product join query, join query statement, without on condition or where condition.

(3) for order by queries, queries with order by do not have limit statements.

This is the end of the content of "the difference between static and dynamic partitions of Hive". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.