hive增删数据 hive表增删改查

目录

• 前言
• Hive数据类型
• Hive的元数据结构
• Hive指令

• 分区
• 分桶

• 补充

• 创建表时，删除表头
• 分隔符

前言

本篇博文主要演示一下在Hive命令行进行增删改查的一系列指令。Hive 的命令和SQL语句有许多相似之处，如果在这之前有过SQL的基础，那么Hive也会更加容易上手。
本文共分三个部分。第一部分会为大家介绍一下Hive的数据类型；第二部分为Hive的元数据结构；第三部分则为Hive的一些指令。我所使用的Hive版本是1.1.0版本，大家可以根据自己版本做相应的参考。

Hive数据类型

Hive的数据类型可以分为两种：原始数据类型和复杂数据类型。
原始数据类型类似于SQL的数据类型:

类型	示例
int	10
double	1.123
string	‘sam’
decimal	3.14
char	‘YES’
varchar	‘Yes’
bigint	100L

Hive还有其他的数据类型，这里就不一一展示。可以看出这些数据类型和SQL中的十分类似。甚至还多了一个String类型。这里String类型和java中的一致（Hive其实起源于FaceBook，它的主要创始人扎克伯格觉得java中的string十分好用，也就借鉴了过来）。在平时最常用的位前四个数据类型。字符串类型的我们一般就用string。decimal可以看成是一种字符串类型的数字，也可以进行计算比较大小，而且它的长度可以非常长，当bigint不够用时可以用这个。
复杂数据类型有三种：
Array:存储的数据为相同的类型；
Map：具有相同类型的键值对；
Struct：封装了一组字段。
Array我们学java时了解到这是一个数组，但在Hive中它其实是一个集合。

c.weight=2中的c其实代指的就是对象。

Hive的元数据结构

前面我们提到过Hive和SQL十分类似，所以它也有相应的数据库、表等结构。除此之外，hive还有独有的分区、分桶结构。接下来会进行详细的介绍。
Database：数据库，它是表的集合，当我们存储在HDFS（物理存储）上时表现为一个文件夹；
Table：表，它是行数据的集合，存储在HDFS上时也表现为一个文件夹，存储在相应的数据库文件下；
Partition：分区，用于分割数据，分割完的数据也保存在一个文件夹下；
Buckets：分桶，好多小伙伴有时会和分区弄混。分桶的作用是用于分布数据，它所产生的是一个文件，不是文件夹；
Partition和Buckets的区别：分区作用于文件夹，是把文件夹下的数据进行分割，然后再分成不同的文件夹存储；分桶作用于文件，是把一个文件内容分割成不同的部分，分别放在不同的文件中存储。
Row:行，记录表中的行记录；
Columns:列，记录表中的列记录；
Views：视图，逻辑概念，可跨越多张表，不用来存储数据；
Index：索引，记录统计数据信息，存储在文件夹中。

Hive指令

理论部分介绍完，接下来我们就介绍一下主要的一些操作语句，也是我们学习Hive所必须掌握的一项技能。首先是进入Hive的命令行。

hive -h hostname -p port

hostname是主机名，port是端口，如果是在自己电脑上操作的可以直接在命令行输入hive就可直接进入：

hive

创建库

首先我们创建一个database

create database if not exists TestHive;

如果没有库可以省略if not exists

show databases可以查看已创建的库;

hive是忽略大小写的。

use testhie;

使用该数据库，接下来的操作都在该数据库中进行。Hive中库级的操作和SQL基本一样。
表级操作
创建表：

create table t1(
id int,
name string);

插入数据：

insert into t1 values(1,'sam'),(2,'marry');

查询数据：

select * from t1;

语句也和SQL的一样，但是这里有一个问题，那就是在给表出入数据的时候，效率实在是太慢了，远远无法和SQL相比。原因其实在前面也有所提及，Hive数据库主要是用来做查询使用的，它的主要功能就是用来查询我们所需要的数据，在这一点上，它的速度比SQL还要快。如果需要用hive插入数据，则会大大影响我们的开发效率，所以一般都是针对不同的文件创建不同类型的表来进行文件的读取。我们先删除掉这个表：

