实时即未来，大数据项目车联网之原始数据车辆指标即席查询（10）

---

theme: smartblue

持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第10天，点击查看活动详情

1. 原始数据车辆指标即席查询

l HBase作为处理实时海量数据的NOSQL数据库应用非常广泛，由于hbase列式存储特征，目前并不能直接使用sql操作hbase数据库，因此，有一种解决方案：phoenix+hbase实现sql操作hbase数据库，这种方案能解决sql操作hbase的原因在于phoenix会创建hbase表的二级索引，通过二级索引操作hbase数据库。

1.1 Apache Phoenix简介

• 官网：http://phoenix.apache.org/language/index.html

l Phoenix是构建在HBase上的一个SQL层，能让我们用标准的JDBC API而不是HBase客户端API来创建表，插入数据和对HBase数据进行查询。Phoenix完全使用Java编写，作为HBase内嵌的JDBC驱动。Phoenix查询引擎会将SQL查询转换为一个或多个HBase扫描，并编排执行以生成标准的JDBC结果集。

• 这里很多人会想Phoenix与HBASE的关系是不是hive与hdfs的关系类似。作以下对比方便大家理解Phoenix。

l sql解决方案对比

hbase的sql解决方案	hivesql方案
phoenix+hbase	hive+hdfs
phoenix是hbase的sql层	hive是hdfs的sql层
可用于实时计算场景，也可以是离线场景	只能用于离线场景
更新快	更新慢

1.2 Phoenix客户端命令操作

Phoenix直接使用HBase API、协同处理器与自定义过滤器，对于简单查询来说，其性能量级是毫秒级，对于百万级别的行数来说，其性能量级是秒级。

l phoenix通过以下方式实现高性能操作hbase：

编译你的SQL查询为原生HBase的Scan语句

检测scan语句最佳的开始和结束的key

精心编排你的scan语句让他们并行执行

推送where子句的位次到服务端过滤器处理

执行聚合查询通过服务端钩子（称为协同处理器）

实现了二级索引来提升非主键字段查询的性能

统计相关数据来提高并行化水平，并帮助选择最佳优化方案

跳过扫描过滤器来优化in、like、or条件查询

优化主键的来均匀分布写压力

• 正是因为Phoenix如此优秀，在企业中应用广泛

• 常用命令以及说明

命令	说明
!table	查看表信息
!describe tablename	查看表字段信息
!history	查看历史执行SQL
!dbinfo	查看数据库信息
!index tb	查看tb的索引信息
!list	查看当前连接数
!quit /!exit	退出Phoenix
!help	帮助命令

• 查看表操作

n TABLE_SCHEMA : 所在的库，对应hbase的namespace

n hbase查看namespace

• 创建表操作

  create table if not exists "user_info"(
      "id" varchar primary key,
      "cf"."name" varchar,
      "cf"."age" integer,
      "cf"."sex" varchar,
      "cf"."address" varchar
  );
  

  

• 新增数据

upsert into "user_info" values('1', '张三', 30, '男', '北京市西城区');
upsert into "user_info" values('2', '李四', 20, '女', '上海市闵行区');
upsert into "user_info" values('3', '王五', 25, '男', '深圳市福田区');
upsert into "user_info" values('4', '刘六', 23, '男', '武汉市汉阳区');
upsert into "user_info" values('5', '田七', 20, '女', '广州市中山区');

• 查询操作

全表查询

select * from "user_info";

统计表的总记录数

select count(1) "totalNum" from "user_info";

统计表按性别分组的总数

select count(1) "totalNum","sex" from "user_info" group by "sex";

• 创建视图

  CREATE VIEW IF NOT EXISTS "user_info_view" AS SELECT * FROM "user_info";
  

  

验证

测试插入一条数据到”user_info”表中，验证视图，有啥变化？

upsert into “user_info” values(‘6’, ‘赵四’, 51, ‘男’, ‘辽宁省开原市松山乡康屯村二组赵四家’);

效果

u 视图也会查看到刚刚插入的数据

删除视图后，对 ”user_info”表，是否有影响？

u 删除视图时，实际的表数据不受影响。但是，该视图的索引数据将被删除。

视图更多信息参考:

http://phoenix.apache.org/views.html

• 更新表操作

更新id为1的张三用户的年龄为28

• upsert into "user_info"("id", "age") values('1', 28); 
  

  删除表操作

删除id为4的记录

delete from “user_info” where “id” = ‘4’;

• 命令更多信息参考:

http://phoenix.apache.org/language/index.html

1.3 构建hbase的二级索引

HBase里面只有rowkey作为一级索引，如果要对库里的非rowkey字段进行数据检索和查询，往往要通过MapReduce/Spark等分布式计算框架进行，硬件资源消耗和时间延迟都会比较高。

为了HBase的数据查询更高效、适应更多的场景， 诸如使用非rowkey字段检索也能做到秒级响应，或者支持各个字段进行模糊查询和多字段组合查询等， 因此需要在HBase上面构建二级索引， 以满足现实中更复杂多样的业务需求。

