【大数据hive】hive ddl语法使用详解

一、前言

DDL，也叫数据定义语言 (Data Definition Language, DDL)，是SQL语言集中对数据库内部的对象结构进行创建，删除，修改等的操作语言，这些数据库对象包括database、table等，使用过mysql的同学应该对此很了解了；

hive中ddl核心操作

• 核心语法由CREATE、ALTER与DROP三个所组成；
• DDL不涉及表内部数据的操作；

二、Hive中DDL语法特点

Hive SQL（HQL）与标准SQL的语法大同小异，基本相通，使用过标准sql的同学上手hive sql时基本无压力，但hive的复杂查询语法相对标准sql来说，细节上又略有不同，后续在实操中可以发现；

HQL中create语法（尤其create table）将是学习掌握Hive DDL语法的重中之重，建表是否成功直接影响数据文件是否映射成功，进而影响后续是否可以基于SQL分析数据

三、Hive中的database

数据库是数据表的基本承载体，现有数据库才会有表，Hive中的数据库有下面的特点

1、默认数据库为default

存储数据位置位于HDFS的/user/hive/warehouse下；

比如我们创建了一个test的数据库，从hdfs的web界面上就能清楚的看到存储位置；

2、用户可自定义数据库

用户自己创建的数据库存储位置是/user/hive/warehouse/database_name.db下

3、创建数据库

hive中创建数据库基本语法如下：

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

参数说明：

• COMMENT：数据库的注释说明语句；
• LOCATION：指定数据库在HDFS存储位置，默认/user/hive/warehouse/dbname.db；
• WITH DBPROPERTIES：用于指定一些数据库的属性配置；

避免要创建的数据库已经存在错误，增加 if not exists 判断，这是一个好的习惯；

创建一个数据库

create database if not exists hero
comment "this is my first db"
with dbproperties ('createdBy'='congge');

注意：如果需要使用location指定路径的时候，最好指向的是一个新创建的空文件夹

切换数据库（使用新的数据库）

use databaseName

删除数据库

完整语法

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

关于删除数据库的几点说明：

• 默认行为是RESTRICT，这意味着仅在数据库为空时才删除它；
• 要删除带有表的数据库（不为空的数据库），我们可以使用CASCADE；

查看数据库详情

desc database databaseName;

显示数据库详细信息

使用extended

desc database extended test;

修改数据库

用户可以使用 ALTER DATABASE 命令为某个数据库的 DBPROPERTIES 设置键-值对属性值， 来描述这个数据库的属性信息。

三、Hive中的table

数据表是hive用来做数据分析的基本模型，因此有必要深入掌握hive的创建表语法相关的知识；

建表基本语法

CREATE TABLE [IF NOT EXISTS] [db_name.]table_name
(col_name data_type [COMMENT col_comment], ... )
[COMMENT table_comment]
[ROW FORMAT DELIMITED …];

完整的建表语法树

    //HIVE DDL CREATE TABLE
    create[temporary][external]table[if not exists][db_name.]table_name
    [(col_name data_type[comment col_conmment],...)]
    [comment table_comment]
    [partitioned by(col_name col_type[comment col_comment],...)]
    [clustered by(col_name col_type,...)[sorted by (col_name[ASC|DESC],...)]into num_buckets buckets]
    [row format delimited|serde serde_name with serdeproperties (property_name=property_value,...)]
    [stored as file_foramt]
    [location hdfs_path]
    [tblproperties(property_name=property_value,...)];

关于语法树中的关键参数说明

1、CREATE TABLE 创建一个指定名字的表，如果库中已有相同名的表，则抛出异常；用户可以使用 IF NOT EXISTS 选项来忽略此异常；

2、EXTERNAL 关键字可以让用户创建一个外部表（默认创建内部表）。外部表在建表的同时必须指定一个指向实际数据的路径（LOCATION），Hive在创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

3、COMMENT 是给表字段或者表内容添加注释说明的；

4、PARTITIONED BY 给表做分区，决定了表是否为分区表；

5、CLUSTERED BY 对于每一个表（table）或者分区， Hive 可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分，Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中；

6、ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’， 这里指定表存储中列的分隔符，默认是 \001，这里指定的是逗号分隔符，还可以指定其他列的分隔符；

7、STORED AS SEQUENCEFILE|TEXTFILE|RCFILE，如果文件数据是纯文本，可以使用 STORED AS TEXTFILE，如果数据需要压缩，使用 STORED AS SEQUENCEFILE；

8、LOCATION 定义 hive 表的数据在 hdfs 上的存储路径，一般管理表（内部表不不要自定义），但是如果定义的是外部表，则需要直接指定一个路径；

注意事项补充说明

• 语法中的关键参数（关键字），用于指定某些功能；
• [ ]中括号的语法表示可选；
• 建表语句中的语法顺序要和语法树中顺序保持一致；
• 最低限度必须包括的语法如下

CREATETABLE[IF NOT EXISTS] [db_name.]table_name (col_name data_type [COMMENT col_comment], ... )
[COMMENT table_comment]
[ROW FORMAT DELIMITED …];

ROW FORMAT 说明

ROW FORMAT这一行所代表的是跟读写文件、序列化SerDe相关的语法，功能有二：

• 使用哪个SerDe类进行序列化；
• 如何指定分隔符；

ROW FORMAT可以说是建表语句中非常重要的一个语法，尤其是分隔符的指定是否合适对后续的数据分析将会带来重要的影响，而SerDe说的是与hive文件读写机制相关的，顾名思义就是序列化与反序列化；

hive文件读写机制说明

hive文件读流程

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

Hive写文件机制

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

SerDe相关语法

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

LazySimpleSerDe 分隔符指定

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

Hive默认分隔符

