因为我们之前已经创建了 MOMO_CHAT:MSG 表,而且数据添加的方式都是以PUT方式原生API来添加的。故此时,我们不再需要再使用Phoenix创建新的表,而是使用Phoenix中的视图,通过视图来建立与HBase表之间的映射,从而实现数据快速查询。
我们可以在现有的HBase或Phoenix表上创建一个视图。表、列蔟和列名必须与现有元数据完全匹配,否则会出现异常。当创建视图后,就可以使用SQL查询视图,和操作Table一样。
create view "my_hbase_table" (
rowkey varchar primary key,
列族.列名1 数据类型,
列族.列名2 数据类型,
....
)
考虑以下几个问题:
-- 创建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
);
SELECT * FROM "MOMO_CHAT"."MSG" LIMIT 1;
根据日期、发送人账号、接收人账号查询历史消息
SELECT
C1."sender_account",
C1."receiver_account",
C1."msg_time",
C1."message"
FROM
MOMO_CHAT.MSG
WHERE
substr( C1."msg_time", 0, 10 ) = '2021-07-16'
AND C1."sender_account" = '18461866438'
AND C1."receiver_account" = '13641568674';
@Override
public List<Msg> getMessage(String date, String sender, String receiver) throws Exception {
Class.forName(PhoenixDriver.class.getName());
Connection connection = DriverManager.getConnection("jdbc:phoenix:node1:2181");
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;
}