单机版的Hbase配合Phoenix在idea上用jdbc实现增删改查

本文出处
一、描述
现有hbase的查询工具有很多如：Hive，Tez，Impala，Shark/Spark，Phoenix等。今天主要记录Phoenix。

      phoenix，中文译为“凤凰”，很美的名字。Phoenix是由saleforce.com开源的一个项目，后又捐给了Apache基金会。它相当于一个Java中间件，提供jdbc连接，操作hbase数据表。

      Phoenix官网上，对Phoenix讲解已经很详细了。如果英语好，可以看官网，更正式一些。

二、Phoenix安装
1.下载Phoenix
下载地址：http://mirror.bit.edu.cn/apache/phoenix/

phoenix与HBase版本对应关系

Phoenix 2.x - HBase 0.94.x

Phoenix 3.x - HBase 0.94.x

Phoenix 4.x - HBase 0.98.1+
我目前测试使用版本概况：

Hadoop2.7.5–HBase1.4.8

所以我可以用phoenix4.x。下载的压缩包为apache-phoenix-4.14.0-HBase-1.4-bin.tar.gz

wget http://mirror.bit.edu.cn/apache/phoenix/apache-phoenix-4.14.0-HBase-1.4/

2.移动压缩包
将apache-phoenix-4.14.0-HBase-1.4-bin.tar.gz移动到hbase集群的其中一个服务器的一个目录下

我移动的目录为/usr/local/phoenix/

mv http://mirror.bit.edu.cn/apache/phoenix/apache-phoenix-4.14.0-HBase-1.4/ /usr/local/phoenix/
3.解压缩文件
tar –zxvf apache-phoenix-4.14.0-HBase-1.4-bin.tar.gz
重命名

mv apache-phoenix-4.14.0-HBase-1.4-bin phoenix-4.14.0
可看到有个phoenix-4.14.0/目录，里面包含了Phoenix的所有文件。

4.配置Phoenix
4.1、将phoenix-4.14.0/目录下 phoenix-4.14.0-HBase-1.4-server.jar、phoenix-core-4.14.0-HBase-1.4.jar、phoenix-4.14.0-HBase-1.4-client.jar拷贝到各个 HBase 的 lib目录中（每个节点都要放） 。

4.2、重启hbase集群，使Phoenix的jar包生效。

4.3、将hbase的配置文件hbase-site.xml 放到phoenix-4.14.0/bin/下，替换Phoenix原来的 配置文件。

5.修改权限
切换到phoenix-4.14.0/bin下，修改psql.py和sqlline.py的权限为777

命令：chmod 777 文件名

6.验证是否成功
6.1、在phoenix-4.14.0/bin/下输入命令： ./sqlline.py localhost

如果看到如下界面表示启动成功。

6.2、输入!tables，查看都有哪些表。红框部分是我建的表，其他为Phoenix系统表，系统表中维护了用户表的元数据信息。

6.3、退出Phoenix。输入!exit命令、或者！quit命令

三、Phoenix使用
1、建表（在phoenix-4.14.0/bin/目录下）
./psql.py localhost:2181 …/examples/WEB_STAT.sql
其中…/examples/WEB_STAT.sql是建表的sql语句（phoenix自带的案例）–里面的语句如下：

CREATE TABLE IF NOT EXISTS WEB_STAT (

HOST CHAR(2) NOT NULL,

DOMAIN VARCHAR NOT NULL,

FEATURE VARCHAR NOT NULL,

DATE DATE NOT NULL,

USAGE.CORE BIGINT,–usage指定列族名

USAGE.DB BIGINT,–usage指定列族名

STATS.ACTIVE_VISITOR INTEGER

CONSTRAINT PK PRIMARY KEY (HOST, DOMAIN, FEATURE, DATE)–指定主键

);
2、导入数据
命令：./psql.py -t WEB_STAT localhost:2181 …/examples/WEB_STAT.csv

