HIVE总结

2023-05-16

一：hive作用

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

Hive本质：将HQL转化成MapReduce程序

（1）Hive处理的数据存储在HDFS

（2）Hive分析数据底层的实现是MapReduce

（3）执行程序运行在Yarn上

1.1：Hive优缺点

1>Hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数;

缺点：

1）Hive的HQL表达能力有限

（1）迭代式算法无法表达

（2）数据挖掘方面不擅长，由于MapReduce数据处理流程的限制，效率更高的算法却无法实现。

2）Hive的效率比较低 (Hive调优比较困难，粒度较粗)

1.2:Hive和数据库比较

从结构上来看，Hive 和数据库除了拥有类似的查询语言，再无类似之处

1.3:数据更新

Hive是针对数据仓库应用设计的，而数据仓库的内容是读多写少的。因此，Hive中不建议对数据的改写，所有的数据都是在加载的时候确定好的。

1.4:执行延迟

Hive 在查询数据的时候，由于没有索引，需要扫描整个表，因此延迟较高。

二：HIve使用

2.1：本地模式

set hive.exec.mode.local.auto=true; //开启本地mr

注意：当切换成本地模式，可能回报一个内存溢出的错误，因为本地模式默认只给我们分配 256M 内存。可以通过修改 hive-env.sh.template

2.2：一般操作

2.3：客户端查看hdfs

三:Hive中的数据类型

基本数据类型和集合数据类型

四：Hive操作

load data local inpath '/opt/module/hive/datas/test.txt' into table 表名;

Hdfs上创建一个库放到指定路径下

4.1：外部表

五：Hive操作表

5.1：上传文件到HDFS下的表

hadoop fs -put student.txt /user/hive/warehouse/student

-- 查看当前表的详情 desc student;

desc formatted student; -- 查看当前表的详细信息

五：DML数据操作

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

5.3：导入数据的方式

-- 3. 导入数据
 --- 本地不覆盖导入
 load data local inpath '/opt/module/hive-3.1.2/datas/ztudent.txt'
 into table student;
 --- 本地覆盖导入
 load data local inpath '/opt/module/hive-3.1.2/datas/student1.txt'
 overwrite
 into table student;

 --- HDFS导入
 load data inpath '/student/ztudent.txt'
 into table student;
 --- 注意：从本地导入数据属于cp行为，如果从HDFS导入属于mv行为


 1.3 通过查询语句向表中插入数据（Insert）
 -- 1. 建表
 create table stu1(id int, name string)
 row format delimited fields terminated by '\t';

 -- 2. 基本插入数据
 insert into table  stu1 values(1,'wangwu'),(2,'zhaoliu');

 -- 3. 基本模式插入（根据单张表查询结果）
 insert into table stu1 select id, name from student;

 1.4 查询语句中创建表并加载数据（As Select）
 create table stu2
 as select id, name from student;

 1.5 创建表时通过Location指定加载数据路径
 create table stu3(id int, name string)
 row format delimited fields terminated by '\t' 
 location '/student';

5.4：Insert数据导出(通常用于数据迁移)

2.1 Insert导出
 --1. 将查询的结果导出到本地
 insert overwrite local directory '/opt/module/hive-3.1.2/datas/export_data'
 select * from student;

 --2. 将查询的结果导出到本地(对数据格式约束)
 insert overwrite local directory '/opt/module/hive-3.1.2/datas/export_data'
 row format delimited fields terminated by "\t"
 select * from student;

 --3. 将查询的结果导出到HDFS上(没有local)
 insert overwrite directory '/export_data'
 row format delimited fields terminated by "\t"
 select * from student;

 2.2 Export导出到HDFS上 & Import数据到指定Hive表中
 -- export导出到HDFS
 export table student to '/export/student';

 -- import 导入Hive的表中(导数据的过程中会根据元数据进行建表)
 import table bucunzai from '/export/student';
  
 -- 注意 Export & Import 使用场景：数据迁移的时候常用！！！

 2.3 清除表中数据（Truncate）
 注意：当清楚数据的表是内部表的时候才允许清楚，外部表不允许清除数据
 truncate table student;

六：Hive函数

6.1：常用函数

HiveSQL语法有限制

6.2：SQL的执行顺序

案例一：

Having语句 (只能和 group by 结合使用)
-- 计算每个部门最高薪水的那个哥们，并且薪资大于等于3000

使用group by，select后面的字段只能是分组字段和要求的值，如平均工资；

select 
t1.deptno,
t2.ename,
t1.max_sal
from 
(
 select 
 deptno,
 max(sal) as max_sal
 from emp 
 group by deptno having max_sal >= 3000
) t1 join emp t2
on t1.deptno = t2.deptno
where t1.max_sal = t2.sal
;

七：JOIN

(emp 可以看成左表 dept可以看成右表表连接的形式与key有关 )

emp 和 dept的交集数据： join

emp的全部数据和 dept匹配到的数据：  left join

dept 的全部数据和 emp 匹配到的数据： right join

获取emp独有的数据 left join   where d.deptno is null

获取dept独有的数据 right join   where e.deptno is null

获取 emp 和dept 的全部数据(全连接) （ union 传统的方式   full join 进行全连接(Hive支持，传统SQL不支持，oracle中支持)）

获取emp和dept各自独有的数据   full join (

where e.deptno is null or d.deptno is null )

full join 进行全连接(Hive支持，传统SQL不支持，oracle中支持)

4.1 emp 和 dept的交集数据
select 
e.empno,e.ename,e.deptno,d.deptno,d.dname
from emp e 
join
dept d 
on e.deptno = d.deptno; 

+----------+----------+-----------+-----------+-------------+
| e.empno  | e.ename  | e.deptno  | d.deptno  |   d.dname   |
+----------+----------+-----------+-----------+-------------+
| 7369     | SMITH    | 20        | 20        | RESEARCH    |
| 7499     | ALLEN    | 30        | 30        | SALES       |
| 7521     | WARD     | 30        | 30        | SALES       |
| 7566     | JONES    | 20        | 20        | RESEARCH    |
| 7654     | MARTIN   | 30        | 30        | SALES       |
| 7698     | BLAKE    | 30        | 30        | SALES       |
| 7782     | CLARK    | 10        | 10        | ACCOUNTING  |
| 7788     | SCOTT    | 20        | 20        | RESEARCH    |
| 7839     | KING     | 10        | 10        | ACCOUNTING  |
| 7844     | TURNER   | 30        | 30        | SALES       |
| 7876     | ADAMS    | 20        | 20        | RESEARCH    |
| 7900     | JAMES    | 30        | 30        | SALES       |
| 7902     | FORD     | 20        | 20        | RESEARCH    |
| 7934     | MILLER   | 10        | 10        | ACCOUNTING  |
+----------+----------+-----------+-----------+-------------+


4.2 emp的全部数据 和 dept匹配到的数据
select
e.empno,e.ename,e.deptno,d.deptno,d.dname
from emp e 
left join 
dept d 
on e.deptno = d.deptno;

+----------+----------+-----------+-----------+-------------+
| e.empno  | e.ename  | e.deptno  | d.deptno  |   d.dname   |
+----------+----------+-----------+-----------+-------------+
| 7369     | SMITH    | 20        | 20        | RESEARCH    |
| 7499     | ALLEN    | 30        | 30        | SALES       |
| 7521     | WARD     | 30        | 30        | SALES       |
| 7566     | JONES    | 20        | 20        | RESEARCH    |
| 7654     | MARTIN   | 30        | 30        | SALES       |
| 7698     | BLAKE    | 30        | 30        | SALES       |
| 7782     | CLARK    | 10        | 10        | ACCOUNTING  |
| 7788     | SCOTT    | 20        | 20        | RESEARCH    |
| 7839     | KING     | 10        | 10        | ACCOUNTING  |
| 7844     | TURNER   | 30        | 30        | SALES       |
| 7876     | ADAMS    | 20        | 20        | RESEARCH    |
| 7900     | JAMES    | 30        | 30        | SALES       |
| 7902     | FORD     | 20        | 20        | RESEARCH    |
| 7934     | MILLER   | 10        | 10        | ACCOUNTING  |
| 6666     | MILLER   | 60        | NULL      | NULL        |
+----------+----------+-----------+-----------+-------------+


