hive

hive

1.hive的基本概念

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

本质：将sql语句转化成MapReduce程序

  1. hive处理的数据存储在hdfs

  2. hive分析数据底层的实现是MapReduce

  3. 执行程序运行在yarn上

2.hive的架构

1）用户接口： Client
CLI（ hive shell）、 JDBC/ODBC(java 访问 hive)

2）元数据： Metastore
元数据包括：表名、表所属的数据库（默认是 default）、表的拥有者、列/分区字段、表的类型（是否是外部表）、表的数据所在目录等；

３）驱动器： Driver
（ 1）解析器（ SQL Parser）：将 SQL 字符串转换成抽象语法树 AST，这一步一般都用第三方工具库完成，比如 antlr；对 AST 进行语法分析，比如表是否存在、字段是否存
在、 SQL 语义是否有误。
（ 2）编译器（ Physical Plan）：将 AST 编译生成逻辑执行计划

​ （ 3）优化器（ Query Optimizer）：对逻辑执行计划进行优化。

​ （ 4）执行器（ Execution）：把逻辑执行计划转换成可以运行的物理计划。对于 Hive 来说，就是 MR/Spark。

3.hive安装

1.derby形式

首先将压缩包解压，并且配置环境变量。

建立一个文件加夹用来存储元数据，然后初始化元数据库

1
2
3
4
mkdir /home/master/derby_data
cd /home/master/derby_data
//执行初始化操作
schematool -dbType derby -initSchema

2.mysql形式

  1. 首先将压缩包解压，并且配置环境变量。

进入到conf/下，修改配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
vi hive-site.xml

<configuration>
<property>
        <name>javax.jdo.option.ConnectionURL</name>
        <!--指定mysql-->
        <value>jdbc:mysql://192.168.43.22: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>hive</value>
</property>
<property>
        <name>javax.jdo.option.ConnectionPassword</name> <value>hive</value>
</property>
<property>
    <name>hive.metastore.warehouse.dir</name>
    <value>/user/hive/warehouse</value>
    <description>location of default database for the warehouse</description>
</property>
</configuration>
  1. 将mysql驱动包复制到hive/lib下
  2. 在mysql中创建用户hive,密码为hive(与配置文件一致)，授予该用户权限，并且打开mysql的远程访问权限
1
2
3
4
5
create user 'hive'@'192.168.43.22' identified by 'hive';
grant all privileges on hive.* to 'hive'@'%' identified by 'hive';

将/etc/mysql/my.conf 下面一句注释
# bindaddress localhost
  1. 执行初始化命令
1
schematool -dbType mysql -initSchema

　4.检查mysql中是否存在hive元数据

４.hive的数据类型

基本数据类型：

集合数据类型：

Array(集合)：

1
2
3
4
5
6
7
8
9
10
11
create table tab_array (a array<int>,b array<string>)
row format delimited
fields terminated by '\t'
collection items terminated by ',';
vi data_array
# tab_array的内容
1,2,3	jack,Tom,lucy
//将数据加载到tab_array中
load data local inpath '/home/master/data_array' into table tab_array;
//查询
select a[2],b[1] from tab_array;

Map(键值对):

1
2
3
4
5
6
7
8
9
10
create table tab_map (name string,info map<string,string>)
row format delimited
fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':';

vi data_map
zhangsan    name:zhangsan,age:18,gender:male
load data local inpath '/home/hdfs/data_map' into table tab_map;
select  info['name'] from tab_map;

struct(类似java的对象)

1
2
3
4
5
6
7
8
9
create table tab_struct(name string,info struct<age:int,tel:string,salary:double>)
row format delimited
fields terminated by '\t'
collection items terminated by ',';

vi data_struct
zhangsan	18,189,22.3
load data local inpath '/home/hdfs/data_struct' into table tab_struct;
select info.age,info.tel from tab_struct;

综合案例:

1
2
3
4
5
6
7
8
9
10
11
12
create table test(
name string,
friends array<string>,
children map<string, int>,
address struct<street:string, city:string>
)
row format delimited fields terminated by ','  --指定列分隔符为逗号
collection items terminated by '_'		--指定集合之间的分隔符为 _
map keys terminated by ':'				--指定map中key与value的分割符
# 将下面的数据导入到表中
jack,friend1_friend2,son1:18_son2:20,nanc_qingshanhu
lucy,friend3_friend4,son1:18_son2:20,kunsan_bacheng

5.DDL数据库定义语言

数据库的操作

创建数据库：

