零基础学习hive（简单实用）

hive是基于Hadoop的一个数据仓库工具，用来进行数据提取、转化、加载，这是一种可以存储、查询和分析存储在Hadoop中的大规模数据的机制。hive数据仓库工具能将结构化的数据文件映射为一张数据库表，并提供SQL查询功能，能将SQL语句转变成MapReduce任务来执行。hive本身并不存储数据，数据存储在hdfs上。

hadoop以及hive的安装可以查看：https://blog.csdn.net/weixin_40474941/article/details/123132371

hive复杂类型数据详解—array,map,struct
开窗函数

复杂类型，开窗函数，调优策略，参数设置等内容将在其他文章中叙述

我们通常使用CLI命令行和浏览器端的Web控制台HUE进行操作，本文以命令行为例介绍hive的基本知识，以工作中常用到的操作为主，更详细的内容可以查看：
《hive编程指南》
《hive简明教程》
以及hive官方文档

一、命令执行

1.启动与退出
输入hive，进入到hive CLI（若未将hive加入到环境变量，需要在hive 的bin 目录下$HIVE_HOME/bin 执行）

执行quit；退出

hive的命令都是以分号“；”结尾

2.不启动/不退出 hive CLI 实现shell与hive命令的交互
（1）在shell命令行执行hive命令：

hive -e “hive语句”

hive -f hql脚本文件

hql文件中可以有一个或多个hive语句

注：用hive -e 或 -f 将查询结果重定向时会将WARN信息一同输入，如：

hive -e “select * from test.demo;” > test1

针对这两条WARN信息的解决办法：

echo "export HIVE_SKIP_SPARK_ASSEMBLY=true" >> /etc/profile
source /etc/profile

（2）在hive内执行shell命令

用source命令执行hql脚本文件

source /root/tmp/demo.hql;

执行shell命令，在命令前加叹号“!”，以分号“;”结尾

! pwd ;

（3）在hive内执行hadoop dfs 命令
将hadoop 关键字去掉，以分号“;”结尾

二、库表操作

1.数据库操作

（1）查看所有数据库

show databases;

（2）创建数据库

create database [ if not exists ] demo [comment ‘注释信息’];

加上 if not exists若数据库已存在则不报错

（3）查看数据库信息

describe database demo1;

或简写为

desc database demo1;

（4）删除数据库

drop database [ if exists ] demo1;

若数据库中已经创建数据表，则需要先删除表再删除库，或者命令后加cascade可以直接删除库和表

drop database [ if exists ] demo1 cascade；

（5）切换到数据库

use demo；

正常查询数据表需要指定数据库db_name.tb_name，切换到数据库后，再查询demo库中的表可以不指定库名直接写表名

2.数据表操作

（1）查看库中表

show tables in db_name；
或
use db_name;
show tables;
可以用正则表达式过滤
show tables ‘tmp*’;

（2）查看表信息

desc tb_name;
desc formatted tb_name; #内容更详细，包括创建者、创建时间、在hdfs 上的目录等

（3）新建表

这里要介绍一下hive中的四种表：内部表，外部表；分区表，桶表

1.内部表
内部表也叫管理表，删除内部表的时候表中的数据同时也会删除，且内部表不方便共享数据，所以我们常用外部表

create table [ if not exist ] table_name 
( 字段名称 字段类型 [ comment '注释' ], 字段名称 字段类型 [ comment '注释' ] )  #字段之间以逗号分隔
[ row format delimited fields terminated by '\t' ] #指定列之间的分隔符
[ stored as textfile] #指定文件的存储格式
;

也可以通过克隆或查询复制已有表的方式创建新表

#克隆表结构，会复制元数据，但不会复制源表的数据
create table tb_name2 like tb_name1;

#复制源表数据及表结构，可以只选择部分列，也可以对数据进行筛选处理
create table tb_name2
as
select col1,col2
from tb_name1
where col1 is not null;

2.外部表
创建外部表时需要指定关键字external，删除外部表的时候只会删除表的元数据，表中的数据依然存在hdfs上

#除增加关键字external外与创建内部表一样
create external table table_name(id int, name string) row format delimited fields terminated by '\t' stored as textfile;