4.3 dept 的全部数据 和 emp 匹配到的数据
select
e.empno,e.ename,e.deptno,d.deptno,d.dname
from emp e 
right join 
dept d 
on e.deptno = d.deptno;
+----------+----------+-----------+-----------+-------------+
| e.empno  | e.ename  | e.deptno  | d.deptno  |   d.dname   |
+----------+----------+-----------+-----------+-------------+
| 7782     | CLARK    | 10        | 10        | ACCOUNTING  |
| 7839     | KING     | 10        | 10        | ACCOUNTING  |
| 7934     | MILLER   | 10        | 10        | ACCOUNTING  |
| 7369     | SMITH    | 20        | 20        | RESEARCH    |
| 7566     | JONES    | 20        | 20        | RESEARCH    |
| 7788     | SCOTT    | 20        | 20        | RESEARCH    |
| 7876     | ADAMS    | 20        | 20        | RESEARCH    |
| 7902     | FORD     | 20        | 20        | RESEARCH    |
| 7499     | ALLEN    | 30        | 30        | SALES       |
| 7521     | WARD     | 30        | 30        | SALES       |
| 7654     | MARTIN   | 30        | 30        | SALES       |
| 7698     | BLAKE    | 30        | 30        | SALES       |
| 7844     | TURNER   | 30        | 30        | SALES       |
| 7900     | JAMES    | 30        | 30        | SALES       |
| NULL     | NULL     | NULL      | 40        | OPERATIONS  |
+----------+----------+-----------+-----------+-------------+

4.4 获取emp独有的数据
select
e.empno,e.ename,e.deptno,d.deptno,d.dname
from emp e 
left join 
dept d 
on e.deptno = d.deptno
where d.deptno is null
;
+----------+----------+-----------+-----------+----------+
| e.empno  | e.ename  | e.deptno  | d.deptno  | d.dname  |
+----------+----------+-----------+-----------+----------+
| 6666     | MILLER   | 60        | NULL      | NULL     |
+----------+----------+-----------+-----------+----------+

4.5 获取dept独有的数据
select
e.empno,e.ename,e.deptno,d.deptno,d.dname
from emp e 
right join 
dept d 
on e.deptno = d.deptno
where e.deptno is null
;

+----------+----------+-----------+-----------+-------------+
| e.empno  | e.ename  | e.deptno  | d.deptno  |   d.dname   |
+----------+----------+-----------+-----------+-------------+
| NULL     | NULL     | NULL      | 40        | OPERATIONS  |
+----------+----------+-----------+-----------+-------------+


4.6 获取 emp 和dept 的全部数据(全连接)
-- 实现方式一： union 传统的方式
select
e.empno,e.ename,e.deptno,d.deptno,d.dname
from emp e 
left join 
dept d 
on e.deptno = d.deptno
union
select
e.empno,e.ename,e.deptno,d.deptno,d.dname
from emp e 
right join 
dept d 
on e.deptno = d.deptno;

+------------+------------+-------------+------------+
| _u1.empno  | _u1.ename  | _u1.deptno  | _u1.dname  |
+------------+------------+-------------+------------+
| NULL       | NULL       | NULL        | 40         |
| 6666       | MILLER     | 60          | NULL       |
| 7369       | SMITH      | 20          | 20         |
| 7499       | ALLEN      | 30          | 30         |
| 7521       | WARD       | 30          | 30         |
| 7566       | JONES      | 20          | 20         |
| 7654       | MARTIN     | 30          | 30         |
| 7698       | BLAKE      | 30          | 30         |
| 7782       | CLARK      | 10          | 10         |
| 7788       | SCOTT      | 20          | 20         |
| 7839       | KING       | 10          | 10         |
| 7844       | TURNER     | 30          | 30         |
| 7876       | ADAMS      | 20          | 20         |
| 7900       | JAMES      | 30          | 30         |
| 7902       | FORD       | 20          | 20         |
| 7934       | MILLER     | 10          | 10         |
+------------+------------+-------------+------------+

-- 方式二： full join 进行全连接(Hive支持，传统SQL不支持，oracle中支持)
select
e.empno,e.ename,e.deptno,d.deptno,d.dname
from emp e 
full join 
dept d 
on e.deptno = d.deptno;

4.7  获取emp和dept各自独有的数据
select
e.empno,e.ename,e.deptno,d.deptno,d.dname
from emp e 
full join 
dept d 
on e.deptno = d.deptno
where e.deptno is null or d.deptno is null
;
+----------+----------+-----------+-----------+-------------+
| e.empno  | e.ename  | e.deptno  | d.deptno  |   d.dname   |
+----------+----------+-----------+-----------+-------------+
| NULL     | NULL     | NULL      | 40        | OPERATIONS  |
| 6666     | MILLER   | 60        | NULL      | NULL        |
+----------+----------+-----------+-----------+-------------+

八：Group By（）Having语句和 LIke

GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列结果进行分组，然后对每个组执行聚合操作。

Having语句

九：order by() sort by() Distribute By() Cluster By()

set mapreduce.job.reduces=3;

set hive.exec.mode.local.auto=false;

运行结果

-- 小结：Hive中如果单独使用sort by 它也能进行局部排序，但是每个文件中的数据是随机进入，如果
-- 如果将 distribute by + sort by 使用就完全和Hadoop中的HashPartitioner相吻合！！！

十：分区表(Hive中分区就是分目录)

Hive中没有索引的概念

分区表在查询的时候要加上分区字段

针对庞大的数据集，存储的时候考虑用分区表表存储，只要按照一定规律
-- 进行分区，将来查询的时候就可以使用分区字段结合查询条件小范围匹配数据
-- 最终避免全表扫描，从而提升查询效率。

即可以将表的分区字段看成表的普通字段

-- 查看分区详情
show partitions dept_par;
-- 准备数据 导入
load data [local] inpath '数据的path' [overwrite] into table student
[partition (partcol1=val1,…)];

load data local inpath '/opt/module/hive-3.1.2/datas/dept_20200401.log' into table dept_par
partition (day='20200401');

load data local inpath '/opt/module/hive-3.1.2/datas/dept_20200402.log' into table dept_par
partition (day='20200402');

load data local inpath '/opt/module/hive-3.1.2/datas/dept_20200403.log' into table dept_par
partition (day='20200403');
注意：再给分区表中导入数据的时候一定要加上分区属性partition(day='20200403')
2. 增加分区
--增加单个分区
alter table dept_par add partition(day='20200404');
--增加多个分区
alter table dept_par add partition(day='20200405') partition(day='20200406');
3. 删除分区
alter table dept_par drop partition(day='20200404');
alter table dept_par drop partition(day='20200405'), partition(day='20200406');

二级分区：

二级分区：
1. 创建分区表 
-- 建表
create table dept_par1(
deptno int, dname string, loc string
)
partitioned by (day string, hour string)
row format delimited fields terminated by '\t';


-- 准备数据 导入
load data [local] inpath '数据的path' [overwrite] into table student
[partition (partcol1=val1,…)];

load data local inpath '/opt/module/hive-3.1.2/datas/dept_20200401.log' into table dept_par1
partition (day='20200401', hour='00');

load data local inpath '/opt/module/hive-3.1.2/datas/dept_20200402.log' into table dept_par1
partition (day='20200401', hour='01');

load data local inpath '/opt/module/hive-3.1.2/datas/dept_20200403.log' into table dept_par1
partition (day='20200401', hour='02');

--思考：分区是不是越多越好？
-- 分区越多导致文件的数量就会增加，在HDFS尽量避免出现大量小文件，在正常使用中一般做到2级分区就可以啦！

10.1：分区表和数据产生关联的三种方式

分区表和数据产生关联的三种方式：(分区表和普通表不一样需要此三种操作才能找到对应的元数据)

方式一：上传数据后修复（msck repair table dept_par2;）

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

(alter table dept_par add partition(day='20200401');)

方式三：创建文件夹后load数据到分区
load data local inpath '/opt/module/hive-3.1.2/datas/dept_20200402.log' into table dept_par4
partition (day='20200402');

10.2：动态分区

相关参数：

注意通过insert into的方式给分区表中插入数据一定要指定 partition(loc)
-- 进行动态分区（动态的往 dept_par_dy 插入数据）
insert into table dept_par_dy partition(loc)
select deptno, dname, loc from dept;

10.3:分桶表(分桶操作实际上就是Hadoop中的分区)

面对海量数据表查询效率低下，此时就考虑缩小数据的管理范围，
-- 从而达到避免查询的时候进行全表扫描，分桶操作实质就是Hadoop
-- 中默认规则的分区操作

分桶表在查询的时候要加上分桶字段，类似于分区表在查询的时候要加上分区字段

注意:在进行分桶操作的时候要设置 set mapreduce.job.reduces=-1; (因为分区分的是分目录(分区上传文件的时候是每个区的文件上传到对应的目录下边)，分桶分的是分文件(上传文件的时候只上传一个文件，hive会按照指定的分桶字段创建不同的桶))
创建分桶表 
CREATE TABLE  table_name 
[(col_name data_type [COMMENT col_comment], ...)] 
[CLUSTERED BY (col_name, col_name, ...) 
INTO num_buckets BUCKETS]

