Hive DDL数据定义语言

桃扇骨 2022-10-15 07:00 246阅读 0赞

### 本文目录 ###

*  1.创建数据库
 *   *  Ⅰ.建库语句
     *  Ⅱ.查询数据库
     *   *  1.查看已有的数据库
         *  2.使用数据库
         *  3.显示数据库信息
     *  Ⅲ.修改数据库dbproperties属性
     *  Ⅲ.删除数据库
     *   *  1.删除空数据库
         *  2.删除非空数据库，采用 cascade 命令强制删除
 *  2.创建表
 *   *  Ⅰ.建表语句
     *  Ⅱ.内部表 & 外部表
     *   *  1.内部表(管理表)和外部表区别
         *  2.何时用内部表(管理表)？何时用外部表？
         *  3.内部表和外部表的转换
     *  Ⅲ.show 查看已有表的建表语句
     *  Ⅳ.修改表
     *   *  1.重命名
         *  2.增加、修改、删除分区
         *  3.增加、修改、替换列信息

DDL (Data Definition Language) 数据定义语言。DDL主要是用在`定义`或`改变`表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用。建库建表并不会经常建，所以 DML 比 DDL 更重要。

# 1.创建数据库 #

## Ⅰ.建库语句 ##

CREATE DATABASE [IF NOT EXISTS] database_name
    [COMMENT database_comment]
    [LOCATION hdfs_path]
    [WITH DBPROPERTIES (property_name=property_value, ...)];

> **字段介绍：** (\[ \] 中括号中内容为创建库时的可选项)  
>    
>   1.COMMENT：数据库描述  
>    
>   2.LOCATION：指定数据库存放在 HDFS 的目录，默认存储在 hive 所指定的地址 /user/hive/warehouse/目录下，HDFS中数据库通常统一为 **\*.db**。比如创建了一个 test库，在HDFS中默认地址为：**/user/hive/warehouse/test.db**  
>    
>   3.WITH DBPROPERTIES：DB数据库属性，设置数据库属性信息，以使用 key=value 格式添加，比如：创建时间、创建人、修改人 等信息。**不常用**
> 
> --------------------
> 
> **注意：**默认创建的库，存储在 Hive 默认路径下，如果通过 LOCATION 属性修改库所在的位置。如果指定库存储在 HDFS 根目录下。建议是 `location '/库名.db'`，而不是 `location '/'`。此处存储在根目录下，如果不指定`库名.db`，那么在 HDFS 根目录下是看不到这个库名的文件夹的，如果在该库中创建表，以表名命名的文件夹会直接在 根目录下，导致数据比较混乱，一般不建议这么干！！！  
> ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6YjM0ODExMDE3NQ_size_16_color_FFFFFF_t_70]

## Ⅱ.查询数据库 ##

> **先创建一个 testdb 数据库**  
> 建库语句：`create database if not exists testdb;`

### 1.查看已有的数据库 ###

> **show databases;**  **(查询所有的数据库)**  
>    
> **show databases** **`like 'test*';`**  **(过滤test开头的数据库)**

### 2.使用数据库 ###

> **use** testdb;

### 3.显示数据库信息 ###

> **desc database** **testdb;**  **(显示数据库基本信息，不包括dbproperties内容)**  
>    
> **desc database extended** **testdb;**  **(显示数据库详细信息，包括dbproperties内容)**

## Ⅲ.修改数据库dbproperties属性 ##

用户可以使用 `alter database 库名` 命令，为某个数据库的 `DBPROPERTIES` 设置键-值对属性值，来描述这个数据库的属性信息。**（数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。hive 暂不支持数据库重命名操作，仅支持修改dbproperties和数据库的用户或权限;）**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6YjM0ODExMDE3NQ_size_16_color_FFFFFF_t_70 1]

## Ⅲ.删除数据库 ##

### 1.删除空数据库 ###

> drop database testdb;  
>    
> drop database **if exists** testdb; **(如果删除的数据库不确定存在与否，最好采用 if exists 判断一下数据库是否存在)**

### 2.删除非空数据库，采用 cascade 命令强制删除 ###

> drop database testdb; **cascade;**  
> ![在这里插入图片描述][2021052411292464.png]

> **注意：**在手工操作删除库后，MySQL元数据中的元数据信息会被删除，HDFS中相对应的文件夹也会被删除。

# 2.创建表 #

