MySQL分區表和分桶表的操作詳解

目錄

• 1.創建分區表
• 2.增刪改查操作
• 2.1 插入數據
• 2.2 操作數據
• 3. 二級分區表
• 3.1 創建分區表
• 3.2 插入數據
• 4.動態分區
• 5.分桶表
• 5.1 新建分桶表
• 5.2 插入數據
• 5.3 既分區有分桶
• 6 分區與分桶的區別

1.創建分區表

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

2.增刪改查操作

2.1 插入數據

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_partitionselect deptno, dname, loc, "2022-06-18" from dept;

2.2 操作數據

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;

3. 二級分區表

就是大文件夾套小文件夾

3.1 創建分區表

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 插入數據

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.動態分區

普通數據無法直接轉化為分區表，只能先新建新的分區表，再將舊數據插入這個新的分區表

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寫到這里

5.分桶表

核心語句：

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

5.1 新建分桶表

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.分桶的意義:在取數的時候可以直接數據定位所在的桶，然后方便遍歷，查詢更高效

5.2 插入數據

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

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

insert overwrite table stu_buckselect id,name from stu_ex;

5.3 既分區有分桶

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_buckselect id, name, "2022-06-14" from stu_ex;

6 分區與分桶的區別

主鍵適合拿來分桶，而普通的列適合拿來分區（一般為日期）

分桶是文件，分區是文件夾

到此這篇關于MySQL分區表和分桶表的操作詳解的文章就介紹到這了,更多相關MySQL分區表和分桶表內容請搜索以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持！