Hive基础操作

数据库技术
307
0
0
2024-02-26
使用这8招后，数据库查询从191s优化到30ms！
上一篇
【数据库设计和SQL基础语法】--索引和优化--SQL语句性能调优
下一篇

Hive基本概念

什么是Hive

Hive：由Facebook开源用于解决海量结构化日志的数据统计。

Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类SQL查询功能。

本质是：将HQL转化成MapReduce程序。

1）Hive处理的数据存储在HDFS

2）Hive分析数据底层的实现是MapReduce

3）执行程序运行在Yarn上

Hive的优缺点

优点

  1. 操作接口采用类SQL语法，提供快速开发的能力（简单、容易上手）。
  2. 避免了去写MapReduce，减少开发人员的学习成本。
  3. Hive的执行延迟比较高，因此Hive常用于数据分析，对实时性要求不高的场合。
  4. Hive优势在于处理大数据，对于处理小数据没有优势，因为Hive的执行延迟比较高。
  5. Hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数。
缺点
  • 1．Hive的HQL表达能力有限

（1）迭代式算法无法表达

（2）数据挖掘方面不擅长

  • 2．Hive的效率比较低

（1）Hive自动生成的MapReduce作业，通常情况下不够智能化

（2）Hive调优比较困难，粒度较粗

Hive基本操作

和MySQL 99%一样

（1）启动hive

 bin/hive

（2）查看数据库

hive> show databases;

（3）打开默认数据库

hive> use default;

（4）显示default数据库中的表

hive> show tables;

（5）创建一张表

hive> create table student(id int, name string);

（6）显示数据库中有几张表

hive> show tables;

（7）查看表的结构

hive> desc student;

（8）向表中插入数据

hive> insert into student values(1000,"ss");

（9）查询表中数据

hive> select * from student;

（10）退出hive

hive> quit;

 Hive常用交互命令

1．“-e”不进入hive的交互窗口执行sql语句
bin/hive -e "select id from student;"
2．“-f”执行脚本中sql语句

（1）创建hivef.sql文件

 touch hivef.sql

文件中写入正确的sql语句

select *from student;

（2）执行文件中的sql语句

bin/hive -f /opt/module/datas/hivef.sql

Hive数据类型

基本数据类型

Hive数据类型

Java数据类型

长度

例子

TINYINT

byte

1byte有符号整数

20

SMALINT

short

2byte有符号整数

20

INT

int

4byte有符号整数

20

BIGINT

long

8byte有符号整数

20

BOOLEAN

boolean

布尔类型，true或者false

TRUE  FALSE

FLOAT

float

单精度浮点数

3.14159

DOUBLE

double

双精度浮点数

3.14159

STRING

string

字符系列。可以指定字符集。可以使用单引号或者双引号。

‘now is the time’ “for all good men”

TIMESTAMP


时间类型


BINARY


字节数组


对于Hive的String类型相当于数据库的varchar类型，该类型是一个可变的字符串，不过它不能声明其中最多能存储多少个字符，理论上它可以存储2GB的字符数。

集合数据类型

数据类型

描述

语法示例

STRUCT

和c语言中的struct类似，都可以通过“点”符号访问元素内容。例如，如果某个列的数据类型是STRUCT{first STRING, last STRING},那么第1个元素可以通过字段.first来引用。

struct()

MAP

MAP是一组键-值对元组集合，使用数组表示法可以访问数据。例如，如果某个列的数据类型是MAP，其中键->值对是’first’->’John’和’last’->’Doe’，那么可以通过字段名[‘last’]获取最后一个元素

map()

ARRAY

数组是一组具有相同类型和名称的变量的集合。这些变量称为数组的元素，每个数组元素都有一个编号，编号从零开始。例如，数组值为[‘John’, ‘Doe’]，那么第2个元素可以通过数组名[1]进行引用。

Array()

Hive有三种复杂数据类型ARRAY、MAP 和 STRUCT。ARRAY和MAP与Java中的Array和Map类似，而STRUCT与C语言中的Struct类似，它封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套。

create table test(
name string,
friends array<string>,
children map<string, int>,
address struct<street:string, city:string>
)
row format delimited fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by '\n';

字段解释：
row format delimited fields terminated by ','  -- 列分隔符
collection items terminated by '_'  	--MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)
map keys terminated by ':'		-- MAP中的key与value的分隔符
lines terminated by '\n';		-- 行分隔符
select friends[1],children['xiao song'],address.city from test
where name="songsong";

类型转化

1．隐式类型转换规则如下

（1）任何整数类型都可以隐式地转换为一个范围更广的类型，如TINYINT可以转换成INT，INT可以转换成BIGINT。

（2）所有整数类型、FLOAT和STRING类型都可以隐式地转换成DOUBLE。

（3）TINYINT、SMALLINT、INT都可以转换为FLOAT。

（4）BOOLEAN类型不可以转换为任何其它的类型。

2．可以使用CAST操作显示进行数据类型转换

例如CAST('1' AS INT)将把字符串'1' 转换成整数1；如果强制类型转换失败，如执行CAST('X' AS INT)，表达式返回空值 NULL。

DDL数据定义

创建表

