大数据离线阶段 06：HQL数据定义语言（DDL）概述

素颜马尾好姑娘i 2023-10-15 09:33 113阅读 0赞

#### **DDL语法的作用** ####

数据定义语言 (Data Definition Language, DDL)，是SQL语言集中对数据库内部的对象结构进行创建，删除，修改等的操作语言，这些数据库对象包括database（schema）、table、view、index等。核心语法由**CREATE**、**ALTER**与**DROP**三个所组成。DDL并不涉及表内部数据的操作。

在某些上下文中，该术语也称为数据描述语言，因为它描述了数据库表中的字段和记录。

![572ccc6d3c04cedfc7458f30a82ff1ae.png][]

#### Hive中DDL使用 ####

Hive SQL（HQL）与SQL的语法大同小异，基本上是相通的，学过SQL的使用者可以无痛使用Hive SQL。只不过在学习HQL语法的时候，特别要注意Hive自己特有的语法知识点，比如partition相关的DDL操作。

基于Hive的设计、使用特点，**HQL中create语法（尤其create table）将是学习掌握DDL语法的重中之重**。可以说建表是否成功直接影响数据文件是否映射成功，进而影响后续是否可以基于SQL分析数据。通俗点说，没有表，表没有数据，你分析什么呢？

选择正确的方向,往往比盲目努力重要。

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

#### **Hive DDL建表基础** ####

#### 完整建表语法树 ####

![b46971764b7581dce7200bc9439f0217.png][]

*  **蓝色**字体是建表语法的关键字，用于指定某些功能。
 *  **\[\]**中括号的语法表示可选。
 *  **|**表示使用的时候，左右语法二选一。
 *  建表语句中的语法顺序要和上述语法规则保持一致。

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

#### **Hive数据类型详解** ####

#### 整体概述 ####

Hive中的数据类型指的是Hive表中的列字段类型。Hive数据类型整体分为两个类别：**原生数据类型**（primitive data type）和**复杂数据类型**（complex data type）。

原生数据类型包括：数值类型、时间类型、字符串类型、杂项数据类型；

复杂数据类型包括：array数组、map映射、struct结构、union联合体。

![f1e669e09f80d9e1bd9a98f162e75726.png][]

关于Hive的数据类型，需要注意：

*  英文字母大小写不敏感；
 *  除SQL数据类型外，还支持Java数据类型，比如：string；
 *  int和string是使用最多的，大多数函数都支持；
 *  复杂数据类型的使用通常需要和分隔符指定语法配合使用。
 *  如果定义的数据类型和文件不一致，hive会尝试隐式转换，但是不保证成功。

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

#### 原生数据类型 ####

Hive支持的原生数据类型如下图所示：

![4f38607730e47d9f4db83e6537d3a8d0.png][]

其中标注的数据类型是使用较多的，详细的描述请查询语法手册：

[https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types][https_cwiki.apache.org_confluence_display_Hive_LanguageManual_Types]

#### 复杂数据类型 ####

Hive支持的复杂数据类型如下图所示：

![61fe78dd6369a2cd0f9ec2b8ecc409fb.png][]

其中标注的数据类型是使用较多的，详细的描述请查询语法手册：

[https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types][https_cwiki.apache.org_confluence_display_Hive_LanguageManual_Types]

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

#### 数据类型隐式、显示转换 ####

与SQL类似，HQL支持隐式和显式类型转换。

原生类型从窄类型到宽类型的转换称为隐式转换，反之，则不允许。

下表描述了类型之间允许的隐式转换：

![6f4cc42ab57887ac7a232712b3e87303.png][]

[https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types][https_cwiki.apache.org_confluence_display_Hive_LanguageManual_Types]

显式类型转换使用CAST函数。

例如，CAST（'100'as INT）会将100字符串转换为100整数值。如果强制转换失败，例如CAST（'INT'as INT），该函数返回NULL。

![a1ffde939be652679dc1c668c09df47f.png][]

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

#### **Hive读写文件机制** ####

#### SerDe是什么 ####

