xufei.biz

Hive Develop Doc.md

16 min read

Hive Data Type

基本数据类型
集合数据类型
数据类型的转化

Hive的原子数据类型是可以进行隐式转换的

  • 小 → 大 ：但是Hive不会进行反向转化
  • STRING 隐式转换成 DOUBLE
  • BOOLEAN 类型不可以转换为任何其它的类型
cast ( 值 as 数据类型 ） :   cast（ '1'  as  int)
 
select  cast('100000000000000000000' as tinyint);  >> null
select  cast(100000000000000000000 as tinyint);  >> -1
select 'dd' + 1 ;      >> null
select '1' + 1 ;       >> 2.0
select  cast('1' as int) + 1;  >> 2

DDL Defined

Database Operation

# 创建数据库
CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]    -- 这个数据库的解释(写一下这个数据库是拿来干什么的)
[LOCATION hdfs_path]          -- 指定存放路径
[WITH DBPROPERTIES (property_name=property_value, ...)];  -- 关于库的属性值  非常鸡肋
 
# 显示有哪些数据库
show databases;	
# 切换数据库
use 数据库名;	
# 查找emp相关的数据库
show databases like 'db_hive*';
# 查看数据库信息
desc database  数据库名;	
desc database extended 数据库名; # 显示库的详细信息
# 删除空数据库
drop database 数据库名;		
# 删除空数据库（标准写法）
drop database if exists 数据库名;
# 强制删除数据库
drop database 数据库名 cascade;

Table Operation

表的类型

  • 内部表：删除表的时候，内部表的元数据和真实数据会被一起删除

  • 外部表 ( 常用 )：删除表的时候，只删除元数据，不删除真实数据

# 展示库中所有的表
show tables;	
# 描述表基本信息
desc `表名`;	
# 描述表详细信息
desc formatted `表名`;  
# 查看表的分区信息
show partitions `表名`;
# 查看建表的语句
show create table `库名`.`表名`;
 
建表语法 : 
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name   -- EXTERANL: 外部表
[(col_name data_type [COMMENT col_comment], ...)]  -- 列名 列类型 列描述信息  ....
[COMMENT table_comment] -- 表描述信息
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] -- 创建分区表指定分区字段  分区列名  列类型
[CLUSTERED BY (col_name, col_name, ...) -- 创建分桶表指定分桶字段   分桶列名
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]  -- 指定分桶数
[ROW FORMAT delimited fields terminated by ... ] -- 指定一条数据字段与字段的分割符
[collection items terminated by  ... ] -- 指定集合元素与元素的分割符
[map keys terminated by ... ] -- 指定map的kv的分割符
[STORED AS file_format] -- 指定文件存储格式，默认为 textfile
[LOCATION hdfs_path] -- 指定表在hdfs中对应的路径
[TBLPROPERTIES (property_name=property_value, ...)] -- 指定表的属性
[AS select_statement] -- 基于某个查询建表

管理表（内部表 Table_Type : managed_table）

管理表（内部表）-- hive掌握数据生命周期(会伴随着表的删除而删除)  --测试，中间表：
 
create table if not exists student1 ( -- 创建内部表
id int comment 'id',           
name string comment '姓名') 
comment '学生表' -- 表的注释
partitioned by(statis_date string)  -- 创建分区列，列名为statis_date 数据类型为string
row format delimited fields terminated by '\t'   -- 创建表，字段与字段分隔符号为Tab键盘
STORED as textfile; -- 文件存储格式
 
# 根据结果创建表[表结构和数据 但是 不带分隔符]
create table student2 as select * from student where 1=1;  
 
# 模仿一张表[表结构并且 带分隔符]
create table student3 as select * from student where 1=0;
create table student3 like student;

外部表（Table_Type : external_table）

reate external table if not exists dept(   -- 创建外部表
deptno int,
dname string,
loc int)
comment 'qqq表'
row format delimited fields terminated by '\t'     -- 根据空格切分
location '/company/dept';    -- 保存路径

