java实现hbase分页,通过hive实现hbase的分页

hbase通过scan和filter可以实现分页功能，网上已经有很多了。今天要说的是通过hivesql的形势来实现分页。

一，单表分页的步骤和过程

1，根据数据获取第一页的数据

hive> select * from hive_hbase_user where contact['mobile']>0 sort by key desc limit 2;

。。。。。。。。。。。。。省略。。。。。。。。。。。。。。

Stage-Stage-1: Map: 1 Reduce: 1 Cumulative CPU: 7.54 sec HDFS Read: 9329 HDFS Write: 333 SUCCESS

Stage-Stage-2: Map: 1 Reduce: 1 Cumulative CPU: 4.41 sec HDFS Read: 6924 HDFS Write: 294 SUCCESS

Total MapReduce CPU Time Spent: 11 seconds 950 msec

OK

2019-10-15 14:35:13,397 INFO [0f1041a5-c5c9-4b0b-a8bc-aad78f202e61 main] mapred.FileInputFormat: Total input paths to process : 1

1000520191014{"mobile":"333333","tel":"44444"}{"age":"34","sex":"male"}{"password":"111111","username":"tanktest212"}

1000520190926{"mobile":"11111111","tel":"2222222"}{"age":"32","sex":"male"}{"password":"111111","username":"tanktest"}

Time taken: 62.669 seconds, Fetched: 2 row(s) //加了order by超级慢

hive_hbase_user表和下面提到hive_hbase_test表，是在上篇文章中创建的。

2，获取第二页的数据

hive> select * from hive_hbase_user where contact['mobile']>0 and key <1000520190926 sort by key desc limit 2;

。。。。。。。。。。。。。省略。。。。。。。。。。。。。。

MapReduce Jobs Launched:

Stage-Stage-1: Map: 1 Reduce: 1 Cumulative CPU: 7.93 sec HDFS Read: 9702 HDFS Write: 345 SUCCESS

Stage-Stage-2: Map: 1 Reduce: 1 Cumulative CPU: 4.3 sec HDFS Read: 6916 HDFS Write: 306 SUCCESS

Total MapReduce CPU Time Spent: 12 seconds 230 msec

OK

2019-10-15 14:46:27,389 INFO [0f1041a5-c5c9-4b0b-a8bc-aad78f202e61 main] mapred.FileInputFormat: Total input paths to process : 1

1000420190926 {"mobile":"15833345678","tel":"02142345678"} {"age":"37","sex":"male3"} {"password":"pass3","username":"test3"}

1000320190926 {"mobile":"15822345678","tel":"02132345678"} {"age":"36","sex":"male2"} {"password":"pass2","username":"newtest2"}

Time taken: 65.752 seconds, Fetched: 2 row(s)

将上一页中最后一条数据的key，带到第二页的条件中来查询。为什么可以这么做呢，满足二个条件，第一，hbase中的rowkey是唯一的。第二加了order by，所以不会出现，错取的情况。缺点，hive是基于mr，一个字慢。

二，多表分页

1，根据数据获取第一页的数据

hive> select a.key,a.contact['mobile'],b.sex from hive_hbase_user a join hive_hbase_test b on a.key=b.key

> where a.contact['mobile']>0 order by a.key desc limit 2;

2，获取第二页的数据

hive> select a.key,a.contact['mobile'],b.sex from hive_hbase_user a join hive_hbase_test b on a.key=b.key

> where a.contact['mobile']>0 and a.key<1000520190926 order by a.key desc limit 2;