做企业网站要注意什么,公众号微信平台,flashfxp 网站,可不可以建网站做微商hive内部表和外部表
默认为内部表#xff0c;外部表的关键字 #xff1a;external内部表#xff1a;对应的文件夹就在默认路径下 /user/hive/warehouse/库名.db/外部表#xff1a;数据文件在哪里都行#xff0c;无须移动数据
# students.txt
1,Lucy,girl,23
2,Tom,boy,2…hive内部表和外部表
默认为内部表外部表的关键字 external内部表对应的文件夹就在默认路径下 /user/hive/warehouse/库名.db/外部表数据文件在哪里都行无须移动数据
# 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 /stuhadoop fs -put students.txt /stuhiveselect * 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. 建表测试 arraystring
create external table array_tab(name string, work_locations arraystring) 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 mapstring,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 nameyaya;
yababastruct
这个数据类型的特点就是可以包含各种各样的数据类型。但是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 structname: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 ip192.168.1.1;hive分区表 有些时候数据是有组织的比方按日期/类型等分类如查询具体某一天的数据时不需要扫描全部目录所以会明显优化性能 一个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使用partioned by (xxx)来创建表的分区 分区表示例 # 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(date12000-01-01);hive show partitions employee; # date12000-01-01
hive desc employee;
OK
id int
name string
salary decimal(20,2)
date1 string# Partition Information
# col_name data_type commentdate1 string# 4. 加载数据到分区
hive load data local inpath /root/employ1.txt into table employee partition(date12000-01-01);# 5. 查询确认
hive select * from employee where date12000-01-01;分区表的用途常用指令 1. 避免全表扫描
2. 一般的应用是以天为单位一天是一个分区比如2000-01-01是一个目录对应的表的一个分区1. show partitions 表名;
2. alter table 表名 add partition(date12000-01-02);
3. msck repair table 表名; 此为修复分区
4. alter table 表名 drop partition(date12000-01-02);添加分区的两种方式 【1】添加分区方式一 先创分区再loadhive操作# 准备新的文件,employee2.txt,内容如下5,赵云,50006,张飞,60001.1) alter table employee add partition(date12000-01-02);1.2) load data local inpath /root/employ2.txt into table employee partition(date12000-01-02);1.3) select * from employee;【2】添加分区方式二 先创建上传hadoop操作再刷新hive操作# 准备新的文件,employee3.txt,内容如下7,司马懿,80008,典韦,78002.1) hadoop fs -mkdir /user/hive/warehouse/test2024.db/employee/date12000-01-032.2) hadoop fs -put /root/employ3.txt /user/hive/warehouse/test2024.db/employee/date12000-01-032.3) hive msck repair table employee; #修复分区表hive show partitions employee; hive select * from employee;练习-创建外部表分区表 【1】创建数据存放目录hadoop fs -mkdir /webloghadoop fs -mkdir /weblog/reporttime2000-01-01hadoop fs -mkdir /weblog/reporttime2000-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/reporttime2020-01-01hadoop fs -put data2.txt /weblog/reporttime2020-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分桶表 分桶是相对分区进行更细粒度的划分。分桶将整个数据内容安装某列属性值的hash值进行区分按照取模结果对数据分桶。如取模结果相同的数据记录存放到一个文件 桶表也是一种用于优化查询而设计的表类型。创建桶表时指定桶的个数、分桶的依据字段hive就可以自动将数据分桶存储。查询时只需要遍历一个桶里的数据或者遍历部分桶这样就提高了查询效率 桶表创建 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.reduces4;# 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# 命令行查看
[rootvm ~]# hadoop fs -text /user/hive/warehouse/test2024.db/stu_buck/000000_0
4,羚羊夫人,Hadoop
[rootvm ~]# hadoop fs -text /user/hive/warehouse/test2024.db/stu_buck/000001_0
5,奥特曼,AI
1,佩奇,Python # 根据id 对桶的个数取模了关于分桶表 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 和 rHive之影评分析案例
数据说明
现有三份数据具体数据如下 users.txt 【1】数据格式共有6040条数据3:M:25:15:55117
【2】对应字段用户id、 性别、 年龄、 职业、 邮政编码user_id gender age work codingmovies.txt 【1】数据格式共有3883条数据3:Grumpier Old Men (1995):Comedy|Romance
【2】对应字段电影ID、 电影名字、电影类型movie_id name genresratings.txt 【1】数据格式共有1000209条数据1:661:3:978302109
【2】对应字段用户ID、 电影ID、 评分、 评分时间戳user_id movie_id rating times案例说明
求被评分次数最多的10部电影并给出评分次数电影名评分次数求movieid 2116这部电影各年龄的平均影评年龄影评分分别求男性女性当中评分最高的10部电影性别电影名影评分求最喜欢看电影影评次数最多的那位女性评最高分的10部电影的平均影评分观影者电影名影评分
库表映射实现 建库 create database movie;
use movie;创建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;创建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;创建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;案例实现 求被评分次数最多的10部电影并给出评分次数电影名评分次数 【1】需求字段1.1) 电影名: t_movie.name1.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_idc.movie_id
group by b.name
order by total desc
limit 10;求movieid 2116这部电影各年龄的平均影评年龄影评分 【1】需求字段1.1) 年龄: t_user.age1.2) 影评分: t_rating.rating
【2】思路t_user和t_rating表进行联合查询movie_id2116过滤条件年龄分组
【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_idc.user_id
where c.movie_id2116
group by a.age;分别求男性女性当中评分最高的10部电影性别电影名影评分 【1】需求字段1.1) 性别: t_user.gender1.2) 电影名:t_movie.name1.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_ida.user_id
join t_moive b on c.moive_idb.movie_id
where a.genderF
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_ida.user_id
join t_moive b on c.moive_idb.movie_id
where a.genderM
group by b.name order by avgrate desc
limit 10;求最喜欢看电影影评次数最多的那位女性评最高分的10部电影的平均影评分电影编号电影名影评分 【1】需求字段1.1) 电影编号: t_rating.movie_id1.2) 电影名: t_movie.name1.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_ida.user_id
where a.genderF
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_id1150 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_idc.movie_id
join t_movie on c.movie_idb.movie_id
group by d.movie_id, b.name;
