大数据HIVE

最新推荐文章于 2024-09-16 11:16:36 发布

爱新觉罗·胤禩

最新推荐文章于 2024-09-16 11:16:36 发布

阅读量890

点赞数 19

文章标签：大数据 hive hadoop

本文链接：https://blog.csdn.net/weixin_44527643/article/details/135765768

版权

启动Hive

启动集群组件

cd /export/onekey ./start-all.sh

终端链接Hive

进入到/export/server/hive-2.1.0/bin目录中
执行以下命令：./beeline
输入：!connect jdbc:hive2://node1:10000，回车
输入用户名：root
在输入密码位置直接回车，即可使用命令行连接到Hive，然后就可以执行HQL了。

数据库操作

创建数据库-默认方式

create database if not exists myhive; 
show databases;  #查看所有数据库
说明： 
1: if not exists：该参数可选,表示如果数据存在则不创建(不加该参数则报错),不存在则创建。 
2: hive的数据库置默认存放在/user/hive/warehouse目录

创建数据库-指定存储路径

create database myhive2 location '/myhive2’;
show databases;  #查看所有数据库
说明： 
1: location ：用来指定数据库的存放目录

Hive数据表操作

创建数据表语法

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] 
[LOCATION hdfs_path]

说明：
1、CREATE TABLE
创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；
用户可以用 IF NOT EXISTS 选项来忽略这个异常。
2、EXTERNAL
可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION）.Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除,而外部表只删除元数据,不删除数据.
3、LIKE
允许用户复制现有的表结构，但是不复制数据。
4、ROW FORMAT DELIMITED
是用来设置创建的表在加载数据的时候，支持的列分隔符。
Hive默认的分隔符是\001，属于不可见字符，这个字符在vi里是^A
5、STORED AS
SEQUENCEFILE|TEXTFILE|RCFILE
如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。
如果数据需要压缩，使用 STORED AS SEQUENCEFILE。
6、CLUSTERED BY
对于每一个表（table）或者分区， Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive也是针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
7、LOCATION
指定加载数据路径(指定在hdfs上的位置).针对的extetnal外部表，要指定存储路径，不指定也没事，默认路径。内部表不用指定，默认路径/user/hive/warehouse
CREATE TABLE创建一个具有给定名称的表。如果已存在具有相同名称的表或视图，则会引发错误。您可以使用IF NOT EXISTS跳过错误。

内部表操作-创建表

create database mytest; #创建数据库 创建表之后，Hive会在对应的数据库文件夹下创建对应的表目录

use mytest; #选择数据库 

create table stu(id int,name string); 

show tables; #查询表

内部表操作-查看表结构/删除表

查看表结构

desc stu; #查看表结构基本信息
desc formatted stu; #查看表结构详细信息

删除表

drop table stu;

数据添加

方式1——直接插入数据

对于Hive中的表，可以通过insert into 指令向表中插入数据。

use mytest; #选择数据库 

create table stu(id int,name string); #创建表

#向表中插入数据
insert into stu values(1,'zhangsan’); 
insert into stu values(2,'lisi');

select * from stu; #查询数据

该方式每次插入都会在表目录中生成对应的数据文件，不推荐使用。

方式2——load数据加载

语法

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1,partcol2=val2 ...)]

说明:
  LOCAL 表示从本地文件系统加载,否则是从HDFS加载

应用1-本地加载

#创建表，同时指定文件的分隔符
create table if not exists stu2(id int ,name string) row format delimited fields terminated by '\t’ ;

#向表加载数据

load data local inpath '/export/data/hivedatas/stu.txt' into table stu2;

应用2——HDFS加载

#创建表，同时指定文件的分隔符
create table if not exists stu3(id int ,name string) 
row format delimited fields terminated by '\t’ ;

#向表加载数据
hadoop fs -mkdir -p /hivedatas 
cd /export/data/hivedatas 
hadoop fs –put stu.txt /hivedatas/ 
load data inpath '/hivedatas/stu.txt' into table stu3;

外部表操作

