HBase Phoenix助力海量数据实时分析

心已赠人 2021-11-18 00:38 442阅读 0赞

## 前言 ##

phoenix这个项目我一直接触不多，在去年接触的一家公司使用phoenix分析数百亿的记录并且在秒级别返回的延迟时，笔者才慢慢探究一些phoenix的内幕。上个星期跟一位phoenix的PMC&Committer聊了聊phoenix的定位及未来的发展，发现phoenix还是比较竞争力的，从最近phoenix不断的发布版本来看，phoenix也在迅速的发展。在phoenix社区也组织了phoenixCon来宣传phoenix:[PhoenixCon 2017][]。本文的目的是想梳理下phoenix的，phoenix怎么支持海量数据的实时分析，一些细节点不多。

## 架构 ##

**phoenix是专门为HBase增强的，具体说就是HBase有几个方面的问题:**

*  **HBase比较底层，没有SQL接口，对于希望使用SQL的同学转换成本高**
 *  **HBase不支持二级索引(HBase自身也有一些方案，不过还是以phoenix支持最佳)**
 *  **HBase不支持事务**

最近几年，从SQL到NO-SQL，再到NewSQL的兴起。其实NewSQL本身并没有什么大技术的创新点(持久内存等硬件本身除外)，**只是架构的创新或者说是一种混合架构**。Phoenix是不是NewSQL，其实并不重要，重要的是他解决了什么问题。  
大致的phoenix嵌入进HBase的图如下所示：

![image][]

**phoenix最主要给HBase添加了二级索引、SQL的支持**。**至于事物及一些其它的支持，算是锦上添花**，让phoenix越看越是NewSQL。phoenix的官方文档比较全，笔者就不搬了。

