hive

hive
1.什么是hive,hive的作用
Hive一个数据仓库工具,可以将结构化的数据文件映射为一张表，并提供类似于SQL查询(HQL),本质是将Hive SQL转化成MapReduce程序.
2.hive工作原理

在hive中创建一张表,这张表要映射到hdfs中的数据(结构化的静态数据),形成映射关系;

表结构是根据要处理的数据来决定的,(描述表结构的信息,指定处理数据的位置)元数据,元数据存储在mysql中;

当在hive客户端中写sql语句时;

首先要加载mysql中的元数据;

然后解析sql语句,分析,优化,将其转换成MapReduce程序,从hdfs中读取数据来处理数据
3.hive的安装
DFS和YARN
start-all.sh
[root@linux01 ~]# jps
44513 ResourceManager
44117 NameNode
45030 Jps
44616 NodeManager
5834 QuorumPeerMain
44223 DataNode
2 mysql正常运行

1. chkconfig mysqld on 开机自启
2. service mysqld start 启动服务
3. netstat -nltp | grep 3306
   tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 1313/mysqld 正常启动
4. mysql -uroot -proot
   3 开启mysql的远程连接权限
   在mysql中执行
   mysql > grant all privileges on . to ‘root’@’%’ identified by ‘root’ with grant option;

---

2.上传hive的安装包,解压
tar -zxf apache-hive-2.3.5.tar.gz

1. cp hive-env.sh.template hive-env.sh
   vi hive-env.sh
   export HADOOP_HOME=/opt/apps/hadoop-2.8.5/

Hive Configuration Directory can be controlled by:

export HIVE_CONF_DIR=/opt/apps/hive-2.3.1/conf/

4. 修改配置文件
   vi hive-site.xml
   -----------------***********
   

javax.jdo.option.ConnectionURL jdbc:mysql://linux01:3306/hive?createDatabaseIfNotExist=true&useSSL=false javax.jdo.option.ConnectionDriverName com.mysql.jdbc.Driver javax.jdo.option.ConnectionUserName root javax.jdo.option.ConnectionPassword root hive.metastore.warehouse.dir /user/hive/warehouse hive.exec.scratchdir /user/hive/tmp hive.querylog.location /user/hive/log hive.server2.thrift.port 10000 hive.server2.thrift.bind.host 0.0.0.0 hive.server2.webui.host 0.0.0.0 hive.server2.webui.port 10002 hive.server2.long.polling.timeout 5000 hive.server2.enable.doAs true datanucleus.autoCreateSchema false datanucleus.fixedDatastore true hive.execution.engine mr hive.zookeeper.quorum lx01,lx02,lx03

-----------------***********
5. vi /opt/apps/hadoop-2.8.5/etc/hadoop/core-site.xml

dfs.permissions.enabled false hadoop.proxyuser.root.hosts * hadoop.proxyuser.root.groups *

6.拷贝一个mysql的jdbc驱动jar包到hive的lib目录中

7 重启hdp
stop-all.sh
start-all.sh
8. 初始化hive的元数据库
${HIVE_HOME}/bin/schematool -initSchema -dbType mysql

1. 在mysql中多一个hive数据库 记录元数据信息
2. 启动以后在HDFS中的目录中 /user/hive目录

9.配置hive的环境变量
export HIVE_HOME=/opt/apps/hive-2.3.1
export PATH=$PATH:$HIVE_HOME/bin
4.hive的两种连接方式

(1)hive命令直接可以在本地启动一个客户端操作hive

(2)可以启动hiveserver2的服务 监听的10000端口

1)可以使用java程序连接(jdbc)

2)可以使用hive自带的beeline方式连接

(3)hiveserver2 前台启动

hiveserver2  & 后台启动 

netstat  -nltp | grep 10000 是否有端口占用

(4)beeline 输入此命令

!connect jdbc:hive2://linux01:10000

用户名 : root;密码:  回车
5.数据类型
Hive数据类型	Java数据类型	长度	例子