SerDe是Serializer、Deserializer的简称，目的是用于序列化和反序列化。序列化是对象转化为字节码的过程；而反序列化是字节码转换为对象的过程。

Hive使用SerDe（和FileFormat）读取和写入行对象。

![4099e47a291f5e4dcac33c880d026e04.png][]

需要注意的是，“key”部分在读取时会被忽略，而在写入时key始终是常数。基本上**行对象存储在“value”中**。

可以通过desc formatted tablename查看表的相关SerDe信息。默认如下：

![9ab588955e797bea505fee6f8b47159f.png][]

#### Hive读写文件流程 ####

**Hive读取文件机制**：首先调用InputFormat（默认TextInputFormat），返回一条一条kv键值对记录（默认是一行对应一条记录）。然后调用SerDe（默认LazySimpleSerDe）的Deserializer，将一条记录中的value根据分隔符切分为各个字段。

**Hive写文件机制**：将Row写入文件时，首先调用SerDe（默认LazySimpleSerDe）的Serializer将对象转换成字节序列，然后调用OutputFormat将数据写入HDFS文件中。

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

#### SerDe相关语法 ####

在Hive的建表语句中，和SerDe相关的语法为：

![702338efd536336324f683cdcffc4989.png][]

其中ROW FORMAT是语法关键字，DELIMITED和SERDE二选其一。

如果使用delimited表示使用默认的LazySimpleSerDe类来处理数据。如果数据文件格式比较特殊可以使用ROW FORMAT SERDE serde\_name指定其他的Serde类来处理数据,甚至支持用户自定义SerDe类。

#### LazySimpleSerDe分隔符指定 ####

LazySimpleSerDe是Hive默认的序列化类，包含4种子语法，分别用于指定**字段之间**、**集合元素之间**、**map映射 kv之间**、**换行**的分隔符号。在建表的时候可以根据数据的特点灵活搭配使用。

![43224a27cb12b9224caa2b562de50380.png][]

#### 默认分隔符 ####

hive建表时如果没有row format语法。此时**字段之间默认的分割符是'\\001'**，是一种特殊的字符，使用的是ascii编码的值，键盘是打不出来的。

![9da42e4914f3bb1e40b07968fa6c9af9.png][]

在vim编辑器中，连续按下Ctrl+v/Ctrl+a即可输入'\\001' ，显示**^A**

![ceb4b2d614d32bad6c62b0103e2c3d1e.png][]

在一些文本编辑器中将以SOH的形式显示：

![080a41f37ea1b311cbba0a62c609e21c.png][]

#### **Hive数据存储路径** ####

#### 默认存储路径 ####

Hive表默认存储路径是由$\{HIVE\_HOME\}/conf/hive-site.xml配置文件的hive.metastore.warehouse.dir属性指定。默认值是：/user/hive/warehouse。

![9071039fbd414f9e3b48d5c51444f051.png][]

在该路径下，文件将根据所属的库、表，有规律的存储在对应的文件夹下。

![21a7b96c8a699f84f2b9205a7cea9329.png][]

#### 指定存储路径 ####

在Hive建表的时候，可以通过**location语法来更改数据在HDFS上的存储路径**，使得建表加载数据更加灵活方便。

语法：LOCATION '<hdfs\_location>'。

对于已经生成好的数据文件，使用location指定路径将会很方便。

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

#### **案例—王者荣耀** ####

#### 原生数据类型案例 ####

文件archer.txt中记录了手游《王者荣耀》射手的相关信息，内容如下所示，其中字段之间分隔符为制表符\\t,要求在Hive中建表映射成功该文件。

1	后羿	5986	1784	396	336	remotely	archer
    2	马可波罗	5584	200	362	344	remotely	archer
    3	鲁班七号	5989	1756	400	323	remotely	archer
    4	李元芳	5725	1770	396	340	remotely	archer
    5	孙尚香	6014	1756	411	346	remotely	archer
    6	黄忠	5898	1784	403	319	remotely	archer
    7	狄仁杰	5710	1770	376	338	remotely	archer
    8	虞姬	5669	1770	407	329	remotely	archer
    9	成吉思汗	5799	1742	394	329	remotely	archer
    10	百里守约	5611	1784	410	329	remotely	archer	assassin

