Operation of Phoenix associated HBase (three cases)

Situation 1: Hbase already has existing tables. You can create corresponding views in Phoenix. Views can only be queried, not added, deleted or modified

• Table has been created and data exists in hbase, table name: phoenix

• Create the corresponding view in phoenix

create view "phoenix"(
pk varchar primary key,
"info"."name" varchar,
"info"."age" varchar
);

• Query view data

Case 2: there are already tables in Hbase. You can create corresponding tables in Phoenix. The operations on tables are readable and writable, which means that you can insert, query and delete the tables in Hbase.

• Table already exists in hbase and has data
  

• Create the corresponding table in phoenix

  create table "t_person"(
  id VARCHAR PRIMARY KEY,
  "f"."id" VARCHAR,
  "f"."name" VARCHAR,
  "f"."age" VARCHAR
  ) column_encoded_bytes=0;

• Querying data in phoenix
  

• Insert data in phoenix
  

At this time, view the corresponding t ﹣ person table data in hbase

Case 3: Hbase does not have a table; you can directly create a table in Phoeinx, and the corresponding Habse will also automatically create a table. Table operation in Phoenix is to directly operate the table in Hbase.

• Create the table directly in phoenix, and the t_test table will be created synchronously in hbase
  

• Insert data to see the effect in phoenix and hbase
  

  

• Operating phoenix through Java client

package com.fwmagic.hbase;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.sql.*;

/**
 * Operating Hbase with Phoenix
 */
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");
    }

    /**
     * Create table and query
     *
     * @throws Exception
     */
    @Test
    public void create() throws Exception {
        Statement statement = connection.createStatement();
        statement.executeUpdate("create  table test(id integer primary key ,animal varchar )");

        //Adding and updating are both one operation: 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);
        }
    }

    /**
     * Query existing tables
     *
     * @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();
        }
    }

    /**
     * Delete data
     *
     * @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();
        }
    }

    /**
     * Delete table
     *
     * @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();

    }
}

• Sample code download

   https://gitee.com/fang_wei/fwmagic-hbase