3.分区表
当一张表的数据量很大时，我们可以通过partitioned by 指定一个或多个分区将数据分类，比如按日期分区，我们查询某一天数据的时候只会遍历当天分区里的数据而不会扫描全表，提高查询效率。分区又分为动态分区和静态分区，向表中导入数据的时候有所差别，将在后面介绍

#指定year为分区键，注意分区键不能同时出现在table_name后面，否则会报错
create table table_name(id int, name string) partitioned by(year int) row format delimited fields terminated by '\t' stored as textfile; 

4.桶表
当分区表中分区的数据量很大时，我们可以通过clustered by 指定字段进行hash运算，并指定分桶个数，使用hash值除以桶的个数取余进行分桶，这样按照指定字段查询时效率更高。此外还有可选项sorted by 指定桶中的数据按哪个字段排序，在join操作时可以提高效率

#该表按照year进行分区，每个分区又分成4个桶
create table table_name(id int, name string) partitioned by(year int) clustered by(id) into 4 buckets;   

提示：

1. 建议将存储类型设置为orc格式，可以对数据进行压缩而且读取的性能较高。
2. 需要注意orc格式不支持load data 导入数据，如果从文件向表中导入数据的话要将表设为textfile。
3. 此外注意分隔符的设置，text 格式的分隔符为“\t”制表符，csv 格式的分隔符为“,”逗号，若hive 表的分隔符与文件不一致，导入的数据可能格式错误或者全是null。

（4）向表中加载数据

将文件中的数据加载进表

load data local inpath ‘文件位置’ overwrite into table tb_name;

local 关键字表示源数据文件在本地，源文件如果在hdfs 上，则去掉local，文件位置填入hdfs的路径

将查询结果导入表

insert overwrite table tb_name2 select * from tb_name1;
也可以一次查询多次插入，比分次性能更高
from tb_name
insert overwrite table log1 select * where event_id=‘001’
insert overwrite table log2 select * where event_id=‘002’

若是分区表需要指定分区，假如以年为分区，在命令的最后加入partition (year=‘2022’)，则会创建分区year=‘2022’ 并将数据全部写入此分区。如果是动态分区，命令修改为partition (year)，不需要指定分区值，如果year字段中包含了2021，2022两年的数据，则会自动创建year='2021’和year=‘2022’ 两个分区，根据year的值将数据写入相应的分区。

查看分区 show partitions tb_name;

使用动态分区需要注意：在生产环境中慎用，上游表出现问题数据追溯会很麻烦。需要在设置中开启动态分区功能，并限制允许创建的最大分区数，设计错误将导致大量分区。

（6）表结构修改

修改表名

alter table old_name rename to new_name;

修改分区

添加一个分区
alter table tb_name add partition(year = 2022)
删除一个分区 alter
table tb_name drop partition(year = 2022)

修改列信息

增加一个新列（只能增加到已有字段之后）
alter table tb_name add columns (col1 int , col2 string) ;

对字段重命名，修改位置、类型或注释等 ，只能修改元数据不对表中已存在的数据做改动
alter table tb_name change column old_name new_name int comment ‘xx’ after coln;
将old_name重命名为new_name，字段类型为int，注释信息为’xx’，将字段移动到字段coln之后，如果要移动到第一列用first 代替after coln

三、查询语法

1.基本查询语法

select col_name，函数列/算数运算列
from tb_name
where 过滤条件
limit 返回条数 ;

select语句的几种写法

select * from tb_name; #查询所有字段
select col1,col2 from tb_name; #指定字段查询
select t.col1 from tb_name t; #使用表别名

函数可以使用 hive 自带的函数，也可以使用用户自定义函数

字段之间支持 “+” 加 “-” 减 “*” 乘 “/” 除 “%” 取模等运算，注意浮点型与整型乘除运算长度溢出问题

where语句用and 或or 连接谓词表达式，当谓词表达式的计算结果为true时，保留并输出相应的行

2.子查询

嵌套子查询

select * from ( select col1,col2 from tb_name ) a;

还有一种定义临时表的方式，语句特别复杂的时候代码可读性更高

with t1 as (select col1,col2 from tb_name) 
select * from t1;

with … as (子查询) 必须和其他语句一起用，定义多个临时表之间用逗号","连接，with 只需要写一次，as 之后的语句要用括号括起来

with t1 as (
select * from tb_name
),
t2 as (
select * from tb_name
)
select * from t1;

