第7章 Hive实战

CREATE TABLE t_complex(
    id      INT,
    hobbies ARRAY<STRING>,          -- 数组
    scores  MAP<STRING, INT>,       -- 键值对
    info    STRUCT<name:STRING, age:INT>
) ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '|'
MAP KEYS TERMINATED BY ':'
STORED AS TEXTFILE;

# 1) 解压并配置环境变量
tar -zxvf apache-hive-4.1.0-bin.tar.gz -C /export/servers/
vi ~/.bashrc
export HIVE_HOME=/export/servers/hive-4.1.0
export PATH=$HIVE_HOME/bin:$PATH

# 2) 修改 hive-site.xml：指定 MySQL 元数据库
<property><name>javax.jdo.option.ConnectionURL</name>
  <value>jdbc:mysql://hadoop1:3306/hive?createDatabaseIfNotExist=true</value></property>
<property><name>javax.jdo.option.ConnectionDriverName</name>
  <value>com.mysql.cj.jdbc.Driver</value></property>
<property><name>hive.metastore.warehouse.dir</name>
  <value>/user/hive/warehouse</value></property>

# 3) 放 MySQL 驱动并初始化元数据
cp mysql-connector-java-8.0.30.jar $HIVE_HOME/lib/
schematool -initSchema -dbType mysql

# 4) 验证
hive -e "show databases;"

CREATE TABLE IF NOT EXISTS student_info(
    stu_id   STRING COMMENT '学号',
    stu_name STRING COMMENT '姓名',
    sex      STRING COMMENT '性别'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS ORC;

-- 内部表 vs 外部表
CREATE EXTERNAL TABLE ext_student(...) LOCATION '/data/student';

-- 分区表
CREATE TABLE score_part(stu_id STRING, score INT)
PARTITIONED BY (course_id STRING);

-- 常用维护
DESC FORMATTED student_info;
SHOW PARTITIONS score_part;
ALTER TABLE student_info ADD COLUMNS (class_id STRING);
TRUNCATE TABLE student_info;

-- 基础查询
SELECT stu_name, sex FROM student_info WHERE sex = '男';

-- 分组统计
SELECT course_id, count(*) AS cnt, round(avg(score), 2) AS avg_score
FROM score_info GROUP BY course_id HAVING avg(score) > 60;

-- 多表连接
SELECT s.stu_name, c.course_name, sc.score
FROM score_info sc
JOIN student_info s ON sc.stu_id = s.stu_id
JOIN course_info  c ON sc.course_id = c.course_id;

-- 窗口函数：取每门课前 3 名
SELECT * FROM (
  SELECT course_id, stu_id, score,
         rank() OVER (PARTITION BY course_id ORDER BY score DESC) rk
  FROM score_info
) t WHERE rk <= 3;

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

-- 注册与使用
ADD JAR /user/hadoop/jars/myudf.jar;
CREATE TEMPORARY FUNCTION my_lower AS 'com.demo.MyLowerUDF';
SELECT my_lower(stu_name) FROM student_info LIMIT 5;

sqoop import \
  --connect jdbc:mysql://hadoop1:3306/nev --username root --password 123456 \
  --table web_chat_ems --hive-import --hive-database ods --hive-table web_chat_ems \
  --fields-terminated-by '\t' -m 1

-- 反向：Hive 导出到 MySQL（目标表需先建好）
sqoop export \
  --connect jdbc:mysql://hadoop1:3306/nev --username root --password 123456 \
  --table visit_dws \
  --export-dir /user/hive/warehouse/dws.db/visit_dws \
  --input-fields-terminated-by '\t'
  -- 注意：导出前请确认 MySQL 端已建好同结构的接收表