Hive入门详解

[successbox title=”什么是HIVE”]

Hive是一个语句Hadoop的一个数据仓库工具，是将结构化数据文件映射成为一个数据表，并提供类SQL的查询功能。

[/successbox]

[infobox title=”Hive可以对数据进行存储于计算”]

​ 数据存储依赖于HDFS

​ 数据计算依赖于MapReduce

[/infobox]

[dangerbox title=”Hive意义（目的）”]

​ 在hadoop是个好软件，但是不好使用（学习成本太高，坡度陡，难度大）的前提下

​ 降低了程序员使用hadoop的学习成本，降低了难度。

[/dangerbox]

[successbox title=”Hive特点”]

可扩展性：与集群的扩展性相同

​ 延展性：Hive提供自定义函数接口，支持HQL语句直接调用java方法

​ 容错性：节点出现问题SQL仍可完成执行。

[/successbox]

[infobox title=”Hive架构”]

[/infobox]

[warningbox title=”元数据”]

描述数据的数据就是元数据
表的名字，
表的列
列的类型

[/warningbox]

[warningbox title=”Hive内部执行过程”]

解释器 -> 编译器（会使用到元数据） -> 优化器 -> 执行器

[/warningbox]

[infobox title=”Hive基本介绍”]

Hive中没有定义专门的数据格式，用户提供的数据是什么格式，hive直接将数据拷贝到集群。不会对数据格式进行改变。

​ 用户定义数据格式需要指定三个属性：

​ 列分隔符（通常为空格、”\t”、”\x001″）、

​ 行分隔符（”\n”）

​ 读取文件数据的方法（Hive 中默认有三个文件格式 TextFile，SequenceFile 以及 RCFile）

​ 不会对数据本身进行任何修改，甚至不会对数据进行扫描。

​ Hive 中不支持对数据的改写和添加（在 一个文本中添加新数据）

​ Hive 在加载数据的过程中不会对数据中的某些 Key 建立索引。

总结：hive具有sql数据库的外表，但应用场景完全不同，hive只适合用来做批量数据统计分析

[/infobox]

[dangerbox title=”Hive内的基本概念”]

​ DB、数据库

​ Table， 表（内部表）

​ External Table， 外部表

​ Partition，分区

​ Bucket。分桶

Hive支持的数据格式

​ 可支持Text， SequenceFile ，ParquetFile，ORC格式RCFILE等

[/dangerbox]

[warningbox title=”Hive的安装部署”]

1、 derby版hive

解压后直接执行bin/hive

​ 默认使用derby（数据库）维护元数据

​ 此版本，每个节点自己独立维护一个derby数据库，所以在节点1添加了数据库，在节点2 无法查看

​ 2、使用mysql共享hive元数据

​ 安装mysql

​ yum install -y mysql mysql-server mysql-devel

​ 启动mysql

​ /etc/init.d/mysqld start

​ chkconfig mysqld on

​ 配置远程连接

​ grant all privileges on . to 'root'@'%' identified by '123456' with grant option;

​ 配置root用户的登录密码

​ update user set password=password('123456') where user='root';

​ 刷新配置‘

​ flush privileges;

​ 使用远程连接工具连接数据库

​ 安装hive

​ 编辑hive-env.sh

​ 配置 HADOOP_HOME

​ 配置HIVE_CONF_DIR

​ 编辑 Hive-site.xml

​ 配置hive需要连接的mysql数据库

​ 添加mysql驱动包到hive的lib文件夹内

​ 最后修改环境变量

​ export HIVE_HOME=/export/servers/hive-1.1.0-cdh5.14.0 ​ export PATH=$PATH:$HIVE_HOME/bin

​ source /etc/profile

 

后台启动:

hivemetastore     nohup hive –service metastore &

hiveserver2        nohup  hive –service hiveserver2 &

[/warningbox]

[infobox title=”使用HIVE的方式”]

1、安装Hive后配置系统环境变量的前提下，在节点的任意位置直接数据如Hive+ 回车

​ hive (default)> ​ > ​ >

​ 2、启动hiveserver2

​hive --service hiveserver2  或者  hiveserver2​

beeline连接hiveserver2

​ 执行beeline + 回车

​ beeline> ​ beeline> ​ beeline>

​ 连接服务

​ !conncet jdbc:hive2://node01:10000

​ 输入用户名和密码（自己定义）

​ Transaction isolation: TRANSACTION_REPEATABLE_READ ​ 0: jdbc:hive2://node01:10000> ​ 0: jdbc:hive2://node01:10000> ​ 0: jdbc:hive2://node01:10000> show databases;

