hive使用

-- ########################################## 建库语句 ##########################################
create database db_emp;
create database if not exists db_emp;
create database db_emp location '/hive/resposistion/db_emp';


-- ########################################## 查询库语句 ##########################################
show databases;
desc database extended db_emp;


-- ####################################### 修改属性【添加自定义属性】########################################
alter database db_emp set dbproperties('createOwn'='kun');


-- ########################################## 删库语句 ##########################################
-- 删除语句【删除空数据库】
drop database db_emp;
-- 强制删除
drop database db_emp cascade;


-- ########################################## 切库语句 ##########################################
use db_emp;

-- ########################################## 查询表结构 ##########################################
show create table student;
desc student;
desc extended student;
desc formatted student;


-- ########################################## 创建表 ##########################################
-- 直接创建表
create external table if not exists student(id int, name string);
-- 使用别的表的查询数据创建表，不允许使用外部表方式
create table if not exists student_new as select id, name from student;
-- 使用别的表结构创建表
create external table if not exists student_like like student;

-- 创建表使用各种分割
-- 数据格式
-- kangkang,lili_xiangxiang,lili:18_xiangxiang:19,fengtai_beijing
-- YueYue,lili_sisi,lili:18_sisi:19,daxiao_beijing
create table persion_info(
  name string,
  friends array<string>,
  children map<string,int>,
  address struct<stree:string, city:string>
)
row format delimited
fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by '\n';


-- ########################################## 修改表 ##########################################
-- 表类型转换
alter table student set tblproperties('EXTERNAL'='TRUE');
alter table student set tblproperties('EXTERNAL'='FALSE');

-- 重命名
alter table student_partition_old rename to student_partition_new;
 
-- 修改列
alter table student_partition change column id stu_id string;-- 增加列

-- 增加列
alter table student_partition add columns(sex int, age int);

-- 替换列【全量】
alter table student_partition replace columns(id string, name string, sex int);

-- 删除表
drop table student_partition;

-- ########################################## 创建分区表 ##########################################
-- 创建分区表
create table if not exists student_partition(id int, name string)
partitioned by (year string)
row format delimited
fields terminated by '\t';

-- 创建二级分区表
create table if not exists student_partition(id int, name string)
partitioned by (month string, year string)
row format delimited
fields terminated by '\t';


-- ########################################## 插入数据表 ##########################################
-- 插入分区表数据
load data local inpath '/opt/module/hive/input/student.txt' into table student_partition partition(year="2019");

-- 插入二级分区表数据
load data local inpath '/opt/module/hive/input/student.txt' into table student_partition_day partition(month="2019-01", day="01");


-- ########################################## 创建&删除&查询分区 ##########################################
-- 创建分区
alter table student_partition add partition(year="2020") partition(year="2018");

-- 删除分区
alter table student_partition drop partition(year="2020") , partition(year="2018");

-- 查看表分区
show partitions student_partition;


-- ########################################## 修复分区数据 ##########################################
-- 上传数据，完成之后因为没有元数据信息所有无法查询
hadoop fs -mkdir  /user/hive/warehouse/student_partition/year=2018
hadoop fs -put input/student.txt /user/hive/warehouse/student_partition/year=2018
-- 方式一：自动修复
msck repair table student_partition;
-- 方式二：手动添加分区
alter table student_partition add partition(year="2017");


-- ########################################## 创建动态分区 ##########################################
-- 开启动态分区功能(默认true)
set hive.exec.dynamic.partition=true
-- 设置为非严格模式(动态分区的模式，默认strict,表示必须指定至少一个分区为静态分区)
set hive.exec.dynamic.partition.mode=nonstrict;
-- 在所有执行MR的节点上，最大一共可以创建多少个动态分区，默认1000。
set hive.exec.max.dynamic.partitions=1000;
-- 在每个执行MR的节点上，最大可以创建多少个动态分区，默认100。
set hive.exec.max.dynamic.partitions.pernode=100;
-- 整个MR Job中，最大可以创建多少个HDFS文件，默认100000。
set hive.exec.max.created.files=100000
-- 当有空分区生成时，是否抛出异常。一般不需要设置。
hive.error.on.empty.partition=false
-- 创建表
create table employee_partition(
  empno int,
  ename string,
  job string,
  mgr string,
  hiredate string, 
  sal int,
  comm int)
partitioned by (deptno int) 
row format delimited fields terminated by '\t' ;
-- 插入数据，此时不用指定partition的字段值，动态获取
insert into employee_partition partition(deptno)
select empno, ename, job, mgr, hiredate, sal, comm, deptno from employee;

-- ########################################## Load装载数据 ##########################################
-- 数据在本地文件是上传到HDFS，数据在HDFS上是剪切
-- 追加
load data [local] inpath '/opt/module/hive/input/student.txt' into table student;
-- 清空，覆盖
load data [local] inpath '/opt/module/hive/input/student.txt' overwrite into table student;


-- ########################################## Insert插入数据 ##########################################
-- 插入数据
insert into table student_partition partition(year="2000") values("2", "tom", NULL);