字段含义：id、name（英雄名称）、hp\_max（最大生命）、mp\_max（最大法力）、attack\_max（最高物攻）、defense\_max（最大物防）、attack\_range（攻击范围）、role\_main（主要定位）、role\_assist（次要定位）。

分析一下：字段都是基本类型，字段的顺序需要注意一下。字段之间的分隔符是制表符，需要使用row format语法进行指定。

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

**建表语句**：

--创建数据库并切换使用
    create database itcast;
    use itcast;
    
    --ddl create table
    create table t_archer(
        id int comment "ID",
        name string comment "英雄名称",
        hp_max int comment "最大生命",
        mp_max int comment "最大法力",
        attack_max int comment "最高物攻",
        defense_max int comment "最大物防",
        attack_range string comment "攻击范围",
        role_main string comment "主要定位",
        role_assist string comment "次要定位"
    ) comment "王者荣耀射手信息"
    row format delimited fields terminated by "\t";

建表成功之后，在Hive的默认存储路径下就生成了表对应的文件夹，把archer.txt文件上传到对应的表文件夹下。

<table> 
 <tbody> 
  <tr> 
   <th>hadoop fs -put archer.txt /user/hive/warehouse/honor_of_kings.db/t_archer</th> 
  </tr> 
 </tbody> 
</table>

执行查询操作，可以看出数据已经映射成功。

![457b418d51e7333303c6b3ea9026f9f2.png][]

想一想：Hive这种能力是不是比mysql一条一条insert插入数据方便多了？

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

#### 复杂数据类型案例 ####

文件hot\_hero\_skin\_price.txt中记录了手游《王者荣耀》热门英雄的相关皮肤价格信息，内容如下,要求在Hive中建表映射成功该文件。

1,孙悟空,53,西部大镖客:288-大圣娶亲:888-全息碎片:0-至尊宝:888-地狱火:1688
    2,鲁班七号,54,木偶奇遇记:288-福禄兄弟:288-黑桃队长:60-电玩小子:2288-星空梦想:0
    3,后裔,53,精灵王:288-阿尔法小队:588-辉光之辰:888-黄金射手座:1688-如梦令:1314
    4,铠,52,龙域领主:288-曙光守护者:1776
    5,韩信,52,飞衡:1788-逐梦之影:888-白龙吟:1188-教廷特使:0-街头霸王:888

字段：id、name（英雄名称）、win\_rate（胜率）、skin\_price（皮肤及价格）

分析一下：前3个字段原生数据类型、最后一个字段复杂类型map。需要指定字段之间分隔符、集合元素之间分隔符、map kv之间分隔符。

**建表语句**：

create table t_hot_hero_skin_price(
        id int,
        name string,
        win_rate int,
        skin_price map<string,int>
    )
    row format delimited
    fields terminated by ','
    collection items terminated by '-'
    map keys terminated by ':' ;

建表成功后，把hot\_hero\_skin\_price.txt文件上传到对应的表文件夹下。

hadoop fs -put hot_hero_skin_price.txt /user/hive/warehouse/honor_of_kings.db/t_hot_hero_skin_price

执行查询操作，可以看出数据已经映射成功。

![18350bf2387b1a8e21e97b6e11163472.png][]

想一想：如果最后一个字段以String类型来定义，后续使用方便吗？

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

#### 默认分隔符案例 ####

文件team\_ace\_player.txt中记录了手游《王者荣耀》主要战队内最受欢迎的王牌选手信息，内容如下,要求在Hive中建表映射成功该文件。

![f50d18f3c979ed83da1a58c9e7f3a1fa.png][]

字段：id、team\_name（战队名称）、ace\_player\_name（王牌选手名字）

分析一下：数据都是原生数据类型，且字段之间分隔符是\\001，因此在建表的时候可以省去row format语句，因为hive默认的分隔符就是\\001。

**建表语句**：

create table t_team_ace_player(
        id int,
        team_name string,
        ace_player_name string
    );

