我们在SpringMVC框架中使用那个presto查询hive的数据做展示时,发现在table里分页是个不可避免的话题。
建议去掉count
但是尴尬的是 对于海量数据 count的效率是很低的,因为在不加条件的情况下基本上要扫描全表。
count操作性能比较收集
oracle里Count一亿条数据 大于10分钟
mysql,3000万行记录,count(*)需要200s
hive+presto count 8499万条记录 需要30s
效率对比如图:
查询准确数据只用了214毫秒,查询数量count却花了39秒的时间,如果我们要先获取准确的数量的话就做不到迅速响应的查询。
所以我们建议在海量数据列表显示时去掉count,默认给比较大的数值,包含所有的数据量。
如果需要准确count的场景,建议增加下拉框选择准确count的模式下再去查询count并说明该模式需要更长的查询时间。
如何分页
在使用hive的sql语句查询时,很悲伤的发现hive的sql没有limit 5,20 的用法,只有limit 20,只能限制条数,不支持直接跳过多少条数据。
还好hive支持使用where id between 10 to 20 或者 时间。
例如
select * from table t1 where t1.DATE_COLUMN between cast(from_unixtime(unix_timestamp(),'yyyy-MM-01 00:00:00') as timestamp) and cast(from_unixtime(unix_timestamp()) as timestamp) ;
limit 与between的区别在于
limit是根据查询出的结果里进行跳过和条数限制。
between则作为查询条件来使用。如果针对全量表 between是比较好进行分页的。 但是对于有查询条件的时候,between就比较复杂了,因为查询条件查出来的数据 列的值的范围是多少 我们并不知道。
所以 需要另外的一种 分页逻辑,数值类型int类型的自增id。
只要id的排序顺序一定,无论我们的查询条件是什么,查询出来的数据都是有序的,而且id有范围。
使用命令如下:
#获取第一页数据:
select * from table where type=2 order by id asc limit 10;
#获取第二页数据:
#需要获取第一页10条中最大的id为preId,作为下一页的条件。
int preId=select max(id) from table where type=2 order by id asc limit 10;
select * from table where type=2 and id >preId order by id asc limit 10;
#获取第三页数据:
#需要获取2页20条中最大的id为preId,作为下一页的条件。
int preId=select max(id) from table where type=2 order by id asc limit 20;
select * from table where type=2 and id >preId order by id asc limit 10;
总结
可以看到第一个limit的数值是跳过的条数,第二个limit是每页的条数。
自增列id生成
在我们设计的hive分页逻辑中,很重要的一个辅助字段就是唯一不重复的自增id。
这个在把数据写入分布式集群时如何生成不重复的自增数值id目前还没有找到很好的方法。
但是 通过hive的row_number()方法可以很好的在新表中统一生成自增列。
使用命令
create table chip_pt(id BIGINT,position string,rs string,linenum string,filename string,createdate string) partitioned by (chromosome string);
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=100000;
set hive.exec.max.dynamic.partitions.pernode.Maximum=100000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=100000;
set mapred.max.split.size=1024000000;
set mapred.min.split.size.per.node=1024000000;
set mapred.min.split.size.per.rack=1024000000;
set mapred.reduce.tasks=100;
set hive.map.aggr=true;
set hive.groupby.skewindata=true;
set hive.enforce.bucketing = true;
insert overwrite table chip_pt
partition(chromosome)
select row_number() over (order by position) as id,position,rs,linenum,filename,createdate,chromosome from chip;
注意id必须使用int类型才能很好的进行排序分页。
当然这种方式针对海量数据来说还是有点麻烦,也就是每次有更新数据都得重新生成一次带有自增列的新表。
除此之外 自己在写入hive时维护自增列也是一种思路,不过比较麻烦而且容易有重复的自增id。
总结
hive表格由于数据量大,所以要做分页还是比较麻烦的,所以在程序设计时尽可能的避免的分页场景。