大数据学习之Hive——08Hive SQL练习之影评案例

最新推荐文章于 2023-07-11 16:25:19 发布
最新推荐文章于 2023-07-11 16:25:19 发布
阅读量2.8k 收藏 29
点赞数 2
分类专栏： Hive 文章标签： hive hadoop sql
原文链接：https://www.cnblogs.com/qingyunzong/p/8727264.html#_label2_1
版权

转载至: https://www.cnblogs.com/qingyunzong/p/8727264.html#_label2_1

1. 案例说明

1. 现有如此三份数据：

  1. users.dat

    1. 数据格式为： 2::M::56::16::70072，
    2. 共有6040条数据
    3. 对应字段为：UserID BigInt, Gender String, Age Int, Occupation String, Zipcode String
    4. 对应字段中文解释：用户id，性别，年龄，职业，邮政编码

  2. movies.dat

    1. 数据格式为： 2::Jumanji (1995)::Adventure|Children’s|Fantasy，
    2. 共有3883条数据
    3. 对应字段为：MovieID BigInt, Title String, Genres String
    4. 对应字段中文解释：电影ID，电影名字，电影类型

  3. ratings.dat 数据格式为： 1::1193::5::978300760，

    1. 共有1000209条数据
    2. 对应字段为：UserID BigInt, MovieID BigInt, Rating Double, Timestamped String
    3. 对应字段中文解释：用户ID，电影ID，评分，评分时间戳
2. 题目要求

  1. 数据要求：

    1. 写shell脚本清洗数据。（hive不支持解析多字节的分隔符，也就是说hive只能解析’:’, 不支持解析’::’，所以用普通方式建表来使用是行不通的，要求对数据做一次简单清洗）
    2. 使用Hive能解析的方式进行

  2. Hive要求：

    1. 正确建表，导入数据（三张表，三份数据），并验证是否正确
    2. 求被评分次数最多的10部电影，并给出评分次数（电影名，评分次数）
    3. 分别求男性，女性当中评分最高的10部电影（性别，电影名，影评分）
    4. 求movieid = 2116这部电影各年龄段（因为年龄就只有7个，就按这个7个分就好了）的平均影评（年龄段，影评分）
    5. 求最喜欢看电影（影评次数最多）的那位女性评最高分的10部电影的平均影评分（观影者，电影名，影评分）
    6. 求好片（评分>=4.0）最多的那个年份的最好看的10部电影
    7. 求1997年上映的电影中，评分最高的10部Comedy类电影
    8. 该影评库中各种类型电影中评价最高的5部电影（类型，电影名，平均影评分）
    9. 各年评分最高的电影类型（年份，类型，影评分）
    10. 每个地区最高评分的电影名，把结果存入HDFS（地区，电影名，影评分）

2. 数据下载

数据资源

3. 前提准备

1. 正确建表，导入数据（三张表，三份数据），并验证是否正确

  1. 分析需求
    需要创建一个数据库movie，在movie数据库中创建3张表，t_user，t_movie，t_rating

    t_user: userid bigint, sex string, age int, occupation string, zipcode string
t_movie: movieid bigint, moviename string, movietype string
t_rating: userid bigint, movieid bigint, rate double, times string

    原始数据是以::进行切分的，所以需要使用能解析多字节分隔符的Serde即可

    使用RegexSerde, 需要两个参数：

    input.regex = "(.*)::(.*)::(.*)"
output.format.string = "%1$s %2$s %3$s"

  2. 创建数据库

    drop database if exists movie;
create database if not exists movie;
use movie;

  3. 创建t_user表

    create table t_user(
userid bigint,
sex string,
age int,
occupation string,
zipcode string) 
row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe' 
with serdeproperties('input.regex'='(.*)::(.*)::(.*)::(.*)::(.*)','output.format.string'='%1$s %2$s %3$s %4$s %5$s')
stored as textfile;

  4. 创建t_movie表

    create table t_movie(
movieid bigint,
moviename string,
movietype string) 
row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe' 
with serdeproperties('input.regex'='(.*)::(.*)::(.*)','output.format.string'='%1$s %2$s %3$s')
stored as textfile;

  5. 创建t_rating表

    create table t_rating(
userid bigint,
movieid bigint,
rate double,
times string) 
row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe' 
with serdeproperties('input.regex'='(.*)::(.*)::(.*)::(.*)','output.format.string'='%1$s %2$s %3$s %4$s')
stored as textfile;

  6. 导入数据

