Java 业务开发中常用的 Hive 技巧

已于 2022-07-30 15:52:17 修改
阅读量642 收藏 3
点赞数 1
分类专栏： hive 项目经验 文章标签： java
于 2020-06-08 20:04:11 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/a1102325298/article/details/106627180
版权

1. 时间戳问题

假设我们 mysql 数据库中有张表叫 student ，它有一个字段是 created（类型是 long 或 int）。mysql 数据库的 student 表在 hive 中对应的表为 dw_student，dw_student 中 created 这个字段对应的类型是 string。这个时候我们在写 hive sql 的时候，如果想比较时间戳，需要把 hive 表中的 created_at 转换为时间戳去比较，否则会查询不到任何数据且不报错。

select * from dw_student where  UNIX_TIMESTAMP(created)<=1588262400

created 字段类型为 int ，created 字段值为时间戳（单位：秒），把 created 转换为字符串（格式为：yyyy-MM-dd HH:mm:ss）可以使用下面这个函数：

  • from_unixtime(created, ‘yyyy-MM-dd HH:mm:ss’)

presto 和 hive 时间格式转换

以下内容来自转载：https://blog.csdn.net/cdyjy_litao/article/details/80252327

北京时间格式 to unix时间格式

数据格式：
2017-11-17 08:28:13
2017-11-17 08:28:10
2017-11-17 08:27:51.343
2017-11-17 08:27:48.021

presto单个标准时间转化（10位unix）：
select to_unixtime(cast ('2017-08-30 10:36:15' as timestamp))   

hive单个标准时间转化（10位unix）：
select unix_timestamp(cast ('2017-08-30 10:36:15' as timestamp))

presto单个毫秒时间转化（13位unix）：
select to_unixtime(cast ('2017-12-01 16:42:08.771' as timestamp))

hive单个毫秒时间转化（自动转为10位unix）：
select unix_timestamp(cast ('2017-12-01 16:42:08.771' as timestamp))

presto变量转化（根据时间格式分别转化为10位unix、13位unix）：
select to_unixtime(cast (time as timestamp))  

hive变量转化（只能自动转化为10位unix）：
select unix_timestamp(cast (time as timestamp))   from table1 where d = '2017-12-05'
unix时间格式 to 北京时间格式

数据格式：
1510894478
1510855235
1510855290929
1510873252377

presto单个10位unix（变为标准格式）：
select format_datetime(from_unixtime(1510284058),'yyyy-MM-dd HH:mm:ss')  

hive单个10位unix（变为标准格式）：
select from_unixtime(1323308943123,'yyyy-MM-dd HH:mm:ss')

presto单个13位unix（毫秒格式）：
select format_datetime(from_unixtime(1510284058.415),'yyyy-MM-dd HH:mm:ss.mmm')

hive单个13位unix（毫秒格式）：手动截取10位
select from_unixtime(cast(substr(1323308943123,1,10) as int),'yyyy-MM-dd HH:mm:ss')

Presto  13、10位unix变量转化（只能截取成10位转化成标准格式）：
select format_datetime(from_unixtime(cast(substr(ts,1,10) as double)),'yyyy-MM-dd HH:mm:ss')  from table1

hive变量转化（事先截取10位unix）：
select from_unixtime(cast(substr(ts,1,10) as int),'yyyy-MM-dd HH:mm:ss') from table1 where d = '2017-12-05'
时间差
select count(*), date_diff('hour',cast(format_datetime(from_unixtime(b.tradetime),'yyyy-MM-dd HH:mm:ss') as timestamp), cast(a.created as timestamp)) as diffhour
from (select gatewaypayid,created from dwd_refundorder where  created > '2022-07-24 00:00:00' and created <= '2022-07-24 09:00:00') as a 
left join (select tradetime,id from dwd_payorder) as b
on a.payid = b.id 
group by date_diff('hour',cast(format_datetime(from_unixtime(b.tradetime),'yyyy-MM-dd HH:mm:ss') as timestamp), cast(a.created as timestamp))
limit 100;

