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