Hive 数据类型和存储格式、建表操作

本文部分内容来源《Hadoop海量数据处理 技术详解与项目实战》人民邮电出版社

一、 基本数据类型

数据类型	所占字节	开始支持版本
TINYINT	1byte，-128 ~ 127
SMALLINT	2byte，-32,768 ~ 32,767
INT	4byte,-2,147,483,648 ~ 2,147,483,647
BIGINT	8byte,-9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807
BOOLEAN
FLOAT	4byte单精度
DOUBLE	8byte双精度
STRING
BINARY	Hive0.8.0
TIMESTAMP	Hive0.8.0
DECIMAL	Hive0.11.0
CHAR	Hive0.13.0
VARCHAR	Hive0.12.0
DATE	Hive0.12.0

二、 集合数据类型

数据类型	所占字节	开始支持版本
ARRAY	ARRAY类型是由一系列相同数据类型的元素组成，这些元素可以通过下标来访问。比如有一个ARRAY类型的变量fruits，它是由[‘apple’,’orange’,’mango’]组成，那么我们可以通过fruits[1]来访问元素orange，因为ARRAY类型的下标是从0开始的；
MAP	MAP包含key->value键值对，可以通过key来访问元素。比如”userlist”是一个map类型，其中username是key，password是value；那么我们可以通过userlist[‘username’]来得到这个用户对应的password；
STRUCT	STRUCT可以包含不同数据类型的元素。这些元素可以通过”点语法”的方式来得到所需要的元素，比如user是一个STRUCT类型，那么可以通过user.address得到这个用户的地址。
UNION	UNIONTYPE	Hive 0.7.0开始支持

三、 存储格式

Hive支持的文件存储格式有
- TEXTFILE
- SEQUENCEFILE
- RCFILE
- 自定义格式
在建表的时候，可以使用STORED AS子句指定文件存储的格式。

TEXTFILE
即通常说的文本格式，默认长期，数据不做压缩，磁盘开销大、数据解析开销大。
SEQUENCEFILE
Hadoop提供的一种二进制格式，使用方便、可分割、可压缩，并且按行进行切分
RCFILE
一种行列存储相结合的存储方式，首先，其将数据按行分块，保证同一条记录在一个块上，避免读一条记录多个块。其次，块上的数据按照列式存储，有利于数据压缩和快速地进行列存取。
自定义文件格式
用户通过实现InputFormat和OutputFormat来自定义输入输出格式。

四、 数据格式

当数据存储在文本文件，必须按照一定的格式区分行和列。常见的有CSV（逗号分割值）和TSV（制表符分割值）。但如果数据中经常出现逗号和制表符，那这两种格式就不合适了。
Hive默认使用几个在平时很少出现的字符作分割符。

分割符	描述
\n	换行符，默认行分隔符
^A(Ctrl+A)	文本中以八进制\001表示，列分隔符
^B(Ctrl+B)	在文本中以八进制\002表示，作为分隔Array、Struct中的元素，或者Map中键值对的分隔
^C(Ctrl+C)	在文本中以八进制\003表示，用于MAP中键值对的分隔

也可以用户指定其它分隔符。
建表语句示例