2. 求被评分次数最多的10部电影，并给出评分次数（电影名，评分次数）
hive> select t1.moviename, count(t1.moviename) as total 
	> from t_movie t1 join t_rating t2 on t1.movieid=t2.movieid 
	> group by t1.moviename 
	> order by total desc 
	> limit 10;

在这里插入图片描述

3. 分别求男性，女性当中评分最高的10部电影（性别，电影名，影评分）

hive> select "F" as sex, moviename as name, avg(rate) as average, count(t2.movieid) as total 
	> from t_user t1 
	> join t_rating t2 on t1.userid=t2.userid 
	> join t_movie t3 on t2.movieid=t3.movieid 
	> where sex="F" 
	> group by moviename 
	> having total >= 50 
	> order by average desc 
	> limit 10;

在这里插入图片描述

hive> select "M" as sex, moviename as name, avg(rate) as average, count(t2.movieid) as total 
	> from t_user t1 
	> join t_rating t2 on t1.userid=t2.userid 
	> join t_movie t3 on t2.movieid=t3.movieid 
	> where sex="M" 
	> group by moviename 
	> having total >= 50 
	> order by average desc 
	> limit 10;

在这里插入图片描述

4. 求movieid = 2116这部电影各年龄段（因为年龄就只有7个，就按这个7个分就好了）的平均影评
hive> select age, avg(rate) as avg_rate 
	> from t_user t1 join t_rating t2 on t1.userid=t2.userid 
	> where movieid=2116 
	> group by age;

在这里插入图片描述

5. 求最喜欢看电影（影评次数最多）的那位女性评最高分的10部电影的平均影评分（观影者，电影名，影评分）

  1. 先求出观影次数最多的女用户的userid

    hive> select userid, count(t2.movieid) as total 
	> from t_user t1 join t_rating t2 on t1.userid=t2.userid 
	> where sex="F" 
	> group by t2.userid 
	> order by total desc 
	> limit 1;

    在这里插入图片描述

  2. 再求出该用户评分最高的10部电影的movieid

    hive> create table tmp1 as 
	> select t1.movieid,moviename, rate 
	> from t_movie t1 join t_rating t2 on t1.movieid=t2.movieid 
	> where t2.userid=1150 
	> order by rate desc 
	> limit 10;

    在这里插入图片描述

  3. 求出这10部电影的平均评分

    hive> select t2.movieid, moviename, avg(t2.rate) as average 
	> from tmp1 t1 
	> join t_rating t2 on t1.movieid=t2.movieid 
	> join t_movie t3 on t2.movieid=t3.movieid 
	> group by t2.movieid, t3.moviename;

    在这里插入图片描述

6. 求好片（评分>=4.0）最多的那个年份的最好看的10部电影

  1. 截取出年份, 并计算出每部电影的平均分, 并将结果写入之间表中:

    hive> create table answer6_1 as 
	> select t1.movieid, moviename, substr(moviename, -5, 4) as years, avg(rate) as avgrate 
	> from t_movie t1 join t_rating t2 on t1.movieid=t2.movieid 
	> group by t1.movieid, moviename;

    在这里插入图片描述

  2. 求出好片（评分>=4.0）最多的那个年份

    hive> create table answer6_2 as 
	> select years, count(movieid) as total 
	> from answer6_1 t1 
	> where avgrate >=4 
	> group by years 
	> order by total desc 
	> limit 1;

    在这里插入图片描述

  3. 求出1998年评分最高的10部电影

    hive> select movieid, moviename, avgrate 
	> from answer6_1 
	> where years=1998 
	> order by avgrate desc 
	> limit 10;

    在这里插入图片描述

7. 求1997年上映的电影中，评分最高的10部Comedy类电影