PS：其中 -t 后面是表名， …/examples/web_stat.csv 是csv数据（注意数据的分隔符需要是逗号）。

3、查询数据
首先使用sqlline查看(截图为部分列的数据)，查询表名不区分大小写。

语句：select * from web_stat;

查询2、查询记录总条数
语句：select count(1) from web_stat;

查询3、查询结果分组排序
语句：select domain,count(1) as num from web_stat group by domain order by num desc;

查询4、求平均值
语句：select avg(core) from web_stat;

查询5、多字段分组，排序，别名。
语句：select domain,count(1) as num,avg(core) as core,avg(db) as db from web_stat group by domain order by num desc;

查询6、查询日期类型字段
语句：select host,domain,date from web_stat where TO_CHAR(date)=‘2013-01-15 07:09:01.000’;

查询7、字符串，日期类型转换
语句：select TO_DATE(‘20131125’,‘yyyyMMdd’) from web_stat;

Ps：输入的日期字符串会被转换为hbase表date的日期类型。

总结：Phoenix还支持了很多函数和sql语法，在这里不再一一列举。

四、Phoenix基本shell命令
PS：以下，可能有部分命令在Phoenix更高版本中已失效，改为其他命令代替，请注意。

0: jdbc:phoenix:localhost> help
!set Set a sqlline variable
!script Start saving a script to a file
!scan Scan for installed JDBC drivers
!save Save the current variabes and aliases
!run Run a script from the specified file
!rollback Roll back the current transaction (if autocommit is off)
!rehash Fetch table and column names for command completion
!record Record all output to the specified file
!reconnect Reconnect to the database
!quit /!exit Exits the program
!properties Connect to the database specified in the properties file(s)
!procedures List all the procedures
!primarykeys List all the primary keys for the specified table
!outputformat Set the output format for displaying results (table,vertical,csv,tsv,xmlattrs,xmlelements)
!nativesql Show the native SQL for the specified statement
!metadata Obtain metadata information
!manual Display the SQLLine manual
!list List the current connections
!isolation Set the transaction isolation for this connection
!indexes List all the indexes for the specified table
!importedkeys List all the imported keys for the specified table
!history Display the command history
!help Print a summary of command usage
!go Select the current connection
!exportedkeys List all the exported keys for the specified table
!dropall Drop all tables in the current database
!describe Describe a table
!dbinfo Give metadata information about the database
!connect Open a new connection to the database.
!commit Commit the current transaction (if autocommit is off)
!columns List all the columns for the specified table
!closeall Close all current open connections
!close Close the current connection to the database
!call Execute a callable statement
!brief Set verbose mode off
!batch Start or execute a batch of statements
!autocommit Set autocommit mode on or off
!all Execute the specified SQL against all the current connections
五、用Phoenix Java api操作HBase
开发环境准备：idea、jdk1.8、window8、hadoop2.7.5、hbase-1.4.8、phoenix4.14.0

1.从集群拷贝以下文件：core-site.xml、hbase-site.xml、hdfs-site.xml文件放到工程resources下

2.在pom.xml中添加依赖(根据自己的版本添加相应的依赖)

org.apache.phoenix phoenix-core 4.14.0-HBase-1.4 4.在客户端C:\Windows\System32\drivers\etc\hosts文件中加入集群的hostname和IP

5.工程截图

6.工程代码

package com.phoenix.day01;

import java.sql.*;

