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

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

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

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;

show partitions dept_partition;

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

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

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;

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

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

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

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;