CN109918393A - 物联网的数据平台及其数据查询和多表联合查询方法 - Google Patents

Abstract

本发明公开了一种物联网的数据平台及其数据查询和多表联合查询方法，所述物联网的数据平台采用HBase数据库进行数据的存储，并且在HBase数据库的集群里集成Phoenix的交互引擎。一种所述的物联网的数据平台的多表联合查询方法，包括步骤：S1，基于datax，将待联合查询的N个数据表的数据从MYSQL数据库的导入到Phoenix中，再进行SQL查询；其中，N≤3，并且每个数据表的数据的数量在十万以内。本发明具有存储量大、数据查询速度快等优点，可广泛应用于物联网平台的大数据相关技术领域。

Description

物联网的数据平台及其数据查询和多表联合查询方法

技术领域

本发明涉及物联网平台的大数据相关技术领域。更具体地说，本发明涉及物联网的数据平台及其数据查询和多表联合查询方法。

背景技术

随着基于LPWAN物联网的蓬勃发展，大量的终端设备能够快速接入网络并汇聚在一起，随着终端设备的不断增多，每天产生海量的日志数据信息给平台系统的存储和查询都带来了巨大的压力。物联网符合并发量大，数据上传频率高，写多读少的高吞吐型业务场景，对查询要求也接近准实时，随着海量数据的线性增长，原有的关系数据库已经不能满足我们的业务需要，尤其数亿量级下的分页查询和海量数据的多表联合查询的场景下，关系数据库的读取相应延迟已经达到了数分钟级，完全不能满足需要。

发明内容

本发明的目的是提供一种存储量大、数据查询速度快，能进行多表联合查询的物联网的数据平台及其数据查询和多表联合查询方法。

为了实现根据本发明的这些目的和其它优点，提供了一种物联网的数据平台，采用HBase数据库进行数据存储，并且在HBase数据库的集群里集成Phoenix查询引擎。

一种所述的物联网的数据平台的数据查询方法，通过Phoenix查询引擎使用SQL在HBase数据库进行数据的查询。

一种所述的物联网的数据平台的多表联合查询方法包括步骤：

S1，基于datax，将待联合查询的N个数据表的数据从原储存的MYSQL数据库的导入到Phoenix查询引擎的数据库中，再进行SQL查询；

其中，N≤3，并且每个数据表的数据的数量在十万以内。

优选地，所述步骤S1具体包括：

B1，针对MYSQL数据库中的所有的数据表，穷举其中任意N个数据表之间联合的所有情况，并确定每种情况下N个数据表联合所对应的SQL查询语句，并在Phoenix查询引擎的数据库中创建相应的表结构；

B2，编写步骤B1中每种情况的job脚本；

B3，datax执行步骤B2得到的job脚本，将步骤B1中每种情况下对应的N个数据表、相应SQL查询语句以及表结构从原储存的MYSQL数据库导入到Phoenix查询引擎的数据库中；

B4，根据用户输入SQL查询语句，在Phoenix查询引擎的数据库中进行SQL查询。

优选地，所述datax包括读取模块和写入模块；

步骤B3，具体为：

读取模块连接远程MYSQL数据库，并执行步骤B2得到的job脚本，从而将步骤B1中每种情况下对应的N个数据表、相应SQL查询语句以及表结构从MYSQL数据库中抽选出来，并将该job脚本的执行返回结果拼装为抽象的数据集，并传递给下游的写入模块处理；

写入模块提供单间的SQL表的数据导入方式接收上述读取模块传递的抽象的数据集，通过Phoenix查询引擎的JDBC驱动，将该抽象的数据插入到Phoenix查询引擎的数据库中。

本发明至少包括以下有益效果：

针对海量数据存储和查询的问题，我们提供了一种物联网的数据平台，采用HBase数据库进行数据的存储，并且在HBase数据库的集群里集成Phoenix的交互引擎。针对海量数据的多表联合查询，我们提供了把MYSQL数据库中的数据表的数据同步到Phoenix中，再进行多表联合查询的方案。

本发明的其它优点、目标和特征将部分通过下面的说明体现，部分还将通过对本发明的研究和实践而为本领域的技术人员所理解。