建表成功后，把team\_ace\_player.txt文件上传到对应的表文件夹下。

hadoop fs -put team_ace_player.txt /user/hive/warehouse/honor_of_kings.db/t_team_ace_player

执行查询操作，可以看出数据已经映射成功。

![49fe56b8671b3f954454dbd20312dea5.png][]

[572ccc6d3c04cedfc7458f30a82ff1ae.png]: https://img-blog.csdnimg.cn/img_convert/572ccc6d3c04cedfc7458f30a82ff1ae.png
[b46971764b7581dce7200bc9439f0217.png]: https://img-blog.csdnimg.cn/img_convert/b46971764b7581dce7200bc9439f0217.png
[f1e669e09f80d9e1bd9a98f162e75726.png]: https://img-blog.csdnimg.cn/img_convert/f1e669e09f80d9e1bd9a98f162e75726.png
[4f38607730e47d9f4db83e6537d3a8d0.png]: https://img-blog.csdnimg.cn/img_convert/4f38607730e47d9f4db83e6537d3a8d0.png
[https_cwiki.apache.org_confluence_display_Hive_LanguageManual_Types]: https://link.zhihu.com/?target=https%3A//cwiki.apache.org/confluence/display/Hive/LanguageManual%2BTypes
[61fe78dd6369a2cd0f9ec2b8ecc409fb.png]: https://img-blog.csdnimg.cn/img_convert/61fe78dd6369a2cd0f9ec2b8ecc409fb.png
[6f4cc42ab57887ac7a232712b3e87303.png]: https://img-blog.csdnimg.cn/img_convert/6f4cc42ab57887ac7a232712b3e87303.png
[a1ffde939be652679dc1c668c09df47f.png]: https://img-blog.csdnimg.cn/img_convert/a1ffde939be652679dc1c668c09df47f.png
[4099e47a291f5e4dcac33c880d026e04.png]: https://img-blog.csdnimg.cn/img_convert/4099e47a291f5e4dcac33c880d026e04.png
[9ab588955e797bea505fee6f8b47159f.png]: https://img-blog.csdnimg.cn/img_convert/9ab588955e797bea505fee6f8b47159f.png
[702338efd536336324f683cdcffc4989.png]: https://img-blog.csdnimg.cn/img_convert/702338efd536336324f683cdcffc4989.png
[43224a27cb12b9224caa2b562de50380.png]: https://img-blog.csdnimg.cn/img_convert/43224a27cb12b9224caa2b562de50380.png
[9da42e4914f3bb1e40b07968fa6c9af9.png]: https://img-blog.csdnimg.cn/img_convert/9da42e4914f3bb1e40b07968fa6c9af9.png
[ceb4b2d614d32bad6c62b0103e2c3d1e.png]: https://img-blog.csdnimg.cn/img_convert/ceb4b2d614d32bad6c62b0103e2c3d1e.png
[080a41f37ea1b311cbba0a62c609e21c.png]: https://img-blog.csdnimg.cn/img_convert/080a41f37ea1b311cbba0a62c609e21c.png
[9071039fbd414f9e3b48d5c51444f051.png]: https://img-blog.csdnimg.cn/img_convert/9071039fbd414f9e3b48d5c51444f051.png
[21a7b96c8a699f84f2b9205a7cea9329.png]: https://img-blog.csdnimg.cn/img_convert/21a7b96c8a699f84f2b9205a7cea9329.png
[457b418d51e7333303c6b3ea9026f9f2.png]: https://img-blog.csdnimg.cn/img_convert/457b418d51e7333303c6b3ea9026f9f2.png
[18350bf2387b1a8e21e97b6e11163472.png]: https://img-blog.csdnimg.cn/img_convert/18350bf2387b1a8e21e97b6e11163472.png
[f50d18f3c979ed83da1a58c9e7f3a1fa.png]: https://img-blog.csdnimg.cn/img_convert/f50d18f3c979ed83da1a58c9e7f3a1fa.png
[49fe56b8671b3f954454dbd20312dea5.png]: https://img-blog.csdnimg.cn/img_convert/49fe56b8671b3f954454dbd20312dea5.png