drop table t1;

下面是一系列的数据，接下来通过这些数据来介绍Hive的用法。

Michael|100|Montreal,Toronto|Male,30|DB:80|Product:DeveloperLead
Will|101|Montreal|Male,35|Perl:85|Product:Lead,Test:Lead
Steven|102|New York|Female,27|Python:80|Test:Lead,COE:Architect
Lucy|103|Vancouver|Female,57|Sales:89,HR:94|Sales:Lead
Mike|104|Montreal|Male,35|Perl:85|Product:Lead,Test:Lead
Shelley|105|New York|Female,27|Python:80|Test:Lead,COE:Architect
Luly|106|Vancouver|Female,57|Sales:89,HR:94|Sales:Lead
Lily|107|Montreal|Male,35|Perl:85|Product:Lead,Test:Lead Shell|108|New
York|Female,27|Python:80|Test:Lead,COE:Architect
Mich|109|Vancouver|Female,57|Sales:89,HR:94|Sales:Lead
Dayong|110|Montreal|Male,35|Perl:85|Product:Lead,Test:Lead
Sara|111|New York|Female,27|Python:80|Test:Lead,COE:Architect
Roman|112|Vancouver|Female,57|Sales:89,HR:94|Sales:Lead
Christine|113|Montreal|Male,35|Perl:85|Product:Lead,Test:Lead
Eman|114|New York|Female,27|Python:80|Test:Lead,COE:Architect
Alex|115|Vancouver|Female,57|Sales:89,HR:94|Sales:Lead
Alan|116|Montreal|Male,35|Perl:85|Product:Lead,Test:Lead Andy|117|New
York|Female,27|Python:80|Test:Lead,COE:Architect
Ryan|118|Vancouver|Female,57|Sales:89,HR:94|Sales:Lead
Rome|119|Montreal|Male,35|Perl:85|Product:Lead,Test:Lead Lym|120|New
York|Female,27|Python:80|Test:Lead,COE:Architect
Linm|121|Vancouver|Female,57|Sales:89,HR:94|Sales:Lead
Dach|122|Montreal|Male,35|Perl:85|Product:Lead,Test:Lead Ilon|123|New
York|Female,27|Python:80|Test:Lead,COE:Architect
Elaine|124|Vancouver|Female,57|Sales:89,HR:94|Sales:Lead
建表

create table empid(
name string,
id int,
address array<string>,
empInfo struct<sex:string,age:int>,
score map<string,int>,
job map<string,string>)
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n';

第三列我这里设为了array类型，因为第一行的地址为两个，第四列的数据为两个不相干的数据，可以设为struct类型，最后两列相当于键值对形式。数据类型设置完后以括号结束。不过命令还未结束。上面的数据并不方便我们的阅读，所以还需要继续进行分割。整个文件以“|”分割，每行的数组（array）以‘“，”分割，map键值对以“：”分割。数据是一行一行的读，所以用换行符“\n”分割每行。

创建完成后可以show tables查看创建的表。

还可以登录50070端口去查看创建的表。前面我们说过table的形式就是在hdfs上创建一个文件夹

我是把hive安装在/opt目录下的，所以这里显示的路径为/opt/hive/warehouse/testhive.db，

在这个路径下可以看到已经创建了一个empid的文件夹。testhive.db后的db是在创建库的时候自动加上去的，表示这是一个数据库database。

上传并查询文件

对应的表创建完成，接下来就需要对文件中的数据进行查询。

我们首先查询一下表格

select * from empid

里面并没有内容。我们既然是根据文件创建的表，那么要想实现查询，首先就需要先上传文件。

我这里吧文件导入到了虚拟机的/opt目录下，上传到建表时建立的empid文件夹下：

hdfs dfs -put employee_id.txt /opt/hive/warehouse/testhive.db/empid

文件上传成功后，再次到Hive命令行执行查询语句:

select * from empid

会发现文件内容已经成功读取并安照建表的格式划分成功，不过这样的数据看起来还是不太容易阅读，大家可以根据自身的需要选择内容进行查询：