Hive建表时如果没有row format语法指定分隔符，则采用默认分隔符；

默认的分割符是'\001'，是一种特殊的字符，使用的是ASCII编码的值，键盘是打不出来的

Hive数据存储路径说明

在上面的建表语句中，注意到最后有一个LOCATION的关键字，这就是用于指定数据表的存放路径的；

hive默认数据存储路径

我们知道，hive本身并不存储数据，数据存放在hdfs上面，所以默认情况下，Hive表默认存储路径是由${HIVE_HOME}/conf/hive-site.xml配置文件的hive.metastore.warehouse.dir属性指定，默认值是：/user/hive/warehouse；

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

如下在上面创建了一个test的数据库，hdfs上面的位置如下：

指定存储路径

Hive建表时，也可以通过location语法来更改数据在HDFS上的存储路径，使得建表加载数据更加灵活方便，在这种情况下，对于已经生成好的数据文件，直接使用location指定路径将会很方便的将数据加载到hive的数据表中；

语法：LOCATION '<hdfs_location>'

四、Hive中的数据类型

Hive数据类型指的是表中列的字段类型，整体分为两类：

• 原生数据类型（primitive data type）；
• 复杂数据类型（complex data type）；

最常用的数据类型是字符串String和数字类型Int

原生数据类型

数值类型，时间日期类型，字符串类型，杂项数据类型；

复杂数据类型

array数组、map映射、struct结构、union联合体

hive中的数据类型补充说明

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

hive中数据类型的隐式转换

我们知道对某些类型的数据库来说，如果插入的数据类型与定义的类型不一致将无法成功写入数据，但hive中，在某些情况下对特定的数据类型存在隐式转换，其特点如下：

• 与标准SQL类似，HQL支持隐式和显式类型转换；
• 原生类型从窄类型到宽类型的转换称为隐式转换，反之，则不允许；

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

hive中数据类型的隐式转换

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

五、Hive建表实操

建表练习之 —— 简单数据类型的操作

数据准备

如下为即将导入数据目录的原始数据文件（游戏中的英雄人物），其中字段之间分隔符为制表符\t,要求在Hive中建表映射成功该文件，字段文件的含义分别是：id、name（英雄名称）、hp_max（最大生命）、mp_max（最大法力）、attack_max（最高物攻）、defense_max（最大物防）、attack_range（攻击范围）、role_main（主要定位）、role_assist（次要定位）；

分析：

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

数据建表语句

drop table t_hero;
--ddl create table
create table t_hero(
      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";

运行上面的sql建表语句

hdfs上面可以看到已成功创建了

上传数据文件到hdfs的表目录下

使用下面的命令进行文件上传

hdfs dfs -put ./hero.txt /user/hive/warehouse/test.db/t_hero

上传完毕后，在hdfs目录下可以看到上面的这个文件，查看hero表，检查数据是否映射到表中

通过这种方式就完成了一个hive建表之后，通过映射文件的方式将数据导到hive表的过程，也是日常开发运维中比较常用的一种方式；

建表练习之 —— 复杂类型数据操作

数据准备

在当前目录下，创建一个数据文件，内容如下，文件中的内容记录了某手游热门英雄的相关皮肤价格信息；

分析这段内容，可以从得出下面几点：

• 字段：id、name（英雄名称）、win_rate（胜率）、skin_price（皮肤及价格）；
• 前3个字段原生数据类型、最后一个字段复杂类型map；
• 需要指定字段之间分隔符、集合元素之间分隔符、map kv之间分隔符；

建表sql

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 ':';   -- 集合元素之间的分隔符

执行sql创建表

上传数据文件到数据表目录进行数据映射

执行下面命令进行hdfs文件上传

hdfs dfs -put ./hot_hero_skin_price.txt /user/hive/warehouse/test.db/t_hot_hero_skin_price

上传成功后再次查看数据表，就能看到映射的数据了；

建表练习之 —— 默认分隔符使用

还记得在上文中提到的,hive中的默认分隔符吗？hive中默认的分割符是'\001'，是一种特殊的字符，使用的是ASCII编码的值，键盘是打不出来的；

数据准备

有如下数据文件，每行数据不同字段之间以默认分隔符分开；

分析这段内容，得出下面几点

• 字段：id、team_name（战队名称）、ace_player_name（选手名字）；
• 数据都是原生数据类型，且字段之间分隔符是\001，因此在建表的时候可以省去row format语句，因为hive默认的分隔符就是\001；

建表sql

由于是默认分隔符，就可以省去row_format；

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

执行sql创建表

上传数据文件到hdfs的表目录下

使用下面的命令进行文件上传

hdfs dfs -put ./team_ace_player.txt /user/hive/warehouse/test.db/t_team_ace_player

上传完毕后，在hdfs目录下可以看到上面的这个文件，查看hero表，检查数据是否映射到表中

建表练习之 —— 指定数据存储路径

在上面的建表练习中，我们并没有在建表语句中指定LOCATION的信息，而是最后通过将数据文件上传到数据表的hdfs文件目录下映射的，其实在某些情况下，这种方式操作起来看起来有点麻烦，于是就可以通过指定LOCATION的方式，就能完成任意目录下的文件映射到hive中的目的；

hsfs创建数据目录

在hdfs的根目录下，创建一个名叫data的目录

hdfs dfs -mkdir /data

上传数据文件到自建目录下

仍然使用上面的那个数据文件进行上传；

hdfs dfs -put ./team_ace_player.txt /data

建表sql（注意指定LOCATION）

create table t_team_ace_player_location(
 id int,
 team_name string,
 ace_player_name string)
 location '/data';

执行sql的创建；

执行完成之后查看一下表的数据，可以发现已经映射成功了；