TINYINT byte 1byte有符号整数 20
SMALINT short 2byte有符号整数 20
INT int 4byte有符号整数 20
BIGINT long 8byte有符号整数 20
BOOLEAN boolean 布尔类型，true或者false TRUE FALSE
FLOAT float 单精度浮点数 3.14159
DOUBLE double 双精度浮点数 3.14159
STRING string 字符系列。可以指定字符集。可以使用单引号或者双引号。 ‘now is the time’ “for all good men”
TIMESTAMP 时间类型
BINARY 字节数组
对于Hive的String类型相当于数据库的varchar类型，该类型是一个可变的字符串，不过它不能声明其中最多能存储多少个字符，理论上它可以存储2GB的字符数.
int
bigint
boolean
double
string
TIMESTAMP

复杂的数据类型

STRUCT(结构体)对象 和c语言中的struct类似，都可以通过“点”符号访问元素内容。例如，如果某个列的数据类型是STRUCT{first STRING, last STRING},那么第1个元素可以通过字段.first来引用。 struct()
MAP 映射 MAP是一组键-值对元组集合，使用数组表示法可以访问数据。例如，如果某个列的数据类型是MAP，其中键->值对是’first’->’John’和’last’->’Doe’，那么可以通过字段名[‘last’]获取最后一个元素 map()
ARRAY 数组 数组是一组具有相同类型和名称的变量的集合。这些变量称为数组的元素，每个数组元素都有一个编号，编号从零开始。例如，数组值为[‘John’, ‘Doe’]，那么第2个元素可以通过数组名[1]进行引用。 Array()
6.对数据库的操作

(1)显示系统所有的数据库

show databases;

(2)创建数据库

create database db_name;

(3)如果数据库已经存在会报错

create database if not exists db_name;这样创建会避免报错

(4)创建数据库指定库的位置不指定在/user/hive/warehouse

关键字 location 指定表或者是数据库的位置(HDFS中)

create database if not exists abc location “/abc” ;

(5)显示所有的数据库(模糊查询)

show databases like “b*” ;

(6)显示数据库的详细信息

desc database abc;

desc database extended abc;

(7)切换数据库

use databasename;

查看当前正在被使用的数据库

(8)select current_database();

(9)删除数据库

drop database abc;

(10)修改数据库

alter database abc set dbproperties(“author”=“abc”);
7.建表语法和导入数据方式

基本语法

create table if not exists tb_name(
列 数据类型,
列 数据类型,
…
)
row format delimited fields terminated by “分隔符
location “/ABC”;(数据在hdfs中的位置)

desc tb_name;显示表的信息

desc formatted tb_abc;显示表的详细信息
数据导入方式总结

将数据导入到表所在的HDFS 的文件夹中 数据就直接加载

核心原理:select查询操作的数据就是表文件夹下的数据

1.默认的表目录2.location指定目录

(1)导入数据的方式一:直接将数据文件上传到对应的表的目录下
hdfs dfs -put abc.csv /tb_product

(2)导入数据的方式二:使用命令导入本地文件

load data local inpath “/hive/abc.csv” into table tb_abc;

底层就是将本地的文件上传到表所在的HDFS的目录下

(3)导入数据的方式三:使用命令导入HDFS文件到表中
load data inpath “/abc.csv” into table tb_abc;

底层将这个文件移动到对应的表的目录下

导入新的数据,覆盖原来的数据

overwrite

load data local inpath “/hive/abc.csv” overwrite into table tb_abc;

(4)导入方式四:建表的时候指定表的位置 ----> 就是数据在hdfs的位置

create table if not exists tb_ABC(
id int ,
name string ,
price double ,
cate string COMMENT ‘类别’,
pnum int ,
description string
)
row format delimited fields terminated by “,”
location “/ABC”;

(5)建表,使用insert 方式插入数据

1. insert语法不要一条一条数据的insert,因为一次insert在HDFS中生成一个小文件
   insert into tb_abc values();

2. insert into tb_abc valeus(),(),(),();

3)****** insert into tb_abc select id+125,name from tb_def;将后面的select运算结果保存到某个表中

overwrite table

insert overwrite table tb_abc select id+125, name from tb_def;

4)将结果数据直接保存在一个新的表中

create table if not exists tb_phone as select * from tb_product where cate = ‘手机’ ;

5)import一定是导出的数据才能导入

导出数据到HDFS中

export table tb_abc to ‘/user/hive/warehouse/export/abc’;

导入数据方式
import table tb_def from ‘/user/hive/warehouse/export/abc’;
8.什么是内部表(管理表),外部表, 以及他们的应用场景

内部表和外部表

内部表 管理表 默认的表类型

删除表的时候 存储数据的文件夹会被删除

