04hive数仓内外部表复杂数据类型与分区分桶

时间:2024-03-11 13:04:51
hive内部表和外部表
  1. 默认为内部表,外部表的关键字 :external
  2. 内部表:对应的文件夹就在默认路径下 /user/hive/warehouse/库名.db/
  3. 外部表:数据文件在哪里都行,无须移动数据
# students.txt
1,Lucy,girl,23
2,Tom,boy,23
3,Jim,boy,35

【1】创建外部表并查看(location指映射的文件路径)
hive> create external table studenttab(id int,name string,sex string, age int )row format delimited fields terminated by ',' location '/stu';

【2】上传文件并测试
	hadoop fs -mkdir /stu
	hadoop fs -put students.txt /stu
	hive>select * from studenttab;
	发现已经存在了数据,而且在默认路径下根本就没有文件夹

【3】 删除表
	2.1)删除内部表 drop table t2; 元数据和具体数据全部删除
	2.2)删除外部表 drop table studenttab; 发现数据还在,只是删除了元数据
	
# 内部表是受hive管理的表,外部表是不受hive管理的表,
# 实际工作中外部表使用较多,先在分布式文件系统中传文件,然后管理

内部表和外部表区别总结

【1】内部表无external关键字,外部表有
【2】内部表由Hive自身管理,外部表由HDFS管理
【3】内部表/user/hive/warehouse位置,外部表存在hdfs中任意位置
【4】内部表元数据及存储数据一起删除,外部表会删除元数据,HDFS上不会被删除

Hive练习

在电商网站上,当我们进入到某电商页面浏览商品时,就会产生用户对商品访问情况的数据,包含两个字段(商品id,点击次数),以逗号分隔,由于数据量很大,所以为了方便统计,我们只截取了一部分数据,内容如下:

1010031,100
1010102,100
1010152,97
1010178,96
1010280,104
1010320,103
1010510,104
1010603,96
1010637,97

问题1: 实现文件和表的映射

create table product_tab(
goods_id int,
goods_click int
)row format delimited fields terminated by ',';

load data local inpath '/root/product.txt' into table product_tab;

select goods_click,goods_id from product_tab order by goods_click; # 实现排序
Hive复杂数据类型
array

1.1 特点为集合里的每一个字段都是一个具体的信息,不会是那种key与values的关系

1.2 建表时,字段名 array

1.3 建表时,collection items terminated by ‘分隔符’

1.4 详情请见如下示例,比如文件内容如下:

# 数据样本 array.txt
yaya	beijing,shanghai,tianjin,hangzhou # 姓名  (\t分割)  工作地点
lucy	shanghai,chengdu,wuhan,shenzhen

# 2. 将本地文件上传至hdfs
hadoop fs -mkdir /stuinfo
hadoop fs -put array.txt /stuinfo

# 3. 建表测试   array<string>
create external table array_tab(name string, work_locations array<string>) row format delimited fields terminated by '\t' collection items terminated by ',' location '/stuinfo';

# 4. 基本查询
hive> select * from array_tab;
yaya    ["beijing","shanghai","tianjin","hangzhou"]
lucy    ["shanghai","chengdu","wuhan","shenzhen"]

# 5. 查询在天津工作过的用户信息
hive> select * from array_tab where array_contains(work_locations, 'tianjin');
yaya    ["beijing","shanghai","tianjin","hangzhou"]

# 6. 查询所有人的第一工作城市
hive> select name,work_locations[0] from array_tab;
yaya    beijing
lucy    shanghai

# 7. 查询 array_contains()函数
#查询在天津工作过的用户信息
hive> select * from array_tab where array_contains(work_locations, 'tianjin');
yaya    ["beijing","shanghai","tianjin","hangzhou"]
map

样本中部分字段基于 key-value 模型

