大数据Hive数据定义语言DDL

本文涉及的产品
云原生大数据计算服务 MaxCompute,5000CU*H 100GB 3个月
云原生大数据计算服务MaxCompute,500CU*H 100GB 3个月
简介: 大数据Hive数据定义语言DDL

1 数据定义语言(DDL)概述

1.1 DDL语法的作用

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

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


a40de28fa449415cb0d004eeec8c6eb3.png

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 完整建表语法树


4847722eba9549bba8b973fa5cb6c00b.png

➢ 蓝色字体是建表语法的关键字,用于指定某些功能。

➢ []中括号的语法表示可选。

➢ | |表示使用的时候,左右语法二选一。

➢ 建表语句中的语法顺序要和上述语法规则保持一致。


3 Hive数据类型详解

3.1 整体概述

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

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

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

cd5f8d136b784897be6b5659733f466e.png

关于 Hive 的数据类型,需要注意:

➢ 英文字母大小写不敏感;

➢ 除 SQL 数据类型外,还支持 Java 数据类型,比如:string;

➢ int 和 string 是使用最多的,大多数函数都支持;

➢ 复杂数据类型的使用通常需要和分隔符指定语法配合使用。

➢ 如果定义的数据类型和文件不一致,hive 会尝试隐式转换,但是不保证成功。


3.2 原生数据类型

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

56f4aafc5c4c45f099ecf13b1f45d88a.png

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

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types


3.3 复杂数据类型

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

72c9125b03ae4c8d9b0ebcaa49ce114f.png

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

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types


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

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

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

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

ff40886639874b42bdee8f1764ca22bc.png

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types

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

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

b7fd6682959c447482e738644554f3d3.png


4 Hive读写文件机制

4.1 SerDe是什么

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

Hive使用SerDe(和FileFormat)读取和写入行对象。

4b06cc1cc2304991aa3e3e589c4001fa.png


需要注意的是,“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相关的语法为:


9fd31612fa084492ba80d0e702f62daf.png

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

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


4.4 LazySimpleSerDe分隔符指定

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


9fd31612fa084492ba80d0e702f62daf.png

4.5 默认分隔符

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

049dcfcab69e417891cd44e0c394eb5b.png

在vim编辑器中,连续按下Ctrl+v/Ctrl+a即可输入’\001’ ,显示^A66e09ff93f3c4654b32bf4c5b85da90e.png

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


ab7b1072761d452abcd053f069ff8ec6.png

5 Hive数据存储路径

5.1 默认存储路径

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

838648852b5447ceab59b4665050edee.png

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

20bce88cfb8943fb9fbec6d3e1cc4fa2.png

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:字段描述消息

e81d629d0de5435eb5c2aae93ac37c6a.png


关键字不能作为字段名,结束后用;


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

946b09c85a6443b59f27b7560c999783.png


hadoop fs -put archer.txt  /user/hive/warehouse/honor_of_kings.db/t_archer

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

select * from oldlu.t_archer;


b5f046809c0e4d8b8e561746448bab50.png

想一想: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

执行查询操作,可以看出数据已经映射成功。a3fb974a645a4f6ca8c2f1b8394389e4.png

想一想:如果最后一个字段以String类型来定义,后续使用方便吗?


6.3 默认分隔符案例

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

f4a0223bc2fd4fbfb39c34e807ce443c.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

执行查询操作,可以看出数据已经映射成功。f5f0e4bab69f461bb50f64092456e842.png

想一想:字段以\001 分隔建表时很方便,那么采集、清洗数据时对数据格式追求

有什么启发?你青睐于什么分隔符?


