Hive基本操作

Hive是一个可以使用多种计算引擎以及兼容多种数据存储格式的分布式OLAP客户端。

Hive简介

Hive只是一个客户端,因为他需要额外的计算引擎支持,以及额外的数据格式支持,而且,这些计算引擎以及额外的数据格式是完全可以独立于Hive之外而存在的。比如我的一张Hive表底层的存储是text或者是parquet、orc等列式数据存储格式,这些数据存储格式其实都是自解释的,也就是说我不用Hive这个外壳,也完全可以用其他api或者计算引擎把想要的数据给取出来。

Hive特点

  • 天然的分布式数据库,底层存储为HDFS(天然的分布式文件系统)
  • 数据操作(查询、修改、条件删除)需要额外的计算引擎支持,如:MR、Tez、spark等
  • 虽然数据都是存储在HDFS,但是支持多种不同的数据存储格式,如:Text、SequenceFile、RCFile、avro、parquet、orc,carbondata等
  • 本身不支持索引功能,不支持时间复杂度非常低的数据结构
  • 所有操作(查询、修改、条件删除)基本上都是离线的,需要进行全表IO
  • 只是将数据与表之间建立一种简单的映射关系
  • 如果用SQL进行数据操作,将SQL转换为计算引擎的执行计划并执行

Hive使用场景

Hive是离线数据库,特点是全量、慢

优势:

  • 因为数据完全是存在HDFS之上,存在数据冗余,因此数据支持高可用
  • 因为Hive的数据存储几乎只占用磁盘空间,适合来存储海量的全量离线数据集(轻松支持PB、TB量级数据集)
  • 支持丰富的数据访问接口,如:JDBC,hiveserver以及主流的计算引擎spark、flink、storm等
  • 支持丰富的SQL语句(遵循SQL92标准),以及可根据业务情况定制的UDF函数
  • 表存储的数据是自解释的,可以完全脱离Hive本身用其他api进行读取

劣势:

  • 数据处理延迟很高,因为每次数据处理都会启动计算引擎以及几乎全量的数据IO
  • 不支持事务
  • 一旦写入,不支持修改

HDFS常用命令

下面这个命令将文件夹底下的全部文件合并,然后下载到本地:

1
hdfs dfs -getmerge hdfs://user/hive/warehouse/tempon.db/tl_text_center /home/admin/t1_subject.txt;

创建路径:

1
hdfs dfs -mkdir 'hdfs://user/admin/deploy/text_anti_brush';

删除文件/文件夹:

1
2
hdfs dfs -rm hdfs://user/admin/deploy/text_anti_brush/t1_subject_info.txt;
hdfs dfs -rm -r -f hdfs://user/admin/deploy/text_anti_brush/;

PUT数据(HDFS路径一定要存在,否则不会执行):

1
hdfs dfs -put -f /home/admin/t1_subject_info.txt hdfs://user/admin/deploy/text_anti_brush/

创建表

其中[]表示可选,|表示二选一。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
[(col_name data_type [COMMENT col_comment], ... [constraint_specification])]
[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]
[SKEWED BY (col_name, col_name, ...)
ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
[STORED AS DIRECTORIES]
[
[ROW FORMAT row_format]
[STORED AS file_format]
| STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]
]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)];

EXTERNAL

EXTERNAL关键字可以让用户创建一个外部表,在建表的同时指定一个指向实际数据的路径(LOCATION),Hive创建内部表时,会将数据移动到数据仓库指向的路径;若创建外部表,仅记录数据所在的路径,不对数据的位置做任何改变。在删除表的时候,内部表的元数据和数据会被一起删除,而外部表只删除元数据,不删除数据

PARTITIONED BY(分区)

假设有海量的数据保存在HDFS的某一个Hive表名对应的目录下,使用hive进行操作的时候,往往会搜索这个目录下的所有文件,非常的耗时。

如果我们知道这些数据的某些特征,可以事先对他们进行分割,在load到HDFS上的时候,他们就会被放到不同的目录下,然后使用Hive进行操作的时候,就可以在where子句中对这些特征进行过滤,那么对数据的操作就只会在符合条件的子目录下进行,其他不符合条件的目录下的内容就不会被读取,在数据量非常大的时候,这样节省大量的时间。

这种把表中的数据分散到子目录下的方式就是分区表

动态分区表

创建一个分区表,指定两级分区:国家和省份

1
partitioned by (country string,state string)

从宽表中查询出所需要的字段加载到分区表中:

1
insert into table emp partition(country,state) select name,age,country,state from t1;

ROW FORMAT

Hive将HDFS上的文件映射成表结构,通过分隔符来区分列(比如,;^等),ROW FORMAT就是用于指定序列化和反序列化的规则。

1
2
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。

比如对于以下记录:

1
2
3
1,xiaoming,book-TV-code,beijing:chaoyang-shagnhai:pudong
2,lilei,book-code,nanjing:jiangning-taiwan:taibei
3,lihua,music-book,heilongjiang:haerbin

