Hive数据仓库知识点总结

最新推荐文章于 2024-07-24 13:08:50 发布
最新推荐文章于 2024-07-24 13:08:50 发布
阅读量285 收藏
点赞数 3
文章标签： hive 数据仓库 hadoop 大数据
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/shmily_lee/article/details/134018678
版权

文章目录

数据仓库

概念

1、数据仓库是企业发展到一定的阶段，现有的发展状况不能满足企业的需求，需要基于企业和行业历史数据来进行智能化的统计分析，通过分析挖掘出有价值的东西，为决策者或者领导层提供科学的决策支持，用于改善企业的业务流程，运行成本，企业效益、提高客户的体验度。

2、数仓中的数据是来自各种各样的数据源，为了分析，不择手段，只要有利于分析的数据，都可以集成到数仓中

3、目前，我们以为数据数仓中的数据是存在HDFS上的
    HDFS：文件   ----> 数仓管理工具  ----->数据库表 ----> HiveSQL/SparkSQL

在这里插入图片描述

主要特征

  • 面向主题的（Subject-Oriented ）

    我们分析一般都是泛泛而谈，一定是由一个或者主题：销售角度、客户角度、商家角度、产品的角度，数仓中的数据一定是要划分主题的

  • 集成的（Integrated）

    数仓的数据可以来自各种四面八方的数据，都集成在一起，集成之后，数据之间会存在格式上的差异，我们通过ETL机制来消除这种差异

  • 稳定的（Non-Volatile）

    1、离线数仓中的数据都是历史数据（历史不容更改）
2、在下一个采集周期到来之前，数仓中的数据是不变的（HiveSQL不支持update和delete命令）

  • 时变的（Time-Variant ）

    当下一个采集周期到来之前，数仓中的数据要及时的更新（T+1机制）

数仓和数据库的区别

1、数据库的数据是面向业务,面向事务(OLTP)的，面向客户的，如果数据丢了，整个企业的业务就无法开展，数据库是为了让企业活下来
2、数据仓库是面向分析(OLAP)的，面向大数据工程师的，数据仓库是让企业活的更好

3、数据库一般是存放业务数据,数据状态都是最新的
4、数仓一般存放是历史数据，数据不是最新的

5、数据库的表在设计是避免出现冗余：ER图、 三大范式
6、数仓为了分析允许数据出现冗余，怎么便于分析就怎么来

7、数据库强调时效性，要求请求和响应之间的时间在毫秒级别
8、数仓具有延时性，一般处理过程比较长

9、数据库面向客户，必须保证安全性，一旦有问题就是大问题 
10、数据仓库是面向内部人员，安全级别较低



#关系
数仓的数据可以来自数据库

#数据分析为什么需要单独搞一个数仓，不在业务数据库上直接分析
1、业务数据库本身需要消耗服务器资源，数据分析不能去抢占业务的资源
2、数据库只是数仓其中一种数据源，数仓可以是多种数据源的集合
3、业务数据库就是为业务而生的，不具备很多大数据分析所需要的的体系结构

数仓的分层

  • 概念

    1、数仓分层实际上描述的是数据在数仓中不同的处理阶段
2、数仓的分层没有统一的标准，每一个公司可能都不一样
3、通用的数仓分层
   DA层/APP层     存放分析后的结果数据         数据结果
   dw层           存放ods层预处理过的数据      数据干净 可以直接用于分析
     DWS层
     DWB层
     DWD层
   ods源数据层     存放最原始的结构化数据        数据原始

在这里插入图片描述

ETL概念

  • 抽取（extract）

    从数据源根据需求采集需要的指定数据：Sqoop  、Kettle、 Flume

  • 转换（transform）

    将抽取后的数据进行前期的分离处理，格式转换，字典表翻译、数据质量的空值

  • 加载（load）

    将转换后的数据加载数仓中

    在这里插入图片描述

Hive框架介绍和安装

Hive的概述

1、Hive是数仓管理工具,用来管理数仓
2、Hive可以将数仓存在HDFS上的文件变成一张张的表
3、Hive提供一种HiveSQL可以表进行分析处理
4、HiveSQL底层默认是MapReduce，以后可以换成其他的引擎（Spark），我们写HiveSQL会去匹配底层的MR模板，匹配上则执行，否则不能执行

Hive的特点

1、Hive本身只是一个工具，它不存任何数据: Hive的表数据存在HDFS上，Hive的元数据(表结构和HDFS文件之间的映射关系)存在第三方的数据库中（MySQL）
   select * from itheima_order_goods;
2、Hive底层引擎默认是MapReduce，你也可以换成其他引擎（Tez，Spark）

Hive的架构

在这里插入图片描述

Hive的交互方式

  • 方式1

    hive   #个人测试

  • 方式2（生产环境）

    hive -e "show databases;"  #直接执行HiveSQL命令


hive -f  test1.sql    #直接执行HiveSQL脚本 （生产环境）

  • 方式3-beeline

    1、beenline是Hive的第二代客户端    #个人测试
2、操作方式
[root@node3 ~]# beeline

beeline> !connect jdbc:hive2://node3:10000
Connecting to jdbc:hive2://node3:10000
Enter username for jdbc:hive2://node3:10000: root
Enter password for jdbc:hive2://node3:10000:123456

3、我们可以使用expect脚本来实现一键进入beenline

Hive的基础操作

Hive的数据库操作

  • 概念
1、当我们在hive中创建一个数据库，则Hive默认会自动会在/user/hive/warehouse/目录下创建一个数据库目录
  create database if not exists  myhive; #/user/hive/warehouse/myhive.db
2、当我们在Hive中创建一个数据库，则Hive会在MySQL中记录一条元数据
  • 操作
-- 1、创建数据库
create database if not exists  myhive;

-- 2、创建数据库并手动指定数据库存放目录
create database if not exists  myhive2 location '/myhive2'

--3、查看数据库的元数据信息
desc database myhive;


--4、删除数据库
drop database mytest;            -- 只能删除空数据库
drop database myhive2 cascade ;  -- 可以删除任何数据库（强删）

Hive的表操作

Hive的数据类型
Hive中常见的数据类型有三种：string、int/tinyint 、 double/decimal(10,2)
（重点）Hive表操作1-内部表和外部表

  • 前置内容

    1、Hive表文件默认的分隔符是'\001'
2、Hive默认不允许对数据进行删除和修改，不支持update和delete
delete  from stu where id = 2;             #报错  
update stu set name = 'ls2' where id = 2;   #报错

truncate table stu;  #不会报错，支持


3、Hive中创建表，自动在HDFS的数据库目录创建对应的表目录（如果加了location关键字则除外），默认表目录的名字和表名一样

  • 内部表

    1、内部表是私有表，一旦给表加载数据之后，内部表认为这份数据就是他独占的，表一旦删除，表数据文件会跟着全部删除，如果在应用中，数据是部门内部的，或者个人的，则表可以设置为内部表，不会对其他人造成影响。
2、外部表创建语法： create  table 表

    use myhive;
-- 1、创建内部表-使用默认分隔符:'\001'
create table stu(id int, name string);
-- 加载数据
insert into stu values (1,'zs');
insert into stu values (2,'ls');
select * from stu;

-- 2、创建内部表-使用指定分隔符: ','

create table stu2(id int, name string)
row format delimited fields terminated by ',';
insert into stu2 values (1,'zs');
insert into stu2 values (2,'ls');

-- 开启本地模式
set hive.stats.column.autogather=false;
set hive.exec.mode.local.auto=true;  --开启本地mr


-- 3、通过复制表结构来建表
create table stu3 as select * from stu2;  -- 即复制表结构，又复制数据
create table stu4 like stu2;   -- 仅复制表结构