外部表

删除表的时候 存储数据的文件夹不会被删除

内部表:业务表使用内部表用

外部表:原始数据,公用的数据使用外部表

//默认的内部表,删除表会删除数据
9.导出数据方式

导出方式总结
导出数据
(1)将数据导出到本地的文件夹中

1)没有指定数据的分隔符

insert overwrite local directory ‘/hive/abc/’ select * from tb_abc;
2)指定分割符

insert overwrite local directory ‘/hive/abcabc/’

ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’

select * from tb_product;

(2)将数据导出到HDFS文件夹中

1)没有指定数据的分隔符

insert overwrite directory ‘/hive/abc/’ select * from tb_product;

2)指定分割符

insert overwrite directory ‘/hive/abcabc/’

ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’

select * from tb_product;

(3)使用HDFS 提供的命令
1)hdfs dfs -get
2)hive命令行中 dfs -get

dfs -get /user/hive/warehouse/db_doit15.db/tb_phone/000000_0 /hive/phone

(4)使用 hive命令行中 >> >

1. hive -e “sql语句”

2. hive -f 文件

使用域shell脚本调度SQL任务的时候使用

(5)export 导出数据

(defahiveult)> export table default.student to

‘/user/hive/warehouse/export/student’;

(6)sqoop数据迁移工具 将mysql中的数据 导入到HDFS中(hive表的目录下)将HDFS 中的结构化数据(hive表) 导入到mysql的表中
用来结构化数据的数据迁移命令 底层 MR 程序

10.分区表和静态分区
分区表 : hive中处理的数据在HDFS中 , select * from tb_name ;

查询表中的数据是加载HDFS中文件夹下的数据
文件夹下的数据很多

select * from tb_name where dt=“2020-06-17” ; – 查询昨天的数据
select * from tb_name where dt=“2020-06-18” ; – 查询今天的数据
按照日期维度查询 全局检索数据 过滤 慢
01,2020-06-18,200
02,2020-06-18,200
03,2020-06-18,100
12,2020-06-19,200
13,2020-06-19,100
13,2020-06-19,200
03,2020-06-18,200
04,2020-06-18,200
05,2020-06-18,20
06,2020-06-18,100
07,2020-06-18,200
08,2020-06-18,200
09,2020-06-18,100
10,2020-06-18,200
11,2020-06-19,20
14,2020-06-19,200
15,2020-06-19,20
16,2020-06-19,100
17,2020-06-19,200
18,2020-06-19,200
19,2020-06-19,100

create table tb_order(
oid int ,
dt string ,
cost double
)
row format delimited fields terminated by “,” ;

load data local inpath “/hive/data/order.txt” into table tb_order ;
select * from tb_order where dt=‘2020-06-18’ ;

分区表:将数据按照查询的维度分文件夹管理

tb_order/
2020-06-17/
文件1
文件2
2020-06-18/
文件3
文件4
2020-06-19/
文件5
1 什么是分区表
2 为什么有分区表 意义

---

每天都会产生订单数据

06-18.txt
01,2020-06-18,200
02,2020-06-18,200
03,2020-06-18,100
03,2020-06-18,200
04,2020-06-18,200
05,2020-06-18,20
06,2020-06-18,100
07,2020-06-18,200
08,2020-06-18,200
09,2020-06-18,100
10,2020-06-18,200
06-19.txt
11,2020-06-19,20
14,2020-06-19,200
15,2020-06-19,20
16,2020-06-19,100
17,2020-06-19,200
18,2020-06-19,200
19,2020-06-19,100
12,2020-06-19,200
13,2020-06-19,100
13,2020-06-19,200

没有分区表
create table tb_order2(
oid int ,
dt string ,
cost double
)
row format delimited fields terminated by “,” ;

load data local inpath “/hive/data/06-18.txt” into table tb_order2 ;
load data local inpath “/hive/data/06-19.txt” into table tb_order2 ;

select * from tb_order where dt=‘2020-06-18’ ; --两个文件

使用分区表

create table tb_p_order(
oid int ,
dt string ,
cost double
)
partitioned by (dy string)
row format delimited fields terminated by “,” ;

load data local inpath “/hive/data/06-18.txt” into table tb_p_order partition(dy=“06-18”);
load data local inpath “/hive/data/06-19.txt” into table tb_p_order partition(dy=“06-19”);

