Hive使用(3.1.3)

Hive使用(3.1.3)

1.hive shell常用命令

#查看当前所有表
show tables;
#创建分区表，并指定列分隔符为','，行分隔符为'\n'，如果备注出现乱码，则修改hive的元数据表TABLE_PARAMS、COLUMNS_V2对应备注字段编码集为utf8即可
create table t1(id int comment '主键',name string comment '名称',favors array<string>,scores map<string,int>,
address struct<home_addr:string,office_addr:string>) comment '测试表'
partitioned by (year int,school string)
row format delimited fields terminated by ';' 
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n'
stored as textfile;
#创建桶表
create table t2(id int) clustered by (id) into 4 buckets;
#查看表数据
select id,name,favors[0],scores['math'],scores['english'],address.office_addr from t1;
#删除表
drop table t1;
#显示当前所有数据库
show databases;
#选择数据库
use default;
#创建数据库
create database db1;
#创建数据库并指定hdfs路径
create database db2 location '/user/hive/mydb2';
#删除数据库
drop database db2;
#查看表信息
desc t1;
#查看建表语句
show create table t1;
#修改表名
alter table t1 rename to t1_bak;
#加载本地文件到表t1（将数据文件直接上传到对应的表目录下，等同于hdfs -put命令）,分区不存在则自动创建
load data local inpath '/home/hive/hivedata/data1.data' into table t1 partition(year=2021,school="cqschool");
#增加表字段
alter table t1 add columns(name string);
#内部表转换为外部表
alter table t1 set tblproperties('EXTERNAL' = 'TRUE');
#添加分区
alter table t1 add partition (year=2021,school="cqschool");
#添加分区并关联数据
alter table t1 add partition (year=2021,school="cqschool") location "/xxx/xxx/xxxx";
#删除分区
alter table t1 drop partition (year=2021,school="cqschool");
#查看分区
show partitions t1;
#1.开启分桶
set hive.enforce.bucketing=true;
#2.写入桶表数据（不能直接用load data,无法分桶）
insert into table t2 select id from t2_source;
#采样，跟当前分桶无关，会重新进行分桶输出，2：将t2中的数据随机分成多少桶，1：取第几桶（从1开始）
select * from t2 tablesample(bucket 1 out of 2 on id);
#创建视图
create view v2 as select * from t2 where id = 8;
#删除视图
drop view v2;

2.hive常用数据类型

类型	描述	示例
TINYINT	1个字节 （8位）有符号整数	1
SMALLINT	2字节（16位）有符号整数	1
INT	4字节（32位）有符号整数	1
BIGINT	8字节（64位）有符号整数	1
FLOAT	4字节（32位）单精度浮点数	1.0
DOUBLE	8字节（64位）双精度浮点数	1.0
DECIMAL	高精度浮点数	1.0
TIMESTAMP	yyyy-MM-dd HH:MM:SS	2017-11-13 12:00:00
DATE	yyyy-mm-dd	2014-04-03
BOOLEAN	true/false	true
STRING	字符串	”xia”
ARRAY	ARRAY	array泛型
MAP	MAP	MAP泛型key value
STRUCT	STRUCT	MAP泛型 列名1:数据类型1,列名2:数据类型2

3.hive常用文件格式

• Text File
  存储为纯文本文件，这是 Hive 默认的文件存储格式，这种存储方式数据不做压缩，磁盘开销大，数据解析开销大；
  一般只有与其他系统由数据交互的接口表采用TEXTFILE 格式，其他事实表和维度表都不建议使用；
• SequenceFile
  SequenceFile是Hadoop API 提供的一种二进制文件，它将数据以<key,value>的形式序列化到文件中。这种二进制文件内部使用Hadoop 的标准的Writable 接口实现序列化和反序列化。它与Hadoop API中的MapFile 是互相兼容的。Hive 中的SequenceFile 继承自Hadoop API 的SequenceFile，不过它的key为空，使用value 存放实际的值， 这样是为了避免MR 在运行map 阶段的排序过程。
  SequenceFile支持三种压缩选择：NONE, RECORD, BLOCK。 Record压缩率低，一般建议使用BLOCK压缩。 SequenceFile最重要的优点就是Hadoop原生支持较好，有API，但除此之外平平无奇，实际生产中不会使用。
• RCFile
  RCFile 文件格式是 FaceBook 开源的一种 Hive 的文件存储格式，一种行列存储相结合的存储方式。首先将表分为几个行组，对每个行组内的数据按列存储，每一列的数据都是分开存储。
• ORC Files
  ORC 是在一定程度上扩展了 RCFile，是对 RCFile 的优化。数据按照行分块，每个块按照列存储，其中每个块都存储有一个索引。hive给出的新格式，属于RCFILE的升级版,性能有大幅度提升,而且数据可以压缩存储,压缩快 快速列存取。
• Avro Files
  Avro 是一个数据序列化系统，设计用于支持大批量数据交换的应用。它的主要特点有：支持二进制序列化方式，可以便捷，快速地处理大量数据；动态语言友好，Avro 提供的机制使动态语言可以方便地处理 Avro 数据。
• Parquet
  Parquet也是一种行式存储，同时具有很好的压缩性能；同时可以减少大量的表扫描和反序列化的时间。
  Parquet 是基于 Dremel 的数据模型和算法实现的，面向分析型业务的列式存储格式。它通过按列进行高效压缩和特殊的编码技术，从而在降低存储空间的同时提高了 IO 效率。

总结： 以上压缩格式中 ORC 和 Parquet 的综合性能突出，使用较为广泛，推荐使用这两种格式。指定存储格式，通常在导入ORC 和 Parquet之前先创建Text File临时表，然后通过insert into select来导入到ORC 和 Parquet格式表中；
4.hive表类型

• 内部表
  hive中的默认表类型，表数据默认存储在warehouse目录中；
  在加载数据的过程中，实际数据会被移动到warehouse目录中；
  删除表时，表中的数据和元数据都将会被同时删除；
• 外部表
  建表语句中包含External的表叫外部表；
  外部表在加载数据的时候，实际数据并不会移动到warehouse目录中，只是与外部数据建立一个链接（映射关系）；
  当删除一个外部表时，只删除元数据，不删除表中的数据，仅删除表和数据之间的链接；
• 分区表
  分区可以理解为分类，通过分区把不同类型数据放到不同目录；
  分区的标准就是指定分区字段，分区字段可以有一个或多个；
  分区表的意义在于优化查询，查询时尽量利用分区字段，如果不使用分区字段，就会全表扫描（比如凹按天分区）；
• 桶表
  桶表是对数据进行hash取值，然后放到不同文件中存储；
  物理上，每个桶就是表（或分区）里的一个文件；
  桶表的作用主要是数据抽样、提高某些查询效率，比如部分join（当join关联字段是同一个字段hash时，相同id数据在同一个分区）；
• 视图
  使用视图可以降低查询的复杂度；
  5.查看当前用户操作历史

tail -10 ~/.hivehistory

6.hive元信息表描述

表名	描述
DBS	数据库信息
TBLS	表信息
COLUMNS_V2	表的列信息