Hive表DDL操作(一)

x33g5p2x  于2021-03-14 发布在 Hive  
字(2.0k)|赞(0)|评价(0)|浏览(694)

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

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;

"

第2关 :Create/Drop/Truncate 表

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;  
"

第3关 :Alter 表/列

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

第4关 :表分区

echo "
CREATE DATABASE IF NOT EXISTS test4
LOCATION '/hive/test4'
WITH DBPROPERTIES('creator'='John','date'='2019-02-25');
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')
COMMENT 'students information table'
PARTITIONED BY (stu_year STRING,subject STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','
TBLPROPERTIES('creator'='John','date'='2019-02-25');
ALTER TABLE student ADD PARTITION (stu_year='2018',subject='Chinese') LOCATION '/hive/test4/student/2018/Chinese'
PARTITION (stu_year='2018',subject='Math') LOCATION '/hive/test4/student/2018/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');



"

相关文章