​ INFO : OK ​ +----------------+--+ ​ | database_name | ​ +----------------+--+ ​ | default | ​ | myhive | ​ +----------------+--+

 
后台启动:
hivemetastore     nohup hive –service metastore &
hiveserver2        nohup  hive –service hiveserver2 &
可通过web访问10002端口验证hiveserver2是否启动
[hide reply_to_this=”true”]

启动metastore
nohup hive --service metastore  2>&1 & 

启动hiveserver2
nohup hive --service hiveserver2   2>&1 & 

[/hide]
[/infobox]
[warningbox title=”通过参数使用Hive”]

​ hive -e ‘命令’

​ hive -e 'show databases;'

hive -f 文件（文件内编写造作命令）
​ hive -f test.sql

[/warningbox]

[dangerbox title=”数据库的相关操作（增删改查）”]

增：create database [if not exists] 库名；

​ 删： drop database 库名；（若数据库内表，那么不允许直接删除，需要先清空所有表在删除）

​ 改: hive 不支持数据库修改；

​ 查： show databases;

​ 查看数据库详细信息：

​ desc database myhive2;

​ desc database extended myhive2;

​ 数据库切换： use 库名；

hive数据存储在HDFS中，hive的库、表、分区都是以文件夹的形式存在。

[/dangerbox]

[warningbox title=”数据表的相关操作”]

建表语句

​ 内部表

​ create table [IF NOT EXISTS] 表名；

​ 外部表

​ create EXTERNAL table [IF NOT EXISTS] 表名；

​ 内部表与外部表的区别

​ 内部表在删除表时，表的元数据与数据同时被删除。

​ 外部表在删除表时，表的元数据被删除，数据不删除。

​

​ 指定导入表的数据列与列的分隔符

​ ROW FORMAT DELIMITED FIELDS TERMINATED BY char （char 分隔符）

​ STORED AS 数据上传到HDFS以什么格式进行存储（SEQUENCEFILE | TEXTFILE | RCFILE）

[/warningbox]

[successbox title=”Hive支持的数据类型”]

基本数据类型

​BOOLEA TINYINT SMALLINT INT BIGINT FLOAT DOUBLE DEICIMAL STRING VARCHAR CHAR BINARY TIMESTAMP DATE INTERVAL

​ 符合数据类型

ARRAY MAP STRUCT UNION

hive初体验

create table if not exists stu2(id int ,name string) row format delimited fields terminated by ‘\t’ stored as textfile location ‘/user/stu2’;

[/successbox]

[warningbox title=”外部表”]

​ 创建外部表

​ create external table techer (t_id string,t_name string) row format delimited fields terminated by ‘\t’;

​ create external table student (s_id string,s_name string,s_birth string , s_sex string ) row format delimited fields terminated by ‘\t’;

​ 数据加载语句

​ load data local inpath ‘/export/servers/hivedatas/student.csv’ into table student;

​ ​load data inpath ‘/hivedatas/techer.csv’ into table techer;

[/warningbox]

[successbox title=”验证删除表后数据是否还在”]

drop table techer;

​ drop table student;

​ 删除后表被删除，数据依然存在

[/successbox]

[dangerbox title=”内部表”]

创建内部表

​ create table student1 (s_id string,s_name string,s_birth string , s_sex string ) row format delimited fields terminated by ‘，’;

​ 数据加载语句

​ load data local inpath ‘/export/servers/hivedatas/student1.csv’ into table student;

验证删除表后数据是否还在

​ drop table student1;

​ 删除后表被删除，数据也被删除

[/dangerbox]

[warningbox title=”分区表”]

​ 创建分区表

​ create table score2 (s_id string,c_id string, s_score int) partitioned by (year string,month string,day string) row format delimited fields terminated by ‘\t’;

​ 加载数据

​ load data local inpath ‘/export/servers/hivedatas/score.csv’ into table score2 partition(year=’2018′,month=’06’,day=’01’);

​ load data local inpath ‘/export/servers/hivedatas/score.csv’ into table score2 partition(year=’2018′,month=’06’,day=’02’);

​ 重点说明：

​ 分区字段绝对不能出现在表已有的字段内。

​ 作用：提高查询效率

[/warningbox]

[successbox title=”分桶表”]

1、开启分桶功能

​ set hive.enforce.bucketing=true;

​ 2、设置桶的数量

​ set mapreduce.job.reduces=3;

​ 3、创建分桶表

​ create table course (c_id string,c_name string,t_id string) clustered by(c_id) into 3 buckets row format delimited fields terminated by ‘\t’;

​ 4、数据加载

​ 4.1 创建普通表

​ create table course_common (c_id string,c_name string,t_id string) row format delimited fields terminated by ‘\t’;

​ 4.2 普通表添加数据

​ load data local inpath ‘/opt/hive/course.csv’ into table course_common;

