大数据测试hue_hive的sql编写案例

软件测试微课堂
87 阅读5分钟

案例一分析

需要用sql实现一个逻辑，大致的逻辑内容如下：

从用户表中取出userid传入,从激活表fin_tmp.lysactivate中选取客户的最新的（create_time）额度estimate_amount,激活状态status in（4,5），该额度即为客户当前最新的额度。 激活表fin_tmp.lysactivate查无数据，返回默认值； 有数据但未激活成功记录（status in（4,5）），返回-99998； 若estimate_amount全为空，返回默认值

表1，用户表部分数据如下：

iduserid
111
222
333
444
555
666
777
888
999
表2，激活表部分数据如下：
iduseridestimate_amountstatuscreate_time
:----::----::----::----::----
1112562512020-03-25
22225542020-02-25
3332522552020-01-25
4442592542020-01-15
53552020-01-14
93522362522020-01-13
10552892532020-01-26
112312569532020-01-27
12552562052020-01-28
136312563242020-02-01
15552892542020-01-26

我们知道大数据测试的数据倾向于庞大型，无法一一的去核对表1的用户是否都在表2中，在sql逻辑编写的时候就需要考虑到各种各样的情况，比如从业务上来说表2中会存在多条激活信息，同时表1中的用户可能在表2中没有激活信息。 在这个逻辑中最大的难点在于，通过激活状态对整个的数据做了分离。 接下来一步步分析，对逻辑进行解读：

1、userid传入

那么这句话的内在含义就是，我得计算所有的用户数据，因为表1存在多个userid。查询时需要考虑到连接，由于我的主体是用户表的userid，那么连接时得以表1为主，即左连接时表1在左，右连接时表1在右。

2、客户的最新的（create_time）额度estimate_amount

关联到了激活表，在激活表中存在重复数据以及表1中不对应的userid激活数据，从最开始我们知道表一为主体，那么激活表中不存在于表1（用户表）的userid，已经在连接时给排除了。接下来是要处理最新和激活信息重复的问题，这个时候就需要使用到hive工具的分析函数row_number ( ) over ( PARTITION BY user_id ORDER BY c.create_time ASC )详细的使用方法看链接：链接

3、激活状态status in（4,5）

如果没有激活状态作为数据的分离条件，那么这个sql将会是很简单的，现在出现这个条件导致之前简单的加个where status in（4,5） 方法不可行了。为什么不可行？因为row_number ( )按时间对数据进行标记，这就会出现这么一种情况，每个分组的userid里面多条激活信息，但是最新的那条激活信息状态status not in（4,5）,当我们where status in（4,5）时这个userid的结果为null了，与实际不符，这个时候我们需要做的是，重新修改sql的写法。将激活表分为两张表，一张表包含status in（4,5）另外一张表status not in（4,5），查询时只对status in（4,5）的这张表进行标记，以此表为主体进行左连接形成新的激活表。

案例一解决方法

由以上的具体分析，接下来就开始我们的sql解决

SELECT
	re.user_id,
CASE
		
		WHEN max( re.user_id_c ) IS NULL THEN
		- 99999 
		WHEN max( re.status_flag ) = 0 THEN
		- 99998 ELSE max( re.estimate_amount ) 
	END AS app_num_key_qq 
FROM
	(
	SELECT
		u.user_id,
		c.user_id AS user_id_c,
	IF
		( c.status IN ( '4', '5', '6', '7' ), 1, 0 ) AS status_flag,
		t.estimate_amount 
	FROM
		fin_tmp.loan_user u
		LEFT JOIN fin_tmp.lysactivate c ON u.user_id = c.user_id
		LEFT JOIN (
		SELECT
			u.user_id,
			c.estimate_amount,
			row_number ( ) over ( PARTITION BY u.user_id ORDER BY c.create_time ASC ) AS rn 
		FROM
			fin_tmp.loan_user u
			LEFT JOIN fin_tmp.lysactivate c ON u.user_id = c.user_id 
			AND c.status IN ( '4', '5', '6', '7' ) 
		) t ON u.user_id = t.user_id 
		AND rn = 1 
	) re 
GROUP BY
	re.user_id;

案例二分析

大数据测试的时候难免会用到造一些假数据用于测试的需求，在hive里造数据，在生成中文伪数据时，存放在表中的值乱码了。这是因为Hive将按照UTF8编码格式对数据文件进行解析和查询。如果数据文件不是UTF8，则会显示乱码。其实这里面有两个方法可以解决此问题，第一种就是通过导出表中已有的数据形成文件，然后修改文件的内容，最后进行加载数据；第二种就是表中没有历史数据，需要手动插入一条数据，这个时候就需要先重新编码为utf-8然后插入到表中。

案例二解决方法

文件导入的方法

hive -e "CREATE EXTERNAL TABLE dt.test_test (
  idca string,
  son string ,
  decilma string ,
  type string 
)
PARTITIONED BY (ymd string)
ROW FORMAT DELIMITED  
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\n';"
#导出表中数据
hive -e "select * from dt.表" > /tmp/test.txt
# 在导出的txt文件中修改内容，然后再导入数据
1、load data local inpath ‘/tmp/test/test.txt’ overwrite into table dt.test_test PARTITION (ymd=“2018-12-19”);
2、load data inpath ‘yangcong/res.txt’ overwrite into table temp.test_test PARTITION (ymd=“2018-12-19”);

直接插入数据的方法 注意insert into后不要用values，而使用select，因为values不支持使用decode等函数

 insert into temp.cly_user_picture select decode(binary('贷款页_'),'utf-8'),decode(binary('贷'),'utf-8'),decode(binary('理财'),'utf-8')

 insert into temp.cly_user_picture select decode(binary('首页_低息贷_'),'utf-8'),decode(binary('借'),'utf-8'),decode(binary('聚财'),'utf-8')
avatar
文章
阅读
粉丝