hive 将一级分区表改成二级分区表 hive三级分区

Hive的分区分桶

1. Hive的分区

1.1 分区的概念

hive的分区就是在表目录下建立子目录,方便查找和关理

1.2 静态分区

静态分区就是自己动手指定分区的值。

1.2.1 建立分区表

create table if not exists t_part(
id int,
name string,
age int
)
partitioned by (birth string)
row format delimited
fields terminated by ','
stored as textfile;

--上传文件
load data local inpath '/root/user.txt' into table t_part partition (birth = '2020-07-05');

1.2.2 三级分区

create table if not exists t_part1(
id int,
name string,
age int
)
partitioned by (year string,month string,day string)
row format delimited
fields terminated by ','
stored as textfile;

load data local inpath '/root/user.txt' into table t_part1 partition (year = '2020',month = '07',day = '01');

1.2.3 常用命令

--修改分区，添加分区
ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec [LOCATION 'location'][, PARTITION partition_spec [LOCATION 'location'], ...];

partition_spec:
  : (partition_column = partition_col_value, partition_column = partition_col_value, ...)

(添加一个分区：因为分区是虚拟的，添加的这个分区中没有文件是看不到的在web界面)
ALTER TABLE `t_part` ADD PARTITION (birth='2008-08-08') location '/user/hive/warehouse/hive.db/t_part';

--显示表中的分区目录
show partitions tablename;

--删除分区(会删除分区的元数据和hdfs中的数据)
alter table t_part drop partition(birth='2020-07-05');

--修改分区的hdfs的路径(修改的是元数据中对应的分区读取数据的路径，但是并不会直接在hdfs中创建新的分区目录)
ALTER TABLE table_name [PARTITION partition_spec] SET LOCATION "new location";

ALTER TABLE `t_part` PARTITION(birth='2020-07-05')
SET LOCATION '/user/hive/warehouse/hive.db/t_part/birth=2020-07-01';

1.3 动态分区

根据已有的字段来自动分区的方式，我们称之为动态分区。
我们hive默认有两种：严格模式和非严格模式。在严格模式下，使用动态分区必须要有一个静态分区。
动态分区不能使用load加载数据，我们要从其他表中加载。

1.3.1 非严格模式下的动态分区

在非严格模式下，可以没有静态分区，全部都是动态分区

--1. 创建普通表
CREATE TABLE IF NOT EXISTS `t_dynamic_source` (
`id` int COMMENT 'userid',
`name` string COMMENT 'username',
`age` int COMMENT 'user age',
`birth` string COMMENT 'user birth'
) COMMENT 'dynamic source'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS textfile
;

--2. 创建一张分区表
CREATE TABLE IF NOT EXISTS `t_dynamic_part` (
`id` int COMMENT 'userid',
`name` string COMMENT 'username',
`age` int COMMENT 'user age'
) COMMENT 'dynamic part'
PARTITIONED BY (`birth` string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS textfile
;

--3. 加载数据到原始表t_dynamic_source
LOAD DATA LOCAL INPATH '/root/part.txt' INTO TABLE `t_dynamic_source`;

--3. 将原始表的数据插入到分区表：动态分区
FROM `t_dynamic_source`
INSERT OVERWRITE TABLE `t_dynamic_part` PARTITION(`birth`)
SELECT *;

1.3.2 严格模式下的动态分区

--1. 重新建立原表
CREATE TABLE IF NOT EXISTS `t_dynamic_source2` (
`id` int COMMENT 'userid',
`name` string COMMENT 'username',
`age` int COMMENT 'user age',
`month` string COMMENT 'user month'
) COMMENT 'dynamic source2'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS textfile
;

--2. 重新建立分区表
CREATE TABLE IF NOT EXISTS `t_dynamic_part2` (
`id` int COMMENT 'userid',
`name` string COMMENT 'username',
`age` int COMMENT 'user age'
) COMMENT 'dynamic part'
PARTITIONED BY (`year` string, `month` string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS textfile
;

--3. 加载数据到原始表t_dynamic_source
LOAD DATA LOCAL INPATH '/root/part2.txt' INTO TABLE `t_dynamic_source2`;
--4. 将原始表的数据插入到分区表：动态分区
FROM `t_dynamic_source2`
INSERT OVERWRITE TABLE `t_dynamic_part2` PARTITION(`year`='2020',`month`)
SELECT * where `id` = 4;

2. Hive的分桶

1. 作用

分桶的最大的作用是帮助我们减少笛卡尔积。

2. 使用

--1. 创建原始数据表
create table `hive`.`t_stu`(
`sno` int,
`sname` string,
`sex` string,
`sage` int,
`sclass` string)
row format delimited
fields terminated by ','
stored as textfile;

load data local inpath '/root/stu.txt' into table `hive`.`t_stu`;

--2. 分桶查询
- select * from t_stu cluster by(sno);
- select * from t_stu distribute by(sno) sort by (sno);
tip：第一句和第二句的作用是一样的

--3. 手动的设置reduce的个数
set mapreduce.job.reduces=4;

--4. 建立分桶表
create table t_buk(
sno int,
sname string,
sex string,
sage int,
sclass string)
clustered by (sno) sorted by (sname desc) into 5 buckets
row format delimited
fields terminated by ','
stored as textfile;

--5. 导入分桶的数据:LOAD DATA操作可以向分桶表插入数据，但是没有效果
insert into t_buk
select * from hive.t_stu distribute by(sno) sort by (sname desc);

3. 抽样查询

--按桶取样
tablesample (bucket x out of y [on colname])

e.g.
select * from t_buk tablesample(bucket 1 out of 4 on sno) s;
查询4个桶其中一个第一个桶中的数据

--按百分比取样
tablesample (n percent)

e.g.
select * from t_buk tablesample (0.5 percent);

--按容量来取样
select * from t_buk tablesample(2b);

--按行取样
select * from t_buk tablesample(10 rows);