EduCoder-Hive表DDL操作（一、二）答案-路漫漫远俢兮

第一关：

//创建数据库test1，位于HDFS的/hive/test1下，创建人creator为John，创建日期date为2019-02-25
CREATE DATABASE IF NOT EXISTS test1 
 LOCATION '/hive/test1'
 WITH DBPROPERTIES('creator'='John','date'='2019-02-25');

//修改数据库test1的创建人为Marry
ALTER DATABASE test1 SET DBPROPERTIES('creator'='Marry');

//删除数据库test1
DROP DATABASE test1;

第二关：

//创建数据库test2
CREATE DATABASE IF NOT EXISTS test2;

//在test2中创建表student
CREATE TABLE IF NOT EXISTS test2.student(
Sno INT COMMENT 'student sno',
name STRING COMMENT 'student name',
age INT COMMENT 'student age',
sex STRING COMMENT 'student sex',
score STRUCT <Chinese:FLOAT,Math:FLOAT,English:FLOAT> COMMENT 'student score');


//使用LIKE关键字创建一个与student表结构相同的表student_info

CREATE TABLE IF NOT EXISTS student LIKE student_info;//这里写反了

CREATE TABLE IF NOT EXISTS student_info LIKE student;（使用LIKE关键字，只复制表定义，不复制表数据）

//删除表student
DROP TABLE  student;

第三关：

//创建数据库test3
CREATE DATABASE IF NOT EXISTS test3;

//在数据库tets3中，创建表student
CREATE TABLE IF NOT EXISTS test3.student(
Sno INT COMMENT 'student sno',
name STRING COMMENT 'student name',
age INT COMMENT 'student age',
sex STRING COMMENT 'student sex',
score STRUCT <Chinese:FLOAT,Math:FLOAT,English:FLOAT> COMMENT 'student score');

//重命名表名字student为student_info
ALTER TABLE student RENAME TO student_info;

//修改列名age为student_age
ALTER TABLE student_info CHANGE age student_age INT COMMENT 'student age';

//增加一列birthday，数据类型为STRING，说明信息为student birthday
ALTER TABLE student_info ADD COLUMNS (birthday STRING COMMENT 'student birthday');

第四关:

CREATE DATABASE IF NOT EXISTS test4;

//在数据库tets4中，创建分区表student，表结构如上所示，和第二、三关相同，
//设置分区列为：stu_year类型STRING、subject类型STRING
CREATE TABLE IF NOT EXISTS test4.student(
Sno INT COMMENT 'student sno',
name STRING COMMENT 'student name',
age INT COMMENT 'student age',
sex STRING COMMENT 'student sex',
score STRUCT <Chinese:FLOAT,Math:FLOAT,English:FLOAT> COMMENT 'student score')
PARTITIONED BY (stu_year STRING,subject STRING);



//添加两个分区：stu_year='2018',subject='Chinese'和stu_year='2018',subject='Math'
ALTER TABLE student ADD PARTITION (subject='Chinese',stu_year='2018')  ***//2018/chinese***
LOCATION '/hive/test4/student/2018/Chinese'
PARTITION (subject='Math',stu_year='2018')
LOCATION '/hive/test4/student/2018/Math';

//重命名表分区：将2018/Math分区重命名为2018/English
ALTER TABLE student PARTITION (subject='Math',stu_year='2018') 
RENAME TO PARTITION (subject='English',stu_year='2018');

//删除表分区：将2018/Chinese分区删除
ALTER TABLE student DROP IF EXISTS PARTITION (subject='Chinese',stu_year='2018');

第五关（二）：

//创建test1数据库
CREATE DATABASE IF NOT EXISTS test1;

//在test1中创建表student
CREATE TABLE IF NOT EXISTS test1.student(
Sno INT ,
name STRING ,
age INT ,
sex STRING ,
score STRUCT <Chinese:FLOAT,Math:FLOAT,English:FLOAT>
) ;


//创建视图student_view
CREATE VIEW student_view(
Sno,
name_length,
age,
sex
) 
AS SELECT Sno,length(name),age,sex FROM student;

//修改视图名student_view为student_info_views
alter view student_view rename to student_info_views;

//删除student_info_views视图
 drop view if exists student_info_views;

第六关（二）：

//创建test2数据库；
CREATE DATABASE IF NOT EXISTS test2;

//创建student表
CREATE TABLE IF NOT EXISTS test2.student(
Sno INT ,
name STRING ,
age INT ,
sex STRING ,
score STRUCT <Chinese:FLOAT,Math:FLOAT,English:FLOAT>
) 
row format delimited fields terminated by ','   
collection items terminated by '-'  
;

//将/home/student.txt中的数据导入到表student中
load data local inpath '/home/student.txt'
overwrite into table student;

//根据学号Sno创建索引student_index
create index student_index on table student(Sno)
as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'
with deferred rebuild
IN TABLE student_index_table;

//删除索引student_index
drop index if exists student_index on student;

 

座右铭：站在别人的思想上，看见自己的不足，传播错误的经验，愿君不重蹈覆辙。

 

由于受限于本人经验，难免不足，如有建议，欢迎留言交流。

 

说明：如果喜欢，请点赞，您的鼓励是本人前进的最好动力。