Hive数据仓库_大数据系统

Hive：计算框架，本质是将HQL转化为MapReduce程序，是一个基于Hadoop的数据仓库工具，可以将结构化的数据映射为一张表，提供类sql查询功能。

特点：

hive处理的数据存储在HDFS中
hive分析数据底层的默认实现是MapReduce
执行的任务运行在yarn上
操作接口采用了类sql语法
将hql语句解析为MapReduce运算
执行延迟高，吞吐量大，适合离线的大数据运算，不适合小数据的计算处理
支持用户自定义函数，根据自己的需求实现业务函数

概述：
数据仓库的内容是读多写少，hive中不建议对数据进行频繁的改写，所有的数据都是在加载的时候确定好的。数据库中的数据通常是需要进行修改的，这是和普通的关系型数据库的差别，二者除了语法相似之外，其他的毫无关联。

索引：
hive在加载数据的过程中不会对数据进行任何处理，甚至不会对数据进行扫描，所以不会对数据中的某些key建立索引，hive要访问数据中满足某些特定条件的值时进行全表扫描，访问延迟高，但是基于MapReduce的，所以支持对数据的并行访问，因此查询效率在暴力检索中并不是效率很低。但是在数据库中往往会对某个key建立索引，由于hive的访问延迟高，决定了hive不适合做在线数据的查询。

执行：
hive的执行引擎是MapReduce。

执行延迟：
基于MapReduce，延迟高。

hive和mysql的关系？？
mysql用于存储实际数据的元数据信息，重点需要关注的表：

在shell交互中执行hive语句

进入hive本体的根目录，执行bin/hive -help查看所有的命令行支持的参数：

实例：

参数-e的实例：
bin/hive -e "show databases"执行查看所有数据库，执行完毕之后自动退出至shell交互端。
bin/hive -e "select * from aa" 执行查看表中的信息。
参数-f的实例：参数-f支持直接跟一个文件名，文件中写入hql语句，然后就会按行自动执行每一条hql语句，将执行之后的结果在终端输出。
bin/hive -f "filePath + fileName"执行每一行的执行结果并在终端输出。
linux相关的：将执行的结果追加到文件中：bin/hive -f "filePath + fileName" > ~/test/sql.txt，并且查询结果不会输出到终端了

hive基本数据类型

上述的使用以int和string为主。hive的string相当于数据库中的varchar，是一个可变长的字符串，不过不能声明其中最多能存储多少个字符，理论上能储存2GB的字符数。

集合数据类型

集合类型在建表语句中的使用：

create table test(
name string,
friends array,
children map,
address struct
)
row format delimited    // 定义行分割
fields terminated by ','   // 定义多个列之间的分割符号
collection items terminated by '_'   // 定义在集合中的多个元素之间的分割符号为'_'
map keys terminated by ':'  // 定义在map集合中出现的键值对的分割符号为':'
lines terminated by 'n'(可省略，默认是n);

访问方式：

select friends[1],children['xiao song'],address.city 
from test
where name="songsong";

类型转化

hive的原子数据类型支持隐式的类型转化，类似于java的类型转化，例如：

TINYINT->INT

但是反向转化会报错，仅支持从小的转化成范围更广的数据类型；
强制类型转化失败返回NULL；

DDL数据定义创建数据库：

create database hive;

数据库被创建之后存储的位置：/user/hive/warehouse；
default默认数据库创建的表的文件夹为/user/hive/warehouse，既在默认数据库中创建表之后将会被存储在warehouse文件夹中；

在此数据库之下创建的表格将会保存在hive.db文件夹中：

建表之后出现了一个和表名相同名称的文件夹，内部存储向表中插入的数据；

自定义数据库在hdfs中的存储位置：
还可以为创建的数据库自定义存储位置，默认的存储路径可在配置文件中修改，目前默认为：/user/hive/warehouse，指定位置的方式如下：

create database test location '/db_hive.db'

按照上述的方式指定之后，test数据库就会被存储到hdfs的/db_hive.db文件夹下；

显示数据库：

show databases;

过滤显示数据库：

show databases like 'a.*'
// 查询所有的数据库名称以a开头的数据库
// 模糊查询

