项目5 基于Hive实现电影网站用户影评分析 - 完整实操教程

📚 教程概述

本教程将带你从零开始搭建Hive数据仓库，并通过电影用户影评数据分析项目，掌握Hive的完整操作流程。教程注重实际操作，每个步骤都配有详细的命令和说明。

第一部分：Hive基础知识

1.1 为什么选择Hive？

传统数据库的局限：

❌ 无法满足海量数据存储需求
❌ 难以处理多种类型的数据
❌ 计算和处理能力不足

Hive的优势：

✅ 基于Hadoop，可处理PB级数据
✅ 使用类SQL语言（HQL），学习成本低
✅ 支持多种数据源（HDFS、HBase等）
✅ 良好的扩展性

1.2 Hive vs 传统数据库

对比项	Hive	传统数据库
查询语言	HQL	SQL
数据存储	HDFS	本地文件系统
执行延迟	高（适合批处理）	低（适合实时查询）
数据规模	大（TB/PB级）	小（GB级）
索引支持	有限	完善

第二部分：Hive环境搭建（实操）

2.1 准备工作

环境要求：

Linux系统（CentOS 7推荐）
Hadoop 3.3.6已安装并正常运行
JDK 1.8+
MySQL 8.0+（用于存储元数据）

2.2 安装Hive（直连数据库模式）

Step 1: 下载并上传Hive安装包

bash

# 在Linux系统中创建目录
mkdir -p /opt/apps
cd /opt/apps

# 使用Xftp或scp上传 apache-hive-3.1.3-bin.tar.gz
# 解压到/usr/local目录
tar -zxvf apache-hive-3.1.3-bin.tar.gz -C /usr/local/

Step 2: 解决Jar包冲突

bash

cd /usr/local/apache-hive-3.1.3-bin/lib

# 删除低版本guava
rm -f guava-19.0.jar

# 复制Hadoop的guava包
cp /usr/local/hadoop-3.3.6/share/hadoop/common/lib/guava-27.0-jre.jar .

# 删除冲突的日志包
rm -f log4j-slf4j-impl-2.17.1.jar

Step 3: 配置环境变量

bash

# 编辑profile文件
vim /etc/profile

# 添加以下内容
export HIVE_HOME=/usr/local/apache-hive-3.1.3-bin
export PATH=$PATH:$HIVE_HOME/bin

# 使配置生效
source /etc/profile

Step 4: 安装MySQL

bash

# 检查并卸载MariaDB
rpm -qa | grep mariadb
rpm -e --nodeps mariadb-libs

# 按顺序安装MySQL RPM包
rpm -ivh mysql-community-common-8.0.21-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.21-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.21-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.21-1.el7.x86_64.rpm

Step 5: 配置MySQL

bash

# 编辑MySQL配置文件
vim /etc/my.cnf

# 在[mysqld]下添加
character-set-server=utf8
collation-server=utf8_general_ci

# 启动MySQL
systemctl start mysqld

# 查看初始密码
grep 'temporary password' /var/log/mysqld.log

# 登录MySQL
mysql -u root -p

# 修改密码（密码策略：大小写字母+数字+特殊字符）
ALTER USER 'root'@'localhost' IDENTIFIED BY 'YourPassword@123';

# 设置远程访问权限
CREATE USER 'root'@'%' IDENTIFIED BY 'YourPassword@123';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

# 设置开机启动
systemctl enable mysqld

Step 6: 配置Hive连接MySQL

xml

# 创建hive-site.xml配置文件
cd /usr/local/apache-hive-3.1.3-bin/conf
vim hive-site.xml
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
    <property>
       <name>hive.support.concurrency</name>
       <value>true</value>
    </property>
    <property>
       <name>hive.enforce.bucketing</name>
       <value>true</value>
    </property>
    <property>
       <name>hive.exec.dynamic.partition.mode</name>
       <value>nonstrict</value>
    </property>
    <property>
       <name>hive.txn.manager</name>
       <value>org.apache.hadoop.hive.ql.lockmgr.DbTxnManager</value>
    </property>
    <property>
       <name>hive.compactor.initiator.on</name>
       <value>true</value>
    </property>
    <property>
       <name>hive.compactor.worker.threads</name>
       <value>1</value>
    </property>
    <property>
       <name>hive.in.test</name>
       <value>true</value>
    </property>

      <property>
         <name>hive.metastore.warehouse.dir</name>
         <value>hdfs://master:8020/user/hive/warehouse</value>
      </property>
      <property>
         <name>javax.jdo.option.ConnectionURL</name>
         <value>
         jdbc:mysql://192.168.128.130:3306/hive?createDatabaseIfNotExist=true
         </value>
         <description>MySQL连接协议</description>
      </property>
      <property>
         <name>javax.jdo.option.ConnectionDriverName</name>
         <value>com.mysql.cj.jdbc.Driver</value>
         <description>JDBC连接驱动</description>
      </property>
      <property>
         <name>javax.jdo.option.ConnectionUserName</name>
         <value>root</value>
         <description>用户名</description>
      </property>
      <property>
         <name>javax.jdo.option.ConnectionPassword</name>
         <value>123456</value>
         <description>密码</description>
      </property>
</configuration>

Step 7: 上传MySQL驱动并初始化元数据

bash

# 下载MySQL JDBC驱动
# mysql-connector-java-8.0.21.jar

# 复制到Hive的lib目录
cp mysql-connector-java-8.0.21.jar /usr/local/apache-hive-3.1.3-bin/lib/

# 初始化元数据库
schematool -dbType mysql -initSchema

Step 8: 启动Hive

bash

# 确保Hadoop集群已启动
start-all.sh

# 启动Hive
hive

# 成功进入Hive命令行界面
hive> show databases;

第三部分：Hive数据库和表操作

3.1 数据库操作

创建数据库

sql

-- 创建数据库
CREATE DATABASE IF NOT EXISTS myhive
COMMENT '电影分析数据库'
LOCATION '/user/hive/warehouse/myhive.db';

-- 查看所有数据库
SHOW DATABASES;

-- 查看数据库详情
DESCRIBE DATABASE EXTENDED myhive;

-- 使用数据库
USE myhive;

修改数据库

sql

-- 修改数据库属性
ALTER DATABASE myhive SET DBPROPERTIES('creator'='admin', 'date'='2024-01-01');

-- 修改数据库位置（注意：不会移动已有数据）
ALTER DATABASE myhive SET LOCATION '/user/hive/warehouse/new_myhive.db';

删除数据库

sql

-- 删除空数据库
DROP DATABASE IF EXISTS myhive;

-- 强制删除（包含表的数据库）
DROP DATABASE IF EXISTS myhive CASCADE;

3.2 表操作实战

3.2.1 创建内部表

sql

-- 使用myhive数据库
USE myhive;