select name,id,address[0],empInfo.sex,score["Sales"] from empid;

当然也可以在后面加上where条件，这里就不过多演示。

分区

在介绍分区之前先说一下为什么要分区。随着系统运行时间的增加，表的数据量会越来越大，而Hive查询文件数据的时候通常使用的是全表扫描，这样会导致系统对大量不必要的数据进行扫描，降低了查询的效率，所以Hive就引进了分区技术避免全表扫描，提升查询效率。
我们以静态分区做演示：
这里用新的一个数据：

Michael|Montreal,Toronto|Male,30|DB:80|Product:DeveloperLead
Will|Montreal|Male,35|Perl:85|Product:Lead,Test:Lead Shelley|New
York|Female,27|Python:80|Test:Lead,COE:Architect
Lucy|Vancouver|Female,57|Sales:89|Sales:Lead

create table empPartition(
name string,
address array<string>,
personnalInfo array<string>,
technol map<string,int>,
jobs map<string,string>)
partitioned by (Year string,month string )
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n';

分区的代码和前面建表的一样，只不过是多了一个partitioned by。括号内的是用来存储的文件夹。month存储在Year路径下。

此时该文件夹下还没有东西，需要我们进行添加。

从本地添加文件：

load data local inpath '/opt/employee.txt'
into table empPartition
partition (Year='2020',month='9');

大的分区为Year，Year目录下又产生一个month文件夹，可以称为子文件夹，这个数据就存储在子目录下：

当然也可以多创建几个子目录，只要数据和前面的格式相同：

通过select查询语句我们可以查询文件内容，后面是分区信息：

在多做了几个分区后，查询empPartition会把它所包含的内容全都查询出来，这里我用了同一份数据，所有两个内容就全是相同的。
HDFS加载文件：
除了从本地加载文件外，我们也可以从hdfs上加载文件进行分区。
先看下代码：

load data inpath '/opt/hive/warehouse/hivetest.db/employee/employee.txt'
into table empPartition
partition (Year='2021',month='9');

代码和从本地加载基本一样，只是少了个local，执行完成后，可以到hdfs上查看上传信息：

此时我们在用查询语句查询会发现，又多了四行数据，不过后面的分区信息不同：

这里我们在简单演示用insert进行分区插入，即动态分区，为了方便，这里简单建立一个表：

create table test(
id int,
name string)
partitioned by (gender string)
row format delimited
fields terminated by ','
lines terminated by '\n';

即使没有数据，也需要有分隔符，至少也要有换行分隔符；
插入数据：

insert into test partition (gender='male') values(1,'sam'),(2,'john');

在插入时需要声明分区的文件夹partition (gender=‘male’)：

查询结果：

静态分区和动态分区在创建表的时候是一样的，只有在加载数据的时候才有区分：

• 静态数据使用load的方式进行加载数据，在加载的同时必须确定分区的值；
• 动态分区使用的是：insert into 表名 partition(分区) select * from 表名 的形式来加载数据。
• 一般情况下不建议使用动态分区，因为动态分区会调用MapReduce进行查询，如果分区的数量过多会影响MapReduce和Yarn集群的效率，达到其瓶颈。

前面我们所建立的表都是自动建立在/opt/hive/warehouse/testhive.db这一路径下，可以成为内部表，下面简单介绍一下外部表。顾名思义，就是表的存储路径不再上述数据库目录下。
首先创建一个外部目录

hdfs dfs -mkdir -p /usr/outTable/emp

创建外部表。外部表和内部表最大的一个区别是，内部表在删除表的时候，数据会一起被删除，它存储在所属数据库子目录下；外部表再删除表的时候数据不会被删除，所以外部表更加安全，在工作中也更加常用，存储在指定的HDFS路径中。（面试基本上是必问的一点！！！）

create external table emp_id(
name string,
empid int,
address array<string>,
empInfo struct<sex:string,age:int>,
score map<string,int>,
jobs map<string,string>)
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n'
stored as textfile
location '/usr/outTable/emp';

textfile为我们上传的文件格式，location是建表的地址，建表完成后，外部表并不会像内部表一样创建一个表目录：

