How to choose between Hive static and dynamic partition tables in production?
one。 Demand
Import data to the target table according to different departments as partitions
two。 Use a static partition table to do this
1. Create a static partition table:
Create table emp_static_partition (empno int, ename string, job string, mgr int, hiredate string, sal double, comm double) PARTITIONED BY (deptno int) row format delimited fields terminated by
two。 Insert data:
Hive > insert into table emp_static_partition partition (deptno=10) select empno, ename, job, mgr, hiredate, sal, comm from emp where deptno=10
3. Query data:
Hive > select * from emp_static_partition
three。 Using dynamic partitioning tables to do this
1. Create a dynamic partition table:
Create table emp_dynamic_partition (empno int, ename string, job string, mgr int, hiredate string, sal double, comm double) PARTITIONED BY (deptno int) row format delimited fields terminated by
[note] there is no difference in syntax between dynamic partition table and static partition table.
two。 Insert data:
Hive > insert into table emp_dynamic_partition partition (deptno) select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp
[note] the field names of partitions are written at the end, and several are written. Compared with static partitions, there is no need for where.
You need to set the value of the property:
Hive > set hive.exec.dynamic.partition.mode=nonstrict
If it is not set, the error is as follows:
3. Query data:
Hive > select * from emp_dynamic_partition
Partition is listed as deptno, which realizes dynamic partition.
four。 Summary
In production, we prefer to choose dynamic partition.
There is no need to manually specify specific partitions for data import
Instead, it is up to the fields of select (the fields are written at the end, there are several to write) to decide which partition to export to, and automatically create the corresponding partition, which is more convenient and quick to use, and is used a lot in production work.