软件-Hive

这是一篇关于Hive的思维导图，是基于hadoop的一个数据仓库工具，可以将结构化数据映射成一张表，并提供类SQL的查询功能。

Hive

是什么

概念

是基于hadoop的一个数据仓库工具，可以将结构化数据映射成一张表，并提供类SQL的查询功能

本质

将 Hive SQL转化成MapReduce程序 Spark程序

架构

数据结构

简单类型

int string double .....

复杂类型

array<string>

map<int,string>

struct(结构体) 对象

安装与配置

1. mysql启动与配置

1.1. service mysqld start

1.2. mysql -u用户名 -p密码

1.3. mysql > grant all privileges on *.* to 'root'@'%' identified by '密码' with grant option;

1.4. mysql > flush privileges;

1.5. 在windows上用Navicat连接一下看是否能成功！

2. hive的安装包解压

2.1. rz

2.2. tar -zxf apache-hive-2.3.5.tar.gz

3. 修改配置文件

3.1. hive-env.sh.template重命名

3.1.1. mv hive-env.sh.template hive-env.sh

3.2. 配置hive-env.sh

3.2.1. vi hive-env.sh

export HADOOP_HOME=/usr/apps/hadoop-2.8.5/ # Hive Configuration Directory can be controlled by: export HIVE_CONF_DIR=/usr/apps/hive-2.3.5/conf/

3.3. 配置hive-site.xml

3.3.1. vi hive-site.xml

javax.jdo.option.ConnectionURL jdbc:mysql://主机名:3306/hive?createDatabaseIfNotExist=true&useSSL=false javax.jdo.option.ConnectionDriverName com.mysql.jdbc.Driver javax.jdo.option.ConnectionUserName 用户名 javax.jdo.option.ConnectionPassword 密码 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 主机名,主机名

3.4. 配置hadoop-2.0.4/ect/hadoop/core-site.xml

3.4.1. vi hadoop-2.0.4/ect/hadoop/core-site.xml

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

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

3.5.1. scp -p ..jar /hive-2.0.4/lib

4. 重启hdp

4.1. stop-all.sh

4.2. start-all.sh

5. 初始化hive的元数据库

5.1. ${HIVE_HOME}/bin/schematool -initSchema -dbType mysql

6. 连接hive

1.打开mysql 2.保证hadoop集群启动

6.1. 单机连接

1. bin/hive

6.2. 网络连接

1. hiveserver2

2. clone session

3. beeline

4. ！connect jdbc:hive2://主机名:10000

表的分类

内部表

删除一个内部表时，Hive也会删除这个表中数据 创建表 create table if not exists student2( id int, name string ) row format delimited fields terminated by '\t' location '/user/hive/warehouse/student2';

外部表

删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。 建表 create external table if not exists default.dept( deptno int, dname string, loc int ) row format delimited fields terminated by '\t';

分区表

Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。 建表 create table dept_partition( deptno int, dname string, loc string ) partitioned by (month string) row format delimited fields terminated by '\t';

静态分区表

load data local inpath '路径‘ into table 表名_partition partition(month='201709');

一级分区表

一级分区: 比如数据是按照月份分区的,数据依然很多 ,查询的时候是按月查询 ,,有的时候需要按天查 ,可以将数据在月的下面按天分区 创建表 create table if not exists t_orders1( oid string , cost double , createtime string ) partitioned by(mt string) row format delimited fields terminated by ',' ;

二级分区表

二级分区 *** 创建表 create table if not exists t_orders2( oid string , cost double , createtime string ) partitioned by(mt string , dy string) row format delimited fields terminated by ',' ; *** 导入数据 load data local inpath "/hive/20190901.log" into table t_orders2 partition(mt="201909",dy='01') ; load data local inpath "/hive/20190902.log" into table t_orders2 partition(mt="201909",dy='02') ; load data local inpath "/hive/20191001.log" into table t_orders2 partition(mt="201910",dy='01') ; load data local inpath "/hive/20191002.log" into table t_orders2 partition(mt="201910",dy='02') ;

动态分区表

