SQL中DDL语法的作用

Hive 完整DDL建表语法

Hive 数据类型

原生数据类型

复杂数据类型

注意事项

显式类型转换使用CAST函数。
例如，CAST(‘100’ as INT)会将100字符串转换为100整数值。
如果强制转换失败，例如CAST('Allen’as INT)，该函数返回NULL

Hive读写文件机制

SerDe是Serializer、Deserializer的简称，目的是用于序列化和反序列化。
序列化是对象转化为字节码的过程；而反序列化是字节码转换为对象的过程。
Hive使用SerDe(包括FileFormat)读取和写入表行对象。需要注意的是，“key”部分在读取时会被忽略，而在写入时key始终是常数。基本上行对象存储在“value”中。
可以通过desc formatted tablename查看表的相关SerDe信息。默认如下

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

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

ROW FORMAT这一行所代表的是跟读写文件、序列化SerDe相关的语法，功能有二：
使用哪个SerDe类进行序列化？
如何指定分隔符？

其中ROW FORMAT是语法关键字，DELIMITED和SERDE二选其一。
如果使用delimited表示使用默认的LazySimpleSerDe类来处理数据。
如果数据文件格式比较特殊可以使用ROW FORMAT SERDE serde_name指定其他的Serde类来处理数据，甚至支持用户自定义SerDe类。

LazySimpleSerDe是Hive默认的序列化类，包含4种子语法，分别用于指定字段之间、集合元素之间、map映射 kv之间、换行的分隔符号。

在建表的时候可以根据数据的特点灵活搭配使用。

Hive建表时如果没有row format语法指定分隔符，则采用默认分隔符；
默认的分割符是’\001’，是一种特殊的字符，使用的是ASCII编码的值，键盘是打不出来的。
Hive数据存储路径
默认路径
Hive表默认存储路径是由$ {HIVE_HOME}/conf/hive-site.xml配置文件的hive.metastore.warehouse.dir属性指定，默认值是:/user/hive/warehouse。

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

指定存储路径

练习

原生数据类型使用

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

建表HQL：

create database if not exists db_test;
use db_test;

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";

[root@node1 hivedata]# hadoop fs -put archer.txt /user/hive/warehouse/db_test.db/t_archer

结果验证：

想一想：Hive这种直接映射文件的能力是不是比mysql一条一条insert插入数据方便多了?

复杂数据类型使用

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

建表HQL：

create table t_hot_hero_skin_price(
id int,
name string comment "英雄名称",
    win_price int comment "胜率",
    skin_price mapcomment "皮肤价格"  -- map复杂类型
) row format delimited
fields terminated by ','    -- 指定字段之间分隔符
collection items terminated by '-'      -- 指定集合元素之间的分隔符
map keys terminated by ':';     -- 指定map元素kv之间的分隔符,int>

[root@node1 hivedata]# hadoop fs -put hot_hero_skin_price.txt /user/hive/warehouse/db_test.db/t_hot_hero_skin_price

# 删除/user/hive/warehouse/db_test.db/t_hot_hero_skin_price下的所有文件
hadoop fs -rm -r /user/hive/warehouse/db_test.db/t_hot_hero_skin_price/*

1成都AG超玩会一诺
2重庆QGhappyHurt
3DYG久诚
4上海EDG.M浪浪
5武汉eStarProCat
6RNG.M暴风锐
7RW侠渡劫
8TES滔搏迷神
9杭州LGD大鹅伪装
10南京Hero久竞清融

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

建表HQL：

create table t_team_ace_player(
id int,
    team_name string,
    ace_player_name string
); -- 没有指定row format语句 此时采用的是默认的\001作为字段的分隔符

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

[root@node1 hivedata]# hadoop fs -put team_ace_player.txt /user/hive/warehouse/db_test.db/t_team_ace_player

结果认证：

应优先考虑\001分隔符

指定数据存储路径

文件team_ace_player.txt中记录了手游《王者荣耀》主要战队内最受欢迎的王牌选手信息，字段之间使用的是\001作为分隔符。

要求把文件上传到HDFS任意路径下，不能移动复制，并在Hive中建表映射成功该文件。

[root@node1 hivedata]# hadoop fs -mkdir /tmp_data
[root@node1 hivedata]# hadoop fs -put team_ace_player.txt /tmp_data

建表HQL：

create table t_team_ace_player_location(
id int,
    team_name string,
    ace_player_name string
) location '/tmp_data'; -- 使用location关键字指定本张表数据在hdfs上的存储路径

HDFS的Web UI页面查看：

结果验证：

为方便集中管理，建议还是放到/user/hive/warehouse/根目录下

————————————————

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

原文链接：https://blog.csdn.net/qq_45917176/article/details/143082051