​ 4.3 在普通表查询数据插入到分桶表

​ insert overwrite table course select * from course_common cluster by(c_id);

​ 5、验证分桶数据

​ [root@node01 hive]# hadoop fs -ls /user/hive/warehouse/hivedatabase.db/course/ Found 3 items -rwxr-xr-x 3 root supergroup 13 2019-11-21 01:56 /user/hive/warehouse/hivedatabase.db/course/000000_0 -rwxr-xr-x 3 root supergroup 13 2019-11-21 01:56 /user/hive/warehouse/hivedatabase.db/course/000001_0 -rwxr-xr-x 3 root supergroup 13 2019-11-21 01:56 /user/hive/warehouse/hivedatabase.db/course/000002_0 [root@node01 hive]# [root@node01 hive]# hadoop fs -cat /user/hive/warehouse/hivedatabase.db/course/000000_0 03 英语 03 [root@node01 hive]# hadoop fs -cat /user/hive/warehouse/hivedatabase.db/course/000001_0 01 语文 02 [root@node01 hive]# hadoop fs -cat /user/hive/warehouse/hivedatabase.db/course/000002_0 02 数学 01

重点说明：

​ 分桶字段必须出现在表已有的字段内。

分桶逻辑：

​ 对分桶字段取哈希，用这个哈希值与桶的数量取余，余几，这个数据就放在哪个桶内。

​ 作用：提高join效率和用于数据取样。

​ 提高join效率：将join关联的字段作为分桶字段。相同的数据汇入到一个桶内，在join时直接读取桶内的所有数据，不用全表扫描。

​ 数据取样：将数据编号作为分桶字段，与分桶数量取余。这样可以讲数据打散，分到不同的桶内。那么每个桶内的数据包含各个“阶段”的数据。

[/successbox]

[warningbox title=”Hive的自定义函数”]

查看函数：show functions;

​ 查看函数的用法： desc function upper;

​ 详细用法： desc function extended upper;

自定义函数种类

​ UDF UDAF UDTF

一进一出 多进一出 一进多出

 

UDF

​ 1 创建一个类 继承UDF 实现evaluate方法，编写自己的业务逻辑代码

​ 2、打包上传到集群（linux）

​ 3、 让Hive能够找到这个jar文件

​ 在hive的shell窗口 add jar 路径+jar包

​ 4、创建临时 函数

​ create temporary function 函数名 as 包名+类名

​ 5、调用新的函数

​ hive (default)> select tolower(‘CCCCCC’); ​ OK ​ _c0 ​ cccccc

[/warningbox]

[dangerbox title=”HIVE通过reflect调用纯java代码”]

1、使用java 编写业务代码，打包上传（linux）

​ 2、让hive 能够找到jar

​ 在hive的shell窗口 add jar 路径+jar包

​ 3、调用

​ select reflect(‘参数一’,’参数二’,’参数三’);

​ 参数一： 包名+类名

​ 参数二： 方法名

​ 参数三：传入的数据

​ 例如：

​ hive (default)> select reflect(‘demo03′,’text’,’qq’); ​ OK ​ _c0 ​ qq —–

[/dangerbox]

[successbox title=”Hive修改表”]

alter table old_table_name rename to new_table_name;

[/successbox]

[infobox title=”添加修改列的信息”]

添加列

​ alter table score5 add columns (mycol string, mysco string);

​ 更新列

​ alter table score5 change column mysco mysconew int;

[/infobox]

[successbox title=”Hive的基本操作”]

1、表数据的导入

​ 有5种方式

​ 1 直接向分区表中插入数据

​ insert into table score3 partition(month =’201807′) values (‘001′,’002′,’100’);

​ 2、通过查询插入数据

​ insert overwrite table score4 partition(month = ‘201806’) select s_id,c_id,s_score from score;

​ 3、多插入模式

​ from score

​ insert overwrite table score_first partition(month=’201806′) select s_id,c_id

​ insert overwrite table score_second partition(month = ‘201806’) select c_id,s_score;

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

​ create table score5 as select * from score;

​ 5、创建表时通过location指定加载数据路径

​ create external table score6 (s_id string,c_id string,s_score int) row format delimited fields terminated by ‘\t’ location ‘/myscore6’;

​ 2、表数据的导出

​ 有7种方式

​ 1 将查询的结果导出到本地

​ insert overwrite local directory ‘/export/servers/exporthive/a’ select * from score;

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

​ 3、将查询的结果导出到HDFS上(没有local)

​ insert overwrite directory ‘/export/servers/exporthive’ row format delimited fields terminated by ‘\t’ collection items terminated by ‘#’ select * from score;

​ 4、Hadoop命令导出到本地

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

​ 5、hive shell 命令导出