1
2
3
4
create database hsj_test;
create database if not exists hsj_test;
# 创建数据库并指定在HDFS上的位置
create database hsj_test3 location '/hsj_test3'

修改数据库

1
2
3
alter database hsj_test3 set dbproperties('createtime'='20190710');
#　查看修改
desc database extended hsj_test3;

查询数据库

1
2
show databases;
show databases like 'hsj_*';

显示数据库信息

1
2
3
desc database hsj_test3;
# 显示详细信息
desc database extended hsj_test3

删除数据库：

数据库为空的时候才能删除否则报错,Database hsj_test3 is not empty. One or more tables exist

1
drop database hsj_test3; 数据库为空的时候才能删除

表操作

创建表

  • 内部表:将数据存储在hive.metastore.warehouse.dir(例如， /user/hive/warehouse)的子目录下，删除表时，会将hdfs上的数据一同删除
1
2
3
4
5
6
7
8
9
10
11
create table inner_table(id int,name string)
row format delimited fields terminated by ',' 
stored as textfile;　　　　　
location '/hdfspath' 指定加载数据的路径
＃字段说明
row format delimited fields terminated by ',' // 指定列之间以,分割
stored as textfile;　指定文件存储形式
常用文件类型:
SEQUENCEFILE（二进制序列文件）
TEXTFILE（文本）
RCFILE（列式存储格式文件）
  • 外部表:删除表并不会删除hdfs上的数据
1
2
create external table if not exist ext_table(id int,name string)
row format delimited fields terminated by ',';
  • 分区表:根据业务编码、日期、其他类型等维度创建分区表，在一个表对应的目录下，一个分区对应一个目录.
1
2
3
4
5
6
7
8
9
10
# 创建一个以月份为分区的表
create table partition_table(id int,name string)
partitioned by (month string)
row format delimited fields terminated by ',';

load data local inpath '/home/hdfs/order_data' into table partition_table
partition(month='8');
load data local inpath '/home/hdfs/order_data' into table partition_table
partition(month='７');
去hdfs中查看，发现在该表对应的目录下，每个月份对应了一个目录

分区表的查询

1
2
3
4
5
6
＃　单分区查询
select * from t_order where month='7';
＃　多分区联合查询
select * from t_order where month='7'
union 
select * from t_order where month='8';

增加分区

1
2
alter table t_order add partition(month='10');
alter table t_order add partition(month='12') partition(month='11');

删除分区

1
2
alter table t_order drop partition(month='10');
alter table t_order drop partition(month='11'),partition(month='12');

查看分区表有多少分区

1
show partitions t_order;

查看分区表结构

1
desc formatted t_order;

创建二级分区表

1
2
3
4
5
6
7
create table t_order_2(id int,name string,cost double)
partitioned by(month string,day string)
row format delimited fields terminated by ',';

load data local inpath '/home/master/phone_data' into table t_order_2 partition(month='1',day='20');
load data local inpath '/home/master/phone_data' into table t_order_2 partition(month='2',day='20');
load data local inpath '/home/master/phone_data' into table t_order_2 partition(month='1',day='15');

将数据直接上传到分区目录上，让分区表和数据产生关联的两种方式

方式一：先上传后修复

1
2
3
4
5
6
7
上传数据
hdfs dfs -mkdir -p /user/hive/warehouse/hsj_hve.db/t_order_2/month=1/day=2
hdfs dfs -put phone_data /user/hive/warehouse/hsj_hve.db/t_order_2/month=1/day=2/

select * from t_order_2 where month='1' and day='2'; 发现查询不到
修复数据再查询
 msck repair table t_order_2;

方式二:上传数据后添加分区

1
2
3
hdfs dfs -mkdir -p /user/hive/warehouse/hsj_hve.db/t_order_2/month=2/day=2
hdfs dfs -put phone_data /user/hive/warehouse/hsj_hve.db/t_order_2/month=2/day=2/
alter table add partition(month='2',day='2');
  • 桶表：将大表进行哈希散列抽样存储，方便做数据和代码验证。在表对应的目录下，将源文件拆分成N个小文件。桶表中的数据，只能从其他表中用子查询进行插入
1
2
3
4
5
6
7
8
9
10
11
12
create table t_phone_bucket(id int,name string ,price string)
clustered by(id) into 3 buckets
row format delimited 
fields terminated by ',';

set hive.enforce.bucketing=true;

insert into table t_phone_bucket select * from t_phone;
# 桶抽样查询
select * from t_phone_bucket tablesample(bucket 3 out of 3 on id);

