Hive的数据类型和常用操作
Hive
数据类型和存储格式
- 基本数据类型:TINYINT(1字节)、SMALLINT(2字节)、INT(4字节)、BIGINT(8字节)、FLOAT(4字节)、DOUBLE(8字节)、BOOLEAN(true/false)、STRING(字符序列)。
Hive也是由Java编写的,所以Hive的基本数据类型都是对Java中的接口的实现,这些基本的数据类型和Java的基本数据类型是一一对应的。
-
复杂数据类型
- STRUCT:结构体,通过属性名访问属性值。struct(first string, last string),第一个元素可以通过”列名.first”访问
- MAP:”列名[first]”
- ARRAY:”列名[1]”
-
存储格式
- TEXTFILE:文本,默认格式,数据不做压缩,磁盘开销大,数据解析开销大。
- SEQUENCEFILE:Hadoop提供的一种二进制格式,使用方便、可分割、可压缩,按行进行切分。
- RCFILE:行列存储相结合的存储方法。首先,其将数据按行分块,保证同一条记录在一个块上,避免读一条记录需要读取多个快;其次,块上的数据按照列式存储,有利于数据压缩和快速进行列存储“线水平划分再按垂直划分”,
- 自定义格式:通过实现InputFormat和OutputFormat来自定义输入输出格式。
RCFILE的优点比较明显,首先,RCFILE具备相当于行存储的数据加载速度和负载适应能力;其次,RCFILE的读优化可以在扫描表时避免不必要的列读取;再次,RCFILE使用列维度的压缩,能够有效提升存储空间利用率。但是RCFILE在数据加载时性能损失较大,但是考虑”HDFS一次写入,多次读取”的特性,这种损失可以接受。
建表时使用STORED AS
子句指定文件存储的格式,该子句表示了该表是以何种文件形式存储在文件系统。
- 数据格式
- 数据存储在文本文件中,需要按照一定的格式区分行和列并且向Hive指明,Hive才能将其识别,常见的格式有CSV(逗号分隔)和TSV(制表符分隔)。
- Hive默认使用了几个平时很少出现的字符:
- \n:默认行分隔符
- ^A(Ctrl + A):文本中以八进制编码\001表示,行分隔符
- ^B(Ctrl + B):文本中以八进制编码\002表示,作为ARRAY、STRUCT元素的分隔符,或者MAP中键值对的分隔符
- ^C(Ctrl + C):文本中以八进制编码\001表示,用于MAP中键值对的分隔
CREATE TABLE student{
name STRING
age INT
cource ARRAY<STRING>
body MAP<STRING,FLOAT>
address STRUCT<STREET:STRING,CITY:STRING,SATE: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;
-
创建表
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name [(col_name data_type [COMMENT col_comment], ...)] [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 row_format] [STORED AS file_format] [LOCATION hdfs_path]
1.CREATE TABLE:创建一个指定名字的表。如果相同名字的表已经存在,则抛出异常;用户可以用 IF NOT EXISTS 选项来忽略这个异常。
2.EXTERNAL关键字可以让用户创建一个外部表,在建表的同时指定一个指向实际数据的路径(LOCATION),Hive 创建内部表时,会将数据移动到数据仓库指向的路径;若创建外部表,仅记录数据所在的路径,不对数据的位置做任何改变。在删除表的时候,内部表的元数据和数据会被一起删除,而外部表只删除元数据,不删除数据。
3. LIKE 允许用户复制现有的表结构,但是不复制数据。
4. ROW FORMAT
DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED,将会使用自带的 SerDe。在建表的时候,用户还需要为表指定列,用户在指定表的列的同时也会指定自定义的 SerDe,Hive通过 SerDe 确定表的具体的列的数据。
5.STORED AS
SEQUENCEFILE|TEXTFILE|RCFILE
如果文件数据是纯文本,可以使用 STORED AS TEXTFILE。如果数据需要压缩,使用 STORED AS SEQUENCEFILE。
6.CLUSTERED BY
对于每一个表(table)或者分区, Hive可以进一步组织成桶,也就是说桶是更为细粒度的数据范围划分。Hive也是 针对某一列进行桶的组织。Hive采用对列值哈希,然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
把表(或者分区)组织成桶(Bucket)有两个理由:
(1)获得更高的查询处理效率。桶为表加上了额外的结构,Hive 在处理有些查询时能利用这个结构。具体而言,连接两个在(包含连接列的)相同列上划分了桶的表,可以使用 Map 端连接 (Map-side join)高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列,如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以,可以大大较少JOIN的数据量。
(2)使取样(sampling)更高效。在处理大规模数据集时,在开发和修改查询的阶段,如果能在数据集的一小部分数据上试运行查询,会带来很多方便。
- 管理表
- 建表时没有特别指明,都是Hive中的管理表。由hive负责管理表的数据,默认数据保存在数据仓库目录下。删除管理表时,Hive将删除管理表的数据和元数据。
create table book (id bigint, name string) partitioned by (pubdate string) row format delimited fields terminated by '\t';
- 外部表
- 当一个数据被多种工具分析,这份数据的所有权不在Hive,这是可以创建外部表来指向这份数据:
create external table td_ext(id bigint, account string, income double, expenses double, time string) row format delimited fields terminated by '\t' location '/td_ext';
删除时只删除该外部表的元数据信息。
- 分区表
- 对表进行水平切分,相对于没有分区的表有明显的性能优势
create table td_part(id bigint, account string, income double, expenses double, time string) partitioned by (logdate string) row format delimited fields terminated by '\t';
分区字段不能和定义表的字段重合。
分区表将表水平切为若干个文件,切分的规则就是分区的字段。
由于数据量可能非常巨大,所以都会对表进行分区,最常见的是按照创建时间或者修改时间进行分区,所以一个表中的分区数目一般比较多。执行一条包含所有分区的查询将耗费集群巨大的时间和资源,对此可以将Hive的安全措施设定为“strict”模式,这样如果一个针对分区表的查询没有对分区进行限制的话,作业会被进制提交。可以修改hive-site.xml
文件中的hive.mapred.mode
为strict(默认为nostrict),或者在hive命令行中:
hive> SET hive.mapred.mode=strict;
- 删除表(DROP)
-
修改表
- 重命名:
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 DROP PARTITION (x = x1,y = y2)
- 增加分区(通常是外部表):
- 修改列信息(列重命名,修改数据类型、注释、表中的位置)
ALTER TABLE tast CHANGE COLUMN id uid INT COMMENT 'the unique id' AFTER name;
- 增加列:
ALTER TABLE test ADD COLUMNS (new_col INT,new_col2 STRING);
- 删除或者替换列(删除test表中所有列并重新定义字段,修改的是元数据):
ALTER TABLE test REPLACE COLUMNS (new_col INT,new_col2 STRING);
- 重命名:
-
装载数据
- 语法结构:
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
- LOAD DATA:
LOAD DATA INPATH '/user/test' INTO TABLE test;
- 如果需要覆盖test表已有的记录,加上OVERWRITE关键字:
LOAD DATA INPATH '/user/test' OVERWRITE INTO TABLE test;
- 如果test表示一个分区表,则必须制定分区:
LOAD DATA INPATH '/user/test' OVERWRITE INTO TABLE test PARTITION (part = "a");
- Hive也支持从本地直接加载数据到表中,需要加上LOCAL关键字:
LOAD DATA LOCAL INPATH '/home/test' INTO TABLE test;
- 语法结构:
filepath:
相对路径,例如:project/data1
绝对路径,例如:/user/hive/project/data1
包含模式的完整 URI,列如:hdfs://namenode:9000/user/hive/project/data1OVERWRITE关键字:如果使用了OVERWRITE关键字,则目标表(或者分区)中的内容会被删除,然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。 如果目标表(分区)已经有一个文件,并且文件名和filepath中的文件名冲突,那么现有的文件会被新文件所替代。
如果加上关键字LOCAL,Hive会在本地文件复制一份再上传至指定目录,如果不加只是将HDFS上的数据移动到制定目录。
- 通过查询语句向表中插入数据
INSERT OVERWRITE TABLE test SELECT * FROM source;
- 当test是分区表时:
INSERT OVERWRITE TABLE test PARTITION (part = "a") SELECT id,name FROM source;
- 一次查询,产生多个不想交的输出:
FROM source
INSERT OVERWRITE TABLE test PARTITION (part = "a")
SELECT id,name WHERE id >= 0 AND id < 100
INSERT OVERWRITE TABLE test PARTITION (part = "b")
SELECT id,name WHERE id >= 100 AND id < 200
INSERT OVERWRITE TABLE test PARTITION (part = "c")
SELECT id,name WHERE id >= 200 AND id < 300
- 动态分区表向表中插入数据
- 基于查询参数自动推断出需要创建的分区,根据SELECT语句的最后一个查询字段作为动态分区的依据,而不是根据字段名来选择。如果制定了n个动态分区的字段,会将select语句中最后n个字段作为动态分区的依据。eg:
INSERT OVERWRITE TABLE test PARTITION(time) SELECT id,modify_time FROM source;
- 基于查询参数自动推断出需要创建的分区,根据SELECT语句的最后一个查询字段作为动态分区的依据,而不是根据字段名来选择。如果制定了n个动态分区的字段,会将select语句中最后n个字段作为动态分区的依据。eg:
Hive默认没有开启动态分区,在执行语句之前,必须先进行参数设置:set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = nostrict;表示所有分区都是动态的,默认不允许所有分区都是动态的,静态分区必须位于动态分区之前。
CTAS加载数据
CREATE TABLE test AS SELECT id,name FROM source
数据导出
INSERT OVERWRITE DIRECTORY '/user/test' SELECT * FROM test
INSERT OVERWRITE LOCAL DIRECTORY '/home/test' SELECT * FROM test
如果Hive表中的数据满足用户需要的数据格式,直接复制文件即可。