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

电玩女神 2023-01-06 05:30 138阅读 0赞

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

*  一、关于此次实践
 *   *  1、实战简介
     *  2、全部任务
 *  二、实践详解
 *   *  1、第1关：Create/Alter/Drop 数据库
     *  2、第2关：Create/Drop/Truncate 表
     *  3、第3关：Alter 表/列
     *  4、第4关：表分区

***叮嘟！这里是小啊呜的学习课程资料整理。好记性不如烂笔头，今天也是努力进步的一天。一起加油进阶吧！***  
![在这里插入图片描述][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角色和权限等内容。  
![在这里插入图片描述][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 1]

# 二、实践详解 #

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

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

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

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

## 2、第2关：Create/Drop/Truncate 表 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTQzNzg5_size_16_color_FFFFFF_t_70 4]  
student表结构：

<table> 
 <thead> 
  <tr> 
   <th>INFO</th> 
   <th>TYPE</th> 
   <th>COMMENT</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Sno</td> 
   <td>INT</td> 
   <td>student sno</td> 
  </tr> 
  <tr> 
   <td>name</td> 
   <td>STRING</td> 
   <td>student name</td> 
  </tr> 
  <tr> 
   <td>age</td> 
   <td>INT</td> 
   <td>student age</td> 
  </tr> 
  <tr> 
   <td>sex</td> 
   <td>STRING</td> 
   <td>student sex</td> 
  </tr> 
  <tr> 
   <td>score</td> 
   <td><code>STRUCT &lt;Chinese:FLOAT,Math:FLOAT,English:FLOAT&gt;</code></td> 
   <td>student score</td> 
  </tr> 
 </tbody> 
</table>

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

#********* 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'
    TBLPROPERTIES ('creator'='Floret','date'='2020-11-16');
    CREATE TABLE IF NOT EXISTS student_info
    LIKE student;
    DROP TABLE IF EXISTS student;
    "
    #********* End *********#

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

## 3、第3关：Alter 表/列 ##

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

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

#********* Begin *********#
    echo "
    CREATE DATABASE IF NOT EXISTS test3
    LOCATION '/hive/test3'
    WITH DBPROPERTIES ('creator'='Floret','date'='2020-11-16');
    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')
    COMMENT 'students information table'
    TBLPROPERTIES ('creator'='Floret','date'='2020-11-16');
    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 *********#

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

## 4、第4关：表分区 ##

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

#********* Begin *********#
    echo "
    CREATE DATABASE IF NOT EXISTS test4
    LOCATION '/hive/test4'
    WITH DBPROPERTIES('creator'='Floret','date'='2020-11-16');
    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'='Floret','date'='2020-11-16');
    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');
    "
    #********* End *********#

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

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

就酱，嘎啦！

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

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTQzNzg5_size_16_color_FFFFFF_t_70_pic_center]: /images/20221119/bbb001f660f9492a8c801a482fe3268c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTQzNzg5_size_16_color_FFFFFF_t_70]: /images/20221119/6f9d35e586f04ea396ae8f316351973d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTQzNzg5_size_16_color_FFFFFF_t_70 1]: /images/20221119/14b39ca1f40e4e9c9db02b9f8caa0f78.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTQzNzg5_size_16_color_FFFFFF_t_70 2]: /images/20221119/a213f5224b7546a4a67fe918e1418c44.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTQzNzg5_size_16_color_FFFFFF_t_70 3]: /images/20221119/fc54d49dbfc9454fa10b66eab718dd5c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTQzNzg5_size_16_color_FFFFFF_t_70 4]: /images/20221119/6c07009ad93d49fd90073074aad89964.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTQzNzg5_size_16_color_FFFFFF_t_70 5]: /images/20221119/37cd887bc8024de29b34ada992330b64.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTQzNzg5_size_16_color_FFFFFF_t_70 6]: /images/20221119/2a83d78d239a469ba3d9695b5df634f1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTQzNzg5_size_16_color_FFFFFF_t_70 7]: /images/20221119/b24f734372e942ad9d91b32154e804ab.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTQzNzg5_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20210113233805335.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTQzNzg5,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTQzNzg5_size_16_color_FFFFFF_t_70 9]: https://img-blog.csdnimg.cn/20210113233628360.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTQzNzg5,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTQzNzg5_size_16_color_FFFFFF_t_70 10]: https://img-blog.csdnimg.cn/20210113233904416.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTQzNzg5,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTQzNzg5_size_16_color_FFFFFF_t_70 11]: https://img-blog.csdnimg.cn/20210113233953224.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTQzNzg5,size_16,color_FFFFFF,t_70
[20200207152559955.png_pic_center]: https://img-blog.csdnimg.cn/20200207152559955.png#pic_center