delimited fields terminated by ','
collection items terminated by '#' 
map keys terminated by ':';
# 1. 数据样本:map.txt
# 编号(id)   姓名(name)   家庭成员(member)  年龄(age)
1,yaya,father:yababa#mother:yamama#brother:daya,28
2,pandas,father:panbaba#mother:panmama#brother:dapan,25
3,ai,father:aibaba#mother:aimama#brother:daai,30
4,ds,father:dsbaba#mother:dsmama#brother:dads,29
        
# 2. 创建对应表
create table map_tab(
id int,
name string,
members map<string,string>,
age int
)row format delimited fields terminated by ','
collection items terminated by '#' 
map keys terminated by ':';

# 3. 数据映射
load data local inpath '/root/map.txt' into table map_tab;

# 4. 基本查询
select * from map_tab;
1       yaya    {"father":"yababa","mother":"yamama","brother":"daya"}  28
2       pandas  {"father":"panbaba","mother":"panmama","brother":"dapan"}       25
3       ai      {"father":"aibaba","mother":"aimama","brother":"daai"}  30
4       ds      {"father":"dsbaba","mother":"dsmama","brother":"dads"}  29

# 5. 查询 yaya 的爸爸是谁
select members['father'] from map_tab where name='yaya';
yababa
struct

这个数据类型的特点就是可以包含各种各样的数据类型。但是struct可以是任意数据类型,在写struct数据类型时,在<>中要写清楚struct字段中的字段名称跟数据类型

delimited fields terminated by ','
collection items terminated by '#' 
# 1. 数据样本:struct.txt
# IP         用户信息
192.168.1.1#yaya:30
192.168.1.2#pandas:50
192.168.1.3#tiger:60
192.168.1.4#lion:70

# 2. 创建对应表
create table struct_tab(
ip string,
userinfo struct<name:string,age:int>
)row format delimited fields terminated by '#'
collection items terminated by ':';

# 3. 数据映射
load data local inpath '/root/struct.txt' into table struct_tab;

# 4. 查询所有数据
select * from struct_tab;

# 5. 查询所有用户的名字
select userinfo.name from struct_tab;