-- 4、查看表的元数据信息
 desc   stu2;          -- 查看字段信息(简单)
 desc formatted stu2;  -- 查看详细的元数据信息


-- 5、删除表
-- 内部表删除，将表数据和元数据全部删除
drop table stu2;
select * from stu;

-- 6、给表加载数据(最正式的) - 本地 -复制

create table stux(id int, name string)
row format delimited fields terminated by '\t';


-- 从本地加载--复制
load data local inpath '/export/data/hivedatas/1.txt' into table stux;
select * from stux;


-- 6、给表加载数据(最正式的) - HDFS - 剪切
create table stuy(id int, name string)
row format delimited fields terminated by '\t';

load data  inpath '/input/hivedatas/1.txt' into table stuy;

select * from stuy;

  • 外部表

    1、外部表是公有表，一旦给表加载数据之后，外部表认为这份数据大家的，表一旦删除，表数据文件不会删除，只删除表和文件之间的映射关系，如果在应用中，数据是各部门共享，则可以设置为外部表，你的表只是对文件有访问权。
2、外部表创建语法： create external table 表...

    
-- 1、创建外部表
create external table teacher
(
    tid   string,
    tname string
) row format delimited fields terminated by '\t';

create external table student
(
    sid    string,
    sname  string,
    sbirth string,
    ssex   string
) row format delimited fields terminated by '\t';

-- 加载数据
load data local inpath '/export/data/hivedatas/student.txt' into table student;
load data local inpath '/export/data/hivedatas/teacher.txt' into table teacher;

select * from student;
select * from teacher;


-- 删除表,只删除元数据，不会删除表数据
drop table teacher;

    多个外部表共享数据

    在这里插入图片描述

    -- 模拟多张表共享一份数据
drop table covid1;
create external  table covid1(
    date_val string,
    country  string,
    state    string,
    code     string,
    cases    int,
    deaths   int
)
row format delimited fields terminated by ','
location '/input/covid';
select * from covid1;


create external  table covid2(
    date_val string,
    country  string,
    state    string,
    code     string,
    cases    int,
    deaths   int
)
row format delimited fields terminated by ','
location '/input/covid';
select * from covid2;

-- 删除covid1
drop table covid1;
select * from covid2;

-- 删除covid2
drop table covid2;
select * from covid2;
Hive的复杂类型

  • array类型

    -- 1、准备数据
zhangsan    beijing,shanghai,tianjin,hangzhou
wangwu  changchun,chengdu,wuhan,beijing

-- 2、创建表
create external table hive_array
(
    name           string,
    work_locations array<string>
)
row format delimited fields terminated by '\t'
collection items terminated by ',';

-- 3、加载数据
load data local inpath '/export/data/hivedatas/work_locations.txt' into table hive_array;

select * from hive_array;

-- 4、查询数据
-- 查询所有数据
select * from hive_array;
-- 查询work_locations数组中第一个元素
select name, work_locations[0] location from hive_array;
-- 查询location数组中元素的个数
select name, size(work_locations) location_size from hive_array;
-- 查询location数组中包含tianjin的信息
select * from hive_array where array_contains(work_locations,'tianjin');

  • map类型

    -- 1、准备数据

1,zhangsan,father:xiaoming#mother:xiaohuang#brother:xiaoxu,28
2,lisi,father:mayun#mother:huangyi#brother:guanyu,22
3,wangwu,father:wangjianlin#mother:ruhua#sister:jingtian,29
4,mayun,father:mayongzhen#mother:angelababy,26

-- 2、建表
create table hive_map
(
    id      int,
    name    string,
    members map<string,>,
    age     int
)
row format delimited fields terminated by ','
collection items terminated by '#'
map keys terminated by ':';

-- 3、加载数据

load data local inpath '/export/data/hivedatas/hive_map.txt' into table hive_map;
select * from hive_map;

-- 4、查询操作
select * from hive_map;
-- 根据键找对应的值
select id, name, members['father'] father, members['mother'] mother, age from hive_map;

-- 获取所有的键
select id, name, map_keys(members) as relation from hive_map;

-- 获取所有的值
select id, name, map_values(members) as relation from hive_map;

-- 获取键值对个数
select id,name,size(members) num from hive_map;

-- 获取有指定key的数据
-- 判断亲属关系中哪一个包含brother
select * from hive_map where array_contains(map_keys(members), 'brother');

-- 查找包含brother这个键的数据，并获取brother键对应的值
select id,name, members['brother'] brother from hive_map where array_contains(map_keys(members), 'brother');

  • struct类型

    class  类名{
   String name,
   int  age ,
   double score
}

-- 1、准备数据
192.168.1.1#zhangsan:40
192.168.1.2#lisi:50
192.168.1.3#wangwu:60
192.168.1.4#zhaoliu:70

-- 2、创建表
create table hive_struct(
    ip string,
    info struct<name:string, age:int>
)
row format delimited fields terminated by '#'
collection items terminated by ':';

-- 3、给表加载数据

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

select * from hive_struct;


-- 4、查询表数据
select  ip,info.name,info.age from hive_struct;
select  ip,info from hive_struct;
（重点) Hive表操作2-分区表

  • 介绍

    1、分区表就是对一个表的文件数据进行分类管理，表现形式就是有很多的文件夹(dt=2019-02-27)
2、分区表的作用是以后查询时，我们可以手动指定对应分区的数据，避免全表扫描，提高查询效率
3、专业的介绍
所谓的分区表，指的就是将数据按照表中的某一个字段进行统一归类，并存储在表中的不同的位置，也就是说，一个分区就是一类，这一类的数据对应到hdfs存储上就是对应一个目录。当我们需要进行处理的时候，可以通过分区进行过滤，从而只取部分数据，而没必要取全部数据进行过滤，从而提升数据的处理效率。且分区表是可以分层级创建。
select * from 表 where dt = '2019-03-13'

4、分区表的关键字是Partition，这里的分区是MR中的分区没有关系
5、分区表可以有内部分区表，也可以有外部分区表
6、什么时候表数据不用分区：  
   1）几乎在实际应用中所有的表数据都要分区
   2）如果你的数据量很小，而且数据很单一，此时可以不用分区

  • 静态分区

    ----------------------单级分区----------------------------------
-- 1、创建单分区表
create table score
(
    sid    string,
    cid    string,
    sscore int
)
partitioned by (dt string)  -- 这个dt是分区字段和表字段没有关系，理论上可以随便写
row format delimited fields terminated by '\t';

-- 2、给分区表加载数据
-- 第一件事：在HDFS的表目录下创建文件夹:dt=2022-10-13  第二件事：将score.txt复制到该文件夹下
load data local inpath '/export/data/hivedatas/score.txt' into table score partition (dt='2022-10-13');

select * from score;

-- 再添加一个分区
load data local inpath '/export/data/hivedatas/score2.txt' into table score partition (dt='2022-10-14');
select * from score;


-- 3、查询数据

-- 查找dt=2022-10-13分区数据
select * from score where dt='2022-10-13';

-- 查找dt=2022-10-14分区数据
select * from score where dt='2022-10-14';

desc score; -- 查看哪个是分区列


----------------------多级分区----------------------------------
-- 1、创建多级分区表
create table score2
(
    sid    string,
    cid    string,
    sscore int
)
partitioned by (year string, month string ,dt string)  -- 这个dt是分区字段和表字段没有关系，理论上可以随便写
row format delimited fields terminated by '\t';



-- 2、给分区表加载数据
-- 第一件事：在HDFS的表目录下创建三级文件夹:year=2022/month=10/dt=13 第二件事：将score.txt复制到该文件夹下
load data local inpath '/export/data/hivedatas/score.txt'
into table score2 partition (year='2022',month='10',dt='13');