​ bin/hive -e “select * from myhive.score;” > /export/servers/exporthive/score.txt

​ 6、 export导出到HDFS上 ​ export table score to ‘/export/exporthive/score’;

​ 7、 sqoop 导出数据

​ 3、清空表数据

​ truncate table score5;

[/successbox]

[dangerbox title=”Hive 常用查询语法”]

SELECT 字段名A，字段名B from 表明

[/dangerbox]

[warningbox title=”Hive参数优先级”]

三种方法

​ 1、配置文件（配置文件参数）

​ 2、hive -hiveconf （命令行参数）

​ 3、在hive shell窗口 设置（ 参数声明）

​ set mapred.reduce.tasks=100;

优先级： 参数声明 > 命令行参数 > 配置文件参数（hive））

[/warningbox]

[successbox title=”Hive的压缩：Snappy最快”]

[/successbox]

[dangerbox title=”Hive支持的数据格式”]

​ 可支持Text， SequenceFile ，ParquetFile，ORC格式RCFILE等

[/dangerbox]

[successbox title=”存储于压缩结合”]

在实际的项目开发当中，hive表的数据存储格式一般选择：orc或parquet。压缩方式一般选择snappy。

[/successbox]

[warningbox title=”Hive优化”]

1、fetch抓取优化

​ 简单查询不转化MR, set hive.fetch.task.conversion=more;

​ 简单查询转化MR, set hive.fetch.task.conversion=none;

2、本地模式

​ 本地计算：数据存储后，计算这批数据的程序已经写完，程序在进行分发时，优先将程序分发到程序所用到数据所在的节点。

​ 本地查询：查询数据的程序运行在提交查询语句的节点上运行（不提交到集群上运行）。

​ 好处：提高查询效率。

​ 数据倾斜：是指数据在一个维度上有非常大量维度的差异。

​ 3、数据倾斜局部聚和

​ 当发生数据倾斜时，使用局部聚和可以起到性能调优的效果（在Map端进行聚合）。

​ 当发生倾斜时，查询语句会转化成至少两个MR程序，第一个程序进行局部聚和，第二个MR程序进行最终聚和。

4、Count(distinct)去重求总数

​ SELECT count(DISTINCT id) FROM bigtable;

​ 优化方案

​ 使用嵌套查询（先对id 分组，再求需的数量）

​ SELECT count(id) FROM (SELECT id FROM bigtable GROUP BY id) a;

​ 5、笛卡尔积

​ 避免join的时候不加on条件

6、分区剪裁、列剪裁

​ 什么是分区剪裁：用哪个分区，获取哪个分区的数据，多的不要获取。

​ 什么是列剪裁：用哪个列，获取哪个列的数据，多的不要获取。

​ 先join 后过滤优化方案

​ join时通常是先join 后过滤优化方案是先过滤后join

​ 例如

​ SELECT a.id FROM bigtable a LEFT JOIN ori b ON a.id = b.id WHERE b.id <= 10;

​ 优化方案

​ 1、SELECT a.id FROM ori LEFT JOIN bigtable b ON (b.id <= 10 AND a.id = b.id);

​ 2、 SELECT a.id FROM bigtable a RIGHT JOIN (

​ SELECT id FROM ori WHERE id <= 10

​ ) b ON a.id = b.id;

7、动态分区调整

​ 以第一个表的分区规则，来对应第二个表的分区规则，将第一个表的所有分区，全部拷贝到第二个表中来，第二个表在加载数据的时候，不需要指定分区了，直接用第一个表的分区即可

​

​ 8、数据倾斜

​ 当有数据倾斜时如何解决。

​ 1、设置reduce数量60，使用ID ,对ID进行分区distribute by

​ 2、设置reduce数量60, 使用distribute by 字段为随机数 select * from a distribute by rand();

​

​ 9、reduce数量

​ 决定reduce数量的因素，

​ 参数1：每个Reduce处理的数据量

​ 参数2：每个任务最大的reduce数

​ 计算reducer数的公式

​ N=min(参数2，总输入数据量/参数1)

10、并行执行

​ 在转换后的各个阶段。没有依赖的前提下，可以开启并行执行（多任务多阶段同时执行），一起到优化执行效率的作用。

11、严格模式

​ 1、用户不允许扫描所有分区

​ 2、使用了order by语句的查询，要求必须使用limit语句。

​ 3、限制笛卡尔积的查询。

​ 12、JVM重用

​ 没有开启jvm重用，每个task都需要独立的开启、关闭jvm(开启、关闭需要1s),任务的开销会很大

​ 开启jvm重用，启动阶段开启部分jvm,这些jvm不关闭，等待被使用，后面的任务需要使用jvm时直接调用,就任务的开销会很小

13、推测执行

​

[/warningbox]