Get the App
SLTechnology News&Howtos  ›  Internet Technology  › 

How to choose between Hive static and dynamic partition tables in production?

Shulou Source: shulou.com Published: 2022-06-03 05:56:11 11月23日 Update

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.

Tags: Partition table data dynamic static field production query different convenient and fast in use tendency name attribute manual is made up of target syntax department demand work Apple Docker Huawei Linux macOS MariaDB Microsoft MySQL NVidia OPPO Reno Xiaomi Shulou Information Docker Microsoft Apple