Hive基础操作

一.SQL,Hive和MapReduce的关系

用户在hive上编写sql语句,hive把sql语句转为mapreduce程序去执行

二.Hive架构映射流程

用户接口： 
    	包括 CLI、JDBC/ODBC、WebGUI。其中，CLI(command line interface)为shell命令行；Hive中的Thrift服务器允许外部客	户端通		过网络与Hive进行交互，类似于JDBC或ODBC协议。WebGUI是通过浏览器访问Hive。
    -- Hive提供了 Hive Shell、 ThriftServer等服务进程向用户提供操作接口
    
Driver:包括语法解析器、计划编译器、优化器、执行器
   		 作用 完成 HQL 查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划存储在 HDFS 中，并在随后有 	 MapReduce 调用执行。
    注意：这部分内容不是具体的服务进程，而是封装在Hive所依赖的Jar包中Java代码中。
    

元数据包含:用Hive创建的database、table、表的字段等元信息。
元数据存储:存在关系型数据库中,如:hive内置的Derby数据库或者第三方MySQL数据库等,一般使用Mysql数据库。
Metastore:即元数据存储服务，
作用是: 客户端连接metastore服务，metastore再去连接MySQL等数据库来存取元数据。
特点: 有了metastore服务，就可以有多个客户端同时连接，而且这些客户端不需要知道MySQL等数据库的用户名和密码，只需要连接metastore 服务即可。

三.MetaStore元数据管理三种模式

metastore服务配置有3种模式: 内嵌模式、本地模式、远程模式

推荐使用: 远程模式

内嵌模式:
    优点: 配置简单 hive命令直接可以使用
    缺点: 不适用于生产环境,derby和Metastore服务都嵌入在主Hive Server进程中,一个服务只能被一个客户端连接(如果用两个客户端以上就非常浪费资源),且元数据不能共享

    
本地模式: 
    优点:可以单独使用外部的数据库(mysql),元数据共享
    缺点:相对浪费资源,metastore嵌入到了hive进程中,每启动一次hive服务，都内置启动了一个metastore。
    
远程模式:
    优点:可以单独使用外部库(mysql),可以共享元数据,本地可以连接metastore服务也可以连接hiveserver2服务,增加了扩展性(其他依赖hive的软件都可以通过Metastore访问hive)
    缺点:需要注意的是如果想要启动hiveserver2服务需要先启动metastore服务

四.hive服务操作[重点]

启动hive服务

[root@node1 /]# nohup hive --service metastore &
[1] 10693
[root@node1 /]nohup: 忽略输入并把输出追加到"nohup.out"

[root@node1 /]# nohup hive --service hiveserver2 &
[2] 10829
您在 /var/spool/mail/root 中有新邮件
[root@node1 /]nohup: 忽略输入并把输出追加到"nohup.out"

[root@node1 /]# jps
2704 NodeManager
2209 DataNode
10946 Jps
10693 RunJar
3208 JobHistoryServer
2075 NameNode
2557 ResourceManager
10829 RunJar

[root@node1 /]# lsof -i:10000
COMMAND   PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
java    10829 root  522u  IPv6 244847      0t0  TCP *:ndmp (LISTEN)

关闭hive服务

[root@node1 /]# jps
2704 NodeManager
2209 DataNode
10946 Jps
10693 RunJar
3208 JobHistoryServer
2075 NameNode
2557 ResourceManager
10829 RunJar
您在 /var/spool/mail/root 中有新邮件
[root@node1 /]# kill -9 10693 10829 
[root@node1 /]# jps
2704 NodeManager
2209 DataNode
10946 Jps
3208 JobHistoryServer
2075 NameNode
2557 ResourceManager
[1]-  已杀死               nohup hive --service metastore
[2]+  已杀死               nohup hive --service hiveserver2

五.客户端连接[重点]

datagrip连接hive服务

创建datagrip项目

连接hive

datagrip连接mysql

六.数据仓库和数据库

数据仓库和数据库的区别