0: jdbc:hive2://linux01:10000> select * from tb_p_order where  dy="06-18";

±----------------±---------------±-----------------±---------------+
| tb_p_order.oid | tb_p_order.dt | tb_p_order.cost | tb_p_order.dy |
±----------------±---------------±-----------------±---------------+
| 1 | 2020-06-18 | 200.0 | 06-18 |
| 2 | 2020-06-18 | 200.0 | 06-18 |
| 3 | 2020-06-18 | 100.0 | 06-18 |
| 3 | 2020-06-18 | 200.0 | 06-18 |
| 4 | 2020-06-18 | 200.0 | 06-18 |
| 5 | 2020-06-18 | 20.0 | 06-18 |
| 6 | 2020-06-18 | 100.0 | 06-18 |
| 7 | 2020-06-18 | 200.0 | 06-18 |
| 8 | 2020-06-18 | 200.0 | 06-18 |
| 9 | 2020-06-18 | 100.0 | 06-18 |
| 10 | 2020-06-18 | 200.0 | 06-18 |
±----------------±---------------±-----------------±---------------+

11.修改表
1.重命名表

ALTER TABLE table_name RENAME TO new_table_name;
2.添加列
alter table dept_partition add columns(deptdesc string);
3.更新列
hive (default)> alter table dept_partition change column deptdesc desc int;
4.替换列
hive (default)> alter table dept_partition replace columns(deptno string, dname string, loc string);
12.基本查询语法
查询
查询语法

select
expr[字段 , 常量 , 运算 , 函数]
id , name , price ,
“hello” ,
1+2 ,
price*2 as double_price ,
pnum+10 as n_num ,
current_date() ,

sum() ,
max() ,
自定义的函数()

from
tb_name;

根据员工数据和部门数据建表 使用load方式导入数据
–员工表
create table if not exists tb_emp(
empno int ,
ename string ,
job string ,
mrg int ,
dt string ,
sal double ,
comm double ,
deptno int
)
row format delimited fields terminated by “\t” ;
load data local inpath “/hive/data/emp.txt” into table tb_emp ;
– 部门表
create external table if not exists tb_dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by ‘\t’;
load data local inpath “/hive/data/dept.txt” into table tb_dept ;

0: jdbc:hive2://linux01:10000> select * from tb_emp ;
±--------------±--------------±------------±------------±------------±------------±-------------±---------------+
| tb_emp.empno | tb_emp.ename | tb_emp.job | tb_emp.mrg | tb_emp.dt | tb_emp.sal | tb_emp.comm | tb_emp.deptno |
±--------------±--------------±------------±------------±------------±------------±-------------±---------------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.0 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-2-20 | 1600.0 | 300.0 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-2-22 | 1250.0 | 500.0 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-4-2 | 2975.0 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-9-28 | 1250.0 | 1400.0 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-5-1 | 2850.0 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-6-9 | 2450.0 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-4-19 | 3000.0 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.0 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-9-8 | 1500.0 | 0.0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-5-23 | 1100.0 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-3 | 950.0 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-3 | 3000.0 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-1-23 | 1300.0 | NULL | 10 |
±--------------±--------------±------------±------------±------------±------------±-------------±---------------+
14 rows selected (0.427 seconds)
0: jdbc:hive2://linux01:10000> select * from tb_dept ;
±----------------±---------------±-------------+
| tb_dept.deptno | tb_dept.dname | tb_dept.loc |
±----------------±---------------±-------------+
| 10 | ACCOUNTING | 1700 |
| 20 | RESEARCH | 1800 |
| 30 | SALES | 1900 |
| 40 | OPERATIONS | 1700 |
±----------------±---------------±-------------+
— 练习题
1 查询每个人的姓名和工作以及工资
select
ename ,
job,
sal
from
tb_emp ;

±--------±-----------±--------+
| ename | job | sal |
±--------±-----------±--------+
| SMITH | CLERK | 800.0 |
| ALLEN | SALESMAN | 1600.0 |
| WARD | SALESMAN | 1250.0 |
| JONES | MANAGER | 2975.0 |
| MARTIN | SALESMAN | 1250.0 |
| BLAKE | MANAGER | 2850.0 |
| CLARK | MANAGER | 2450.0 |
| SCOTT | ANALYST | 3000.0 |
| KING | PRESIDENT | 5000.0 |
| TURNER | SALESMAN | 1500.0 |
| ADAMS | CLERK | 1100.0 |
| JAMES | CLERK | 950.0 |
| FORD | ANALYST | 3000.0 |
| MILLER | CLERK | 1300.0 |
±--------±-----------±--------+
2 奖金不为null的人

