hive的基础知识
数据仓库的基本介绍：
1、数据仓库的基本概念
名字叫做data warehourse 数据仓库
仓库：主要用于存储东西的，不会生产东西，也不会消耗东西
数据仓库：不会产生任何的数据，也不会消耗任何的数据，只是用于存储这些数据
主要用于分析性报告和决策支持
2、数据仓库的主要特征：
面向主题：有确切的分析目标
集成性：相关的数据都会弄到数据仓库当中来，便于我们下一步的分析
非易失性：数据一旦进入到数据仓库之后，不会轻易的改变
时变性：根据一些不同的指标求取，会产生不同的一些分析维度
3、数据库与数据仓库的区别：
数据库：OLTP On-Line Transaction Processing 联机事务处理 主要用于我们业务数据库当中的增删改查
数据仓库 OLAP On-Line Analytical Processing 联机分析处理 主要用于我们的数据的分析查询等操作，操作的都是历史数据，不会新增 也不会修改，更加不会删除数据
最基本的区别：数据仓库主要用于统计分析，数据库主要用于事务处理
kettle：来实现数据仓库的开发，主要是用于处理数据库当中的数据
4、数据仓库的分层：数据仓库主要分为三层
源数据层：贴源层 ODS层 主要是获取我们的源数据
数据仓库层：DW层 主要对我们的贴源层的数据进一步的分析，得出我们想要的结果
数据应用层：APP层 主要对我们应用层分析之后得到的结果做进一步的展示
数据在各个层级之间流动的一个过程，可以称之为ETL过程 （抽取Extra, 转化Transfer, 装载Load）的过程
5、数据仓库的元数据管理：主要用于记录数据库表之间的关系，数据库表字段的含义等等
还有包括一些数据处理的规则，数据装载的周期，数据导出的周期等等
hive的基本介绍：
1、hive是什么：基于hadoop的一个数据仓库的工具。可以hdfs上面结构化的数据映射成为一张表
数据结构：
结构化的数据：字段个数一定，字段之间的分隔符一定
半结构化的数据：例如xml，json等
非结构化的数据：没有任何规律格式的数据
hive底层的数据存储都是使用HDFS，数据的统计计算都是使用的MapReduce,
说白了可以将hive理解为一个MapReduce的客户端工具，你写的hql语句会翻译成MapReduce的任务去执行
2、为什么要使用hive 写MR太复杂了，不会，sql相对简单一些
3、hive一些特点：
可扩展：扩展的是我们的hadoop集群
延展性：支持用户的自定义函数
容错性：良好的容错
hive的架构：
用户接口：主要是为了我们编辑sql语句，然后提交给hive
解析器：包含三大块：
编译器：主要将我们的sql语句进行编译成一个MR的任务
优化器：主要是对我们的sql语句进行优化
执行器：提交mr的任务，进行执行
元数据库：hive的元数据 表与hdfs数据之间的映射关系 默认使用的是derby，一般都会改成mysql
hive的安装：使用mysql作为元数据存储
mysql使用yum源在线的方式进行安装
hive的配置文件的修改
hive-env.sh:
HADOOP_HOME=/export/servers/hadoop-2.6.0-cdh5.14.0
export HIVE_CONF_DIR=/export/servers/hive-1.1.0-cdh5.14.0/conf
注意：一定要指向到我们hive的配置文件的路径
hive的交互方式：
第一种方式 bin/hive
第二种方式：使用beeline客户端来进行连接
启动服务端hiveserver2
bin/hive --service hiveserver2 前台启动
nohup bin/hive --service hiveserver2 2>&1 & 进程后台启动
启动客户端，使用beeline的方式连接我们的服务端
bin/beeline
beeline> !connect jdbc:hive2://node03.hadoop.com:10000
输入用户名root 密码 123456即可连接上
第三种交互方式：使用hive -e不进入hive客户端直接执行sql语句
bin/hive -e “use myhive;select * from test;”
bin/hive -f hive.sql 通过 -f 指定我们需要执行的sql脚本文件
hive的基本操作：
创建数据库与创建数据库表操作
创建数据库操作：create database if not exists xxx;
创建数据库表的操作：
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 创建表的三个关键字段
[(col_name data_type [COMMENT col_comment], …)] 定义我们的列名，以及列的类型
[COMMENT table_comment] 注释信息，只能用英文或者拼音，不接受中文
[PARTITIONED BY (col_name data_type [COMMENT col_comment], …)] 分区：这里的是hive的分区，分的是文件夹
[CLUSTERED BY (col_name, col_name, …) 分桶：按照字段进行划分文件
[SORTED BY (col_name [ASC|DESC], …)] INTO num_buckets BUCKETS] stored by 划分到多少个桶里面去
[ROW FORMAT row_format] 指定字段之间的分隔符
[STORED AS file_format] 数据的存储格式为哪一种
[LOCATION hdfs_path] 指定我们这个表在hdfs的哪一个位置
hive当中的第一种表模型：管理表 又叫做 内部表
最明显的一个特征：删除表的时候，会把hdfs的数据同步删除
创建内部表的语法 create table stu (id int ,name string);
注意创建内部表不要带external关键字
创建内部表：指定分隔符，指定文件存储格式，指定hdfs的存放位置
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; 这种语法就会把我们stu2里面的数据以及表结构都复制到stu3里面来
复制表结构
create table stu4 like stu2; 只会把stu2的表结构复制给stu4 ,不会复制数据
hive当中的第二种表模型：外部表 外部表删表是不会删除数据的，创建外部表的关键字 external
外部表的数据都是指定hdfs的文件路径加载进来的，外部表认为自己没有独享这份数据，所以删除外部表的时候，不会同步删除hdfs的数据
与内部表的特征针锋相对，删除表的时候，不会删除表数据
创建外部techer表，并且指定字段之间的分隔符
create external table techer(t_id string,t_name string) row format delimited fields terminated by '\t';
创建外部stduent表，并且指定字段之间的分隔符
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]
从本地文件系统加载数据： load data local inpath '/export/servers/hivedatas/student.csv' into table student; 实质上就是把本地文件上传到外部表对应的文件夹下面去了
从hdfs文件系统加载数据： load data inpath '/hivedatas/techer.csv' into table techer; 实质上就是把hdfs的文件移动到表的文件夹下面去了
hive当中的第三种表模型：分区表 就是分文件夹，可以按照时间，或者其他条件，创建一些文件夹
创建一个字段的分区表：关键词就是partitioned by
create table score(s_id string,c_id string,s_score int) partitioned by (month string) row format delimited fields terminated by '\t';
创建一个多分区的字段
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 score partition (month='201806');
加载数据到多分区表当中去：从本地文件系统加载 ：load data local inpath '/export/servers/hivedatas/score.csv' into table score2 partition(year='2018',month='06',day='01');
查看表分区：show partitions tableName
添加一个分区 alter table score add partition(month='xxx')
删除分区：alter table score drop partition(month='xxx')
hive当中的第四种表模型：分桶表 就是分文件 与mr当中的分区类似
创建分桶表的语法 关键字：clustered by (col_name) into xx buckets
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';
分桶表当中不能直接通过load的方式加载数据，需要我们通过 insert overwrite table course select * from course_common cluster by (c_id);
实际工作当中，如果数据量实在比较大，可以通过分桶加分区的方式一起来创建表 可以考虑创建分区分桶表
hive表当中加载数据：
load data
通过load的方式加载数据 load data local inpath '/export/servers/hivedatas/score.csv' overwrite into table score partition(month='201806');
insert overwrite select
通过一张表，然后将查询结果插入到另外一张表里面去 insert overwrite table score4 partition(month='201802') select s_id ,c_id ,s_score from score;
通过查询语句查询某张表，并且将数据弄到另外一张表里面去
hive的查询语法：
hive的group by 操作 group by的字段要么与select后面的字段保持一致，要么select后面不要出现任何字段
hive的join操作：hive的join操作只支持等值连接，不支持非等值连接
hive的配置文件说明：
1、全局配置 hive-site.xml 对所有的hive的任务都会生效
2、命令行的参数 bin/hive -hiveconf hive.root.logger=INFO,console 对当前会话有效
3、hive的参数声明 进入hive客户端以后通过 set 来进行设置一些参数 set mapred.reduce.task=20; 对当前客户端有效
hive自带的函数查看：
desc function extended upper; 查看函数的详细用法
hive当中的数据存储格式：
行式存储：textFile sequenceFile 都是行式存储
列式存储：orc parquet 可以使我们的数据压缩的更小，压缩的更快
数据查询的时候尽量不要用select * 只选举我们需要的字段即可
hive的数据存储格式：用的比较多的一中行式存储 ： textfile
用的比较多的列式存储： orc parquet
其中orc底层有自带的一种压缩算法，会对数据进行压缩的比较厉害
实际工作当中，很多时候，列式存储的数据格式都是选择orc或者paruet 压缩方式都是选择snappy
hive的调优：
第一个调优：fetch抓取，能够避免使用mr的，就尽量不要用mr，因为mr太慢了
set hive.fetch.task.conversion=more 表示我们的全局查找，字段查找，limit查找都不走mr
这个属性配置有三个取值 more minimal none 如果配置成none，所有的都要走mr程序
hive的本地模式：
set hive.exec.mode.local.auto=true 开启本地模式，解决多个小文件输入的时候，分配资源时间超过数据的计算时间
set hive.exec.mode.local.auto.inputbytes.max=51234560; 设置输入的数据临界值，如果小于这值都认为是小任务模式，启动本地模式来执行
set hive.exec.mode.local.auto.input.files.max=10; 设置输入文件个数的临界值，如果小于这个数量，那么也认为是小任务模式
第二个优化：hive表的优化
去重的优化：
select count(distinct s_id) from score;这种写法所有的去重数据都会在一个reduce当中去，造成数据处理比较慢
select count(1) from (select s_id from score group by s_id) by sid; 这种写法，使用了一个嵌套子查询，先对数据进行group by去重，然后再进行统计
尽量避免大sql，可以将一个很大的sql拆成多段，分步的去执行
大表join大表的优化：
空key的过滤
不过滤：
INSERT OVERWRITE TABLE jointable
SELECT a.* FROM nullidtable a JOIN ori b ON a.id = b.id;
结果：
No rows affected (152.135 seconds)
过滤：过滤掉我们所有的为null的id，使得我们的输入数据量变少
INSERT OVERWRITE TABLE jointable
SELECT a.* FROM (SELECT * FROM nullidtable WHERE id IS NOT NULL ) a JOIN ori b ON a.id = b.id;
结果：
No rows affected (141.585 seconds)
空key的转换
如果规定这些空key过滤不调，那么我们可以对空key进行转换
SELECT a.*
FROM nullidtable a
LEFT JOIN ori b ON CASE WHEN a.id IS NULL THEN 'hive' ELSE a.id END = b.id;
如果空key比较多，那么就会将大量的空key转换成 hive，那么就会遇到一个问题，数据倾斜
数据倾斜的表现形式：有一个reduce处理的数据量远远比其他reduce处理的数据量要大，造成其他的reduce数据都处理完了，这个还没处理完
怎么发现的数据倾斜，如何出现的数据倾斜，怎么解决的数据倾斜
空key的打散
SELECT a.*
FROM nullidtable a
LEFT JOIN ori b ON CASE WHEN a.id IS NULL THEN concat('hive', rand()) ELSE a.id END = b.id;
通过将空key打散成不同的随记字符串，就可以解决我们hive的数据倾斜的问题
hive第三个优化：map端的join
hive已经开启了自动的map端的join功能，不管是我们的大表join小表，还是小表join大表，都会将我们的小表加载到内存当中来
首先第一步：启动一个local的task，寻找哪个表的数据是小表数据
hive的group by优化：能在map端聚合的数据，就尽量在map端进行聚合
多加一层mr的程序，让我们的数据实现均衡的负载，避免数据的倾斜
count(distinct)的优化：
这种写法效率低下：SELECT count(DISTINCT id) FROM bigtable;
可以准换成这种写法：SELECT count(id) FROM (SELECT id FROM bigtable GROUP BY id) a;
笛卡尔积：任何时候都要避免笛卡尔积，避免无效的on条件
select from A left join B – on A.id = B.id
使用分区裁剪，列裁剪：
分区裁剪：如果是我们的分区表，那么查询的时候，尽量带上我们的分区条件
列裁剪：尽量避免使用select * ,需要查询哪些列，就选择哪些列
动态分区调整：
分区表的数据加载两种方式：
load data inpath ‘/export/xxx’ into table xxx partition(month = ‘xxx’)
insert overwrite table xxx partition (month = ‘xxx’) select xxx
使用动态分区动态的添加数据
如果要使用动态分区添加数据，最后一个字段一定要是我们的分区字段
INSERT overwrite TABLE ori_partitioned_target PARTITION (p_time)
SELECT id, time, uid, keyword, url_rank, click_num, click_url, p_time
FROM ori_partitioned;
数据的倾斜：
主要就是合理的控制我们的map个数以及reduce个数
第一个问题：maptask的个数怎么定的？？？与我们文件的block块相关，默认一个block块就是对应一个maptask
第二个问题：reduceTask的个数怎么定的？？？是我们自己手动设置的，爱设几个设几个，没人管你
第三个问题：是不是maptask的个数越多越好：不一定：有时候有些小文件，都要启动一个maptask，分配资源的时间超过了数据处理的时间
减少mapTask的个数：设置map端的小文件合并：使用combineHiveInputFormat来实现对我们小文件的合并，减少maptask的个数 或者使用本地模式也可以解决小文件的问题
增加maptask的个数：我们可以多设置几个reduce，然后使用distribte by将我们的数据打散
set mapreduce.job.reduces =10;
create table a_1 as
select * from a
distribute by rand(123);
第四个问题：控制reduceTask的个数：
reduce个数设置方法：
（1）每个Reduce处理的数据量默认是256MB
hive.exec.reducers.bytes.per.reducer=256123456
（2）每个任务最大的reduce数，默认为1009
hive.exec.reducers.max=1009
（3）计算reducer数的公式
N=min(参数2，总输入数据量/参数1)
直接凭感觉设置reduce的个数：
set mapreduce.job.reduces = 15;
查看执行计划：
explain extended select * from course;
并行执行：有时候有些sql之间是不相关的，可以并行的一起执行，那么就可以用并行执行
严格模式: 如果开启hive的严格模式，有以下三个限制
1、分区表需要带上分区字段
2、order by 必须使用limit
3、笛卡尔积不能执行
jvm的重用：我们的container的里面的任务执行完成之后，不要马上释放资源，留着资源给下一个任务执行
推测执行：maptask的推测执行以及reducetask的推测执行：
一般都直接关闭maptask以及reducetask的推测执行
set mapreduce.map.speculative=false;关闭map端的推测执行
set mapreduce.reduce.speculative=false; 关闭reduce端的推测执行
压缩与存储：压缩：snappy 源始数据存储：TextFile
处理之后的数据存储：ORC PARQUET
上一篇： clojure parse xml
下一篇： hive 基础知识汇总