1.3.1 索引类型

1.3.1.1 Covered Indexes(覆盖索引)

覆盖索引：只需要通过索引就能返回所要查询的数据，所以索引的列必须包含所需查询的列(SELECT的列和WHRER的列)

• 不带索引的查询：

explain select * from “user_info” where “sex”=‘男’;

(由图看知先进行了全表扫描再通过过滤器来筛选出目标数据，显示这种查询方式效率是很低的)

• 带索引：

(创建基于Sex的覆盖索引并绑定name列上的数据)

CREATE INDEX “user_index” ON “user_info” (“cf”.“sex”) include (“cf”.“name”);
当你要通过sex来查询name时就直接可以从索引上取回数据而无需先得到索引再去数据表中查询数据
EXPLAIN SELECT “name” FROM “user_info” WHERE “sex”=‘男’;
（使用了"user_index"索引使用SCAN在索引区间内查询）

注意：SELECT所带的字段必须包含在覆盖索引内

• 删除索引

drop index “user_index” ON “user_info”;

1.3.1.2 Functional indexes(函数索引)

从Phoeinx4.3以上就支持函数索引，其索引不局限于列，可以合适任意的表达式来创建索引，当在查询时用到了这些表达式时就直接返回表达式结果

例2：使用REVERSE函数创建函数索引使查询出的name列反转和sex字段进行拼接

• 创建函数索引

CREATE INDEX “concat_index” ON “user_info” (REVERSE(“cf”.“name”) || ‘-’ || “cf”.“sex”);
EXPLAIN select REVERSE(“name”) || ‘-’ || “sex” from “user_info”;

• 删除索引

drop index “concat_index” ON “user_info”;

1.3.1.3 Global indexes(全局索引)

全局索引适用于多读少写的场景，在写操作上会给性能带来极大的开销，因为所有的更新和写操作（DELETE,UPSERT VALUES和UPSERT SELECT）都会引起索引的更新,在读数据时，Phoenix将通过索引表来达到快速查询的目的。

• 在用使用全局索引之前需要在每个RegionServer上的hbase-site.xml添加如下属性：

<property>
   <name>hbase.regionserver.wal.codec</name>
   <value>org.apache.hadoop.hbase.regionserver.wal.IndexedWALEditCodec</value>
</property>

• 在sex字段上创建索引

CREATE INDEX “sex_index” ON “user_info”(“cf”.“sex”);
explain select “sex” from “user_info” where “sex”=‘男’;

• 以下查询会用到索引列

explain select “sex”,“id” from “user_info” where “sex”=‘男’;

• 以下查询不会用到索引

explain SELECT “sex”,“name” FROM “user_info” WHERE “sex”=‘男’;
(虽然sex是索引字段，但name不是索引字段，所以不会使用到索引)
explain SELECT “name” FROM “user_info” WHERE “sex”=‘男’;
（同理，name不是索引字段）

• 进入hbase的命令行

scan ‘sex_index’

1.以上可以看出global index的设计方式，会单独写一张索引表，列族为include字段，rowkey的设计方位是:
二级索引字段1+"\x00"+二级索引字段2(复合索引)…+"\x00"+原表rowkey
2.查询的时候，会直接定位到索引表，通过rowkey查到位置，然后从索引表中带出数据
3.因为建立索引的时候还要多写一份include字段，读的时候直接从索引表定位并读出信息。所以这种表的应用场景定位是写的慢，读得快

• 删除索引

drop index "sex_index" ON "user_info";

1.3.1.4 Local indexes(本地索引)

***本地索引适用于写多读少，空间有限的场景，***和全局索引一样，Phoneix在查询时会自动选择是否使用本地索引，使用本地索引，为避免进行写操作所带来的网络开销，索引数据和表数据都存放在相同的服务器中，***当查询的字段不完全是索引字段时本地索引也会被使用，***与全局索引不同的是，所有的本地索引都单独存储在同一张共享表中，由于无法预先确定region的位置，所以在读取数据时会检查每个region上的数据因而带来一定性能开销。

• 修改hbase的配置文件：hbase-site.xml

<property>
	<name>hbase.zookeeper.quorum</name>
	<value>node01,node02,node03:2181</value>
</property>

• 创建本地索引

CREATE LOCAL INDEX “sex_local_index” ON “user_info”(“cf”.“sex”);
scan “user_info”

1.以上可以看出local index的设计方式，索引数据直接写在原表rowkey中，列族不写任何实际信息，local index的rowkey的设计方位是:
原数据region的start key+"\x00"+二级索引字段1+"\x00"+二级索引字段2(复合索引)…+"\x00"+原rowkey
第一条信息"原数据region的start key"，这样做的目的是保证索引数据和原数据在一个region上，定位到二级索引后根据原rowkey就可以很快在本region上获取到其它信息，减少网络开销和检索成本。
2.查询的时候，会在不同的region里面分别对二级索引字段定位，查到原rowkey后在本region上获取到其它信息
3.因为这种索引设计方式只写索引数据，省了不少空间的占用，根据索引信息拿到原rowkey后再根据rowkey到原数据里面获取其它信息。所以这种表的应用场景定位是写的快，读得慢

