本文介紹了如何使用Phoenix來連接Hbase并完成增刪改查
1.概述
Phoenix作為應用層和HBASE之間的中間件,以下特性使它在大數(shù)據(jù)量的簡單查詢場景有著獨有的優(yōu)勢
二級索引支持(global index + local index)
編譯SQL成為原生HBASE的可并行執(zhí)行的scan
在數(shù)據(jù)層完成計算，server端的coprocessor執(zhí)行聚合
下推where過濾條件到server端的scan filter上
利用統(tǒng)計信息優(yōu)化咙轩、選擇查詢計劃（5.x版本將支持CBO）
skip scan功能提高掃描速度
2.下載
https://phoenix.apache.org/download.html
選擇對應的Hbase版本下載即可
3.安裝
3.1.上傳解壓
tar -zxvf apache-phoenix-4.14.0-HBase-1.2-bin.tar.gz -C /opt/soft
3.2.拷貝jar到Hbase的lib下
scp phoenix-4.14.0-HBase-1.2-server.jar hadoop@hadoop1:/opt/soft/hbase-1.2.6/lib/
scp phoenix-4.14.0-HBase-1.2-server.jar hadoop@hadoop2:/opt/soft/hbase-1.2.6/lib/
scp phoenix-4.14.0-HBase-1.2-server.jar hadoop@hadoop3:/opt/soft/hbase-1.2.6/lib/
4.啟動
4.1.啟動Hbase
4.2.進入Phoenix控制臺
./bin/sqlline.py
通過 help 可以查看命令
0: jdbc:phoenix:> !tables
+------------+--------------+----------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------+
| TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | REMARKS | TYPE_NAME | SELF_REFERENCING_COL_NAME | REF_GENERATION | INDEX_STATE | IMMUTABLE |
+------------+--------------+----------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------+
| | SYSTEM | CATALOG | SYSTEM TABLE | | | | | | false |
| | SYSTEM | FUNCTION | SYSTEM TABLE | | | | | | false |
| | SYSTEM | LOG | SYSTEM TABLE | | | | | | true |
| | SYSTEM | SEQUENCE | SYSTEM TABLE | | | | | | false |
| | SYSTEM | STATS | SYSTEM TABLE | | | | | | false |
| | | TEST | TABLE | | | | | | false |
| | | US_POPULATION | TABLE | | | | | | false |
+------------+--------------+----------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------+
CRUD
0: jdbc:phoenix:> !sql
. . . . . . . . > create table test1 (mykey integer not null primary key, mycolumn varchar);
No rows affected (1.538 seconds)
0: jdbc:phoenix:> !sql
. . . . . . . . > upsert into test1 values (1,'Hello');
1 row affected (0.064 seconds)
0: jdbc:phoenix:> !sql
. . . . . . . . > select * from test1;
+--------+-----------+
| MYKEY | MYCOLUMN |
+--------+-----------+
| 1 | Hello |
+--------+-----------+
1 row selected (0.09 seconds)
0: jdbc:phoenix:> !sql
. . . . . . . . > delete from test1 where mykey = 1;
1 row affected (0.074 seconds)
0: jdbc:phoenix:> select * from test1;
+--------+-----------+
| MYKEY | MYCOLUMN |
+--------+-----------+
+--------+-----------+
No rows selected (0.153 seconds)
鍵入 !quit 退出shell
5.JavaAPI
5.1.引入依賴
<dependency>
<groupId>org.apache.phoenix</groupId>
<artifactId>phoenix-core</artifactId>
<version>4.14.0-HBase-1.2</version>
</dependency>
5.2.CRUD
public class PhoenixTest {
public static void main(String[] args) throws Exception {
Statement stmt = null;
ResultSet rset = null;
Connection con = DriverManager.getConnection("jdbc:phoenix:hadoop1,hadoop2,hadoop3");
stmt = con.createStatement();
stmt.executeUpdate("create table test (mykey integer not null primary key, mycolumn varchar)");
stmt.executeUpdate("upsert into test values (1,'Hello')");
stmt.executeUpdate("upsert into test values (2,'World!')");
con.commit();
PreparedStatement statement = con.prepareStatement("SELECT * from test");
rset = statement.executeQuery();
while (rset.next()) {
System.out.println(rset.getString("mycolumn"));
}
statement.close();
con.close();
}
}