-- 创建简单内部表
CREATE TABLE IF NOT EXISTS user_info(
    id INT COMMENT '用户ID',
    name STRING COMMENT '用户姓名'
)
COMMENT '用户信息表'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;

-- 查看表结构
DESCRIBE FORMATTED user_info;

3.2.2 创建外部表

准备数据文件 student_info.txt（制表符分隔）：

2021513501	张晓娟	女	153*****506	2021级信管1班
2021513505	李小鹏	男	156*****501	2021级信管2班
2021513503	张丽	女	186*****556	2021级信管1班

上传到HDFS：

bash

# 创建HDFS目录
hdfs dfs -mkdir -p /stu

# 上传数据文件
hdfs dfs -put student_info.txt /stu/

创建外部表：

sql

CREATE EXTERNAL TABLE IF NOT EXISTS student_external(
    stu_no STRING COMMENT '学号',
    stu_name STRING COMMENT '姓名',
    stu_sex STRING COMMENT '性别',
    telephone STRING COMMENT '电话',
    stu_class STRING COMMENT '班级'
)
COMMENT '学生信息外部表'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE
LOCATION '/stu';

-- 查询数据验证
SELECT * FROM student_external LIMIT 5;

内部表 vs 外部表的区别：

内部表：删除表时，元数据和HDFS上的数据都会被删除
外部表：删除表时，只删除元数据，HDFS上的数据保留

3.2.3 创建分区表

sql

-- 创建单分区表
CREATE TABLE IF NOT EXISTS stu_score(
    sno STRING COMMENT '学号',
    course STRING COMMENT '课程',
    score INT COMMENT '成绩'
)
COMMENT '学生成绩表'
PARTITIONED BY (class_name STRING COMMENT '班级')
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;

-- 添加分区
ALTER TABLE stu_score ADD PARTITION(class_name='07111301');
ALTER TABLE stu_score ADD PARTITION(class_name='07111302');

-- 查看分区
SHOW PARTITIONS stu_score;

-- 删除分区
ALTER TABLE stu_score DROP IF EXISTS PARTITION(class_name='07111302');

创建多级分区表：

sql

