大数据从入门到实战 - Hive表DDL操作（二）

我不是女神ヾ 2023-01-06 05:30 133阅读 0赞

### 大数据从入门到实战 - Hive表DDL操作（二） ###

*  一、关于此次实践
 *   *  1、实战简介
     *  2、全部任务
 *  二、实践详解
 *   *  1、第1关：Create/Drop/Alter 视图
     *  2、第2关：Create/Drop/ALTER 索引

***叮嘟！这里是小啊呜的学习课程资料整理。好记性不如烂笔头，今天也是努力进步的一天。一起加油进阶吧！***  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTQzNzg5_size_16_color_FFFFFF_t_70_pic_center]

# 一、关于此次实践 #

## 1、实战简介 ##

Hive数据定义语言（Date Definition Language）包括 Create/Drop/Alter数据库、Create/Drop/Truncate表、Alter 表/分区/列、Create/Drop/Alter视图、Create/Drop/Alter索引、Create/Drop函数、Create/Drop/Grant/Revoke角色和权限等内容。

## 2、全部任务 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTQzNzg5_size_16_color_FFFFFF_t_70]

# 二、实践详解 #

## 1、第1关：Create/Drop/Alter 视图 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTQzNzg5_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTQzNzg5_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTQzNzg5_size_16_color_FFFFFF_t_70 3]

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

**评测**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTQzNzg5_size_16_color_FFFFFF_t_70 4]

## 2、第2关：Create/Drop/ALTER 索引 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTQzNzg5_size_16_color_FFFFFF_t_70 5]  
按照以上要求填写命令。每个要求对应一条命令，共`5`条命令，以`;`隔开。由于`hive`启动时间较长，测评时请耐心等待，大概需要时间：`1-3`分钟。

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

**评测**  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTQzNzg5_size_16_color_FFFFFF_t_70 6][]

**Ending！**  
更多课程知识学习记录随后再来吧！

就酱，嘎啦！

![在这里插入图片描述][20200207152559955.png_pic_center]

注：  
人生在勤，不索何获。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTQzNzg5_size_16_color_FFFFFF_t_70_pic_center]: /images/20221119/1234b94177af48709d85802c888a7064.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTQzNzg5_size_16_color_FFFFFF_t_70]: /images/20221119/5442994d92f44ee7bc921471f37bef9e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTQzNzg5_size_16_color_FFFFFF_t_70 1]: /images/20221119/b5a9561b0dc442aa8e408f61bb0a7151.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTQzNzg5_size_16_color_FFFFFF_t_70 2]: /images/20221119/d665835b3f714c828c7a9679af5f9844.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTQzNzg5_size_16_color_FFFFFF_t_70 3]: /images/20221119/3000178de6dd4f7fbbacc2996bb363b3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTQzNzg5_size_16_color_FFFFFF_t_70 4]: /images/20221119/438d3a6f752144419329aea599525163.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTQzNzg5_size_16_color_FFFFFF_t_70 5]: /images/20221119/3380102b591444c788574025ea1009db.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTQzNzg5_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20210113234705541.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTQzNzg5,size_16,color_FFFFFF,t_70
[20200207152559955.png_pic_center]: /images/20221119/93af983559454ff1a2eb9ac997cb3d65.png