-- 建表
create table stu_bucket(id int, name string)
clustered by(id) 
into 4 buckets
row format delimited fields terminated by '\t';

-- 加载数据
load data local inpath '/opt/module/hive-3.1.2/datas/student.txt' 
into table stu_bucket;

10.4:抽样查询

抽样查询取是按照id进行随机分桶，然后选择第x个桶，并不是真正意义上按照分桶的规则进行划分，然后获取第x个桶的数据，而是随机的。
四、 抽样查询
-- 语法: TABLESAMPLE(BUCKET x OUT OF y ON 分桶字段) 

select * from stu_bucket tablesample(bucket 1 out of 4 on id);

select * from emp tablesample(bucket 1 out of 4 on empno);

十一：Hive中的函数(思路：按照需求缺什么补什么)

2> CASE WHEN THEN ELSE END（逻辑判断函数）

1). 按照部门分组,再按照性别分组，求统计
select 
dept_id,
sex,
count(name) as num
from emp_sex
group by dept_id,sex;
+----------+------+------+
| dept_id  | sex  | num  |
+----------+------+------+
| A        | 女    | 1    |
| A        | 男    | 2    |
| B        | 女    | 2    |
| B        | 男    | 1    |
+----------+------+------+

2). 实现目标结果数据
select 
dept_id,
sum(CASE sex WHEN '男' THEN 1 ELSE 0 END) as man,
sum(CASE sex WHEN '女' THEN 1 ELSE 0 END) as women
from emp_sex
group by dept_id;
+----------+------+--------+
| dept_id  | man  | women  |
+----------+------+--------+
| A        | 2    | 1      |
| B        | 1    | 2      |
+----------+------+--------+
3). 扩展 if() 函数

select 
dept_id,
sum(if(sex='男',1,0)) as man,
sum(if(sex='女',1,0)) as women
from emp_sex
group by dept_id;
+----------+------+--------+
| dept_id  | man  | women  |
+----------+------+--------+
| A        | 2    | 1      |
| B        | 1    | 2      |
+----------+------+--------+

11.1：函数第二波

COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

1). 把 constellation 和 blood_type 拼接起来
select 
concat_ws(',',constellation,blood_type) as cb,
name
from person_info; --> t1    因为第二个表查询的时候用到了第一个表中的name，因此要把那么查出来
+--------+-------+
|   cb   | name  |
+--------+-------+
| 白羊座,A  | 孙悟空   |
| 射手座,A  | 大海    |
| 白羊座,B  | 宋宋    |
| 白羊座,A  | 猪八戒   |
| 射手座,A  | 凤姐    |
| 白羊座,B  | 苍老师   |
+--------+-------+

2). 根据 cb 字段进行分组
select 
t1.cb,
concat_ws('|',collect_set(t1.name))
from
(
 select 
 concat_ws(',',constellation,blood_type) as cb,
 name
 from person_info
) t1
group by t1.cb;
+--------+----------+
| t1.cb  |   _c1    |
+--------+----------+
| 射手座,A  | 大海|凤姐    |
| 白羊座,A  | 孙悟空|猪八戒  |
| 白羊座,B  | 宋宋|苍老师   |
+--------+----------+

侧写函数LATERAL VIEW 语法：(因为SQL语法局限需要 LATERAL VIEW）

explode 一进多出函数，侧写函数代表虚拟表的过程；执行过程

炸开用explode 对应key 用侧写函数LATERAL VIEW

EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。
select 
explode(split(category,',')) as category_name,
movie
from movie_info;
+------+
| col  |
+------+
| 悬疑   |
| 动作   |
| 科幻   |
| 剧情   |
| 悬疑   |
| 警匪   |
| 动作   |
| 心理   |
| 剧情   |
| 战争   |
| 动作   |
| 灾难   |
+------+

select 
movie,
category_name
from movie_info
lateral view explode(split(category,',')) movie_info_tmp as category_name
;

+--------------+----------------+
|    movie     | category_name  |
+--------------+----------------+
| 《疑犯追踪》       | 悬疑             |
| 《疑犯追踪》       | 动作             |
| 《疑犯追踪》       | 科幻             |
| 《疑犯追踪》       | 剧情             |
| 《Lie to me》  | 悬疑             |
| 《Lie to me》  | 警匪             |
| 《Lie to me》  | 动作             |
| 《Lie to me》  | 心理             |
| 《Lie to me》  | 剧情             |
| 《战狼2》        | 战争             |
| 《战狼2》        | 动作             |
| 《战狼2》        | 灾难             |
+--------------+----------------+

11.2：函数第三波

窗口函数就是针对SQL中的限制重新开辟一个处理数据空间，
-- 可以灵活的的控制窗口的大小，窗口的大小取决于数据的记录的条数。

需求一：查询在2017年4月份购买过的顾客及总人数

-- 开窗函数此处应用大意是按照名字分组开辟了一个新的统计名字个数的空间，这个空间的范围是从每一个组中的起始位置到最后位置即每一个组统计一次。

WINDOW 规范默认为
ROW BETWEEN UNBOUNDED PRECEDING(从前面的起点) AND UNBOUNDED FOLLOWING(到后面的终点).

count(name) over() totalNum 统计姓名的个数此时如果不分组的情况会对相同的名字进行个数统计，因此需要加上 group by() 而over() 是在group by()之后，

开辟了新的统计组个数的空间(即要统计总人数就要分组
select
name ,
count(name) 
from business
where substring(orderdate,0,7) = '2017-04'
group by name ;

name	_c1
jack	1
mart	4
)但是分组的结果会重复统计相同的人名，此时开窗的作用就是开辟了新的统计人数的空间，即一个组统计一次；
1) 根据需求的日期过滤数据
select 
orderdate ,
name
from business 
where substring(orderdate,0,7) = '2017-04' ;

orderdate	name
2017-04-06	jack
2017-04-08	mart
2017-04-09	mart
2017-04-11	mart
2017-04-13	mart

2). 根据 name 进行分组,然后使用开窗函数重新处理分组后的结果 求统计
    -- (因为用到了分组，而分组要查询的字段 是分组字段  因此 查询字段不能带日期)

select
name,
count(name) over() totalNum
from business
where substring(orderdate,0,7) = '2017-04'
group by name ;
-- 开窗函数此处应用  大意是 按照名字分组 开辟了一个新的统计名字个数的空间，这个空间的范围是
-- 从每一个组中的起始位置到最后位置 。 


如果
select
name,
count(name) over() totalNum
from business
where substring(orderdate,0,7) = '2017-04'
没有开窗语句也没有order by语句 则 默认窗口大小从起点到终点
name	totalnum
mart	5
mart	5
mart	5
mart	5
jack	5
需求二：查询顾客的购买明细及月购买总额

目的是统计顾客的购买明细(每个顾客每个月的花费金额) 及月购买总额(一个月内所有顾客花费的总金额) 因此SQL(

sum(cost) over(partition by substring(orderdate,0,7)) costByMonth 表示在求一个月内所有顾客花费的总金额的时候重新开辟了一个新的空间，新的空间的大小是按照substring(orderdate,0,7)一个月进行分区汇总的。

（partition by 可以控制新空间的大小)只有窗口语句没有order by语句此时窗口的大小是每一个分区内的起点到终点
select 
name,
cost,
orderdate,
sum(cost) over(partition by substring(orderdate,0,7)) total_cost
from business;
需求三：查询每个顾客的购买明细及 (每个)月购买总额
select
name,
cost,
orderdate,
sum(cost) over(partition by substring(orderdate,0,7),name) oneCostByMonth
from business ;
(sum(cost) over(partition by substring(orderdate,0,7),name) oneCostByMonth)

开窗函数新空间的大小由按照月份和顾客确定。只有窗口语句没有order by语句此时窗口的大小是每一个分区内的起点到终点

需求四：上述的场景, 将每个顾客的cost按照日期进行累加
-- 方式一
select
name,
cost,
orderdate,
sum(cost) over(partition by name order by orderdate) oneCustTotal
from business ;

-- 方式二

select
name,
cost,
orderdate,
sum(cost) over(partition by name order by orderdate 
rows between UNBOUNDED PRECEDING and current row
) oneCustTotal
from business ;
开窗Sql代码

（sum(cost) over(partition by name order by orderdate) oneCustTotal）

