Hive数据表操作--学习笔记

1，Hive数据表操作

1，建表语句和内外部表
①创建内部表

create [external] table [if not exists] 表名(
字段名 字段类型 [comment '注释'],
字段名 字段类型 [comment '注释'],
...
)
[row format delimited
fields terminated by '指定分隔符'];
1
2
3
4
5
6
7

（1）直接使用create table创建表时默认创建内部表，而当添加external后，表示创建外部表；
（2）当要查看表类型与详细信息时，使用语法[desc formatted 表名;]；
2，外部表(EXTERNAL_TABLE)
外部表（External），是指被external关键字修饰的表， 也可称为关联表。
要创建一个外部表，需要添加external关键字，语法：

create external table 数据表名 ......[location]......
1

1.1，请说一说内部表和外部表有什么区别？

在Hive中，内部表和外部表的区别主要有如下几点：
1，通过external关键字区分，内外部表
内部表：内部表直接使用create table 内部表名 的形式创建即可
外部表：外部表需要使用external 关键字来修饰，因此创建外部表语法时，create external table table 外部表名
2，内部表和外部表再Hive中的管理范围不一样
内部表：表类型表示为Manage_table ,Hive在管理内部表时当使用drop删除表，既能删除内部表的元数据，也能删除在HDFS中的实际数据，因此，Hive能完全控制和管理内部表，所以内部表也可以称为管理表
外部表：表类型表示为，external_taable,Hive在管理外部表时，只能删除元数据信息，而不能删除HDFS中的实际数据，当使用drop删除外部表时，只能删除外部表的元数据，而不能删除外部表位于HDFS中的真实数据，因此，Hive仅能管理外部表的部分内容，所以外部表也可以称为关联表。
3，更改表结构与两类表操作有所不同
内部表：更改内部表结构时，HDFS中可以同步更改数据结果；
外部表：当对外部表更改结构后，HDFS中不能立马同步结果，而需要进行对外部表进行修复操作；
4，存储路径可能不同
内部表：当在设计内部表时，一般对于存储路径是默认的，即/user/hive/warehose目录下；
外部表：一般在创建外部表时，为了数据的安全，可能会设定存储路径名。
以上就是我对内部表和外部表区别的一些个人理解。
3,字段类型
对于字段类型，整体可以分为两个大类别：
（1）原生数据类型（primitive data type）
原生数据类型包括：数值类型、时间类型、字符串类型、杂项数据类型；
在原生数据类型中，较为常见的有：int、double、string、varchar、timestamp、
date。
（2）复杂数据类型（complex data type）
复杂数据类型包括：array数组、map映射、struct结构、union联合体。
4,关于Hive的数据类型，需要注意：
（1）Hive的数据类型不区分大小写，或者说对大小写不敏感；
（2）除SQL数据类型外，还支持Java数据类型，比如string；
（3）int和string是使用最多的，大多数函数都支持；
（4）复杂数据类型的使用时，通常需要和分隔符指定语法并配合使用；
（5）如果定义的数据类型和文件不一致，hive会尝试自动转换类型，但是不保证成
功。

1.2 创建表

（1）创建表 create
（2）查看表 desc/show
（3）删除表 drop
（4）修改表 alter
1，默认分隔符
默认分隔符应该是：“\001”。在某些文本编辑器中，这类特殊符号会显示为SOH。
2，常见的建表语句
（1）基于查询结果建表
create table 表名 as select 语句;
（2）基于已存在的表结构建表（相当于复制一张和已存在的表一摸一样的表）
create table 表名 like 已存在的表名;

-- 了解快速建表操作
create table computer(
brand string,
price double,
size int
) row format delimited
fields terminated by "\t";
insert into computer values ("苹果",19999.99,8);
-- _copy
create table phone_copy as select * from phone;
create table computer_copy like computer;
1
2
3
4
5
6
7
8
9
10
11

1.3查看表与删除表

（1）查看表
在Hive中，DESC TABLE语句可用于显示Hive中表的元数据信息。

-- 查看字段基本信息
desc [数据库名.]表名;
-- 以序列化形式查看字段更多信息
desc extended [数据库名.]表名;
-- 以表格形式查看字段所有信息
desc formatted [数据库名.]表名;
1
2
3
4
5
6

（2）删除表
drop table 表名;
当使用删除表命令删除内部表时，元数据及数据表内容等都会删除。

1.4修改表

（1）添加一列
给数据表添加一列新字段，

alter table 表名 add columns(字段名 数据类型);
1

（2）修改字段名
当数据表的某字段名有误时，可以修改字段名，语法

alter table 表名 change 原字段 新字段名 类型;
1

（3）修改表名
一般地，给数据表命名时一般会添加前缀tb或tbl或table字样。语法：

alter table 表名 rename to 新的表名;
1

（4）修改表位置路径
也可以修改表的存储位置，语法：

alter table 表名 set location '路径';
1

1.5操作内部表数据

在Hive中的内部表操作数据，语法：
（1）插入数据
insert into 表名[(字段名, 字段名, …)] values(值, 值, …);
（2）查看数据
select * from 表名;
（3）清空数据
truncate table 表名;