select * from score2;

-- 再添加一个分区
load data local inpath '/export/data/hivedatas/score2.txt'
    into table score2 partition (year='2022',month='11',dt='13');
-- 再添加一个分区
load data local inpath '/export/data/hivedatas/score2.txt'
    into table score2 partition (year='2023',month='11',dt='13');

select * from score2;


-- 3、查询分区数据：查询 2022年 10月13号数据
select * from  score2 where year='2022' and month = '10' and dt = '13';


----------------------分区相关的SQL----------------------------------

show  partitions  score; -- 查看表所有分区情况
alter table score add partition(dt='2022-01-01');  -- 手动添加一个分区
alter table score drop partition(dt='2022-01-01'); -- 手动删除一个分区

  • 动态分区

    在这里插入图片描述

    • 单级分区
    
-- -----------------------单级分区：按照日进行分区---------------------------------
-- 1、开启动态分区
set hive.exec.dynamic.partition=true;  -- 开启动态分区
set hive.exec.dynamic.partition.mode=nonstrict;-- 设置为非严格格式


-- 2、模拟数据
/*
1	2022-01-01	zhangsan	80
2	2022-01-01	lisi	70
3	2022-01-01	wangwu	90
1	2022-01-02	zhangsan	90
2	2022-01-02	lisi	65
3	2022-01-02	wangwu	96
1	2022-01-03	zhangsan	91
2	2022-01-03	lisi	66
3	2022-01-03	wangwu	96
*/
-- 3、创建一个中间普通表（该表用来存入原始数据）
create table test1
(
    id       int,
    date_val string,
    name     string,
    score    int
)
row format delimited fields terminated by '\t';

-- 4、给普通表加载数据
load data local inpath '/export/data/hivedatas/partition.txt' into table test1;

-- 5、来创建最终的分区表
create table test2
(
    id    int,
    name  string,
    score int
)
partitioned by (dt string) -- 这个分区字段的名字随便写，它来决定HDFS上文件夹的名字：day=2022-01-01
row format delimited fields terminated by ',';

-- 6、查询普通表，将数据插入到分区表
insert overwrite table test2 partition (dt)
select id, name, score, date_val  from test1;

select * from test2;



-- -----------------------单级分区：按照月进行分区---------------------------------
1       2022-01-01      zhangsan        80
2       2022-01-01      lisi    70
3       2022-01-01      wangwu  90
1       2022-01-02      zhangsan        90
2       2022-01-02      lisi    65
3       2022-01-02      wangwu  96
1       2022-01-03      zhangsan        91
2       2022-01-03      lisi    66
3       2022-01-03      wangwu  96
1       2022-02-01      zhangsan        80
2       2022-02-01      lisi    70
3       2022-02-01      wangwu  90
1       2022-02-02      zhangsan        90
2       2022-02-02      lisi    65
3       2022-02-02      wangwu  96
1       2022-02-03      zhangsan        91
2       2022-02-03      lisi    66
3       2022-02-03      wangwu  96

load data local inpath '/export/data/hivedatas/partition2.txt' overwrite into table test1;

drop  table test2_1;
create table test2_1
(
    id    int,
    date_val string,
    name  string,
    score int
)
partitioned by (month string) -- 这个分区字段的名字随便写，它来决定HDFS上文件夹的名字：day=2022-01-01
row format delimited fields terminated by ',';


-- 6、查询普通表，将数据插入到分区表
insert overwrite table test2_1 partition (month)
select id, date_val,name, score, substring(date_val,1,7)   from test1;
    • 多级分区
    -- 1、创建普通表

drop table if exists test3;
create table test3
(
    id       int,
    date_val string,
    name     string,
    sex      string,
    score    int
)
    row format delimited fields terminated by '\t';
;


-- 2、给普通表加载数据
load data local inpath '/export/data/hivedatas/partition3.txt' overwrite into table test3;
select * from test3;

-- 3、创建最终的分区表
drop table test4;
create table test4
(
    id    int,
    name  string,
    score int
)
    partitioned by (xxx string, yyy string)
    row format delimited fields terminated by '\t'
;

-- 4、去普通表查询，将查询后的结果插入到最终的分区表

insert overwrite table test4
select id, name, score,date_val,sex from test3;  -- 这里的动态分区是看最后的两个字段
Hive表操作3-分桶表

  • 概念

    1、分桶就是MR的分区
2、分桶表的表现形式就是分文件,可以通俗的理解为将一个大的表文件拆分成多个小文件
3、分桶的作用有两个：
  作用1：主要是来提高多张表join的效率
  作用2：主要是用于数据的抽样
4、分桶的方式就是拿到分桶字段的值，然后取hash值对分桶的个数取模  
  
  
专业说法：
在表或者分区中使用分桶通常有两个原因，一个是为了高效的join查询，另一个则是为了高效的抽样。
桶其实是在表中加入了特殊的结构，hive在查询的时候可以利用这些结构来提高查询效率。比如，
如果两个表根据相同的字段进行分桶，则在对这两个表进行join关联的时候可以使用map-side关联高效实现。

  • 分桶表的操作

    -- 1、创建分桶表
create table course
(
    cid    string,
    c_name string,
    tid    string
)
clustered by (cid) into 3 buckets
row format delimited fields terminated by '\t';

-- 解释：
clustered by (cid) into 3 buckets  表示按照cid 这一列进行分桶，并且将表数据分到3个桶中（3个文件中）


-- 2、创建普通表
create table course_common
(
    cid    string,
    c_name string,
    tid    string
) row format delimited fields terminated by '\t';

-- 3、给普通表加载数据
load data local inpath '/export/data/hivedatas/course.txt' into table course_common;

select * from course_common;


-- 4、将普通表的数据进行查询插入到普通表
insert overwrite table course
select * from course_common cluster by (cid);

select * from course;

在这里插入图片描述

  • 作用

    • 作用1-提高join的效率

    在这里插入图片描述

​

  • 作用2 -可以用于数据的抽样

    1、有时候在大数据分析时，我们并不需要全部的数据参与分析，而只需要抽取一部分具有代表性的数据参与分析，这样可以提高分析的效率，此时就可以使用分桶表来完成

    1   zs
2   ls
3   ww
4   zl
5   zq
6   mb
7   lf
8   we
9   zz
10  qw



-- 1、创建分桶表
drop table sample_test;
create table sample_test
(
    sid    int,
    s_name string
)
clustered by (sid) into 6 buckets
row format delimited fields terminated by '\t';

-- 2、创建普通表
create table sample_common
(
    sid    int,
    s_name string
) row format delimited fields terminated by '\t';

-- 3、给普通表加载数据
load data local inpath '/export/data/hivedatas/sample.txt' overwrite into table sample_common;

select * from sample_common;

set hive.stats.column.autogather=false;
set hive.exec.mode.local.auto=true;  --开启本地mr


-- 4、将普通表的数据进行查询插入到普通表
insert overwrite table sample_test
select * from sample_common cluster by (sid);

select * from course;


-- 5、对数据进行抽样(先保留)
-- TABLESAMPLE (BUCKET x OUT OF y [ON colname])  6 / 2  = 3
select * from sample_test tablesample ( bucket  1 out of 2 on sid);


/*

select * from student tablesample(bucket x out of y on id);

n：总桶数

x：从第几个桶开始抽取

y：必须是总桶数的因数或倍数（自定义）

z：共需抽取出的桶数（z=n/y）


select * from student tablesample(bucket 1 out of 2 on id);
z 数据属于第几个桶

1 第1个分桶的数据（1）

2 第3个分桶的数据（1+y）

3 第5个分桶的数据（3+y）

4 第7个分桶的数据（5+y）

5 第9个分桶的数据（7+y）
*/
表结构操作
-- 删表
drop table score4;  #内部表和外部表的删除是不一样的