2. 分组中取多个字段

以一个场景来说明这个问题。假设 dw_student_course 表（选课表）里有很多条记录，如下表：

iduser_idcourse_idcourse_name
1521000001英语
2521000011英语
3521000021英语
4521000032数学
5521000043语文
6521000054物理
7521000064物理
8521000074物理
9521000085化学
10521000095化学

我们现在想知道某门课程的选课人数大于 2 门的该课程 id 和课程名，那写 hive sql 怎么写呢？

select max(course_id),course_name from 
dw_student_course  
group by course_name having count(course_name) > 2

PS : 注意 hive sql 中是不允许 select 后跟非 group by key 字段的，所以这里我们通过用一个聚合函数 max 来选一个 course_id 出来，这样就不会报错了。

3. 统计去重

实现方式：

  1. select count(distinct field_name) * from t_name;
  2. select count(field_name) * from t_name group by field_name; （推荐）

在 hive 中查询时，我们表的数据量一般都很大，用第一种方式查询的话，性能会比较慢，所以推荐使用第二种方式进行查询。为什么第一种方式会慢呢？因为第一种方式会导致数据倾斜，大数据并不怕数据多，就怕数据倾斜。具体原因请见如下博客：https://blog.csdn.net/xiaoshunzi111/article/details/68484426

4. 查询一张表中满足某种条件下的所有记录的某个字段的值均为同一个

比如：我要根据查询一个账单表（dwd_loan_bill_td）中业务类型（bizType）为 88 且属于同一个月账单下所有账单还款状态均为已还款的月账单 id（monthbillid）。

idbizTyperepaystatusmonthbillid
1111000001
2111000001
3201000001
48801100002
58811100002
68811220003
78811220003
88801333004
98801333004
10201009004

hive sql 如下：

select max(monthbillid) as monthbillid from dwd_loan_bill_td
where
bizType=88
group by monthbillid
having avg(repaystatus) = 1

月账单和账单的关系是一对多，每个账单的记录中会记录所对应的月账单 id。

5. 查询一张表中，某2个字段完全相同的记录

假设有表 A（id, name)，查询该表中，id 和 name 完全相同的记录：

select a.*
  from A a
inner join
 (select id, name
         from A
         group by id, name
         having count(*) > 1) b
on a.id = b.id
and a.name = b.name

转载自该链接

6. 使用tinyint(1) 用来存储枚举时候的坑

https://my.oschina.net/u/4418709/blog/3510914

7. 查询 group by 后的记录数

select count(*) from (SELECT userId FROM dwd_loan_payorder WHERE to_unixtime(cast(created as timestamp))>=1577808000 group by userId) as payorder;

8. 多表关联查询

left join 多表关联查询时，如果根据驱动表的值在辅助表中没有查询到相应的记录，则查询出来的辅助表中的字段都为 NULL。

left join 原理

W3CSCHOOL上是这样描述 left join 的： LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。

sql语句：

SELECT * FROM TAB1 LEFT JOIN TAB2 ON (TAB1.TSIZE=TAB2.T_SIZE)

TAB1、TAB2 表以及使用上述 SQL 语句执行后的结果如下图：
在这里插入图片描述
查询结果：
在这里插入图片描述
结果中 TAB1 表的（2，20）变成了 2 条，它究竟怎么增加的？

