HIVE汇总

最新推荐文章于 2024-08-12 09:57:55 发布

刘广睿

最新推荐文章于 2024-08-12 09:57:55 发布

阅读量1.3k

点赞数 1

分类专栏：大数据文章标签：优化 hive

本文链接：https://blog.csdn.net/a18302465887/article/details/78838355

版权

大数据专栏收录该内容

24 篇文章 2 订阅

订阅专栏

Beeline version 1.2.1 by Apache Hive
beeline> ! connect jdbc:hive2://node-1:10000

HIVE 优化（重点别不当回事）

方法一

    通过设置 set hive.groupby.skewindata = false，可以解决负载均衡问题。底层原理不清楚。使其不安装key的hash取模的方式，使其随机发送到reduce。减小负债均衡。

方法二 join操作

在执行join操作的时候，可以实现多个表之间的join操作。原则就是先将小表放在左边，因为左边会将数据加载到内存中，减小内存溢出的概率。其次就是小表的key相对来说比较分散，join任务执行的快。
比如一个字段为null，但是在进行分桶操作的时候，根据这个null的字段进行分桶，会将数据都集中放到一个reduce中。造成数据倾斜，因此可以将null变为一个字符串加上随机数去解决。

1、入门介绍

1.1 运行机制

HIVE 提供一个客户端窗口，内部有解析器、编译器、执行器、优化器，将程序员输入SQL语句转化为MR程序，通过JOB任务提交到Hadoop集群中去运行。
- 解析器将SQL语句转换存储到元素据库中，元数据信息是由MySQL数据库保存的
- 编译器将SQL语句转为MR程序
  。。。。

1.2 系统结构

数据最终存储在HDFS上面，元数据存储在derby中、mysql中。HIVE就是利用了HDFS存储能力，MR计算能力
HIVE的执行流程就是SQL经过解析器、编译器，读取MYSQL数据中的元数据信息。执行器、优化器将转换为一个MR程序，通过Yarn，HDFS数据进行数据的分析统计

1.3 HIVE与传统数据库区别

1.数据存储的位置，数据量不同
2.执行的SQL语句不同
3.执行的效率不同

1.4 HIVE内置derby数据库

derby是个内存数据，每次开启或者关闭数据会丢失。特点是简单、不需要配置可以直接使用，但是不支持远程调用。只能在自己的进程中完。

思考一

*当我在hive客户端创建一个数据库的时候，会在mysql数据库中创建一个数据库，同时也会在hdfs默认路径上面创建一个user/hive/datahouse/rui.db/，如果查看表内容的时候，看到null的字段的话就代表表识别不了文本数据的内容，查看字段类型

注意事项

（1）创建变的时候，最好指定可以识别的文本数据类型
```
create table day_table (id int, content string) partitioned by (dt string) row format delimited fields terminated by ',' 
```
（2）将数据加载到创建的表中加local 代表的linux本地，默认是hdfs上面，而且加载数据的文本名字就是数据在表目录下的名字。从hdfs上面加载数据的，数据会被剪切到表的结构中。
```
load  data inpath '/hive/data-hive' into table rui1  //默认从hdfs上面加载数据，
load  data local inpath '/hive/data-hive' into table rui1  //默认从linux本地上面加载数据   
```

2、表的创建

2.1 分区表的创建

分区就是为了辅助查询，减小查询的范围，通过一定条件/规格进行管理，类如国家，下面省、市、县等。
分区有两种类型，一个是一个层级的划分、另一个是多层级划分。

单表分区

create table day_table (id int, content string) partitioned by (dt string); 
LOAD DATA local INPATH '/root/hivedata/dat_table.txt' INTO TABLE day_table partition(dt='2017-07-07');

多表分区

create table day_hour_table (id int, content string) partitioned by (dt string, hour string);
LOAD DATA local INPATH '/root/hivedata/dat_table.txt' INTO TABLE day_hour_table PARTITION(dt='2017-07-07', hour='08');

2.2 分桶（默认-1）

就是将数据放入不同的桶（代表不同的表），分桶的字段可以跟表的字段重复；处获取更高的效率，使取样更加高效，减小笛卡儿积关联查询，方便对数据进行划分取样

分桶的好处就是可以将大表的中数据根据 clustered By（Sno）取模/桶数（底层走dMR程序），进行分别存放在，在执行jion连接的时候减小查询的次数，提供效率。记住clustered by 与 sort by 不能共存啊，因为分桶的时候会根据分桶字段进行排序。

步骤
第一步
set hive.enforce.bucketing = true; //开启分桶
set mapreduce.job.reduces=4; // 指定分桶 (默认也代表最大分桶数)

第二步 （按照字段进行分桶） 
create table stu_buck(Sno int,Sname string,Sex string,Sage int,Sdept string)
clustered by(Sno)            // 分桶，在表的下面分成三个文件 
sorted by(Sno DESC)          //这个排序没有作用
into 3 buckets               //具体分桶数以下面的为准
row format delimited
fields terminated by ',';    //分桶的字段,根据Sno的hash值取模