具体实施方式

下面结合实施例对本发明做进一步的详细说明，以令本领域技术人员参照说明书文字能够据以实施。

需要说明的是，下述实施方案中所述实验方法，如无特殊说明，均为常规方法，所述试剂和材料，如无特殊说明，均可从商业途径获得；在本发明的描述中，术语“横向”、“纵向”、“上”、“下”、“前”、“后”、“左”、“右”、“竖直”、“水平”、“顶”、“底”、“内”、“外”等指示的方位或位置关系为基于实施例所示的方位或位置关系，仅是为了便于描述本发明和简化描述，并不是指示或暗示所指的装置或元件必须具有特定的方位、以特定的方位构造和操作，因此不能理解为对本发明的限制。

本发明提供一种物联网的数据平台，采用HBase数据库进行数据存储，并且在HBase数据库的集群里集成Phoenix查询引擎。

在该种技术方案中，考虑到支持大量IoT数据的不间断写入，至少能存放百T以上的数据，随着数据规模的增长，能够方便的进行垂直和水平扩展。我们采用采用HBase数据库进行数据的存储，进行海量数据的存储和查询，HBASE是开源，分布式，可横向扩充的，一致的，低时延的，随机访问的非关系型数据库，HBASE具备随即读写功能，是一种面向列的数据库，HBASE按列存储数据，方便做数据压缩，对某一列或者某几列的查询有非常大的I/O优势，查找速度快，可扩展性强，更容易进行分布式扩展，HBASE还具有低时延随机访问的特性。Phoenix查询引擎为交互式查询引擎。

(1)HBASE写操作：1-3ms，每个节点每秒1000-10000个写操作。

(2)HBASE读操作：内存读0-3ms，硬盘读10-30ms，从内存读每个节点每秒10000-40000个读操作。

(3)在表的任何位置都可以读，写或者插入数据。

(4)没有顺序写的限制。

一种所述的物联网的数据平台的数据查询方法，通过Phoenix查询引擎使用SQL在HBase数据库进行数据的查询。

在该种技术方案中，Phoenix查询引擎支持使用SQL进行HBase数据的查询，会将SQL查询转换为一个或多个HBase API，协同处理器与自定义过滤器的实现，并编排执行。使用Phoenix进行简单查询，其性能量级是毫秒。

考虑支持基于时间戳的历史数据查询，响应时间至少达到秒级，后期根据业务需要，支持对一些关键字段进行索引，以满足某些查询场景。HBase是基于磁盘的NoSql数据库，因为采用了LSM的数据结构，随机写效率较高，特别物联网的数据上传特点，在基于Rowkey方面的查询延时接近准实时。但是因为原生HBase的查询方式比较底层，没有SQL查询接口，对于使用者要求较高，而且没有二级索引，如果不是基于Rowkey查询，查询效率会急剧下降，为了减少客户的使用难度，采用在HBase集群里集成了Phoenix的交互引擎。Phoenix主要给HBase增加了SQL查询以及二级索引支持，大大降低了二次开发难度，也进一步丰富了对多种查询场景的支持。

一种所述的物联网的数据平台的多表联合查询方法包括步骤：

S1，基于datax，将待联合查询的N个数据表的数据从原储存的MYSQL数据库的导入到Phoenix查询引擎的数据库中，再进行SQL查询；

其中，N≤3，并且每个数据表的数据的数量在十万以内。

在该种技术方案中，datax是开源的离线数据同步工具/平台，实现包括MySQL、SQLServer、Oracle、PostgreSQL、HDFS、Hive、HBase、Phoenix、OTS、ODPS等各种异构数据源之间高效的数据同步功能。datax本身作为数据同步框架，将不同数据源的同步抽象为从源头数据源读取数据的Reader插件，以及向目标端写入数据的Writer插件，datax框架可以支持任意数据源类型的数据同步工作。同时datax插件体系作为一套生态系统，每接入一套新数据源该新加入的数据源即可实现和现有的数据源互通。

该技术方案的环境配置为：

·Linux

·JDK(1.8以上，推荐1.8)

·Python(推荐Python2.6.X)

·Apache Maven 3.x(Compile DataX)。