查看数据库的详情信息：

desc databases_Name

修改数据库

用户可以使用ALTER DATAbase命令为某个数据库的DBPROPERTIES设置键-值对属性值，来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置；

删除数据库：

删除空数据库：

drop database db_hive2;

如果删除的数据库不存在，最好采用 if exists判断数据库是否存在；
删除不为空的数据库会报错，使用强制删除语句cascade实现删除；

drop database db_hive cascade;

创建表：

管理表
不加任何external关键字创建的表，删除数据后，存储在hdfs路径下的表数据被删除，同时在mysql中存储的元数据信息也同样被删除。
默认创建的表都是所谓的管理表，有时也被称为内部表。因为这种表，Hive会（或多或少地）控制着数据的生命周期。Hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如，/user/hive/warehouse)所定义的目录的子目录下。当我们删除一个管理表时，Hive也会删除这个表中数据。管理表不适合和其他工具共享数据。

外部表
因为表是外部表，所以Hive并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉；
在按照原建表语句新建一张表，在不插入任何数据的情况下，查询新建的该表，同样会查询到原表的数据；

注解：可以利用alter关键字对表的属性进行设置，实现内部表与外部表的相互转化，此处不再赘述。

分区表

设置分区表能在数仓中查询数据时避免对全表的扫描，大大提高查询效率。
分区表的设置：

create table dept_partition(
deptno int,
dname string,
loc string
)
partitioned by (month string)   // 指定分区信息，分区信息存储在mysql数据库中，在执行查询之前优先去数据库中取出分区数据，从而才能提高查询效率。
row format delimited
fields terminated by 't';

导入数据：
在导入数据的时候同时创建分区：

load data local inpath 'FilePath' into table tableName partition

在导入数据之前提前创建分区：

alter table dept_partition add partition(month='202010') partition(month='202011')

删除分区：

alter table Name drop partition(month='202010');
// 多张表之间通过逗号分割

上表的分区信息为月份，分区信息在表中的具体表现是具体的字段，是完整的数据一部分，但是实际在hdfs中的存储特性并不是一整张表连续存储的，而是按照分区信息进行分开存储，多级目录的存储，这样能有效的将所有的数据按照自己的业务需求进行分开存储，查询时通过过滤条件大大的提高查询效率。

分区注意事项：创建二级分区

create table dept_partition2(
deptno int, dname string, loc string
)
partitioned by (month string, day string)
row format delimited 
fields terminated by 't';

二级分区既在一级分区的基础上又新建一个目录，既两层目录，使用起来和一级分区相同；
一般分区用来放在where子句中帮助查询时过滤数据而使用；

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

（1）：上传数据后修复：
直接在hdfs的表文件夹下创建分区文件夹，并将文件直接通过hdfs语句上传到该分区文件夹下，之后在hive中无法访问到，因为如果要访问到数据前提是：1.在mysql中存有数据的元数据信息；2.在hdfs中存有真实的数据，在这里只满足含有数据而直接上传并不会自动在mysql中生成元数据信息，故可利用修复语句进行修补mysql中的元数据信息：

msck repair table dept_partition2;

这样在mysql中就可以看到元数据信息了，也同样能访问到数据了。

（2）：上传数据后添加分区：

hive (default)> dfs -mkdir -p
 /user/hive/warehouse/dept_partition2/month=201709/day=11;
hive (default)> dfs -put /opt/module/datas/dept.txt  /user/hive/warehouse/dept_partition2/month=201709/day=11;

alter table dept_partition2 add partition(month='201709', day='11');

（3）：创建文件夹后load数据到分区：

hive (default)> dfs -mkdir -p
 /user/hive/warehouse/dept_partition2/month=201709/day=10;

hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table
 dept_partition2 partition(month='201709',day='10');

hive (default)> select * from dept_partition2 where month='201709' and day='10';

修改表

（1）重命名表：

ALTER TABLE table_name RENAME TO new_table_name

（2）增加、删除、替换列信息：

change：

alter table tableName change column old_col_name new_col_name column_type;

对于修改表的信息：无论是否修改，其old_col_name 、new_col_name、 column_type都要有，即使不修改也要填写相同的；