相关实践学习
基于MaxCompute的热门话题分析
本实验围绕社交用户发布的文章做了详尽的分析,通过分析能得到用户群体年龄分布,性别分布,地理位置分布,以及热门话题的热度。
SaaS 模式云数据仓库必修课
本课程由阿里云开发者社区和阿里云大数据团队共同出品,是SaaS模式云原生数据仓库领导者MaxCompute核心课程。本课程由阿里云资深产品和技术专家们从概念到方法,从场景到实践,体系化的将阿里巴巴飞天大数据平台10多年的经过验证的方法与实践深入浅出的讲给开发者们。帮助大数据开发者快速了解并掌握SaaS模式的云原生的数据仓库,助力开发者学习了解先进的技术栈,并能在实际业务中敏捷的进行大数据分析,赋能企业业务。 通过本课程可以了解SaaS模式云原生数据仓库领导者MaxCompute核心功能及典型适用场景,可应用MaxCompute实现数仓搭建,快速进行大数据分析。适合大数据工程师、大数据分析师 大量数据需要处理、存储和管理,需要搭建数据仓库?学它! 没有足够人员和经验来运维大数据平台,不想自建IDC买机器,需要免运维的大数据平台?会SQL就等于会大数据?学它! 想知道大数据用得对不对,想用更少的钱得到持续演进的数仓能力?获得极致弹性的计算资源和更好的性能,以及持续保护数据安全的生产环境?学它! 想要获得灵活的分析能力,快速洞察数据规律特征?想要兼得数据湖的灵活性与数据仓库的成长性?学它! 出品人:阿里云大数据产品及研发团队专家 产品 MaxCompute 官网 https://www.aliyun.com/product/odps&nbsp;
目录
相关文章
|
27天前
|
SQL 分布式计算 Hadoop
Hadoop-14-Hive HQL学习与测试 表连接查询 HDFS数据导入导出等操作 逻辑运算 函数查询 全表查询 WHERE GROUP BY ORDER BY(一)
Hadoop-14-Hive HQL学习与测试 表连接查询 HDFS数据导入导出等操作 逻辑运算 函数查询 全表查询 WHERE GROUP BY ORDER BY(一)
28 4
|
27天前
|
SQL 分布式计算 关系型数据库
Hadoop-21 Sqoop 数据迁移工具 简介与环境配置 云服务器 ETL工具 MySQL与Hive数据互相迁移 导入导出
Hadoop-21 Sqoop 数据迁移工具 简介与环境配置 云服务器 ETL工具 MySQL与Hive数据互相迁移 导入导出
48 3
|
27天前
|
SQL
Hadoop-14-Hive HQL学习与测试 表连接查询 HDFS数据导入导出等操作 逻辑运算 函数查询 全表查询 WHERE GROUP BY ORDER BY(二)
Hadoop-14-Hive HQL学习与测试 表连接查询 HDFS数据导入导出等操作 逻辑运算 函数查询 全表查询 WHERE GROUP BY ORDER BY(二)
31 2
|
26天前
|
SQL 分布式计算 Java
大数据-96 Spark 集群 SparkSQL Scala编写SQL操作SparkSQL的数据源:JSON、CSV、JDBC、Hive
大数据-96 Spark 集群 SparkSQL Scala编写SQL操作SparkSQL的数据源:JSON、CSV、JDBC、Hive
27 0
|
27天前
|
SQL 分布式计算 关系型数据库
Hadoop-24 Sqoop迁移 MySQL到Hive 与 Hive到MySQL SQL生成数据 HDFS集群 Sqoop import jdbc ETL MapReduce
Hadoop-24 Sqoop迁移 MySQL到Hive 与 Hive到MySQL SQL生成数据 HDFS集群 Sqoop import jdbc ETL MapReduce
68 0
|
2月前
|
SQL JavaScript 前端开发
Hive学习-数据定义语句
Hive学习-数据定义语句
37 5
|
3月前
|
SQL 物联网 数据处理
"颠覆传统,Hive SQL与Flink激情碰撞!解锁流批一体数据处理新纪元,让数据决策力瞬间爆表,你准备好了吗?"
【8月更文挑战第9天】数据时代,实时性和准确性至关重要。传统上,批处理与流处理各司其职,但Apache Flink打破了这一界限,尤其Flink与Hive SQL的结合,开创了流批一体的数据处理新时代。这不仅简化了数据处理流程,还极大提升了效率和灵活性。例如,通过Flink SQL,可以轻松实现流数据与批数据的融合分析,无需在两者间切换。这种融合不仅降低了技术门槛,还为企业提供了更强大的数据支持,无论是在金融、电商还是物联网领域,都将发挥巨大作用。
57 6
|
3月前
|
SQL 关系型数据库 HIVE
实时计算 Flink版产品使用问题之如何将PostgreSQL数据实时入库Hive并实现断点续传
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
3月前
|
SQL 分布式计算 数据处理
实时计算 Flink版产品使用问题之怎么将数据从Hive表中读取并写入到另一个Hive表中
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。