每个顾客的cost按照日期进行累加，即开窗的新的空间是按照顾客名字进行分区，按照日期进行累加，因此要排序，开窗规则的默认窗口是从起始行累加到当前行。

(sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row) 开窗规则的默认窗口是从起始行累加到当前行。

注意：
当 ORDER BY 指定时缺少 WINDOW 子句，WINDOW 窗口大小规范默认为
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

当 ORDER BY 和 WINDOW 子句都缺失时即单纯的over()，WINDOW 窗口大小规范默认为
ROW BETWEEN UNBOUNDED PRECEDING 起点 AND UNBOUNDED FOLLOWING. 终点

-- 需求五：查询每个顾客上次的购买时间和下一次购买时间
LAG(col,n,default_val)：往前第n行数据
LEAD(col,n, default_val)：往后第n行数据
select
name ,
orderdate,
lag(orderdate,1,'startTime') over(partition by name order by orderdate)  preTime,
lead(orderdate,1,'endTime') over(partition by name order by orderdate)  nextTime
from business ;
name	orderdate	pretime	nexttime
jack	2017-01-01	startTime	2017-01-05
jack	2017-01-05	2017-01-01	2017-01-08
jack	2017-01-08	2017-01-05	2017-02-03
jack	2017-02-03	2017-01-08	2017-04-06
jack	2017-04-06	2017-02-03	endTime
mart	2017-04-08	startTime	2017-04-09
mart	2017-04-09	2017-04-08	2017-04-11
mart	2017-04-11	2017-04-09	2017-04-13
mart	2017-04-13	2017-04-11	endTime
neil	2017-05-10	startTime	2017-06-12
neil	2017-06-12	2017-05-10	endTime
tony	2017-01-02	startTime	2017-01-04
tony	2017-01-04	2017-01-02	2017-01-07
tony	2017-01-07	2017-01-04	endTime
开窗函数是作为对其它函数在统计的时候由于SQL的局限，进行辅助作用的辅助主函数的，因此不能单独存在；因此以下写法不正确，应该将over()开窗函数放在主函数的后边。且开窗函数不能单独使用(以下两种写法都是错误的)
select
name ,
orderdate,
over(partition by name order by orderdate)  lag(orderdate,1,'startTime')  preTime,
over(partition by name order by orderdate)  lead(orderdate,1,'endTime')  nextTime
from business ;


开窗函数不能单独使用
select
name ,
orderdate,
over(partition by name order by orderdate)  preTime,
over(partition by name order by orderdate)  nextTime
from business ;
需求六：查询前20%时间的订单信息

NTILE(n)：把有序窗口的行分发到指定数据的组中， NTILE(n) n为需要的组数各个组有默认有编号，编号从1开始，对于每一行，
NTILE函数返回此行所属的组的编号。注意：n必须为int类型。
select
t1.name,
t1.orderdate,
t1.cost
from
(
select
name,
orderdate,
cost,
-- 分成5组 按照日期排序  
ntile(5) over(order by orderdate) as group_id
from business 
) t1 
where t1.group_id = 1;

t1.name	t1.orderdate	t1.cost
jack	2017-01-01	10
tony	2017-01-02	15
tony	2017-01-04	29
rank() over(partition by subject order by score desc) rankOrder,
dense_rank() over(partition by subject order by score desc) dense_rank_Order,
row_number() over(partition by subject order by score desc) row_number_orderNum 开窗函数辅助主函数 row_number()进行统计
select
name ,
subject,
score ,
rank() over(partition by subject order by score desc) rankOrder,
dense_rank() over(partition by subject order by score desc) dense_rank_Order,
row_number() over(partition by subject order by score desc) row_number_orderNum
from score ;

name	subject	score	rankorder	dense_rank_order	row_number_ordernum
孙悟空	数学	95	1	1	1
宋宋	数学	86	2	2	2
婷婷	数学	85	3	3	3
大海	数学	56	4	4	4
宋宋	英语	84	1	1	1
大海	英语	84	1	1	2
婷婷	英语	78	3	2	3
孙悟空	英语	68	4	3	4
大海	语文	94	1	1	1
孙悟空	语文	87	2	2	2
婷婷	语文	65	3	3	3
宋宋	语文	64	4	4	4

十二：自定义函数(看文档)

1). 获取客户端连接对象
2). 调用API完成具体功能
3). 关闭资源

十三：Hive企业级调优(看文档)

十四：Hive ETL案例分析

ETL阶段：清洗字段数目

数据清洗（ETL）
-- 操作数据的注意点
1). 对字段的数量进行验证
2). 对category字段中的空格进行处理
3). 对关联视频字段进行处理

-- ETL的思路
1). 通过MR程序进行数据清洗（编码）
2). 打包，上传至Linux中
3). 执行数据清洗

-- 进行数据清洗
1). 把待处理的数据上传至HDFS

2). 执行MR清洗程序
hadoop jar /opt/module/hive-3.1.2/datas/GuliETL-1.0-SNAPSHOT.jar com.atguigu.mr.GuliEtlDriver /gulivideo/video /gulivideo/video/output

需求分析：

需求一：统计视频(字段用videoId视频id) 观看数(views用字段观看次数) Top10

select
videoId,
views
from gulivideo_orc
order by views desc
limit 10;

+--------------+-----------+
|   videoid    |   views   |
+--------------+-----------+
| dMH0bHeiRNg  | 42513417  |
| 0XxI-hvPRRA  | 20282464  |
| 1dmVU08zVpA  | 16087899  |
| RB-wUgnyGv0  | 15712924  |
| QjA5faZF1A8  | 15256922  |
| -_CSo1gOd48  | 13199833  |
| 49IDp76kjPw  | 11970018  |
| tYnn51C3X_w  | 11823701  |
| pv5zWaTEVkI  | 11672017  |
| D2kJZOfq7zk  | 11184051  |
+--------------+-----------+

需求二：统计视频类别热度Top10(热度用视频的数量来体现)

统计视频类别热度Top10(热度用视频的数量来体现)
-- 分析：
1). 获取视频类别并炸开
select 
videoId,
category_name
from gulivideo_orc
lateral view explode(category) gulivideo_orc_tmp as category_name; --> t1 

2). 根据 t1 中的 category_name 进行分组 求统计 获取 hot值并根据hot值倒序取前十
select 
category_name,
count(t1.videoId) as hot
from 
(
 select 
 videoId,
 category_name
 from gulivideo_orc
 lateral view explode(category) gulivideo_orc_tmp as category_name
) t1 
group by t1.category_name
order by hot desc 
limit 10
;

+----------------+---------+
| category_name  |   hot   |
+----------------+---------+
| Music          | 179049  |
| Entertainment  | 127674  |
| Comedy         | 87818   |
| Animation      | 73293   |
| Film           | 73293   |
| Sports         | 67329   |
| Games          | 59817   |
| Gadgets        | 59817   |
| People         | 48890   |
| Blogs          | 48890   |
+----------------+---------+

需求三：统计出视频观看数最高的20个视频的所属类别以及类别包含Top20视频的个数

视频(字段用videoId视频id) 观看数(views用字段观看次数) 最高的20个视频
select
videoId ,
views ,
category ,
category_name
from gulivideo_orc
order by views desc
limit 20 ;    -- t1

-- top 20 视频的videoId 与 类别
select
t1.videoId videoId_t2,
category_name
from (
select
videoId ,
views ,
category 
from gulivideo_orc
order by views desc
limit 20
) t1
LATERAL VIEW explode(t1.category) gulivideo_orc_temp as category_name ;   -- t2

以及   类别   包含Top20视频的个数 (按照类别进行分组  统计视频的个数)
select
t2.category_name,
count(t2.videoId_t2) as num
from (
select
t1.videoId videoId_t2,
category_name
from (
select
videoId ,
views ,
category 
from gulivideo_orc
order by views desc
limit 20
) t1
LATERAL VIEW explode(t1.category) gulivideo_orc_temp as category_name
) t2
group by t2.category_name  ;


t2.category_name	_c1
Blogs	            2
Comedy	            6
Entertainment	    6
Music	            5
People	            2
UNA	                1

需求四：统计视频观看数Top50所关联视频的所属类别排名

统计视频，观看次数，和视频相关id
select 
videoId ,
views ,
relatedId 
from gulivideo_orc
order by views desc
limit 50 ;   -- t1
将 t1 中的关联视频字段炸开
select
t1.videoId ,
videoId_Id
from (
select 
videoId ,
views ,
relatedId 
from gulivideo_orc
order by views desc
limit 50
) t1
LATERAL VIEW explode(t1.relatedId) 
gulivideo_orc_temp as videoId_Id ;   -- t2