instr(string str, string substr): 查找字符串str中子字符串substr出现的位置，如果查找失败将返回0
lcase/lower: 字符串转小写函数
ucase/upper: 字符串转大写函数

  1. 需要电影类型，所有可以将第六步中求出answer6_1表和t_movie表进行联合查询

    hive> create table answer7_1 as 
	> select b.movieid as id, b.moviename as name, b.years as years, b.avgrate as rate, a.movietype as type 
	> from t_movie a join answer6_1 b on a.movieid=b.movieid;

  2. 从answer7_1按照电影类型中是否包含Comedy和按照评分>=4.0作为where过滤条件，按照评分作为排序条件进行查询，将结果保存到answer7_2中

    hive> create table answer7_2 as 
	> select t.id as id, t.name as name, t.rate as rate 
	> from answer7_1 t 
	> where t.years=1997 and instr(lcase(t.type),'comedy') >0 
	> order by rate desc
	> limit 10;

  3. 综合SQL

    hive> create table answer7_3 as
	> select t1.movieid as id, t2.moviename, t2.movietype, avgrate 
	> from answer6_1 t1 join t_movie t2 on t1.movieid=t2.movieid 
	> where years=1997 and instr(lcase(t2.movietype),'comedy') > 0 
	> order by avgrate desc 
	> limit 10;

    在这里插入图片描述

8. 该影评库中各种类型电影中评价最高的5部电影（类型，电影名，平均影评分）

  1. 需要电影类型，所有需要将answer7_1中的type字段进行裂变，将结果保存到answer8_2中

    hive> create table answer8_1 as 
    > select a.id as id, a.name as name, a.years as years, a.rate as rate, tv.type as type 
    > from answer7_1 a 
    > lateral view explode(split(a.type,"\\|")) tv as type;

    在这里插入图片描述

  2. 求TopN，按照type分组，需要添加一列来记录每组的顺序，将结果保存到answer8_2中

    hive> create table answer8_2 as 
    > select id,name,years,rate,type,row_number() 
    > over(distribute by type sort by rate desc ) as num
    > from answer8_1;

	select * from answer8_2 where num <= 5;

    在这里插入图片描述

9. 各年评分最高的电影类型（年份，类型，影评分）

  1. 需要按照电影类型和上映年份进行分组，按照影评分进行排序，将结果保存到answer9_A中

    hive> create table answer9_1 as 
    > select a.years as years, a.type as type, avg(a.rate) as rate 
    > from answer8_1 a 
    > group by a.years,a.type 
    > order by rate desc;

    在这里插入图片描述

  2. 求TopN，按照years分组，需要添加一列来记录每组的顺序，将结果保存到answer9_2中

    hive> create table answer9_2 as 
    > select years,type,rate,row_number() 
    > over (distribute by years sort by rate) as num
    > from answer9_1;

    在这里插入图片描述

  3. 按照num=1作为where过滤条件取出结果数据

    hive> select * from answer9_2 where num=1;

    在这里插入图片描述

10. 每个地区最高评分的电影名（地区，电影名，影评分）

  1. 需要把三张表进行联合查询，取出电影id、电影名称、影评分、地区，将结果保存到answer10_1表中

    hive> create table answer10_1 as
    > select c.movieid, c.moviename, avg(b.rate) as avgrate, a.zipcode
    > from t_user a 
    > join t_rating b on a.userid=b.userid 
    > join t_movie c on b.movieid=c.movieid 
    > group by a.zipcode,c.movieid, c.moviename;

    在这里插入图片描述

  2. 求TopN，按照地区分组，按照平均排序，添加一列num用来记录地区排名，将结果保存到answer10_2表中

    hive> create table answer10_2 as
    > select movieid,moviename,avgrate,zipcode, row_number() 
    > over (distribute by zipcode sort by avgrate) as num     
    > from answer10_1;

    在这里插入图片描述

  3. 按照num=1作为where过滤条件取出结果数据

    hive> select t.* from answer10_2 t where t.num=1;

    在这里插入图片描述

转载至: https://www.cnblogs.com/qingyunzong/p/8727264.html#_label2_1