数据库与数据仓库的区别:实际讲的是OLTP与OLAP的区别
OLTP（On-Line Transaction Processin）:叫联机事务处理，也可以称面向用户交易的处理系统，  主要面向用户进行增删改查

OLAP（On-Line Analytical Processing）:叫联机分析处理,一般针对某些主题的历史数据进行分析 主要面向分析,支持管理决策。

数据仓库主要特征:面向主题的（Subject-Oriented ）、集成的（Integrated）、非易失的（Non-Volatile）和时变的（Time-Variant）

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

数据仓库基础三层架构

源数据层（ODS）：此层数据无任何更改，直接沿用外围系统数据结构和数据，不对外开放；为临时存储层，是接口数据的临时存储区域，为后一步的数据处理做准备。

数据仓库层（DW）：也称为细节层，DW层的数据应该是一致的、准确的、干净的数据，即对源系统数据进行了清洗（去除了杂质）后的数据。

数据应用层（DA或APP）：前端应用直接读取的数据源；根据报表、专题分析需求而计算生成的数据。

ETL和ELT

广义上ETL:数据仓库从各数据源获取数据及在数据仓库内的数据转换和流动都可以认为是ETL（抽取Extract, 转化Transform , 装载Load）的过程。

但是在实际操作中将数据加载到仓库却产生了两种不同做法:ETL和ELT。

狭义上ETL: 先从数据源池中抽取数据，数据保存在临时暂存数据库中（ODS）。然后执行转换操作，将数据结构化并转换为适合目标数据仓库系统的形式,然后将结构化数据加载到数据仓库中进行分析。

ELT: 从数据源中抽取后立即加载。没有专门的临时数据库（ODS），这意味着数据会立即加载到单一的集中存储库中,数据在数据仓库系统中直接进行转换，然后进行分析

七.hive数据库操作

基本操作[掌握]

知识点:

创建数据库: create database [if not exists] 库名 [location '路径'];

使用数据库: use 库名;

注意: location路径默认是:  hdfs://node1:8020/user/hive/warehouse/库名.db

删除数据库: drop database 数据库名 [cascade];

示例:

-- hive库的核心操作
-- 创建数据库
-- 注意: 默认location路径是/user/hive/warehouse/库名.db
-- 库路径: /user/hive/warehouse/hive1.db
create database hive1;
-- 库路径: /user/hive/warehouse/test.db
create database test;
-- 使用库
use hive1;
-- 注意: 建库的时候可以使用location修改数据库路径
-- 库路径: /test1
create database test1 location '/test1';

-- 为了方便演示location效果,可以先去分别创建一个简单的表
-- 表路径:/user/hive/warehouse/hive1.db/stu
create table hive1.stu(id int,name string);
-- 表路径:/test1/stu
create table test1.stu(id int,name string);

-- 演示删除空数据库
drop database test;
-- 盐水删除非空数据库
drop database test1; -- 报错,hive比较特殊,drop不能直接删除有表的库
-- drop+cascade能够删除有表的库
drop database test1 cascade ;

其他操作[了解]

知识点:

创建数据库: create database [if not exists] 库名 [comment '注释'] [location '路径'] [with dbproperties ('k'='v')];

修改数据库路径: alter database 库名 set location 'hdfs://node1.itcast.cn:8020/路径'
修改数据库属性: alter database 库名 set dbproperties ('k'='v');

查看所有的数据库: show databases;
查看某库建库语句: show create database 库名;
查看指定数据库信息: desc database 库名;
查看指定数据库扩展信息: desc database extended 库名;
查看当前使用的数据库: select current_database();

示例:

-- hive库的其他操作
-- schema在hive/mysql中相当于database关键字
create schema demo1;
-- comment: 建库的时候可以添加注释,建议不要中文,因为乱码
-- location: 建表的时候可以指定hdfs上库目录路径,建议使用默认路径/user/hive/warehouse/库名.db
-- with dbproperties: 建表的时候可以设置属性,格式是k=v,了解即可
create database demo2
    comment 'database'
    location '/user/hive/warehouse/demo2.db'
    with dbproperties ('name' = 'bz666');