-- 清空表数据
truncate table score4; #只能清空内部表（管理表）
（重要）Hive表数据的插入方式
1、方式1：
insert into table score3 partition(dt ='2022-10-01') values ('001','002',100);

2、方式2 -(重要)
insert overwrite table score4 partition(dt ='2022-10-01') 
select sid,cid,sscore from score;

3、方式3 -(重要)
load data local inpath '/export/data/hivedatas/score.txt' overwrite into table score5 partition(dt ='2022-10-01');

4、方式4
create table score5 as select * from score;

5、方式5 -(重要)
create external table score6 (sid string,cid string,sscore int) row format delimited fields terminated by '\t' location '/myscore6';

6、方式6
hadoop fs -put stu.txt /user/hive/warehouse/myhive.db/stu

7、方式7 -(重要)
sqoop框架将数据直接导入hive
Hive表数据导出
-- 将sql查询的结果导出到本地磁盘
insert overwrite local directory '/export/data/exporthive'
row format delimited fields terminated by '\t'
select * from score where sscore > 85;

-- 将sql查询的结果导出到HDFS —（重要）
insert overwrite  directory '/export/data/exporthive'
row format delimited fields terminated by '\t'
select * from score where sscore > 85;


-- 将Hive -e 命令的执行结果导出本地目录文件
hive -e "select * from myhive.score;" > /export/data/exporthive/score.txt

-- 将一张表的数据全部导出到HDFS
export table score to '/export/exporthive/score';

(重点)Hive表的查询-基本查询

普通查询
1、聚合函数对null的态度
create table test11(
    id int,
    score int
);

insert into test11 values (1,50);
insert into test11 values (2,50);
insert into test11 values (3,null);
insert into test11 values (4,50);

select * from test11;

select sum(score) from test11; // 150
select avg(score) from test11; // 150  / 3
select avg(if(score is null, 0, score)) from test11; // 150  / 4
select avg(coalesce(score,0)) from test11; // 150  / 4


2、limit关键字
select * from student limit 3;
select * from student limit 2,3;  --从索引为2（从0开始）显示，显示3行


3、where条件查询

select * from score where sscore not in(80,90); -- 成绩不是80或者90
select * from score where not sscore  in(80,90);-- 成绩不是80或者90


4、like关键字
select * from student where sname like '赵%'; --  姓赵的
select * from student where sname like '%雷'; --  名字最后一个字是 雷
select * from student where sname like '%雷%'; -- 名字中包含 雷
select * from student where sname like '_雷%'; -- 名字第二个字是 雷\

5、分组-group by
-- 分组之后每一组只剩下一条数据，所以select后边只能跟分组字段和聚合函数
select  sid, sum(sscore) from score group by sid;

-- 分组之后的条件筛选是having，不是where
select  sid, sum(sscore) as total_score from score group by sid having  total_score > 450
join查询

  • 内连接

    -- 1、内连接：求交集
select * from teacher;
insert into teacher values ('04','赵六');

select * from  teacher inner join course c on teacher.tid = c.tid;
select * from  teacher  join course c on teacher.tid = c.tid;
select * from  teacher , course where teacher.tid = course.tid;

  • 左外连接

    -- 2、左外连接
-- 左外是以左表为主，把左表的数据全部输出，右表有对应的数据就输出，没有对应的数据就输出NULL
select * from  teacher left  join course c on teacher.tid = c.tid;

    在这里插入图片描述

  • 右外连接

    -- 3、右外连接
select * from course;
insert into course values ('04','政治','05');
-- 右外是以右表为主，把右表的数据全部输出，左表有对应的数据就输出，没有对应的数据就输出NULL
select * from  teacher right  join course c on teacher.tid = c.tid;

    在这里插入图片描述

  • 满外连接

    -- 4、满外连接
-- 查询左外连接和右外连接的并集

select * from  teacher full  join course c on teacher.tid = c.tid;

    在这里插入图片描述

排序查询

  • order by （重点）

    1、order by 用于全局排序，要求只能有一个Reduce
2、如果有多个Reduce，则不能使用order by
3、order by 的使用方法和MySQL是一样的

    select * from score order by sscore ;     -- 升序排序
select * from score order by sscore desc; -- 降序排序

  • sort by

    在这里插入图片描述

    1、 sort by 会做两件事情：
  1)会将表文件拆分成多个文件(默认的分区)
  2)保证每一个输出的文件内容都有序

    1)设置reduce个数
 set mapreduce.job.reduces=3;
2）查询成绩按照成绩降序排列
 select * from score sort by sscore;
3)将查询结果导入到文件中（按照成绩降序排列）
 insert overwrite local directory '/export/data/exporthive/sort' select * from score sort by sscore;

  • distributed by + sort by（重点）

    1、distributed by 会按照某个字段进行分区，sort by 会给每个分区的数据进行排序

    1)设置reduce的个数，将我们对应的sid划分到对应的reduce当中去
set mapreduce.job.reduces=7;
2)通过distribute by进行数据的分区
insert overwrite local directory '/export/data/exporthive/distribute' select * from score distribute by sid sort by sscore;

  • cluster by

    1、当distributed by 和 sort by字段相同时：cluster by 等价于  distributed by  + sort by
 cluster by id => distributed by id sort by id
 
2、当reduce个数 < id的个数时，排序有意义
 id有100个  rduce 100个
 

    set mapreduce.job.reduces=2;
insert overwrite local directory '/export/data/exporthive/cluster_by'
select * from score  cluster by sid;

（重点）Hive的函数

内置函数

  • 数学函数

    -- 四舍五入函数
select round(3.1415926,4); -- 四舍五入 保留4位小数 3.1416

-- 获取 [1,100]之间的随机数
select `floor`(rand() * 100) + 1; 

-- 向下取整
select floor(2.8999); -- 2

  • 字符串函数

    -- 字符串拼接
select concat(rand(),'-',sid) as sid, sname from student;
select concat(rand(),'-',sid) as sid, sname from student;


-- 字符串拼接，带分隔符
select concat_ws('-','2022','10','15');
select log10(100)


-- 字符串截取
select substr('2022-12-23 10:13:45',1,4); -- 2022
select substr('2022-12-23 10:13:45',6,6); -- 12


-- 字符串替换
select regexp_replace('foobar', 'oo|ar', '');