通过和原表Join获取关联视频对应的所属类别 

select 
t2.videoId_Id videoId_t4,
t3.category category_t4
from(
select
t1.videoId ,
videoId_Id
from (
select 
videoId ,
views ,
relatedId 
from gulivideo_orc
order by views desc
limit 50
) t1
LATERAL VIEW explode(t1.relatedId) 
gulivideo_orc_temp as videoId_Id
) t2 
join gulivideo_orc t3
on t2.videoId_Id = t3.videoId  ;  -- t4

将 t4 结果中的 category 炸开

select
t4.videoId_t4 videoId_t5,
category_name_t4 category_t5
from
(
select 
t2.videoId_Id videoId_t4,
t3.category category_t4
from(
select
t1.videoId ,
videoId_Id
from (
select 
videoId ,
views ,
relatedId 
from gulivideo_orc
order by views desc
limit 50
) t1
LATERAL VIEW explode(t1.relatedId) 
gulivideo_orc_temp as videoId_Id
) t2 
join gulivideo_orc t3
on t2.videoId_Id = t3.videoId 
) t4
LATERAL VIEW explode(t4.category_t4) t4_temp as category_name_t4 ;   -- t5

根据 t5 的 category_name 进行分组 然后求统计

select
t5.category_t5 category_t6,
count(t5.videoId_t5) num
from
(
select
t4.videoId_t4 videoId_t5,
category_name_t4 category_t5
from
(
select 
t2.videoId_Id videoId_t4,
t3.category category_t4
from(
select
t1.videoId ,
videoId_Id
from (
select 
videoId ,
views ,
relatedId 
from gulivideo_orc
order by views desc
limit 50
) t1
LATERAL VIEW explode(t1.relatedId) 
gulivideo_orc_temp as videoId_Id
) t2 
join gulivideo_orc t3
on t2.videoId_Id = t3.videoId 
) t4
LATERAL VIEW explode(t4.category_t4) t4_temp as category_name_t4
) t5
group by t5.category_t5  ;  -- t6

根据 t6 中num 进行排名
select
t6.category_t6,
t6.num ,
rank() over(order by t6.num desc) as r_k
from
(
select
t5.category_t5 category_t6,
count(t5.videoId_t5) num
from
(
select
t4.videoId_t4 videoId_t5,
category_name_t4 category_t5
from
(
select 
t2.videoId_Id videoId_t4,
t3.category category_t4
from(
select
t1.videoId ,
videoId_Id
from (
select 
videoId ,
views ,
relatedId 
from gulivideo_orc
order by views desc
limit 50
) t1
LATERAL VIEW explode(t1.relatedId) 
gulivideo_orc_temp as videoId_Id
) t2 
join gulivideo_orc t3
on t2.videoId_Id = t3.videoId 
) t4
LATERAL VIEW explode(t4.category_t4) t4_temp as category_name_t4
) t5
group by t5.category_t5
) t6 ;

需求五：统计每个类别中的视频热度Top10，以Music为例 (热度以观看数衡量)

方式一：

获取视频类别的并炸开
select
videoId,
views,
category_name
from gulivideo_orc
lateral view explode(category) gulivideo_orc_tmp as category_name; --> t1

2). 根据 t1 中的 category_name 分组然后求统计
select 
t1.category_name,
t1.views
from 
(
 select
videoId,
views,
category_name
from gulivideo_orc
lateral view explode(category) gulivideo_orc_tmp as category_name
) t1 
where t1.category_name = 'Music'
order by t1.views desc 
limit 10
;

+-------------------+-----------+
| t1.category_name  | t1.views  |
+-------------------+-----------+
| Music             | 15256922  |
| Music             | 11823701  |
| Music             | 11672017  |
| Music             | 9579911   |
| Music             | 7533070   |
| Music             | 6946033   |
| Music             | 6935578   |
| Music             | 6193057   |
| Music             | 5581171   |
| Music             | 5142238   |
+-------------------+-----------+

方式二：

统计类别为Music 的videoId
select
videoId,
views,
category_name
from gulivideo_orc
LATERAL VIEW explode(category) gulivideo_orc_temp as category_name 
where category_name = "Music"   
order by views desc
limit 10;

需求六：统计每个类别中的视频热度Top10

1).  获取视频类别的并炸开
select
videoId,
views,
category_name
from gulivideo_orc
LATERAL VIEW explode(category) gulivideo_orc_temp as category_name ; -- t1


2). 根据 观看数 对每个类别进行排序
select
t1.videoId videoId_t2,
t1.views views_t2,
t1.category_name category_t2,
rank() over(partition by t1.category_name order by views desc) r_k
from (
select
videoId,
views,
category_name
from gulivideo_orc
LATERAL VIEW explode(category) gulivideo_orc_temp as category_name
) t1    ;    -- t2


获取前10 

select 
t2.videoId_t2 ,
t2.views_t2 ,
t2.category_t2,
t2.r_k
from (
select
t1.videoId videoId_t2,
t1.views views_t2,
t1.category_name category_t2,
rank() over(partition by t1.category_name order by views desc) r_k
from (
select
videoId,
views,
category_name
from gulivideo_orc
LATERAL VIEW explode(category) gulivideo_orc_temp as category_name
) t1 
) t2
where t2.r_k <=10 ;

每个类别都是前十名  
aRNzWyD7C9o	8825788	UNA	1
jtExxsiLgPM	5320895	UNA	2
PxNNR4symuE	4033376	UNA	3
8cjTSvvoddc	3486368	UNA	4
LIhbap3FlGc	2849832	UNA	5
lCSTULqmmYE	2179562	UNA	6
UyTxWvp8upM	2106933	UNA	7
y6oXEWowirI	1666084	UNA	8
_x2-AmY8FI8	1403113	UNA	9
ICoDFooBXpU	1376215	UNA	10
RjrEQaG5jPM	2803140	Vehicles	1
cv157ZIInUk	2773979	Vehicles	2
Gyg9U1YaVk8	1832224	Vehicles	3
6GNB7xT3rNE	1412497	Vehicles	4
tth9krDtxII	1347317	Vehicles	5
46LQd9dXFRU	1262173	Vehicles	6
pdiuDXwgrjQ	1013697	Vehicles	7
kY_cDpENQLE	956665	Vehicles	8
YtxfbxGz1u4	942604	Vehicles	9
aCamHfJwSGU	847442	Vehicles	10

十五：SQL练习

需求一：

方式一：
一： 求出每个学生所有科目成绩的最小值
select
uid,
min(score) over(partition by uid) min_score
from score  ;   -- t1

二： 求出学科的平均成绩的最大值
select
subject_id,
avg(score)  avg_score
from score  
group by subject_id 
order by avg_score desc
limit 1 ;  -- t2

三： 求出每个学生所有科目成绩的最小值 大于 
	 各个学科的平均成绩的最大值的uid

select
uid
from(
select
uid,
min(score) over(partition by uid) min_score
from score
) t1 , (

select
subject_id,
avg(score)  avg_score
from score  
group by subject_id 
order by avg_score desc
limit 1 
) t2
where t1.min_score > t2.avg_score  ;

uid
1001
1001
1001

 
方式二  (标准方式)
求出每个学科平均成绩

select
uid,
score,
avg(score) over(partition by subject_id) avg_score
from score   ;  	-- t1
求出每个学科平均成绩
1003	70	81.66666666666667
1002	85	81.66666666666667
1001	90	81.66666666666667
1003	70	81.66666666666667
1002	85	81.66666666666667
1001	90	81.66666666666667
1003	85	81.66666666666667
1002	70	81.66666666666667
1001	90	81.66666666666667
根据是否大于平均成绩记录flag，大于则记为0否则记为1
select
t1.uid,
if(t1.score>t1.avg_score,0,1) flag
from(
select
uid,
score,
avg(score) over(partition by subject_id) avg_score
from score
)  t1 ;         -- t2

1003	1
1002	0
1001	0
1003	1
1002	0
1001	0
1003	0
1002	1
1001	0
根据学生id进行分组统计flag的和，和为0则是所有学科都大于平均成绩
select
uid
from (
select
t1.uid,
if(t1.score>t1.avg_score,0,1) flag
from(
select
uid,
score,
avg(score) over(partition by subject_id) avg_score
from score
)  t1
) t2 
group by uid
having sum(flag)=0;

uid
1001

需求二：

使用SQL统计出每个用户的累积访问次数

修改数据格式
select
    userId,
    date_format(regexp_replace(visitDate,'/','-'),'yyyy-MM') mn,
    visitCount
from 
    action ;      -- t1

