07 数据仓库工具hive

数据仓库工具hive

一、数据仓库的概念

什么是数据仓库，主要用来干嘛的

1、什么是数据仓库

仓库就是用来存储东西的  仓库不会生产东西   仓库不会消耗这些粮食
说白了里面的东西，是外界放进去的，供外界使用
总结来说就是仓库不生产也不消耗，外界放入，供外界使用，主要的功能就是存储
数据仓库：简称DW  data  warehouse  里面装的都是数据
目的：构建面向分析的集成化数据环境，主要职责是做分析，对仓库里面的数据来做分析
数据分析可以支持我们做决策等

2、数据仓库的主要特征

面向主题：数据分析有一定的范围，需要选取一定的主题进行分析

集成性：集成各个其他方面关联的一些数据，比如分析订单购买人的情况，就涉及到牵连用户信息的情况

非易失性：数据分析主要是分析过去已经发生的数据   ，分析的东西都是既成就的事实，不会在改变了

时变性：随着时间的发展，数据的形态也在发生变化，数据分析的手段也要相应的进行改变

3、 数据仓库与数据库区别

得要知道
数据库：事务处理   OLTP On-Line Transaction Processing 联机事务处理  
数据库主要的功能就是用来做事务处理的，主要负责频繁的增删改查

数据仓库：分析处理  OLAP  On-Line Analytical Processing  联机的分析处理  
不需要做事务的保证  主要职责就是做数据的分析，面向分析的

首先要明白，数据仓库的出现，并不是要取代数据库。
1.数据库是面向事务的设计，数据仓库是面向主题设计的。
2.数据库一般存储业务数据，数据仓库存储的一般是历史数据。
3.数据库设计是尽量避免冗余，一般针对某一业务应用进行设计，比如一张简单的User表，记录用户名、
密码等简单数据即可，符合业务应用，但是不符合分析。数据仓库在设计是有意引入冗余，依照分析需求
，分析维度、分析指标进行设计。
4.数据库是为捕获数据而设计，数据仓库是为分析数据而设计。

数据库的数据，允许增删改
数据仓库的数据，一般不允许增删改  

数据仓库的产生是因为有大量的数据库的存在，需要对数据进一步的决策分析，然
后才产生数据仓库的概念
excel kettle sass passs 都是数据分析的一些软件

4、数据仓库分层架构

按照数据流入流出的过程，数据仓库架构可分为三层——源数据、数据仓库、数据应用。

数据仓库的分层：
数据仓库一般分为三层：

第一层：源数据层 又叫ODS  我们产生数据的地方
第二层：数据仓库层  又叫DW  主要集中存储数据   面向主题进行分析
第三步：数据应用层 又叫app层   主要用于展示我们分析之后的数据结果

数据在这三层之间的一个流动，称之为ETL的过程
ETL：extrat(抽取)  transform（转换）   load（加载）
数据从源数据层流向数据仓库层再流向app层

为什要对数据仓库的分层？：每一层职责清晰

数据仓库的元数据管理：定义我们的数据架构，数据的位置，数据的信息的
这里的元数据，记录的是我们数据仓库当中的数据如何进行处理的一整套的过程

二、hive的基本概念以及hive的安装

1.hive简介

hive：是一个数据仓库的处理工具，专门用于数据仓库的数据分析
hive 是基于hadoop的一个数据仓库的工具，将结构化的数据，映射成为一张表，并且提供类似sql的查询功能
本质是将sql语句转换成mapreduce的任务进行执行

结构化数据：提现为数据字段固定，数据类型固定   （数据库的表就是一种最典型的结构化数据）
半结构化数据：数据类型一定  但是数据的字段个数不定 （XML和JSON）
非结构化数据：完全没有任何规律  字段类型不定，字段的个数不定，数据的类型不定 比如说音频  视频 

hive当中的元数据信息：
主要描述了hive表与hdfs的数据之间的映射关系   
包括了字段的映射（hive当中的哪个字段与数据当中的哪一列进行对应），数据位置的保存，建表的语句定义等等

hive是一个数据仓库的工具，主要用于做数据分析的
数据的存储：用的是HDFS
数据的计算：用的是MR
hive与hdfs上面数据的映射关系，叫做元数据信息，一般都保存在mysql或者oracle里面等等

Hive的特点
1.可扩展 
Hive可以自由的扩展集群的规模，一般情况下不需要重启服务。

2.延展性 
Hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数。

3.容错 
良好的容错性，节点出现问题SQL仍可完成执行。

2.hive架构

基本组成

用户接口：包括CLI、JDBC/ODBC、WebGUI。其中，CLI(command line interface)为shell命令行；JDBC/ODBC是
Hive的JAVA实现，与传统数据库JDBC类似；WebGUI是通过浏览器访问Hive。

元数据存储：通常是存储在关系数据库如mysql/derby中。Hive 将元数据存储在数据库中。Hive 中的元数据包括表的名字，
表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录等。

解释器、编译器、优化器、执行器:完成HQL 查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。
生成的查询计划存储在HDFS 中，并在随后有MapReduce 调用执行。