仅能对单列操作，不支持批量的列操作；

对于修改字段的类型，需要注意转化之间的关系，不能随意的转化，一定遵循转化后的类型范围更大才能完成转换，否则转换失败抛异常，相关的StackOverFlow上的回答：StackOverFlow

alter table table_name add columns (col_name1 data_type1, col_name2 data_type_2...)

支持批量的添加列，实际生产环境中并不会这么做，通常在最初创建表的时候多余的创建出几个列，如果需要添加新列，直接对多余的列进行重命名即可充当新列使用，就不会涉及到修改表的结构了。
执行样例结果如下图：

replace

alter table cc replace columns (id int, name string);

执行完上述代码之后，得到的结果是：整张表的所有列变为了上述代码中指定的两个列：id、name，他会强制替换掉表中的所有列，使表中的列仅是指定的列。
假设原表中有两个列，利用replace之后原表中的数据并不会删除，若replace之后指定一个列，仅会展示一个列而第二个列的数据并没有删除；
添加回来后还能正常显示。

drop

drop table tableName;

删除表，执行之后该表就没了。。。

DML数据操作数据导入

将本地文件系统hdfs中的数据导入hive数仓。

向表中装载数据（Load）：

load data local inpath '路径+文件名' into/overwrite into table 表名 partition 分区信息
1. into是追加式写入
2. overwrite into是覆盖式写入

通过查询语句向表中插入数据（insert）：

- 创建一张表：
 create table student(id int, name string) 
 partitioned by (month string)
 row format delimited
 fields terminated by 't';
   
- 基本插入数据：
 insert into table student partition(month='202110') values(1,'wangwu');

- 基本模式插入（根据单张表的查询结果）
 insert overwrite/into table student partition(month='202010')
 select id, name from student where month = '202010';

创建表的过程中导入数据：
执行语句之后，as语句块之后的查询语句执行结果将会被插入到前面的新建表中，并且原封不动的拷贝，所以在创建表的时候不用指定新表的字段了，继承的是原表的查询结果的字段。

create table if not exists dd as select * from dd;

数据导出

将hive数仓中的数据导出到本地文件系统hdfs。

将查询结果导出到本地：

insert overwrite local directory '/opt/module/datas/export/student'
select * from student;

如果指定的directory下含有文件，下一次执行的写入操作将会覆盖上一次的文件000000_0内容；

将查询结果格式化输出到本地：

insert overwrite local directory '/opt/module/datas/export/student1'
ROW FORMAT DELIMITED FIELDS TERMINATED BY 't'
select * from student;

将查询结果导出到HDFS文件系统：
没有了local关键字的指定默认保存到hdfs文件系统中；

insert overwrite directory '/user/atguigu/student2'
ROW FORMAT DELIMITED FIELDS TERMINATED BY 't' 
select * from student;

import和export语句：
在实际应用开发中使用频率不高，此处不做赘述，使用方法google一下。
此处只做简单的说明：需要让import的数据是export导出的数据，因为二者必须存在相对的映射关系，导入表的数据必须包含metadata，不对应或者待导入的表的结构和导入的结构不一致，会导致导入失败而抛异常。具体的使用用法详见google。。。

export table default.student to
 '/user/hive/warehouse/export/student';

清空表：
truncate语句执行，简单，此处不做赘述。

truncate table tableName;

查询语句比较运算符：

Like和RLike

模糊查询的功能：RLIKE相比较like而言，拓展了java的正则表达式，匹配条件更强大；

% 代表零个或多个字符(任意个字符)。
_ 代表一个字符。

逻辑运算符：

使用条件和mysql的语法相同。

分组

回顾一下一个DQL语句怎么写：

mysql的执行顺序和hql的执行顺序是相同的，where的执行顺序在group…by之前执行的，可以对分组的数据做初步的过滤，having执行顺序在group…by之后，是对分组之后的数据进行二次过滤的。

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

join

hive支持常规的sql join语句，但是只支持等值连接，不支持非等值连接。

select e.empno, e.ename, d.deptno, d.dname from emp e join dept d
 on e.deptno = d.deptno;

注意：join语法不支持or条件的连接。

连接查询