内外部表的相互切换

查看是内部表还是外部表 -> Table Type:EXTERNAL_TABLE  # desc formatted `表名`;  
 
# 把学生表类型修改为外部表[固定写法区分大小写]
alter table 表名 set tblproperties('EXTERNAL'='TRUE');   
# 把学生表类型修改为内部表[固定写法区分大小写]
alter table 表名 set tblproperties('EXTERNAL'='FALSE');

修改表

alter table 旧表名 rename to 新表名 ;     -- 修改表名
alter table 表 add columns(新列名 新列数据类型);    -- 添加列
alter table 表 change (旧列名 新列名 新列数据类型);       -- 替换列【列的类型只能小改大】
 
alter table 表 replace columns
(idss int , namess string , type int , createtime  string );   
-- 修改全部列【1:1对应，或增加列】【类型从小到大】
 
# 删除表
drop table if exit 表名; 
 
# 清除表  Truncate 只能删除管理表，不能删除外部表中数据
truncate table student;

DML Operation

Load Data Operation
  • load 文件导入
  • insert 根据查询表结果导入已建立好的表
  • as select 根据查询表结果直接新建表
  • location hdfs目录上已有文件，建表直接指定改目录
 
hive> load data [local] inpath '数据的path' [overwrite] into table 表名 [partition (partcol1=val1,…)];
local		-- 从本地加载数据到hive表；否则从HDFS加载数据到hive表
overwrite	-- 覆盖已有数据，否则追加
inpath -- 数据路径
partition   -- 上传到指定分区
 
# 从本地加载数据，源文件不变
# 从HDFS加载数据，源文件移动

LOAD DATA

# 从本地文件覆盖导入数据
load data local inpath '/usr/tmp/test1.txt' overwrite into table test1;
 
# 从 hdfs 目录覆盖导入数据
load data inpath '/test1.txt' overwrite into table test2;
 
# 把本地数据追加到test_1表的2020-12-10分区中
LOAD DATA LOCAL INPATH '/home/admin/test/test.txt' INTO TABLE test_1 PARTITION(dt='2020-12-10')

INSERT DATA

INSERT  加载数据  [ 常用于根据某些表查询结果导入 ]
 
1、把stu2中表数据追加到stu1的20201022分区
insert into table stu1 partition(dt=20201022) select * from stu2; 
 
2、把stu2中表数据覆盖到stu1的20201022分区
insert overwrite table stu1 partition(dt=20201022) select * from stu2;  
 
3、基本模式插入（根据单张表查询结果）
insert into table test3 select * from test2;
 
4、多表（多分区）插入模式（根据多张表查询结果）
hive (default)> from student
              insert overwrite table student partition(month='201707')
              select id, name where month='201709'
              insert overwrite table student partition(month='201706')
              select id, name where month='201709';

AS SELECT

# 直接基于查询创建数据表
create table test5 as select * from test4;

SELECT DATA

/student4/data.txt   # 此路径下已存在数据
 
create table student4(id string, name string) 
row format delimited fields terminated by '\t'
location '/student4' ;

Data Export

  • insert
  • hive -e > 查询结果重新定向
  • export 数据迁移

INSERT DATA

 
# 如果表中的列的值为null,导出到文件中以后通过\N来表示. 
 
1、导出到 本地文件
insert overwrite local directory '/usr/tmp/export' 
row format delimited fields terminated by '\t' 
select * from test1;
 
2、导出到 hdfs
insert overwrite directory '/usr/tmp/test' 
row format delimited fields terminated by '\27' 
select * from test1;

HIVE -E

hive -e "select * from mydb.student" > /zhj/temp/tb.txt  # 导出到本地

EXPORT

export ：导出 (数据 + 元数据)  一般用于数据迁移
 
# export 导出到hdfs
export table mydb.student to '/student';
 
# import 从hdfs导入
import table mydb.student2 from '/student';
 