创建表

创建表时，使用external关键字修饰则为外部表，外部表数据可用于共享。
创建表之后，Hive会在Location指定目录下创建对应的表目录

#创建学生表
create external table student (sid string,sname string,sbirth string , ss       ex string) row format delimited fields terminated by ‘\t’;

#创建老师表
create external table teacher (tid string,tname string) row format delimited fields terminated by ‘\t’;

加载数据

外部表加载数据也是通过load命令来完成。

#给学生表添加数据 
load data local inpath '/export/data/hivedatas/student.txt' into table student; 

#给老师表添加数据，并覆盖已有数据 
load data local inpath '/export/data/hivedatas/teacher.txt' overwrite into table teacher;


#查询数据
select * from student; 
select * from teacher;

内外部表特点

内部表：
hive内部表信息存储默认的文件路径是在/user/hive/warehouse/databasename.db/tablename/目录
hive内部表在进行drop操作时,其表中的数据会被删除
外部表：
外部表在进行drop操作的时，只会删除表与数据文件之间的映射关系，而不删除HDFS上的文件
外部表一般用于数据共享表，比较安全

分区表

在大数据中，最常用的一种思想就是分治，分区表实际就是对应hdfs文件系统上的的独立的文件夹，该文件夹下是该分区所有数据文件。
分区可以理解为分类，通过分类把不同类型的数据放到不同的目录下。
Hive中可以创建一级分区表，也可以创建多级分区表
分区表里面的数据插入的时候需要指定分区
into是不断插入新的数据
overwrite是先删除指定分区的数据，再写入新的数据
分区是一个文件夹
多级分区表的分区是按照一级包含二级，二级包含三级的关系生成

创建一级分区表

create table score(sid string,cid string, sscore int) partitioned by (month string) row format delimited fields terminated by ',';

数据加载

load data local inpath '/export/data/hivedatas/score.txt' into table score partition (month='202006');
#增加 
insert [overwrite] into score partition (month='202006') values (xxx);

创建多级分区表

加载数据之后，多级分区表会创建多级分区目录

create table score2(sid string,cid string, sscore int) partitioned by (year string,month string, day string) 
row format delimited fields terminated by ',';

数据加载

load data local inpath '/export/data/hivedatas/score.txt' into table score2 partition(year='2020',month='06',day='01');

 insert [overwrite] into score partition(year='2020',month='06',day='01') values (xxx);

增删查分区