create database demo3;


-- 查看指定库的建库语句
show create database demo2;
show create database demo3;
-- 查看所有的数据库
show databases;
-- 查看当前使用数据库
select current_database();
-- 查看指定库的基本信息
desc database demo2;
-- 查看指定库的扩展信息
desc database extended  demo2;

-- 修改location路径
-- 注意: 必须是绝对路径,而且修改后的路径如果不存在,不会直接创建
alter database demo2 set location 'hdfs://node1.itcast.cn:8020/demo2.db';
-- 以后建表的时候自动创建
create table demo2.stu(id int,name string);
-- 再次查看指定库的扩展信息
desc database extended  demo2;

-- 修改dbproperties
alter database demo2 set dbproperties('name'='binzi');
-- 再次查看指定库的扩展信息
desc database extended  demo2;

八.Hive表概述

建表语法

create [external] table [if not exists] 表名(字段名 字段类型 , 字段名 字段类型 , ... )
[partitioned by (分区字段名 分区字段类型)] # 分区表固定格式
[clustered by (分桶字段名)  into 桶个数 buckets]  # 分桶表固定格式 注意: 可以排序[sorted by (排序字段名 asc|desc)]
[row format delimited fields terminated by '字段分隔符'] # 自定义字段分隔符固定格式
[stored as textfile]  # 默认即可
[location 'hdfs://node1.itcast.cn:8020/user/hive/warehouse/库名.db/表名'] # 默认即可
; # 注意: 最后一定加分号结尾

注意: 关键字顺序是从上到下从左到右,否则报错

数据类型

基本数据类型:  整数: int  小数: float double  字符串: string varchar(长度)  日期: date timestamp

复杂数据类型:  集合: array  映射: map   结构体: struct  联合体: union 

表分类

Hive中可以创建的表有好几种类型， 分别是：
内部表(管理表): MANAGED_TABLE
    分区表
    分桶表
外部表(非管理表): EXTERNAL_TABLE
    分区表
    分桶表
    
default默认库存储路径: hdfs://node1.itcast.cn:8020/user/hive/warehouse   
自定义库在HDFS的默认存储路径: hdfs://node1.itcast.cn:8020/user/hive/warehouse/库名.db
自定义表在HDFS的默认存储路径: hdfs://node1.itcast.cn:8020/user/hive/warehouse/库名.db/表名
业务数据文件在HDFS的默认存储路径: hdfs://node1.itcast.cn:8020/user/hive/warehouse/库名.db/表名/业务数据文件

内部表和外部表区别?
内部表: 未被external关键字修饰的即是内部表， 即普通表。 内部表又称管理表,还可以叫托管表
	删除内部表:直接删除元数据（metadata）和存储数据本身
外部表: 被external关键字修饰的即是外部表， 即关联表。 还可以叫非管理表或非托管表
	删除外部表:仅仅是删除元数据（metadata），不会删除存储数据本身

-- 内部表(又叫管理表或者托管表)
create table stu1(
    id int,
    name string
);
-- 外部表(又叫非管理表,非托管表)
create external table stu2(
    id int,
    name string
);
-- 查看表结构
desc stu1;
desc stu2;
-- 查看表格式化信息
desc formatted stu1; -- 内部表类型: managed_table
desc formatted stu2; -- 外部表类型: external_table

-- 演示内外部表的重点区别
-- 删除内部表(管理表/托管表),会删除表相关的所有数据
insert into stu1 values(1,'张三');
drop table stu1;
-- 删除外部表,只删除了元数据,hdfs中业务数据保留
insert into stu2 values(1,'张三');
drop table stu2;
-- 再次建表后,可以使用location重新关联原来hdfs保留的业务数据
create external table stu22(
    id int,
    name string
)location '/user/hive/warehouse/hive1.db/stu2';
-- 验证数据
select * from stu22 limit 10;

九.hive数据表的操作[预习]

分隔符