-- import 导入必须是export 导出

DQL QUERY

graph LR
    A[from] --> B[join on]
    B --> C[where]
    C --> D[group by]
    D --> E[select 聚合函数]
    E --> F[having]
    F --> G[order by]
    G --> H[limit ]

SELECT [ALL | DISTINCT] select_expr, select_expr, ...    
-- ALL一般都省略， DISTINCT去重[group by优化]
  FROM table_reference        -- 来自哪张表
  [WHERE where_condition]     -- 筛选
  [GROUP BY col_list]		  -- 分组
  [HAVING col_list]			  -- 分组后筛选
  [ORDER BY col_list]         -- 全局排序
  [CLUSTER BY col_list			-- 分区排序
  | 
   [DISTRIBUTE BY col_list] 	-- 分区
   [SORT BY col_list]			-- 区内排序
  ]
 [LIMIT number]               -- 分页[显示多少行]
-- 列别名,表别名
 
select 1+1;
 
select 1+1 value1;
 
select 1+1 as value2;
 
select 1+1 `1+1的值`;
 
# select 'name' vv ; ≠  select 'name' 'vv' ;
-- 常用聚合函数  <多进一出>
select count(*) from emp;		-- 求总行数
select max(sal) from emp;		-- 求最大工资
select min(sal) from emp;		-- 求最小工资
select sum(sal) from emp;		-- 求工资总和
select avg(sal) from emp;		-- 求平均工资
-- limit 用法
select * from emp limit 5;			-- 取前5行，第1行到第5行  把0省略
select * from emp limit 2,4;		-- 从第3行开始取4行，从第3行到第6行
select * from emp limit 2,-1;		-- 从第3行取到末尾

LIKE AND RLIKE

  • LIKE：使用LIKE运算选择类似的值；选择条件可以包含字符或数字

    • % : 代表零个或多个字符(任意个字符)
    • _ : 代表一个字符

  • RLIKE子句：是Hive中这个功能的一个扩展，其可以通过 ‘Java的正则表达式’ 这个更强大的语言来指定匹配条件

  -- 名字开头待S
  select * from emp where ename like 'S%';
  select * from emp where ename rlike '^S';
  -- 名字末尾带S
  select * from emp where ename like '%S';
  select * from emp where ename rlike 'S$';
  -- 名字中带S
  select * from emp where ename like '%S%';
  select * from emp where ename rlike '[S]';

GROUP BY

group by 通常和聚合函数一起使用 【多进一出】,having 对分组结果的过滤

-- 求每个部门每个工种的最大工资
select
t.deptno,
t.job,
max(t.sal) max_sal			   -- 此列别名为max_sal
from mydb.emp t				    -- 表别名为t
group by t.deptno, t.job;     -- 代码美化
 
 
having 与 where 不同点
	1、where 后面不能写分组函数，而 having 后面可以使用分组函数。
	2、having 只用于 group by 分组统计语句
	3、分组之前用where 分组之后用having

Sort

ORDER BY

全局排序 order by ，只有一个Reducer 

select * from emp
order by sal  [desc];            -- 按照sal升序  asc：升序[可省略]  desc：降序
 
select ename, sal*2 twosal from emp order by twosal;  -- 按照列别名排序
select * from emp order by deptno , sal ;         -- 按照多列排序

SORT BY

Summary

  • Reduce内部排序 sort by 【不单独使用】
  • reducer产生一个排序文件。每个Reducer内部进行排序 根据哈希值排序
set mapreduce.job.reduces=3;   -- 设置reduce的个数
set mapreduce.job.reduces;     -- 查看reduce的个数
select * from emp sort by deptno;    -- 系统内置分3个reduce，每个reduce里升序    【不单独使用】

DISTRIBUTE BY

distribute by的分区规则是根据分区字段的 hash值与 reduce 的个数进行 取模 后，余数相同 的分到一个区

set mapreduce.job.reduces=3;		-- 设置三个分区
 
