导语：针对hive各种数据库操作，内部表、外部表、分区表、分桶表的表属性查看修改操作以及hive数据的导入与导出详解。

hive简介：

        hive是基于Hadoop的一个数据仓库工具，用来进行数据提取、转化、加载，这是一种可以存储、查询和分析存储在Hadoop中的大规模数据的机制。hive数据仓库工具能将结构化的数据文件映射为一张数据库表，并提供SQL查询功能，能将SQL语句转变成MR任务来执行。

数据库操作

创建数据库

-- 创建数据库
create database if not exists database_name;


-- 指定hdfs存储位置
create database database_name location '/path';

修改数据库

alter  database  name set  dbproperties('createtime'='20230301');

查看数据库详细信息

查看数据库基本信息
desc  database  name;

查看数据库更多详细信息
desc database extended  name;

删除数据库

删除一个空数据库，数据库下面不能有数据表
drop  database  name;

强制删除数据库，包含数据库下面的表一起删除
drop  database  name cascade;

数据表操作

内部表

建表

 create table stu(id int,name string);

-- 创建表并指定字段之间的分隔符
create  table if not exists stu2(id int ,name string) 
row format delimited fields terminated by '\t' -- 指定字段分隔符
stored as textfile                             -- 指定存储格式
location '/user/stu2';                         -- 指定存储位置

-- 根据查询结果创建表
create table stu3 as select * from stu2;

--根据已经存在的表结构创建表
create table stu4 like stu2;

字段类型

hive数据类型

类型	含义
BOOLEAN	true/false
TINYINT	1字节的有符号整数 -128~127
SMALLINT	2个字节的有符号整数，-32768~32767
INT	4个字节的带符号整数
BIGINT	8字节带符号整数
FLOAT	4字节单精度浮点数1.0
DOUBLE	8字节双精度浮点数
DEICIMAL	任意精度的带符号小数 decimal(13,2) 代表最多有13位数字，其中后2位是小数，整数部分是11位；如果整数部分超过11位，则这个字段就会变成null；如果小数部分不足2位，则后面用0补齐两位，如果小数部分超过两位，则超出部分四舍五入
STRING	字符串，变长
VARCHAR	变长字符串
CHAR	固定长度字符串
BINARY	字节数组
TIMESTAMP	时间戳，毫秒值精度
DATE	日期
INTERVAL	时间频率间隔
ARRAY	有序的的同类型的集合
MAP	key-value,key必须为原始类型，value可以任意类型
STRUCT	字段集合,类型可以不同
UNION	在有限取值范围内的一个值

查询表的结构

只查询表内字段及属性
desc stu2;

详细查询
desc formatted  stu2;

查询创建表的语句
show create table stu2;

外部表

外部表因为是指定其他的hdfs路径的数据加载到表当中来，所以hive表会认为自己不完全独占这份数据，所以删除hive表的时候，数据仍然存放在hdfs当中，不会删掉，只会删除表的元数据（表头）

建外部表

create external table student (s_id string,s_name string) 
row format delimited fields terminated by '\t';

本地文件系统向表中加载数据

追加操作
load data local inpath '/export/servers/hivedatas/stu.txt' into table student;

覆盖操作
load data local inpath '/export/servers/hivedatas/stu.txt' overwrite  into table student;

hdfs文件系统向表中加载数据

load data inpath '/hivedatas/te.csv' into table techer;

加载数据到指定分区
load data inpath '/hivedatas/te.csv' into table techer partition(cdate=20201210);

• 使用 load data local 表示从本地文件系统加载，文件会拷贝到hdfs上
• 使用 load data 表示从hdfs文件系统加载，文件会直接移动到hive相关目录下，注意不是拷贝过去，因为hive认为hdfs文件已经有3副本了，没必要再次拷贝了
• 如果表是分区表，load 时不指定分区会报错
• 如果加载相同文件名的文件，会被自动重命名

分区表

建分区表

create table score(s_id string, s_score int) partitioned by (month string);


-- 建一个表带多个分区
create table score2 (s_id string, s_score int) partitioned by (year string,month string,day string);

注意

hive表创建的时候可以用 location 指定一个文件或者文件夹，当指定文件夹时，hive会加载文件夹下的所有文件，当表中无分区时，这个文件夹下不能再有文件夹，否则报错

加载数据

加载数据到一个分区的表中

load data local inpath '/export/servers/hivedatas/score.txt' into table score partition (month='201806');

加载数据到一个多分区的表中去

load data local inpath '/export/servers/hivedatas/score.txt' into table score2 partition(year='2018',month='06',day='01');

分区的增删改查

查看分区
show  partitions  score;

添加一个分区
alter table score add partition(month='201805');

同时添加多个分区
alter table score add partition(month='201804') partition(month = '201803');

删除分区
alter table score drop partition(month = '201806');

分桶表

将数据按照指定的字段进行分成多个桶中去，就是按照分桶字段进行哈希划分到多个文件当中去
分区就是分文件夹，分桶就是分文件

分桶优点

1. 提高join查询效率
2. 提高抽样效率

使用方法

开启hive的捅表功能

set hive.enforce.bucketing=true;

设置reduce的个数

set mapreduce.job.reduces=3;

创建桶表

create table course (c_id string,c_name string) clustered by(c_id) into 3 buckets;

注意

桶表的数据加载通过hdfs  dfs  -put文件或者通过load  data均不可以，

只能通过insert  overwrite 进行加载
所以把文件加载到桶表中，需要先创建普通表

修改表和删除表

修改表操作

-- 修改表名称
alter  table  old_table_name  rename  to  new_table_name;



-- 增加/修改列信息

查询表结构
desc score5;

添加列
alter table score5 add columns (mycol string, mysco string);

更新列
alter table score5 change column mysco mysconew int;

删除表操作

删除表操作
drop table score5;


清空表操作
truncate table score6;

注意

如果 hdfs 开启了回收站，drop 删除的表数据是可以从回收站恢复的，表结构恢复不了，需要自己重新创建；truncate 清空的表是不进回收站的，所以无法恢复truncate清空的表

hive表加载数据

直接向分区表中插入数据

insert into table score partition(month ='201807') values ('001','002','100');

通过load方式加载数据

load data local inpath '/export/servers/hivedatas/score.csv' overwrite into table score partition(month='201806');

通过查询方式加载数据

insert overwrite table score2 partition(month = '201806') select s_id,c_id,s_score from score1;

查询语句中创建表并加载数据

create table score2 as select * from score1;

在创建表是通过location指定加载数据的路径

create external table score6 (s_id string,c_id string,s_score int) row format delimited fields terminated by ',' location '/myscore';

hive表中数据导出

insert导出

将查询的结果导出到本地
insert overwrite local directory '/export/servers/tt' select * from score;

将查询的结果格式化导出到本地
insert overwrite local directory '/export/servers/tt' row format delimited fields terminated by '\t' collection items terminated by '#' select * from student;

将查询的结果导出到HDFS上(没有local)
insert overwrite directory '/export/servers/tt' row format delimited fields terminated by '\t' collection items terminated by '#' select * from score;

Hadoop命令导出到本地

dfs -get /export/servers/exporthive/000000_0 /export/servers/exporthive/local.txt;