首先得了解 sql 语句的执行顺序，是这样的：

  1. FROM：对 FROM 子句中的前两个表执行笛卡尔积(交叉联接)，生成虚拟表 VT1。
  2. ON：对 VT1 应用 ON 筛选器，只有那些使为真才被插入到 TV2。

    对于 MySQL 数据库来说，left jon 的 on 中的筛选条件可以在对 FROM 子句中的前两个表执行笛卡尔积前，利用索引去过滤一些无效的数据，从而减小数据量。且推荐经过索引过滤后数据量最小的表做为驱动表，也就是左表，因为这样可以减少数据的笛卡尔积的次数。

  3. OUTER (JOIN):如果指定了 OUTER JOIN(相对于 INNER JOIN)，保留表中未找到匹配的行将作为外部行添加到 VT2，生成 TV3。如果 FROM 子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤 1 到步骤 3，直到处理完所有的表位置。
  4. WHERE：对 TV3 应用 WHERE 筛选器，只有使为 true 的行才插入 TV4。
  5. GROUP BY：按 GROUP BY 子句中的列列表对 TV4 中的行进行分组，生成 TV5。
  6. 聚合函数：把聚合结果插入 VT5，生成 VT6。
  7. HAVING：对 VT6 应用 HAVING 筛选器，只有使为true的组插入到 VT7。
  8. SELECT：处理 SELECT 列表，产生 VT8。
  9. DISTINCT：将重复的行从 VT8 中删除，产品 VT9。
  10. ORDER BY：将 VT9 中的行按 ORDER BY 子句中的列列表顺序，生成一个游标(VC10)。
  11. LIMIT：从 VC10 的开始处选择指定数量或比例的行，生成表 TV11，并返回给调用者。

PS：sql语句的执行顺序来自：http://wang286480403.iteye.com/blog/1899915

根据第 1 条，FROM 中的两个表会执行笛卡尔积，那么 TAB1 和 TAB2 的笛卡尔积就是：
在这里插入图片描述

根据第2条，应用 ON 筛选，找到 TSIZE 和 T_SIZE 中相等的记录，结果是：
在这里插入图片描述
根据第 3 条，由于指定了 left join（left join属于outer join啊！），需要保留左表中没有找到匹配的记录：
在这里插入图片描述
最后按照 sql 语句得到的结果就是这个：
在这里插入图片描述

上述内容转载自 https://blog.csdn.net/lamyourangle/article/details/76673850 这位同学的博客，我只是根据这篇博客做了一些删改。

多表左连接查询例子

查询支付单中的待确认金额不等于确认收货总金额加上未确认收货退款的总金额的记录。

支付单中的待确认金额 = 确认收货总金额 + 未确认收货退款的总金额

SELECT payorder.userId, payorder.payrequestid, payorder.toBeConfirmedAmount, payorder.orderamount
	, coalesce(totalRefundAmount, 0) AS totalRefundAmount
	, coalesce(totalConfirmedAmount, 0) AS totalConfirmedAmount
	, payorder.created,payorder.paystatus
FROM dwd_loan_payorder payorder
	LEFT JOIN (
		SELECT SUM(amount) AS totalRefundAmount, payrequestid
		FROM dwd_loan_refundorder
		WHERE isconfirmed = 'false'
		GROUP BY payrequestid
	) refundorder
	ON payorder.payrequestid = refundorder.payrequestid
	LEFT JOIN (
		SELECT SUM(orderAmount) AS totalConfirmedAmount, payrequestid
		FROM dwd_loan_confirmorder
		GROUP BY payrequestid
	) confirmorder
	ON payorder.payrequestid = confirmorder.payrequestid
WHERE payorder.paystatus = '4'
	AND payorder.orderamount <>	(cast(payorder.toBeConfirmedAmount as bigint) + cast(coalesce(totalRefundAmount, 0) as bigint) + cast(coalesce(totalConfirmedAmount, 0)  as bigint))
ORDER BY payorder.id DESC
LIMIT 100000;

9. 计算表达式中不能有为 null 的字段

null 不能进行算术运算，所有有 null 参与的运算结果都为 null。

例 1：

hive> select null+2;
OK
NULL

例 2：null=0，null!=0这类结果都为null，做判断条件时等效为false

hive> select case when null=0 then 1 else 0 end;
OK
0

hive> select null!=1;
OK
NULL

解决方案

hive ：
实际应用中可用 nvl(字段名，值)，如 nvl(a,0) 设置 null 的默认值，避免查询出错。

hive> select nvl(null,0) + 2;
OK
2