-- 使用插入数据覆盖， 注意字段顺序
insert overwrite table student_partition partition(year="2000") select id, name, sex from student_bak;


-- ########################################## 查询并创建数据 ##########################################
create table student_new as select id, name from student;


-- ######################################## 创建表并指定数据文件夹#########################################
-- 创建表并指定存放数据的HDFS文件夹位置，然后上传文件到文件夹即可
create table studeng_new(id int, name string) 
row format delimited fields terminated by '\t'
location '/user/hive/warehouse/student_new'

-- ########################################## Insert导出 ###########################################
insert overwrite [local] directory '/opt/module/hive/input/student.data' 
row format delimited fields terminated by '\t' 
select * from student;


-- ######################################## Export导出Import导入 ########################################
-- export导出到HDFS上
export table student to '/hive/export/student';
import table student from '/hive/export/student';

-- 必须是管理表才能截断数据
truncate table student;

-- order by[全局排序,只有一个Reduce]
select * from employee order by sal;

-- sort by[局部排序,多个Reduce,分区字段随机]	
set mapreduce.job.reduces=2;
-- 分为两个区，每个区内有序
select deptno,sal from employee sort by sal;

-- distribute by[分区排序],先按照给定字段分区之后再排序
select * from employee distribute by deptno sort by sal;
set mapreduce.job.reduces=3;
select deptno,sal from employee distribute by deptno sort by sal;

--cluster by[当distribute by field sort by field asc相同时可替代]
select deptno from employee cluster by deptno;

-- 创建分桶表
create table employee_buck(empno int, ename string, deptno int)
clustered by(deptno) into 3 buckets
row format delimited fields terminated by '\t';

-- 导入数据
set hive.enforce.bucketmapjoin = true;
set mapreduce.job.reduces = -1;
insert into table employee_buck select empno, ename, deptno from employee;

-- 查询分桶数据
select * from employee_buck where deptno=10;

-- 分桶抽样查询
select * from employee_buck tablesample(bucket 1 out of 6 on deptno);
-- x out of y
-- x: 开始的桶号，基数为1
-- y: 桶数的因子或倍数，如果为因子则为抽取buckets/y个，如果是倍数则为(buckets/y) * buckets个

select nvl(NULL, 100);

-- 字符串转时间
select date_format('2020-09-22', 'yyyy-MM-dd');

-- 时间增加，数值可为负数为减
select date_add('2020-09-22', -1);

-- 时间减少，数值可为负数为增
select date_sub('2020-09-22', -1);

-- 两时间相减
select datediff('2020-09-22','2020-09-21');

-- case when
select 
  classno,
  sum(case sex when "男" then 1 else 0 end) as male,
  sum(case sex when "女" then 1 else 0 end) as female 
from emp_sex group by classno;

-- if
select if(mgr = "", "领导", "员工") from employee;

-- 拼接
select concat("hello", "_", "world");

-- 拼接指定分隔符
select concat_ws("_", "hello", "world");

-- collect_set返回一个array
select deptno, collect_set(ename) from employee group by deptno;

select deptno, ename from emp_dept lateral view explode(enames) table_tmp as ename;

/*
 * 数据
 * name sale_date   price
 * Jack	2020-01-01	10
 * Jack	2020-01-02	20
 * Jack	2020-01-03	30
 * Jack	2020-01-05	5
 * Jane	2020-01-01	8
 * Jane	2020-01-02	12
 * Jane	2020-01-08	1
 * Roes	2020-01-08	100
 */
 
 
-- 按照名称统计，消费时间排序，计算累加消费
select name, sale_date, sum(price) over(distribute by name sort by sale_date) from business;
select name, sale_date, sum(price) over(partition by name order by sale_date) from business;
/**
 * 结果如下
 * name    sale_date       sum_window_0
 * Jack    2020-01-01      10
 * Jack    2020-01-02      30
 * Jack    2020-01-03      60
 * Jack    2020-01-05      65
 * Jane    2020-01-01      8
 * Jane    2020-01-02      20
 * Jane    2020-01-08      21
 * Roes    2020-01-08      100
 */


-- 查询上次消费和本次消费的总计
select
 name, 
 sale_date,
 sum(price) over(distribute by name sort by sale_date rows between 1 PRECEDING and CURRENT ROW) 
from business;
/**
 * 结果如下
 * Jack    2020-01-01      10
 * Jack    2020-01-02      30
 * Jack    2020-01-03      50
 * Jack    2020-01-05      35
 * Jane    2020-01-01      8
 * Jane    2020-01-02      20
 * Jane    2020-01-08      13
 * Roes    2020-01-08      100
 */


-- 查询顾客上次购买时间
select name, sale_date, lag(sale_data, 1) over(distribute by name sort by sale_date) from business;
/**
 * 结果如下
 * Jack    2020-01-01      NULL
 * Jack    2020-01-02      2020-01-01
 * Jack    2020-01-03      2020-01-02
 * Jack    2020-01-05      2020-01-03
 * Jane    2020-01-01      NULL
 * Jane    2020-01-02      2020-01-01
 * Jane    2020-01-08      2020-01-02
 * Roes    2020-01-08      NULL
 */
 
 