CREATE TABLE student(
 name    STRING
 age     INT
 cource  ARRAY<STRING>
 body    MAP<STRING,FLOAT>
 address STRUCT<STREET:STRING,CITY:STRING,STATE:STRING>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\001'
COLLECTION ITEMS TERMINATED BY '\002'
MAP KEYS TERMINATED BY '\003'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

目前LINES TERMINATED BY ‘\n’ , Hive只支持\n分隔。

五、 HQL

HQL是一种SQL方言，支持绝大部分SQL-92标准，并对其做了一些扩展。

1. Hive中的数据库

Hive中的数据库本质上仅仅是个表的目录或者命名空间。在生产环境中，如果表常多的话，一般会用数据库将生产表组织成逻辑组。
实际情况中，用户不指定数据库，则默认数据库default。可以使用

hive > CREATE DATABASE IF NOT EXISTS test;

创建数据库。

Hive会为每个创建的数据库在HDFS上创建一个目录，表以子目录的形式存储，数据以表目录下的文件的形式存储。如果用户使用default数据库，该数据库本身没有自己的目录，数据库所在的目录在hive-site.xml文件中的配置项 hive.metastore.warehouse.dir配置目录后，默认是/user/hive/warehouse。
如果针对某个数据库改变其存放位置，则

hive> CREATE DATABASE test LOCATION '/user/hadoop/temp';

查看某个已存在的数据库，可以使用：

hive> DESCRIBE DATABASE test;

使用

hive> USE test;

切换数据库。

常用命令

hive> create database test;
hive> show databases;
hive> show databases lie 'f.*';
hive> describe database finacial;
hive> use test;
hive> drop database if exists test;
hive> drop database if exists finacial cascade;
hive> drop database test set dbproperties('created by '='aaaname');

• 查看表

hive> use test;
hive> show tables like 'aaa.*';

2. Hive中的表

USE test;
SHOW TABLES; 
SHOW TABLES IN test;

CREATE TABLE IF NOT EXISTS test.student(
  name STRING COMMENT 'student name',
  age  INT COMMENT 'student age',
  cource  ARRAY<STRING>,
  body MAP<STRING,FLOAT>,
  address STRUCT<STREET:STRING,CITY:STRING,STATE:STRING>)
  COMMENT 'the info of student'
  ROW FORMAT DELIMITED
  FIELDS TERMINATED BY '\001'
  COLLECTION ITEMS TERMINATED BY '\002'
  MAP KEYS TERMINATED BY '\003'
  LINES TERMINATED BY '\n'
  STORED AS TEXTFILE
  LOCATION '/user/hive/warehouse/test.db/student';

查看字段和表注释

DESC student;
DESC EXTENDED student;
DESC FORMATTED student

3. 管理表

Hive中，在建表时，如果没有特别指明的话，都是Hive中所谓的管理表（MANAGED TABLE），也叫托管表，管理表意味着由Hive负责管理表的数据，Hive默认会数据保存到数据仓库目录下。当删除管理表时，Hive将删除管理表的数据和元数据。

4. 外部表

如果一份数据需要被多种工具分析时，如Pig、Hive，数据所有权并不由Hive拥有。这时可以创建一个外部表（External Table）指向这份数据，如下：

CREATE EXTERNAL TABLE IF NOT EXISTS test.student(
  name STRING COMMENT,
  age INT COMMENT,
  cource ARRAY<STRING>,
  body MAP<STRING,FLOAT>,
  address STRUCT<STREET:STRING,CITY:STRING,STATE:STRING>
)
LOCATION '/user/test/x'

其中EXTERNAL指明了该表为外部表，而LOCATION子句指明了数据存放在HDFS的位置。
当需要删除外部表时，Hive只会删除元数据信息而不会删除该表的数据。

5. 分区表

Hive可支持对表进行分区，水平切分将数据按照某种规则进行存储。
首先，创建分区管理表

CREATE TABLE student_info(
  student_ID STRING,
  name STRING,
  age INT,
  sex STRING,
  father_name STRING,
  mother_name STRING)
PARTITIONED BY (province STRING,city STRING);

Partitioned by 子句指名按学生家庭住址的city和province字段进行分区，分区的字段不能和定义表的字段重合。表的分区是以表目录的子目录存在，而表是以目录形式存在。
分区的作用相当于简易的索引，对提升性能作用很大。
可以将hive的安全措施设定为”strict”模式，这样一个针对分区表的查询如果没有对分区进行限制的话，会禁止提交作业。

如果是外部表建分区，建完分区还要执行ALTER TABLE单独为外部表的分区键指定值和存储位置。

6. 其它操作

删除表

DROP TABLE IF EXISTS test

修改表名

ALTER TABLE test RENAME TO test2

增加、修改、删除表分区
增加分区（通常外部表）

ALTER TABLE test ADD PARTITION(x=x1,y=y2) LOCATION '/USER/TEST/X1/Y1'

修改分区

ALTER TABLE test ADD PARTITION(x=x1,y=y2) SET LOCATION '/user/test/x1/y1'

删除分区

ALTER TABLE test ADD DROP PARTITION(x=x1,y=y2)

修改列信息

ALTER TABLE test
 CHANGE COLUMN id uid INT
 COMMENT 'the unique id'
 AFTER name;

AFTER name意思是把字段移到name字段之后。
增加列

ALTER TABLE test ADD COLUMNS(new_col INT,new_col2 STRING);

删除或者替换列

ALTER TABLE test REPLACE COLUMNS(new_col INT,new_col2 STRING);

ALTER TABLE只是修改了元数据，所以一定要保证表的数据和修改后的元数据要匹配，否则数据将会变得不可用。