presto：
实际应用中可用 nvl(字段名，值)，如nvl(a,0) 设置 null 的默认值，避免查询出错。

presto> select coalesce(null,0) + 2;
OK
2

参考链接：Hive 中 null 的处理

10. 解析 json

hive ：

select get_json_object(payorder.reserved, '$.confirmAmount') as confirmAmount
from dwd_pay_payorder as payorder

presto：

SELECT b.subOrderType, a.id
FROM dwd_paymentorder a
	LEFT JOIN (
		SELECT id, CAST(json_extract_scalar(reserve, '$.subOrderType') AS int) AS subOrderType
		FROM dwd_paymentorder
	) b
	ON a.id = b.id
WHERE 
	b.subOrderType = 1 
	AND substr(created, 1, 10) > '2019-07-02'
ORDER BY created DESC
LIMIT 100;

PS：payorder.reserved 这个字段的值为 json 串。

参考链接：Hive 解析 json

11. 字段类型转换

hive 和 presto 中都提供了 cast 函数来用于字段类型转换。定义如下：

cast(value AS type) → type
显式转换一个值的类型。 可以将varchar类型的值转为数字类型，反过来转换也可以。

try_cast(value AS type) → type
与cast类似，不过，如果转换失败会返回null，这个只有presto有

另外需要注意的是： hive中的 int 类型是就是 int，而 presto 中是包装类型 Integer；hive中的 string 类型是就是 string，而 presto 中是 varchar(n)；如果 cast 的 type 写错也会报错。

varchar(n) 中的 n 可以根据字符串具体的长度自己设置，比如：varchar(100) 或 varchar(200) 或 varchar(500) 等等。

具体实战

payorder.id 字段 bigint 类型 转 字符串类型

hive ：

select cast(payorder.id AS string) from dwd_pay_payorder as payorder

presto：

select cast(payorder.id AS varchar(100)) from dwd_pay_payorder as payorder

注意点

有一个需求需要统计某个时间小于100000s的所有记录，这个时间存在一个map<string,string>中，然后自然想到的就是where map[“stat_time”] <100000 ，结果出来的数据特别少，开始还天真的以为是数据的确就特别少。后来产品说不正常。仔细排查以后发现，这些数据都是小于10的。

相信看到这里就已经比较清晰了，这presto种字符串和数字比较，是把数字转化成字符串进行比较，也就是"10000" 和 23比，“10000” 小，由于hive和很多语言以及框架上，这种情况都是把字符串转化成数字，就没做特殊处理。结果遇到了这个坑。后来把查询引擎缓存hive就好了。

参考链接：https://cloud.tencent.com/developer/article/1336156

12. 获取 group by 后 id 最小的记录

dw_useridentity 表记录：

iduseIdidentityidstatus
152100000111111
252100000111121
35210000222221
45210000333331
55210000444440

我现在想取同 userId 下 id 最小的那条用户实名关联记录。sql 如下：

SELECT  t1.identityid, t1.userid
FROM dw_useridentity as t1
INNER JOIN
(
    SELECT userid, min(id) AS min_id
    FROM dw_useridentity
    where status = 1
    GROUP BY userid
) as t2
ON t1.userid = t2.userid AND t1.id = t2.min_id

13. hive 差集运算

hive 差集运算

14. hive 分区表

如果查询的是分区 hive 表，必须在 sql where 条件中加入分区字段。

15. 多表 join 时过滤字段值是 NULL 的记录

例子：

select userid, amount from
(select transid,amount from dwd_recon_outbill 
where partnerName in('alipay')) as a 
left join (select userid,transid from dwd_payorder) as b 
on a.transid = b.transid

问题：过滤 userid是NULL的记录。

解决方案：

  1. left join 改为 join
  2. 在外面嵌套一层，where userid is not null