-- 解析URL
select parse_url('http://www.facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST');
select parse_url('http://www.facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'PATH');
select parse_url('http://www.facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY');
select parse_url('http://www.facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY','k1');
select parse_url('http://www.facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY','k2');


-- 字符串切割
select split('2022-12-23','-');

  • 日期函数

    -- 获取当前的时间
select `current_date`(); -- 2022-10-25

-- 将日期转为指定的格式
select date_format('2022-1-1 1:1:1','yyyy-MM-dd HH:mm:ss') -- 2022-01-01 01:01:01


select to_date('2022-01-01 01:01:01'); -- 年月日
select year('2022-01-01 01:01:01');  -- 年
select month('2022-01-01 01:01:01'); -- 月
select day('2022-01-01 01:01:01');   -- 日
select hour('2022-01-01 01:01:01');  -- 小时
select minute('2022-01-01 01:01:01'); -- 分钟
select second('2022-01-01 01:01:01'); -- 秒

select quarter('2022-10-15 01:01:01');      -- 季度
select weekofyear('2022-10-15 01:01:01');   -- 获取今年的第几周
select `dayofweek`('2022-10-16 01:01:01');  -- 获取今天是周几(1-7)

select date_add('2022-10-15',10); --  将日期向后推10天
select date_add('2022-10-15',-10);--  将日期向前推10天
select date_sub('2022-10-15',10); --   将日期向前推10天

select abs(datediff('2022-02-13','2022-10-15')); -- 求日期之间的差值

条件函数

  • if语句

    -- 标记每个学生的及格和不及格的情况
select *, if(sscore >= 60,'及格','不及格') as flag from score;

-- 同时计算及格和不及格的平均分
select avg(if(sscore>=60,sscore,null)) as avg1,avg(if(sscore<60,sscore,null)) as avg2 from score

  • case when 语句

    • 格式1

      
/*

 订单号      金额         支付方式
 order_id amount payment_type    payment_name
 1001       50     1              支付宝支付
 1002       80     2              微信支付
 1003       60     3              余额支付
 1004       40     4              货到付款
 */

select
    case payment_type
        when 1
            then '支付宝支付'
        when 2
            then '微信支付'
        when 3
            then '余额支付'
        else '货到付款'
    end as payment_name;

    • 格式2

      select
    *,
    case when sscore >= 90 and sscore <= 100
            then '优秀'
        when  sscore >= 80
            then '良好'
        when sscore >= 60
            then '一般'
        else
             '及格'
    end as flag
from score;

类型转换函数

select cast(12.35 as int);
select cast('123' as int);
select cast('2020-12-05' as date);

行转列

在这里插入图片描述

-- 1、准备数据
20	SMITH   
30	ALLEN   
30	WARD    
20	JONES   
30	MARTIN  
30	BLAKE   
10	CLARK   
20	SCOTT   
10	KING    
30	TURNER  
20	ADAMS   
30	JAMES   
20	FORD    
10	MILLER  

-- 2、创建表
create table emp(
deptno int,
ename string
) row format delimited fields terminated by '\t';

-- 3、加载数据
load data local inpath "/export/data/hivedatas/emp.txt" into table emp;

-- 4、实现功能

-- collect_set(去重)/collect_list(不去重) 可以将每一组的ename存入一个集合中（set集合，list集合）
select deptno, collect_list(ename) from emp group by deptno;

select deptno,concat_ws("|",collect_set(ename)) as ems from emp group by deptno;
 
select deptno, collect_set(ename)[0] from emp group by deptno; -- 用索引访问

列转行

  • 概念

    在这里插入图片描述

  • 数据

    10      CLARK|KING|MILLER|SCOTT|KING
20      SMITH|CLARK|JONES|SCOTT|ADAMS|FORD
30      ALLEN|WARD|CLARK|MARTIN|BLAKE|TURNER|JAMES

  • 实现步骤

    -- 创建表
create table myhive.emp2(
deptno int,
names array<string>
)
row format delimited fields terminated by '\t'
collection items terminated by '|';


-- 加载数据
load data  local inpath '/export/data/hivedatas/emp2.txt' overwrite into table myhive.emp2;

use myhive;

-- 最终SQL，该SQL可以处理任何数据重复的情况
select * from emp2 lateral view explode(names) tmp_tb as name;

-- lateral view是一个侧视图关键字，后边一般跟UDTF函数（一进多出函数）
-- 格式： select 字段 from 原表  lateral view  UDTF函数
select deptno,name   from emp2 lateral view explode(names) tmp_tb as name;

reflect函数

  • 概念

    该函数可以调用Java中的静态方法，必须是静态的，而且是JDK自带的

  • 用法

    select col1,col2,reflect('java.lang.Math','max',col1,col2) as max_v from test_reflect;

select concat(reflect('java.util.UUID','randomUUID'),'-',sid) ,* from student;

开窗函数

  • 分组排序函数

    image-20221018210218434

  • 代码

    --  partition by 类似 group by
-- order by    排序
select
    *,
    row_number() over (partition by userid  order by pv desc) as rk1,
    rank() over (partition by userid  order by pv desc) as rk2,
    dense_rank() over (partition by userid  order by pv desc) rk3
from test_window_func1;

-- 如果没有partition by，则整张表就是一组
select
  *,
  dense_rank() over (order by pv desc) rk
from test_window_func1;



-- row_number() over() 如果没有order by ，则可以对数据进行去重
select
  *,
  row_number() over (partition by userid) rk
from test_window_func1;


-- 分组求 Top3
-- 子查询包裹的表必须有别名
select * from
(
    select
      *,
      dense_rank() over (partition by  userid order by pv desc) rk
    from test_window_func1
)t
where rk <=3;

-- with语句用法 
/*
with t1 as(
  select 语句
),    -- 这里不加逗号 
t2 as (
  select 语句
),      -- 这里加逗号 
t3 as (
  select 语句
)       -- 这里不加逗号 
select
* 
from t1 left join t2 on 条件
        left jon  t3 on 条件 ;    -- 这里加分号  
*/

with t1 as (
 select
      *,
      dense_rank() over (partition by  userid order by pv desc) rk
 from test_window_func1
)
select * from t1 where rk <= 3;

  • 聚合统计开窗函数(sum,avg,max,min)

    -- sum
-- 默认从开头累加到当前行
select userid,createtime,pv,
sum(pv) over(partition by userid order by createtime) as pv1
from test_window_func1;

-- 从开头累加到当前行
select userid,createtime,pv,
sum(pv) over(partition by userid order by createtime rows between unbounded preceding and current row) as pv2
from test_window_func1;

 --如果没有order  by排序语句  默认把分组内的所有数据进行sum操作
select userid,createtime,pv,
sum(pv) over(partition by userid) as pv3
from test_window_func1;

--  从当前行向上推三行 累加 到当前行
select userid,createtime,pv,
sum(pv) over(partition by userid order by createtime rows between 3 preceding and current row) as pv4
from test_window_func1;

--  从当前行向上推三行 累加 到当前行的下一行
select userid,createtime,pv,
sum(pv) over(partition by userid order by createtime rows between 3 preceding and 1 following) as pv5
from test_window_func1;

-- 从当前行累加到最后
select userid,createtime,pv,
sum(pv) over(partition by userid order by createtime rows between current row and unbounded following) as pv6
from test_window_func1;

-- 从上一行 累加到最后
select userid,createtime,pv,
sum(pv) over(partition by userid order by createtime rows between 1 preceding and unbounded following) as pv6
from test_window_func1;



-- max
-- 从开头累加到当前行
select userid,createtime,pv,
max(pv) over(partition by userid order by createtime rows between unbounded preceding and current row) as pv2
from test_window_func1;

-- avg
select userid,createtime,pv,
avg(pv) over(partition by userid order by createtime rows between unbounded preceding and current row) as pv2
from test_window_func1;

-- count
select userid,createtime,pv,
count(pv) over(partition by userid order by createtime rows between unbounded preceding and current row) as pv2
from test_window_func1;

  • 什么时候需要开窗函数

    当我们需要对数据进行分组统计的同时，还想保留表的明细数据， 则此时就可以用开窗

  • 常见的开窗函数

    -- 聚合开窗函数
count(); 	-- 窗口内总条数
sum();		-- 窗口内数据的和
min();		-- 窗口内最小值
max();		-- 窗口内最大值
avg();		-- 窗口内的平均值
-- 排序开窗函数
row_number();	-- 从1开始，按照顺序，生成分组内记录的序列
rank();			-- 生成数据项在分组中的排名，排名相等会在名次中留下空位
dense_rank();	-- 生成数据项在分组中的排名，排名相等会在名次中不会留下空位
ntile(n);		-- 将分区中已排序的行划分为大小尽可能相等的指定数量的排名的组，
				-- 并返回给定行所在的组的排名。
percent_rank(); -- 计算给定行的百分比排名。可以用来计算超过了百分之多少的人。(当前行的rank值-1)/(分组内的总行数-1)
				-- 如360小助手开机速度超过了百分之多少的人。
cume_dist();	-- 计算某个窗口或分区中某个值的累积分布。假定升序排序，则使用以下公式确定累积分布：
				-- 小于等于当前值x的行数 / 窗口或partition分区内的总行数。其中，x 等于 order by 子句中指定的列的当前行中的值。
-- 其他窗口函数
FIRST_VALUE();		-- 返回分区中的第一个值。
LAST_VALUE();		-- 返回分区中的最后一个值。
LAG(col,n,default);	-- 用于统计窗口内往上第n个值。	
LEAD(col,n,default);-- 用于统计窗口内往下第n个值。

自定义函数

  • UDF

    • pom.xml

        <dependencies>
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-exec</artifactId>
            <version>3.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-common</artifactId>
            <version>3.3.0</version>
        </dependency>
    </dependencies>

    • 代码

      /*
  需求：
    UDF --> 1行进来  一行出去
   15812345678 --->  158****5678
 */
@SuppressWarnings("all")  //压制警告
public class MyUDF extends UDF {
    public String evaluate(String phoneStr){
        String str1 = phoneStr.substring(0,3); //158
        String str2 = phoneStr.substring(7);  //5678

        return  str1 + "****" + str2;  //158****5678
    }
}

    • 转临时函数

      1、将代码打包
2、将jar包上传到 /export/server/hive/lib目录，并重命名我们的jar包名称
  cd /export/server/hive-3.1.2/lib
 mv day19_udf-1.0-SNAPSHOT.jar my_udf.jar
 
3、hive的客户端添加我们的jar包
 add jar /export/server/ hive-3.1.2 /lib/my_udf.jar
 
4、设置函数与我们的自定义函数关联-临时函数
 create temporary function my_jiami as 'cn.itcast.udf.MyUDF';
 
5、使用自定义的临时函数 
 select my_jiami('15812345678') from test_user;

    • 转永久函数

      1、将代码打包
2、将jar包上传到/export/server/hive-3.1.2/lib目录，并重命名我们的jar包名称
  cd /export/server/hive-3.1.2/lib
 mv day19_udf-1.0-SNAPSHOT.jar my_udf.jar
 
3、把自定义函数的jar上传到hdfs中.
hadoop fs -mkdir  /hive_func
hadoop fs -put my_udf.jar /hive_func  

4. 创建永久函数
 create function my_jiami2 as 'cn.itcast.udf.MyUDF'
    using jar 'hdfs://node1:8020/hive_func/my_udf.jar';
5、 验证
 select my_jiami2('15812345678') from test_user;
 
 
 -- 特别强调；
   DataGrip对永久函数支持不够友好，如果希望DataGrip能够永远识别永久函数，则需要重启Hadoop，重启Hive的两个RunJar

  • UDTF-单列

    • 代码

      public class MyUDTF extends GenericUDTF {
    private final transient Object[] forwardListObj = new Object[1];

    /**
      1、该函数只会执行一次，主要完成初始化工作
     */
    @Override
    public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {
        // 该集合用来指定转换之后出来的每一列叫什么名字（列名）
        List<String> fieldNames = new ArrayList<>();
        fieldNames.add("my_col1");
        //fieldNames.add("my_col2");


        // 该集合用来指定转换之后出来的每一列是什么类型（列类型） - String
        List<ObjectInspector> fieldOIs = new ArrayList<ObjectInspector>()  ;
        fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
        //fieldOIs.add(PrimitiveObjectInspectorFactory.javaIntObjectInspector);

         
        return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames, fieldOIs);

    }

    /**
     * 每转换一行，该方法就会执行一次
     * select my_udtf("zookeeper,hadoop,hdfs,hive,MapReduce",",") word;
     * select my_udtf("张三:19,李四:20",":",",")
     *
     *
     * @param objects
     * @throws HiveException
     */
    @Override
    public void process(Object[] objects) throws HiveException {
        //1:获取原始数据
        String args = objects[0].toString(); // "zookeeper,hadoop,hdfs,hive,MapReduce"

        //2:获取数据传入的第二个参数，此处为分隔符
        String splitKey = objects[1].toString(); //","


        //3.将原始数据按照传入的分隔符进行切分
        String[] fields = args.split(splitKey); // ["zookeeper","hadoop","hdfs","hive","MapReduce"]

        //4:遍历切分后的结果，并写出
        for (String field : fields) {
            //将每一个单词添加值对象数组
            forwardListObj[0] = field;
            //forwardListObj[1] = field;
            //将对象数组内容写出 ，每调用一次forward，就会多出一行
            forward(forwardListObj);
        }
    }

    @Override
    public void close() throws HiveException {

    }
}

    • 测试

       这里的测试和UDF一模一样
 
 select my_udtf("zookeeper,hadoop,hdfs,hive,MapReduce",",") word;

  • UDTF-双列

    import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;