# 6. 查询访问过192.168.1.1的用户的名字
select userinfo.name from struct_tab where ip='192.168.1.1';
hive分区表
  1. 有些时候数据是有组织的,比方按日期/类型等分类,如查询具体某一天的数据时,不需要扫描全部目录,所以会明显优化性能

  2. 一个Hive表在HDFS上是有一个对应的目录来存储数据,普通表的数据直接存储在这个目录下,而分区表数据存储时,是再划分子目录来存储的

    #原始表
    /user/hive/warehouse/test2024.db/logtab/{t1.log,t2.log,t3.log}
    
    # 分区表
    /user/hive/warehouse/test2024.db/logtab/t1/t1.log
    /user/hive/warehouse/test2024.db/logtab/t2/t2.log
    /user/hive/warehouse/test2024.db/logtab/t3/t3.log
    
  3. 使用partioned by (xxx)来创建表的分区

  4. 分区表示例

    # 1. 样本数据 - employee.txt,按天来做管理,1天一个分区,意义在于优化查询
    # 员工编号(id)    员工姓名(name)   员工工资(salary)
    1,赵丽颖,100000
    2,超哥哥,12000
    3,迪丽热巴,130000
    4,宋茜,800000
    
    # 2. 创建分区表 - 内部表
    create table employee(
    id int,
    name string,
    salary decimal(20,2)
    ) partitioned by (date1 string) row format delimited fields terminated by ',';
      
    # 3. 添加分区并查看 - 此时在hdfs中已经创建了该分区的对应目录
    hive> alter table employee add partition(date1='2000-01-01');
    
    hive> show partitions employee;   # date1=2000-01-01
    hive> desc employee;
    OK
    id                      int
    name                    string
    salary                  decimal(20,2)
    date1                   string
    
    # Partition Information
    # col_name              data_type               comment
    
    date1                   string
    
    # 4. 加载数据到分区
    hive> load data local inpath '/root/employ1.txt' into table employee partition(date1='2000-01-01');
    
    # 5. 查询确认
    hive> select * from employee where date1='2000-01-01';
    
  5. 分区表的用途&常用指令

    1. 避免全表扫描
    2. 一般的应用是以天为单位,一天是一个分区,比如2000-01-01是一个目录,对应的表的一个分区
    
    1. show partitions 表名;
    2. alter table 表名 add partition(date1='2000-01-02');
    3. msck repair table 表名;     此为修复分区
    4. alter table 表名 drop partition(date1='2000-01-02');
    
  6. 添加分区的两种方式

    【1】添加分区方式一 先创分区,再load(hive操作)
    	# 准备新的文件,employee2.txt,内容如下
    	5,赵云,5000
    	6,张飞,6000
    	
    	1.1) alter table employee add partition(date1='2000-01-02');
    	1.2) load data local inpath '/root/employ2.txt' into table employee partition(date1='2000-01-02');
    	1.3) select * from employee;
    	
    	
    【2】添加分区方式二 先创建上传(hadoop操作),再刷新(hive操作)
    	# 准备新的文件,employee3.txt,内容如下
    	7,司马懿,8000
    	8,典韦,7800
    	2.1) hadoop fs -mkdir /user/hive/warehouse/test2024.db/employee/date1=2000-01-03
    	2.2) hadoop fs -put '/root/employ3.txt' /user/hive/warehouse/test2024.db/employee/date1=2000-01-03
    	2.3) hive> msck repair table employee;		#修复分区表
    		 hive> show partitions employee; 
             hive> select * from employee;
    

    练习-创建外部表分区表

    【1】创建数据存放目录
    	hadoop fs -mkdir /weblog
    	hadoop fs -mkdir /weblog/reporttime=2000-01-01
    	hadoop fs -mkdir /weblog/reporttime=2000-01-02
    
    【2】准备两个文件
    # data1.txt
    1 rose 200
    2 tom 100
    3 lucy 200
    # data2.txt
    4 yaya 300
    5 nono 100
    6 doudou 200
    
    【3】将文件存入对应分区目录
    	hadoop fs -put data1.txt /weblog/reporttime=2020-01-01
    	hadoop fs -put data2.txt /weblog/reporttime=2020-01-02
    
    【4】创建外部表
    	create external table w1(id int, name string, score int) partitioned by(reporttime string) row format delimited fields terminated by ' ' location '/weblog';
    	
    【5】修复分区
    	msck repair table w1;
    	
    【6】确认分区	
    	show partitions w1;
    	
    【7】查询确认
    	select * from w1;
    
hive分桶表
  1. 分桶是相对分区进行更细粒度的划分。分桶将整个数据内容安装某列属性值的hash值进行区分,按照取模结果对数据分桶。如取模结果相同的数据记录存放到一个文件

  2. 桶表也是一种用于优化查询而设计的表类型。创建桶表时,指定桶的个数、分桶的依据字段,hive就可以自动将数据分桶存储。查询时只需要遍历一个桶里的数据,或者遍历部分桶,这样就提高了查询效率

  3. 桶表创建

    1. 分桶表创建之前需要开启分桶功能
    2. 分桶表创建的时候,分桶的字段必须是表中已经存在的字段,即要按照表中某个字段进行分开
    3. 针对分桶表的数据导入,load data的方式不能够导成分桶表的数据,没有分桶效果
    4. 用 insert + select ,插入
    
    # 样本数据 - 学生选课系统:course.txt
    # 学生编号(id)   学生姓名(name)  选修课程(course)
    1,佩奇,Python
    2,乔治,Hive
    3,丹尼,Python
    4,羚羊夫人,Hadoop
    5,奥特曼,AI
    6,怪兽,DS
    
    # 1. 先创建普通表,导入数据 - student,
    create table student(
    id int,
    name string,
    course string
    )row format delimited fields terminated by ',';
    
    load data local inpath '/root/course.txt' into table student;
    
    # 2. 开启分桶功能并指定桶的数量
    set hive.enforce.bucketing = true;
    set mapreduce.job.reduces=4;
    
    # 3. 创建分桶表 - stu_buck
    create table stu_buck(
    id int,
    name string,
    course string
    ) clustered by(id) into 4 buckets row format delimited fields terminated by ',';
    
    # 4. 分桶表数据导入
    insert into table stu_buck select * from student;  #会触发mapreduce
    select * from stu_buck;
    
    # 5. 到浏览器中查看,发现stu_buck文件夹中出现了4个桶表 
    000000_0  000001_0 000002_0 000003_0
    
    # 命令行查看
    [root@vm ~]# hadoop fs -text /user/hive/warehouse/test2024.db/stu_buck/000000_0
    4,羚羊夫人,Hadoop
    [root@vm ~]# hadoop fs -text /user/hive/warehouse/test2024.db/stu_buck/000001_0
    5,奥特曼,AI	
    1,佩奇,Python		# 根据id 对桶的个数取模了
    
  4. 关于分桶表

    4.1 想要把表格划分的更加细致

    4.2 分桶表的数据采用 insert + select ,插入的数据来自于查询结果(查询时候执行了mr程序)

    4.3 分桶表也是把表所映射的结构化数据文件分成更细致的部分,但是更多的是用在join查询提高效率之上,只需要把 join 的字段在各自表当中进行分桶操作即可