public class Phoenix_Test {
private static Connection conn=null;
private static Statement statement = null;
static {
try {
//phoenix4.14.0用下面的驱动对应hbase1.4.+
Class.forName(“org.apache.phoenix.jdbc.PhoenixDriver”);
//这里配置zookeeper的地址，可单个，也可多个。可以是域名或者ip
String url = “jdbc:phoenix:master2:2181/hbase”;
//String url = “jdbc:phoenix:41.byzoro.com,42.byzoro.com,43.byzoro.com:2181”;
conn = DriverManager.getConnection(url);
statement = conn.createStatement();
} catch (Exception e) {
e.printStackTrace();
}

}

/**
 * 测试类
 * @param args
 * @throws SQLException
 */
public static void main(String[] args) throws SQLException {
    testRead();
    testReadAll();
    testupsertAll(1000000);
    testUpsert();
    testCreateTable("student");
    testDelete("student");
    testSelect("student");
}


/**
 * 扫描全表
 * @param tableName
 * @throws SQLException
 */
private static void testSelect(String tableName) throws SQLException {
    String sql="select * from "+tableName+"";
    statement.executeUpdate(sql);
    conn.commit();

    conn.close();
    statement.close();
}

/**
 * 单挑添加数据
 * @throws SQLException
 */
private static void testUpsert() throws SQLException {
    String sql1="upsert into test_phoenix_api values(1,'test1')";
    String sql2="upsert into test_phoenix_api values(2,'test2')";
    String sql3="upsert into test_phoenix_api values(3,'test3')";
    statement.executeUpdate(sql1);
    statement.executeUpdate(sql2);
    statement.executeUpdate(sql3);
    conn.commit();
    System.out.println("数据已插入");

    statement.close();
    conn.close();
}

/**
 * 删除数据
 * @param tableName
 * @throws SQLException
 */
private static void testDelete(String tableName) throws SQLException {
    String sql="delete from "+tableName+" where mykey = 1";
    statement.executeUpdate(sql);
    conn.commit();

    conn.close();
    statement.close();
}

/**
 * c创建表
 * @param tableName
 * @throws SQLException
 */
private static void testCreateTable(String tableName) throws SQLException {
    String sql="create table "+tableName+"(mykey integer not null primary key ,mycolumn varchar )";
    statement.executeUpdate(sql);
    conn.commit();

    conn.close();
    statement.close();
}

/**
 * 批量插入
 * @param count
 * @throws SQLException
 */
private static void testupsertAll(int count) throws SQLException {

    for (int i = 0; i < count; i++) {
        String sql ="upsert into STUDENT_CON values("+i+",'lisi',12)";
        statement.executeUpdate(sql);
        conn.commit();
    }
    System.out.println(count+"条数据已插入完毕");
    conn.close();
    statement.close();
}
/**
 * 使用phoenix提供的api操作hbase中读取数据
 */
private static void testReadAll() throws SQLException {

//String sql = “select count(1) as num from web_stat”;
String sql = “select * from web_stat where core = 35”;
long time = System.currentTimeMillis();
ResultSet rs = statement.executeQuery(sql);
while (rs.next()) {
//获取core字段值
int core = rs.getInt(“core”);
//获取core字段值
String host = rs.getString(“host”);
//获取domain字段值
String domain = rs.getString(“domain”);
//获取feature字段值
String feature = rs.getString(“feature”);
//获取date字段值,数据库中字段为Date类型，这里代码会自动转化为string类型
String date = rs.getString(“date”);
//获取db字段值
String db = rs.getString(“db”);
System.out.println(“host:”+host+"\tdomain:"+domain+"\tfeature:"+feature+"\tdate:"+date+"\tcore:" + core+"\tdb:"+db);
}
long timeUsed = System.currentTimeMillis() - time;
System.out.println("time " + timeUsed + “mm”);
//关闭连接
rs.close();
statement.close();
conn.close();
}
/**
* 使用phoenix提供的api操作hbase读取数据
*/
private static void testRead() throws SQLException {
//Statement statement = conn.createStatement();
String sql = “select count(1) as num from web_stat”;
long time = System.currentTimeMillis();
ResultSet rs = statement.executeQuery(sql);
while (rs.next()) {
int count = rs.getInt(“num”);
System.out.println("row count is " + count);
}
long timeUsed = System.currentTimeMillis() - time;
System.out.println("time " + timeUsed + “mm”);
//关闭连接
rs.close();
statement.close();
conn.close();
}
}