select
empno,
ename,
sal,
deptno
from emp 
distribute by deptno sort by sal;     -- 首先按照deptno的值的余数升序，然后按照sal里升序
 
30 mod 3 = 0    -- 分区1
10 mod 3 = 1	-- 分区2
20 mod 3 = 2	-- 分区3
50 mod 3 = 2	-- 分区3

CLUSTER BY

et mapreduce.job.reduces=3;		-- 设置三个分区
 
select * from emp
cluster by deptno;  -- 只能是升序
-------等价
select * from emp 
distribute by deptno sort by deptno [desc];    -- 分三区，余数先排序，再排里面数值排序

分区表和分桶表

分区表

  • 分区表就是分 目录
  • 防止暴力扫描，分区列相当与伪列
-- 创建分区表
create table dept_partition(
deptno int,
dname string, 
loc string)
comment 'dept分区表'
partitioned by (day string)    -- 创建分区列名为day
row format delimited fields terminated by '\t'; -- 安装tab键分隔
 
-- 上传数据的分区表中
load data local inpath '/zhj/dept_20200401.log' into table mydb.dept_partition partition(day='20200401');
load data local inpath '/zhj/dept_20200402.log' into table mydb.dept_partition partition(day='20200402');
load data local inpath '/zhj/dept_20200403.log' into table mydb.dept_partition partition(day='20200403');
 
-- 查询
show partitions dept_partition;   -- 查看表中有多少个分区
select * from dept_partition ; -- 查询全部数据
select * from dept_partition where day = '20200402';  -- 查询20200402分区里的内容

Change Partition Table

alter table dept_partition add partition(day='20200404')   -- 增加单个分区
 
alter table dept_partition add partition(day='20200405') partition(day='20200406');  -- 增加多个分区，无,
 
alter table dept_partition drop partition (day='20200406');   -- 删除分区
 
alter table dept_partition drop partition (day='20200404'), partition(day='20200405');  -- 删除多个分区，有,

分区表多级分区

create table dept_partition2(
deptno int, 
dname string,
loc string)
comment '部门二级分区表'
partitioned by (day string, hour string)
row format delimited fields terminated by '\t';
 
load data local inpath '/zhj/dept_20200401_11.log' into table
dept_partition2 partition(day='20200401', hour='11'); 		-- 加载数据到分区
 
select * from dept_partition2 
where day = '20200401' and hour = '11';  -- 查询20200401的hour为11的内容
 
alter table dept_partition2 drop partition(day='20200401',hour='11');  -- 删除二级分区

Hive 数据倾斜

Question

Map阶段同一Key数据分发给一个reduce，当一个key数据过大时就倾斜了

数据倾斜的表现

  • 由于数据分布不均匀，造成数据大量的集中到一点，造成数据热点
  • 任务进度长时间维持在 99%或者 100%的附近，查看任务监控页面，发现只有少量 reduce 子任务未完成，因为其处理的数据量和其他的 reduce 差异过大
# 采用中间临时表优化
with as 用法:性能优化，解决数据倾斜
 
 
with t1 as(   # 一次分析，多次使用，提供性能的优化，达到了“少读”的目标
select        # with as 属于一次性的，而且必须要和其他sql一起使用才可以！
  u.userkey,  # 这里必须要整体作为一条sql查询，即with as语句后不能加分号，不然会报错。
  o.*
from user_info u
left join user_order o
on u.idno = o.idno
where u.idno is not null )
# 是可以把where条件写在后面的，hive会进行谓词下推，先执行where条件在执行 left join
 
select
  a.userkey,
  a.idno,
  a.phone,
  a.name,
  b.user_active_at,
  c.intend_commodity,
  c.intend_rank,
  d.order_num,
  d.order_amount
from user_info a
left join user_active b on a.userkey = b.userkey
left join user_intend c on a.phone = c.phone
left join t1 d on a.userkey = d.userkey;

300

Graph View