*  secondary indexing：[http://phoenix.apache.org/secondary\_indexing.html][http_phoenix.apache.org_secondary_indexing.html]
 *  standard SQL：[https://phoenix.apache.org/language/index.html][https_phoenix.apache.org_language_index.html]

特别地：

*  支持join(hash joinSort-Merge Join)
 *  相关SQL的优化(现在正在对接Calcite：[http://calcite.apache.org/][http_calcite.apache.org] )
 *  支持jdbc直接访问

## 性能 ##

phoenix适合海量数据的实时分析需求：

*  **phoenix基本通过建立索引在海量数据上查询少量数据，并且基本实时返回。**
 *  **phoenix也支持做一些复杂SQL操作，包括join，sub-query等。**
 *  **phoenix不适合于ETL，比如10T数据变为10T数据。**

如下有一张跟Hadoop体系hive的对比：

![image][image 1]

出自：[https://phoenix.apache.org/performance.html][https_phoenix.apache.org_performance.html]

在四个场景中，Hive On HBase最差，phoenix（key filter）最快，**究其原因就是phoenix（key filter）走了index，查询时间基本在1s以内**。另外，Hive on HDFS跟phoenix on HDFS对比，在5000w条数据量左右是分界线，时间在20s左右，其实hive启动时间都在5s左右，再跑一个MR/TEZ作业都在10s左右(后续有一定的优化)。

测试数据：[http://phoenix-bin.github.io/client/performance/phoenix-20160718043634.htm][http_phoenix-bin.github.io_client_performance_phoenix-20160718043634.htm]   
其中包括了主要的一些场景，比如：Aggregation、 Count Distinct、 IN/LIKE Clause、LIMIT，测试数据集合基本是百万级别，时间在数秒左右。  
其实，分析的性能就在于index是否命中，另外，index也会消耗更多的空间,较多的index也会降低写的QPS

## 演示 ##

目前[云HBase][HBase]也已经支持phoenix4.6版本，用户可以直接下载 [http://hbase-cloud2.oss-cn-hangzhou.aliyuncs.com/phoenix-4.6.0-HBase-1.1.tar.gz][http_hbase-cloud2.oss-cn-hangzhou.aliyuncs.com_phoenix-4.6.0-HBase-1.1.tar.gz]   
我们简单演示下join的功能，参考：[https://phoenix.apache.org/joins.html\#joining-tables-with-indices-eg1][https_phoenix.apache.org_joins.html_joining-tables-with-indices-eg1]

CREATE TABLE IF NOT EXISTS Customers( CustomerID VARCHAR NOT NULL, CustomerName VARCHAR, Country VARCHAR CONSTRAINT pk PRIMARY KEY(CustomerID) ); CREATE TABLE IF NOT EXISTS Items( ItemID VARCHAR NOT NULL, ItemName VARCHAR, Price Decimal CONSTRAINT pk PRIMARY KEY(ItemID) ); SELECT O.OrderID, C.CustomerName FROM ORDERS AS O INNER JOIN CUSTOMERS AS C ON O.CustomerID = C.CustomerID; 0: jdbc:phoenix:hb-bp18zlo0b209p7g23-001.hbas> CREATE INDEX iOrders ON Orders (ItemID) INCLUDE (CustomerID, Quantity); 5 rows affected (1.519 seconds) 0: jdbc:phoenix:hb-bp18zlo0b209p7g23-001.hbas> CREATE INDEX i2Orders ON Orders (CustomerID) INCLUDE (ItemID, Quantity); 5 rows affected (1.502 seconds) 0: jdbc:phoenix:hb-bp18zlo0b209p7g23-001.hbas> CREATE INDEX iItems ON Items (ItemName) INCLUDE (Price); 6 rows affected (1.427 seconds) 0: jdbc:phoenix:hb-bp18zlo0b209p7g23-001.hbas> SELECT O.OrderID, C.CustomerName, C.Country, O.Date . . . . . . . . . . . . . . . . . . . . . . .> FROM Orders AS O . . . . . . . . . . . . . . . . . . . . . . .> INNER JOIN Customers AS C . . . . . . . . . . . . . . . . . . . . . . .> ON O.CustomerID = C.CustomerID; +------------------------------------------+------------------------------------------+------------------------------------------+-------------------------+ | O.ORDERID | C.CUSTOMERNAME | C.COUNTRY | O.DATE | +------------------------------------------+------------------------------------------+------------------------------------------+-------------------------+ | 1630781 | Alps Nordic AB | Sweden | 0009-01-22 13:00:00.000 | | 1630782 | Salora Oy | Finland | 0009-02-22 13:00:00.000 | | 1630783 | Logica | Belgium | 0009-03-22 13:00:00.000 | | 1630784 | Alps Nordic AB | Sweden | 0009-04-22 13:00:00.000 | | 1630785 | Deister Electronics | Germany | 0009-05-22 13:00:00.000 | +------------------------------------------+------------------------------------------+------------------------------------------+-------------------------+ 5 rows selected (0.136 seconds) 0: jdbc:phoenix:hb-bp18zlo0b209p7g23-001.hbas> SELECT ItemName, sum(Price * Quantity) AS OrderValue . . . . . . . . . . . . . . . . . . . . . . .> FROM Items . . . . . . . . . . . . . . . . . . . . . . .> JOIN Orders . . . . . . . . . . . . . . . . . . . . . . .> ON Items.ItemID = Orders.ItemID . . . . . . . . . . . . . . . . . . . . . . .> WHERE Orders.CustomerID > 'C002' . . . . . . . . . . . . . . . . . . . . . . .> GROUP BY ItemName; +------------------------------------------+------------------------------------------+ | ITEMNAME | ORDERVALUE | +------------------------------------------+------------------------------------------+ | BX016 | 10374 | | MU3508 | 1.44E+4 | | XT2217 | 46436 | +------------------------------------------+------------------------------------------+ 3 rows selected (0.184 seconds)

较大的数据量：

0: jdbc:phoenix:hb-bp18zlo0b209p7g23-001.hbas> select count(*) from "usertable"; +------------------------------------------+ | COUNT(1) | +------------------------------------------+ | 29643363 | +------------------------------------------+ 1 row selected (38.704 seconds) 0: jdbc:phoenix:hb-bp18zlo0b209p7g23-001.hbas> select count(*) from "usertable" where "cf"."field0" like '9%'; +------------------------------------------+ | COUNT(1) | +------------------------------------------+ | 925932 | +------------------------------------------+ 1 row selected (1.071 seconds) 0: jdbc:phoenix:hb-bp18zlo0b209p7g23-001.hbas> select count(*) from "usertable" where "cf"."field0" like '9C%'; +------------------------------------------+ | COUNT(1) | +------------------------------------------+ | 14248 | +------------------------------------------+ 1 row selected (0.043 seconds) 0: jdbc:phoenix:hb-bp18zlo0b209p7g23-001.hbas> select "cf"."field0","field1" from "usertable" where "field0" like '9Cy/9>%'; +------------------------------------------+------------------------------------------+ | field0 | field1 | +------------------------------------------+------------------------------------------+ | 9Cy/9>2Go.]a'Iy;>.44&09~<Z/=Ye2L3(Cg.Ni&Gc1Ue&@1).n"+8%'f+,.+".%Kc!-.4%b)249(z/3z;6~0N5,( 70h?<t6X}+ | &5j;O!.,& Uw3Qy":"/<*-No>M51Y}<]-18<5 $(Gw5Ig.8p.R7-$<0[5$?&<-f;Yg6$89_c+Ha'9`6Mc2'8 | +------------------------------------------+------------------------------------------+ 1 row selected (0.046 seconds)

## HBase技术交流社区 - 阿里官方“HBase生态+Spark社区大群”点击加入：[https://dwz.cn/Fvqv066s][https_dwz.cn_Fvqv066s] ##

[PhoenixCon 2017]: https://yq.aliyun.com/go/articleRenderRedirect?url=https%3A%2F%2Fwww.eventbrite.com%2Fe%2Fphoenixcon-2017-tickets-32872245772
[image]: /images/20211116/6b670e1063e94b68a79bd2b2fe3de7cc.png
[http_phoenix.apache.org_secondary_indexing.html]: https://yq.aliyun.com/go/articleRenderRedirect?url=http%3A%2F%2Fphoenix.apache.org%2Fsecondary_indexing.html
[https_phoenix.apache.org_language_index.html]: https://yq.aliyun.com/go/articleRenderRedirect?url=https%3A%2F%2Fphoenix.apache.org%2Flanguage%2Findex.html
[http_calcite.apache.org]: https://yq.aliyun.com/go/articleRenderRedirect?url=http%3A%2F%2Fcalcite.apache.org%2F
[image 1]: /images/20211116/677fd8f50cd149fab5563b149e98e256.png
[https_phoenix.apache.org_performance.html]: https://yq.aliyun.com/go/articleRenderRedirect?url=https%3A%2F%2Fphoenix.apache.org%2Fperformance.html
[http_phoenix-bin.github.io_client_performance_phoenix-20160718043634.htm]: https://yq.aliyun.com/go/articleRenderRedirect?url=http%3A%2F%2Fphoenix-bin.github.io%2Fclient%2Fperformance%2Fphoenix-20160718043634.htm
[HBase]: https://yq.aliyun.com/go/articleRenderRedirect?url=https%3A%2F%2Fhbase.console.aliyun.com%2F
[http_hbase-cloud2.oss-cn-hangzhou.aliyuncs.com_phoenix-4.6.0-HBase-1.1.tar.gz]: https://yq.aliyun.com/go/articleRenderRedirect?url=http%3A%2F%2Fhbase-cloud2.oss-cn-hangzhou.aliyuncs.com%2Fphoenix-4.6.0-HBase-1.1.tar.gz
[https_phoenix.apache.org_joins.html_joining-tables-with-indices-eg1]: https://yq.aliyun.com/go/articleRenderRedirect?url=https%3A%2F%2Fphoenix.apache.org%2Fjoins.html%23joining-tables-with-indices-eg1
[https_dwz.cn_Fvqv066s]: https://yq.aliyun.com/go/articleRenderRedirect?url=https%3A%2F%2Fdwz.cn%2FFvqv066s