• 删除索引

drop index “sex_local_index” ON “user_info”;

1.3.2 local index和global index比较

• 索引数据

n global index单独把索引数据存到一张表里，保证了原始数据的安全，侵入性小

n local index把索引数据写到原始数据（创建索引的表）里面，侵入性强，原表的数据量=原始数据+索引数据，使原始数据更大

• 性能方面

n global index要多写出来一份数据，写的压力就大一点，但读的速度就非常快

n local index只用写一份索引数据，节省不少空间，但多了一步通过rowkey查找数据，写的速度非常快，读的速度就没有直接取自己的列族数据快。

1.3.3 索引的优化

以下属性都必须在各节点上的hbase-site.xml中设置为true才能起效:

配置项	说明
index.builder.threads.max（默认值：10）	根据主表的更新来确定更新索引表的线程数
index.builder.threads.keepalivetime：（默认值：60）	builder线程池中线程的存活时间
index.write.threads.max:（默认值：10）	更新索引表时所能使用的线程数(即同时能更新多少张索引表)，其数量最好与索引表的数量一致
index.write.threads.keepalivetime（默认值：60）	更新索引表的线程所能存活的时间
hbase.htable.threads.max（默认值：2147483647）	每张索引表所能使用的线程(即在一张索引表中同时可以有多少线程对其进行写入更新)，增加此值可以提高更新索引的并发量
hbase.htable.threads.keepalivetime（默认值：60）	索引表上更新索引的线程的存活时间
index.tablefactoy.cache.size（默认值：10）	允许缓存的索引表的数量增加此值，可以在更新索引表时不用每次都去重复的创建htable，由于是缓存在内存中，所以其值越大，其需要的内存越多

1.4 原始数据hbase表二级索引

原始数据实时ETL创建好表，且表中存储了大量车辆信息数据，为了提高查询的效率和查询维度，通过phoenix建立hbase的二级索引方式实现。

HBase中已存在“itcast src”表，因此我们需要在phoenix中创建对应hbase的表，此表创建好二级索引，我们就可以通过除rowkey外的其他列进行高效的组合查询

l hbase表

• 创建hbase表对应的phoenix表

create table if not exists "itcast_src"(
pk VARCHAR PRIMARY KEY,
"cf"."vin" VARCHAR,
"cf"."terminalTime" VARCHAR,
"cf"."soc" VARCHAR,
"cf"."lat" VARCHAR,
"cf"."lng" VARCHAR,
... ...
"cf"."currentElectricity" VARCHAR,
"cf"."processTime" VARCHAR
)
-- 要让phoenix中建立的表能映射hbase中的表，需要禁用列映射规则（会降低查询性能）
column_encoded_bytes=0;

n phoenix完整建表语句:讲义关联资料\phoenix表itcast_src.md

• 一般情况在phoenix关联hbase表，不使用建表方式，有2种原因：

查询性能低

删除phoenix表，也会把hbase中被映射的表删除(重要，容易造成数据丢失)

• 创建hbase表对应的视图

create view if not exists "itcast_src"(
pk VARCHAR PRIMARY KEY,
"cf"."vin" VARCHAR,
"cf"."terminalTime" VARCHAR,
"cf"."soc" VARCHAR,
"cf"."lat" VARCHAR,
"cf"."lng" VARCHAR,
... ...
"cf"."processTime" VARCHAR
);

n phoenix完整视图创建语句：讲义关联资料\phoenix视图itcast_src.md

查看hbase表信息

n phoenix查看表信息 | 查看表总记录数

查看表数据（10条）

查看索引

建立global index，生产环境数据分布在多个region上,由于创建的视图，数据写入hbase数据的性能已优化，需要查询效率高，因此选择全局索引

create index “itcast_src_index” on “itcast_src”(“cf”.“vin”, “cf”.“terminalTime”, “cf”.“soc”) ;

查看索引（自动添加主键索引）

统计记录数，速度提升41倍

1.5 phoenix使用场景

l phoenix不适合做聚合查询，如：group by、max、count等聚合函数查询，查询效率低，由于hbase为列式存储数据，phoenix建立了与hbase映射，仍然不能提高聚合查询效率，只有行式存储数据库，聚合查询效率才不至于如此低

• 思考：phoenix的适用场景

明细查询，如根据vin查询车辆信息、根据日期查询某一天的车辆信息数

历史数据查询，如根据条件查询历史数据

n sql优化查询，非根据rowkey（hbase中的主键）查询数据

思考：

phoenix只适合做明细查询和历史查询，不适合多维度统计，sql优化查询，如何从hbase原始数据中加载出车辆明细指标数据？