2021-10-19 hive

动态分区和静态分区的区别

1.动态分区和静态分区都需要自己去指定分区的字段，静态分区同时需要去指定分区的内容

2.动态分区不能用load data的方法进行数据的导入，而静态分区可以通过load data和insert overwrite两种方法导入数据

3.进行insert overwrite 导入的时候，静态不需要将分区字段的内容在select中查询出来的，但是动态需要查询出这个字段

4.动态分区在使用之前，需要打开动态分区的开关，并且要打开非严格模式的开光。

分桶使用的场景

分桶在表链接的时候使用，可以加快两张表联合查询的速度，且只有当联合表格的分桶数量相等，或者分桶数量是倍数关系的时候，才有加速效果

分桶可以让表链接加速，是因为分桶可以让表连接的笛卡尔积数据量变少。

#!/bin/bash
echo 用shell脚本实现分区的加载
sleep 2s
#删除已存在的文件
rm -rvf /root/log*.txt

#1、生成log1.txt
echo 100,登录,20210510>>/root/log1.txt
echo 101,下单,20210510>>/root/log1.txt
echo 102,退出,20210510>>/root/log1.txt
echo 103,登录,20210510>>/root/log1.txt
echo 104,退出,20210510>>/root/log1.txt
echo 105,登录,20210510>>/root/log1.txt
echo 106,下单,20210510>>/root/log1.txt
echo 107,退出,20210510>>/root/log1.txt

#2、生成log2.txt
echo 1100,登录,20210511>>/root/log2.txt
echo 1101,下单,20210511>>/root/log2.txt
echo 1102,退出,20210511>>/root/log2.txt
echo 1103,登录,20210511>>/root/log2.txt
echo 1104,退出,20210511>>/root/log2.txt
echo 1105,登录,20210511>>/root/log2.txt
echo 1106,下单,20210511>>/root/log2.txt
echo 1107,退出,20210511>>/root/log2.txt

#3、生成log3.txt
echo 2100,登录,20210512>>/root/log3.txt
echo 2101,下单,20210512>>/root/log3.txt
echo 2102,退出,20210512>>/root/log3.txt
echo 2103,登录,20210512>>/root/log3.txt
echo 2104,退出,20210512>>/root/log3.txt
echo 2105,登录,20210512>>/root/log3.txt
echo 2106,下单,20210512>>/root/log3.txt
echo 2107,退出,20210512>>/root/log3.txt

sleep 3s
#新建目录并上传文件
#先删除已经存在的目录
hadoop fs -rm -r /logs

#新建目录
hadoop fs -mkdir /logs
hadoop fs -mkdir /logs/20210510
hadoop fs -mkdir /logs/20210511
hadoop fs -mkdir /logs/20210512

#切换路径
cd /root
hadoop fs -put log1.txt /logs/20210510
hadoop fs -put log2.txt /logs/20210511
hadoop fs -put log3.txt /logs/20210512

#执行hive3.hql脚本
hive -f /root/birth/hive3.hql

--用vi新建hive3.hql脚本
--切换数据库
use bigdate007;

--删除表
drop table if exists ext_logs_p;

--在bigdata 数据库中建表
create external table bigdate007.ext_logs_p(
id int,
name string,
operate string)
partitioned by(date_time string)
row format delimited fields terminated by ','
location '/logs';

--挂载分区
alter table bigdate007.ext_logs_p add partition (date_time='20210510') location '/logs/20210510';
alter table bigdate007.ext_logs_p add partition (date_time='20210511') location '/logs/20210511';
alter table bigdate007.ext_logs_p add partition (date_time='20210512') location '/logs/20210512';


1.先创建一个带有分桶属性的表格
create table s_cl(
id int,
name string,
sex string,
age int)
clustered by(age) into 4 buckets
row format delimited fields terminated by ',';

insert into s_cl values(15,'wangxiaoer15','男',15);

--2.复制一个相同结构的备份表格
create table s_cl_tmp like s_cl;

--3.将文件的内容映射到备份表中
load data local inpath '/root/s.txt' into table bigdate007.s_cl_tmp;

--4.使用查询的方式对备份表进行分桶
insert overwrite table s_cl select * from s_c1_tmp cluster by age;

#!/bin/bash
echo 开始运行脚本
cd /root/
rm -vf stu_c.txt
sleep 2s
echo 1001,lilei,男,18>>stu_c.txt
echo 1002,lucy,女,16>>stu_c.txt
echo 1003,tom,男,17>>stu_c.txt
echo 1004,jack,男,18>>stu_c.txt
echo 1005,eve,女,18>>stu_c.txt
echo 1006,allen,男,18>>stu_c.txt
echo 1007,miller,男,19>> stu_c.txt
echo 1008,flower,女,16>> stu_c.txt
echo 1009,adam,男,17>> stu_c.txt
echo 1010,toly,男,16>> stu_c.txt
echo 1011,steven,男,17>> stu_c.txt
echo 1012,bob,男,18>> stu_c.txt
echo 1013,lucky,女,17>> stu_c.txt

sleep 2s
hadoop fs -rm -r /data
hadoop fs -mkdir /data
hadoop fs -put stu_c.txt /data

sleep 2s
#执行hive5.hql
hive -f /root/hive5.hql

echo 脚本执行完毕！！！

hive5.hql脚本

--1.删除表
use bigdate007;

drop table if exists stu_info_c;
drop table if exists stu_info_c_tmp;

--2.新建表
create table stu_info_c(
id int,
name string,
sex string,
age int)
clustered by(age) into 4 buckets
row format delimited fields terminated by ',';

--3.再复制一个和分桶表相同结构的临时表，只有结构不会有内容
create table stu_info_c_tmp like stu_info_c;

--4.在临时表中添加表格的所有的数据

load data inpath '/data/stu_c.txt' into table bigdate007.stu_info_c_tmp;

--5.使用insert复制表格的内容到分桶表中，复制的同时进行数据的分桶，cluster by (age)
insert overwrite table stu_info_c
select * from stu_info_c_tmp cluster by (age);