在另一种技术方案中，所述步骤S1具体包括：

B1，针对MYSQL数据库中的所有的数据表，穷举其中任意N个数据表之间联合的所有情况，并确定每种情况下N个数据表联合所对应的SQL查询语句，并在Phoenix查询引擎的数据库中创建相应的表结构；

B2，编写步骤B1中每种情况的job脚本；

B3，datax执行步骤B2得到的job脚本，将步骤B1中每种情况下对应的N个数据表、相应SQL查询语句以及表结构从原储存的MYSQL数据库导入到Phoenix查询引擎的数据库中；

B4，根据用户输入SQL查询语句，在Phoenix查询引擎的数据库中进行SQL查询。在另一种技术方案中，所述datax包括读取模块和写入模块；

步骤B3，具体为：

读取模块连接远程MYSQL数据库，并执行步骤B2得到的job脚本，从而将步骤B1中每种情况下对应的N个数据表、相应SQL查询语句以及表结构从MYSQL数据库中抽选出来，并将该job脚本的执行返回结果拼装为抽象的数据集，并传递给下游的写入模块处理；

写入模块提供单间的SQL表的数据导入方式接收上述读取模块传递的抽象的数据集，通过Phoenix查询引擎的JDBC驱动，将该抽象的数据插入到Phoenix查询引擎的数据库中。

在该种技术方案中，从MYSQL数据库导入到Phoenix，需要配置读入，需要配置导出MysqlReader插件和导入HBase11xsqlwriter插件。

MysqlReader插件实现了从MYSQL数据库读取数据。在底层实现上，MysqlReader通过JDBC连接远程MYSQL数据库，并执行相应的sql语句将数据从MYSQL数据库中SELECT出来。

HBase11xsqlwriter实现了向hbase中的SQL表(Phoenix)批量导入数据的功能。Phoenix因为对rowkey做了数据编码，所以，直接使用HBaseAPI进行写入会面临手工数据转换的问题，麻烦且易错。本插件提供了单间的SQL表的数据导入方式。在底层实现上，通过Phoenix的JDBC驱动，执行UPSERT语句向hbase写入数据。

实施例一：

我们以push_data表为例，进行详细的描述以及多表查询的实现过程。

1.先设计基于HBASE+PHOENIX的数据表，以及相关的索引表。设计表的时候要设计相关加盐表，并且需要注意压缩方式，以及配置相关加盐桶的数量，桶跟容量没关系的，主要解决热点问题，桶的数量一般建议是hbase Region server的1到2倍。Region server就是购买的机器上每个core节点有一个。Phoenix表中加盐是指对pk对应的byte数组插入特定的byte数据。加盐能解决HBASE读写热点问题，例如:单调递增rowkey数据的持续写入，使得负载集中在某一个RegionServer上引起的热点问题。

2.加盐表的设计，在创建表的时候指定属性值：SALT_BUCKETS，其值表示所分buckets(region)数量，范围是1～256。加盐的过程就是在原来key的基础上增加一个byte作为前缀,计算公式如下：

new_row_key＝(++index％BUCKETS_NUMBER)+original_key

当可用block cache的大小小于表数据大小时，较优的slated bucket是和regionserver数量相同，这样可以得到更好的读写性能。当表的数量很大时，基本上会忽略blcokcache的优化收益，大部分数据仍然需要走磁盘IO。比如对于10个region server集群的大表，可以考虑设计64～128个slat buckets。加盐时需要注意：创建加盐表时不能再指定split key。加盐属性不等同于split key,一个bucket可以对应多个region。太大的slatedbuckets会减小range查询的灵活性，甚至降低查询性能。创建索引表时，注意对需要进行查询的字段放到索引表中。Push_data表的设计如下：

3.基于HBASE+PHOENIX的表，数据的新增，修改，查询和删除的代码大数据平台的实现。提供基于springboot+mybatis的轻量级的，前后端分离的Java后台开发平台。支持MySQL、Hbase等主流数据库,支持多数据源。实现前后端分离，通过token进行数据交互，灵活的权限控制，可控制到API接口权限需求，提供API模板，根据token作为登录令牌，极大的方便了APP接口开发提供Hibernate Validator校验框架，轻松实现后端校验，并引入swagger文档支持，方便编写API接口文档。

