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

-- 創(chuàng)建一個名字為dt='2022-06-14'的文件夾，在其中導入數(shù)據(jù)
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   // 不用指定分區(qū)
select empno,ename,job,sal,deptno from emp;  //直接把deptno寫到這里

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

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

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)  // 先創(chuàng)建文件夾
clustered by (id) sorted by (id desc) into 4 buckets  //然后內(nèi)部分桶
row format delimited fields terminated by '\t';

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