语法： TABLESAMPLE(BUCKET x OUT OF y)

数据库抽样查询

1
select * from stu tablesample(0.1 percent) ;S

修改表

重命名表:语法　ALTER TABLE table_name RENAME TO new_table_name

1
alter table student rename to stu;

修改列

1
2
3
4
5
6
# 增加列
alter table stu add columns(age int);
# 替换列　膝盖表中所有字段　注意类型要匹配
alter table stu replace columns(name string,gender string,age int);
# 修改列
alter table stu change column age age string

##　六.DML数据操作

数据加载：

1
load data [local] inpath '/opt/module/datas/student.txt' [overwrite] into table student[partition (partcol1=val1,…)];

数据插入

1
2
3
4
5
6
7
插入选择的数据
insert overwrite table t_phone select * from t_phone_back;
基本插入数据
insert into table  p_order partition(month='10') values(1,'wangwu',900.1);
基本模式插入数据
insert overwrite table p_order partition(month='11')
select id, name from p_order where month='7';

查询语句中创建并加载数据

1
create table if not exists stu as select id ,name from student;

将数据导出到本地

1
2
3
insert overwrite local directory '/home/master/hive_export'
row format delimited fields terminated by '\t'
select * from t_order;

将数据导出到hdfs

1
2
3
insert overwrite  directory '/home/master/hive_export'
row format delimited fields terminated by '\t'
select * from t_order;

七查询

group by语句：GROUP BY 语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，

然后对每个组执行聚合操作

计算 emp 表每个部门的平均工资

1
select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;

计算 emp 每个部门中每个岗位的最高薪水

1
2
select t.deptno, t.job, max(t.sal) max_sal from emp t group by t.deptno,
t.job;

having:

（ 1） where 针对表中的列发挥作用，查询数据； having 针对查询结果中的列发挥作用，
筛选数据。
（ 2） where 后面不能写聚合函数，而 having 后面可以使用聚合函数。
（ 3） having 只用于 group by 分组统计语句

求每个部门的平均薪水大于 2000 的部门

1
2
 select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal >
2000

join

等值连接

根据员工表和部门表中的部门编号相等，查询员工编号、 员工名称和部门编号；

1
2
select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on
e.deptno = d.deptno;
内连接:

只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来

1
2
select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno =
d.deptno;
左外连接:

JOIN 操作符左边表中符合 WHERE 子句的所有记录将会被返回

1
2
 select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno =
d.deptno;
右外连接

JOIN 操作符右边表中符合 WHERE 子句的所有记录将会被返回

1
2
select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno =
d.deptno;
满外连接

将会返回所有表中符合 WHERE 语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用 NULL 值替代

1
2
select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno =
d.deptno;

排序

全局排序 order by

ASC ：升序（默认) DESC:降序

查询员工信息按工资升序排列

1
select * from emp order by sal;

多个列排序

按照部门和工资升序排序

1
select ename, deptno, sal from emp order by deptno, sal ;
内排序 sort by
1
2
3
4
5
6
7
设置 reduce 个数
set mapreduce.job.reduces=3;
根据部门编号降序查看员工信息
select * from emp sort by empno desc;
将查询结果导入到文件中（按照部门编号降序排序
insert overwrite local directory '/opt/module/datas/sortby-result' select *
from emp sort by deptno desc;
分区排序 Distribute By

类似 MR 中 partition，进行分区，结合 sort by 使用

1
2
3
set mapreduce.job.reduces=3;
insert overwrite local directory '/opt/module/datas/distribute-result' select *
from emp distribute by deptno sort by empno desc;

Cluster By:当 distribute by 和 sorts by 字段相同时，可以使用 cluster by 方式。

cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。但是排序只能是倒序排序， 不能指定排序规则为 ASC 或者 DESC。

1
2
3
以下两种写法等价
select * emp cluster by deptno;
select * from emp distribute by deptno sort by deptno;
行转列
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
＃　数据
大海	射手座	A
宋宋	白羊座	B
猪八戒	白羊座	A
凤姐	射手座	A
孙悟空	白羊座	A
# 表
create table persion_info(name string,constellation string,blood_type string)
row format delimited fields terminated by '\t';
# 转成格式
射手座,A	大海|凤姐
白羊座,A	猪八戒|孙悟空
白羊座,B	宋宋

语句
select t.base,concat_ws('|',collect_set(t.name)) 
from 
(
select concat(constellation,',',blood_type) base,name from persion_info
)t
 group by t.base;

