phoenix 读取mysql_Phoenix + HBase,让你像操作MySQL一样操作HBase

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

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

hbase中已创建表且有数据，表名：phoenix

在phoenix中创建对应视图

create view "phoenix"(

pk varchar primary key,

"info"."name" varchar,

"info"."age" varchar

);

查询视图数据

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

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中对应的t_person表数据

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

在phoenix中直接建表，t_test表会在hbase中同步创建

插入数据看phoenix和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