经常把连接查询经常用来做条件判断的字段(即on后面的字段)作为分桶的依据字段。

在这里插入图片描述

hive常用字符串操作函数
select length('hello2020');
select length(name) from employee; 用途: 比如说第一列为手机号,我来查证手机号是否合法

select reverse('hello');

select concat('hello', 'world')  # 假如说有一个表是三列,可以拼接列
   select concat(id,name) from w1;
   select concat(id,',',name)  from w1;

select concat_ws('.', 'www', 'baidu', 'com'); # 只能操作字符串,不能有整型

select substr('abcde', 2);  # 用途:截取身份证号的后四位?可以使用此方法

select upper('ddfFKDKFdfd')

select lower('dfdfKJKJ')	# 可以做数据的转换

select trim("     dfadfd.   "); # 去除左右两侧的空白,可以加 l  和 r

Hive之影评分析案例

数据说明

现有三份数据,具体数据如下:

  1. users.txt

    【1】数据格式(共有6040条数据)
    	3:M:25:15:55117
    【2】对应字段
    	用户id、 性别、  年龄、 职业、 邮政编码
    	user_id  gender  age   work    coding
    
  2. movies.txt

    【1】数据格式(共有3883条数据)
    	3:Grumpier Old Men (1995):Comedy|Romance
    【2】对应字段
    	电影ID、 电影名字、电影类型
    	movie_id   name    genres
    
  3. ratings.txt

    【1】数据格式(共有1000209条数据)
    	1:661:3:978302109
    【2】对应字段
    	用户ID、  电影ID、  评分、  评分时间戳
    	user_id   movie_id  rating    times
    
案例说明
  1. 求被评分次数最多的10部电影,并给出评分次数(电影名,评分次数)
  2. 求movieid = 2116这部电影各年龄的平均影评(年龄,影评分)
  3. 分别求男性,女性当中评分最高的10部电影(性别,电影名,影评分)
  4. 求最喜欢看电影(影评次数最多)的那位女性评最高分的10部电影的平均影评分(观影者,电影名,影评分)
库表映射实现
  1. 建库

    create database movie;
    use movie;
    
  2. 创建t_user表并导入数据 - a

    create table t_user(
    user_id bigint,
    gender string,
    age int,
    work string,
    code string
    )row format delimited fields terminated by ':';
    
    load data local inpath '/home/tarena/hadoop/users.txt' into table t_user;
    
  3. 创建t_movie表并导入数据 - b

    create table t_movie(
    movie_id bigint,
    name string,
    genres string
    )row format delimited fields terminated by ':';
    
    load data local inpath '/home/tarena/hadoop/movies.txt' into table t_movie;
    
  4. 创建t_rating表并导入数据 - c

    create table t_rating(
    user_id bigint,
    movie_id bigint,
    rating double,
    times string
    )row format delimited fields terminated by ':';
    
    load data local inpath '/home/tarena/hadoop/ratings.txt' into table t_rating;
    
