初级练习[1]:Hive数据环境搭建与SQL查询实战

环境准备

建表语句

-- 创建学生表
DROP TABLE IF EXISTS student_info;
CREATE TABLE IF NOT EXISTS student_info (
  stu_id STRING COMMENT '学生id',
  stu_name STRING COMMENT '学生姓名',
  birthday STRING COMMENT '出生日期',
  sex STRING COMMENT '性别'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

-- 创建课程表
DROP TABLE IF EXISTS course_info;
CREATE TABLE IF NOT EXISTS course_info (
  course_id STRING COMMENT '课程id',
  course_name STRING COMMENT '课程名',
  tea_id STRING COMMENT '任课老师id'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

-- 创建老师表
DROP TABLE IF EXISTS teacher_info;
CREATE TABLE IF NOT EXISTS teacher_info (
  tea_id STRING COMMENT '老师id',
  tea_name STRING COMMENT '老师姓名'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

-- 创建分数表
DROP TABLE IF EXISTS score_info;
CREATE TABLE IF NOT EXISTS score_info (
  stu_id STRING COMMENT '学生id',
  course_id STRING COMMENT '课程id',
  score INT COMMENT '成绩'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

数据准备

步骤

1. 创建目录[lzl@hadoop12 module]$ mkdir data
2. 将数据文件放置到指定目录 将如下4个文件放到 /opt/module/data 目录下。
3. 数据样式说明- student_info.txt``````001,彭于晏,1995-05-16,男002,胡歌,1994-03-20,男003,周杰伦,1995-04-30,男004,刘德华,1998-08-28,男005,唐国强,1993-09-10,男006,陈道明,1992-11-12,男007,陈坤,1999-04-09,男008,吴京,1994-02-06,男009,郭德纲,1992-12-05,男0010,于谦,1998-08-23,男011,潘长江,1995-05-27,男012,杨紫,1996-12-21,女013,蒋欣,1997-11-08,女014,赵丽颖,1