关于hive建表的时候尽量建立宽表而并不是多表查询频繁的表格，因为join语句执行效率不高，对于MR而言，计算资源更稀缺，对于数据冗余方面不需要考虑。

笛卡尔积现象：

忘加查询连接语句的条件会发生笛卡尔积现象。

排序

order by排序，全局排序
全局排序一个reducer，和在MR中自定义的reducer个数无关，仅仅跑一个reducer；

可以按照多个字段排序，如果前一个字段相等就继续比较下一个字段。

sort by 区内排序
区内排序执行过程中分配给多个reducer，通常为用户指定的reducer个数来执行排序，不同的reducer在各自的任务结果是有序的，但是在全局中是相对有序的，并不是完全有序的，通常使用时结合distribute by使用，先按照某个字段分区，然后在各自的分区内按照指定的排序字段进行排序。注意：hive要求distribute by语句写在sort by 语句之前；当指定的reducer为默认值-1时，一个reducer执行的结果和order by的执行结果是相同的。
cluster by ：当执行分区的字段和执行排序的字段相同时往往使用这个语句。既分区字段和排序字段相同。排序只能是升序序列，不能指定 asc 或 desc ；

分桶

分桶表的前提条件是
分桶的前提：

开启分桶功能
设置reduce的分配工作，按照人为的规定来指定reduce任务的个数：

set mapreduce.job.reduces=-1;
set hive.enforce.bucketing=true;

分桶的实质和上文中讲到的distribute by 的过程是一样的，执行MapReduce程序的reduce任务对一个文件进行切分，按照指定的字段进行切分，所以总结来看分桶操作如果想实现必须要走的是MapReduce任务才行，所以创建完表之后如何导入数据是个问题，如果导入的数据是直接load data local inpath 走的不是MapReduce程序，在hdfs中看到的并不是一个分区后的表，所以用 insert overwrite table buck select * from buck2 将数据从一个非分桶且结构完全相同的表中导入数据才能得到正确的分桶表。

创建分桶表：

create table buck (id int, name string)
clustered by(id)
into 4 buckets
row format delimited
fields terminated by 't';

创建辅助表：

create table buck2 (id int, name string)
row format delimited
fields terminated by 't'

不同的数据被分配到不同的区域中，是按照的指定字段的哈希code进行随机分配防止数据的堆积。

分桶抽样查询

分桶抽样是分桶的主要应用方式之一，基于分桶之后的数据抽取的原则：

select * from stu_buck tablesample(bucket x out of y on id);

要求x必须小于等于y；
x 代表的是从第几个桶开始抽
暂且略过此内容，用处不是很大。

常用函数：

空字符串赋值：
NVL：给值为NULL的数据赋值，他的格式是：NVL(string 1, replace_with) 如果 string1 是 NULL 则NVL函数返回的是 replace_with 的值，否则返回的是 string1 的值，如果二者的值都是 NULL 则得到的返回值是 NULL ；

select NVL(字段1, 为null时赋的值) from 表1

// if null, replace it with other column
select NVL(字段1, 字段2) from 表1

替换函数：

select regexp_relace('2019/07/03', '/', '-');

- 将字符串中的指定字符替换成指定的自定义字符。

日期处理函数：

select date_format('2020-11-11', 'yyyy-MM-dd');

- 按照第二个字符串的指定格式将日期格式处理成指定的格式。

日期计算格式：

select date_add('2020-11-11', 5);
hive> 2020-11-16

CASE WHEN

select
   dept_id, sum(if(sex='男',1,0)), sum(if(sex='女',1,0)) 
from 
   emp_sex
group by 
   dept_id;

case when：

select 
   dept_id,
   sum(case sex when '男' then 1 else 0 end) male_count,
   sum(case sex when '男' then 1 else 0 end) female_count 
from 
   emp_sex 
group by 
   dept_id;

collect_set(name)
函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段；所以要基于分组函数执行此函数；
练习：
数据集：

最终统计结果要求：

射手座,A            大海|凤姐
白羊座,A            孙悟空|猪八戒
白羊座,B            宋宋

HQL代码：

select
    t1.base,
    concat_ws('|', collect_set(t1.name)) name