逗号用于分割列,即FIELDS TERMINATED BY ',',分割为如下列:

  • ID
  • name
  • hobby(该字段是数组形式,通过-进行分割,即COLLECTION ITEMS TERMINATED BY '-'
  • address(该字段是键值对形式,通过:分割键值,即MAP KEYS TERMINATED BY ':'

LINES TERMINATED BY char用于区分不同条的数据,默认是换行符。

STORED AS

1
STORED AS SEQUENCEFILE|TEXTFILE|RCFILE

如果文件数据是纯文本,可以使用STORED AS TEXTFILE。如果数据需要压缩,使用STORED AS SEQUENCEFILE

删除表

删除内部表很简单,这里主要说一下删除外部表。

删除外部表(同时丢弃数据)

一种办法是先把外部表转成内部表,然后再删除:

1
2
3
4
5
6
ALTER TABLE
external_hive_table
SET TBLPROPERTIES (
'EXTERNAL'='FALSE');

DROP TABLE external_hive_table;

另外一种办法是同时去删除HDFS文件:

1
DROP TABLE external_hive_table;
1
hdfs dfs -rm -r -f hdfs://user/admin/deploy/external_hive_table/;

如果删除特定分区数据,可以使用:

1
ALTER TABLE external_hive_table DROP IF EXISTS PARTITION(year=2021);

同时删除HDFS文件。

删除外部表(保留数据)

1
DROP TABLE external_hive_table;

查看表信息

  1. 查看表的分区情况
1
show partitions new_partition;
  1. 查看表的创建语句
1
show create table new_partition;
  1. 查看表的结构信息(详细)
1
desc formatted new_partition;
  1. 查看表的结构信息(简单)
1
desc new_partition;
  1. 查看表的扩展信息(信息更详细)
1
desc extended new_partition;

插入数据

Hive现在还不支持在insert语句里面直接给出一组记录的文字形式,也就是说,Hive并不支持INSERT INTO …. VALUES形式的语句。

Hive常见的数据导入方式:

  1. 从本地文件系统中导入数据到Hive表
  2. 从HDFS上导入数据到Hive表
  3. 从别的表中查询出相应的数据并导入到Hive表中
  4. 在创建表的时候通过从别的表中查询出相应的记录并插入到所创建的表中

Load加载数据(本地文件系统/HDFS导入数据)

Hive Load语句不会在加载数据的时候做任何转换工作,而是纯粹的把数据文件复制/移动到Hive表对应的地址。

1
2
3
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] [INPUTFORMAT 'inputformat' SERDE 'serde'] (3.0 or later)
  • LOCAL:
    • 如果命令中带有LOCAL,说明从本地文件系统加载数据,文件路径可以是相对路径,也可以是绝对路径。在这种情况下,首先将文件从本地复制到hdfs相应的位置,然后移动到hive表格中,这个时候原始数据文件是存在于Hive表之下的路径下。
    • 如果不包含LOCAL关键字,则移动(不是复制)HDFS文件到目标表中。
  • filepath:可以是一个相对路径,也可以是一个绝对路径。可以是一个文件,也可以是一个文件夹目录。如果是一个目录,这个时候文件夹下的所有文件都会被加载。
  • OVERWRITE:代表加载数据之前会清空目标表格,否则就是追加的方式。
  • PARTITION:如果表是分区表则必须指定PARTITION从句,否则会报如下错误:FAILED:SemanticException [Error 10062]: Need to specify partition columns because the destination table is partitioned

Load Data报错:Invalid path

通过类似下面的命令去Load Data:

1
load data local inpath '/home/xxx/2018-05-27_14.txt' overwrite into table data_idea_id_hourly partition ( dt = '2018-05-27',hour = '14' );

报错找不到文件(确定当前机器确实存在这个文件):

1
Error: Error while compiling statement: FAILED: SemanticException Line 1:23 Invalid path ''/home/xxx/2018-05-01_14.txt'': No files matching path file:/home/xxx/2018-05-01_14.txt (state=42000,code=40000)

下面是一个解释:

When using the JDBC driver, the command executes on the HiveServer2 side. The file is evaluated to locally exist on the server, which is not true in your case (it exists on the local client program machine).

Try instead to load the file to HDFS first, and use a HDFS URI in the LOAD DATA statement to make the server find it.

从上面的解释可知,hive导入数据语句load data [local] inpath是一个服务器端的指令,它是在服务器端执行。因此指定local时表明加载的文件为本地文件,但是这里的local,在hive中指的是hiveserver服务所在的机器,而不是hivecli或beeline客户端所在的机器(生产环境大都是hiveserver和hivecli不在同一个机器)

因此我们可以先将本地文件上传至HDFS,然后使用load data inpath 'hdfspath' [overwrite] into table table_name来导入数据

从别的表查询记录导入到Hive表

下面语句就是将wyp表中的查询结果并插入到test表中:

1
2
3
4
insert into table test
partition (age)
select id, name, tel, age
from wyp;

从别的表查询记录插入到新建Hive表中

在实际情况中,表的输出结果可能太多,不适于显示在控制台上,这时候,将Hive的查询输出结果直接存在一个新表中:

1
2
3
4
create table test4
as
select id, name, tel
from wyp;

参考


----------- 本文结束啦感谢您阅读 -----------

赞赏一杯咖啡

欢迎关注我的其它发布渠道