u01	2017-01	5
u02	2017-01	6
u03	2017-01	8
u04	2017-01	3
u01	2017-01	6
u01	2017-02	8
U02	2017-01	6
U01	2017-02	4

计算每人单月访问量
select
t1.userId userId_t2,
t1.mn mn_t2 ,
sum(t1.visitCount) mn_count_t2
from (
select
    userId,
    date_format(regexp_replace(visitDate,'/','-'),'yyyy-MM') mn,
    visitCount
from 
    action
) t1
group by t1.userId , t1.mn ;    -- t2

userid_t2	mn_t2	mn_count_t2
U01	2017-02	4
U02	2017-01	6
u01	2017-01	11
u01	2017-02	8
u02	2017-01	6
u03	2017-01	8
u04	2017-01	3

按月累计访问量

select
t2.userid_t2,
t2.mn_t2,
t2.mn_count_t2,
sum(t2.mn_count_t2) over(partition by t2.userid_t2 order by t2.mn_t2)
from (
select
t1.userId userId_t2,
t1.mn mn_t2 ,
sum(t1.visitCount) mn_count_t2
from (
select
    userId,
    date_format(regexp_replace(visitDate,'/','-'),'yyyy-MM') mn,
    visitCount
from 
    action
) t1
group by t1.userId , t1.mn
)  t2  ;

u01	2017-01	11	11
u01	2017-02	12	23
u02	2017-01	12	12
u03	2017-01	8	8
u04	2017-01	3	3

需求三：

有50W个京东店铺，每个顾客访客访问任何一个店铺的任何一个商品时都会产生一条访问日志，访问日志存储的表名为Visit，访客的用户id为user_id，被访问的店铺名称为shop。

方式一  ： (不对，每家店铺应该去除相同顾客，即相同顾客算一次)
select
shop,
count(user_id) num
from visit
group by shop ;
a	9
b	6
c	4
方式二：  一家店去除了相同的顾客
select
shop,
count(distinct user_id) 
from visit 
group by shop  ; 
a	4
b	4
c	3

每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数

（1）查询每个店铺被每个用户访问次数
select
shop ,
user_id ,
count(*) ct
from visit 
group by shop , user_id  ;  -- t1

shop	user_id	ct
a	u1	3
b	u1	2
a	u2	2
b	u2	1
c	u2	2

（2）计算每个店铺被用户访问次数排名
select
t1.shop shop_t2,
t1.user_id user_id_t2,
t1.ct ct_t2,
rank() over(partition by shop order by t1.ct) r_k
from(
select
shop ,
user_id ,
count(*) ct
from visit 
group by shop , user_id
) t1  ;     -- t2

（3）取每个店铺排名前3的
select
t2.shop_t2,
t2.user_id_t2,
t2.ct_t2
from (
select
t1.shop shop_t2,
t1.user_id user_id_t2,
t1.ct ct_t2,
rank() over(partition by shop order by t1.ct ) r_k
from(
select
shop ,
user_id ,
count(*) ct
from visit 
group by shop , user_id
) t1
)  t2 
where t2.r_k <= 3 ;


a	u3	1
a	u2	2
a	u1	3
a	u5	3
b	u2	1
b	u5	1
b	u1	2
b	u4	2
c	u3	1
c	u6	1
c	u2	2

需求四:

数据样例:2017-01-01,10029028,1000003251,33.57。
1）给出 2017年每个月的订单数、用户数、总成交金额。

select
date_format(dt,'yyyy-MM'),
count(order_id),
count(distinct user_id),
sum(amount)
from order_tab
where date_format(dt,'yyyy') = '2017'
group by date_format(dt,'yyyy-MM') ;

2）给出2017年11月的新客数(指在11月才有第一笔订单)
select
count(user_id)
from order_tab
group by user_id
having date_format(min(dt),'yyyy-MM')='2017-11' ;

需求五：

select 
    sum(user_total_count),
    sum(user_total_avg_age),
    sum(twice_count),
    sum(twice_count_avg_age)
from 
(select
   0 user_total_count,
   0 user_total_avg_age,
   count(*) twice_count,
   cast(sum(age)/count(*) as decimal(10,2)) twice_count_avg_age
from 
(
   select
   user_id,
   min(age) age
from
   (select
   user_id,
   min(age) age
from
   (
    select
   user_id,
   age,
   date_sub(dt,rk) flag
from
   (
    select
       dt,
       user_id,
       min(age) age,
       rank() over(partition by user_id order by dt) rk
    from
       user_age
    group by
       dt,user_id
    )t1
    )t2
group by
   user_id,flag
having
   count(*)>=2)t3
group by
   user_id 
)t4

union all

select
   count(*) user_total_count,
   cast((sum(age)/count(*)) as decimal(10,1)),
   0 twice_count,
   0 twice_count_avg_age
from 
   (
      select
         user_id,
         min(age) age 
      from 
         user_age 
      group by 
         user_id
   )t5)t6;

需求六：

select
   t1.userid,
   t1.paymenttime,
   od.money
from
   (select
   userid,
   min(paymenttime) paymenttime
from
   ordertable
where
   date_format(paymenttime,'yyyy-MM')='2017-10'
group by
   userid)t1
join
   ordertable od
on
   t1.userid=od.userid
   and
   t1.paymenttime=od.paymenttime;

需求七：

select
    ip,
    interface,
    count(*) ct
from
    ip
where
    date_format(time,'yyyy-MM-dd HH')>='2016-11-09 14'
    and 
    date_format(time,'yyyy-MM-dd HH')<='2016-11-09 15'
    and
    interface='/api/user/login'
group by
ip,interface
order by
    ct desc
limit 2;t1

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

hive

HIVE总结的相关文章

Hive 将字符串转换为字符数组

例如如何将字符串转换为字符数组 abcd gt a b c d 我知道分割方法 SELECT split abcd a b c d 最后一个空格有错误吗或任何其他想法这实际上不是一个错误蜂巢分割功能 https github com
Hive 和 PIG/Grunt shell 挂在 cygwin 上

我在 Windows 7 机器 32 位上以本地模式运行 Hadoop 我已将 HIVE PIG Hadoop Java6 全部安装在 C 驱动器上我使用的 Cygwin 版本 2 819 我已将 C 安装在 cygwin 上我可以从
java.lang.RuntimeException：无法实例化 org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient

我在 ubuntu 14 0 上安装了 Hadoop 2 7 1 和 apache hive 1 2 1 版本为什么会出现这个错误是否需要安装任何元存储当我们在终端上输入 hive 命令时 xml 的内部是如何调用的这些 xml 的
Apache Hive regEx serde：数据类型

对于处理日志我想使用 Apache Hive regEx serde 但我只找到使用 String 作为表列的数据类型的示例现在我的问题是是否支持基于日期的类型整数和数组还是只是字符串此示例和其他示例仅使用字符串 CREAT
Apache Hive - 复杂数据类型映射不起作用

蜂巢版本2 1 1 问题描述集合项终止值作为映射键插入蜂巢表 CREATE TABLE profiles id int name struct
从hive中的子查询中获取值

我试图参数化配置单元中的值而不是在查询中对其进行硬编码下面是查询 select from employee where sal gt 30000 但我不需要使用硬编码的 30000 值而是需要来自相同的查询如下所示但我遇到了问题
在 HIVE 中查找函数

我想检查一个字段是否包含字符串我想要一个如下所示的函数 FIND string to find field to search 我的数据如下所示 field to search no match in this string record
如何通过Spark Thrift Server访问自定义UDF？

我正在 EMR 上运行 Spark Thrift 服务器我通过以下方式启动 Spark Thrift 服务器 sudo u spark usr lib spark sbin start thriftserver sh queue inte
Hive 分区表上的 Spark 行为

我用的是 Spark 2 实际上我不是执行查询的人所以我不能包含查询计划数据科学团队问过我这个问题我们将 Hive 表划分为 2000 个分区并以 parquet 格式存储当在 Spark 中使用相应的表时执行器之间恰好执行了 2
使用 Hive 自定义输入格式

Update 好吧事实证明以下不起作用的原因是因为我使用的是较新版本的InputFormat API import org apache hadoop mapred这是旧的与import org apache hadoop mapredu
Hive 表的默认分隔符是什么？

如果我们在创建表时不提及任何分隔符 hive 是否有默认分隔符创建表日志 ts bigint 行字符串按 dt 字符串国家地区字符串分区默认分隔符 001 如果创建hive表时没有设置您可以将其更改为其他分隔符例如 hive
在 IDEA 中运行 Spark on Hive 项目期间创建事务连接工厂时出错