-- 查询消费日期25%为一组的数据
select name, sale_date,  ntile(4) over(sort by sale_data) as percent from business order by percent;
/**
 * 结果如下
 * Jack    2020-01-01      1
 * Jane    2020-01-01      1
 * Jack    2020-01-02      2
 * Jane    2020-01-02      2
 * Jack    2020-01-05      3
 * Jack    2020-01-03      3
 * Jane    2020-01-08      4
 * Roes    2020-01-08      4
 */


-- 消费者自身消费金额排序
select name, price, sale_date, rank() over(distribute by name sort by price desc) from business;
/**
 * 结果如下
 * Jack    30      2020-01-03      1
 * Jack    20      2020-01-02      2
 * Jack    10      2020-01-01      3
 * Jack    5       2020-01-05      4
 * Jane    12      2020-01-02      1
 * Jane    8       2020-01-01      2
 * Jane    1       2020-01-08      3
 * Roes    100     2020-01-08      1
 */

-- 查看系统自带的函数
show functions;

-- 显示自带的函数的用法
desc function [funcName];
desc function extended [funcName];

-- 开启hive传输数据压缩功能
set hive.exec.compress.intermediate=true;
-- 开启map-reduce中map输出压缩功能
set mapreduce.map.output.compress=true;
-- 设置map-reduce中map输出数据的压缩方式
set mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;

-- 开启hive最终输出数据压缩功能
set hive.exec.compress.output=true;
-- 开启map-reduce最终输出数据压缩
set mapreduce.output.fileoutputformat.compress=true;
-- 设置map-reduce中map输出数据的压缩方式
set mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodec;
-- 设置map-reduce最终数据输出压缩为块压缩
set mapreduce.output.fileoutputformat.compress.type=BLOCK;
-- 测试一下输出结果是否压缩文件
insert overwrite local directory '/opt/module/hive/input/employee' select deptno, sal from employee distribute by deptno sort by sal;

-- ######################################## TEXTFILE ########################################
create table employee_textfile(
  empno int,
  ename string
)
row format delimited fields terminated by '\t'
stored as textfile;

-- ######################################## ORC ########################################
create table employee_orc(
  empno int,
  ename string
)
row format delimited fields terminated by '\t'
stored as orc tblproperties ("orc.compress"="SNAPPY"); 
-- 默认压缩为ZLIB

insert into employee_orc select empno, ename from employee_textfile;

-- ######################################## PARQUET ########################################
create table employee_parquet(
  empno int,
  ename string
)
row format delimited fields terminated by '\t'
stored as parquet;

insert into employee_parquet select empno, ename from employee_textfile;

-- 开启本地模式默认为false
set hive.exec.mode.local.auto=true;
-- 数据量必须小于指定参数才使用本地模式，默认128M
set hive.exec.mode.local.auto.inputbytes.max=50000000;
-- job的map数必须小于参数才使用本地模式，默认4
set hive.exec.mode.local.auto.tasks.max=10;

-- 关闭mapjoin功能(默认是打开的)
set hive.auto.convert.join = false;

select * from (select * from bigNullKeyTable where bigtable_id is not null) T left join bigTable on bigTable.id = T.bigtable_id;

select * from bigNullKeyTable left join bigTable on if(bigNullKeyTable.bigtable_id is null, concat('temp_join_key', rand()) ,bigNullKeyTable.bigtable_id ) =  bigTable.id;

-- 设置自动选择MapJoin，默认为true
set hive.auto.convert.join = true; 
-- 大表小表的阈值设置(默认25M下认为是小表)
set hive.mapjoin.smalltable.filesize=25000000;

-- 是否在Map端进行聚合，默认为True
set hive.map.aggr = true;

-- 在Map端进行聚合操作的条目数目，默认100000
set hive.groupby.mapaggr.checkinterval = 100000;

-- 有数据倾斜的时候进行负载均衡(默认是false)
set hive.groupby.skewindata = true;

select count(distinct mgr) from employee;

set mapreduce.job.reduces=10;
select count(mgr) from (select mgr from employee group by mgr) T;

-- 开启可使用笛卡尔积
set hive.strict.checks.cartesian.product=false;

select ename,deptno from (select * from employee where mgr is not null) emp join department on emp.deptno=department.id;

-- 决定每个map处理的最大的文件大小，单位为B,默认256M
mapreduce.input.fileinputformat.split.maxsize=256000000;

-- 每个Reduce处理的数据量默认是256MB
set hive.exec.reducers.bytes.per.reducer=256000000;
-- 每个任务最大的reduce数，默认为1009
set hive.exec.reducers.max=1009;
-- reduce个数设置
set mapreduce.job.reduces=2;

-- 默认已合并
set hive.input.format=org.apache.hadoop.hive.q1.io.CombineHive.InputFormat;

-- 打开任务并行执行，默认false
set hive.exec.parallel=true; 
-- 同一个sq1允许最大并行度，默认8
set hive.exec.plarallel.thread.number=16;

explain select * from employee_partition where deptno=20;
explain extended select * from employee_partition where deptno=20;