hive 建库建表插入数据

最新推荐文章于 2024-12-12 22:12:57 发布

原创最新推荐文章于 2024-12-12 22:12:57 发布 · 1.4k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#hive

本文详细介绍了使用Hive进行数据库创建、表结构定义、数据上传及加载的完整流程。通过具体命令演示了如何在HDFS中存储数据，并利用Hive SQL进行数据表的构建与数据插入，为大数据分析提供了实用的操作指南。

hive 建库建表插入数据

先上传文件

sftp> put /Users/chenxin/Downloads/HQL50/HQL50/score.txt /root/data/hql50

sftp> put /Users/chenxin/Downloads/HQL50/HQL50/teacher.txt /root/data/hql50 

sftp> put /Users/chenxin/Downloads/HQL50/HQL50/course.txt /root/data/hql50

sftp> put /Users/chenxin/Downloads/HQL50/HQL50/student.txt /root/data/hql50

在hdfs 建文件夹将传上去的文件存到hdfs中

hdfs dfs -mkdir /homework
hdfs dfs -put course.txt /homework
hdfs dfs -put score.txt /homework
hdfs dfs -put student.txt /homework
hdfs dfs -put teacher.txt /homework

去50070端口查看是否传上去了

hive 建库用库

create database homework;
use homewrok;

建表

create table if not exists course(
course_id int,course_name string,teacher_id int
) COMMENT 'course table' 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\n' 
STORED AS TEXTFILE;

create table if not exists score(
student_id int,course_id int,score int
) COMMENT 'score table' 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\n' 
STORED AS TEXTFILE;

create table if not exists student(
student_id int,student_name string,
student_birth string,student_sex string
) COMMENT 'student table' 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\n' 
STORED AS TEXTFILE;

create table if not exists teacher(
teacher_id int,
Display all 478 possibilities? (y or n)
ame string
) COMMENT 'teacher table' 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\n' 
STORED AS TEXTFILE;

查看表是否建完成

show tables;

将hdfs文件中数据插入表中

load data inpath'/homework/course.txt' into table course;
load data inpath'/homework/score.txt' into table score;
load data inpath'/homework/student.txt' into table student;
load data inpath'/homework/teacher.txt' into table teacher;

查看表中是否有数据