MySQL分区表和分桶表的操作详解（mysql分表分区）真没想到

87 0 0

目录1.创建分区表2.增删改查操作2.1 插入数据2.2 操作数据3. 二级分区表3.1 创建分区表3.2 插入数据4.动态分区5.分桶表5.1 新建分桶表5.2 插入数据5.3 既分区有分桶6 分区与分桶的区别

create table dept_partition(
deptno int,
dname string,
loc int
)
partitioned by (dt string) // 分区字段(date)
row format delimited fields terminated by ‘\t’;

1）导入本地数据

— 创建一个名字为dt=’2022-06-14’的文件夹，在其中导入数据
load data local inpath ‘/opt/module/hive/datas/dept.txt’
into table dept_partition
partition(dt=’2022-06-14′);

分区表就是先创建文件夹，然后在文件夹中写入数据

换句话说，分区表就是将一张大表分成若干个文件夹进行管理

2）插入数据

insert overwrite table dept_partition partition(dt=’2022-06-17′)
select deptno, dname, loc from dept;

insert overwrite table dept_partition
select deptno, dname, loc, ‘2022-06-18’ from dept;

1）查看分区数

show partitions dept_partition;

2）查询指定分区

select * from dept_partition where dt=’2022-06-14′;

3）增加/删除分区

alter table dept_partition add partition(dt=’2022-06-19′);
alter table dept_partition drop partition(dt=’2022-06-19′);

ps.也可以直接在liunx端输入命令增加分区

— 将18号分区复制一份，命名为13号分区

hadoop fs -cp /user/hive/warehouse/dept_partition/dt=2022-06-18

/user/hive/warehouse/dept_partition/dt=2022-06-13

ps..如果直接在网页端新建文件夹,终端不会显示新建的分区，必须修复

msck repair table dept_partition;

就是大文件夹套小文件夹

create table dept_partition2(
deptno int,
dname string,
loc int
)
partitioned by (month string, day string) // month为父目录，day为子目录
row format delimited fields terminated by ‘\t’;

load data local inpath ‘/opt/module/hive/datas/dept.txt’
into table dept_partition2 partition(month=’2022-06′, day=’15’);

insert into dept_partition2 partition(month=’2022-06′,day=’15’)
select deptno, dname, loc from dept;

普通数据无法直接转化为分区表，只能先新建新的分区表，再将旧数据插入这个新的分区表

1）创建分区表

create table emp_par(
empno int,
ename string,
job string,
salary decimal(16,2)
) partitioned by (deptno int)
row format delimited fields terminated by ‘\t’;

2）然后将数据插入这张分区表

方式一：一个分区一个分区的插入

insert into emp_par partition(deptno=10)
select empno,ename,job,sal from emp where deptno=10; //然后是11，12…

方式二：动态分区一次搞定

insert overwrite table emp_par // 不用指定分区
select empno,ename,job,sal,deptno from emp; //直接把deptno写到这里

核心语句：

clustered by (a) sorted by (b) into 4 buckets //按照a分了4个桶，桶内按照b排序

create table stu_buck(
id int,
name string
)
clustered by (id) sorted by (id) into 4 buckets //根据id的hash值按4取模
row format delimited fields terminated by ‘\t’;

查看

select * from stu_buk

可以发现分成了四个区

ps.分桶的意义:在取数的时候可以直接数据定位所在的桶，然后方便遍历，查询更高效

load data inpath ‘/datas/student.txt’ into table stu_buck;

ps.不能用本地模式,必须用hdfs模式

insert overwrite table stu_buck
select id,name from stu_ex;

create table stu_par_buck(
id int,
name string
)
partitioned by (dt string) // 先创建文件夹
clustered by (id) sorted by (id desc) into 4 buckets //然后内部分桶
row format delimited fields terminated by ‘\t’;

插入数据：

与普通的一样

insert into stu_par_buck
select id, name, ‘2022-06-14’ from stu_ex;

主键适合拿来分桶，而普通的列适合拿来分区（一般为日期）

分桶是文件，分区是文件夹

到此这篇关于MySQL分区表和分桶表的操作详解的文章就介绍到这了,更多相关MySQL分区表和分桶表内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

您可能感兴趣的文章:MySQL实现清空分区表单个分区数据深入了解MySQL中分区表的原理与企业级实战MySQL普通表如何转换成分区表MySQL分区表管理命令汇总

# 随心笔谈