## Ⅰ.建表语句 ##

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
    [(col_name data_type [COMMENT col_comment], ...)]
    [COMMENT table_comment]
    [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
    [CLUSTERED BY (col_name, col_name, ...)
    [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
    [ROW FORMAT row_format]
    [STORED AS file_format]
    [LOCATION hdfs_path]
    [TBLPROPERTIES (property_name=property_value, ...)]
    [AS select_statement]
    [LIKE table_name]

> **字段介绍：**(\[ \] 中括号中内容为创建表时的可选项)  
>    
> 1.EXTERNAL：  
>   external 关键字，可以允许用户创建一个外部表，在建表的同时可以指定一个指向实际数据的路径（LOCATION），在删除表的时候，**内部表**的元数据和HDFS数据会被一起删除；而**外部表**只删除元数据，不删除HDFS中实际的数据。**（Hive中的表，分为外部表和内部表）**  
>    
> 2.COMMENT col\_comment：  
>   为表中的每一列属性字段备注，即：列备注  
>    
> 3.COMMENT table\_comment：  
>   为表添加备注，即：表备注  
>    
> 4.PARTITIONED BY：  
>   创建分区表，请参考：[分区表介绍][Link 1]  
>    
> 5.CLUSTERED BY：  
>   创建分桶表，请参考：[分桶表介绍][Link 1]  
>    
> 6.SORTED BY (col\_name \[ASC|DESC\], …)\] INTO num\_buckets BUCKETS：  
>   对桶中的一个或多个列另外排序，这个也和分桶表有关系，分桶表会指定按照哪个字段进行分桶，以及分几个桶（INTO num\_buckets BUCKETS），不常用。在 分桶表中有介绍。请参考：[分桶表介绍][Link 1]  
>    
> 7.ROW FORMAT：  
>   用于定义行的格式，如：ROW FORMAT DELIMITED \[FIELDS TERMINATED BY char\] \[COLLECTION ITEMS TERMINATED BY char\] \[MAP KEYS TERMINATED BY char\] \[LINES TERMINATED BY char\] | SERDE serde\_name \[WITH SERDEPROPERTIES (property\_name=property\_value, property\_name=property\_value, …)\]  
>    
>   用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。**如果没有指定 ROWFORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe**。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表的具体的列的数据。  
>    
>   SerDe 是 Serialize/Deserilize 的简称， hive 使用 Serde 进行行对象的序列与反序列化。  
>    
> 8.STORED AS file\_format：  
>   指定存储文件类型。file\_format 就类似于 text，lzo，snappy 压缩方式，就是文件格式，按什么存储。Hive对应的是MR，MR可以使用压缩，那么Hive就可以指定压缩格式。默认的是text文本文件  
>    
>   常用的存储文件类型：`SEQUENCEFILE`（二进制序列文件）、`TEXTFILE`（文本）、`ORCFILE`（列式存储格式文件） 如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE  
>    
> 7.LOCATION ：  
>   指定表在 HDFS 上的存储位置。  
>    
> 8.TBLPROPERTIES ：  
>   对表加一些属性，类似于 `DBPROPERTIES`，对表加一些属性。  
>    
> 9.AS select\_statement：  
>   select\_statement 意为 select 筛选语句。Hive 建表时，可以根据另外一张表查询出来的结果来建表。比如A表有10个字段，现在我需要建个表，只要5个字段，但是5个字段都是A表中的，可以根据 `create table xx as select id,name,age,address,mobile from user`; 用这种方式建表，通过查询的方式建表，同时还附带查询出来的数据哦！！！  
>    
> 10.LIKE table\_name：  
>   LIKE 允许用户复制现有的表结构，但是不复制数据。  
>   eg：`create table test1 like test;`

## Ⅱ.内部表 & 外部表 ##

Hive 中的表分为`内部表`和`外部表`。内部表又称为管理表(`MANAGED_TABLE`)，没有添加 EXTERNAL 关键字的表都是内部表。真正的类型应该叫做**管理表**  
![在这里插入图片描述][20210524160342986.png]

### 1.内部表(管理表)和外部表区别 ###

*  管理表：删除时，元数据和 HDFS 数据都会被删除；
 *  外部表：删除时，只有元数据会被删除，HDFS 数据不会被删除

### 2.何时用内部表(管理表)？何时用外部表？ ###

由于管理表在删除时，会同时将HDFS数据一并删除，所以不适合和其他工具（Spark、Flink、其他系统等）共享使用数据。如果数据除了 Hive 使用，还有其他系统使用，建议建成外部表。内部表利用场景相对较少，只有中间表、临时表等会用到内部表，其他表都会用外部表。

外部表就更安全一些，防止误操作。表删了，数据还在，我们就可以通过建表语句的方式，创建一个元数据，然后 Hive 就又能操作数据，然后再去查询，就又能找到了。

### 3.内部表和外部表的转换 ###

如果建表时忘记指定，写入一段时间数据后发现了问题，本应该是外部表，结果建的却是管理表，该怎么办呢？这种情况下，我们也可以实现内部表和外部表之间的相互转换，省去了建表、拷贝数据等繁琐操作。

> **1.查询表的类型**  
>    
>   可通过命令查询，也可以直接在元数据库中的TBLS表中查看。命令如下：  
>    
>   **命令：**  
>    hive (default)> desc formatted test;  
>    Table Type: MANAGED\_TABLE  
>    
> **2.管理表 & 外部表转换**  
>    
>  1.**管理表**修改为**外部表**：`alter table test set tblproperties('EXTERNAL'='TRUE');`  
>    
>  2.**外部表**修改为**管理表**：`alter table test set tblproperties('EXTERNAL'='FALSE');`  
>    
>  **注意：**`('EXTERNAL'='TRUE')`和`('EXTERNAL'='FALSE')`为固定写法，区分大小写！

## Ⅲ.show 查看已有表的建表语句 ##

在Hive中建表，我们可以通过一个简单的语句建表，比如`create table test(id string);` ，我们就可以创建一个 test 表，非常的简单，但是其他需要我们定义的项，我们没有定义则默认使用的都是默认值。

可以使用命令`show create table 表名;`来查看默认的建表语句。如果没指定，使用的都是默认配置。  
  ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6YjM0ODExMDE3NQ_size_16_color_FFFFFF_t_70 2]

## Ⅳ.修改表 ##

### 1.重命名 ###

> **语法：**  
>    
>  ALTER TABLE table\_name RENAME TO new\_table\_name;

### 2.增加、修改、删除分区 ###

> 创建分区表，请参考：[分区表介绍][Link 1]

### 3.增加、修改、替换列信息 ###

> 修改列信息，包括：**1.修改列名**  **2.修改列类型**  
>    
> **1.更新列**  
>    
> **语法：**  
>   ALTER TABLE table\_name CHANGE \[COLUMN\] col\_old\_name col\_new\_name column\_type \[COMMENT col\_comment\] \[FIRST|AFTER column\_name\]  
>    
> **注意：**  
>   在修改列类型时，①int 修改为 String 是允许的；②String 修改为 int 如果 String字段为汉字等，修改提示失败，如果修改成功，查询出来的就有NULL了，所以修改会失败；③如果只需要改列名，而不需要改类型，也需要根据语法，将原来的类型加上  
> ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6YjM0ODExMDE3NQ_size_16_color_FFFFFF_t_70 3]  
>    
> **2.增加列 & 替换列**  
>    
> **语法：**  
>   ALTER TABLE table\_name ADD|REPLACE COLUMNS (**col\_name** **data\_type** \[COMMENT col\_comment\], …)  
>    
> **注意：**  
>   **ADD** 是代表新增一字段，字段位置在所有列后面（partition分区列之前），  
>   **REPLACE** 则是表示替换表中的所有字段。  
>    
>   **更新列**使用的 **CHANGE** ，也可以看做是一种替换，CHANGE 是指定列名的方式修改，只对当前一个指定列生效；（即：如果表中有id和name两个字段，使用 change 将 name 字段 修改为 name1字段，则该表中有 id 和 name1 共2个字段了）  
>    
>   **替换列**使用的 **REPLACE**，是以表为维度，对整个表生效，所有字段全部改掉。（即：如果表中有id和name两个字段，使用 replace 替换为 address字段，则该表中就只有一个address字段了）  
> ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6YjM0ODExMDE3NQ_size_16_color_FFFFFF_t_70 4]  
> **3.删除列**  
>    
>   Hive 并没有提供删除列语法支持，那么如何删除列呢？？  
>    
>   可以使用 REPLACE 来实现。比如表 test 中有 **id**(int)、**name**(string)、**address**(string) 三列，需要删除 address 列，使用 REPLACE 即可。语句：`alter table test REPLACE columns (id int,name string);`。  
>    
>   **在修改表时，操作的都是元数据，HDFS文件的数据是不会变化的。**如果由2个字段再改为3个字段，address列依然还是会有数据的。  
> ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6YjM0ODExMDE3NQ_size_16_color_FFFFFF_t_70 5]