1.创建一个普通表 2.写入数据 3.创建一个分区表 4.设置开启动态分区 set hive.exec.dynamic.partition=true //使用动态分区 set hive.exec.dynamic.partition.mode=nonstrick;//无限制模式，如果模式是strict，则必须有一个静态分区且放在最前面 set hive.exec.max.dynamic.partitions.pernode=10000;//每个节点生成动态分区的最大个数 set hive.exec.max.dynamic.partitions=100000;//生成动态分区的最大个数 set hive.exec.max.created.files=150000;//一个任务最多可以创建的文件数目 set dfs.datanode.max.xcievers=8192;//限定一次最多打开的文件数 set hive.merge.mapfiles=true; //map端的结果进行合并 set mapred.reduce.tasks =20000; //设置reduce task个数 5.从普通表导入数据至分区表 insert into table 表名 partition(birthday（分区名）) select id , cost ,birthday from demo ； 可按日期动态分区

分区表DML

分桶表

将表的数据分文件管理 根据条件字段的hashcode%桶数将数据分别存储在不同的文件中 , 当有join关联的时候,join的条件检索范围只在分桶内 1 创建一个普通的表 2 导入原始数据 3 创建一个分桶i表(分桶字段和桶个数) create table student( sid string , name string ) clustered by(sid) -- 根据sid字段的hashcode取模 3 into 3 buckets row format delimited fields terminated by '\t'; 4 开启分桶开关 hive (default)> set hive.enforce.bucketing=true; -- 开启分桶 hive (default)> set mapreduce.job.reduces=-1; -- 默认reduce个数 5 使用insert方式导入数据到桶表中 insert into table student select sid, name from stu; clustered by(字段) into 3 buckets ;

DDL（ 数据定义语言）

数据库相关

创建数据库

create database 数据库名;

create databases if not exists 数据库名; (防止创建的数据库已经存在) create databases 数据库名 location '路径名';（指定数据库在HDFS上存放的位置）

查询数据库

显示数据库

show databases;

查看数据库

desc database 数据库名；

切换数据库

use 数据库名；

修改数据库

更改设置键-值对信息，其他不可更改

删除数据库

drop database 数据库名

表相关

修改表

重命名表

alter table 表名 rename to 新表名

添加列

alter table 表名 add columns(列名 列数据类型)

更新列

alter table 表名 change column 列名 desc 列数据类型; ？？？？？？？

替换列

alter table 表名 replace columns(新列名 列数据类型, 新列名 列数据类型)

删除表

drop table 表名

清除表中数据

truncate table 表名

创建表

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] row format delimited fields terminated by “分隔符” [stored as file_format] [location hdfs_path]

location 指定表的位置

partition 指定分区字段

COMMENT 为表和列添加注释

PARTITIONED BY 创建分区表

CLUSTERED BY 创建分桶表

区相关

增加分区

alter table 表名 add partition ( )

删除分区

alter table 表名 drop partition ( )

查看分区

show partitions 表名

查看分区结构

desc formatted 表名

创建分区

partition by （列名 数据结构） 创建表时创建

DML（ 数据操控语言）

数据导入

load data

1 导入本地数据 load data local inpath "" (overwrite) into table tb_name partition(多级分区) 2 导入HDFS的数据 load data inpath "" (overwrite) into table tb_name partition(多级分区)

建表的时候指定location(HDFS的路径)

insert into的方式

1 insert into table tbname values() 2 insert into table tbname select * from ...

As Select (建表加导入查询到的数据)

create table tb_name as select * from tb_name2 ;

import导入export出的数据

导入和导出是特定的数据格式 导入的数据必须是export导出的数据

数据导出

insert overwrite local directory '路径'

insert overwrite local directory '/hive/data/orders3' select * from orders3 ; 将从orders3中查询的结果导入到本地的/hive/data/orders3目录下

HIVE的客户端兼容hdfs的命令 使用dfs -get 下载hive表中的数据

dfs -get '数据库路径' 'hdfs路径'；

使用shell的>到文件

hive -e "select * from orders3 ;" > /hive/data/orders3/my.txt hive -f 文件

export导出数据 结合import导入

查询

查询函数

concat（）

select concat("hello",":","tom",":","JIM")

字符串的拼接 参数是可变的

concat_ws（）

select concat_ws(":","hello","tom","JIM")

参数1 拼接符号 参数2 可变参数 拼接的字符串

参数1 拼接的字符串 参数2 可以是一个数组

collect_set(列)

select * , collect_set(列名) from 表名 ;

将分组后的数据的某一个字段，遍历存储在一个数组中

行转列

explode（）

explode(列明或行名) 炸裂 一行炸裂成多行

将hive中的aray数组或者map集合结构拆分成多行

later view

