DQL-02-分区表和分桶表和抽样查询

-- 创建分区表
create table dept_partition(
deptno int, dname string, loc string
)
partitioned by (day string)
row format delimited fields terminated by '\t';
-- 指定分区导入数据
load data local inpath '/opt/module/hive/datas/dept_20200401.log'
    into table dept_partition partition (day='20200401');
load data local inpath '/opt/module/hive/datas/dept_20200402.log'
    into table dept_partition partition(day='20200402');
load data local inpath '/opt/module/hive/datas/dept_20200403.log'
    into table dept_partition partition(day='20200403');
-- 查询分区表中数据
-- 单分区查询
select * from dept_partition where day='20200401';
-- 多表联合查询
select * from dept_partition where day='20200401'
union
select * from dept_partition where day='20200402'
union
select * from dept_partition where day='20200403';
-- 等价于
select * from dept_partition
where day='20200401' or day='20200402' or day='20200403';
-- 增加单个分区
alter table dept_partition add partition (day='20200404');
-- 增加多个分区
alter table dept_partition
    add partition (day='20200405') partition (day='20200406');
-- 删除单个分区
alter table dept_partition drop partition (day='20200406');
-- 删除多个分区
-- 增加多个分区的时候不用加,
-- 删除多个分区的时候加,
alter table dept_partition drop partition (day='20200404'),
    partition (day='20200405');
-- 查看表有多少分区
show partitions dept_partition;
-- 查看分区表结构
desc formatted dept_partition;
-- 二级分区
create table dept_partition2(
    deptno int, dname string, loc string
)
partitioned by (day string, hour string)
row format delimited fields terminated by '\t';
-- 加载数据到二级分区表中
load data local inpath '/opt/module/hive/datas/dept_20200401.log'
into table dept_partition2 partition(day='20200401', hour='12');
-- 查询分区数据(其实就是多了个筛选条件)
select * from dept_partition2 where  day='20200401' and hour='12';
-- 把数据直接上传到分区目录上，让分区表和数据关联
-- ！！！①上传后修复
-- 先创建文件目录
dfs -mkdir -p /user/hive/warehouse/db_hive.db/dept_partition2/day=20200401/hour=13;
-- 再上传数据
dfs -put /opt/module/hive/datas/dept_20200401.log
/user/hive/warehouse/db_hive.db/de[t_partition2/day=20200401/hour=13;
-- 此时查询不到刚上传的数据
select * from dept_partition2 where day='20200401' and hour='13';
-- 执行修复命令
msck repair table dept_partition2;
-- 再次查询显示数据
select * from dept_partition2 where day='20200401' and hour='13';
-- ！！！②上传后添加分区
-- 先创建文件目录
dfs -mkdir -p /user/hive/warehouse/db_hive.db/dept_partition2/day=20200401/hour=14;
-- 再上传数据
dfs -put /opt/module/hive/datas/dept_20200401.log
/user/hive/warehouse/db_hive.db/de[t_partition2/day=20200401/hour=14;
-- 添加分区，此时查询有数据
alter table dept_partition2 add partition(day='201709',hour='14');
-- ！！！③创建文件夹后load数据到分区
dfs -mkdir -p /user/hive/warehouse/db_hive.db/dept_partition2/day=20200401/hour=15;
load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table
 dept_partition2 partition(day='20200401',hour='15');
-- 动态分区:数据库自动会根据分区字段的值，将数据插入到相应的分区中
-- 开启动态分区
hive.exec.dynamic.partition=true
-- 设置非严格模式：
-- 动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，
-- nonstrict模式表示允许所有的分区字段都可以使用动态分区
hive.exec.dynamic.partition.mode=nonstrict
-- 在所有执行MR的节点上，最大一共可以创建多少个动态分区。默认1000
hive.exec.max.dynamic.partition=1000
-- 在每个执行MR的节点上，最大可以创建多少个动态分区
hive.exec.max.dynamic.partitions.pernode=100
-- 整个MR Job中，最大可以创建多少个HDFS文件。默认100000
hive.exec.max.created.files=100000
-- 当有空分区生成时，是否抛出异常
hive.error.on.empty.partition=false
-- 将dept表中的数据按照地区（loc字段），
-- 插入到目标表dept_partition的相应分区中。
create table dept_partition_dy(id int,name string)
partitioned by (loc int)
row format delimited fields terminated by '\t';
-- 设置动态分区
set hive.exec.dynamic.partition.mode=nonstrict;
insert into table dept_partition_dy partition (loc)
select deptno,dname,loc from db_hive.dept;
-- 查看分区情况
show partitions dept_partition_dy;
select * from db_hive.dept;

-- 分桶表:Hive的分桶采用对分桶字段的值进行哈希，
-- 然后除以桶的个数求余的方 式决定该条记录存放在哪个桶当中
-- 分区针对的是数据的存储路径；分桶针对的是数据文件。
create table backet(id int,name string)
clustered by (id) into 4 buckets
row format delimited fields terminated by '\t';
-- 查看表结构,显示分了4个桶
desc formatted backet;
load data local inpath '/opt/module/hive/datas/backet.txt'
    into table backet;
-- 查看分桶数据
select * from backet;
-- 抽样查询:x的值必须小于等于y的值
select * from backet tablesample (bucket 1 out of 4 on id);