> 下一篇：[Hive DML数据操纵语言][Hive DML]

--------------------

**博主写作不易，加个关注呗**

**求关注、求点赞，加个关注不迷路 ヾ(◍°∇°◍)ﾉﾞ**

**我不能保证所写的内容都正确，但是可以保证不复制、不粘贴。保证每一句话、每一行代码都是亲手敲过的，错误也请指出，望轻喷 Thanks♪(･ω･)ﾉ**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6YjM0ODExMDE3NQ_size_16_color_FFFFFF_t_70]: /images/20221014/f293778618594243a8d0886a982ed5cb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6YjM0ODExMDE3NQ_size_16_color_FFFFFF_t_70 1]: /images/20221014/015f4415f8d048e8a17e882f796c4376.png
[2021052411292464.png]: /images/20221014/1220f99755b843b9845186e3dc051795.png
[Link 1]: https://blog.csdn.net/lzb348110175/article/details/117332288
[20210524160342986.png]: /images/20221014/6968a24c9f83426fbee6f47779abf81c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6YjM0ODExMDE3NQ_size_16_color_FFFFFF_t_70 2]: /images/20221014/3208744f04a04948a5c1cf36ff2284f1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6YjM0ODExMDE3NQ_size_16_color_FFFFFF_t_70 3]: /images/20221014/b9c51b817d0f407da0a8724e05006455.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6YjM0ODExMDE3NQ_size_16_color_FFFFFF_t_70 4]: /images/20221014/c4087524aa6d4305a5455da5a1d95615.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6YjM0ODExMDE3NQ_size_16_color_FFFFFF_t_70 5]: /images/20221014/05af4b816c7343e98f204b8ac576f754.png
[Hive DML]: https://blog.csdn.net/lzb348110175/article/details/117250309