第三步 （导入数据）
load data inpath '/路径' into table stu_buck
insert overwrite table stu_buck
select * from student cluster by(Sno);   //开始真正的计算认为

2.3 外部表/内部表

默认情况下创建的是内部表，数据会移动到仓库中，当删除表的时候数据也删除了！当创建外部表的时候，只会记录数据所在的位置，不会数据进行任何改变。
在创建外部表的时候需要指定 external关键字，并且指定location关键字，加载外部数据路径，数据一定在hDFS上面；表的关系是在mysql中。HIVE仓库没有。
```
create external table student_ext(Sno int,Sname string,Sex string,Sage int,Sdept string) row format delimited fields terminated by ',' location '/stu';
```
like 复制的表结构，但是不会复制数据

2.4 select load 查询

load 将数据进行移动，insert 底层是mr

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO  table  rui
overwrite 数据  指定新路径下面的数据会覆盖原表中的数据，也就是删除原表中的数据，添加数据到新表中。

2.5 多重插入

将A表中的字段age，name等字段信息，将age添加到B表中，将name添加到C表中，具体语法见下面

注意事项： source_table表中字段数目与test_insert1，test_insert2字段数量一致

create table source_table (id int, name string) row format delimited fields terminated by ',';
create table test_insert1 (id int) row format delimited fields terminated by ',';
create table test_insert2 (name string) row format delimited fields terminated by ',';  

from source_table                     
insert overwrite table test_insert1 
select id  // 将source_table 表中的id字段添加到test_insert1中 这个格式记住就可以了
insert overwrite table test_insert2
select name;

2.6 动态分区插入（看着有点点乱）

将dynamic_partition_table中的字段，

需求：
将dynamic_partition_table中的数据按照时间(day)，插入到目标表d_p_t的相应分区中。

原始表：
create table dynamic_partition_table(day string,ip string)row format delimited fields terminated by ","; 

load data local inpath '/root/hivedata/dynamic_partition_table.txt' into table dynamic_partition_table;
2015-05-10,ip1
2015-05-10,ip2
2015-06-14,ip3
2015-06-14,ip4
2015-06-15,ip1
2015-06-15,ip2

目标表：
create table d_p_t(ip string) partitioned by (month string,day string);

动态插入：
insert overwrite table d_p_t partition (month,day) 
select ip,substr(day,1,7) as month,day  //月与天 映射为partition分区
from dynamic_partition_table;  


将查询的结果保存到HDFS/本地linux系统中，加local代表保存到linux中
insert overwrite local directory '/home/hadoop/test/'  //这个路径必须是空，否则会造成本地文件丢失
select * from t_p;  //保存到本地的操作也是调用MR程序去执行，分区数就是对应表创建时的分区，但是所有数据都在一起啊没办法分开吗？

insert overwrite directory '/aaa/test'
select * from t_p;

2.7 查询语句

order by 根据选择的字段进行全局排序，既然是全局的话只有一个reduce
sort by 根据同步的reduce进行排序
disturbute by 只是分桶，不排序，key的hash值
cluster by 分桶且排序
cluster by = distribute by + sort by

2.8 HIVE join操作

1、HIVE 在执行join操作的时候，只支持等值链接。原因底层要转化为MR，最后到reduce中也就是一个分区中。关键字left semi join和cross join操作。

left semi join（hive特殊的链接方式），只返回on条件成功的，但是只显示a表中的内容。相当于inner join的a表内容

select * from a left semi join b on a.id = b.id

    | a.id  | a.name  |
    +-------+---------+--+
    | 2     | b       |
    | 3     | c       |
    | 7     | y       |

cross join 返回连个表的笛卡尔积结果，且不需要指定字段,效率最低
```
select a.*,b.* from a cross join b  
```

内链接，只是返回匹配成功的表内容

select * from a  inner join b on a.id = b.id  //根据on的条件进行匹配

left/right join ，以左表为主表，左边内容全部出现，匹配成功返回内容，失败返回null字段

select * from a left join b on a.id = b.id  //以左边的表为主表，匹配右边 如果没有匹配成功就返回null

full outer join （就是全部展示，对于）

3.HIVE函数

运算符
内置函数
- subStr（’sdfds’,-1,2） //默认是从1开始所有小于1都是1开始取2位
HIVE自定义函数，UDF （user define fuction），python也可以实现自定义函数
- 1、实现步骤
  - 创建一个JAVA项目添加hadoop-common-2.6.4.jar，hive-exec-1.2.1.jar
  - 自定义类继承UDF
  - 重载方法evaluate，方法实现后（方法要自定义为public），打成jar导出
  - 在hive客户端 add jar /jar的路径
  - 调用方式就可以了