Hive SQL DDL建表语法

1. 完整语法树

	//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,...)];

2. Hive数据类型

Hive数据类型指的是表中列的字段类型，整体分为两类：原生数据类型(primitive data type)和复杂数据类型(compliex data type)。原生数据类型包括：数值类型、时间日期类型、字符串类型、杂项数据类型；复杂数据类型包括：array数组，map映射，struct结构、union联合体。


注意事项：

1. Hive SQL中，数据类型英文字母大小写不敏感；
2. 除SQL数据类型外，还支持Java数据类型，比如字符串String；
3. 复杂数据类型的使用通常需要和分隔符指定语法配合使用；
4. 如果定义的数据类型和文件不一致，Hive会尝试隐式转换，但是不保证成功。
5. HQL支持隐式和显示转换，原生类型从窄类型到宽类型称为隐式转换，反之则不允许。
6. 显示转换使用CAST函数。CAST('100' as INT)会将100字符串转换为100整数值。

3. ROW FORMAT

3.1 SerDe

• SerDe是Serializer、Deserializer的简称，即序列化和反序列化。
• 序列化是将对象转换为字节码的过程，反序列化是字节码转换为对象的过程。
• Hive使用SerDe（包括FileFormat）读取和写入表行对象，需要注意的是，key部分在读取时会被忽略，而且写入时key始终是常数。基本上行对象存储在value中。

3.2 Hive读写文件流程

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

3.3 相关语法

• ROW FOEMAT是语法关键字，delimited和serde二选一。
• 使用delimited表示使用默认的LazySimpleSerDe类来处理数据。
• 如果数据文件格式比较特殊，可以使用[ROW FORAMT SERDE serde_name]指定其他的Serde类来处理数据，支持用户自定义SerDe。
• 建表时如果没有row format语法指定分隔符，则采用默认分隔符’\001’，使用ASCII二进制编码值为：0000 0001。
  

4. LOCATION

Hive的默认存储路径是${Hive_HOME}/conf/hive-site.xml配置文件的hive.metastore.dir属性指定，默认值是/usr/hive/warehouse。建表时使用LOCATION语法来更改数据在HDFS上的路径：LOCATION '<hdfs_locaiton>'。

5. 案例

5.1 原生数据类型使用

//学生表
	create table student(
		id int comment"ID",
		name string comment"名字"
)comment "学生表"
row format delimited
	fields terminated by "\t";//以tab键为分割符

5.2 复杂数据类型使用

//学生成绩表
	create table score(
	id int,
	name string,
	mark map<string,int>  --map复杂类型
)
row format delimited //如果没有该语句，则使用默认分隔符'\001'
	fields terminated by ',' --指定字段之间分隔符
	collection items terminated by '-' --指定集合元素之间的分隔符
	map keys terminated by ':' --指定map元素KV之间的分隔符 

5.2 指定存储路径

//教师表
	create table teacher(
		id int,
		name string,		
)
	location '/data'; --使用location关键字在“根目录”下的data里建表，从HiveUI页面可以查看。