from
    (select
        name,
        concat(constellation, ",", blood_type) base
    from
        person_info) t1
group by
    t1.base;

列转行：
基本函数：

EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。
LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合

样例需求：
将数据分析成：

需要将第二列拆分开，按行展示，用explode函数即可解决，然后利用lateral view 将展开的数据和原表进行产生联系，这样查出来的数据才是正确的而并非笛卡尔积现象。

select
    movie,
    category_name
from 
    movie_info lateral view explode(category) table_tmp as category_name;

例题分析：

蚂蚁金服数仓题1：

解法：

首先求出来所有用户的总共的减少碳排放量；

select user_id, sum(low_carbon) sum_low_carbon
 from user_low_carbon
where
 date_format(regexp_replace( data_dt, '/', '-'), 'yyyy-MM') < '2017-10' 
 group by user_id; t1

注意对日期的处理和筛选，然后按照用户的id进行筛选，利用分组函数求出每个用户的总减少排碳量；

取出胡杨的能量和沙柳的能量；

select low_carbon from plant_carbon where plant_id = 'p004'; t2
select low_carbon from plant_carbon where plant_id = 'p002'; t3

计算每个人申请领取的沙柳个数；

select user_id, floor((t1.sum_low_carbon - t2.low_carbon)/t3.low_carbon) as int_count
from
(select user_id, sum(low_carbon) sum_low_carbon from user_low_carbon
where date_format(regexp_replace( data_dt, '/', '-'), 'yyyy-MM') < '2017-10' group by user_id)t1,
(select low_carbon from plant_carbon where plant_id = 'p004')t2,
(select low_carbon from plant_carbon where plant_id = 'p002')t3 order by int_count desc; t4

将下一次个用户的排碳量的值和本用户的排碳量的值展示在同一行；

select t4.user_id, t4.int_count counts, LEAD(t4.int_count, 1, '9999-99-99') over(order by t4.int_count desc) lead_count
from
(select user_id, floor((t1.sum_low_carbon - t2.low_carbon)/t3.low_carbon) as int_count
from
(select user_id, sum(low_carbon) sum_low_carbon from user_low_carbon
where date_format(regexp_replace( data_dt, '/', '-'), 'yyyy-MM') < '2017-10' group by user_id)t1,
(select low_carbon from plant_carbon where plant_id = 'p004')t2,
(select low_carbon from plant_carbon where plant_id = 'p002')t3 order by int_count desc) t4; t5

对最终处理完成在一行中展示的表进行处理；

select t5.user_id, (t5.counts - t5.lead_count) sub from 
(select t4.user_id, t4.int_count counts, LEAD(t4.int_count, 1, '9999-99-99') over(order by t4.int_count desc) lead_count
from
(select user_id, floor((t1.sum_low_carbon - t2.low_carbon)/t3.low_carbon) as int_count
from
(select user_id, sum(low_carbon) sum_low_carbon from user_low_carbon
where date_format(regexp_replace( data_dt, '/', '-'), 'yyyy-MM') < '2017-10' group by user_id)t1,
(select low_carbon from plant_carbon where plant_id = 'p004')t2,
(select low_carbon from plant_carbon where plant_id = 'p002')t3 order by int_count desc) t4)t5;

蚂蚁金服数仓题2：

首先抓住条件：利用2017和大于100的这两个条件进行过滤；

select user_id, date_format(regexp_replace(data_dt, '/', '-'), 'yyyy-MM-dd') new_data_dt 
from user_low_carbon 
where substring(data_dt, 1, 4)='2017' group by user_id, data_dt having sum(low_carbon) >= 100;

要求数据的话需要将前两天和后两天的日期放到同一行中，便于后面的作差运算；

select user_id, new_data_dt,
lag(new_data_dt, 2, '1970-01-01') over(partition by user_id order by new_data_dt) lag2,
lag(new_data_dt, 1, '1970-01-01') over(partition by user_id order by new_data_dt) lag1,
lead(new_data_dt, 2, '1970-01-01') over(partition by user_id order by new_data_dt) lead2,
lead(new_data_dt, 1, '1970-01-01') over(partition by user_id order by new_data_dt) lead1
from (select user_id, date_format(regexp_replace(data_dt, '/', '-'), 'yyyy-MM-dd') new_data_dt 
from user_low_carbon 
where substring(data_dt, 1, 4)='2017' group by user_id, data_dt having sum(low_carbon) >= 100) t1;

