Hive的分区以及各种函数用法

1.分区表的创建

create table emp_partition(
EMPNO bigint,
ENAME string,
JOB string,
MGR bigint,
HIREDATE string,
SAL bigint,
COMM bigint,
DEPTNO bigint
)PARTITIONED BY (dt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
;

创建一个以dt为分区列的分区表

2.向表中插入数据

insert into table emp_partition partition(dt='20220401')
    select * from emp;
select * from emp_partition;

插入数据时必须同时插入分区列数据

3.PARTITIONED BY

Permission denied –没有权限

hdfs dfs -chmod 777 /tmp/hadoop-yarn

分区表的好处，避免全表扫描

4.多级分区

create table emp_partition(
EMPNO bigint,
ENAME string,
JOB string,
MGR bigint,
HIREDATE string,
SAL bigint,
COMM bigint,
DEPTNO bigint
)PARTITIONED BY (dt string,hour string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
;

insert into table emp_partition partition(dt='20220402',hour='12',minute='56')
     select * from emp where job='SALESMAN';
select * from emp_partition where dt='20220401' and hour='12';

5.动态分区

可以按照原表中的某一个字段作为分区，但是注意这个字段不能存在于表中

例如：emp中我们按照deptno，将数据插入到emp_dynamic_partition

create table emp_dynamic_partition(
EMPNO bigint,
ENAME string,
JOB string,
MGR big