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

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

一、 基本数据类型

数据类型所占字节开始支持版本TINYINT1byte，-128 ~ 127SMALLINT2byte，-32,768 ~ 32,767INT4byte,-2,147,483,648 ~ 2,147,483,647BIGINT8byte,-9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807BOOLEANFLOAT4byte单精度DOUBLE8byte双精度STRINGBINARYHive0.8.0TIMESTAMPHive0.8.0DECIMALHive0.11.0CHARHive0.13.0VARCHARHive0.12.0DATEHive0.12.0

二、 集合数据类型

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

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13

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

五、 HQL

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

1. Hive中的数据库

Hive中的数据库本质上仅仅是个表的目录或者命名空间。在生产环境中，如果表常多的话，一般会用数据库将生产表组织成逻辑组。

实际情况中，用户不指定数据库，则默认数据库default。可以使用

hive > CREATE DATABASE IF NOT EXISTS test;

• 1

创建数据库。

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

如果针对某个数据库改变其存放位置，则

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

• 1

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

hive> DESCRIBE DATABASE test;

• 1

使用

hive> USE test;

• 1

切换数据库。

常用命令

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');

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 查看表

hive> use test;

hive> show tables like 'aaa.*';

• 1
• 2

2. Hive中的表

USE test;

SHOW TABLES;

SHOW TABLES IN test;

• 1
• 2
• 3

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';

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15

查看字段和表注释

DESC student;

DESC EXTENDED student;

DESC FORMATTED student

• 1
• 2
• 3

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'

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8

其中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);

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8

Partitioned by 子句指名按学生家庭住址的city和province字段进行分区，分区的字段不能和定义表的字段重合。表的分区是以表目录的子目录存在，而表是以目录形式存在。

分区的作用相当于简易的索引，对提升性能作用很大。

可以将hive的安全措施设定为”strict”模式，这样一个针对分区表的查询如果没有对分区进行限制的话，会禁止提交作业。

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

6. 其它操作

删除表

DROP TABLE IF EXISTS test

• 1

修改表名

ALTER TABLE test RENAME TO test2

• 1

增加、修改、删除表分区

增加分区（通常外部表）

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

• 1

修改分区

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

• 1

删除分区

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

• 1

修改列信息

ALTER TABLE test

CHANGE COLUMN id uid INT

COMMENT 'the unique id'

AFTER name;

• 1
• 2
• 3
• 4

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

增加列

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

• 1

删除或者替换列

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

• 1

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