Jiang锋时刻
关注
  • 2
    点赞
  • 踩
  • 29
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
hive-programming:此示例将让您提取有用的统计数据，例如排名前 10 的平均评分电影、使用 Hive 查询语言对 200 万条记录进行基于流派的过滤
06-11
蜂巢编程 此示例将让您提取有用的统计数据，例如排名前 10 的平均评分电影、使用 Hive 查询语言对 200 万条记录进行基于流派的过滤。
014 大数据之HIVE实战
小哥哥咯的博客
01-17 1665
1、HIVE的计算引擎 hive的引擎mapreduce、tez和spark三者比较 hive sql执行方式对比（tez,mapreduce,spark,storm） TEZ和MapReduce区别 Flink on Hive构建流批一体数仓 MR引擎在HIVE 2中将被弃用，官方推荐使用TEZ或SPARK等引擎，在实际应用中如果使用TEZ或者SPARK计算时出现内存溢出的问题（TEZ和SPARK都是内存式计算），可以考虑使用MR进行运算，虽然慢但是肯定会出结果。 Flink使用HiveCatalog可
大数据--HiveQL语句(实战练习)
qq_51641196的博客
12-05 687
题目:1、 创建员工信息数据文件employess.txt，数据文件中包含内容如下(姓名、年龄、薪资、)：Lilith Hardy,30,6000,50,Finance DepartmentByron Green,36,5000,25,Personnel DepartmentYvette Ward,21,4500,15.5,Arlen Esther,28,8000,20,Finance Depa...
大数据技术之Hadoop学习(七)——Hive数据仓库
weixin_63507910的博客
01-12 2641
Hive数据仓库的认识与操作
【如何把hive表中的数据放入模型中去训练】
limiaoiao的博客
12-27 260
网上的都是使用txt导入模型。如下： Classification and regression - Spark 2.2.0 Documentation al data = spark.read.format("libsvm").load("data/mllib/sample_libsvm_data.txt") // Automatically identify categorical features, and index them. // Set maxCategories so feature
大数据技术之hive学习文档
06-29
【大数据技术之 Hive 学习文档】 Hive 是一个基于 Hadoop 的数据仓库工具，它允许用户使用类似于 SQL 的查询语言（Hive Query Language，HQL）对存储在 HDFS 上的大量结构化数据进行分析。Facebook 开源了 Hive，以...
13——大数据技术之Hive介绍与核心知识点.pdf
08-20
大数据技术之Hive介绍与核心知识点，hive核心知识点和介绍，简介、架构、常用语句、概念等等。。。。。。。
尚硅谷大数据技术之Hive1
08-04
【尚硅谷大数据技术之Hive1】章节主要介绍了Hive的基本概念、优缺点、架构原理以及与传统数据库的比较。 1. **Hive是什么** Hive是由Facebook开源的一个数据仓库工具，专门用来处理海量的结构化日志数据统计。它...
8-大数据技术之Hive
最新发布
12-15
【大数据技术之Hive】 Hive是Facebook开源的一款基于Hadoop的数据仓库工具，它使得用户能够使用类SQL的查询语言（HQL）处理存储在HDFS中的大规模数据。Hive的出现主要是为了解决在Hadoop上进行复杂数据查询的难题，...
大数据技术之Hive.docx
03-03
大数据技术之Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类SQL查询功能。本质是将HQL转化成MapReduce程序。Hive处理的数据存储在HDFS，分析数据底层的实现是MapReduce，执行...
Hive SQL（一）
currify的博客
07-11 1830
数据库是长期存放在计算机内、有组织的、可共享的大量数据的集合。数据库中的数据按照一定的数据模型组织、描述和储存，具有较小的冗余度、较高的数据独立性和易拓展性，并可为各种用户共享。主要分为关系型数据库和非关系型数据库。
01、Hive数据仓库——Hive SQL练习
hujieliang123的博客
02-18 1799
Hive数据仓库——Hive SQL练习
大数据学习——hive的sql练习
weixin_30337157的博客
01-17 231
1新建一个数据库 create database db3; 2创建一个外部表 --外部表建表语句示例： create external table student_ext(Sno int,Sname string,Sex string,Sage int,Sdept string) row format delimited fields terminated by ...
实操用Hive分析大数据事半功倍
qq_29718979的博客
06-07 1802
一般我们分析大数据，也许会想到Spark、Storm，但前提得会JAVA等编程语言，不然拿到数据也无法做分析。而Hive而解决了这个问题，只需要会Sql语言即可做mapreduce的大数据分析任务。今天我们创建测试数据用Hive进行mapreduce的实际分析。 一、先安装好Hive、Mysql环境 1、在昨天hdfs的基础上，安装Hive、Mysql。 2、因Hive依赖于Mysql存...
Hive 史上最全面的大数据学习第九篇（四） Hive Sql 操作 今天的你也要加油啊
热忱的博客
07-19 356
五、Hive SQL 操作 5.1 Select 语句 使用正则表达式指定列 create table logs ( uuid string, userid string , fromUrl string , dateString string, timeString string, ipAddress string, browserName string, pcSystemNameOrmobileBrandName string , .
hive案例——影评
jin6872115的博客
04-09 3285
现有如此三份数据： 1、users.dat 数据格式为： 2::M::56::16::70072 对应字段为：UserID BigInt, Gender String, Age Int, Occupation String, Zipcode String 对应字段中文解释：用户id，性别，年龄，职业，邮政编码 2、movies.dat 数据格式为： 2::Jumanji (1995):...
Hive学习之路 （十二）Hive SQL练习之影评案例
weixin_34400525的博客
04-10 1061
案例说明 现有如此三份数据：1、users.dat 数据格式为： 2::M::56::16::70072， 共有6040条数据对应字段为：UserID BigInt, Gender String, Age Int, Occupation String, Zipcode String对应字段中文解释：用户id，性别，年龄，职业，邮政编码 2、movies.dat 数据格式为： 2::...
Hive案例：影评
weixin_45607513的博客
01-12 719
数据准备 数据下载链接： https://github.com/qianhonglinIT/HiveMovieCaseStudy 在数据库中创建3张表，t_user，t_movie，t_rating create table t_user( userid bigint, sex string, age int, occupation string, zipcode string) row for...
HIVE综合练习-SQL练习之影评案例
Mr_qing的博客
06-03 3476
一、案例说明 现有如此三份数据： 1、users.dat 数据格式为： 2::M::56::16::70072， 共有6040条数据 对应字段为：UserID BigInt, Gender String, Age Int, Occupation String, Zipcode String 对应字段中文解释：用户id，性别，年龄，职业，邮政编码 2、movies.dat 数据格式为： 2::Jumanji (1995)::Adventure|Children’s|Fantasy， 共有3883条数据 对应字
爬取豆瓣电影数据并保存至本地csv文件并将数据导入MySQL数据库
03-27
抱歉，作为AI语言模型，我无法执行爬虫操作和访问本地文件系统。但您可以参考以下步骤来完成该任务： 1. 安装Python的爬虫框架Scrapy和MySQL数据库连接库pymysql： ``` pip install scrapy pip install pymysql ``` 2. 创建一个Scrapy项目： ``` scrapy startproject douban_movie ``` 3. 编写爬虫代码。在douban_movie/spiders目录下创建一个名为douban_spider.py的文件，实现对豆瓣电影top250页面的爬取，并将结果保存至本地csv文件： ```python import scrapy import csv class DoubanSpider(scrapy.Spider): name = 'douban' start_urls = ['https://movie.douban.com/top250'] def parse(self, response): for movie in response.css('.item'): yield { 'title': movie.css('.title::text').get(), 'rating': movie.css('.rating_num::text').get(), 'comment': movie.css('.quote span::text').get() } next_page = response.css('.next a::attr(href)').get() if next_page is not None: yield response.follow(next_page, self.parse) # 保存至本地csv文件 with open('douban_movie.csv', mode='a', newline='', encoding='utf-8') as file: writer = csv.writer(file) for movie in response.css('.item'): writer.writerow([ movie.css('.title::text').get(), movie.css('.rating_num::text').get(), movie.css('.quote span::text').get() ]) ``` 4. 运行爬虫并将结果导入MySQL数据库。在douban_movie目录下创建一个名为mysql_pipeline.py的文件，实现将csv文件中的数据导入MySQL数据库： ```python import csv import pymysql class MysqlPipeline: def __init__(self): self.conn = pymysql.connect( host='localhost', port=3306, user='root', password='password', db='douban_movie', charset='utf8mb4' ) self.cursor = self.conn.cursor() def process_item(self, item, spider): self.cursor.execute( "INSERT INTO movie(title, rating, comment) VALUES (%s, %s, %s)", (item['title'], item['rating'], item['comment']) ) self.conn.commit() return item def close_spider(self, spider): self.cursor.close() self.conn.close() if __name__ == '__main__': with open('douban_movie.csv', mode='r', encoding='utf-8') as file: reader = csv.reader(file) next(reader) # 跳过表头 for row in reader: pipeline = MysqlPipeline() pipeline.process_item({ 'title': row[0], 'rating': row[1], 'comment': row[2] }, None) ``` 5. 运行爬虫并导入数据： ``` scrapy crawl douban python mysql_pipeline.py ``` 注意：在运行mysql_pipeline.py文件之前，需要先创建MySQL数据库和movie表。可以使用以下SQL语句： ``` CREATE DATABASE douban_movie CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE douban_movie; CREATE TABLE movie ( id INT(11) NOT NULL AUTO_INCREMENT, title VARCHAR(255) NOT NULL, rating FLOAT NOT NULL, comment VARCHAR(255), PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; ```

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值