案例实现
  1. 求被评分次数最多的10部电影,并给出评分次数(电影名,评分次数)

    【1】需求字段
    	1.1) 电影名: t_movie.name
    	1.2) 评分次数: t_rating.rating
    【2】思路
    	按照电影名进行分组统计,求出每部电影的评分次数并按照评分次数降序排序
    【3】实现
    create table result1 as
    select b.name as name,count(b.name) as total from t_movie b inner join t_rating c on b.movie_id=c.movie_id
    group by b.name
    order by total desc
    limit 10;
    
  2. 求movieid = 2116这部电影各年龄的平均影评(年龄,影评分)

    【1】需求字段
    	1.1) 年龄: t_user.age
    	1.2) 影评分: t_rating.rating
    【2】思路
    	t_user和t_rating表进行联合查询,movie_id=2116过滤条件,年龄分组
    【3】实现
    create table result3 as
    select a.age as age, avg(c.rating) as avgrate from t_user a
    join t_rating c
    on a.user_id=c.user_id 
    where c.movie_id=2116 
    group by a.age;
    
  3. 分别求男性,女性当中评分最高的10部电影(性别,电影名,影评分)

    【1】需求字段
    	1.1) 性别: t_user.gender
    	1.2) 电影名:t_movie.name
    	1.3) 影评分:t_rating.rating
    【2】思路
    	2.1) 三表联合查询
    	2.2) 按照性别过滤条件,电影名作为分组条件,影评分作为排序条件进行查询
    【3】实现
    3.1) 女性当中评分最高的10部电影
    create table result2_F as
    select 'F' as sex, b.name as name,  avg(c.rating) as avgrate 
    from t_rating c join t_user a on c.user_id=a.user_id
    join t_moive b on c.moive_id=b.movie_id
    where a.gender='F'
    group by b.name order by avgrate desc 
    limit 10;
    
    3.2) 男性当中评分最高的10部电影
    create table result2_M as
    select 'M' as sex, b.name as name,  avg(c.rating) as avgrate 
    from t_rating c join t_user a on c.user_id=a.user_id
    join t_moive b on c.moive_id=b.movie_id
    where a.gender='M'
    group by b.name order by avgrate desc 
    limit 10;
    
  4. 求最喜欢看电影(影评次数最多)的那位女性评最高分的10部电影的平均影评分(电影编号,电影名,影评分)

    【1】需求字段
    	1.1) 电影编号: t_rating.movie_id
    	1.2) 电影名: t_movie.name
    	1.3) 影评分: t_rating.rating
    【2】思路
    	2.1) 先找出最喜欢看电影的那位女性
    	2.2) 根据2.1中的女性user_id作为where过滤条件,以看过的电影的影评分rating作为排序条件进行排序,找出评分最高的10部电影
    	2.3) 求出2.2中10部电影的平均分
    	
    【3】实现
    3.1) 最喜欢看电影的女性(t_rating.user_id, 次数)
    create table result4_A as 
    select c.user_id,count(c.user_id) as total from t_rating c 
    join t_user a on c.user_id=a.user_id 
    where a.gender='F'
    group by c.user_id order by total desc limit 1;
    
    3.2) 找出那个女人评分最高的10部电影
    create table result4_B as
    select c.movie_id, c.rating as rating from t_rating c
    where c.user_id=1150 order by rating desc limit 10;
    
    3.3) 求出10部电影的平均分
    select d.movie_id as movie_id, b.name as name,avg(c.rating) from result4_B d join t_rating on d.movie_id=c.movie_id
    join t_movie on c.movie_id=b.movie_id
    group by d.movie_id, b.name;