Hive分区分桶基本操作

重置HIVE

登录mysql

[root@m ~]# mysql -uroot -p1

->MySQL

drop database hive;
create database hive;
--修改数据库编码
alter database 
grant all on hive.* to hive@'%' identified by '1';
grant all on hive.* to hive@localhost identified by '1';
flush privileges;

->Linux

[root@m ~]# hadoop fs -rm -r /user/hive/warehouse/*

HIVE分区分桶简述

[root@m ~]# hive

->hive

show tables;

--分区字段和分桶字段暂时不要使用中文

--创建一个分区表,分区字段使用的是假字段(不必要定义为表格字段)
create table student(sid int, sname string)
partitioned by (sgender string)
row format delimited
fields terminated by '-';

--从文件载入数据
load data local inpath '/root/student' into table student partition (gender='man');

load data local inpath '/root/student' into table student partition (gender='woman');

--从分区表中查询数据
select * from student where gender='man';
select * from student where gender='woman';
select * from student;

--创建一个分区+分桶表
create table bstudent(id int, name string)
partitioned by (gender string)
clustered by (id) sorted by (id asc) into 2 buckets
row format delimited fields terminated by '-'
stored as textfile;

--修改环境变量,强制开启分桶
set hive.enforce.bucketing = true;

--向分区分桶表中插入数据
insert overwrite table bstudent partition (gender='man') select id,name from student where gender='man';
insert overwrite table bstudent partition (gender='woman') select id,name from student where gender='woman';

--从分区分桶表中抽样查询数据
select * from bstudent tablesample (bucket 1 out of 2 on id);

--从分区分桶表中查询所有数据
select * from bstudent;