基于phoenix的用户清单查询

说明

在没有分布式NOSQL前，移动、电信用户扣费清单查询、通话清单查询，是一件很头疼的事情。而hbase提供了海量数据的毫秒级查询。可见，hbase是个非常好的实时查询框架，缺点就是查询功能非常薄弱，仅限于通过行键查询，而phoenix是构建于hbase之上的，提供了HBase的sql访问功能，可以使用标准的JDBC API操作去创建表、插入记录、查询数据。因此容易让开发人员上手、简化开发流程。

应用场景

现在有个门户系统，用户量很多，并且每个用户访问页面的行为都会被记录，现在要做用户访问清单查询功能，提供用户查看自己的浏览历史，并且在每个页面的停留时间。

数据准备

--hive中的数据
u1,2016-02-12 10:20:21,page1
u2,2016-02-12 10:23:43,page2
u1,2016-02-12 10:30:21,page2
u1,2016-02-12 11:12:32,page3
u2,2016-02-12 11:12:51,page3
u1,2016-02-12 11:23:56,page4
--原始数据表
CREATE TABLE test.user_his (
userid string,
createtime string,
url string
) ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',';
--数据导入后
select * from test.user_his;
OK
u1      2016-02-12 10:20:21     page1
u2      2016-02-12 10:23:43     page2
u1      2016-02-12 10:30:21     page2
u1      2016-02-12 11:12:32     page3
u2      2016-02-12 11:12:51     page3
u1      2016-02-12 11:23:56     page4
Time taken: 0.042 seconds, Fetched: 6 row(s)

数据转换

基于hive计算每个用户的页面停留时间，并且把数据保存在test.user_his_2，准备导入到phoenix中

--创建表test.user_his_2
CREATE TABLE test.user_his_2 (
userid string,
createtime string,
url string,
period int
) ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',';
--对原始数据进行转换并保存在表test.user_his_2
insert into table test.user_his_2 
select b.userid, b.url, b.createtime,
unix_timestamp(b.endtime,'yyyy-MM-dd HH:mm:ss') - unix_timestamp(b.createtime,'yyyy-MM-dd HH:mm:ss') peroid
from(
select userid,url,createtime,
lead(createtime,1,createtime) over (partition by userid order by createtime) endtime
from test.user_his) b
--表test.user_his_2数据查看
select * from test.user_his_2;
OK
u1      page1   2016-02-12 10:20:21     600
u1      page2   2016-02-12 10:30:21     2531
u1      page3   2016-02-12 11:12:32     684
u1      page4   2016-02-12 11:23:56     0
u2      page2   2016-02-12 10:23:43     2948
u2      page3   2016-02-12 11:12:51     0
Time taken: 0.06 seconds, Fetched: 6 row(s)

导入pheonix步骤

如果直接导入到phoenix的正式查询表，是会覆盖原来的数据，不能做到增量导入，并且导入数据后，相关索引表是无法同步改变的，所以增加一个中间环节，先在phoenix中创建一个与正式表一致的临时表，数据先导入到临时表，然后利用phoenix upsert...select...来增量插入到正式表，也保持了索引表的同步更新。整个流程如下图所示。

导入前准备

--在pheonix中创建用户历史访问查询表
--创建导入临时表
create table tmp.user_his_2 (
userid varchar,
url varchar,
createtime varchar,
period integer
CONSTRAINT pk PRIMARY KEY (userid,createtime)
) 
salt_buckets=3;
--创建正式表，要有住够的预分区，防止数据热点以及region过大，采用压缩方式。
create table test.user_his_2 (
userid varchar,
url varchar,
createtime varchar,
period integer
CONSTRAINT pk PRIMARY KEY (userid,createtime)
) 
salt_buckets=16,
COMPRESSION='GZ';
--创建索引表user_his_2_idx，用来做url的二级索引
create index user_his_2_idx on test.user_his_2(url);

导入pheonix临时表

--这里使用的是phoenix内部提供的bulkload导入功能，主要是通过mapreduce来生成hfile
hadoop jar /home/pub/phoenix/phoenix-4.5.2-HBase-0.98-client.jar org.apache.phoenix.mapreduce.CsvBulkLoadTool --table tmp.user_his_2 --input /user/hive/warehouse/test.db/user_his_2 -d ',' -z xx1,xx2,xx3

临时表导入到正式表中

upsert into test.user_his_2 select * from tmp.user_his_2;

查询

select url,createtime,period from test.user_his_2 where userid = 'u1';
+------------------------------------------+------------------------------------------+-----------------------+
|                   URL                    |                CREATETIME                |                  PERI |
+------------------------------------------+------------------------------------------+-----------------------+
| page3                                    | 2016-02-12 11:12:32                      | 684                   |
| page1                                    | 2016-02-12 10:20:21                      | 600                   |
| page2                                    | 2016-02-12 10:30:21                      | 2531                  |
| page4                                    | 2016-02-12 11:23:56                      | 0                     |
+------------------------------------------+------------------------------------------+-----------------------+