import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;

import java.util.ArrayList;
import java.util.List;

public class MyUDTF2 extends GenericUDTF {
    private final transient Object[] forwardListObj = new Object[2];

    /**
      1、该函数只会执行一次，主要完成初始化工作
     */
    @Override
    public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {
        // 该集合用来指定转换之后出来的每一列叫什么名字（列名）
        List<String> fieldNames = new ArrayList<>();
        fieldNames.add("name");
        fieldNames.add("age");


        // 该集合用来指定转换之后出来的每一列是什么类型（列类型） - String ， Int
        List<ObjectInspector> fieldOIs = new ArrayList<ObjectInspector>()  ;
        fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
        fieldOIs.add(PrimitiveObjectInspectorFactory.javaIntObjectInspector);

         
        return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames, fieldOIs);

    }

    /**
     * 每转换一行，该方法就会执行一次
     * select my_udtf("张三:19,李四:20,王五:21",",",":")
     * @param objects
     * @throws HiveException
     */
    @Override
    public void process(Object[] objects) throws HiveException {
        //1:获取原始数据
        String line = objects[0].toString(); // "张三:19,李四:20,王五:21"

        //2:获取数据传入的第二个参数，此处逗号隔符
        String splitKey1 = objects[1].toString(); //","

        //3:获取数据传入的第二个参数，此处冒号分隔符
        String splitKey2 = objects[2].toString(); //":"

        //4:使用逗号将数据切分成一个个的键值对
        String[] array1 = line.split(splitKey1); //["张三:19","李四:20","王五:21"]

        //5:遍历数组
        for (String kv : array1) {
            String[] array2 = kv.split(splitKey2); // ["张三",19]
            forwardListObj[0] = array2[0];  // "张三"
            forwardListObj[1] = Integer.parseInt(array2[1]); //19

            forward(forwardListObj);
        }

    }

    @Override
    public void close() throws HiveException {

    }
}

    select my_udtf("张三:19,李四:20",",",":")

(重要)Hive的压缩方式

  • 概念

    1、Hive底层默认是MaReduce，Hive的压缩实际上就是MapReduce的压缩
2、MapReduce压缩分为Map端结果文件压缩和Reduce端结果文件压缩

  • 参数设置

    -- 开启Map端压缩
set hive.exec.compress.intermediate=true;
set mapreduce.map.output.compress=true;
set mapreduce.map.output.compress.codec= org.apache.hadoop.io.compress.SnappyCodec;

-- 1）开启hive最终输出数据压缩功能
set hive.exec.compress.output=true;
-- 2）开启mapreduce最终输出数据压缩
set mapreduce.output.fileoutputformat.compress=true;
-- 3）设置mapreduce最终数据输出压缩方式
set mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodec;
-- 4）设置mapreduce最终数据输出压缩为块压缩
set mapreduce.output.fileoutputformat.compress.type=BLOCK;