导入文件，我这里是在opt目标下，所以employee_id.txt前面没有加路径：

hdfs dfs -put employee_id.txt /usr/outTable/emp

导入成功后，我我们hive命令行再用select查询一下，可以发现用外部表同样可以查询到数据。

在做数据分析的时候，我们可以借助分区分为男女，根据性别把文件放到不同的子目录下。

分桶

分桶在前面提到过，是对文件进行的划分，我们还以上面的数据为例。

create table emp_buckets(
name string,
address array<string>,
personnalInfo array<string>,
technol map<string,int>,
jobs map<string,string>)
clustered by(address) into 4 buckets
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n'
location '/usr/buckets';

和前面的代码相比修改了两个部分，桶的划分（这里分为2个，分桶数最好是2的n次方）和存储地址，以地址为划分依据。
这时候，如果我们直接导入数据是无法进行分桶的，因为实际上我们只是把文件上传到了该目录下，并没有对它进行分桶的操作，所以为了解决这一问题，我们还需要再建立一个临时表：

create table emp(
name string,
address array<string>,
personnalInfo array<string>,
technol map<string,int>,
jobs map<string,string>)
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n';

该临时表和分桶表的数据类型一致，然后把要分桶的文件上传到emp表：

load data local inpath '/opt/employee.txt' into table emp;

上传完成后，启用分桶：

set hive.enforce.bucketing=true;

再把emp文件夹中的数据添加到分桶表中：

insert into emp_buckets select * from emp;

这样就可以分桶成功我们从下图可以看出，分成了4个。如果不启动分桶则只会产生一个文件。

可以通过hdfs命令的-cat来查看分桶的内容

1、分桶随机分割数据库，分区是非随机分割数据库。因为分桶是按照列的哈希函数进行分割的，相对比较平均；而分区是按照列的值来进行分割的，容易造成数据倾斜。

2、分桶是对应不同的文件（细粒度），分区是对应不同的文件夹（粗粒度）。桶是更为细粒度的数据范围划分，分桶的比分区获得更高的查询处理效率，使取样更高效。

3、注意：普通表（外部表、内部表）、分区表这三个都是对应HDFS上的目录，桶表对应是目录里的文件

补充

最后补充一下对表结构的修改，修改表名和数据类型。就借用前面的一个建表语句：

create table empid(
name string,
id int,
address array<string>,
empInfo struct<sex:string,age:int>,
score map<string,int>,
job map<string,string>)
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n';

用desc查看表结构类型为：

下面对职工信息的名称和数据类型做一下修改，语句和SQL一样：

alter table empid change empInfo info struct<sex:string,age:string>;

显示分区：

show partitions 表名;

show partitions emppartition;

增加分区：

格式：alter table表名 add partition(分区字段=分区的值);

alter table emppartition add partition(Year='2019',month='5');

如果想要增加多个分区，可以在第一个分区后继续添加，用空格隔开

删除分区：

格式：alter table 表名 drop partition(分区字段=分区值’);

alter table emppartition drop partition(Year='2019');

可以登录HDFS查看表是否已删除：

给表新增一列：

格式：alter table 表名 add columns (列名 数据类型);

注意，需要加columns.

本篇文章主要介绍了一下Hive表的操作，希望大家可以多多练习，加深这方面的运用。Hive剩下的视图、索引等用法也会在后续的文章中陆续更新。

创建表时，删除表头

create external table ex_spu(
spu_id string,
shop_id string,
shop_name string,
category_name string,
spu_name string,
spu_price double,
spu_originprice double,
month_sales int,
praise_num int,
spu_unit string,
spu_desc string,
spu_image string)
row format delimited 
fields terminated by ','
stored as textfile
location '/apps/data/test/' #创建在数据路径
tblproperties("skip.header.line.count"="1") #去表头

分隔符

有时候在使用空格分割时，我们会用到\\s+或者\t或者手动空格，
这里需要注意的是。假如当我们文件中的数据是用\t分割的话，那么在建表时也需要用\t才可以分割。否则，会把数据全都归为一列。