select
empno ,
ename ,
comm
from
tb_emp
where comm is not null
;
±-------±--------±--------+
| empno | ename | comm |
±-------±--------±--------+
| 7499 | ALLEN | 300.0 |
| 7521 | WARD | 500.0 |
| 7654 | MARTIN | 1400.0 |
| 7844 | TURNER | 0.0 |
±-------±--------±--------+
有奖金的人
select
empno ,
ename ,
comm
from
tb_emp
where comm is not null
and comm > 0
;
±-------±--------±--------+
| empno | ename | comm |
±-------±--------±--------+
| 7499 | ALLEN | 300.0 |
| 7521 | WARD | 500.0 |
| 7654 | MARTIN | 1400.0 |
±-------±--------±--------+

3 查询每个人的姓名和工作以及工资(计算奖金)
任何数字和NULL运算都是null

select
ename ,
job,
(sal+comm) as total_sal
from
tb_emp ;
±--------±-----------±-----------+
| ename | job | total_sal |
±--------±-----------±-----------+
| SMITH | CLERK | NULL |***
| ALLEN | SALESMAN | 1900.0 |
| WARD | SALESMAN | 1750.0 |
| JONES | MANAGER | NULL |***
| MARTIN | SALESMAN | 2650.0 |
| BLAKE | MANAGER | NULL |***
| CLARK | MANAGER | NULL |***
| SCOTT | ANALYST | NULL |***
| KING | PRESIDENT | NULL |***
| TURNER | SALESMAN | 1500.0 |
| ADAMS | CLERK | NULL |***
| JAMES | CLERK | NULL |***
| FORD | ANALYST | NULL |***
| MILLER | CLERK | NULL |***
±--------±-----------±-----------+

0: jdbc:hive2://linux01:10000> select 1+NULL ;
±------+
| _c0 |
±------+
| NULL |
±------+
1 row selected (0.18 seconds)
0: jdbc:hive2://linux01:10000> select 2*NULL ;
±------+
| _c0 |
±------+
| NULL |
±------+
1 row selected (0.154 seconds)
0: jdbc:hive2://linux01:10000> select current_date() ;
±------------+
| _c0 |
±------------+
| 2020-06-18 |
±------------+

思路 comm ==null ? 0 : comm
hive中对null值的处理 nvl(要判断的字段 , 如果是null的默认值)
nvl(comm , 0) 如果奖金为null 赋值成0
select nvl(comm , 0) from tb_emp ;
±--------+
| _c0 |
±--------+
| 0.0 |
| 300.0 |
| 500.0 |
| 0.0 |
| 1400.0 |
| 0.0 |
| 0.0 |
| 0.0 |
| 0.0 |
| 0.0 |
| 0.0 |
| 0.0 |
| 0.0 |
| 0.0 |
±--------+

select
ename ,
job ,
(sal+nvl(comm,0)) as total_sal
from
tb_emp ;
±--------±-----------±-----------+
| ename | job | total_sal |
±--------±-----------±-----------+
| SMITH | CLERK | 800.0 |
| ALLEN | SALESMAN | 1900.0 |
| WARD | SALESMAN | 1750.0 |
| JONES | MANAGER | 2975.0 |
| MARTIN | SALESMAN | 2650.0 |
| BLAKE | MANAGER | 2850.0 |
| CLARK | MANAGER | 2450.0 |
| SCOTT | ANALYST | 3000.0 |
| KING | PRESIDENT | 5000.0 |
| TURNER | SALESMAN | 1500.0 |
| ADAMS | CLERK | 1100.0 |
| JAMES | CLERK | 950.0 |
| FORD | ANALYST | 3000.0 |
| MILLER | CLERK | 1300.0 |
±--------±-----------±-----------+

4 求每个部门的平均工资
4.1
每个人的工资和部门名称

select
tb_emp.ename ,
tb_emp.sal ,
tb_emp.deptno,
tb_dept.dname
from
tb_emp
join
tb_dept
on tb_emp.deptno = tb_dept.deptno ;
±--------------±------------±----------