技术选型如下：

-核心框架：Spring Boot 2.0

-安全框架：Apache Shiro 1.4

-视图框架：Spring MVC4.3

-持久层框架：MyBatis 3.3

-数据库连接池：Druid 1.0

-日志管理：SLF4J 1.7、Log4j

-页面交互：Vue2.x

-翻页插件：pagehelper 1.2.10

-Hbase数据库：阿里云HBASE 1.1.4

-Phoenix：阿里云Phoenix 4.12.0。

4、多表查询的实现，首先根据SQL查询语句，确认关联的表，在Phoenix中创建相关的表结构，然后编写datax的相关的N个数据表的job脚本，通过LINUX调用datax的程序，执行JOB脚本，实现数据表从MYSQL导入到Phoenix中。然后，再用代码，实现相关的接口进行查询。

4.我们以如下查询为例进行详细介绍：

推送数据查询，多表查询，查询条件包括公司(company_id)、appEui、项目(subnet_id)、devEui、是否发送成功(is_send)、开始时间(create_time)、结束时间(create_time)SELECT

a)首先设计mgr_company，nw_sn_device_info，nw_subnet sub相关的N个数据表结构。并在Phoenix中执行生成相关的表。

b)编写mgr_company，nw_sn_device_info，nw_subnet sub，push_data数据同步脚本。利用datax调用脚本，导入数据。以push_data为例脚本如下：

c)开发接口，实现在Phoenix中多表查询的功能，经过测试，通过limt offet分页，3秒内可以查到结果。可以满足业务的要求。

尽管本发明的实施方案已公开如上，但其并不仅仅限于说明书和实施方式中所列运用，它完全可以被适用于各种适合本发明的领域，对于熟悉本领域的人员而言，可容易地实现另外的修改，因此在不背离权利要求及等同范围所限定的一般概念下，本发明并不限于特定的细节和这里示出与描述的实施例。

Claims (5)

1.一种物联网的数据平台，其特征在于，

采用HBase数据库进行数据存储，并且在HBase数据库的集群里集成Phoenix查询引擎。

2.一种如权利要求1所述的物联网的数据平台的数据查询方法，其特征在于，通过Phoenix查询引擎使用SQL在HBase数据库进行数据的查询。

3.一种如权利要求1所述的物联网的数据平台的多表联合查询方法，其特征在于，包括步骤：

S1，基于datax，将待联合查询的N个数据表的数据从原储存的MYSQL数据库的导入到Phoenix查询引擎的数据库中，再进行SQL查询；

其中，N≤3，并且每个数据表的数据的数量在十万以内。

4.如权利要求3所述的物联网的数据平台的多表联合查询方法，其特征在于，所述步骤S1具体包括：

B1，针对MYSQL数据库中的所有的数据表，穷举其中任意N个数据表之间联合的所有情况，并确定每种情况下N个数据表联合所对应的SQL查询语句，并在Phoenix查询引擎的数据库中创建相应的表结构；

B2，编写步骤B1中每种情况的job脚本；

B3，datax执行步骤B2得到的job脚本，将步骤B1中每种情况下对应的N个数据表、相应SQL查询语句以及表结构从原储存的MYSQL数据库导入到Phoenix查询引擎的数据库中；

B4，根据用户输入SQL查询语句，在Phoenix查询引擎的数据库中进行SQL查询。

5.如权利要求4所述的物联网的数据平台的多表联合查询方法，其特征在于，所述datax包括读取模块和写入模块；

步骤B3，具体为：

读取模块连接远程MYSQL数据库，并执行步骤B2得到的job脚本，从而将步骤B1中每种情况下对应的N个数据表、相应SQL查询语句以及表结构从MYSQL数据库中抽选出来，并将该job脚本的执行返回结果拼装为抽象的数据集，并传递给下游的写入模块处理；

写入模块提供单间的SQL表的数据导入方式接收上述读取模块传递的抽象的数据集，通过Phoenix查询引擎的JDBC驱动，将该抽象的数据插入到Phoenix查询引擎的数据库中。