函数说明：

concat : 连接函数，用于将多个列或者字符串连接起来　concat(col1,col2,)

concat_ws:一种特殊的concat,有多个参数，第一个参数指定为连接符concat(splitor,col1,col2)，只能用来连接字符串类型或者字符串数组的列

collect_set:只接受基本类型数据，主要是将某字段的值去重汇总，产生array类型字段

列转行
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# 数据
《疑犯追踪》	悬疑,动作,科幻,剧情
《 Lie to me》	悬疑,警匪,动作,心理,剧情
《战狼 2》	战争,动作,灾难
#　表
create table movie_info(
	movie string,
    category array<string>
    ) row format delimited fields terminated by "\t"
    collection items terminated by ",";
转换格式：
《疑犯追踪》	悬疑
《疑犯追踪》	动作
《疑犯追踪》	科幻
《疑犯追踪》	剧情
《 Lie to me》	悬疑
《 Lie to me》	警匪
《 Lie to me》	动作
《 Lie to me》	心理
《 Lie to me》	剧情
《战狼 2》	战争
《战狼 2》	动作
《战狼 2》	灾难
＃　查询语句
select movie ,category_name from 
movie_info lateral view explode(category) table_tmp as category_name;

函数说明：

LATERAL VIEW:

用法:LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释:用于和 split, explode 等 UDTF 一起使用，它能够将一列数据拆成多行数据，在此
基础上可以对拆分后的数据进行聚合。

八.窗口函数

OVER():指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化
CURRENT ROW:当前行
n PRECEDING :往前 n 行数据
n FOLLOWING :往后 n 行数据
UNBOUNDED:起点， UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED
FOLLOWING 表示到后面的终点

LAG(col,n):往前第 n 行数据
LEAD(col,n):往后第 n 行数据
NTILE(n):把有序分区中的行分发到指定数据的组中，各个组有编号，编号从 1 开始，对于
每一行， NTILE 返回此行所属的组的编号。 注意： n 必须为 int 类型。

数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94
# 表
 create table business(
    name string,
    orderdate string,
    cost int)
    row format delimited fields terminated by ',';

1.查询在 2017 年 4 月份购买过的顾客及总人数

1
select name,count(*) over() from business where substring(orderdate,0,7)='2017-04' group by name;

2.查询顾客的购买明细及月购买总额

1
select *,sum(cost) over(distribute by month(orderdate);

3.查询顾客的购买明细,将 cost 按照日期进行累加

1
2
3
select *,sum(cost) over(partition by name order by orderdate) from business;

# 分区限定了窗口的界限　排序限定了窗口的大小

4.查看顾客上次的购买时间

1
select * ,lag(orderdate,1) over(distribute by name order by orderdate) from business;

5.查询前 20%时间的订单信息

1
2
3
select * from
(select name,orderdate,cost,ntile(5) over(order by orderdate) id from business)t
where t.id = 1;
Rank函数

需要配合窗口函数over()使用

Rank() :　排序，如果有相同会重复，总数不会减少

​ 假设　小明考试考了100,小刚考试考了100,小红考试考了99,那小明小刚并列第一（重复），小红第三（总数不会减少）

dense_Rank():排序，如果有相同会重复，总数会减少.

​ 还是上面那个例子，明小刚并列第一（重复），小红第二（总数会减少）

row_number():按顺序排序

九.函数

系统内置函数

1
2
3
4
5
6
查看系统内置函数
show functions;
显示内置函数用法
desc function upper;
详细显示内置函数用法
desc function extended upped

自定义函数

UDF（user-defined function）：一进一出

UDAF（ User-Defined Aggregation Function):聚集函数　多进一出

UDTF（ User-Defined Table-Generating Functions）：一进多出

实现步骤

  1. 继承org.apache.hadoop.hive.ql.UDF,实现evaluate方法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
public  class MyUdf extends  UDF {

   public String  evaluate(String phone){
       switch (phone.substring(0,3)){
           case "187":
               return "上海";
           case "159":
               return "北京";
           case "138":
               return "南昌";
           default:
               return "未知";
       }
   }
}
  1. 打成jar包，上传到hive所在的机器
  2. 在hive中创建一个函数，和jar中的自定义类建立
1
2
add jar /home/master/xxxx.jar
create [temporary] function getArea(col) as com.hsj.udf.MyUDF
  1. 使用
1
select id,name tel,getArea(tel) from t_student;

十.设置本地模式