作差得到结果：

select user_id, new_data_dt,
datediff(new_data_dt, lag2) as lag2_diff,
datediff(new_data_dt, lag1) as lag1_diff,
datediff(new_data_dt, lead1) as lead1_diff,
datediff(new_data_dt, lead2) as lead2_diff
from (select user_id, new_data_dt,
lag(new_data_dt, 2, '1970-01-01') over(partition by user_id order by new_data_dt) lag2,
lag(new_data_dt, 1, '1970-01-01') over(partition by user_id order by new_data_dt) lag1,
lead(new_data_dt, 2, '1970-01-01') over(partition by user_id order by new_data_dt) lead2,
lead(new_data_dt, 1, '1970-01-01') over(partition by user_id order by new_data_dt) lead1
from (select user_id, date_format(regexp_replace(data_dt, '/', '-'), 'yyyy-MM-dd') new_data_dt 
from user_low_carbon 
where substring(data_dt, 1, 4)='2017' group by user_id, data_dt having sum(low_carbon) >= 100) t1)t2;

根据连续的数据具有的规律进行筛选：

now - lag2 : 2
now - lag1 : 1
now - lead1 : -1
now - lead2 : -2

select user_id, new_data_dt
from (select user_id, new_data_dt,
datediff(new_data_dt, lag2) as lag2_diff,
datediff(new_data_dt, lag1) as lag1_diff,
datediff(new_data_dt, lead1) as lead1_diff,
datediff(new_data_dt, lead2) as lead2_diff
from (select user_id, new_data_dt,
lag(new_data_dt, 2, '1970-01-01') over(partition by user_id order by new_data_dt) lag2,
lag(new_data_dt, 1, '1970-01-01') over(partition by user_id order by new_data_dt) lag1,
lead(new_data_dt, 2, '1970-01-01') over(partition by user_id order by new_data_dt) lead2,
lead(new_data_dt, 1, '1970-01-01') over(partition by user_id order by new_data_dt) lead1
from (select user_id, date_format(regexp_replace(data_dt, '/', '-'), 'yyyy-MM-dd') new_data_dt 
from user_low_carbon 
where substring(data_dt, 1, 4)='2017' group by user_id, data_dt having sum(low_carbon) >= 100) t1)t2)t3
where (lag1_diff=1 and lag2_diff=2) or (lag1_diff=1 and lead1_diff=-1) or (lead1_diff=-1 and lead2_diff=-2);

解法二：利用等差数列的特点；
为每个用户分配rank，一定是等差数列的排列，如果用户的日期大于100的情况是连续的，日期也是连续的，也符合等差数列的特点，两个等差数列作差之后得到的结果一定是常数，即通过对用户分类求出每个用户的常数，然后对每个用户进行计数之后找出常数大于3的即可；

select user_id, date_format(regexp_replace(data_dt, '/', '-'), 'yyyy-MM-dd') data_dt
from user_low_carbon where substring(data_dt, 1, 4)='2017' group by user_id, data_dt having sum(low_carbon) >= 100; t1

select user_id, data_dt, row_number() over(partition by user_id order by data_dt) nums, substring(data_dt, 10, 10) dd
from (select user_id, date_format(regexp_replace(data_dt, '/', '-'), 'yyyy-MM-dd') data_dt
from user_low_carbon where substring(data_dt, 1, 4)='2017' group by user_id, data_dt having sum(low_carbon) >= 100)t1; t2

select user_id, data_dt, (dd - nums) sub
from
(select user_id, data_dt, row_number() over(partition by user_id order by data_dt) nums, substring(data_dt, 10, 10) dd
from (select user_id, date_format(regexp_replace(data_dt, '/', '-'), 'yyyy-MM-dd') data_dt
from user_low_carbon where substring(data_dt, 1, 4)='2017' group by user_id, data_dt having sum(low_carbon) >= 100)t1)t2; t3