# 查看分区
show partitions score; 
# 添加分区  
alter table score add partition(month='202008’); 
alter table score add partition(month='202009') partition(month = '202010');
#删除分区
alter table score drop partition(month = '202010');

Hive 查询操作

跟mysq的差不多

select [distinct]select_expr, select_expr, ... 
from table_reference 
[where where_condition] 
[group by col_list] 
[having where_condition] 
[order by col_list] ... 
[limit number]

解释:
 select 查询关键字
 distinct 去重
 from 指定要查询的表
 where 指定查询条件
 group by  分组查询
 having 对分组后的结果进行条件筛选
 order by  排序
 limit  查询指定的记录数

# 全表查询
select * from score;
# 特定列查询
select sid,sic from score;
# 使用别名查询
select sid as stu_id from score;
#去重查询
select distinct sid from score;

内置函数

round(double a, int d) ：返回指定精度d的double类型
substr(string A, int start, int len)：返回字符串A从start位置开始，长度为len的字符串
substring(string A, intstart, int len)：返回字符串A从start位置开始，长度为len的字符串
concat(string A, string B…)返回输入字符串连接后的结果，支持任意个输入字符串
year(string date)
month(string date)
day(string date)
hour(string date)
举例：

hive> select year('2012-12-08’); 
2012 
hive> select month('2012-12-08’); 
12
hive> select month('2012-12-08’); 
8
hive> select hour('2012-12-08 13:35:35’); 
13
- date_format(string ts, string fmt)

举例：

select date_format('2020-1-1', 'yyyy-MM-dd'); -- 2020-01-01 
select date_format('2020-1-1 12:23', 'yyyy-MM-dd'); -- 2020-01-01 
select date_format('2020-1-1 12:23:35', 'yyyy-MM-dd'); -- 2020-01-01 
select date_format('2020-1-1 1:1:1', 'yyyy-MM-dd HH:mm:ss');--2020-01-01 01:01:01 
select hour(date_format('2020-1-1 18:1:00', 'yyyy-MM-dd HH:mm:ss')); --18

CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END
案例:

select 
sid ,
case 
  when sscore>=60 
    then '及格’ 
  when sscore<60 
    then '不及格’
  else '其他’ 
end as flag
from score

ROW_NUMBER() 从1开始，按照顺序，生成分组内记录的序列
-RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位
ENSE_RANK() 生成数据项在分组中的排名，排名相等会在名次中不会留下空位
举例：
COUNT：统计行数量
SUM：获取单个列的合计值
AVG：计算某个列的平均值
MAX：计算列的最大值
MIN：计算列的最小值

1）求总行数（count） 
select count(1) from score; 
select count(*) from score; 
2）求分数的最大值（max） 
select max(sscore) from score; 
3）求分数的最小值（min）
 select min(sscore) from score; 
4）求分数的总和（sum） 
select sum(sscore) from score; 
5）求分数的平均值（avg） 
select avg(sscore) from score;

查询操作符

在这里插入图片描述

例子

（1）查询分数等于80的所有的数据 
select * from score where sscore = 80; 

（2）查询分数在80到100的所有数据 
select * from score where sscore between 80 and 100;
 
（3）查询成绩为空的所有数据 
select * from score where sscore is null;
 
（4）查询成绩是80或 90的数据
 select * from score where sscore in(80,90);
#模糊查询
 （1）查找姓‘李’的学生信息 
select * from student where sname like '李%’;
 
（2）查找名字第二个字是'兰'的学生信息 
select * from student where sname like '_兰%';

#逻辑运算符
（1）查询成绩大于80，并且sid是01的数据 
select * from score where sscore >80 and sid = '01’; 
（2）查询成绩大于80，或者sid 是01的数 
select * from score where sscore > 80 or sid = '01’; 
（3）查询s_id 不是 01和02的学生 
select * from score where sid not in ('01','02'); 

# 分组查询
（1）计算每个学生的平均分数 
 select sid ,avg(sscore) from score group by sid;
 
（2）计算每个学生最高成绩 
 select sid ,max(sscore) from score group by sid;
#HAVING语句通常与GROUP BY语句联合使用，用来过滤由GROUP BY语句返回的记录集。HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。
# -- 求每个学生平均分数大于85的人 
select sid ,avg(sscore) avgscore from score group by sid having avgscore > 85;

#排序
(1)查询学生的成绩，并按照分数升序排列 
select * from score order by sscore asc; 

(2)按照分数的平均值降序排序 
select sid ,avg(sscore) avg from score group by sid order by avg desc;

#limit显示查询返回结果
--查出成绩最高的学生信息
select * from score order by sscore desc limit 1
select * from score order by sscore desc limit 0,1; -- 第一个数字表示从第一行开始，第二个数字表示连续显示1个
 
--查出成绩最低的倒数3后三名学生信息
select * from score order by sscore asc limit 3;
select * from score order by sscore asc limit 0,3;  
 
--将成绩进行降序排序，并查出第2，3,4名
select * from score order by sscore desc limit 1,3;

Hive查询操作-insert into 和 insert overowrite语句

insert into 语句可以将一张表查询的结果插入到另外一张表

INSERT OVERWRITE|INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] [(col1,col2 ...)] select_statement FROM from_statement;

overwrite: 表示插入数据时，覆盖源数据
PARTITION:给分区表添加数据时，需要加该选项

create table student1(sid string,sname string) row format delimited fields terminated by '\t’;
--插入数据-追加
insert into table student1 select sid,sname from student;
--插入数据-覆盖
insert overwrite table student1 select sid,sname from student;