实操练习手册

CREATE DATABASE IF NOT EXISTS school COMMENT '校园选课库';
USE school;

CREATE TABLE IF NOT EXISTS student_info(
    stu_id STRING, stu_name STRING, birthday STRING, sex STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

LOAD DATA LOCAL INPATH '/home/hadoop/data/student_info.txt' INTO TABLE student_info;
SELECT count(*) FROM student_info;

CREATE TABLE IF NOT EXISTS score_part(
    stu_id STRING, score INT
)
PARTITIONED BY (course_id STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

SET hive.exec.dynamic.partition = true;
SET hive.exec.dynamic.partition.mode = nonstrict;

INSERT OVERWRITE TABLE score_part PARTITION(course_id)
SELECT stu_id, score, course_id FROM score_info;

SHOW PARTITIONS score_part;

CREATE TABLE IF NOT EXISTS score_bucket(
    stu_id STRING, course_id STRING, score INT
)
CLUSTERED BY (stu_id) INTO 4 BUCKETS
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;

INSERT OVERWRITE TABLE score_bucket SELECT * FROM score_info;

SELECT * FROM score_bucket TABLESAMPLE(BUCKET 1 OUT OF 4 ON stu_id);

CREATE VIEW IF NOT EXISTS v_score_detail AS
SELECT s.stu_name, c.course_name, t.tea_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
JOIN teacher_info t ON c.tea_id = t.tea_id;

SELECT * FROM v_score_detail LIMIT 5;

CREATE TABLE IF NOT EXISTS stu_male(
    stu_id STRING, stu_name STRING, birthday STRING, sex STRING
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;

INSERT INTO stu_male SELECT * FROM student_info WHERE sex = '男';
SELECT count(*) FROM stu_male;

SELECT stu_id, stu_name, birthday FROM student_info WHERE sex = '女';

SELECT stu_name FROM student_info WHERE stu_name LIKE '刘%';

SELECT course_id, count(*) AS 人数, round(avg(score), 2) AS 平均分
FROM score_info GROUP BY course_id ORDER BY course_id;

WITH avg_t AS (
    SELECT course_id, round(avg(score), 2) AS avg_score
    FROM score_info GROUP BY course_id HAVING avg(score) > 65
)
SELECT c.course_name, a.avg_score
FROM avg_t a JOIN course_info c ON a.course_id = c.course_id
ORDER BY a.avg_score DESC;

SELECT s.stu_name, sum(sc.score) AS 总分
FROM score_info sc JOIN student_info s ON sc.stu_id = s.stu_id
GROUP BY s.stu_name
ORDER BY 总分 DESC
LIMIT 5;

SELECT c.course_name, t.tea_name
FROM course_info c JOIN teacher_info t ON c.tea_id = t.tea_id
ORDER BY c.course_id;

SELECT s.stu_id, s.stu_name
FROM student_info s
LEFT JOIN score_info sc ON s.stu_id = sc.stu_id AND sc.course_id = '05'
WHERE sc.stu_id IS NULL
ORDER BY s.stu_id;

SELECT s.sex, round(avg(sc.score), 2) AS 平均分, count(*) AS 记录数
FROM score_info sc JOIN student_info s ON sc.stu_id = s.stu_id
GROUP BY s.sex;

SELECT CASE WHEN score >= 90 THEN '优秀'
             WHEN score >= 80 THEN '良好'
             WHEN score >= 60 THEN '及格'
             ELSE '不及格' END AS 等级,
       count(*) AS 人数
FROM score_info
WHERE course_id = '01'
GROUP BY CASE WHEN score >= 90 THEN '优秀'
              WHEN score >= 80 THEN '良好'
              WHEN score >= 60 THEN '及格'
              ELSE '不及格' END
ORDER BY 人数 DESC;

SELECT * FROM (
    SELECT course_id, stu_id, score,
           rank() OVER (PARTITION BY course_id ORDER BY score DESC) AS rk
    FROM score_info
) t
WHERE rk <= 3
ORDER BY course_id, rk;

SELECT concat(upper(stu_name), '(', sex, ')') AS 标签
FROM student_info LIMIT 5;

SELECT course_id,
       count(*) AS 人数,
       round(stddev(score), 2) AS 标准差
FROM score_info
GROUP BY course_id
ORDER BY 标准差 DESC;

WITH stage1 AS (
    SELECT course_id,
           cast(rand() * 10 AS INT) AS salt,
           count(*) AS part_cnt
    FROM score_info
    GROUP BY course_id, cast(rand() * 10 AS INT)
)
SELECT course_id, sum(part_cnt) AS cnt
FROM stage1
GROUP BY course_id
ORDER BY course_id;

CREATE TABLE IF NOT EXISTS score_orc(
    stu_id STRING, course_id STRING, score INT
)
STORED AS ORC
TBLPROPERTIES ('orc.compress' = 'SNAPPY');

INSERT INTO score_orc SELECT * FROM score_info;