在这里插入图片描述

(重要)Hive的存储格式

在这里插入图片描述

  • 行存储

    • TextFile: 默认的文本存储
    • SequenceFile

    在这里插入图片描述

  • 列存储

    • ORC

      1：orc格式采用更加合理的数据的摆放方式，让元数据存储空间更小
2：orc格式默认内部是有压缩的，使用的压缩算法是ZLIB

    • PARQUET

​ 在这里插入图片描述

1、注意，我们以后几乎所有的表存储都是列存储，因为列存储更符合我们的需求
2、我们项目中使用的列存储时ORC格式
3、一般的列存储在内部会有一些索引，可以提高查询的效率

  • SQL实现

    -- 创建文本表
create table log_text (
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE ;


-- 给文本表加载数据
load data local inpath '/export/data/hivedatas/log.data' into table  log_text;

select * from log_text; -- Text表文件的大小是： 18.13M


-- 创建ORC表
create table log_orc(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
stored as orc;


load data local inpath '/export/data/hivedatas/log.data' into table  log_orc;

-- 查询文本表，并将查询的数据插入到ORC格式的表
insert overwrite  table log_orc select * from log_text;

select * from log_orc;  -- ORC表文件的大小是： 2.78M


-- 创建PARQUET表
create table log_parquet(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
stored as parquet ;


-- 查询文本表，并将查询的数据插入到PARQUET格式的表
insert overwrite  table log_parquet select * from log_text;


select * from log_orc;  -- PARQUET表文件的大小是： 13.09M

(重要)压缩方式和存储格式结合

  • 概念

    理论上Hive支持的任何一个存储格式都可以和hive支持的任何一个压缩方式进行结合

  • SQL

    -- 使用纯ORC，不使用任何的压缩算法
create table log_orc_none(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS orc tblproperties ("orc.compress"="NONE");

insert overwrite  table log_orc_none select * from log_text;


select * from log_orc_none ; -- 7.69M



-- 使用ORC，使用ORC默认的压缩算法ZLIB
create table log_orc_zlib(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS orc tblproperties ("orc.compress"="ZLIB");


insert overwrite  table log_orc_zlib select * from log_text;

select * from log_orc_none ; -- 2.78M


-- 使用ORC，使用SNAPPY压缩算法!!!!!!!!!!!!!!!!!!!!
create table log_orc_snappy(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS orc tblproperties ("orc.compress"="SNAPPY");

insert overwrite  table log_orc_snappy select * from log_text;

select * from log_orc_snappy ; 	-- 3.75 MB




create table log_parquet_snappy(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS parquet tblproperties ("orc.compress"="SNAPPY");


insert overwrite  table log_parquet_snappy select * from log_text;

select * from log_parquet_snappy ; -- 13.09 MB

  • 结论

    在这里插入图片描述

(重要)Hive的优化

key值问题-谓词下推

  • 普通查询

    select * from 表A a  left  join  表B b  on a.id = b.id where 过滤a表和b表的最新数据

  • 高效率查询-谓词下推

    select * from  表A a where 过滤a表的最新数据
select * from  表B b where 过滤b表的最新数据


select * from (select * from  表A a where 过滤a表的最新数据) a  left  join  (select * from  表B b where 过滤b表的最新数据) b  on a.id = b.id

key问题-加盐

在这里插入图片描述

set hive.exec.reducers.bytes.per.reducer=32123456;
set mapreduce.job.reduces=7;
INSERT OVERWRITE TABLE jointable
SELECT a.*
FROM nullidtable a
LEFT JOIN ori b ON CASE WHEN a.id IS NULL THEN concat('hive', rand()) ELSE a.id END = b.id;

Hive的去重问题

  • 问题描述
1	2022-01-01	zhangsan	80
1	2022-01-01	zhangsan	80
1	2022-01-01	zhangsan	80

2	2022-01-01	lisi	    70
2	2022-01-01	lisi	    70
2	2022-01-01	lisi	    70
------------去重--------------

1	2022-01-01	zhangsan	80
2	2022-01-01	lisi	    70
  • 去重方式
1、distinct
select distinct * from 表

 注意，在Hive中distinct必须只有一个reduce才能完成整体的去重，效率极低，千万不要用

在这里插入图片描述


2、group by 去重

select sid,sname,sbirth,ssex from  student2 group by sid,sname,sbirth,ssex;

在这里插入图片描述

3、row_number() over()去重

with t as (
 select
    *,
    row_number() over (partition by sid ) rk
 from student2
)
select * from t where rk = 1;

注意，生产环境就用该方式进行去重，这里不能使用rank和dense_rank()

如何演示数据倾斜

set hive.exec.reducers.bytes.per.reducer=32123456;
set mapreduce.job.reduces=7;
INSERT OVERWRITE TABLE jointable
SELECT a.*
FROM nullidtable a
LEFT JOIN ori b ON CASE WHEN a.id IS NULL THEN 'hive' ELSE a.id END = b.id;

在这里插入图片描述

并行执行

select * from A表 where  条件
union all
select * from B表 where  条件
union all
select * from C表 where  条件

当多个SQL之间没有依赖关系，则可以让他们同时执行

set hive.exec.parallel=true;          -- 开启并行执行      
set hive.exec.parallel.thread.number=16;   -- 最多允许有多少个SQL可以并行执行


select * from (select * from (select * from ))  -- 该SQL不能使用并行执行，因为有依赖关系

严格模式和非严格模式

#在严格模式下，会对你的很多SQL做一定的约束，约束一些你的低效率SQL

set hive.mapred.mode  = strict;  -- 开启严格模式
set hive.mapred.mode  =  nostrict; -- 开启非严格模式


select * from score where dt='2022-10-13'; -- 在严格模式下，分区表必须加分区条件

select * from score where dt='2022-10-13' order by sscore limit 10; -- order by 必须加limit

select * from (select * from score where dt='2022-10-13') a
            join (select * from score where dt='2022-10-13')b
            on a.sid = b.sid;  -- join时必须加on条件

哪些关键字只能有一个Reduce

1、 distinct

2、 order by
shmily_lee
关注
  • 3
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
【2022持续更新】大数据最全知识点整理-hive篇
欢迎来到李奇峰的数据世界
01-31 1809
本专栏博文会整理日常工作与面试中最常用到的大数据相关组件与Java语言的架构、概念、知识点，方便大家进行查阅。 涉及到的面试题以及答案均为博主搜罗整理，并加上自己的理解编写而成。同时博主会在部分题目的下方添加管遇此题深入理解的博文连接，方便读者的深入理解。 希望大家可以通过此篇博文对于大数据相关概念有一个更深入的理解 还有哪些想看的面试题，读者可以在评论区补充，博主会在一天内进行更新！
2021年-Hive大数据仓库-期末考试知识点重点-笔记整理.pdf
10-11
Hive大数据仓库知识点 Hive是一种基于Hadoop的大数据仓库工具，可以将结构化的数据文件映射为一个表，并提供类SQL查询功能。Hive的优点是学习成本低，可以通过类SQL语句快速实现简单的MapReduce统计。延迟较高，...
Hive知识点总结
weixin_70137450的博客
09-19 2221
大数据之Hive相关知识点总结
hive学习笔记
hyshucom
09-14 644
1.HIVE结构 Hive 是建立在 Hadoop 上的数据仓库基础构架。它提供了一系列的工具，可以用来进行数据提取转化加载（ETL），这是一种可以存储、查询和分析存储在 Hadoop 中的大规模数据的机制。Hive 定义了简单的类 SQL 查询语言，称为 QL，它允许熟悉 SQL 的用户查询数据。同时，这个语言也允许熟悉 MapReduce 开发者的开发自定义的 mapper 和 reduc...
hive主要知识点
Pepsi_zz的博客
02-26 2650
1、数据仓库 数据仓库是一个面向主题的、集成的、随时间变化的、但信息本身相对稳定的数据集合，用于对管理决策过程的支持。 2、数据库和数据仓库 数据库：是一种逻辑概念，用来存放数据的仓库。通过数据库软件来实现。数据库由许多表组成，表是二维的，一张表里面可以有很多字段，数据库的表，在与能够用二维表现多维关系。 数据仓库：是数据库概念的升级。从逻辑上理解，数据库和数据仓库没有区别，都是通过数据库软件实现的存放数据的地方，只不过从数据量来说，数据仓库要比数据库更庞大得多。数据仓库主要用于数据挖掘和数据分析，辅助领导
hive总结
TU_JCN的博客
02-22 463
文章目录1、数据仓库的基本概念2、数据仓库与数据库区别3、数据仓库分层架构4、为什么要对数据仓库分层？5、Hive是什么6、hive简述8、用户接口：Client9、SQL语言的分类10、hive的几种表11、hive的底层执行引擎有3种12、分桶表13、导入导出数据14、having 与 where 不同点15、4个By区别5、cluster by = distribute by + so...
基于Hadoop的数据仓库Hive学习指南.doc
04-06
【标题】：“基于Hadoop的数据仓库Hive学习指南” 【描述】：该文档是一份针对Hive的学习资料，旨在引导读者理解如何在Hadoop平台上利用Hive进行数据仓库操作和编程实践。它涵盖了Hive的基本概念、安装步骤、实验...
大数据 hive 实战数据
04-07
让我们深入探讨一下这两个部分以及与Hive、大数据、Hadoop和数据仓库相关的知识点。 首先，`video`数据可能包含了关于视频的各种信息，如视频ID、视频标题、时长、分类、上传日期、播放次数、评论数量等。这些数据...
数据仓库工程师面试题.docx
11-07
下面是对数据仓库的详细介绍和知识点总结： 什么是数据仓库？ 数据仓库（Data Warehouse）是指一个大型的存储数据的集合，用于解决企业数据分析和决策目的。它将来自多个数据源的数据进行筛选、整合和指导业务流程...
厦门大学林子雨版大数据基础入门培训课程 教师培训交流讲义-模块11-基于Hadoop的数据仓库Hive 共48页.ppt
02-02
总结来说，Hive作为大数据处理的重要工具，通过与Hadoop生态系统的紧密协作，为企业提供了高效、可扩展的数据仓库解决方案，尤其适用于大规模数据的批处理分析。而Impala等工具则为需要更高查询性能的应用提供了补充...
大数据之Hive知识点总结(一)
weixin_45524326的博客
01-13 349
大数据之Hive知识点总结(一) 1、数据仓库，英文名称：Data Warehouse,简写DW或DWH，数据仓库是构建面向分析的集成化数据环境，为企业提供决策支持，出于分析性报告和决策性支持目的而创建，数据仓库本身并不“生产”任何数据，同时自身不需要“消费”任何数据，数据来源于外部，并且开放给外部应用。 2、数据仓库的主要特征：数据仓库是面向主题、集成、非易失和时变的数据集合，用以支持管理决策。...
数据仓库Hive编程——Hive基础知识
热门推荐
冯·诺依曼
07-29 2万+
从早期的互联网主流大爆发开始，主要的搜索引擎公司和电子商务公司就一直在和不断增长的数据进行较量。最近，社交网站也遇到了同样的问题。如今，许多组织已经意识到他们所收集的数据是让他们了解他们的用户，提高业务在市场上的表现以及提高基础架构效率的一个宝贵的资源。 Hadoop生态系统就是为处理如此大数据集而产生的一个合乎成本效益的解决方案。Hadoop实现了一个特别的计算模型，也就是MapReduce，其...
Hive数据仓库基础知识
赵广陆
07-19 1490
目录1 事实表、维度表1.1 什么是事实表呢？1.2 什么是维度表呢？2 数据库三范式2.1 首先看第一范式(1NF):2.2 第二范式(2NF)2.3 第三范式(3NF)3 维度建模模型3.1 星型模型和雪花模型3.1.1 星型模型3.1.2 雪花模型4 数据仓库分层设计 1 事实表、维度表 首先我们来看两个名词：事实表和维度表 1.1 什么是事实表呢？ 事实表是指保存了大量业务数据的表，或者说保存了一些真实的行为数据的表 例如：销售商品所产生的订单数据 1.2 什么是维度表呢？ 首先说一下什么是维度
Hive与数据仓库——知识总结
weixin_45508154的博客
08-15 669
Hive介绍： Hive建立在Hadoop上的数据仓库基础框架，提供了一系列工具，存储，查询和分析存储在分布式存储系统中的大规模数据集，可以将结构化的数据文件映射为一张数据库表如（csv），并提供类SQL查询功能 Hive它能存储很大数据集，可以直接访问存储在HDFS中的文件，Hive支持MR，Spark，Tez三种分布式计算引擎，Hive的简单的类SQL查询语言，通过底层的计算引擎，将SQL转为具体的计算任务进行执行 Hive底层与数据库交互原理： 由于Hive的元数据可能要面临不断地更新、修改和读
hive数据仓库之笔记
DeaSun
06-10 602
简介 Hive 是一个离线数据数据仓库工具，用于解决海量结构化数据的日志的数据统计，其作用将结构化的数据文件映射成一张数据库表，并能提供简单的 SQL 的查询功能。Hive 的本质是将 HQL 语句转化为 MapReduce，所以其依赖于 HDFS，但是计算处理却依赖于 MapReduce。 HQL 与 SQL 的区别 HQL SQL 数据存储 HDFS，Hbase Local ...
大数据学习笔记——数据仓库hive重要的总结
youmianzhou的博客
08-05 821
1、外部表和内部表 Hive 表分为两类，即内部表和外部表。 所谓内部表，即Hive 管理的表， Hive 内部表的管理既包含逻辑以及语法上的，也包含实际物理意义上的，即创建 Hive 内部表时，数据将真实存在于表所在的目录内，删除内部表时，物理数据和文件也一并删除。 外部表 （ external table）则不然，其管理仅仅是在逻辑和语法意义上的，即新建表仅仅是指向一个外部目录而已。 同样，删除时也并不物理删除外部目录，而仅仅是将引用和定义 删除。外部表，指定 EXTERNAL 关键字后，因而不会把.
【Hive】位于Hadoop顶层的数据仓库——Hive知识点总结（图解）
丧心病狂Loli控的博客
11-13 613
  何为数据仓库（Data Warehouse）？ ▍初见与概念 数据仓库是一个面向主题的（Subject Oriented）、集成的（Integrated）、相对稳定的（Non-Volatile）、反映历史变化（Time Variant）的数据集合。 面向主题——从字面意思似乎难以理解，但通过对比就很好解释：与面向主题相对应的是面向应用。比如单纯的一次「支付」就是面向应用，而一整个「交易」就是面向主题 集成——集成就是把不同数据源的数据整合到一起，即使数据的类型是不同的。比如一个数据源使用男/
SpringBoot集成Tomcat、DispatcherServlet
最新发布
好记性不如烂笔头
07-24 1227
AbstractApplicationContext 的 refresh 方法是一个空壳方法，我们主要看它的实现类 ServletWebServerApplicationContext。
大厂数据仓库面试深度解析
总结，本面试题集全面覆盖了大数据生态中的核心技术和业务知识，对准备进入大厂的面试者来说，理解和掌握这些知识点是至关重要的。通过深入学习和实践，可以有效提升个人在大数据领域的专业能力。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值