Hive表DDL操作

第1关：Create/Alter/Drop数据库

编程要求

在Begin - End区域内进行sql语句代码补充，具体任务如下：

• 创建数据库test1，位于HDFS的/hive/test1下，创建人creator为John，创建日期date为2019-02-25
• 修改数据库test1的创建人为Marry
• 删除数据库test1

#********* Begin *********#
echo "
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;
"
#********* End *********#

第2关：Create/Drop/Truncate 表

编程要求

在Begin - End区域内进行sql语句代码补充，具体任务如下：

student表结构：

INFO	TYPE	COMMENT
Sno	INT	student sno
name	STRING	student name
age	INT	student age
sex	STRING	student sex
score	STRUCT <Chinese:FLOAT,Math:FLOAT,English:FLOAT>	student score

• 创建数据库test2
• 在test2中创建表student，表结构如上所示
• 使用LIKE关键字创建一个与student表结构相同的表student_info
• 删除表student

#********* Begin *********#
echo "

create database if not exists test2;
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');
create table if not exists student_info like student;
drop table if exists student;

"
#********* End *********#

第3关：Alter表/列

编程要求

在Begin - End区域内进行sql语句代码补充，具体任务如下：

student表结构：

INFO	TYPE	COMMENT
Sno	INT	student sno
name	STRING	student name
age	INT	student age
sex	STRING	student sex
score	STRUCT <Chinese:FLOAT,Math:FLOAT,English:FLOAT>	student score

• 创建数据库test3
• 在数据库tets3中，创建表student，表结构如上所示，和第二关相同
• 重命名表名字student为student_info
• 修改列名age为student_age
• 增加一列birthday，数据类型为STRING，说明信息为student birthday

#********* Begin *********#
echo "

CREATE DATABASE IF NOT EXISTS test3;
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');
ALTER TABLE student RENAME TO student_info;
ALTER TABLE student_info CHANGE age student_age INT COMMENT 'student age';
ALTER TABLE student_info ADD COLUMNS (birthday STRING COMMENT 'student birthday');

"
#********* End *********#

第4关：表分区

知识点

1.表分区信息持久化的语法为：MSCK REPAIR TABLE table_name;

编程要求

在Begin - End区域内进行sql语句代码补充，具体任务如下： student表结构：

INFO	TYPE	COMMENT
Sno	INT	student sno
name	STRING	student name
age	INT	student age
sex	STRING	student sex
score	STRUCT <Chinese:FLOAT,Math:FLOAT,English:FLOAT>	student score

• 创建数据库test4
• 在数据库tets4中，创建分区表student，表结构如上所示，和第二、三关相同，设置分区列为：stu_year类型STRING、subject类型STRING
• 添加两个分区：stu_year='2018',subject='Chinese'和stu_year='2018',subject='Math'
• 重命名表分区：将2018/Math分区重命名为2018/English
• 删除表分区：将2018/Chinese分区删除

#********* Begin *********#
echo "

CREATE DATABASE IF NOT EXISTS test4;
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);
ALTER TABLE student ADD PARTITION (stu_year='2018',subject='Chinese') PARTITION (stu_year='2018',subject='Math');
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');

"
#********* End *********#

第5关：Create/Drop/Alter 视图

编程要求

在Begin - End区域内进行sql语句代码补充，具体任务如下：

student表结构：

INFO	TYPE
Sno	INT
name	STRING
age	INT
sex	STRING
score	STRUCT Chinese:FLOAT,Math:FLOAT,English:FLOAT

• 创建test1数据库
• 在test1中创建表student，表结构如上所示
• 创建视图student_view
• 修改视图名student_view为student_info_views
• 删除student_info_views视图

#********* Begin *********#
echo "
create database if not exists test1;
create table if not exists test1.student(Sno INT,name STRING,age INT,sex STRING,score STRUCT <Chinese:FLOAT,Math:FLOAT,English:FLOAT>);
create view student_view(Sno,name_length,age,sex) as select Sno,length(name),age,sex from student;
alter view student_view rename to student_info_views;
drop view if exists student_info_views;

"
#********* End *********#

第6关：Create/Drop/ALTER 索引

编程要求

student.txt中的数据格式如下：

• 创建test2数据库；

• 根据以上数据创建student表；

• 将/home/student.txt中的数据导入到表student中；

• 根据学号Sno创建索引student_index；

• 删除索引student_index。

#********* Begin *********#
echo "
create database if not exists test2;
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 '-';
load data local inpath '/home/student.txt' overwrite into table student;
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;
drop index if exists student_index on student;

"
#********* End *********#