快捷導(dǎo)航

MySQL分區(qū)表和分桶表的操作詳解

更新時(shí)間：2023年05月11日 11:59:26 作者：菠蘿橡皮刀

這篇文章主要介紹了MySQL分區(qū)表和分桶表的操作，分區(qū)表和分桶表都是數(shù)據(jù)庫(kù)中的表，用于對(duì)數(shù)據(jù)進(jìn)行分區(qū)或分桶存儲(chǔ)，以提高數(shù)據(jù)庫(kù)的性能和查詢效率，需要詳細(xì)了解可以參考下文

1.創(chuàng)建分區(qū)表

create table dept_partition(
    deptno int,
    dname string,
    loc int
)
partitioned by (dt string)  // 分區(qū)字段(date)
row format delimited fields terminated by '\t';

2.增刪改查操作

2.1 插入數(shù)據(jù)

1）導(dǎo)入本地?cái)?shù)據(jù)

-- 創(chuàng)建一個(gè)名字為dt='2022-06-14'的文件夾，在其中導(dǎo)入數(shù)據(jù)
load data local inpath '/opt/module/hive/datas/dept.txt' 
into table dept_partition 
partition(dt='2022-06-14');

分區(qū)表就是先創(chuàng)建文件夾，然后在文件夾中寫入數(shù)據(jù)

換句話說(shuō)，分區(qū)表就是將一張大表分成若干個(gè)文件夾進(jìn)行管理

2）插入數(shù)據(jù)

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;

2.2 操作數(shù)據(jù)

1）查看分區(qū)數(shù)

show partitions dept_partition;

2）查詢指定分區(qū)

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

3）增加/刪除分區(qū)

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

ps.也可以直接在liunx端輸入命令增加分區(qū)

-- 將18號(hào)分區(qū)復(fù)制一份，命名為13號(hào)分區(qū)
hadoop fs -cp /user/hive/warehouse/dept_partition/dt=2022-06-18
/user/hive/warehouse/dept_partition/dt=2022-06-13

ps..如果直接在網(wǎng)頁(yè)端新建文件夾,終端不會(huì)顯示新建的分區(qū)，必須修復(fù)

msck repair table dept_partition;

3. 二級(jí)分區(qū)表

就是大文件夾套小文件夾

3.1 創(chuàng)建分區(qū)表

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';

3.2 插入數(shù)據(jù)

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;

4.動(dòng)態(tài)分區(qū)

普通數(shù)據(jù)無(wú)法直接轉(zhuǎn)化為分區(qū)表，只能先新建新的分區(qū)表，再將舊數(shù)據(jù)插入這個(gè)新的分區(qū)表

1）創(chuàng)建分區(qū)表

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）然后將數(shù)據(jù)插入這張分區(qū)表

方式一：一個(gè)分區(qū)一個(gè)分區(qū)的插入

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

方式二：動(dòng)態(tài)分區(qū)一次搞定

insert overwrite table emp_par   // 不用指定分區(qū)
select empno,ename,job,sal,deptno from emp;  //直接把deptno寫到這里

5.分桶表

核心語(yǔ)句：

clustered by (a) sorted by (b) into 4 buckets //按照a分了4個(gè)桶，桶內(nèi)按照b排序

5.1 新建分桶表

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

查看

select * from stu_buk

可以發(fā)現(xiàn)分成了四個(gè)區(qū)

ps.分桶的意義:在取數(shù)的時(shí)候可以直接數(shù)據(jù)定位所在的桶，然后方便遍歷，查詢更高效

5.2 插入數(shù)據(jù)

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

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

insert overwrite table stu_buck
select id,name from stu_ex;

5.3 既分區(qū)有分桶

create table stu_par_buck(
    id int,
    name string
)
partitioned by (dt string)  // 先創(chuàng)建文件夾
clustered by (id) sorted by (id desc) into 4 buckets  //然后內(nèi)部分桶
row format delimited fields terminated by '\t';

插入數(shù)據(jù)：

與普通的一樣

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

6 分區(qū)與分桶的區(qū)別

主鍵適合拿來(lái)分桶，而普通的列適合拿來(lái)分區(qū)（一般為日期）

分桶是文件，分區(qū)是文件夾

到此這篇關(guān)于MySQL分區(qū)表和分桶表的操作詳解的文章就介紹到這了,更多相關(guān)MySQL分區(qū)表和分桶表內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片