我正在尝试为 Spark Streaming 项目设置一个开发环境该项目需要将数据写入 Hive 我有一个包含 1 个主设备 2 个从设备和 1 台开发机器的集群在 Intellij Idea 14 中编码在 Spark shell
Hive如何存储数据，什么是SerDe？

当查询表时 SerDe 将将文件中的字节中的一行数据反序列化为 Hive 内部使用的对象来操作该行数据执行 INSERT 或 CTAS 时请参阅第 441 页上的导入数据表的 SerDe 将将 Hive 的一行数据的内部表示序列化为
Hive 中字符串数据类型是否有最大大小？

谷歌了很多但没有在任何地方找到它或者这是否意味着只要允许集群 Hive 就可以支持任意大字符串数据类型如果是这样我在哪里可以找到我的集群可以支持的最大字符串数据类型大小提前致谢 Hive 列表的当前文档STRING作为有效的数据类
Hive 中 Sortby 和 orderby 查询的区别

Hive sort by and order by命令用于按排序顺序获取数据例如 Sort by hive gt SELECT E EMP ID FROM Employee E SORT BY E empid Order by hive
连接到 Hive 时使用 Spark 进行 Kinit

我正在尝试从独立的 Spark 连接到 Hive hadoop 集群具有 kerberos 身份验证有人可以让我知道如何在 Spark 程序中执行 kinit 我可以连接到配置单元吗更新我的 Spark 与 Hadoop 位于不同的集
使用 python 从 hive 读取数据时的性能问题

我在 hive 中有一个表其中包含 351 837 110 MB 大小记录我正在使用 python 读取该表并写入 sql server 在此过程中从 hive 读取数据到 pandas dataframe 需要很长时间当我加载整
hive查询无法通过jdbc生成结果集

我是 Hive 和 Hadoop 的新手在我的教程中我想将表创建为 import java sql SQLException import java sql Connection import java sql ResultSet im
计算行的排名

我想根据一个字段对用户 ID 进行排名对于相同的字段值排名应该相同该数据位于 Hive 表中 e g user value a 5 b 10 c 5 d 6 Rank a 1 c 1 d 3 b 4 我怎样才能做到这一点可以使用ra
在 HIVE 中，使用 COALESCE 将 Null 值替换为相同的列值

我想用同一列中的值替换特定列的空值我想得到结果我在下面尝试过 select d day COALESCE val LAST VALUE val TRUE OVER ORDER BY d day ROWS BETWEEN UNBOUNDED

随机推荐

C++11向线程函数传递参数

template span class token operator lt span class Function span class token punctuation span class span class token punct
C++11之std::future对象使用说明

std future介绍在前面几篇文章中基本都用到thread对象 xff0c 它是C 43 43 11中提供异步创建多线程的工具但是我们想要从线程中返回异步任务结果 xff0c 一般需要依靠全局变量 xff1b 从安全角度看 xff0
delete 和 delete[]真正区别

我们通常从教科书上看到这样的说明 xff1a delete 释放new分配的单个对象指针指向的内存 delete 释放new分配的对象数组指针指向的内存那么 xff0c 按照教科书的理解 xff0c 我们看下下面的代码 xff1a spa
Activity的onNewIntent

一个应用的Activity可供多种方式调用启动 xff0c 当多个调用希望只有一个Activity的实例存在 xff0c 并且还要区分是被谁启动或是已经启动被谁拉到前台来的 xff0c 这就需要Activity的onNewIntent In
at 与 crontab调度命令详解

目录 1 At调度只执行一次 1 1准备任务 xff1a 查看at服务是否开启 1 2绝对时间定制任务 1 3相对时间定制任务 1 4查看at进程 1 5删除at任务 2 crontab调度可重复执行 2 1简述 2 2crontab调
[计算机网络] --- STP (下篇) 工作原理及配置

文章目录前言一 stp工作原理二 stp计算过程工作步骤1 选举根桥2 选举根端口3 选举指定端口4 确立阻塞端口三例题 xff1a 前言上一篇文章我们介绍了stp的起源和一些相关术语 xff0c 接下来我们就正式开始介绍stp的
iOS富文本实现(一)：私密阅读效果

废话不多说 xff0c 咱们直接先看效果 xff01 看是不是咱想要的哈目录 xff1a 一前言 xff1a 二核心需求说明三实现效果核心代码片段四几个注意的小细节一前言 xff1a 我的第一篇CSDN文章就这样发布了 x
登录注册 servlet基础

工具类页面与数据库的交换 span class token keyword package span org span class token punctuation span lizhenhua span class token pun
解决Ubuntu开机之后不显示桌面图标，只显示桌面文件的问题

Ubuntu开机之后不显示桌面图标 xff08 指左侧那一栏 xff09 xff0c 但桌面上的文件可以使用 xff0c 鼠标可以使用鼠标右键 gt 打开终端 xff1b xff08 如果鼠标不能使用 xff0c 按ctl 43 alt
yii2中登录后跳转回登录前请求的页面

yii2中登录后跳转回登录前请求的页面作者 php 发布时间 2017 06 16 浏览 1030次转发请备注原文地址 xff1a www niwoxuexi com blog php article 158 html yii2中登录后
Gateway

Gateway SpringCloud微服务网关组件一 Spring Cloud Gateway简介 1 为什么要用Gateway xff1f 在微服务架构中 xff0c 通常一个系统会被拆分为多个微服务 xff0c 微服务之间的调用可以
Android(安卓)时间戳和日期之间的转化

注 xff1a 转发请注明原地址 xff1a https www niwoxuexi com blog android article 170 在Android开发过程中 xff0c 经常会遇到日期的各种格式转换 xff0c 主要使用Sim
如何判断Activity是否在前台显示

转发请备注原文地址 xff1a https www niwoxuexi com blog android00 article 223 html 我们在Android开发过程中 xff0c 经常会判断Activity是否在前台显示 xff0c
Android 获取cache缓存的目录路径

转发请备注原文地址 xff1a https www niwoxuexi com blog android00 article 224 html Android开发中 xff0c 有时需要知道cache缓存的路径我写了一个静态类 xff0c
objc[8715]: Class JavaLaunchHelper is implemented in both...

在Mac上 xff0c 升级IntelliJ Idea 2017 01后 xff0c 运行的时候出现了一个红色的警告 xff1a objc 8715 Class JavaLaunchHelper is implemented in both
mac上使用dex2jar遇到的权限问题的解决

本文来自 xff1a https www niwoxuexi com blog android article 235 html 摘要在dex2jar目录下执行sudo sh d2j dex2jar sh classes dex时报错如下
Android Studio将module变为library

本文来源你我学习网 xff1a 地址 https www niwoxuexi com blog android article 244 html 我们在开发的时候如在在Android Studio中的module打包成library方便模块
Navicat Premium实现mysql数据库备份/还原

转发请备注原文地址 xff1a https www niwoxuexi com blog php article 161 htm Navicat Premium 是一个非常好用的数据库 xff08 支持 MySQL SQLite Oracl
在vue中获取dom元素

转发请备注原文链接地址 xff1a https www niwoxuexi com blog web article 307 html 在vue中经常会通过js操作dom对象 xff0c 可以通过给标签添加ref属性实现 xff0c 下面通
HIVE总结

一 xff1a hive作用 Hive是基于Hadoop的一个数据仓库工具 xff0c 可以将结构化的数据文件映射为一张表 xff0c 并提供类SQL 查询功能 Hive本质 xff1a 将HQL转化成MapReduce程序 xff08 1

HIVE总结

一：hive作用

1.1：Hive优缺点

1.2:Hive和 数据库比较

从结构上来看，Hive 和数据库除了拥有类似的查询语言，再无类似之处

1.3:数据更新

1.4:执行延迟

Hive 在查询数据的时候，由于没有索引，需要扫描整个表，因此延迟较高。

二：HIve使用

2.1：本地模式

set hive.exec.mode.local.auto=true; //开启本地mr

2.2：一般操作

2.3：客户端查看hdfs

三:Hive中的数据类型

基本数据类型和集合数据类型

四：Hive操作

load data local inpath '/opt/module/hive/datas/test.txt' into table 表名;

Hdfs上创建一个库放到指定路径下

4.1：外部表

五：Hive操作表

5.1：上传文件到HDFS下的表

hadoop fs -put student.txt /user/hive/warehouse/student

-- 查看当前表的详情 desc student;

desc formatted student; -- 查看当前表的详细信息

五：DML数据操作

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

5.3：导入数据的方式

5.4：Insert数据导出(通常用于数据迁移)

六：Hive函数

6.1：常用函数

HiveSQL语法有限制

6.2：SQL的执行顺序

案例一：