1
2
3
4
5
6
7
set hive.exec.mode.local.auto=true; 
//设置 local mr 的最大输入数据量，当输入数据量小于这个值时采用 local mr 的方式，
默认为 134217728，即 128M
set hive.exec.mode.local.auto.inputbytes.max=50000000;
//设置 local mr 的最大输入文件个数，当输入文件个数小于这个值时采用 local mr 的方式，
默认为 4
set hive.exec.mode.local.auto.input.files.max=10;

十一jdbc连接hive

  1. 修改配置文件 hdfs-site.xml core-site.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
#hdfs-site.xml 
<property>  
    <name>dfs.webhdfs.enabled</name>  
    <value>true</value>  
</property>
#core-site.xml
<property>
    <name>hadoop.proxyuser.master.hosts</name><!--允许所有主机以master登录-->
    <value>*</value>
</property>
<property>
    <name>hadoop.proxyuser.master.groups</name><!--允许任意组master登录-->
    <value>*</value>
</property>

2.jdbc api(注意先将驱动包导入)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
public class HiveJdbcTest {

    private static String driver = "org.apache.hive.jdbc.HiveDriver";
    private static  String url = "jdbc:hive2://192.168.43.58:10000/hsj_test";
    private static String user ="master";
    private static String passwd="master";
    public static void main(String[] args) throws Exception {

        //注册驱动　匹配到正确的驱动
        Class.forName(driver);
        //获取连接
        Connection connection = DriverManager.getConnection(url, user, passwd);
        System.out.println(connection);
        Statement statement =
                connection.createStatement();
        String sql = "select * from business";
        ResultSet resultSet =
                statement.executeQuery(sql);
        while (resultSet.next()){
            String per = resultSet.getString("name");
            String info = resultSet.getString("orderdate");
            int extrainfo = resultSet.getInt("cost");
            System.out.println(per+info+extrainfo);
        }
    }
}

十二.hive结合hbase

  1. hbase 中创建表
1
2
3
4
5
 create 'person',{NAME => 'f1',VERSIONS => 1},{NAME => 'f2',VERSIONS => 1},{NAME => 'f3',VERSIONS => 1}
put 'person','1001','f1:name','jack'
put 'person','1001','f2:age','18'
put 'person','1002','f1:name','jack'
put 'person','1003','f3:position','ceo
  1. 打开hive
1
2
3
4
5
6
7
8
9
10
11
SET hbase.zookeeper.quorum=master:2181;
SET zookeeper.znode.parent=/hbase;

CREATE EXTERNAL TABLE person (
rowkey string,
f1 map<STRING,STRING>,
f2 map<STRING,STRING>,
f3 map<STRING,STRING>
) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,f1:,f2:,f3:")
TBLPROPERTIES ("hbase.table.name" = "person");

本文标题:hive

文章作者:HanderH

发布时间:2019-07-10, 16:21:25

最后更新:2019-08-20, 18:33:25

原始链接:http://www.handerh.top/2019/07/10/hive/

许可协议: "署名-非商用-相同方式共享 4.0" 转载请保留原文链接及作者。

文章目录
  1. 1. 1.hive的基本概念
  2. 2. 2.hive的架构
  3. 3. 3.hive安装
    1. 3.1. 1.derby形式
    2. 3.2. 2.mysql形式
  4. 4. ４.hive的数据类型
  5. 5. 5.DDL数据库定义语言
    1. 5.0.1. 数据库的操作
  6. 5.1. 表操作
    1. 5.1.1. 创建表
    2. 5.1.2. 修改表
  • 6. 七查询
    1. 6.0.1. group by语句：GROUP BY 语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，
    2. 6.0.2. having:
    3. 6.0.3. join
      1. 6.0.3.1. 等值连接
      2. 6.0.3.2. 内连接:
      3. 6.0.3.3. 左外连接:
      4. 6.0.3.4. 右外连接
      5. 6.0.3.5. 满外连接
    4. 6.0.4. 排序
      1. 6.0.4.1. 全局排序 order by
      2. 6.0.4.2. 内排序 sort by
      3. 6.0.4.3. 分区排序 Distribute By
      4. 6.0.4.4. 行转列
      5. 6.0.4.5. 列转行
  • 7. 八.窗口函数
    1. 7.0.0.0.1. Rank函数
  • 8. 九.函数
  • 9. 十.设置本地模式
  • 10. 十一jdbc连接hive
  • 11. 十二.hive结合hbase
    • |
    载入天数...载入时分秒...