大数据Hive数据定义语言DDL
目录
1 数据定义语言（DDL）概述
1.1 DDL语法的作用
数据定义语言 (Data Definition Language, DDL)，是SQL语言集中对数据库内部的对象结构进行创建，删除，修改等的操作语言，这些数据库对象包括database（schema）、table、view、index等。核心语法由CREATE、ALTER与DROP三个所组成。DDL并不涉及表内部数据的操作。
在某些上下文中，该术语也称为数据描述语言，因为它描述了数据库表中的字段和记录。
1.2 Hive中DDL使用
Hive SQL（HQL）与SQL的语法大同小异，基本上是相通的，学过SQL的使用者可以无痛使用Hive SQL。只不过在学习HQL语法的时候，特别要注意Hive自己特有的语法知识点，比如partition相关的DDL操作。
基于Hive的设计、使用特点，HQL中create语法（尤其create table）将是学习掌握DDL语法的重中之重。可以说建表是否成功直接影响数据文件是否映射成功，进而影响后续是否可以基于SQL分析数据。通俗点说，没有表，表没有数据，你分析什么呢？
选择正确的方向,往往比盲目努力重要。
2 Hive DDL建表基础
2.1 完整建表语法树
➢ 蓝色字体是建表语法的关键字，用于指定某些功能。
➢ []中括号的语法表示可选。
➢ | |表示使用的时候，左右语法二选一。
➢ 建表语句中的语法顺序要和上述语法规则保持一致。
3 Hive数据类型详解
3.1 整体概述
Hive中的数据类型指的是Hive表中的列字段类型。Hive数据类型整体分为两个类别：原生数据类型（primitive data type）和复杂数据类型（complex data type）。
原生数据类型包括：数值类型、时间类型、字符串类型、杂项数据类型；
复杂数据类型包括：array数组、map映射、struct结构、union联合体。
关于 Hive 的数据类型，需要注意：
➢ 英文字母大小写不敏感；
➢ 除 SQL 数据类型外，还支持 Java 数据类型，比如：string；
➢ int 和 string 是使用最多的，大多数函数都支持；
➢ 复杂数据类型的使用通常需要和分隔符指定语法配合使用。
➢ 如果定义的数据类型和文件不一致，hive 会尝试隐式转换，但是不保证成功。
3.2 原生数据类型
Hive 支持的原生数据类型如下图所示：
其中标注的数据类型是使用较多的，详细的描述请查询语法手册：
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types
3.3 复杂数据类型
Hive支持的复杂数据类型如下图所示：
其中标注的数据类型是使用较多的，详细的描述请查询语法手册：
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types
3.4 数据类型隐式、显示转换
与SQL类似，HQL支持隐式和显式类型转换。
原生类型从窄类型到宽类型的转换称为隐式转换，反之，则不允许。
下表描述了类型之间允许的隐式转换：
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types
显式类型转换使用CAST函数。
例如，CAST（'100’as INT）会将100字符串转换为100整数值。 如果强制转换失败，例如CAST（'INT’as INT），该函数返回NULL。
4 Hive读写文件机制
4.1 SerDe是什么
SerDe是Serializer、Deserializer的简称，目的是用于序列化和反序列化。序列化是对象转化为字节码的过程；而反序列化是字节码转换为对象的过程。
Hive使用SerDe（和FileFormat）读取和写入行对象。
需要注意的是，“key”部分在读取时会被忽略，而在写入时key始终是常数。基本上行对象存储在“value”中。
可以通过desc formatted tablename查看表的相关SerDe信息。默认如下：
4.2 Hive读写文件流程
Hive读取文件机制：首先调用InputFormat（默认TextInputFormat），返回一条一条kv键值对记录（默认是一行对应一条记录）。然后调用SerDe（默认LazySimpleSerDe）的Deserializer，将一条记录中的value根据分隔符切分为各个字段。
Hive写文件机制：将Row写入文件时，首先调用SerDe（默认LazySimpleSerDe）的Serializer将对象转换成字节序列，然后调用OutputFormat将数据写入HDFS文件中。
4.3 SerDe相关语法
在Hive的建表语句中，和SerDe相关的语法为：
其中ROW FORMAT是语法关键字，DELIMITED和SERDE二选其一。
如果使用delimited表示使用默认的LazySimpleSerDe类来处理数据。如果数据文件格式比较特殊可以使用ROW FORMAT SERDE serde_name指定其他的Serde类来处理数据,甚至支持用户自定义SerDe类。
4.4 LazySimpleSerDe分隔符指定
LazySimpleSerDe是Hive默认的序列化类，包含4种子语法，分别用于指定字段之间、集合元素之间、map映射 kv之间、换行的分隔符号。在建表的时候可以根据数据的特点灵活搭配使用。
4.5 默认分隔符
hive建表时如果没有row format语法。此时字段之间默认的分割符是’\001’，是一种特殊的字符，使用的是ascii编码的值，键盘是打不出来的。
在vim编辑器中，连续按下Ctrl+v/Ctrl+a即可输入’\001’ ，显示^A
在一些文本编辑器中将以SOH的形式显示：
5 Hive数据存储路径
5.1 默认存储路径
Hive表默认存储路径是由${HIVE_HOME}/conf/hive-site.xml配置文件的hive.metastore.warehouse.dir属性指定。默认值是：/user/hive/warehouse。
在该路径下，文件将根据所属的库、表，有规律的存储在对应的文件夹下。
5.2 指定存储路径
在Hive建表的时候，可以通过location语法来更改数据在HDFS上的存储路径，使得建表加载数据更加灵活方便。
语法：LOCATION ‘<hdfs_location>’。
对于已经生成好的数据文件，使用location指定路径将会很方便。
6 案例—王者荣耀
6.1 原生数据类型案例
文件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 oldlu;
use oldlu;
--ddl create table
create table oldlu.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";
如果创建没有成功此处要新建文件夹 不然会找不到文件夹报错
hadoop fs -mkdir /user/hive/warehouse/oldlu.db/t_archer
comment:字段描述消息
关键字不能作为字段名,结束后用;
建表成功之后，在Hive的默认存储路径下就生成了表对应的文件夹，把archer.txt文件上传到对应的表文件夹下。
hadoop fs -put archer.txt /user/hive/warehouse/honor_of_kings.db/t_archer
执行查询操作，可以看出数据已经映射成功。
select * from oldlu.t_archer;
想一想：Hive这种能力是不是比mysql一条一条insert插入数据方便多了？
6.2 复杂数据类型案例
文件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 oldlut_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 ':' ;
注意:hive的语法可能impala不支持,如果不支持必须要使用hive进行创建
建表成功后，把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
执行查询操作，可以看出数据已经映射成功。
想一想：如果最后一个字段以String类型来定义，后续使用方便吗？
6.3 默认分隔符案例
文件team_ace_player.txt中记录了手游《王者荣耀》主要战队内最受欢迎的王牌选手信息，内容如下,要求在Hive中建表映射成功该文件。
字段：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
执行查询操作，可以看出数据已经映射成功。
想一想：字段以\001 分隔建表时很方便，那么采集、清洗数据时对数据格式追求
有什么启发？你青睐于什么分隔符？