3.case … when … then

和if 条件语句类似，用于处理单个列的查询结果

select name,class,score,
  case
    when score >= 90 then 'genius'
    when score >= 60 and score < 80 then 'excellent' 
    else 'fighting'
    end as a
from test.demo_score
where class = 'math';

a math 90 genius
b math 80 fighting
c math 50 fighting
w math 70 excellent

4.group by 语句

通常和聚合函数一起使用，按照一个或多个列对结果进行分组，然后对每个组进行聚合操作
如test.demo_score表中字段分别为名字，分数，科目

select name,sum(score) from test.demo_score group by name;

按名字分组将分数相加，执行命令，结果为：

a 182
b 140
c 116
w 126

having子句可以对group by产生的结果进行过滤：

select name,sum(score) from test.demo_score group by name having sum(score) >= 140 ; 

结果为：

a 182
b 140

5.join表连接

hive只支持等值连接，on子句指定连接条件，多个连接条件只能用and并列，不支持or

（1）inner join 内连接
连接的表中，只有同时满足连接条件的记录才会输出

如test.demo表中name值为abcde，test.demo_score中name值为abcw，通过name字段inner join后只有name为abc对应的数据保留

select a.name,a.id,b.name,b.class
from
test.demo a
inner join
test.demo_score b
on a.name = b.name

a 1 a math
a 1 a english
b 2 b math
b 2 b english
c 3 c math
c 3 c english

（2）left join 左连接
左侧表的数据全部保留，右侧表只有符合on条件的数据才保留，如右表中name没有de值，则返回null

select a.name,a.id,b.name,b.class
from
test.demo a
left join
test.demo_score b
on a.name = b.name

a 1 a math
a 1 a english
b 2 b math
b 2 b english
c 3 c math
c 3 c english
d 4 NULL NULL
e 5 NULL NULL

（3）right join 右连接
和左连接规则相反

a 1 a math
a 1 a english
b 2 b math
b 2 b english
c 3 c math
c 3 c english
NULL NULL w math
NULL NULL w english

（4）full join 全连接
两侧表的数据全部保留

（5）left semi join 左半开连接
返回左表的记录，但前提是右表满足on条件。相当于执行效率更高的inner join，但是又有点区别，select中不能出现右表的字段

select a.name,a.id
from
test.demo a
left semi join
test.demo_score b
on a.name = b.name;

a 1
b 2
c 3
left semi join只返回左边的记录，右表的数据相当于一个过滤条件。由于右表abc的记录都有两条inner join的结果会是
a 1
a 1
b 2
b 2
c 3
c 3

（6）笛卡尔积连接

select * from a join b ;

不加on 连接条件

结果是左表中的每一条数据和右表中的每一条数据进行连接，产生的数据量是左表行数乘右表行数。在严格模式下禁止用户执行笛卡尔积连接。

6.排序

（1）order by 全局排序

按分数倒序排列，正序排列不加desc
select * from test.demo_score order by score desc;

由于是全局排序，order by 的操作要在map 后所有的数据都汇集到一个reduce 上执行，如果数据量大执行的时间会很久

（2）sort by
sort by 是在每一个reduce 中进行排序，但相同字段可能会被分配到多个reduce 上，因此局部排序的结果未必在全局上也排好顺序

select * from test.demo_score distribute by name sort by score;

sort by 经常和distribute by 一起使用，distribute by name可以指定相同name的数据分配到同一个reduce 上处理，但依然不是全局排序

如果distribute by 和sort by 的字段为同一个，可以简写为cluster by

select * from test.demo_score distribute by score sort by score;
等同于
select * from test.demo_score cluster by score;

7.union all

union all 可以将多个查询进行合并，但每一个union 子查询必须具有相同的列，且字段的类型一致。多用于将多个表的数据进行合并。

select a.name,a.id,a.source from
(
select name,id,'demo' as source from test.demo 
union all
select name,score as id,'demo_score' as source from test.demo_score
)a ;

8.常用函数

网友整理了hive函数大全，涵盖了常用函数，讲解很清晰
https://blog.csdn.net/wisgood/article/details/17376393

查看函数的使用方法：

desc function extended 函数名称

行列转换函数在下面文章中有讲解，可以跳转查看
[hive复杂类型数据详解—array,map,struct]