Having语句 (只能和 group by 结合使用) -- 计算每个部门最高薪水的那个哥们，并且薪资大于等于3000

使用group by，select后面的 字段只能是分组字段和 要求的值，如平均工资；

七：JOIN

(emp 可以看成左表 dept可以看成右表 表连接的形式与key有关 )

emp 和 dept的交集数据： join

emp的全部数据 和 dept匹配到的数据 ： left join

dept 的全部数据 和 emp 匹配到的数据 ： right join

获取emp独有的数据 left join where d.deptno is null

获取dept独有的数据 right join where e.deptno is null

获取 emp 和dept 的全部数据(全连接) （ union 传统的方式 full join 进行全连接(Hive支 持，传统SQL不支持，oracle中支持)）

获取emp和dept各自独有的数据 full join (

where e.deptno is null or d.deptno is null )

full join 进行全连接(Hive支持，传统SQL不支持，oracle中支持)

八：Group By（）Having语句 和 LIke

GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列结果进行分组，然后对每个组执行聚合操作。

Having语句

九：order by() sort by() Distribute By() Cluster By()

set mapreduce.job.reduces=3;

set hive.exec.mode.local.auto=false;

-- 小结：Hive中如果单独使用sort by 它也能进行局部排序，但是每个文件中的数据是随机进入，如果 -- 如果 将 distribute by + sort by 使用 就完全和Hadoop中的HashPartitioner相吻合！！！

十：分区表(Hive中分区就是分目录)

Hive中没有索引的概念

分区表在查询的时候要加上分区字段

即可以将表的分区字段看成表的普通字段

-- 查看分区详情 show partitions dept_par;

注意：再给分区表中导入数据的时候一定要加上 分区 属性partition(day='20200403')

二级分区：

--思考：分区是不是越多越好？ -- 分区越多导致文件的数量就会增加，在HDFS尽量避免出现大量小文件，在正常使用中一般做到2级分区就可以啦！

10.1：分区表和数据产生关联的三种方式

分区表和数据产生关联的三种方式：(分区表和普通表不一样需要此三种操作才能找到对应的元数据)

方式一：上传数据后修复 （msck repair table dept_par2;）

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

(alter table dept_par add partition(day='20200401');)

方式三：创建文件夹后load数据到分区

10.2：动态分区

相关参数 ：

注意通过insert into的方式给分区表中插入数据一定要指定 partition(loc)

10.3:分桶表(分桶操作实际上就是Hadoop中的分区)

面对海量数据表查询效率低下，此时就考虑缩小数据的管理范围， -- 从而达到避免查询的时候进行全表扫描，分桶操作实质就是Hadoop -- 中默认规则的分区操作

分桶表在查询的时候要加上分桶字段，类似于分区表在查询的时候要加上分区字段

10.4:抽样查询

抽样查询取是按照id进行随机分桶，然后选择第x个桶，并不是真正意义上按照分桶的规则进行划分，然后获取第x个桶的数据，而是随机的。

十一：Hive中的函数(思路：按照需求缺什么 补什么)

2> CASE WHEN THEN ELSE END（逻辑判断函数）

11.1：函数第二波

COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

侧写函数LATERAL VIEW 语法 ：(因为SQL语法局限 需要 LATERAL VIEW）

1.2:Hive和数据库比较

Having语句 (只能和 group by 结合使用)
-- 计算每个部门最高薪水的那个哥们，并且薪资大于等于3000

使用group by，select后面的字段只能是分组字段和要求的值，如平均工资；

(emp 可以看成左表 dept可以看成右表表连接的形式与key有关 )

emp的全部数据和 dept匹配到的数据： left join

dept 的全部数据和 emp 匹配到的数据： right join

获取 emp 和dept 的全部数据(全连接) （ union 传统的方式 full join 进行全连接(Hive支持，传统SQL不支持，oracle中支持)）

八：Group By（）Having语句和 LIke

-- 小结：Hive中如果单独使用sort by 它也能进行局部排序，但是每个文件中的数据是随机进入，如果
-- 如果将 distribute by + sort by 使用就完全和Hadoop中的HashPartitioner相吻合！！！

-- 查看分区详情
show partitions dept_par;

注意：再给分区表中导入数据的时候一定要加上分区属性partition(day='20200403')

--思考：分区是不是越多越好？
-- 分区越多导致文件的数量就会增加，在HDFS尽量避免出现大量小文件，在正常使用中一般做到2级分区就可以啦！

方式一：上传数据后修复（msck repair table dept_par2;）

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

相关参数：

面对海量数据表查询效率低下，此时就考虑缩小数据的管理范围，
-- 从而达到避免查询的时候进行全表扫描，分桶操作实质就是Hadoop
-- 中默认规则的分区操作

十一：Hive中的函数(思路：按照需求缺什么补什么)

侧写函数LATERAL VIEW 语法：(因为SQL语法局限需要 LATERAL VIEW）

explode 一进多出函数，侧写函数代表虚拟表的过程；执行过程

窗口函数就是针对SQL中的限制重新开辟一个处理数据空间，
-- 可以灵活的的控制窗口的大小，窗口的大小取决于数据的记录的条数。

-- 开窗函数此处应用大意是按照名字分组开辟了一个新的统计名字个数的空间，这个空间的范围是从每一个组中的起始位置到最后位置即每一个组统计一次。

WINDOW 规范默认为
ROW BETWEEN UNBOUNDED PRECEDING(从前面的起点) AND UNBOUNDED FOLLOWING(到后面的终点).

count(name) over() totalNum 统计姓名的个数此时如果不分组的情况会对相同的名字进行个数统计，因此需要加上 group by() 而over() 是在group by()之后，

目的是统计顾客的购买明细(每个顾客每个月的花费金额) 及月购买总额(一个月内所有顾客花费的总金额) 因此SQL(

sum(cost) over(partition by substring(orderdate,0,7)) costByMonth 表示在求一个月内所有顾客花费的总金额的时候重新开辟了一个新的空间，新的空间的大小是按照substring(orderdate,0,7)一个月进行分区汇总的。

（partition by 可以控制新空间的大小)只有窗口语句没有order by语句此时窗口的大小是每一个分区内的起点到终点

需求三：查询每个顾客的购买明细及 (每个)月购买总额

注意：
当 ORDER BY 指定时缺少 WINDOW 子句，WINDOW 窗口大小规范默认为
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

当 ORDER BY 和 WINDOW 子句都缺失时即单纯的over()，WINDOW 窗口大小规范默认为
ROW BETWEEN UNBOUNDED PRECEDING 起点 AND UNBOUNDED FOLLOWING. 终点

-- 需求五：查询每个顾客上次的购买时间和下一次购买时间
LAG(col,n,default_val)：往前第n行数据
LEAD(col,n, default_val)：往后第n行数据

开窗函数是作为对其它函数在统计的时候由于SQL的局限，进行辅助作用的辅助主函数的，因此不能单独存在；因此以下写法不正确，应该将over()开窗函数放在主函数的后边。且开窗函数不能单独使用(以下两种写法都是错误的)

NTILE(n)：把有序窗口的行分发到指定数据的组中， NTILE(n) n为需要的组数各个组有默认有编号，编号从1开始，对于每一行，
NTILE函数返回此行所属的组的编号。注意：n必须为int类型。

rank() over(partition by subject order by score desc) rankOrder,
dense_rank() over(partition by subject order by score desc) dense_rank_Order,
row_number() over(partition by subject order by score desc) row_number_orderNum 开窗函数辅助主函数 row_number()进行统计

1). 获取客户端连接对象
2). 调用API完成具体功能
3). 关闭资源

数据清洗（ETL）
-- 操作数据的注意点
1). 对字段的数量进行验证
2). 对category字段中的空格进行处理
3). 对关联视频字段进行处理

-- ETL的思路
1). 通过MR程序进行数据清洗（编码）
2). 打包，上传至Linux中
3). 执行数据清洗

-- 进行数据清洗
1). 把待处理的数据上传至HDFS

2). 执行MR清洗程序
hadoop jar /opt/module/hive-3.1.2/datas/GuliETL-1.0-SNAPSHOT.jar com.atguigu.mr.GuliEtlDriver /gulivideo/video /gulivideo/video/output

需求一：统计视频(字段用videoId视频id) 观看数(views用字段观看次数) Top10

需求三：统计出视频观看数最高的20个视频的所属类别以及类别包含Top20视频的个数

需求四：统计视频观看数Top50所关联视频的所属类别排名

需求五：统计每个类别中的视频热度Top10，以Music为例 (热度以观看数衡量)

需求六：统计每个类别中的视频热度Top10

HIVE总结的相关文章