默认分隔符: 展示形式是 SOH , /0001 ,□等

自定义分隔符:... row format delimited fields terminated by '指定分隔符'

-- 演示默认分隔符
create table hive01.stu (
    id int ,
    name string,
    age int
);
-- 数据可以使用insert方式,也可以直接上传文件
-- 演示insert方式
insert into hive01.stu(id,name) values(1,'张三'),(2,'李四'),(3,'王五');
-- 查看数据
select * from hive01.stu;


-- 演示自定义分隔符
create table hive01.stu2 (
    id int ,
    name string,
    age int
)row format delimited
fields terminated by ',';
-- 数据可以使用insert方式,也可以直接上传文件
-- 演示insert方式
insert into hive01.stu2(id,name) values(1,'张三'),(2,'李四'),(3,'王五');
-- 查看数据
select * from hive01.stu2;

HDFS上传文件映射表

内部表

知识点

创建普通内部表: create table [if not exists] 表名(字段名 字段类型 , 字段名 字段类型...) [row format delimited fields terminated by '指定分隔符'];

删除内部表: drop table 内部表名;       注意: 删除mysql中元数据同时也会删除hdfs中存储数据

修改表名: alter table 旧表名 rename to 新表名;
修改表字段名称和类型: alter table 表名 change 旧字段名 新字段名 新字段类型;
修改表之添加字段(列): alter table 表名 add columns (字段名 字段类型);
修改表之替换字段(列):alter table 表名 replace columns (字段名 字段类型);

查看所有表: show tables;
查看指定表基本信息: desc 表名; 
查看指定表扩展信息: desc extended 表名;
查看指定表格式信息: desc formatted 表名;
查看指定表建表语句: show create table 表名;

示例:

-- 操作表的前提: 要先有库,并使用它
create database myhive;
use myhive;
-- 1.内部表操作: 创建内部表
-- 创建普通数据表
create table if not exists stu(id int ,name string);
-- 插入数据
insert into stu values(1,'张三');
-- 查询所有数据
select * from stu;
-- 上述stu使用的是默认分隔符:展示形式是 /0001,SOH,□...
-- 键盘不好输入,需要自定指定分隔符


-- 创建普通数据表: 指定分隔符
create table if not exists stu1(
    id int ,
    name string
)
row format delimited fields terminated by ',';
-- 插入数据
insert into stu1 values(1,'李四');
-- 查询所有数据
select * from stu1;


-- 其他创建内部表的形式
-- 建表时复制其他表的结构和数据: create table 表名 as select语句
create table stu2 as select * from stu1;

-- 建表时复制其他表的结构: create table 表名 like 其他表名;
create table stu3 like stu1;

-- 2.查看表
-- 查看所有表
show tables;
-- 查看指定表基本信息
desc stu1;
-- 查看指定表扩展信息
desc extended stu1;
-- 查看指定表格式后的信息
desc formatted stu1;
-- 查看指定表建表语句
show create table stu1;


-- 3.修改表
-- 修改表名
alter table stu1 rename to stu111;
-- 修改表字段:添加列
alter table stu2 add columns (gender int);
-- 修改表字段:改名称和类型
alter table stu2 change id sid string;
-- 修改表字段:替换列(删除/添加)
alter table stu2 replace columns (sid string);
alter table stu2 replace columns (sid string,age int);

-- 4.删除表
drop table stu111;
truncate table stu;

--看指定表基本信息
desc stu1;
-- 查看指定表扩展信息
desc extended stu1;
-- 查看指定表格式后的信息
desc formatted stu1;
-- 查看指定表建表语句
show create table stu1;


-- 3.修改表
-- 修改表名
alter table stu1 rename to stu111;
-- 修改表字段:添加列
alter table stu2 add columns (gender int);
-- 修改表字段:改名称和类型
alter table stu2 change id sid string;
-- 修改表字段:替换列(删除/添加)
alter table stu2 replace columns (sid string);
alter table stu2 replace columns (sid string,age int);

-- 4.删除表
drop table stu111;
truncate table stu;