数据仓库笔记
1.数据仓库
2.hive介绍
3.hive的操作
4.hive参数
5.hive函数（udf）
6.hive数据压缩
7.hive存储格式
8.存储和压缩相结合
9.hive调优
1.数据仓库
? 数据仓库：用于存储大量的历史历史数据。简称DW或者DWH，databasewarehouse，用于面向企业提供决策支持。
? 数据仓库：既不生产数据，又不消耗数据，只是用于对数据的存储。
? 粮仓：既不消耗粮食，又不生产粮食，只是用于粮食的存储。
? 数据仓库的特征：
? 1）面向主题：把系统数据集成到一起，按照一个层面进行数据的分析。
? 2）集成性:将多个子系统的数据集成到一起，进行数据分析。
? 3）非易失性：数据仓库中的数据不会轻易的删除。
? 4）时变性：数据会随着时间进行更新。
? 数据仓库和数据库的区别：
? OLTP:在线联机事务处理On-Line Transaction Processing（），针对数据事务的处理，主要应用在数据库中。
? OLAP:在线联机分析处理（On-Line Analytical Processing），针对历史数据的分析，用于企业的决策支持。
数据仓库的分层架构：
? 1）数据源层（ods层，贴源层）：最原始的数据
? 2）数据仓库层：通过ETL进行数据的处理，并且进行数据的分析。
? ETL(抽取Extra, 转化Transfer, 装载Load)
? 3)数据应用层：对分析结果一个直观的展示（图表和报表）
? 分层的原因：
? 用空间换时间。
? 数据仓库的元数据管理：
? 元数据：是数据和数据模型之间的一个对应（映射）关系。
2.hive的基本概念
? 1)hive的简介
? hive是Hadoop的数据仓库工具，用于分析结构化的数据。是通过类sql的方式，hql：hive sql。
? 结构化数据：比如关系型数据库中的数据。文本（特定的文本格式，通过分隔符进行分割）
? 半结构化数据：比如json， xml
? 非结构化数据：比如视频
? hive中数据存储：数据实际存储在hdfs上。
? hive中sql查询:sql的执行最终会转换为mapreduce，完成查询操作。
? 使用hive的原因：
? select * from user；
? select * from product left join order on product.id = order.pid;
? hive 使用sql完成数据的分析工作。
? hive的特点：
? 1）可扩展
? hadoop集群的节点可扩展
? 2）延展性
? 可以扩展相关的功能1，如udf。
? 3）容错
? 节点出现问题，sql仍可执行。
? hive的架构
? 1)hive的用户接口
? hive的客户端：shell/cli jdbc
? 2)hive的解析器
? 编译器：将sql进行拆分和解析
? 优化器：将执行的sql进行语法优化
? 执行器：进行数据查询并返回结果
? 3）hive的执行和落地点：
? hive执行：mapreduce
? 落地点：数据最终存储到hdfs上
? hive和hadoop的关系
? 执行通过mapreduce，存储在hdfs上
hive与传统数据库对比
? hive的sql实际是一个离线分析
? hive的数据存储
? hive数据最终存储在hdfs上
? hive存储格式：text sequencefile qarquetfile orc
?
? hive的安装（详见文档）
? hive的交互
? 1)bin/hive
? bin/hive 客户端连接
? 2）hive jdbc方式连接
? bin/hive --service hiveserver2 #使用jdbc连接的方式，首先要启动hiveserver2服务
? 后台启动：
? nohup bin/hive --service hiveserver2 2>&1 &
? bin/beeline
? !connect jdbc:hive2://node03:10000 #hive的jdbc url地址
? hive的用户名：和hadoop安装的用户名一致 root
? hive的密码：任意密码
? 注意：在使用beeline前，要使用bin/hive 连接一次，目的是让mysql数据库自动生成元数据信息。
? 3）hive参数的方式
? hive -e “select * from test；”
? hive -f hive.sql #执行脚本文件
3.HIVE的基本操作
? 1.数据库的创建
? create database if not exists myhive;
? use myhive;
? 默认数据库存放位置：
? <name>hive.metastore.warehouse.dir</name>
? <value>/user/hive/warehouse</value>
? 可以通过hive-site.xml修改默认保存地址
?
? 执行表的存储位置：
? create database myhive2 location ‘/myhive2‘
?
? 修改数据库
? 注意：只能修改数据库的属性，数据库名和存储地址不能修改。
? alter database myhive2 set dbproperties(‘createtime‘=‘20180611‘);
? 查看信息：
? desc database myhive2;
? desc database extended myhive2;
? 删除数据库：
? drop database myhive2 cascade; #如果数据库下有表，会一起删除
? 2）数据库表的操作
? CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name #创建一张表 external 外部
[(col_name data_type [COMMENT col_comment], ...)] #字段和字段类型
[COMMENT table_comment] #表的注释
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] #PARTITIONED BY表分区，分的是文件夹
[CLUSTERED BY (col_name, col_name, ...) #CLUSTERED BY 表分桶，分的是文件
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] #SORTED BY 排序
[ROW FORMAT row_format] #ROW FORMAT 数据的格式化 默认对每一个字段的分割 ‘‘\001‘
[STORED AS file_format] #STORED AS 数据存储格式
[LOCATION hdfs_path] #LOCATION 数据存储路径
? 1）管理表（内部表）
? 由hive管理的表，当删除表的时候，数据也会随之删除。
? 创建表的时候，不加external关键字，就是内部表。
? 创建表并指定字段之间的分隔符：
? create table if not exists stu2(id int ,name string) row format delimited fields terminated by ‘\t‘stored as textfile location ‘/user/stu2‘;
? 2）外部表
? 一张表不止由hive自己管理。当删除表的时候，不会删除数据。
? 外部表的创建需要加上external关键字。
? 创建教师外部表：
? create external table teacher (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;
? 加上overwrite 代表覆盖现有数据
? load data local inpath ‘/export/servers/hivedatas/student.csv‘ overwrite into table student;
? 加载hdfs上的数据
? load data inpath ‘/hivedatas/teacher.csv‘ into table teacher;
? 分区表：
? 按照指定的字段，进行数据目录的划分。核心思想：分而治之。
? 语法：partitioned by （month string）
? 创建分区：
? create table score(s_id string,c_id string,s_score int) partitioned by (month string) row format delimited fields terminated by ‘\t‘;
? 加载数据：
? load data local inpath ‘/export/servers/hivedatas/score.csv‘ into table score partition (month=‘201806‘);
? 分区联合查询使用union all来实现
? select * from score where month = ‘201806‘ union all select * from score where month = ‘201806‘
? 查看分区
? show partititions score;
? 添加一个分区
? alter table score add partition(month=‘201805‘);
? 删除分区
? alter table score drop partition(month = ‘201806‘);
? 需求描述：现在有一个文件score.csv文件，存放在集群的这个目录下/scoredatas/month=201806，这个文件每天都会生成，存放到对应的日期文件夹下面去，文件别人也需要公用，不能移动。需求，创建hive对应的表，并将数据加载到表中，进行数据统计分析，且删除表之后，数据不能删除
? 1）外部表：
? 2）分区表：month=201806
? 3）指定存储目录
? create external table score3(s_id string, c_id string, s_score int) partitioned by (month string) row format delimited fields terminated by ‘\t‘ location ‘\scoredats‘;
? 进行表的修复,说白了就是建立我们表与我们数据文件之间的一个关系映射
? msck repair table score4;
? 分桶表
? 将数据按照指定的字段进行划分，分到不同的文件中去。
? 语法：clustered by （id） into 3 buckets
? 开启hive的桶表功能
? set hive.enforce.bucketing=true;
? 设置reduce的个数
? set mapreduce.job.reduces=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‘;
? 分桶表数据的加载不能使用load的方式，需要通过间接从普通表查询数据插入的方式加载数据。
? 创建普通表
? create table course_common (c_id string,c_name string,t_id string) row format delimited fields terminated by ‘\t‘;
? 通过insert overwrite给桶表中加载数据
? insert overwrite table course select * from course_common cluster by(c_id);
? 表的其他操作
? 1）重命名：
? alter table score4 rename to score5;
? （2）添加列
? alter table score5 add columns (mycol string, mysco string);
? （3）查询表结构
? desc score5;
? （4）更新列
? alter table score5 change column mysco mysconew int;
? （5）查询表结构
? desc score5;
? (6)删除表
? drop table test；
? 数据的加载
? 1）通过load方式加载数据
? load data local inpath ‘/export/servers/hivedatas/score.csv‘ overwrite into table score partition(month=‘201806‘);
? 2) 通过查询方式加载数据
create table score4 like score;
insert overwrite table score4 partition(month = ‘201806‘) select s_id,c_id,s_score from score;
? 多插入模式：
? score s_id，c_id，s_score
? 字表：score1 s_id，c_id
? score2 c_id s_score
?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;
? 清空表数据：
? truncate table score6;
2.hive查询语法
? SELECT [ALL | DISTINCT] select_expr, select_expr, ... #查询字段
FROM table_reference #查询表
[WHERE where_condition] #查询条件
[GROUP BY col_list [HAVING condition]] #分组，数据的过滤
[CLUSTER BY col_list #
| DISTRIBUTE BY col_list #DISTRIBUTE BY 将数据按照分区进行查询
? #ORDER BY 排序（只能设置一个renduce，全局 的排序）
? #SORT BY 局部的排序，是在每一个分区内部局部的排序
? #DISTRIBUTE BY和SORT BY结合一起使用
? #CLUSTER BY = DISTRIBUTE BY（id）+SORT BY（id）
]
[LIMIT number] #limit 限定返回条数
全表查询
select * from score;
选择特定列查询
select s_id ,c_id from score;
列别名
1）重命名一个列。
2）便于计算。
3）紧跟列名，也可以在列名和别名之间加入关键字‘AS’
select s_id as myid ,c_id from score;
常用函数
1）求总行数（count）
select count(1) from score;
2）求分数的最大值（max）
? select max(s_score) from score;
3）求分数的最小值（min）
select min(s_score) from score;
4）求分数的总和（sum）
select sum(s_score) from score;
5）求分数的平均值（avg）
? select avg(s_score) from score;
LIMIT语句
典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。
select * from score limit 3;
WHERE语句
1）使用WHERE 子句，将不满足条件的行过滤掉。
2）WHERE 子句紧随 FROM 子句。
3）案例实操
查询出分数大于60的数据
select * from score where s_score > 60;
LIKE和RLIKE
1）使用LIKE运算选择类似的值
2）选择条件可以包含字符或数字:
% 代表零个或多个字符(任意个字符)。
_ 代表一个字符。
3）RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。
4）案例实操
? （1）查找以8开头的所有成绩
? select * from score where s_score like ‘8%‘;
? （2）查找第二个数值为9的所有成绩数据
select * from score where s_score like ‘_9%‘;
? （3）查找成绩中含9的所有成绩数据
? select * from score where s_score rlike ‘[9]‘;
GROUP BY语句
GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。
案例实操：
? （1）计算每个学生的平均分数
? select s_id ,avg(s_score) from score group by s_id;
? （2）计算每个学生最高成绩
? select s_id ,max(s_score) from score group by s_id
HAVING语句
1）having与where不同点
（1）where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据。
（2）where后面不能写分组函数，而having后面可以使用分组函数。
（3）having只用于group by分组统计语句。
2）案例实操：
? 求每个学生的平均分数
? select s_id ,avg(s_score) from score group by s_id;
? 求每个学生平均分数大于85的人
select s_id ,avg(s_score) avgscore from score group by s_id having avgscore > 85;
JOIN
? 1）等值join
? 用于多表之间的关联，在hive中只能完成等值连接，join on o.pid =p.id
? 2）表别名
? select * from teacher t join course c on t.t_id = c.t_id;
? 内连接（INNER JOIN）
内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。
select * from teacher t inner join course c on t.t_id = c.t_id;
左外连接（LEFT OUTER JOIN）
? 左外连接：JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。
查询老师对应的课程
select * from teacher t left join course c on t.t_id = c.t_id;
右外连接（RIGHT OUTER JOIN）
右外连接：JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。
select * from teacher t right join course c on t.t_id = c.t_id;
满外连接（FULL OUTER JOIN）
? 满外连接：将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。
SELECT * FROM teacher t FULL JOIN course c ON t.t_id = c.t_id ;
9.排序
? 1）全排序
? order by：reduce的数量必须为一个，是一个全局排序。
? 按照别名排序
? 按照分数的平均值排序
? select s_id ,avg(s_score) avg from score group by s_id order by avg;
? 多个列排序
? 按照学生id和平均成绩进行排序
? select s_id ,avg(s_score) avg from score group by s_id order by s_id,avg;
? 2）Sort By局部排序
? 1）设置reduce个数
? set mapreduce.job.reduces=3;
? 2）查看设置reduce个数
? set mapreduce.job.reduces;
? 3）查询成绩按照成绩降序排列
? select * from score sort by s_score;
? 4）将查询结果导入到文件中（按照成绩降序排列）
? insert overwrite local directory ‘/export/servers/hivedatas/sort‘ select * from score sort by s_score;
? 3）分区排序（DISTRIBUTE BY）
? DISTRIBUTE by 按照指的字段将数据发送到reduce中进行数据的处理。
? 设置reduce的个数，将我们对应的s_id划分到对应的reduce当中去
? set mapreduce.job.reduces=7;
? 通过distribute by 进行数据的分区
? insert overwrite local directory ‘/export/servers/hivedatas/sort‘ select * from score distribute by s_id sort by s_score;
?
? 4）CLUSTER BY
? 按照指定字段分区，并排序（只有升序排序）
? 当distribute by和sort by字段相同时，可以使用cluster by方式。
4.hive的shell参数
? 对于一般参数，有以下三种设定方式：
? 1）配置文件 hive-site.xml
? 2）命令行参数
? bin/hive -hiveconf hive.root.logger=INFO,console
? 3) 参数声明
? set mapred.reduce.tasks=100;
? 这一设定的作用域也是session级的。
? 生效顺序
? 参数声明 > 命令行参数 > 配置文件参数（hive）
5.hive的函数
? 1）内置函数
? 1）查看系统自带的函数
? hive> show functions;
? 2）显示自带的函数的用法
? hive> desc function upper;
? 3）详细显示自带的函数的用法
? hive> desc function extended upper;
? 2）hive自定义函数
? 1）udf：（User-Defined-Function）用户自定义函数，一进一出
? 2）udaf：用户自定义聚合函数，多进一出
? 3）udtf：UDTF（User-Defined Table-Generating Functions），一进多出
? lateral view explore()
?
原文：https://www.cnblogs.com/MrChenShao/p/11713222.html