MySQL分区表和分桶表的操作详解

叶从峰

1.创建分区表

1. create table dept_partition(
   
2.     deptno int,
   
3.     dname string,
   
4.     loc int
   
5. )
   
6. partitioned by (dt string)  // 分区字段(date)
   
7. row format delimited fields terminated by '\t';

复制代码

2.增删改查操作


2.1 插入数据

1）导入本地数据

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

复制代码

分区表就是先创建文件夹，然后在文件夹中写入数据
换句话说，分区表就是将一张大表分成若干个文件夹进行管理
2）插入数据

1. insert overwrite table dept_partition partition(dt='2022-06-17')
   
2. select deptno, dname, loc from dept;

复制代码

1. insert overwrite table dept_partition
   
2. select deptno, dname, loc, '2022-06-18' from dept;

复制代码

2.2 操作数据

1）查看分区数

1. show partitions dept_partition;

复制代码

2）查询指定分区

1. select * from dept_partition where dt='2022-06-14';

复制代码

3）增加/删除分区

1. alter table dept_partition add partition(dt='2022-06-19');
   
2. alter table dept_partition drop partition(dt='2022-06-19');

复制代码

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

1. -- 将18号分区复制一份，命名为13号分区hadoop fs -cp /user/hive/warehouse/dept_partition/dt=2022-06-18 /user/hive/warehouse/dept_partition/dt=2022-06-13

复制代码

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

1. msck repair table dept_partition;

复制代码

3. 二级分区表

就是大文件夹套小文件夹

3.1 创建分区表

1. create table dept_partition2(
   
2.     deptno int,
   
3.     dname string,
   
4.     loc int
   
5. )
   
6. partitioned by (month string, day string)  // month为父目录，day为子目录
   
7. row format delimited fields terminated by '\t';

复制代码

3.2 插入数据

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

复制代码

1. insert into dept_partition2 partition(month='2022-06',day='15')
   
2. select deptno, dname, loc from dept;

复制代码

4.动态分区

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

1. create table emp_par(
   
2.     empno int,
   
3.     ename string,
   
4.     job string,
   
5.     salary decimal(16,2)
   
6. ) partitioned by (deptno int)
   
7. row format delimited fields terminated by '\t';

复制代码

2）然后将数据插入这张分区表
方式一：一个分区一个分区的插入

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

复制代码

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

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

复制代码

5.分桶表

核心语句：

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

复制代码

5.1 新建分桶表

1. create table stu_buck(
   
2.     id int,
   
3.     name string
   
4. )
   
5. clustered by (id) sorted by (id) into 4 buckets  //根据id的hash值按4取模
   
6. row format delimited fields terminated by '\t';

复制代码

查看

1. select * from stu_buk

复制代码

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

5.2 插入数据

1. load data inpath '/datas/student.txt' into table stu_buck;

复制代码

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

1. insert overwrite table stu_buck
   
2. select id,name from stu_ex;

复制代码

5.3 既分区有分桶

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

复制代码

插入数据：
与普通的一样

1. insert into stu_par_buck
   
2. select id, name, '2022-06-14' from stu_ex;

复制代码

6 分区与分桶的区别

主键适合拿来分桶，而普通的列适合拿来分区（一般为日期）
分桶是文件，分区是文件夹
到此这篇关于MySQL分区表和分桶表的操作详解的文章就介绍到这了,更多相关MySQL分区表和分桶表内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

来源:https://www.jb51.net/article/284061.htm
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！