hive与hadoop的关系：hive利用HDFS存储数据并且hive就是一个mr的客户端

3.Hive与传统数据库对比

千万别把hive当做数据库来用，现在版本的hive支持插入，不支持修改，删除操作 最主要的功能是查询
hive是用来做数据仓库的
mapreduce为什么慢？？？？反复的将数据从磁盘到内存，从内存到磁盘，从磁盘到内存 从内存到磁盘
hive的数据是存储在hdfs上面，而且hive支持各种存储格式

4.Hive的数据存储

1、Hive中所有的数据都存储在 HDFS 中，没有专门的数据存储格式
（可支持Text，SequenceFile，ParquetFile，ORC格式RCFILE等）
SequenceFile是hadoop中的一种文件格式：文件内容是以序列化的kv对象来组织的

2、只需要在创建表的时候告诉 Hive 数据中的列分隔符和行分隔符，Hive 就可以解析数据。

3、Hive 中包含以下数据模型：DB、Table，External Table，Partition，Bucket。
db：在hdfs中表现为${hive.metastore.warehouse.dir}目录下一个文件夹
table：在hdfs中表现所属db目录下一个文件夹
external table：与table类似，不过其数据存放位置可以在任意指定路径
partition：在hdfs中表现为table目录下的子目录
bucket：在hdfs中表现为同一个表目录下根据hash散列之后的多个文件

5.HIVE的安装部署

5.1 derby版hive直接使用：

1、解压hive
cd /export/softwares
tar -zxvf hive-1.1.0-cdh5.14.0.tar.gz -C ../servers/

1、直接启动  bin/hive
cd ../servers/
cd hive-1.1.0-cdh5.14.0/
bin/hive
hive> create database mytest;

缺点：多个地方安装hive后，每一个hive是拥有一套自己的元数据，大家的库、表就不统一；

5.2 使用mysql共享hive元数据

mysql数据库的安装（使用rpm包的方式进行安装，不推荐）

第一步：查看系统自带的mysql的rpm包
rpm -qa | grep mysql
rpm -e mysql-libs-5.1.73-8.el6_8.x86_64 --nodeps
第二步：安装mysql的rpm包
rpm   -ivh  *.rpm
第三步：启动mysql的服务
service  mysqld  start
第四步：查看mysql初始化密码
more /root/.mysql_secret

1.mysql数据库的安装（使用yum源进行安装，强烈推荐）

第一步：在线安装mysql相关的软件包
yum  install  mysql  mysql-server  mysql-devel
第二步：启动mysql的服务
/etc/init.d/mysqld start
第三步：通过mysql安装自带脚本进行设置
/usr/bin/mysql_secure_installation
第四步：进入mysql的客户端然后进行授权
进入mysql的客户端
mysql -uroot -p

mysql的远程连接授权语句：
grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
flush privileges;	刷新权限表

*.*   第一个星表示数据库名称  第二个星表示数据库下面的哪个表
'root'@'%'  root：表示mysql的用户   %表示我们的ip地址 可以从哪台机器使用哪个用户来连接我们的数据库服务器
root@node1

all privileges  :所有的权限  这里还可以写  insert  update  delete  select 

identified by ：远程连接的时候的密码，与我们mysql的root用户密码无关

实际线上环境，关于mysql的控制比较严格，不同的人分配不同用户，创建不同的数据库，分配不同的权限

mysql数据库的卸载

停止mysql服务 service mysql stop
查看所有mysql的安装包 rpm -qa | grep -i mysql
卸载所有mysql的安装包 rpm -e xxx
查看是否卸载干净  rpm -qa | grep -i mysql
查看哪里还有mysql的文件夹 whereis mysql
删除 rm -rf /user/lib64/mysql
删除 rm -rf /user/my.cnf
删除 rm -rf /root/.mysql_secret
删除 rm -rf /var/lib/mysql

2.修改hive的配置文件

修改hive-env.sh

添加我们的hadoop的环境变量
cd  /export/servers/hive-1.1.0-cdh5.14.0/conf
cp hive-env.sh.template hive-env.sh
vim hive-env.sh

# Set HADOOP_HOME to point to a specific hadoop install directory
HADOOP_HOME=/export/servers/hadoop-2.6.0-cdh5.14.0
# Hive Configuration Directory can be controlled by:
export HIVE_CONF_DIR=/export/servers/hive-1.1.0-cdh5.14.0/conf

修改hive-site.xml

cd /export/servers/hive-1.1.0-cdh5.14.0/conf
vim hive-site.xml

<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
        <property>
                <name>javax.jdo.option.ConnectionURL</name>
                <value>jdbc:mysql://node3.hadoop.com:3306/hive?createDatabaseIfNotExist=true</value>
        </property>

        <property>
                <name>javax.jdo.option.ConnectionDriverName</name>
                <value>com.mysql.jdbc.Driver</value>
        </property>
        <property>
                <name>javax.jdo.option.ConnectionUserName</name>
                <value>root</value>
        </property>
        <property>
                <name>javax.jdo.option.ConnectionPassword</name>
                <value>123456</value>
        </property>
        <property>
                <name>hive.cli.print.current.db</name>
                <value>true</value>
        </property>
        <property>
                <name>hive.cli.print.header</name>
                <value>true</value>
        </property>
        <property>
                <name>hive.server2.thrift.bind.host</name>
                <value>node3.hadoop.com</value>
        </property>
