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

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.

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.