数据仓库（英语：Data Warehouse，简称数仓、DW），是一个用于存储、分析、报告的数据系统。
数据仓库的目的是构建面向分析的集成化数据环境，分析结果为企业提供决策支持（Decision Support）
数仓专注分析
为了分析数据而来，分析结果给企业决策提供支撑
案例：中国人手保险公司
1. 业务数据的存储问题
2. 分析型决策的制定
OLTP环境开展分析可行吗？
OLTP（On-line Transaction Processing）：操作型处理、称为联机事务处理，也可以称为面向交易的处理系统，它是针对具体业务在数据库联机的日常操作，通常对少数记录进行查询、修改。用户较为关心操作的响应时间、数据的安全性、完整性等问题。
可以，但是没必要
数据仓库面世
OLAP（On-line Analytical Processing）：分析型处理，称为联机分析处理，一般针对某些主题历史数据进行分析，支持管理决策。
数据仓库的构建
1. 面向主题性（Subject-Oriented）
主题是一个抽象的概念，是较高层次上数据综合、归类并进行分析利用的抽象
2. 集成性（Integrated）
主题相关的数据通常会分布在多个操作型系统中，彼此分散、独立、异构。需要集成到数仓主题下。
主题相关的数据通常会分布在多个操作型系统中，彼此分散、独立、异构。
因此在数据进入数据仓库之前，必然要经过统一与综合，对数据进行抽取、清理、转换和汇总，这一步是数据仓库 建设中最关键、最复杂的一步，所要完成的工作有：
下图说明了保险公司综合数据的简单处理过程，其中数据仓库中与“承保”主题有关的数据来自于多个不同的操作型系统。
这些系统内部数据的命名可能不同，数据格式也可能不同。把不同来源的数据存储到数据仓库之前，需要去除这些 不一致
3. 非易失性、非异变性（Non-Volatile）
也叫非易变性。数据仓库是分析数据的平台，而不是创造数据的平台。
4. 时变性
数据仓库的数据需要随着时间更新，以适应决策的需要。
数仓开发语言概述
SQL语言介绍
数仓与SQL
SQL全称叫做结构化查询语言，结构化是什么意思？
结构化数据
SQL语法分类
SQL主要语法分为两个部分：数据定义语言 (DDL)和数据操纵语言 (DML) 。
DDL语法使我们有能力创建或删除表，以及数据库、索引等各种对象，但是不涉及表中具体数据操作：
DML语法是我们有能力针对表中的数据进行插入、更新、删除、查询操作：
什么是Hive？
为什么使用Hive？
Hive和Hadoop关系
如果让您设计Hive这款软件，要求能够实现用户只编写sql语句，Hive自动将sql转换MapReduce程序，处理位于HDFS上的结构化数据。如何实现？
案例
在HDFS文件系统上有一个文件，路径为/data/china_user.txt
需求：统计来自于上海年龄大于25岁的用户有多少个？
场景目的
重点理解下面两点：
映射信息记录
SQL语法解析、编译
对Hive的理解
最终效果
Hive架构图
用户接口
元数据存储
Driver驱动程序
执行引擎
什么是元数据
Hive Metadata
Hive Metatore
metastore配置方式
metastore远程模式
安装前准备
Hadoop与Hive整合
<!-- 整合hive --> <property> <name>hadoop.proxyuser.root.hosts</name> <value>*</value> </property> <property> <name>hadoop.proxyuser.root.groups</name> <value>*</value> </property> Linux
命令行安装
sudo apt-get install mysql-server 查看运行状态
systemctl status mysql # 显示running说明已经在运行 查看默认用户密码
sudo cat /etc/mysql/debian.cnf # Automatically generated for Debian scripts. DO NOT TOUCH! [client] host = localhost user = XXXX password = XXXXX socket = /var/run/mysqld/mysqld.sock [mysql_upgrade] host = localhost user = XXXX password = XXXX socket = /var/run/mysqld/mysqld.sock 使用默认用户登陆
mysql -uXXX -p 查看用户权限
use mysql; select host, user, plugin from user; # 可以看到root用户只能localhost登陆 设置root运行远程登陆
# 更改localhost为 % update user set host='%' where user='root'; # 刷新权限 flush privileges; 修改Mysql加密规则和root密码
MySQL8.0之前的版本密码加密规则：mysql_native_password
MySQL8.0密码加密规则：caching_sha2_password
# 修改加密方式 ALTER USER 'root'@'%' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER; # 重新设置root的密码 ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '密码'; FLUSH PRIVILEGES; select host, user, plugin from user; +-----------+------------------+-----------------------+ | host | user | plugin | +-----------+------------------+-----------------------+ | % | root | mysql_native_password | | localhost | debian-sys-maint | caching_sha2_password | | localhost | mysql.infoschema | caching_sha2_password | | localhost | mysql.session | caching_sha2_password | | localhost | mysql.sys | caching_sha2_password | +-----------+------------------+-----------------------+ 修改bind-address开放远程登陆
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf # 修改 bind-address = 0.0.0.0 重启MySql服务
systemctl restart mysql 查看防火墙是否放行3306端口
namp -p3306 localhost
如果不是open则需要，关闭防火墙
MySQL安装后的目录结构分析
(此结构只针对于使用apt-get install 在线安装情况)
/var/lib/mysql//usr/share/mysql/usr/bin(mysqladmin mysqldump等命令)/etc/rc.d/init.d/MySQL的卸载
# 停止进程 sudo kill $(pgrep mysql) sudo apt-get remove --purge mysql-* （node1安装即可）
tar zxvf apache-hive-3.1.2-bin.tar.gz mv apache-hive-3.1.2-bin/ hive # 解决Hive与Hadoop之间guava版本差异 cd /export/server/apache-hive-3.1.2-bin/ rm -rf lib/guava-19.0.jar cp /export/server/hadoop-3.3.0/share/hadoop/common/lib/guava-27.0-jre.jar ./lib/ Step3 修改hive-env.sh
cd /export/server/apache-hive-3.1.2-bin/conf mv hive-env.sh.template hive-env.sh vim hive-env.sh export HADOOP_HOME=/opt/hadoop-3.3.0 export HIVE_CONF_DIR=/opt/hive-3.1.3/conf export HIVE_AUX_JARS_PATH=/opt/hive-3.1.3/lib Step4 新增hive-site.xml
<configuration> <!-- 存储元数据mysql相关配置 --> <property> <name>javax.jdo.option.ConnectionURL</name> <value>jdbc:mysql://XXX(hive节点名):3306/hive3?createDatabaseIfNotExist=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8</value> </property> <property> <name>javax.jdo.option.ConnectionDriverName</name> <value>com.mysql.jdbc.Driver</value> </property> <property> <name>javax.jdo.option.ConnectionUserName</name> <value>sql数据库的用户名</value> </property> <property> <name>javax.jdo.option.ConnectionPassword</name> <value>sql密码需要自己填写</value> </property> <!-- H2S运行绑定host --> <property> <name>hive.server2.thrift.bind.host</name> <value>XXX(hive节点名)</value> </property> <!-- 远程模式部署metastore metastore地址 --> <property> <name>hive.metastore.uris</name> <value>thrift://XXX(hive节点名):9083</value> </property> <!-- 关闭元数据存储授权 --> <property> <name>hive.metastore.event.db.notification.api.auth</name> <value>false</value> </property> </configuration> Step5 添加驱动、初始化
上传MySQL JDBC驱动到Hive安装包lib路径下 下载链接
初始化Hive的元数据
cd /export/server/apache-hive-3.1.2-bin/ bin/schematool -initSchema -dbType mysql -verbos #初始化成功会在mysql中创建74张表 1. 自带客户端
bin/hive, bin/beeline$HIVE_HOME/bin/hive, 是一个 shellUtil。主要功能：一是可用于以交互或批处理模式运行Hive查询；二是用于Hive相关服务的启动，比如metastore服务。$HIVE_HOME/bin/beeline，是一个JDBC客户端，是官方强烈推荐使用的Hive命令行工具，和第一代客户端相比，性能加强安全性提高。
HiveServer2服务介绍
关系梳理
bin/beeline客户端使用
在hive安装的服务器上，首先启动metastore服务，然后启动hiveserver2服务
#先启动metastore服务 然后启动hiveserver2服务 nohup /opt/hive-3.1.3/bin/hive --service metastore & nohup /opt/hive-3.1.3/bin/hive --service hiveserver2 & 在node3上使用beeline客户端进行连接访问。需要注意hiveserver2服务启动之后需要稍等一会才可以对外提供服务。
Beeline是JDBC的客户端，通过JDBC协议和Hiveserver2服务进行通信，协议的地址是： jdbc:hive2://node1:10000
[root@node3 ~]# /opt/hive-3.1.3/bin/beeline Beeline version 3.1.2 by Apache Hive beeline> ! connect jdbc:hive2://node1:10000 Connecting to jdbc:hive2://node1:10000 Enter username for jdbc:hive2://node1:10000: root Enter password for jdbc:hive2://node1:10000: Connected to: Apache Hive (version 3.1.2) Driver: Hive JDBC (version 3.1.2) Transaction isolation: TRANSACTION_REPEATABLE_READ 0: jdbc:hive2://node1:10000> 2. Hive 可视化客户端
Datagrip
Hive命令行下执行set命令【仅当前会话有效】
hive> set hive.cli.print.current.db=true; # 显示当前数据库名称 Hive脚本~/.hiverc中配置set命令【当前用户有效】
Hive运行时日志
cd /opt/hive-3.1.3/conf mv hive-log4j.properties.template hive-log4j.properties # 更改以下选项，日志级别可以根据需要调整 # list of properties property.hive.log.level = WARN property.hive.root.logger = DRFA # 更改路径 property.hive.log.dir = /opt/hive-3.1.3/logs property.hive.log.file = hive.log property.hive.perflogger.log.level = INFO Hive任务执行日志
cd /opt/hive-3.1.3/conf mv hive-exec-log4j.properties.template hive-exec-log4j.properties # 更改以下选项，日志级别可以根据需要调整 # list of properties property.hive.log.level = WARN property.hive.root.logger = FA property.hive.query.id = hadoop # 更改路径 property.hive.log.dir = /opt/hive-3.1.3/logs property.hive.log.file = ${sys:hive.query.id}.log Hive数据模型总览
SQL中DDL语法的作用
数据库database
/user/hive/warehouse下/user/hive/warehouse/database_name.db下create database
create database用于创建新的数据库
/user/hive/warehouse/dbname.dbCREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name [COMMENT database_comment] [LOCATION hdfs_path] [WITH DBPROPERTIES (property_name=property_value, ...)]; 例子：创建数据库itcast
注意：如果需要使用location指定路径的时候，最好指向的是一个新创建的空文件夹
create database if not exists itcast comment "this is my first db" with dbproperties ('createdBy'='Allen'); use database
drop database
删除数据库
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE]; 表Table
建表语法树（基础）
CREATE TABLE [IF NOT EXISTS] [db_name.]table_name (col_name data_type [COMMENT col_comment], ... ) [COMMENT table_comment] [ROW FORMAT DELIMITED …]; -- 简写 CREATE TABLE table_name (col_name data_type); 数据类型
分隔符指定语法
LazySimpleSerDe是Hive默认的，包含4种子语法，分别用于指定字段之间、集合元素之间、map映射 kv之间、 换行的分隔符号
在建表的时候可以根据数据的特点灵活搭配使用
Hive默认分隔符
实例练习
实例1
建表
create database if not exists learnsql; use learnsql; create table t_archer ( id int comment "ID", name string comment "英雄名称", hp_max int comment "最大生命", mp_max int comment "最大法力", attack_max int comment "最高物理攻击", defense_max int comment "最高物理防御", attack_range string comment "攻击范围", role_main string comment "主要定位", role_assist string comment "次要定位" ) comment "英雄信息" ROW FORMAT delimited fields terminated by 't'; 上传文件
#把文件从课程资料中首先上传到node1 linux系统上 #执行命令把文件上传到HDFS表所对应的目录下 hadoop fs -put archer.txt /user/hive/warehouse/itheima.db/t_archer 查看表
在bin/beeline客户端查看 or 可视化工具
select * from t_archer; --- 1,后羿,5986,1784,396,336,remotely,archer, 2,马可波罗,5584,200,362,344,remotely,archer, 3,鲁班七号,5989,1756,400,323,remotely,archer, 4,李元芳,5725,1770,396,340,remotely,archer, 5,孙尚香,6014,1756,411,346,remotely,archer, 6,黄忠,5898,1784,403,319,remotely,archer, 7,狄仁杰,5710,1770,376,338,remotely,archer, 8,虞姬,5669,1770,407,329,remotely,archer, 9,成吉思汗,5799,1742,394,329,remotely,archer, 10,百里守约,5611,1784,410,329,remotely,archer,assassin 内部表
外部表
检表语句中包含External的表叫做外部表
外部表在加载数据的时候，实际数据并不会移动到warehouse目录中，只是与外部数据建立一个链接（映射关系）
当删除一个外部表是，只删除元数据，不删除表中的数据，仅删除表和数据之间的链接
create external table external_table( key string )location '/data/external'; -- 目录是hdfs中的位置，若不存在，会自动创建 -- 添加数据会进入到此目录下，不会进入到warehouse 相互转换
-- 内部表转外部表 alter table tblName set tblproperties('external'='true'); -- 外部表转内部表 alter table tblName set tblproperties('external'='false'); 分区表
分区可以理解为分类，通过分区把不同类型数据放到不同目录
分区的标准就是指定分区字段，分区字段可以有一个或多个
分区表的意义在于优化查询，查询时，尽量利用分区字段，如果不使用分区字段，就会全表扫描，最典型的一个场景就是把天作为分区字段，查询的时候指定天
create table partition_1( id int, name string )partitioned by (dt string) row format delimited fields terminated by 't'; -- 需要指定分区 load data local inpath '/root/Documents/partition_1.data' into table partition_1 partition(dt='20200101'); -- 展示分区情况 show partitions partition_1; 外部分区表
工作中最常见的表：外部表+分区表
删除分区时，分区数据不会被删除
桶表
桶表是对数据进行哈希取值，然后放到不同文件中存储
物理上，每个通就是表（或分区）里的一个文件
桶表的作用
create table bucket_tb( id int )clustered by (id) into 4 buckets; -- 根据id值，分成4个桶 -- 直接使用load命令是没有办法分桶的 -- 设置有几个桶就有几个reduce任务 set hive.enforce.bucketing=true; -- 只能使用insert ... select进行插入数据 insert into table bucket_tb select id from b_source where id is not null; -- 抽样数据 -- tablesample(bucket x out of y on id); -- 注意：y>=x -- y：表示把桶表中的数据随机分为多少桶 -- x: 表示取出第几桶的数据 select * from bucket_tb tablesample(bucket 1 out of 4 on id); -- 如果a、b表都是id的桶表，就不会产生全表的笛卡尔积，只会在同id内做笛卡尔积 select a.id,a.name,b.addr from a join b on a.id = b.id; --1、显示所有数据库 SCHEMAS和DATABASES的用法 功能一样 show databases; show schemas; --2、显示当前数据库所有表 show tables; SHOW TABLES [IN database_name]; --指定某个数据库 --3、查询显示一张表的元数据信息 desc formatted t_team_ace_player; Load加载数据
不管路径在哪里，只有把数据文件移动到对应的表文件夹下面，Hive才能映射解析成功
最原始暴力的方式就是使用hadoop fs –put|-mv等方式直接将数据移动到表文件夹下
但是，Hive官方推荐使用Load命令将数据加载到表中
因为采用hadoop fs上传文件就跨过了hive去操作底层，这不符合操作逻辑也不安全
Load语法
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename; 语法规则之LOCAL
指定LOCAL， 将在本地文件系统中查找文件路径。
file:///user/hive/project/data1没有指定LOCAL关键字
filepath指向的是一个完整的URI，会直接使用这个URIfs.default.name指定的（不出意外，都是HDFS）LOCAL本地是哪里？
如果对HiveServer2服务运行此命令
本地文件系统指的是Hiveserver2服务所在机器的本地Linux文件系统，不是Hive客户端所在的本地文件系统
语法规则之filepath
filepath表示待移动数据的路径。可以指向文件（在这种情况下，Hive将文件移动到表中），也可以指向目录（在 这种情况下，Hive将把该目录中的所有文件移动到表中）。filepath文件路径支持下面三种形式，要结合LOCAL关键字一起考虑：
project/data1/user/hive/project/data1 schema的完整URI，例如：hdfs://namenode:9000/user/hive/project/data1实例1
--step1:建表 --建表student_local 用于演示从本地加载数据 create table student_local(num int,name string,sex string,age int,dept string) row format delimited fields terminated by ','; desc student_local; --建表student_HDFS 用于演示从HDFS加载数据 create table student_HDFS(num int,name string,sex string,age int,dept string) row format delimited fields terminated by ','; desc student_HDFS; --建议使用beeline客户端 可以显示出加载过程日志信息 --step2:加载数据 -- 从本地加载数据 数据位于HS2（node1）本地文件系统 本质是hadoop fs -put上传操作 LOAD DATA LOCAL INPATH '/home/node1/Document/students.txt' INTO TABLE student_local;
--从HDFS加载数据 数据位于HDFS文件系统根目录下 本质是hadoop fs -mv 移动操作 --先把数据上传到HDFS上 hadoop fs -put /home/node1/Document/students.txt LOAD DATA INPATH '/students.txt' INTO TABLE student_HDFS;
慢
语法功能
insert+select
INSERT INTO TABLE tablename select_statement1 FROM from_statement; Select语法树
SELECT [ALL | DISTINCT] select_expr, select_expr, ... FROM table_reference [WHERE where_condition] [GROUP BY col_list] [ORDER BY col_list] [LIMIT [offset,] rows]; 具体语法与sql类似，可参考此文章。
查看所有内置函数
show functions; 查看指定函数信息
desc function FUNC; 查看指定函数扩展信息
desc function extended FUNC; 字符串函数
------------String Functions 字符串函数------------ select length("itcast"); select reverse("itcast"); select concat("angela","baby"); --带分隔符字符串连接函数：concat_ws(separator, [string | array(string)]+) select concat_ws('.', 'www', array('itcast', 'cn')); --字符串截取函数：substr(str, pos[, len]) 或者 substring(str, pos[, len]) select substr("angelababy",-2); --pos是从1开始的索引，如果为负数则倒着数 select substr("angelababy",2,2); --分割字符串函数: split(str, regex) select split('apache hive', ' '); 日期函数
----------- Date Functions 日期函数 ----------------- --获取当前日期: current_date select current_date(); --获取当前UNIX时间戳函数: unix_timestamp select unix_timestamp(); --日期转UNIX时间戳函数: unix_timestamp select unix_timestamp("2011-12-07 13:01:03"); --指定格式日期转UNIX时间戳函数: unix_timestamp select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss'); --UNIX时间戳转日期函数: from_unixtime select from_unixtime(1618238391); select from_unixtime(0, 'yyyy-MM-dd HH:mm:ss'); --日期比较函数: datediff 日期格式要求'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd' select datediff('2012-12-08','2012-05-09'); --日期增加函数: date_add select date_add('2012-02-28',10); --日期减少函数: date_sub select date_sub('2012-01-1',10); 数学函数
----Mathematical Functions 数学函数------------- --取整函数: round 返回double类型的整数值部分 （遵循四舍五入） select round(3.1415926); --指定精度取整函数: round(double a, int d) 返回指定精度d的double类型 select round(3.1415926,4); --取随机数函数: rand 每次执行都不一样 返回一个0到1范围内的随机数 select rand(); --指定种子取随机数函数: rand(int seed) 得到一个稳定的随机数序列 select rand(3); 条件函数
主要用于条件判断、逻辑判断转换这样的场合
-----Conditional Functions 条件函数------------------ select * from student limit 3; --if条件判断: if(boolean testCondition, T valueTrue, T valueFalseOrNull) select if(1=2,100,200); select if(sex ='男','M','W') from student limit 3; --空值转换函数: nvl(T value, T default_value) select nvl("allen","cool"); select nvl(null,"cool"); --条件转换函数: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end; select case sex when '男' then 'male' else 'female' end from student limit 3; ROW_NUMBER()：对每一行赋其行值
OVER()：分组
取出每个学科中的前三名
-- student_score table -- id,name,sub,score -- 3,zs1,english,89 -- 4,zs2,chinese,60 -- 5,zs2,math,75 -- 6,zs2,english,80 -- 7,zs3,chinese,79 -- 8,zs3,math,83 -- 9,zs3,english,72 -- 10,zs4,chinese,90 -- 11,zs4,math,76 -- 12,zs4,english,80 -- 13,zs5,chinese,98 -- 14,zs5,math,80 -- 15,zs5,english,70 select * from ( select *, row_number() over(partition by sub order by score desc) as num from student_score ) s where s.num<=3; RANK()：排名，存在相等的排名，比如 1、2、2、4这样的排名
DENSE_RANK()：密集排名，与rank不同的是1、2、2、3
行转列
CONCAT_WS()：连接元素
COLLECT_LIST()：聚合元素成list
COLLECT_SET()：聚合元素成set
-- student_favor table -- name favor -- zs swing -- zs footbal -- zs sing -- zs codeing -- zs swing -- 将上面的多行，转换成单行 select name, collect_list(favor) as favor_list from student_favors group by name; select name, collect_set(favor) as favor_list from student_favors group by name; select name,concat_ws(',',collect_list(favor)) as favor_list from student_favors group by name; 列转行
SPLIT()：分割字符串，返回数组
EXPLODE()：将数组元素，转换成多行，或者将map元素转换为多行和多列
LATERAL VIEW：
1.Lateral View 用于和UDTF函数【explode,split】结合来使用。
2.首先通过UDTF函数将数据拆分成多行，再将多行结果组合成一个支持别名的虚拟表。
3..主要解决在select使用UDTF做查询的过程中查询只能包含单个UDTF，不能包含其它字段以及多个UDTF的情况。
4.语法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)
-- name favorlist -- zs swing,footbal,sing -- ls codeing,swing -- 转换成下面的形式 -- zs swing -- zs footbal -- zs sing -- ls codeing -- ls swing select name,favor_new from student_favors_2 lateral view explode(split(favorlist,',')) table1 as favor_new; -- 这样写会直接报错，因为name的个数与explode后的行数不等了 select name,explode(split(favorlist,',')) from student_favors_2 ; ORDER BY：全局有序
SORT BY：局部有序（单个reduce内有序）
DISTRIBUTE BY：对数据进行分区，一般和sort by结合使用
CLUSTER BY：cluster by id = distribute by id sort by id 等同于，但不支持desc
-- 统计order 表中name 去重之后的数据量 select count(distinct name) from order -- 性能很低，要在一个reduce任务内完成 -- 分为两步，可以使用多个reduce任务完成，效率高 select count(*) from (select name from order group by name) tmp 数据倾斜小实例
之前在Hadoop三大组件中最后一个单元提到过
select a.Key, SUM(a.Cnt) as Cnt from ( select Key, COUNT(*) as Cnt From TableName Group By Key, -- 将key001占比大的数据打散，可以根据其实际比例来进行，这样可以分配到多个reduce任务中 CASE When key = 'Key001' THEN Hash(Random()) % 50 ELSE 0 END ) a GROUP by a.Key; 
