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