educoder（头歌）实训平台大数据作业答案-Hive表DDL *** 作（一）

Hive表DDL *** 作（一） 第1关：Create/Alter/Drop 数据库

CREATE DATABASE IF NOT EXISTS test1  
LOCATION '/hive/test1'  
WITH DBPROPERTIES('creator'='John','date'='2019-02-25');  

ALTER DATABASE test1 SET DBPROPERTIES('creator'='Marry');  

DROP DATABASE test1;  

第2关：Create/Drop/Truncate 表

CREATE DATABASE IF NOT EXISTS test2  
LOCATION '/hive/test2'  
WITH DBPROPERTIES('creator'='John','date'='2022-04-20');  

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  COMMENT 'student score');

CREATE TABLE IF NOT EXISTS student_info  
LIKE student;  

DROP TABLE IF EXISTS student;  

第3关：Alter 表/列

CREATE DATABASE IF NOT EXISTS test3  
LOCATION '/hive/test3'  
WITH DBPROPERTIES('creator'='John','date'='2022-04-20');  

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  COMMENT 'student score');

ALTER TABLE student RENAME TO student_info;  

ALTER TABLE student_info CHANGE age student_age int; 

ALTER TABLE student_info ADD COLUMNS (birthday STRING COMMENT 'student birthday'); 

第4关：表分区

CREATE DATABASE IF NOT EXISTS test4  
LOCATION '/hive/test4'  
WITH DBPROPERTIES('creator'='John','date'='2022-04-20');  

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  COMMENT 'student score')
PARTITIONED BY (stu_year STRING,subject STRING)  
ROW FORMAT DELIMITED  
FIELDS TERMINATED BY '\t'  
COLLECTION ITEMS TERMINATED BY ',' ;

ALTER TABLE student ADD PARTITION (stu_year='2018',subject='Chinese') LOCATION '/hive/test4/student/Chinese/2018'  
PARTITION (stu_year='2018',subject='Math') LOCATION '/hive/test4/student/Math/2018';

ALTER TABLE student PARTITION (stu_year='2018',subject='Math') RENAME TO PARTITION (stu_year='2018',subject='English'); 

ALTER TABLE student DROP IF EXISTS PARTITION (stu_year='2018',subject='Chinese');