<!--
        <property>
                <name>hive.metastore.uris</name>
                <value>thrift://node3.hadoop.com:9083</value>
        </property>
-->
</configuration>

hive在使用mysql作为元数据的时候，mysql的数据库编码不能是UTF8,hive默认使用的latain1 这种编码

3.上传mysql的lib驱动包

将mysql的lib驱动包上传到hive的lib目录下
cd /export/servers/hive-1.1.0-cdh5.14.0/lib
将mysql-connector-java-5.1.38.jar 上传到这个目录下

6.hive交互方式

第一种交互方式：Hive交互shell

cd /export/servers/hive-1.1.0-cdh5.14.0
bin/hive

查看所有的数据库
hive (default)> show databases;

创建一个数据库
hive (default)> create database myhive;
使用该数据库并创建数据库表
hive (default)> use myhive;
hive (myhive)> create table test(id int,name string);

以上命令操作完成之后，一定要确认mysql里面出来一个数据库hive

第二种交互方式：Hive JDBC服务

启动hiveserver2服务
前台启动
cd  /export/servers/hive-1.1.0-cdh5.14.0
bin/hive --service hiveserver2

后台启动
cd  /export/servers/hive-1.1.0-cdh5.14.0
nohup bin/hive --service hiveserver2  2>&1 &

beeline连接hiveserver2
bin/beeline
beeline> !connect jdbc:hive2://node3.hadoop.com:10000
用户名和密码随便输出，但是用户名一定要有权限去操作hdfs的数据

注意：如果使用beeline方式连接hiveserver2，一定要保证hive在mysql当中的元数据库已经创建成功，不然就会拒绝连接

第三种交互方式：Hive命令

bin/hive -e 不进入hvie的客户端直接执行hql语句
bin/hive -f hive.sql 不进入hive的客户端，直接执行hive的脚本文件

三、hive基本操作

3.1、创建数据库与创建数据库表

创建数据库

create database if not exists myhive;
use  myhive;

创建一个hive的数据库，就会创建一个对应的文件夹在这个路径下面
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/warehouse</value>

创建数据库并指定hdfs存储位置
create database myhive2 location '/myhive2';

修改数据库
可以使用alter  database  命令来修改数据库的一些属性。但是数据库的元数据信息是不可更改的，包括数据库的名称
以及数据库所在的位置
alter  database  myhive2  set  dbproperties('createtime'='20200419');

查看数据库基本信息
desc  database  myhive2;
查看数据库更多详细信息
desc database extended  myhive2;  

删除数据库
删除一个空数据库，如果数据库下面有数据表，那么就会报错
drop  database  myhive2;
强制删除数据库，包含数据库下面的表一起删除
drop  database  myhive  cascade;

创建数据库表

建表语法

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], …)]
[CLUSTERED BY (col_name, col_name, …)
[SORTED BY (col_name [ASC|DESC], …)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]

external 定义我们的表为外部表
location  指定我们表里面的数据应该存放在hdfs的哪个位置 
partitioned by 创建分区表  按照文件夹，将不同的数据，划分到不同的文件夹下面去
clustered  by   分桶表  将我们的数据按照一定的规则，划分到多个文件里面去
store as  指定数据的存储格式 text  sequenceFile  parquet  orc
row  format    指定我们hive建表的分隔符，与我们hdfs的数据的分隔符保持一致，才能够映射hdfs上面的数据到
对应的hive的表的字段里面来
LIKE 允许用户复制现有的表结构，但是不复制数据。

hive当中的四种常见的表模型：

第一种表模型：管理表

hive建表初体验
use myhive;
create table stu(id int,name string);
insert into stu values (1,"zhangsan");
select * from stu;

hive当中的默认分隔符： \001  键盘打不出来  asc码值  非打印字符  避免分隔符的冲突
第一种表模型：管理表，又叫做内部表  删除表的时候，hdfs上面对应的数据，同步删除

Hive建表时候的字段类型：

创建表指定字段之间的分隔符，指定数据的存储格式，指定数据的存放位置

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;

仅仅复制表结构，不复制表数据

create table stu4 like stu2;

查看表的类型

desc formatted  tableName;

删除表

drop table tableName

第二种表模型：外部表

外部表：删表的时候不会删除hdfs上面的数据

指定hdfs其他位置的路径的数据，加载到hive的表当中来
hive认为数据是从其他地方移动过来的，hive表没有完全的独占这份数据，删除表的时候不能够删除数据

管理表和外部表的使用场景：
每天将收集到的网站日志定期流入HDFS文本文件。在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、
结果表使用内部表存储，数据通过SELECT+INSERT进入内部表。

操作案例

分别创建老师与学生表外部表，并向表中加载数据
创建老师表：create exte