-- ------------------操作数据--------------------------
use sz41db_machine;
create table teacher(
id int,
name string,
gender string
) row format delimited
fields terminated by "\t";
insert into teacher values (1,"fangGE","Man"),(2,"文
文","Woman");
select * from teacher;
truncate table teacher;
1
2
3
4
5
6
7
8
9
10
11
12

1.6上传文件映射表

-- ---------------映射文件-----------------------
use sz41db_machine;
show tables ;
create table student(
id int,
stu_name string
)row format delimited
fields terminated by ",";
select * from student;
1
2
3
4
5
6
7
8
9

（1）当数据表字段内容与表目录下的文件数据有对应，则会自动映射数据内容
到Hive表中；
（2）我们会发现，当把数据表导入到HDFS对应目录后，这类插入数据的方式
比直接insert要更高效。

2，外部表操作

1，创建外部表

create external table [if not exists] 表名(
字段名 字段类型 [comment '注释'],
字段名 字段类型 [comment '注释'],
...
)
[comment '注释']
[row format delimited
fields terminated by '指定分隔符']
[location 'HDFS路径名']
[tblproperties (属性名=值, 属性名=值, ...)];
1
2
3
4
5
6
7
8
9
10

desc 表名; – 查看字段基本信息 1
desc formatted 表名; – 以表格形式查看信息 1
desc extended 表名; – 以序列化形式查看更多信息

2.1修改表信息

（1）查看创建表语句
为了查看建表的具体信息，可以使用创建表语句：

show create table 表名;
1

（2）修改字段类型
当要修改表中的字段类型时，语句：

alter table 表名 replace columns (字段名 类型, 字段名 类型, ...);
1

（3）修改表属性
在创建表时，我们看到可以添加一个[tblproperties属性]选项。其实，也是可以修改
的。语法：

alter table 表名 set tblproperties (属性名=值, ...);
1

说明：
（1）当修改属性且修改表中不存在的属性信息时，则表示添加一个表属性；
（2）当修改表中已有属性名时，仅需设定属性名一致，并把值修改即可。
2，切换内部表与外部表
（1）当仅需要临时使用数据时，可以临时使用外部表，其他时候通常建议使用内部表；
（2）一般情况下，不建议修改表的存储路径地址，因此修改表路径仅作了解。
alter table 表名 set tblproperties (“EXTERNAL”=“TRUE”);

2.2 内部表与外部表的区别

再Hive中，内部表和外部表的区别主要有如下几点：
1，通过external关键字区分，内外部表
内部表：内部表直接使用create table 内部表名 的形式创建即可
外部表：外部表需要使用external 关键字来修饰，因此创建外部表语法时，create external table table 外部表名
2，内部表和外部表再Hive中的管理范围不一样
内部表：表类型表示为Manage_table ,Hive在管理内部表时当使用drop删除表，既能删除内部表的元数据，也能删除在HDFS中的实际数据，因此，Hive能完全控制和管理内部表，所以内部表也可以称为管理表
外部表：表类型表示为，external_taable,Hive在管理外部表时，只能删除元数据信息，而不能删除HDFS中的实际数据，当使用drop删除外部表时，只能删除外部表的元数据，而不能删除外部表位于HDFS中的真实数据，因此，Hive仅能管理外部表的部分内容，所以外部表也可以称为关联表。
3，更改表结构与两类表操作有所不同
内部表：更改内部表结构时，HDFS中可以同步更改数据结果；
外部表：当对外部表更改结构后，HDFS中不能立马同步结果，而需要进行对外部表进行修复操作；
4，存储路径可能不同
内部表：当在设计内部表时，一般对于存储路径是默认的，即/user/hive/warehose目录下；
外部表：一般在创建外部表时，为了数据的安全，可能会设定存储路径名。
以上就是我对内部表和外部表区别的一些个人理解。

3，数据的导入与导出

1.1添加数据

（1）在HDFS访问页面导入文件
首先，进入HDFS访问页面中，然后把外部文件导入存储数据的路径。
（2）直接使用insert into插入数据
采用SQL语法insert来插入数据到数据表中，语法：

insert into 表名[(字段名, 字段名, ...)] values(值, 值, ...);
1

（3）从已有数据的表中，导入数据到新表
当表A中已存在数据，要把表内数据复制到另一张表B时，语法：

-- 追加数据
insert into [table] 表名A select * from 表名B;
-- 数据覆盖
insert overwrite table 表名A select * from 表名B;
1
2
3
4

1.2数据的加载

（1）使用hdfs put命令上传文件
（2）使用load语法加载数据
（1）使用hdfs put命令上传文件
-put命令用于将本地系统的文件上传到HDFS系统中，语法：
hdfs dfs -put /本地文件路径 /hdfs文件路径
（2）使用load语法加载数据
load可以翻译为：加载。可以用于给数据表中导入数据，语法：

load data [local] inpath '文件路径名' [overwrite] into table 表
名;
1
2

1.3数据的导出

（1）从HDFS页面导出文件
到HDFS访问页面中，直接Download下载文件。
（2）使用hdfs get命令下载文件
-get命令用于将HDFS系统中的文件下载到本地，语法：
（3）使用类SQL命令导出数据
也可以将hive表中的数据导出到其他任意目录，例如linux本地目录、hdfs路径等。

insert overwrite [local] directory "存放目录路径名" select 语句;
1

将查询的结果导出到本地，且使用默认列分隔符。

insert overwrite local directory '/home/hadoop/export1' select
* from test_load ;
1
2

将查询的结果以指定列分隔符\t形式，导出到本地Linux目录。

insert overwrite local directory '/home/hadoop/export2' row
format delimited fields terminated by '\t' select * from
test_load;
1
2
3

将查询的结果导出到HDFS上(不带local关键字)。

insert overwrite directory '/tmp/export' row format delimited
fields terminated by '\t' select * from test_load;
1
2