漫谈大数据 - HiveSQL总结（一）库表操作

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

hive简介：

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

目录

数据库操作

创建数据库

修改数据库

查看数据库详细信息

删除数据库

数据表操作

内部表

建表

字段类型

查询表的结构

外部表

建外部表

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

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

分区表

建分区表

加载数据

分区的增删改查

分桶表

分桶优点

使用方法

修改表和删除表

修改表操作

删除表操作

hive表加载数据

直接向分区表中插入数据

通过load方式加载数据

通过查询方式加载数据

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

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

hive表中数据导出

insert导出

Hadoop命令导出到本地

---

数据库操作

创建数据库

-- 创建数据库
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;

欢迎点赞收藏评论交流~

hive的DQL查询语法以及hive函数总结请至下篇

漫谈大数据 - HiveSQL总结（二）查询操作_昊昊该干饭了的博客-CSDN博客导语：HiveSQL各关键字详解，hive函数大全，类似于个人记录工具书，后续遇到其他的也会继续加进来。https://blog.csdn.net/qq_52213943/article/details/130289418?spm=1001.2014.3001.5501