Hive表DDL操作（二）

第1关 Create/Drop/Alter 视图

#********* 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 *********#

第2关 Create/Drop/ALTER 索引

#********* Begin *********#

echo "

create database if not exists test2

LOCATION '/hive/test2'

WITH DBPROPERTIES('creator'='Floret','date'='2020-11-16');

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')

COMMENT 'students information table'

row format delimited fields terminated by ','

collection items terminated by '-'

TBLPROPERTIES ('creator'='Floret','date'='2020-11-16');

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 *********#