语法
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
[(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 row_format] 
[STORED AS file_format] 
[LOCATION hdfs_path]
create table test(
name string,
friends array<string>,
children map<string, int>,
address struct<street:string, city:string>
)
row format delimited fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by '\n';

字段解释：

row format delimited fields terminated by ',' -- 列分隔符

collection items terminated by '_' --MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)

map keys terminated by ':' -- MAP中的key与value的分隔符

lines terminated by '\n'; -- 行分隔符

DML数据定义

数据导入

向表中装载数据（Load）

1．语法

hive> load data [local] inpath '/opt/module/datas/student.txt' overwrite into | into table student [partition (partcol1=val1,…)];

（1）load data:表示加载数据

（2）local:表示从本地加载数据到hive表；否则从HDFS加载数据到hive表

（3）inpath:表示加载数据的路径

（4）overwrite:表示覆盖表中已有数据，否则表示追加

（5）into table:表示加载到哪张表

（6）student:表示具体的表

（7）partition:表示上传到指定分区

排序

全局排序（Order By）

Order By：全局排序，一个Reducer

ASC（ascend）: 升序（默认）

DESC（descend）: 降序

每个Reduce内部排序（Sort By）

只有分区才能在每一个reduce中排序，所以个Distribute By一起用

分区排序（Distribute By）

Distribute By：类似MR中partition，进行分区，结合sort by使用。

注意，Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。

对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。

还需要设置一个参数，默认值为-1

安装你的业务逻辑设置数量

set mapreduce.job.reduces=3;

根据deptno分区,在分区内安装empno排序 

insert overwrite local directory 
'/opt/module/datas/distribute-result' 
select * from emp distribute by deptno sort by empno desc;

Cluster By

当distribute by和sorts by字段相同时，可以使用cluster by方式。

cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。

set mapreduce.job.reduces=3;
hive (default)> select * from emp cluster by deptno;

等价 

hive (default)> select * from emp distribute by deptno sort by deptno;

其他常用查询函数

空字段赋值

NVL：给值为NULL的数据赋值，它的格式是NVL( string1, replace_with)。它的功能是如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值，如果两个参数都为NULL ，则返回NULL。

select nvl(comm,-1) from emp;
select nvl(comm,deptid) from emp;

CASE WHEN

select 
  dept_id,
  sum(case sex when '男' then 1 else 0 end) male_count,
  sum(case sex when '女' then 1 else 0 end) female_count
from 
  emp_sex
group by
  dept_id;

列转行

相关函数说明

CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;

CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;

COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

输入数据

name

constellation

blood_type

孙悟空

白羊座

A

大海

射手座

A

宋宋

白羊座

B

猪八戒

白羊座

A

凤姐

射手座

A

输出数据
射手座,A            大海|凤姐
白羊座,A            孙悟空|猪八戒
白羊座,B            宋宋
过程
select
   name,
   concat(constellation, ",", blood_type) base
 from
   person_info

输出t1表

   c_b                     name

射手座,A            大海
射手座,A            凤姐
白羊座,A            孙悟空
白羊座,A            猪八戒
白羊座,B            宋宋

另上面的表为t1

select c_b,COLLECT_SET(name) from t1 group by c_b;

输出t2表

射手座,A            ["大海","凤姐"]
白羊座,A            ["孙悟空","八戒"]
白羊座,B            ["宋宋"]

另上面的表为t2

select 
   c_b,
   CONCAT_WS（"|" , COLLECT_SET(name) )
from 
   t2 
   group by c_b;

就得出结果了

行转列

EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。

LATERAL VIEW

    用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

    解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

输入数据
movie	        category
《疑犯追踪》	悬疑,动作,科幻,剧情
《Lie to me》	悬疑,警匪,动作,心理,剧情
《战狼2》	战争,动作,灾难
输出数据
《疑犯追踪》      悬疑
《疑犯追踪》      动作
《疑犯追踪》      科幻
《疑犯追踪》      剧情
《Lie to me》   悬疑
《Lie to me》   警匪
《Lie to me》   动作
《Lie to me》   心理
《Lie to me》   剧情
《战狼2》        战争
《战狼2》        动作
《战狼2》        灾难
创建表并且加载数据
create table movie_info(
    movie string, 
    category array<string>) 
row format delimited fields terminated by "\t"
collection items terminated by ",";

load data local inpath "/opt/module/datas/movie.txt" into table movie_info;
过程
select
    movie,
    category_name
from 
    movie_info 
lateral view explode(category) table_tmp as category_name;
窗口函数

1．相关函数说明

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化

CURRENT ROW：当前行

n PRECEDING：往前n行数据

n FOLLOWING：往后n行数据

UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点

LAG(col,n)：往前第n行数据

LEAD(col,n)：往后第n行数据

NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。

 Rank

RANK() 排序相同时会重复，总数不会变

DENSE_RANK() 排序相同时会重复，总数会减少

ROW_NUMBER() 会根据顺序计算

总结

1）这篇文章写的太烂了

2）hive就是简化了mapreduce，并将添加了一些hql函数

3）hive的学习可以理解为当初学SQL一样枯燥乏味

4）行转列、列转行、窗口函数等是重点，总之，查询是重点

使用这8招后，数据库查询从191s优化到30ms！
上一篇
【数据库设计和SQL基础语法】--索引和优化--SQL语句性能调优
下一篇