CREATE TABLE IF NOT EXISTS orders(
    order_id STRING,
    user_id STRING,
    amount DOUBLE
)
PARTITIONED BY (year STRING, month STRING, day STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t';

-- 添加多级分区
ALTER TABLE orders ADD PARTITION(year='2024', month='01', day='15');

3.2.4 创建分桶表

sql

-- 创建分桶表
CREATE TABLE IF NOT EXISTS student_bucket(
    sno STRING,
    sname STRING,
    age INT
)
COMMENT '学生分桶表'
CLUSTERED BY (sno) INTO 4 BUCKETS
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;

-- 设置分桶属性
SET hive.enforce.bucketing = true;

3.3 修改表操作

sql

-- 重命名表
ALTER TABLE score RENAME TO stu_score;

-- 添加列
ALTER TABLE stu_score ADD COLUMNS (
    credit FLOAT COMMENT '学分',
    gpa FLOAT COMMENT '绩点'
);

-- 修改列
ALTER TABLE stu_score CHANGE COLUMN credit Credits FLOAT COMMENT '学分';

-- 替换所有列（谨慎使用）
ALTER TABLE stu_score REPLACE COLUMNS (
    sno STRING,
    course STRING,
    score INT
);

-- 查看表结构
DESCRIBE EXTENDED stu_score;

第四部分：数据操作实战

4.1 数据装载

准备测试数据

student.txt（制表符分隔）：

2018213201	李小勇	男	20	CS
2018213202	刘良	女	19	IS
2018213203	王芝芝	女	22	MA
2018213204	张大立	男	19	IS
2018213205	刘云山	男	18	MA

course.txt：

1	Python语言程序设计
2	数据库系统原理
3	信息系统分析与设计
4	大数据技术及应用
5	Hadoop开发与应用

sc.txt：

2018213201	1	81
2018213201	2	85
2018213201	3	88
2018213202	2	90
2018213202	3	80

创建表

sql

-- 学生表
CREATE TABLE IF NOT EXISTS student(
    Sno STRING COMMENT '学号',
    Sname STRING COMMENT '姓名',
    Ssex STRING COMMENT '性别',
    Sage INT COMMENT '年龄',
    Sdept STRING COMMENT '专业'
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;

-- 课程表
CREATE TABLE IF NOT EXISTS course(
    Cno INT COMMENT '课程编号',
    Cname STRING COMMENT '课程名称'
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;

-- 成绩表
CREATE TABLE IF NOT EXISTS sc(
    Sno STRING COMMENT '学号',
    Cno INT COMMENT '课程编号',
    Grade INT COMMENT '成绩'
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;

装载数据

方法1：从本地文件系统装载

sql

-- LOCAL关键字表示从本地文件系统读取
LOAD DATA LOCAL INPATH '/opt/stu/student.txt' INTO TABLE student;
LOAD DATA LOCAL INPATH '/opt/stu/course.txt' INTO TABLE course;
LOAD DATA LOCAL INPATH '/opt/stu/sc.txt' INTO TABLE sc;

方法2：从HDFS装载

bash

# 先上传到HDFS
hdfs dfs -put /opt/stu/*.txt /user/data/
-- 不加LOCAL关键字，从HDFS读取（会移动文件）
LOAD DATA INPATH '/user/data/student.txt' INTO TABLE student;

方法3：OVERWRITE覆盖装载

sql

-- 覆盖表中原有数据
LOAD DATA LOCAL INPATH '/opt/stu/student.txt' OVERWRITE INTO TABLE student;

4.2 查询操作

基础查询

sql

-- 查询所有数据
SELECT * FROM student;

-- 查询指定列
SELECT Sno, Sname FROM student;

-- 条件查询
SELECT Sno, Sname FROM student WHERE Ssex='男';

-- 去重查询
SELECT DISTINCT Sdept FROM student;

-- 限制返回行数
SELECT * FROM student LIMIT 5;

聚合查询

sql

-- 统计记录数
SELECT COUNT(*) AS total FROM student;

-- 按专业统计人数
SELECT Sdept, COUNT(*) AS count 
FROM student 
GROUP BY Sdept;

-- 计算平均年龄
SELECT AVG(Sage) AS avg_age FROM student;

-- 查找最高分
SELECT MAX(Grade) AS max_grade FROM sc;

连接查询

sql

-- 内连接：查询学生姓名和课程名称
SELECT s.Sname, c.Cname, sc.Grade
FROM student s
JOIN sc ON s.Sno = sc.Sno
JOIN course c ON sc.Cno = c.Cno;

-- 左连接：显示所有学生（包括未选课的）
SELECT s.Sno, s.Sname, c.Cname
FROM student s
LEFT JOIN sc ON s.Sno = sc.Sno
LEFT JOIN course c ON sc.Cno = c.Cno;

高级查询

sql

-- HAVING子句：查询选修3门以上课程的学生
SELECT Sno, COUNT(*) AS course_count
FROM sc
GROUP BY Sno
HAVING COUNT(*) > 3;

-- ORDER BY全局排序
SELECT * FROM student ORDER BY Sage ASC;

-- SORT BY分区内排序（需要多个reducer）
SET mapred.reduce.tasks=2;
SELECT * FROM student SORT BY Sage ASC;

-- DISTRIBUTE BY + SORT BY
SELECT * FROM student
DISTRIBUTE BY Sdept
SORT BY Sage ASC;

-- CLUSTER BY（等同于DISTRIBUTE BY + SORT BY同一字段）
SELECT * FROM student CLUSTER BY Sdept;

子查询

sql

-- 子查询：查询成绩高于平均分的记录
SELECT Sno, Cno, Grade
FROM sc
WHERE Grade > (SELECT AVG(Grade) FROM sc);

-- IN子查询：查询选修了Python课程的学生
SELECT Sno, Sname
FROM student
WHERE Sno IN (
    SELECT Sno FROM sc WHERE Cno = 1
);

4.3 插入操作

sql

-- 插入单条数据
INSERT INTO TABLE student VALUES
('2018213230', '王小明', '男', 20, 'CS');

-- 插入多条数据
INSERT INTO TABLE student VALUES
('2018213231', '李红', '女', 19, 'IS'),
('2018213232', '张伟', '男', 21, 'MA');

-- 从查询结果插入
INSERT INTO TABLE student
SELECT * FROM student_external WHERE Sdept='CS';

-- 覆盖插入
INSERT OVERWRITE TABLE student
SELECT * FROM student_backup;

-- 多表插入
FROM student
INSERT OVERWRITE TABLE student_cs SELECT * WHERE Sdept='CS'
INSERT OVERWRITE TABLE student_is SELECT * WHERE Sdept='IS';

4.4 更新和删除操作

注意：Hive默认不支持UPDATE和DELETE，需要配置事务表

配置事务支持

bash

# 编辑hive-site.xml
vim /usr/local/apache-hive-3.1.3-bin/conf/hive-site.xml
<!-- 添加以下配置 -->
<property>
    <name>hive.support.concurrency</name>
    <value>true</value>
</property>

<property>
    <name>hive.enforce.bucketing</name>
    <value>true</value>
</property>

<property>
    <name>hive.exec.dynamic.partition.mode</name>
    <value>nonstrict</value>
</property>

<property>
    <name>hive.txn.manager</name>
    <value>org.apache.hadoop.hive.ql.lockmgr.DbTxnManager</value>
</property>

<property>
    <name>hive.compactor.initiator.on</name>
    <value>true</value>
</property>

<property>
    <name>hive.compactor.worker.threads</name>
    <value>1</value>
</property>

创建事务表

sql

-- 创建支持事务的ORC格式表
CREATE TABLE student_orc(
    Sno STRING,
    Sname STRING,
    Ssex STRING,
    Sage INT,
    Sdept STRING
)
CLUSTERED BY (Sno) INTO 2 BUCKETS
STORED AS ORC
TBLPROPERTIES ('transactional'='true');

-- 从原表导入数据
INSERT INTO TABLE student_orc SELECT * FROM student;

执行更新和删除

sql

-- 更新数据
UPDATE student_orc 
SET Sage = 21 
WHERE Sno = '2018213201';

-- 删除数据
DELETE FROM student_orc 
WHERE Sno = '2018213230';

-- 清空表（删除所有数据，保留表结构）
TRUNCATE TABLE student_orc;

第五部分：电影用户影评分析项目实战

5.1 项目背景

分析电影网站的用户影评数据，包括：

用户基本信息（性别、年龄、职业等）
电影信息（ID、类型等）
用户评分记录

5.2 数据准备

数据文件说明

1. ratings.dat（评分数据，分隔符::）

1::1193::5::978300760
1::661::3::978302109
1::914::3::978301968

字段：UserID::MovieID::Rating::Timestamp

2. users.dat（用户数据，分隔符::）

1::F::1::10::48067
2::M::56::16::70072
3::M::25::15::55117

字段：UserID::Gender::Age::Occupation::Zip-code

3. movies.dat（电影数据，分隔符::）

1::Toy Story (1995)::Animation|Children's|Comedy
2::Jumanji (1995)::Adventure|Children's|Fantasy
3::Grumpier Old Men (1995)::Comedy|Romance

字段：MovieID::Title::Genres

5.3 创建数据库和表

sql

-- 创建电影分析数据库
CREATE DATABASE IF NOT EXISTS film_analysis
COMMENT '电影用户影评分析数据库'
LOCATION '/user/hive/warehouse/film_analysis.db';

USE film_analysis;

创建评分表

sql

CREATE TABLE IF NOT EXISTS film_ratings(
    UserID INT COMMENT '用户ID',
    MovieID INT COMMENT '电影ID',
    Rating INT COMMENT '评分',
    ts BIGINT COMMENT '时间戳'
)
COMMENT '用户评分表'
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe'
WITH SERDEPROPERTIES ("field.delim"="::")
STORED AS TEXTFILE;

创建用户表

sql

CREATE TABLE IF NOT EXISTS film_users(
    UserID INT COMMENT '用户ID',
    Gender STRING COMMENT '性别',
    Age INT COMMENT '年龄段',
    Occupation INT COMMENT '职业',
    Zip_code STRING COMMENT '邮政编码'
)
COMMENT '用户信息表'
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe'
WITH SERDEPROPERTIES ("field.delim"="::")
STORED AS TEXTFILE;

创建电影表

sql

CREATE TABLE IF NOT EXISTS film_movies(
    MovieID INT COMMENT '电影ID',
    Title STRING COMMENT '电影标题',
    Genres STRING COMMENT '电影类型'
)
COMMENT '电影信息表'
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe'
WITH SERDEPROPERTIES ("field.delim"="::")
STORED AS TEXTFILE;

5.4 装载数据

bash

# 上传数据文件到HDFS
hdfs dfs -mkdir -p /user/film/data
hdfs dfs -put ratings.dat /user/film/data/
hdfs dfs -put users.dat /user/film/data/
hdfs dfs -put movies.dat /user/film/data/
-- 装载数据
LOAD DATA INPATH '/user/film/data/ratings.dat' INTO TABLE film_ratings;
LOAD DATA INPATH '/user/film/data/users.dat' INTO TABLE film_users;
LOAD DATA INPATH '/user/film/data/movies.dat' INTO TABLE film_movies;

-- 验证数据
SELECT COUNT(*) FROM film_ratings;
SELECT COUNT(*) FROM film_users;
SELECT COUNT(*) FROM film_movies;

SELECT * FROM film_ratings LIMIT 5;

5.5 数据分析任务

任务1：统计评分次数最多的10部电影

sql

-- 创建结果表
CREATE TABLE IF NOT EXISTS top10_movies_by_ratings AS
SELECT 
    r.MovieID,
    m.Title,
    COUNT(*) AS rating_count
FROM film_ratings r
JOIN film_movies m ON r.MovieID = m.MovieID
GROUP BY r.MovieID, m.Title
ORDER BY rating_count DESC
LIMIT 10;

-- 查看结果
SELECT * FROM top10_movies_by_ratings;

任务2：统计不同性别用户评分最高的10部电影

sql

-- 男性用户评分最高的10部电影
CREATE TABLE IF NOT EXISTS top10_movies_male AS
SELECT 
    m.Title,
    AVG(r.Rating) AS avg_rating,
    COUNT(*) AS rating_count
FROM film_ratings r
JOIN film_users u ON r.UserID = u.UserID
JOIN film_movies m ON r.MovieID = m.MovieID
WHERE u.Gender = 'M'
GROUP BY m.MovieID, m.Title
HAVING COUNT(*) >= 50  -- 至少50个评分
ORDER BY avg_rating DESC
LIMIT 10;

-- 女性用户评分最高的10部电影
CREATE TABLE IF NOT EXISTS top10_movies_female AS
SELECT 
    m.Title,
    AVG(r.Rating) AS avg_rating,
    COUNT(*) AS rating_count
FROM film_ratings r
JOIN film_users u ON r.UserID = u.UserID
JOIN film_movies m ON r.MovieID = m.MovieID
WHERE u.Gender = 'F'
GROUP BY m.MovieID, m.Title
HAVING COUNT(*) >= 50
ORDER BY avg_rating DESC
LIMIT 10;

-- 查看结果
SELECT * FROM top10_movies_male;
SELECT * FROM top10_movies_female;

任务3：计算指定电影各年龄段用户的平均评分

sql

-- 以电影ID为1（Toy Story）为例
CREATE TABLE IF NOT EXISTS movie_ratings_by_age AS
SELECT 
    m.Title,
    u.Age,
    AVG(r.Rating) AS avg_rating,
    COUNT(*) AS rating_count
FROM film_ratings r
JOIN film_users u ON r.UserID = u.UserID
JOIN film_movies m ON r.MovieID = m.MovieID
WHERE r.MovieID = 1
GROUP BY m.Title, u.Age
ORDER BY u.Age;

-- 查看结果
SELECT * FROM movie_ratings_by_age;

任务4：统计各类型电影中评分最高的5部

sql

-- 先将电影类型拆分（处理多类型电影）
-- 使用lateral view explode拆分类型
CREATE TABLE IF NOT EXISTS top_movies_by_genre AS
SELECT 
    genre,
    Title,
    avg_rating,
    rating_count,
    row_num
FROM (
    SELECT 
        genre,
        m.Title,
        AVG(r.Rating) AS avg_rating,
        COUNT(*) AS rating_count,
        ROW_NUMBER() OVER (PARTITION BY genre ORDER BY AVG(r.Rating) DESC) AS row_num
    FROM film_ratings r
    JOIN film_movies m ON r.MovieID = m.MovieID
    LATERAL VIEW explode(split(m.Genres, '\\|')) genreTable AS genre
    GROUP BY genre, m.Title, m.MovieID
    HAVING COUNT(*) >= 30
) t
WHERE row_num <= 5
ORDER BY genre, row_num;

-- 查看结果
SELECT * FROM top_movies_by_genre;

5.6 高级分析

分析5：用户活跃度分析

sql

-- 统计用户评分次数分布
CREATE TABLE IF NOT EXISTS user_activity_analysis AS
SELECT 
    rating_range,
    COUNT(*) AS user_count
FROM (
    SELECT 
        UserID,
        CASE 
            WHEN rating_count <= 20 THEN '1-20'
            WHEN rating_count <= 50 THEN '21-50'
            WHEN rating_count <= 100 THEN '51-100'
            WHEN rating_count <= 200 THEN '101-200'
            ELSE '200+'
        END AS rating_range
    FROM (
        SELECT UserID, COUNT(*) AS rating_count
        FROM film_ratings
        GROUP BY UserID
    ) t1
) t2
GROUP BY rating_range
ORDER BY rating_range;

SELECT * FROM user_activity_analysis;

分析6：电影类型受欢迎程度

sql

-- 统计各类型电影的平均评分和数量
CREATE TABLE IF NOT EXISTS genre_popularity AS
SELECT 
    genre,
    COUNT(DISTINCT m.MovieID) AS movie_count,
    AVG(r.Rating) AS avg_rating,
    COUNT(*) AS total_ratings
FROM film_ratings r
JOIN film_movies m ON r.MovieID = m.MovieID
LATERAL VIEW explode(split(m.Genres, '\\|')) genreTable AS genre
GROUP BY genre
ORDER BY avg_rating DESC;

SELECT * FROM genre_popularity;

第六部分：性能优化

6.1 数据存储格式优化

使用ORC格式提升查询性能

sql

-- 创建ORC格式的评分表
CREATE TABLE film_ratings_orc(
    UserID INT,
    MovieID INT,
    Rating INT,
    ts BIGINT
)
STORED AS ORC
TBLPROPERTIES (
    'orc.compress'='SNAPPY',
    'orc.create.index'='true'
);

-- 从原表导入数据
INSERT INTO TABLE film_ratings_orc 
SELECT * FROM film_ratings;

-- 比较存储大小
-- TextFile: 约 24MB
-- ORC: 约 5MB (压缩后)

使用Parquet格式

sql

-- 创建Parquet格式表
CREATE TABLE film_ratings_parquet(
    UserID INT,
    MovieID INT,
    Rating INT,
    ts BIGINT
)
STORED AS PARQUET;

INSERT INTO TABLE film_ratings_parquet 
SELECT * FROM film_ratings;

存储格式对比：

格式	压缩比	查询速度	适用场景
TextFile	无	慢	原始数据
ORC	高	快	复杂查询、列式访问
Parquet	高	快	列式存储、跨平台
Avro	中	中	模式演进

6.2 分区优化

sql

-- 创建按日期分区的评分表
CREATE TABLE film_ratings_partitioned(
    UserID INT,
    MovieID INT,
    Rating INT,
    ts BIGINT
)
PARTITIONED BY (year STRING, month STRING)
STORED AS ORC;

-- 开启动态分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
SET hive.exec.max.dynamic.partitions=1000;
SET hive.exec.max.dynamic.partitions.pernode=100;

-- 插入数据并自动创建分区
INSERT INTO TABLE film_ratings_partitioned PARTITION(year, month)
SELECT 
    UserID,
    MovieID,
    Rating,
    ts,
    year(from_unixtime(ts)) AS year,
    lpad(month(from_unixtime(ts)), 2, '0') AS month
FROM film_ratings;

-- 查询指定分区（避免全表扫描）
SELECT * FROM film_ratings_partitioned
WHERE year='2000' AND month='03'
LIMIT 10;

6.3 分桶优化

sql

-- 创建分桶表
CREATE TABLE film_ratings_bucketed(
    UserID INT,
    MovieID INT,
    Rating INT,
    ts BIGINT
)
CLUSTERED BY (MovieID) INTO 32 BUCKETS
STORED AS ORC;

-- 开启分桶
SET hive.enforce.bucketing=true;

-- 插入数据
INSERT INTO TABLE film_ratings_bucketed
SELECT * FROM film_ratings;

-- 使用分桶表进行JOIN操作（性能更好）
SELECT /*+ MAPJOIN(m) */
    r.MovieID,
    m.Title,
    COUNT(*) AS rating_count
FROM film_ratings_bucketed r
JOIN film_movies m ON r.MovieID = m.MovieID
GROUP BY r.MovieID, m.Title;

6.4 查询优化技巧

使用EXPLAIN查看执行计划

sql

-- 查看查询执行计划
EXPLAIN 
SELECT m.Title, AVG(r.Rating) AS avg_rating
FROM film_ratings r
JOIN film_movies m ON r.MovieID = m.MovieID
GROUP BY m.Title;

-- 查看详细执行计划
EXPLAIN EXTENDED
SELECT m.Title, AVG(r.Rating) AS avg_rating
FROM film_ratings r
JOIN film_movies m ON r.MovieID = m.MovieID
GROUP BY m.Title;

MapJoin优化小表关联

sql

-- 设置MapJoin阈值（25MB以下的表会自动使用MapJoin）
SET hive.auto.convert.join=true;
SET hive.mapjoin.smalltable.filesize=25000000;

-- 手动指定MapJoin
SELECT /*+ MAPJOIN(m) */
    r.UserID,
    m.Title,
    r.Rating
FROM film_ratings r
JOIN film_movies m ON r.MovieID = m.MovieID
WHERE r.Rating >= 4;

使用CTE简化复杂查询

sql

-- 使用WITH子句（Common Table Expression）
WITH high_rated_movies AS (
    SELECT 
        MovieID,
        AVG(Rating) AS avg_rating,
        COUNT(*) AS rating_count
    FROM film_ratings
    GROUP BY MovieID
    HAVING AVG(Rating) >= 4 AND COUNT(*) >= 50
),
active_users AS (
    SELECT 
        UserID,
        COUNT(*) AS rating_count
    FROM film_ratings
    GROUP BY UserID
    HAVING COUNT(*) >= 100
)
SELECT 
    m.Title,
    h.avg_rating,
    h.rating_count,
    COUNT(DISTINCT r.UserID) AS active_user_count
FROM high_rated_movies h
JOIN film_movies m ON h.MovieID = m.MovieID
JOIN film_ratings r ON h.MovieID = r.MovieID
JOIN active_users a ON r.UserID = a.UserID
GROUP BY m.Title, h.avg_rating, h.rating_count
ORDER BY h.avg_rating DESC
LIMIT 20;

6.5 其他性能优化配置

sql

-- 1. 开启本地模式（适合小数据量）
SET hive.exec.mode.local.auto=true;
SET hive.exec.mode.local.auto.inputbytes.max=50000000;
SET hive.exec.mode.local.auto.input.files.max=5;

-- 2. 并行执行
SET hive.exec.parallel=true;
SET hive.exec.parallel.thread.number=8;

-- 3. JVM重用
SET mapreduce.job.jvm.numtasks=10;

-- 4. 合并小文件
SET hive.merge.mapfiles=true;
SET hive.merge.mapredfiles=true;
SET hive.merge.size.per.task=256000000;
SET hive.merge.smallfiles.avgsize=16000000;

-- 5. 矢量化查询执行
SET hive.vectorized.execution.enabled=true;
SET hive.vectorized.execution.reduce.enabled=true;

-- 6. 启用谓词下推
SET hive.optimize.ppd=true;

-- 7. 启用列裁剪
SET hive.optimize.cp=true;

第七部分：常见问题与解决方案

7.1 安装配置问题

问题1：Hive无法连接MySQL

错误信息：

Exception in thread "main" java.lang.RuntimeException: 
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: 
Communications link failure

解决方案：

bash

# 1. 检查MySQL是否启动
systemctl status mysqld

# 2. 检查MySQL远程访问权限
mysql -u root -p
mysql> SELECT host, user FROM mysql.user;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES;

# 3. 检查防火墙
systemctl status firewalld
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload

# 4. 验证MySQL驱动是否正确
ls /usr/local/apache-hive-3.1.3-bin/lib/mysql-connector-java*.jar

问题2：Guava版本冲突

错误信息：

java.lang.NoSuchMethodError: com.google.common.base.Preconditions.checkArgument

解决方案：

bash

cd /usr/local/apache-hive-3.1.3-bin/lib
rm -f guava-19.0.jar
cp /usr/local/hadoop-3.3.6/share/hadoop/common/lib/guava-27.0-jre.jar .

问题3：日志冲突

错误信息：

SLF4J: Class path contains multiple SLF4J bindings

解决方案：

bash

cd /usr/local/apache-hive-3.1.3-bin/lib
rm -f log4j-slf4j-impl-2.17.1.jar

7.2 数据操作问题

问题4：中文数据乱码

解决方案：

sql

-- 在建表时指定字符集
CREATE TABLE test_chinese(
    id INT,
    name STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;

-- 确保MySQL使用UTF-8
ALTER DATABASE hive CHARACTER SET utf8;
# 编辑my.cnf
vim /etc/my.cnf
# 添加
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

问题5：分隔符包含特殊字符

数据文件包含::分隔符：

sql

-- 使用MultiDelimitSerDe
CREATE TABLE test_multi_delim(
    field1 STRING,
    field2 STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe'
WITH SERDEPROPERTIES ("field.delim"="::");

问题6：无法删除或更新数据

错误信息：

FAILED: SemanticException [Error 10294]: 
Attempt to do update or delete using transaction manager 
that does not support these operations.

解决方案： 参考第四部分4.4节，配置事务表支持。

7.3 性能问题

问题7：查询速度慢

诊断步骤：

sql

-- 1. 查看执行计划
EXPLAIN EXTENDED SELECT ...;

-- 2. 检查是否使用了分区
SHOW PARTITIONS table_name;

-- 3. 查看表统计信息
ANALYZE TABLE table_name COMPUTE STATISTICS;
DESCRIBE FORMATTED table_name;

优化建议：

使用分区表减少数据扫描
使用ORC/Parquet格式
小表使用MapJoin
合理设置Reducer数量

sql

-- 设置Reducer数量
SET mapreduce.job.reduces=10;

-- 或根据数据量自动设置
SET hive.exec.reducers.bytes.per.reducer=256000000;

问题8：内存溢出

错误信息：

Error: Java heap space

解决方案：

bash

# 编辑hive-env.sh
vim /usr/local/apache-hive-3.1.3-bin/conf/hive-env.sh

# 添加
export HADOOP_HEAPSIZE=2048
export HADOOP_CLIENT_OPTS="$HADOOP_CLIENT_OPTS -Xmx2048m"
-- 在Hive中设置
SET mapreduce.map.memory.mb=2048;
SET mapreduce.reduce.memory.mb=4096;
SET mapreduce.map.java.opts=-Xmx1638m;
SET mapreduce.reduce.java.opts=-Xmx3276m;

第八部分：高级主题

8.1 自定义函数（UDF）

创建Java UDF

编写UDF类：

java

package com.example.hive.udf;

import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;

public class ToUpperCaseUDF extends UDF {
    public Text evaluate(Text input) {
        if (input == null) {
            return null;
        }
        return new Text(input.toString().toUpperCase());
    }
}

打包并上传：

bash

# 编译打包
mvn clean package

# 上传jar到HDFS
hdfs dfs -put my-hive-udf.jar /user/hive/lib/

在Hive中注册使用：

sql

-- 添加jar包
ADD JAR /user/hive/lib/my-hive-udf.jar;

-- 创建临时函数
CREATE TEMPORARY FUNCTION to_upper 
AS 'com.example.hive.udf.ToUpperCaseUDF';

-- 使用函数
SELECT to_upper(Title) FROM film_movies LIMIT 10;

-- 创建永久函数
CREATE FUNCTION to_upper 
AS 'com.example.hive.udf.ToUpperCaseUDF' 
USING JAR 'hdfs:///user/hive/lib/my-hive-udf.jar';

8.2 Hive与其他工具集成

与Spark集成

bash

# 配置Spark使用Hive元数据
vim $SPARK_HOME/conf/spark-defaults.conf
# 添加
spark.sql.warehouse.dir=/user/hive/warehouse
spark.sql.catalogImplementation=hive
// Spark代码访问Hive表
val spark = SparkSession.builder()
    .appName("Hive Integration")
    .enableHiveSupport()
    .getOrCreate()

// 查询Hive表
val df = spark.sql("SELECT * FROM film_analysis.film_ratings LIMIT 10")
df.show()

与HBase集成

sql

-- 创建Hive外部表关联HBase
CREATE EXTERNAL TABLE hive_hbase_table(
    key STRING,
    value STRING
)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES (
    "hbase.columns.mapping" = ":key,cf:value"
)
TBLPROPERTIES (
    "hbase.table.name" = "hbase_table"
);

8.3 数据导出

导出到本地文件

sql

-- 导出为CSV
INSERT OVERWRITE LOCAL DIRECTORY '/opt/export/top_movies'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
SELECT * FROM top10_movies_by_ratings;

导出到HDFS

sql

-- 导出到HDFS
INSERT OVERWRITE DIRECTORY '/user/hive/export/movies'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
SELECT * FROM film_movies;

使用Sqoop导出到MySQL

bash

# 将Hive表导出到MySQL
sqoop export \
    --connect jdbc:mysql://192.168.128.130:3306/movie_db \
    --username root \
    --password YourPassword@123 \
    --table movies_export \
    --export-dir /user/hive/warehouse/film_analysis.db/film_movies \
    --input-fields-terminated-by '\001'

第九部分：实战练习题

练习1：基础查询

sql

-- 1. 查询评分为5分的所有记录
SELECT * FROM film_ratings WHERE Rating = 5;

-- 2. 统计每个用户的平均评分
SELECT UserID, AVG(Rating) AS avg_rating
FROM film_ratings
GROUP BY UserID
ORDER BY avg_rating DESC
LIMIT 10;

-- 3. 查询评分数量前20的电影
SELECT MovieID, COUNT(*) AS count
FROM film_ratings
GROUP BY MovieID
ORDER BY count DESC
LIMIT 20;

练习2：多表关联

sql

-- 4. 查询女性用户最喜欢的电影类型
SELECT 
    genre,
    COUNT(*) AS rating_count,
    AVG(r.Rating) AS avg_rating
FROM film_ratings r
JOIN film_users u ON r.UserID = u.UserID
JOIN film_movies m ON r.MovieID = m.MovieID
LATERAL VIEW explode(split(m.Genres, '\\|')) t AS genre
WHERE u.Gender = 'F'
GROUP BY genre
ORDER BY rating_count DESC;

-- 5. 找出评分差异最大的电影（男女用户评分差异）
WITH male_ratings AS (
    SELECT 
        r.MovieID,
        AVG(r.Rating) AS male_avg
    FROM film_ratings r
    JOIN film_users u ON r.UserID = u.UserID
    WHERE u.Gender = 'M'
    GROUP BY r.MovieID
),
female_ratings AS (
    SELECT 
        r.MovieID,
        AVG(r.Rating) AS female_avg
    FROM film_ratings r
    JOIN film_users u ON r.UserID = u.UserID
    WHERE u.Gender = 'F'
    GROUP BY r.MovieID
)
SELECT 
    m.Title,
    ma.male_avg,
    fa.female_avg,
    ABS(ma.male_avg - fa.female_avg) AS rating_diff
FROM male_ratings ma
JOIN female_ratings fa ON ma.MovieID = fa.MovieID
JOIN film_movies m ON ma.MovieID = m.MovieID
ORDER BY rating_diff DESC
LIMIT 20;

练习3：时间序列分析

sql

-- 6. 分析不同时间段的评分趋势
SELECT 
    year(from_unixtime(ts)) AS year,
    month(from_unixtime(ts)) AS month,
    COUNT(*) AS rating_count,
    AVG(Rating) AS avg_rating
FROM film_ratings
GROUP BY year(from_unixtime(ts)), month(from_unixtime(ts))
ORDER BY year, month;

-- 7. 找出在特定月份最受欢迎的电影
SELECT 
    month(from_unixtime(r.ts)) AS month,
    m.Title,
    COUNT(*) AS rating_count,
    AVG(r.Rating) AS avg_rating
FROM film_ratings r
JOIN film_movies m ON r.MovieID = m.MovieID
GROUP BY month(from_unixtime(r.ts)), m.MovieID, m.Title
ORDER BY month, rating_count DESC;

练习4：综合分析

sql

-- 8. 用户画像分析：找出不同职业用户的偏好类型
CREATE TABLE user_genre_preference AS
SELECT 
    u.Occupation,
    genre,
    COUNT(*) AS rating_count,
    AVG(r.Rating) AS avg_rating
FROM film_ratings r
JOIN film_users u ON r.UserID = u.UserID
JOIN film_movies m ON r.MovieID = m.MovieID
LATERAL VIEW explode(split(m.Genres, '\\|')) t AS genre
GROUP BY u.Occupation, genre
ORDER BY u.Occupation, rating_count DESC;

-- 查看结果
SELECT * FROM user_genre_preference 
WHERE Occupation = 0 
ORDER BY rating_count DESC 
LIMIT 10;

第十部分：项目总结与最佳实践

10.1 Hive使用最佳实践

1. 表设计原则

✅ 优先使用分区表：按日期、地区等常用查询条件分区
✅ 选择合适的存储格式：大数据量用ORC/Parquet
✅ 合理使用分桶：对JOIN操作频繁的字段分桶
✅ 外部表用于原始数据：保护源数据不被误删

2. 查询优化原则

✅ 分区裁剪：WHERE条件中包含分区字段
✅ 列裁剪：只SELECT需要的列
✅ MapJoin小表：小表（<25MB）自动使用MapJoin
✅ 合理使用缓存：频繁查询的中间结果存为表

3. 数据管理原则

✅ 定期清理临时表
✅ 合并小文件：避免产生大量小文件
✅ 压缩数据：使用SNAPPY或ZLIB压缩
✅ 数据质量检查：导入后验证数据完整性

10.2 常用命令速查

sql

-- 数据库操作
SHOW DATABASES;
CREATE DATABASE db_name;
USE db_name;
DROP DATABASE db_name CASCADE;

-- 表操作
SHOW TABLES;
DESCRIBE FORMATTED table_name;
SHOW CREATE TABLE table_name;
SHOW PARTITIONS table_name;
DROP TABLE table_name;

-- 数据操作
LOAD DATA LOCAL INPATH 'file' INTO TABLE table_name;
INSERT INTO TABLE table_name SELECT ...;
SELECT * FROM table_name LIMIT 10;

-- 性能优化
EXPLAIN SELECT ...;
ANALYZE TABLE table_name COMPUTE STATISTICS;
SET hive.exec.parallel=true;

-- 查看配置
SET -v;  -- 查看所有配置
SET property_name;  -- 查看特定配置

10.3 学习路径建议

初级阶段（1-2周）：

掌握Hive安装配置
熟悉HQL基本语法
完成简单的数据导入和查询

中级阶段（2-4周）：

掌握分区表、分桶表的使用
学习JOIN、子查询等复杂查询
了解不同存储格式的特点

高级阶段（1-2月）：

性能调优和问题排查
自定义函数开发
与其他大数据工具集成

10.4 推荐资源

官方文档：

Apache Hive官网：https://hive.apache.org
Hive Language Manual：https://cwiki.apache.org/confluence/display/Hive/LanguageManual

学习资源：

Hive编程指南（书籍）
Hadoop权威指南（书籍）
各大数据社区博客

附录：完整项目代码

电影分析项目完整SQL脚本

sql

-- ============================================
-- 电影用户影评分析完整脚本
-- ============================================

-- 1. 创建数据库
CREATE DATABASE IF NOT EXISTS film_analysis
COMMENT '电影用户影评分析数据库';

USE film_analysis;

-- 2. 创建表
CREATE TABLE IF NOT EXISTS film_ratings(
    UserID INT,
    MovieID INT,
    Rating INT,
    ts BIGINT
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe'
WITH SERDEPROPERTIES ("field.delim"="::")
STORED AS TEXTFILE;

CREATE TABLE IF NOT EXISTS film_users(
    UserID INT,
    Gender STRING,
    Age INT,
    Occupation INT,
    Zip_code STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe'
WITH SERDEPROPERTIES ("field.delim"="::")
STORED AS TEXTFILE;

CREATE TABLE IF NOT EXISTS film_movies(
    MovieID INT,
    Title STRING,
    Genres STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe'
WITH SERDEPROPERTIES ("field.delim"="::")
STORED AS TEXTFILE;

-- 3. 装载数据（假设数据已上传到HDFS）
LOAD DATA INPATH '/user/film/data/ratings.dat' INTO TABLE film_ratings;
LOAD DATA INPATH '/user/film/data/users.dat' INTO TABLE film_users;
LOAD DATA INPATH '/user/film/data/movies.dat' INTO TABLE film_movies;

-- 4. 数据验证
SELECT COUNT(*) AS ratings_count FROM film_ratings;
SELECT COUNT(*) AS users_count FROM film_users;
SELECT COUNT(*) AS movies_count FROM film_movies;

-- 5. 分析任务
-- 任务1：评分次数最多的10部电影
CREATE TABLE top10_movies_by_ratings AS
SELECT 
    r.MovieID,
    m.Title,
    COUNT(*) AS rating_count
FROM film_ratings r
JOIN film_movies m ON r.MovieID = m.MovieID
GROUP BY r.MovieID, m.Title
ORDER BY rating_count DESC
LIMIT 10;

-- 任务2：不同性别用户评分最高的10部电影
CREATE TABLE top10_movies_male AS
SELECT 
    m.Title,
    AVG(r.Rating) AS avg_rating,
    COUNT(*) AS rating_count
FROM film_ratings r
JOIN film_users u ON r.UserID = u.UserID
JOIN film_movies m ON r.MovieID = m.MovieID
WHERE u.Gender = 'M'
GROUP BY m.MovieID, m.Title
HAVING COUNT(*) >= 50
ORDER BY avg_rating DESC
LIMIT 10;

CREATE TABLE top10_movies_female AS
SELECT 
    m.Title,
    AVG(r.Rating) AS avg_rating,
    COUNT(*) AS rating_count
FROM film_ratings r
JOIN film_users u ON r.UserID = u.UserID
JOIN film_movies m ON r.MovieID = m.MovieID
WHERE u.Gender = 'F'
GROUP BY m.MovieID, m.Title
HAVING COUNT(*) >= 50
ORDER BY avg_rating DESC
LIMIT 10;

-- 任务3：指定电影各年龄段的平均评分
CREATE TABLE movie_ratings_by_age AS
SELECT 
    m.Title,
    u.Age,
    AVG(r.Rating) AS avg_rating,
    COUNT(*) AS rating_count
FROM film_ratings r
JOIN film_users u ON r.UserID = u.UserID
JOIN film_movies m ON r.MovieID = m.MovieID
WHERE r.MovieID = 1
GROUP BY m.Title, u.Age
ORDER BY u.Age;

-- 任务4：各类型电影评分最高的5部
CREATE TABLE top_movies_by_genre AS
SELECT 
    genre,
    Title,
    avg_rating,
    rating_count,
    row_num
FROM (
    SELECT 
        genre,
        m.Title,
        AVG(r.Rating) AS avg_rating,
        COUNT(*) AS rating_count,
        ROW_NUMBER() OVER (PARTITION BY genre ORDER BY AVG(r.Rating) DESC) AS row_num
    FROM film_ratings r
    JOIN film_movies m ON r.MovieID = m.MovieID
    LATERAL VIEW explode(split(m.Genres, '\\|')) genreTable AS genre
    GROUP BY genre, m.Title, m.MovieID
    HAVING COUNT(*) >= 30
) t
WHERE row_num <= 5
ORDER BY genre, row_num;

-- 6. 查看结果
SELECT * FROM top10_movies_by_ratings;
SELECT * FROM top10_movies_male;
SELECT * FROM top10_movies_female;
SELECT * FROM movie_ratings_by_age;
SELECT * FROM top_movies_by_genre;

结语

通过本教程，你已经完整学习了：

✅ Hive的基础概念和架构
✅ Hive的安装配置（内嵌、直连、远程三种模式）
✅ 数据库和表的创建与管理
✅ 数据的装载、查询、插入和删除
✅ 电影用户影评分析实战项目
✅ 性能优化技巧
✅ 常见问题解决方案

接下来的学习方向：

深入学习Hive性能调优
学习Hive与Spark、Flink等工具的集成
探索实时数据仓库技术（如Hudi、Iceberg）
参与实际的大数据项目

记住： 实践是最好的老师，多动手操作，多解决实际问题，你会越来越熟练！

祝学习顺利！🎉

项目5 基于Hive实现电影网站用户影评分析 - 完整实操教程 ​

📚 教程概述 ​

第一部分：Hive基础知识 ​

1.1 为什么选择Hive？ ​

1.2 Hive vs 传统数据库 ​

第二部分：Hive环境搭建（实操） ​

2.1 准备工作 ​

2.2 安装Hive（直连数据库模式） ​

Step 1: 下载并上传Hive安装包 ​

Step 2: 解决Jar包冲突 ​

Step 3: 配置环境变量 ​

Step 4: 安装MySQL ​

Step 5: 配置MySQL ​

Step 6: 配置Hive连接MySQL ​

Step 7: 上传MySQL驱动并初始化元数据 ​

Step 8: 启动Hive ​

第三部分：Hive数据库和表操作 ​

3.1 数据库操作 ​

创建数据库 ​

修改数据库 ​

删除数据库 ​

3.2 表操作实战 ​

3.2.1 创建内部表 ​

3.2.2 创建外部表 ​

3.2.3 创建分区表 ​

3.2.4 创建分桶表 ​

3.3 修改表操作 ​

第四部分：数据操作实战 ​

4.1 数据装载 ​

准备测试数据 ​

创建表 ​

装载数据 ​

4.2 查询操作 ​

基础查询 ​

聚合查询 ​

连接查询 ​

高级查询 ​

子查询 ​

4.3 插入操作 ​

4.4 更新和删除操作 ​

配置事务支持 ​

创建事务表 ​

执行更新和删除 ​

第五部分：电影用户影评分析项目实战 ​

5.1 项目背景 ​

5.2 数据准备 ​

数据文件说明 ​

5.3 创建数据库和表 ​

创建评分表 ​

创建用户表 ​

创建电影表 ​

5.4 装载数据 ​

5.5 数据分析任务 ​

任务1：统计评分次数最多的10部电影 ​

任务2：统计不同性别用户评分最高的10部电影 ​

任务3：计算指定电影各年龄段用户的平均评分 ​

任务4：统计各类型电影中评分最高的5部 ​

5.6 高级分析 ​

分析5：用户活跃度分析 ​

分析6：电影类型受欢迎程度 ​

第六部分：性能优化 ​

6.1 数据存储格式优化 ​

使用ORC格式提升查询性能 ​

使用Parquet格式 ​

6.2 分区优化 ​

6.3 分桶优化 ​

6.4 查询优化技巧 ​

使用EXPLAIN查看执行计划 ​

MapJoin优化小表关联 ​

使用CTE简化复杂查询 ​

6.5 其他性能优化配置 ​

第七部分：常见问题与解决方案 ​

7.1 安装配置问题 ​

问题1：Hive无法连接MySQL ​

问题2：Guava版本冲突 ​

问题3：日志冲突 ​

7.2 数据操作问题 ​

问题4：中文数据乱码 ​

问题5：分隔符包含特殊字符 ​

问题6：无法删除或更新数据 ​

项目5 基于Hive实现电影网站用户影评分析 - 完整实操教程

📚 教程概述

第一部分：Hive基础知识

1.1 为什么选择Hive？

1.2 Hive vs 传统数据库

第二部分：Hive环境搭建（实操）

2.1 准备工作

2.2 安装Hive（直连数据库模式）

Step 1: 下载并上传Hive安装包

Step 2: 解决Jar包冲突

Step 3: 配置环境变量

Step 4: 安装MySQL

Step 5: 配置MySQL

Step 6: 配置Hive连接MySQL

Step 7: 上传MySQL驱动并初始化元数据

Step 8: 启动Hive

第三部分：Hive数据库和表操作

3.1 数据库操作

创建数据库

修改数据库

删除数据库

3.2 表操作实战

3.2.1 创建内部表

3.2.2 创建外部表

3.2.3 创建分区表

3.2.4 创建分桶表

3.3 修改表操作

第四部分：数据操作实战

4.1 数据装载

准备测试数据

创建表

装载数据

4.2 查询操作

基础查询

聚合查询

连接查询

高级查询

子查询

4.3 插入操作

4.4 更新和删除操作

配置事务支持

创建事务表

执行更新和删除

第五部分：电影用户影评分析项目实战

5.1 项目背景

5.2 数据准备

数据文件说明

5.3 创建数据库和表

创建评分表

创建用户表

创建电影表

5.4 装载数据

5.5 数据分析任务

任务1：统计评分次数最多的10部电影

任务2：统计不同性别用户评分最高的10部电影

任务3：计算指定电影各年龄段用户的平均评分

任务4：统计各类型电影中评分最高的5部

5.6 高级分析

分析5：用户活跃度分析

分析6：电影类型受欢迎程度

第六部分：性能优化

6.1 数据存储格式优化

使用ORC格式提升查询性能

使用Parquet格式

6.2 分区优化

6.3 分桶优化

6.4 查询优化技巧

使用EXPLAIN查看执行计划

MapJoin优化小表关联

使用CTE简化复杂查询

6.5 其他性能优化配置

第七部分：常见问题与解决方案

7.1 安装配置问题

问题1：Hive无法连接MySQL

问题2：Guava版本冲突

问题3：日志冲突

7.2 数据操作问题

问题4：中文数据乱码

问题5：分隔符包含特殊字符

问题6：无法删除或更新数据