7. 基于Phoenix消息数据查询
7.1 建立视图
应用场景
因为我们之前已经创建了 MOMO_CHAT:MSG 表，而且数据添加的方式都是以PUT方式原生API来添加的。故此时，我们不再需要再使用Phoenix创建新的表，而是使用Phoenix中的视图，通过视图来建立与HBase表之间的映射，从而实现数据快速查询。
视图介绍
我们可以在现有的HBase或Phoenix表上创建一个视图。表、列蔟和列名必须与现有元数据完全匹配，否则会出现异常。当创建视图后，就可以使用SQL查询视图，和操作Table一样。
语法示例：
-- 映射HBase中的表
CREATE VIEW "my_hbase_table"
( k VARCHAR primary key, "v" UNSIGNED_LONG) default_column_family='a';
-- 映射Phoenix中的表
CREATE VIEW my_view ( new_col SMALLINT )
AS SELECT * FROM my_table WHERE k = 100;
-- 映射到一个SQL查询
CREATE VIEW my_view_on_view
AS SELECT * FROM my_view WHERE new_col > 70;
建立MOMO_CHAT:MSG的视图
考虑以下几个问题：
1.视图如何映射到HBase的表？视图的名字必须是：命名空间.表名
2.视图中的列如何映射到HBase的列蔟和列？列名必须是：列蔟.列名
3.视图中的类如何映射到HBase的ROWKEY？指定某个列为primary key，自动映射ROWKEY
参考创建语句：
-- 创建MOMO_CHAT:MSG视图
create view if not exists "MOMO_CHAT". "MSG" (
"pk" varchar primary key, -- 指定ROWKEY映射到主键
"C1"."msg_time" varchar,
"C1"."sender_nickyname" varchar,
"C1"."sender_account" varchar,
"C1"."sender_sex" varchar,
"C1"."sender_ip" varchar,
"C1"."sender_os" varchar,
"C1"."sender_phone_type" varchar,
"C1"."sender_network" varchar,
"C1"."sender_gps" varchar,
"C1"."receiver_nickyname" varchar,
"C1"."receiver_ip" varchar,
"C1"."receiver_account" varchar,
"C1"."receiver_os" varchar,
"C1"."receiver_phone_type" varchar,
"C1"."receiver_network" varchar,
"C1"."receiver_gps" varchar,
"C1"."receiver_sex" varchar,
"C1"."msg_type" varchar,
"C1"."distance" varchar,
"C1"."message" varchar
);
尝试查询一条数据
SELECT * FROM "MOMO_CHAT"."MSG" LIMIT 1;
如果发现数据能够正常展示，说明视图映射已经成功。
注意：因为列名中有小写，需要用引号将字段名包含起来
7.2 开发基于SQL查询数据接口
使用SQL语句查询数据
- 需求
根据日期、发送人账号、接收人账号查询历史消息 - 编写SQL语句
-- 查询对应日期的数据（只展示出来5条）
SELECT * FROM "MOMO_CHAT"."MSG" T
WHERE substr("msg_time", 0, 10) = '2020-08-29'
AND T."sender_account" = '13504113666'
AND T."receiver_account" = '18182767005' LIMIT 100;
- 编写Java代码
1.编写PhoenixChatMessageService实现ChatMessageService接口
2.在构造器中创建JDBC连接
- JDBC驱动为：PhoenixDriver.class.getName()
- JDBC连接URL为：jdbc:phoenix:node1.itcast.cn:2181
3.基于JDBC实现getMessage查询
4.在close方法中
public class PhoenixChatMessageService implements ChatMessageService {
private Connection connection;
public PhoenixChatMessageService() {
try {
Class.forName(PhoenixDriver.class.getName());
connection = DriverManager.getConnection("jdbc:phoenix:node1.itcast.cn:2181");
} catch (ClassNotFoundException e) {
throw new RuntimeException("加载Phoenix驱动失败!");
} catch (SQLException e) {
throw new RuntimeException("获取Phoenix JDBC连接失败!");
}
}
@Override
public List<Msg> getMessage(String date, String sender, String receiver) throws Exception {
PreparedStatement ps = connection.prepareStatement(
"SELECT * FROM MOMO_CHAT.MSG T WHERE substr(\"msg_time\", 0, 10) = ? "
+ "AND T.\"sender_account\" = ? "
+ "AND T.\"receiver_account\" = ? ");
ps.setString(1, date);
ps.setString(2, sender);
ps.setString(3, receiver);
ResultSet rs = ps.executeQuery();
List<Msg> msgList = new ArrayList<>();
while(rs.next()) {
Msg msg = new Msg();
msg.setMsg_time(rs.getString("msg_time"));
msg.setSender_nickyname(rs.getString("sender_nickyname"));
msg.setSender_account(rs.getString("sender_account"));
msg.setSender_sex(rs.getString("sender_sex"));
msg.setSender_ip(rs.getString("sender_ip"));
msg.setSender_os(rs.getString("sender_os"));
msg.setSender_phone_type(rs.getString("sender_phone_type"));
msg.setSender_network(rs.getString("sender_network"));
msg.setSender_gps(rs.getString("sender_gps"));
msg.setReceiver_nickyname(rs.getString("receiver_nickyname"));
msg.setReceiver_ip(rs.getString("receiver_ip"));
msg.setReceiver_account(rs.getString("receiver_account"));
msg.setReceiver_os(rs.getString("receiver_os"));
msg.setReceiver_phone_type(rs.getString("receiver_phone_type"));
msg.setReceiver_network(rs.getString("receiver_network"));
msg.setReceiver_gps(rs.getString("receiver_gps"));
msg.setReceiver_sex(rs.getString("receiver_sex"));
msg.setMsg_type(rs.getString("msg_type"));
msg.setDistance(rs.getString("distance"));
msgList.add(msg);
}
return msgList;
}
@Override
public void close() {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void main(String[] args) throws Exception {
ChatMessageService chatMessageService = new PhoenixChatMessageService();
List<Msg> message = chatMessageService.getMessage("2020-08-24", "13504113666", "18182767005");
for (Msg msg : message) {
System.out.println(msg);
}
chatMessageService.close();
}
}
7.3 二级索引
上面的查询，因为没有建立索引，组合条件查询效率较低，而通过使用Phoenix，我们可以非常方便地创建二级索引。Phoenix中的索引，其实底层还是表现为HBase中的表结构。这些索引表专门用来加快查询速度。
索引分类
1.全局索引
2.本地索引
3.覆盖索引
4.函数索引
全局索引
1.全局索引适用于读多写少业务
2.全局索引绝大多数负载都发生在写入时，当构建了全局索引时，Phoenix会拦截写入(DELETE、UPSERT值和UPSERT SELECT)上的数据表更新，构建索引更新，同时更新所有相关的索引表，开销较大
3.读取时，Phoenix将选择最快能够查询出数据的索引表。默认情况下，除非使用Hint，如果SELECT查询中引用了其他非索引列，该索引是不会生效的
4.全局索引一般和覆盖索引搭配使用，读的效率很高，但写入效率会受影响
创建语法：
CREATE INDEX 索引名称 ON 表名 (列名1, 列名2, 列名3...)
本地索引
1.本地索引适合写操作频繁，读相对少的业务
2.当使用SQL查询数据时，Phoenix会自动选择是否使用本地索引查询数据
3.在本地索引中，索引数据和业务表数据存储在同一个服务器上，避免写入期间的其他网络开销
4.在Phoenix 4.8.0之前，本地索引保存在一个单独的表中，在Phoenix 4.8.1中，本地索引的数据是保存在一个影子列蔟中
5.本地索引查询即使SELECT引用了非索引中的字段，也会自动应用索引的
注意：创建表的时候指定了SALT_BUCKETS，是不支持本地索引的。
创建语法：
CREATE local INDEX 索引名称 ON 表名 (列名1, 列名2, 列名3...)
覆盖索引
Phoenix提供了覆盖的索引，可以不需要在找到索引条目后返回到主表。Phoenix可以将关心的数据捆绑在索引行中，从而节省了读取时间的开销。
例如，以下语法将在v1和v2列上创建索引，并在索引中包括v3列，也就是通过v1、v2就可以直接把数据查询出来。
CREATE INDEX my_index ON my_table (v1,v2) INCLUDE(v3)
函数索引
函数索引(4.3和更高版本)可以支持在列上创建索引，还可以基于任意表达式上创建索引。然后，当查询使用该表达式时，可以使用索引来检索结果，而不是数据表。例如，可以在UPPER(FIRST_NAME||‘ ’||LAST_NAME)上创建一个索引，这样将来搜索两个名字拼接在一起时，索引依然可以生效。
-- 创建索引
CREATE INDEX UPPER_NAME_IDX ON EMP (UPPER(FIRST_NAME||' '||LAST_NAME))
-- 以下查询会走索引
SELECT EMP_ID FROM EMP WHERE UPPER(FIRST_NAME||' '||LAST_NAME)='JOHN DOE'
索引示例一：创建全局索引 + 覆盖索引
- 需求
我们需要根据用户ID来查询订单的ID以及对应的支付金额。例如：查询已付款的订单ID和支付金额
此时，就可以在USER_ID列上创建索引，来加快查询 - 创建索引
create index GBL_IDX_ORDER_DTL on ORDER_DTL(C1."user_id") INCLUDE("id", C1."money");
可以在HBase shell中看到，Phoenix自动帮助我们创建了一张GBL_IDX_ORDER_DTL的表。这种表就是一张索引表。它的数据如下：
这张表的ROWKEY为：用户ID + \x00 + 原始表ROWKEY，列蔟对应的就是include中指定的两个字段。hbase(main):005:0> scan "GBL_IDX_ORDER_DTL", { LIMIT => 1} ROW COLUMN+CELL 1250995\x00d7be5c39-e07c-40e8-bf09-492 column=C1:\x00\x00\x00\x00, timestamp=1589350330650, value=x 2fbc6335c 1250995\x00d7be5c39-e07c-40e8-bf09-492 column=C1:\x80\x0B, timestamp=1589350330650, value=\xC6\x08\xB8\x01 2fbc6335c 1 row(s) Took 0.1253 seconds - 查询数据
select "user_id", "id", "money" from ORDER_DTL where "user_id" = '8237476'; - 查看执行计划
explain select "user_id", "id", "money" from ORDER_DTL where "user_id" = '8237476';
我们发现，PLAN中能看到SCAN的是GBL_IDX_ORDER_DTL，说明Phoenix是直接通过查询索引表获取到数据。 - 删除索引
使用drop index 索引名 ON 表名
drop index IDX_ORDER_DTL_DATE on ORDER_DTL; - 查看索引
!table
- 测试查询所有列是否会使用索引
explain select * from ORDER_DTL where "user_id" = '8237476';
通过查询结果发现，PLAN中是执行的FULL SCAN，说明索引并没有生效，进行的全表扫描。 - 使用Hint强制使用索引
explain select /*+ INDEX(ORDER_DTL GBL_IDX_ORDER_DTL) */ * from ORDER_DTL where USER_ID = '8237476';
通过执行计划，我们可以观察到查看全局索引，找到ROWKEY，然后执行全表的JOIN，其实就是把对应ROWKEY去查询ORDER_DTL表。
383
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
