Phoenix + HBase,让你像操作MySQL一样操作HBase荐

Phoenix关联HBase的操作(三种情况)

情况一:Hbase已经有已存在的表了,可在Phoenix中创建对应的视图,视图只能做查询操作,不能做增删改

  • hbase中已创建表且有数据,表名:phoenix

Phoenix + HBase,让你像操作MySQL一样操作HBase荐

  • 在phoenix中创建对应视图
create view "phoenix"(
pk varchar primary key,
"info"."name" varchar,
"info"."age" varchar
);
  • 查询视图数据

Phoenix + HBase,让你像操作MySQL一样操作HBase荐

情况二:Hbase已存在表,可在Phoenix中创建对应的表,对表的操作可读可写,意味着可以对Hbase的表进行插入查询,删除操作。

  • hbase中已存在表并且有数据
    Phoenix + HBase,让你像操作MySQL一样操作HBase荐

  • 在phoenix中创建对应的表
    create table "t_person"(
    id VARCHAR PRIMARY KEY,
    "f"."id" VARCHAR,
    "f"."name" VARCHAR,
    "f"."age" VARCHAR
    ) column_encoded_bytes=0;
  • 在phoenix中查询数据
    Phoenix + HBase,让你像操作MySQL一样操作HBase荐

  • 在phoenix中插入数据
    Phoenix + HBase,让你像操作MySQL一样操作HBase荐

此时查看hbase中对应的t_person表数据
Phoenix + HBase,让你像操作MySQL一样操作HBase荐

情况三:Hbase没有表;可直接在Phoeinx中创建表,对应的Habse中也会自动建表,在Phoenix中对表操作就是直接对Hbase中的表进行操作。

  • 在phoenix中直接建表,t_test表会在hbase中同步创建
    Phoenix + HBase,让你像操作MySQL一样操作HBase荐

  • 插入数据看phoenix和hbase中的效果
    Phoenix + HBase,让你像操作MySQL一样操作HBase荐

    Phoenix + HBase,让你像操作MySQL一样操作HBase荐

  • 通过Java客户端操作phoenix
package com.fwmagic.hbase;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.sql.*;
/**
* 通过Phoenix操作Hbase
*/
public class PhoenixQueryHbase {
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
@Before
public void init() throws Exception {
connection = DriverManager.getConnection("jdbc:phoenix:hd1,hd2,hd3:2181");
}
/**
* 建表并查询
*
* @throws Exception
*/
@Test
public void create() throws Exception {
Statement statement = connection.createStatement();
statement.executeUpdate("create  table test(id integer primary key ,animal varchar )");
//新增和更新都是一个操作:upsert
statement.executeUpdate("upsert into test values (1,'dog')");
statement.executeUpdate("upsert into test values (2,'cat')");
connection.commit();
PreparedStatement preparedStatement = connection.prepareStatement("select * from  test");
rs = preparedStatement.executeQuery();
while (rs.next()) {
String id = rs.getString("id");
String animal = rs.getString("animal");
String format = String.format("id:%s,animal:%s", id, animal);
System.out.println(format);
}
}
/**
* 查询已有的表
*
* @throws Exception
*/
@Test
public void testQuery() throws Exception {
String sql = "select * from tc";
try {
ps = connection.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
String id = rs.getString("ID");
String name = rs.getString("NAME");
String age = rs.getString("AGE");
String sex = rs.getString("SEX");
String format = String.format("id:%s,name:%s,age:%s,sex:%s", id, name, age, sex);
System.out.println(format);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (rs != null) rs.close();
if (ps != null) ps.close();
if (connection != null) connection.close();
}
}
/**
* 删除数据
*
* @throws Exception
*/
@Test
public void delete() throws Exception {
try {
ps = connection.prepareStatement("delete from test where id=2");
ps.executeUpdate();
connection.commit();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (rs != null) rs.close();
if (ps != null) ps.close();
if (connection != null) connection.close();
}
}
/**
* 删除表
*
* @throws Exception
*/
@Test
public void dropTable() throws Exception {
try {
ps = connection.prepareStatement("drop table test");
ps.execute();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (rs != null) rs.close();
if (ps != null) ps.close();
if (connection != null) connection.close();
}
}
@After
public void close() throws Exception {
if (rs != null) rs.close();
if (ps != null) ps.close();
if (connection != null) connection.close();
}
}
  • 示例代码下载
   https://gitee.com/fang_wei/fwmagic-hbase