Hive的分区表

1、创建分区表：分区表的分区是虚拟的

create table if not exists emp_part(
empno int,
enname string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)
partitioned by (date string)
row format delimited fields terminated by '\t';

 

2、用原来普通表的方式加载数据，发现报错：

hive (db_emp)> load data local inpath '/opt/data/emp' into table emp_part;
FAILED: SemanticException [Error 10062]: Need to specify partition columns because the destination table is partitioned

正确的命令如下：

hive (db_emp)> load data local inpath '/opt/data/emp' into table emp_part partition (date='201807');
Loading data to table db_emp.emp_part partition (date=201807)
Partition db_emp.emp_part{date=201807} stats: [numFiles=1, numRows=0, totalSize=728, rawDataSize=0]
OK
Time taken: 0.831 seconds

查看hdfs：

 查询某个分区：

hive (db_emp)> select * from emp_part where date='201805';
OK
emp_part.empno	emp_part.enname	emp_part.job	emp_part.mgr	emp_part.hiredate	emp_part.sal	emp_part.comm	emp_part.deptno	emp_part.date
7499	ALLEN 	SALESMAN 	7698	1981-02-20	1600.0	300.0	30	201805
7521	WARD  	SALESMAN 	7698	1981-02-22	1250.0	500.0	30	201805
7566	JONES 	MANAGER  	7839	1981-04-02	2975.0	0.0	20	201805
7654	MARTIN	SALESMAN 	7698	1981-09-28	1250.0	1400.0	30	201805
7698	BLAKE 	MANAGER  	7839	1981-05-01	2850.0	0.0	30	201805
7782	CLARK 	MANAGER  	7839	1981-06-09	2450.0	0.0	10	201805
7788	SCOTT 	ANALYST  	7566	1987-07-13	3000.0	0.0	20	201805
7839	KING  	PRESIDENT	NULL	1981-11-07	5000.0	0.0	10	201805
7844	TURNER	SALESMAN 	7698	1981-09-08	1500.0	0.0	30	201805
7876	ADAMS 	CLERK    	7788	1987-07-13	1100.0	0.0	20	201805
7900	JAMES 	CLERK    	7698	1981-12-03	950.0	0.0	30	201805
7902	FORD  	ANALYST  	7566	1981-12-03	3000.0	0.0	20	201805
7934	MILLER	CLERK    	7782	1982-01-23	1300.0	0.0	10	201805
Time taken: 0.351 seconds, Fetched: 13 row(s)