Hive SQL DDL语法 - 指定分隔符、指定存储路径

Hive SQL DDL建表语法树

• “[]”中的语法可选
• “|”表示使用时，语法需要二选一
• 建表语句中语法顺序要和语法树规则保持一直
• 整体分为原生数据类型和复杂数据类型
• 原生数据类型包括：数值类型、字符串类型、时间日期类型、杂项数据类型。
• 复杂数据类型包括：array数组、map映射、struct结构、union联合体。

CREATE [TEMPORARY][EXTERNAL] TABLE [IF NOT EXISTS][db_name.]table_name
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type[COMMENT col_comment],...)]
[CLUSTERED BY (col_name,col_name,...)[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_format]
[LOCATION hdfs_path]
[TBLPROPERTIES(property_name=property_value,...)];

SerDe是什么

• 是Serializer、Deserializer的简称
• 目的是序列化和反序列化
• 序列化时对象转化为字节码的过程，反序列化时字节码转换为对象的过程
• Hive使用SerDe读取和写入表行对象

Hive读写文件流程

• 读文件机制：
  首先调用InputFormat，返回一条一条kv键值对记录(默认是一行对应一条键值对)。然后调用SerDe的Deserializer，将一条记录中的value根据分隔符切分为各个字段。
• 写文件机制：
  将行写入文件时，首先调用SerDe的Serializer将对象转换成字节序列，然后调用OutputFormat将数据写入HDFS文件中。

SerDe相关语法

ROW FORMAT DELIMITED|SERDE

• DELIMITED表示使用默认的LazySimpleSerDe类来处理数据
• 可以使用ROW FORMAT SERDE serde_name指定其他SerDe类来处理数据，甚至支持用户自定义类

LazySimpleSerDe指定分隔符

• LazySimpleSerDe包含四种子语法，用于指定字段之间、集合元素之间、map映射kv之间、换行的分隔符号。

ROW FORMAT DELIMITED
			[FIELDS TERMINATED BY char]           #字符之间分隔符
			[COLLECTION ITEMS TERMINATED BY char] #集合元素之间分隔符
			[MAP KEYS TERMINATED BY char]         #Map映射kv之间分隔符
			[LINES TERMINATED BY char]            #行数据之间分隔符

SERDE处理数据(如json)

ROW FORMAT SERDE ser_name
	[WITH SERDEPROPERTIES
		(property_name=property_value,
		 property_name=proberty_value,..)]

Hive默认分隔符

• Hive建表时若没有row format指定分隔符，则采用默认分隔符；
• 默认分隔符是\001使用的是ASCLL编码的值。

指定存储路径

• Hive建表时，可以通过location语法更改数据存储路径。
• 对于已经生成好的数据文件，使用location指定路径会很方便。

LOCATION '<hdfs_location>'

案例

使用原生数据类型

• 字段含义：id、name（英雄名称）、hp_max（最大生命）、attack_max（最高物攻）、defense_max（最大物防）、attack_range（攻击范围）、role_main（主要定位）、role_assist（次要定位）。
• 字段之间分隔符为制表符。

--创建数据库并切换使用
CREATE DATABASE IF NOT EXISTS pljnb;
USE pljnb;
-- 创建表
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";

使用复杂数据类型

• 字段：id、name（英雄名称）、win_rate（胜率）、skin_price（皮肤及价格）；
• 需要指定字段之间分隔符、集合元素之间分隔符、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 ':'; --集合元素kv之间分隔符

使用默认分隔符

• 字段：id、team_name（战队名称）、ace_player_name（王牌选手名字）
• 数据都是原生数据类型，且字段之间分隔符是\001，则建表时可以不指定分隔符。

CREATE TABLE t_team_ace_player(
	id INT,
	team_name STRING,
	ace_player_name STRING
);

指定数据存储路径

• 使用location关键字指定路径表在hdfs上的存储路径

CREATE TABLE t_team_ace_player_location(
	id INT,
	team_name STRING,
	ace_player_name STRING
)
LOCATION '/data'; --使用location关键字指定路径表在hdfs上的存储路径