presto java 分页_数据仓库(十四)--java--presto---hive的分页查询

我们在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表格由于数据量大，所以要做分页还是比较麻烦的，所以在程序设计时尽可能的避免的分页场景。