侧视图的意义是配合explode（或者其他的UDTF），一个语句生成把单行数据拆解成多行后的数据结果集。 lateral view explode(列名) A（新列名）as B（新视图名）

一进多出，对拆分的数据进行聚合

列转行

case when

case when sex = '1' then '男' when sex = '2' then '女' else '未知' end

nvl（）

nvl(string,replace string); 如果string为null，则返回replace string；否则返回string

判定NULL

greatest（）

寻找最大值

over（）

通常是over(partition by 字段名 order by 字段名)

指定分析函数工作的窗口大小

lead（）

LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值 第一个参数为列名， 第二个参数为往下第n行（可选，默认为1）， 第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值

lag（）

在不使用自连接的情况下同时访问到一个表的多行数据 LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值 第一个参数为列名， 第二个参数为往上第n行（可选，默认为1）， 第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值

ntile（n）

NTILE(n)，用于将分组数据按照顺序切分成n片，返回当前切片值

将数据均匀的分到n个桶中，可用于求前a%的数据

row_number（）

ROW_NUMBER( )从1开始，按照顺序，生成分组内记录的序列 –比如，按照pv降序排列，生成分组内每天的pv名次 ROW_NUMBER() 的应用场景非常多，再比如，获取分组内排序第一的记录;获取一个session中的第一条refer等

会根据顺序计算

rank( )

字段 rank a 1 b 2 b 2 c 3 d 4

生成数据项在分组中的排名，排名相等会在名次中留下空位

dense_rank( )

字段 dense_rank a 1 b 2 b 3 c 4 d 5

生成数据项在分组中的排名，排名相等会在名次中不会留下空位

三者不同

ROW_NUMBER、RANK和DENSE_RANK的区别 row_number： 按顺序编号，不留空位 rank： 按顺序编号，相同的值编相同号，留空位 dense_rank： 按顺序编号，相同的值编相同的号，不留空位

substring(字段 , 0,len)

截取string的0到len的数据

with cube

a a b a b c a b c d

生成的结果集显示了所选列中值的所有组合的聚合

map（）

map('act_1day',act_1day,"act_2day",act_2day,"act_3day",act_3day,"act_4day",act_4day,"act_5day",act_5day,"act_6day",act_6day,"act_7day",act_7day,"act_7plus",act_7plus) as info

日期相关

diffdate( )

参数一是字段名 参数二是被减数 参数三是减数

返回两个日期之间的时间

date_sub

第一个参数是日期 第二个参数是需要减去的天数

从日期减去指定的时间间隔

date_add

第一个参数是日期 第二个参数是需要增加的天数

向日期添加指定的时间间隔。

Grouping Sets

INSERT INTO TABLE ADS_B2C_GMV_DIM SELECT '2019-11-27' as dt, b.brand_name, b.third_cat_name, SUM(a.goods_price*a.goods_amount) AS gmv FROM dwd_b2c_orders_goods a join dwd_b2c_goods b ON a.goods_id=b.sku_id GROUP BY b.brand_name,b.third_cat_name GROUPING SETS((b.brand_name),(b.third_cat_name),()) ; 

可以在Group By语句后面添加Grouping Sets语句，以实现对同一个数据集上同时进行多组的group by操作

可以理解为多个group by 语句进行union操作

Join连接

内连接

内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。 select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;

外连接

左连接

左外连接：JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。 select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;

右连接

右外连接：JOIN操作符右边表中符合WHERE子句的所有记录将会被返回 select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;

全连接

满外连接：将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。 select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;

UNION

请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。 默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

如果允许重复的值，请使用 UNION ALL。

排序

order by 字段1,字段2... 数据的全局排序

sort by 数据分区后排序

set mapreduce.job.reduces=3 会有3个reduce任务处理每个查询语句 1 按照第一个字段分区 3 2 再在分区后的数据中按照部门编号排序 select * from emp sort by deptno ;

distribute by 字段 按照字段数据分区 分区后的数据的排序是sort by

set mapreduce.job.reduces=3 会有3个reduce任务处理每个查询语句 1 按照部门分区 2 在分区后的数据中按工资降序排列 select * from emp distribute by deptno sort by sal desc ;

分区排序

cluster by 当分区字段和区内排序的字段相同的时候使用cluster by 注意不能降序排列

分区字段和排序字段一致 select * from emp distribute by ename sort by ename ; 等同于 select * from emp cluster by ename