延迟满足
关注
  • 1
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
java操作hive_java操作hive
weixin_32211739的博客
02-20 2649
存储类:package com.rxzx.hive;public class Beacon {private String stime;private String uid;private String sid;private String pid;public String getStime() {return stime;}public void setStime(String stime) ...
【大数据】Java同学入门Hive编程 —— 简介和入门操作
陌北有棵树的博客
08-23 601
【一】简介 承接上文《Java后端同学入门Spark编程》，对于日常的数据需求来说，熟悉Hive是一项必备的技能，因为很多日常的数据导出是不需要Spark任务的，跑一个HiveQL就可以完成，但秉承着知其然还要知其所以然的目的，我们首先来了解一下Hive相关的知识，然后熟悉一些日常HiveQL中可能用到的函数。 Hive用来作为原始数据和转换后数据的存储，简化ETL。 首先给出官方对于Hive的定...
Hive JavaApi调用
qq_56520755的博客
05-08 1241
再启动hive集群hive。
Hive的安装和使用以及Java操作hive
CSDN专家-微编程的博客
12-20 9266
Hive 引言 简介 hive是facebook开源，并捐献给了apache组织，作为apache组织的顶级项目(hive.apache.org)。 hive是一个基于大数据技术的数据仓库(DataWareHouse)技术，主要是通过将用户书写的SQL语句翻译成MapReduce代码，然后发布任务给MR框架执行，完成SQL 到 MapReduce的转换。可以将结构化的数据文件映射为一张数据库表，并提供类SQL查询功能。 总结 Hive是一个数据仓库 Hive构建在HDFS上，可以存储海量数据。 Hive
java操作hive数据库，建表
最新发布
qqnbsp的博客
11-02 554
kerberos登陆的工具类，在hive hdfs等认证均可使用。
java大数据面试真题含答案.rar
03-31
理解这些概念并能熟练运用到实际项目中是Java开发的基础。 2. **大数据处理框架**：Hadoop和Spark是最常被提及的大数据处理框架。Hadoop的核心组件包括HDFS（分布式文件系统）和MapReduce（并行计算模型），而Spark...
在Hadoop Map-Reduce中实施联接
04-04
联接在数据库中是常见的操作，但在分布式环境下如Hadoop中，其实现方式有所不同。 MapReduce的工作流程分为两个主要阶段：Map阶段和Reduce阶段。在Map阶段，原始数据被分割成多个块，并在各个节点上并行处理。...
大数据课程-Hadoop集群程序设计与开发-10.Sqoop数据迁移_lk_edit.pptx
01-01
Sqoop是一款由Apache开发的开源软件，专门用于高效地在HDFS或Hive与MySQL、Oracle等传统RDBMS之间转移数据。 ### 10.1 Sqoop概述 Sqoop是一个用于Hadoop和关系数据库间数据传输的工具。它允许用户将数据从RDBMS...
大数据实训方案.pdf
10-31
1. Linux入门：包括Linux简介、安装、常用命令、文件权限管理、用户和权限设置以及Java开发环境的搭建。 2. Hadoop基础：涵盖Hadoop介绍、安装配置、HDFS原理及操作，以及MapReduce编程。 3. Hive和ZooKeeper：学习...
SpringBoot示例代码
04-24
SpringBoot是Java开发领域中的一款热门框架，由Pivotal团队维护，旨在简化Spring应用的初始搭建以及开发过程。它集成了大量常用的第三方库配置，如数据源、JPA、MVC、security、actuator等，让开发者可以快速地创建...
13、java api访问hive操作示例
热门推荐
alanchanchn的专栏
06-08 4万+
本文依赖hive环境可用，特别是HiveServer2。本文仅仅介绍通过java api访问hive的数据。
12_hadoop三大发行版本
withlw
12-09 336
1、hadoop三大发行版本 Hadoop三大发行版本：Apache、Cloudera、Hortonworks。 Apache版本最原始（最基础）的版本，对于入门学习最好。 Cloudera在大型互联网企业中用的较多。 Hortonworks文档较好。 Apache Hadoop 官网地址：http://hadoop.apache.org/releases.html 下载地址：https://archive.apache.org/dist/hadoop/common/ Cloudera Hadoop 官网
伪分布式下hive配置并用beeline远程连接
weixin_43532528的博客
11-15 666
搭建hive用的是hive3.2.1，hadoop3.2.1，所用的环境是操作系统 Linux Ubuntu16.04(64位)，需要的安装包jdk-8u211-linux-x64-tar.gz Hadoop-3.1.2.tar.gz apache-hive-3.1.2-bin.tar.gz)， mysql（5.7.27，系统里自带有MySQL的安装包，不用自己下载） 将jdk，hadoop，...
JAVA如何连接Hive
非著名程序猿
04-25 5691
pom.xml <!-- https://mvnrepository.com/artifact/org.apache.hadoop/hadoop-common --> <dependency> <groupId>org.apache.hadoop</groupId> <artifactId>hadoop-common</artifactId> <version>2.7.3</version&.
使用java连接hive，并执行hive语句详解
liuxiaochen123的专栏
09-24 1万+
安装hadoop 和 hive我就不多说了，网上太多文章 自己看去 首先，在机器上打开hiveservice hive --service hiveserver -p 50000 & 打开50000端口，然后java就可以使用java连了，需要的jar包我发个图片 就这多jar包，必须的 不多说，直接上代码 package asia.wildfire.hiv
Java 连接 Hive
Allen 的技术专栏
04-27 2449
Hive具有一个可选的组件叫做HiveServer后者HiveThrift,其允许通过指定端口访问Hive.访问Hive常用访问方式是通过CLI进行访问。不过，CLI设计使其不方便通过编程的方式访问。CLI是胖客户端。Hive提供了jdbc驱动，使得我们可以用Java代码来连接Hive并进行一些类关系型数据库的sql语句查询等操作。同关系型数据库一样，我们也需要将Hive的服务打开；在Hive
java版the hive_1、Java通过JDBC操作Hive
weixin_26886855的博客
02-26 288
0、概述使用的都是CLI或者hive –e的方式仅允许使用HiveQL执行查询、更新等操作。然而Hive也提供客户端的实现，通过HiveServer或者HiveServer2，客户端可以在不启动CLI的情况下对Hive中的数据进行操作，两者都允许远程客户端使用多种编程语言如Java、Python向Hive提交请求，取回结果。HiveServer与HiveServer2的异同？HiveServer和...
java连hive数据库
Werkple的博客
04-20 813
数据库的驱动 连接的是何种数据库。//加载hive驱动。
关于Java连接Hive，Spark等服务的Kerberos工具类封装
m0_46168848的博客
03-04 1386
关于Java连接Hive，Spark等服务的Kerberos工具类封装
编写Java 在hive udf函数中执行hivesql
06-06
可以使用Hive JDBC驱动程序来在Hive UDF函数中执行Hive SQL语句。具体步骤如下： 1. 在Hive UDF函数中获取Hive JDBC驱动程序的连接。 ``` Connection conn = DriverManager.getConnection("jdbc:hive2://localhost:10000/default", "", ""); ``` 2. 创建一个Statement对象并执行Hive SQL语句。 ``` Statement stmt = conn.createStatement(); stmt.execute("SELECT * FROM table_name"); ``` 3. 处理查询结果。 ``` ResultSet rs = stmt.getResultSet(); while (rs.next()) { // 处理查询结果 } ``` 完整的示例代码如下： ``` import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class HiveUDF { public static void main(String[] args) throws Exception { // 获取Hive JDBC驱动程序的连接 Connection conn = DriverManager.getConnection("jdbc:hive2://localhost:10000/default", "", ""); // 创建一个Statement对象并执行Hive SQL语句 Statement stmt = conn.createStatement(); stmt.execute("SELECT * FROM table_name"); // 处理查询结果 ResultSet rs = stmt.getResultSet(); while (rs.next()) { // 处理查询结果 } // 关闭连接 rs.close(); stmt.close(); conn.close(); } } ``` 在Hive UDF函数中使用以上代码即可执行Hive SQL语句。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值