select user_id, count(sub) as s
from
(select user_id, data_dt, (dd - nums) sub
from
(select user_id, data_dt, row_number() over(partition by user_id order by data_dt) nums, substring(data_dt, 10, 10) dd
from (select user_id, date_format(regexp_replace(data_dt, '/', '-'), 'yyyy-MM-dd') data_dt
from user_low_carbon where substring(data_dt, 1, 4)='2017' group by user_id, data_dt having sum(low_carbon) >= 100)t1)t2) t3
group by user_id, sub; t4

select user_id
from
(select user_id, count(sub) as s
from
(select user_id, data_dt, (dd - nums) sub
from
(select user_id, data_dt, row_number() over(partition by user_id order by data_dt) nums, substring(data_dt, 10, 10) dd
from (select user_id, date_format(regexp_replace(data_dt, '/', '-'), 'yyyy-MM-dd') data_dt
from user_low_carbon where substring(data_dt, 1, 4)='2017' group by user_id, data_dt having sum(low_carbon) >= 100)t1)t2) t3
group by user_id, sub) t4
group by user_id, s having max(s) >= 4;

样例：用hive实现wordcount

create table wordcount(line string)
row format delimited
fields terminated by ',';

load data local inpath '/root/hive_data/wc.txt' into table wordcount;

select explode(split(line, '\s')) as word from wordcount; t

select
	t.word,
	count(t.word)
from
	(select explode(split(lines, '\s')) as word from wordcount) t
group by
	t.word;

先将数据按行用空格隔开存到数组中，然后调用explode函数将数组按行裂开，然后用户count函数进行统计即可。

自定义函数UDF：

在本地运行hive需要配置hadoop的依赖jar，以及hive的依赖jar：
启动maven工程的quick-start项目，配置pom.xml


    
      junit
      junit
      4.11
      test
    
    
      org.apache.hive
      hive-exec
      2.3.3
    
    
      org.apache.hadoop
      hadoop-client
      2.7.3
    
    
      org.apache.hadoop
      hadoop-common
      2.7.3
    
    
      org.apache.hadoop
      hadoop-hdfs
      2.7.3
    
    
      org.apache.hadoop
      hadoop-mapreduce-client-core
      2.7.3

自定义函数实现接口：

package com.tommy;

import org.apache.hadoop.hive.ql.exec.UDF;

public class MyUDF extends UDF {
    public int evaluate(int data) {

        return data + 5;
    }
}

打成jar
add jar /home/moudel/apache-hive-2.3.3-bin/lib/hive-demo-1.0-SNAPSHOT.jar
create function addFive as 'com.tommy.MyUDF' 注意最后指定类的全限定名；
假设将idea工程自定义的函数打成jar后上传到linux中，然后在某个库中添加函数，则该函数只能在指定的添加操作执行的数据库中执行，既函数的添加是针对于某个具体的库的，若只在某一个库中添加了函数，则在其他的库中无法使用，如果要执行，那么要在有函数的数据库中执行，然后带操作的表要指定所属的数据库，例如在A库中使用函数，对B库中的表操作，使用B.table即可；

自定义UDTF

过程：
初始化类：

@Override
    public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {

        // 定义输出数据的列名
        List fieldNames = new ArrayList<>();
        fieldNames.add("word");

        // 定义输出数据的类型
        List fieldOIs = new ArrayList<>();
        fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);  // 列将来以什么下形式输出的;
        // 就写什么类型
        // 返回值
        return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames, fieldOIs);
    }

核心处理逻辑代码：

    public void process(Object[] objects) throws HiveException {
  // process函数传的参数是数组，默认接受多个参数
        // 1. 获取数据
        String data = objects[0].toString();

        // 2.获取分隔符
        String splitKey = objects[1].toString();

        // 3.切分数据
        String[] words = data.split(splitKey);

        // 4. 遍历写出
        // 这个循环代表就是一进多出的体现，进入一次object数据，通过for循环输出n次;
        for (String word : words) {

            // 将数据放入到集合中
            dataList.clear();
            dataList.add(word);

            // 写出数据
            forward(dataList);   // 高度封装的方法，仅需要关心;
        }
    }

Hive数据仓库

大数据系统相关栏目本月热门文章