目录

• Phoenix概述
• Phoenix安装
• 映射视图
• 映射表
• 直接建表
• 批量数据导入
• 标准JDBC访问Phoenix

Phoenix概述

• Phoenix是一个用于增强HBase的软件套件，结合了sql和hbase的优点，提供了一个可以通过sql来访问的OLTP (On-Line Transaction Processing 联机事务处理过程)或OLAP (Online Analytical Processing 联机分析处理)服务

• 内核使用HBase，表面支持sql来进行增删改查，也可将HBase的表结构映射为关系型表结构

• Phienix使用一系列的协处理器来实现sql的支持，客户端利用sql来请求各类复杂查询时，都是通过Phoenix的协处理器在regionserver侧完成计算

Phoenix安装

• 拷贝jar包到HBase

scp phoenix-5.0.0-HBase-2.0-server.jar phoenix-core-5.0.0-HBase-2.0.jar linux01:/opt/apps/hbase-2.0.4/lib/

scp phoenix-5.0.0-HBase-2.0-server.jar phoenix-core-5.0.0-HBase-2.0.jar linux02:/opt/apps/hbase-2.0.4/lib/

scp phoenix-5.0.0-HBase-2.0-server.jar phoenix-core-5.0.0-HBase-2.0.jar linux03:/opt/apps/hbase-2.0.4/lib/

• 把hadoop和hbase的配置文件拷贝到phoenixera的客户端目录

cp /opt/apps/hbase-2.0.4/conf/hbase-site.xml /opt/apps/hadoop-2.8.5/etc/hadoop/{core-site.xml,hdfs-site.xml} /opt/apps/phoenix-5.0.0/bin/

• 重启HBase

stop-hbase.sh

start-hbase.sh

• 启动Phoenix命令行客户端

bin/sqlline.py

• 显示表

!tables

• 退出

!quit

映射视图

• 只可对数据进行查询，删除视图不影响Hbase中的表

create view

0: jdbc:phoenix:> create view "avatar"(
. . . . . . . . > pk varchar primary key,
. . . . . . . . > "f"."name" varchar,
. . . . . . . . > "f"."age" unsigned_int);

phoenix支持sql，sql语法不支持大小写，而HBase严格区分大小写，所以表名加引号

!tables

!tables

!describe

!describe "avatar"

select

select * from "avatar";

select * from "avatar" where "age" < 13;

select * from "avatar" where "name" = 'Aang';

drop view

drop view "avatar";

映射表

• 可对数据进行增删改查，删除表时会将HBase中对应的表删除

同一个表不能同时映射视图和表

create table

0: jdbc:phoenix:> create table "student"(
. . . . . . . . > pk varchar primary key,
. . . . . . . . > "f"."name" varchar)
. . . . . . . . > column_encoded_bytes=0;

upsert

upsert into "student" values('003', 'Sokka');

drop table

drop table "avatar";

直接建表

create table

0: jdbc:phoenix:> create table user(id INTEGER NOT NULL,
. . . . . . . . > name VARCHAR(20),
. . . . . . . . > age INTEGER,
. . . . . . . . > gender VARCHAR,
. . . . . . . . > CONSTRAINT pk PRIMARY KEY(id,name)
. . . . . . . . > )
. . . . . . . . > ;

upsert

upsert into user values(1,'Aang',12,'M');

upsert into user values(2,'Katara',14,'F');

select

select * from user;

select * from user where age < 13;

批量数据导入

• 使用bin/psql.py一次性导入DDL脚本，数据文件，DML脚本

psql my_ddl.sql
psql localhost my_ddl.sql
psql localhost my_ddl.sql my_table.csv
psql -t MY_TABLE my_cluster:1825 my_table2012-Q3.csv
psql -t MY_TABLE -h COL1,COL2,COL3 my_cluster:1825 my_table2012-Q3.csv
psql -t MY_TABLE -h COL1,COL2,COL3 -d : my_cluster:1825 my_table2012-Q3.csv

• 导入数据到已存在的表中

[root@linux01 phoenix-5.0.0]# bin/psql.py -t USER linux01:2181 /tmp/phoenixera/user.csv

user.csv:

3,Sokka,15,M
4,Toph,12,F
5,Zuko,16,M

• 执行建表语句脚本文件，导入数据，执行查询语句脚本文件

bin/psql.py linux01,linux02,linux03:2181 /tmp/phoenixera/us_population.sql /tmp/phoenixera/us_population.csv /tmp/phoenixera/us_population_queries.sql

us_population.sql

CREATE TABLE IF NOT EXISTS us_population(
state CHAR(2) NOT NULL,
city VARCHAR NOT NULL,
population BIGINT
CONSTRAINT my_pk PRIMARY KEY(state, city));

us_population.csv

NY,New York,8143197
CA,Los Angeles,3844829
IL,Chicago,2842518
TX,Houston,2016582
PA,Philadelphia,1463281
AZ,Phoenix,1461575
TX,San Antonio,1256509
CA,San Diego,1255540
TX,Dallas,1213825
CA,San Jose,912332

us_population_queries.sql

SELECT state as "State",count(city) as "City Count",sum(population) as "Population Sum"
FROM us_population
GROUP BY state
ORDER BY sum(population) DESC;

标准JDBC访问Phoenix

• 要导入Phoenix的jdbc驱动jar依赖

<dependency>
    <groupId>org.apache.phoenix</groupId>
    <artifactId>phoenix-core</artifactId>
    <version>5.0.0-HBase-2.0</version>
</dependency>

import java.sql.*;

public class PhoenixJDBC {

    public static void main(String[] args) throws Exception {

        Statement stmt = null;
        ResultSet rset = null;

        // URI: schema协议 + authority权限 + host + post
        Connection con = DriverManager.getConnection("jdbc:phoenix:linux01,linux02,linux03:2181");
        stmt = con.createStatement();

        // 建表
        stmt.executeUpdate("create table test (id integer not null primary key, name varchar)");

        // 插入或更新数据
        stmt.executeUpdate("upsert into test values (1, 'Aang')");
        stmt.executeUpdate("upsert into test values (2, 'Katara')");
        con.commit();

        // 查询数据
        PreparedStatement statement = con.prepareStatement("select * from test");
        rset = statement.executeQuery();
        while (rset.next()){
            System.out.println(rset.getString("id") + "\t" + rset.getString("name"));
        }
        statement.close();
        stmt.close();
        con.close();
    }
}