Phoenix集成
Phoenix是构建在HBase上的一个SQL层,能让我们用标准的JDBC APIs而不是HBase客户端APIs来创建表,插入数据和对HBase数据进行查询。Phoenix完全使用Java编写,作为HBase内嵌的JDBC驱动。Phoenix查询引擎会将SQL查询转换为一个或多个HBase扫描,并编排执行以生成标准的JDBC结果集。下载apache-phoenix-4.10.0-HBase-1.2-bin.tar.gz,注意下载的Phoenix版本必须和hbase目标版本保持一致。
安装
1、确保HDFS/HBase正常运行
2、解压Phoenix的安装包,将phoenix-[version]-server.jar
和phoenix-[version]-client.jar
拷贝到所有运行HBase的节点的lib目录下
[root@CentOS ~]# tar -zxf apache-phoenix-4.10.0-HBase-1.2-bin.tar.gz -C /usr/
[root@CentOS ~]# mv /usr/apache-phoenix-4.10.0-HBase-1.2-bin/ /usr/phoenix-4.10.0
[root@CentOS phoenix-4.10.0]# cp phoenix-4.10.0-HBase-1.2-client.jar /usr/hbase-1.2.4/lib/
[root@CentOS phoenix-4.10.0]# cp phoenix-4.10.0-HBase-1.2-server.jar /usr/hbase-1.2.4/lib/
[root@CentOS phoenix-4.10.0]#
3、强烈建议大家将HBase的历史残余数据给清楚之后再启动HBase
[root@CentOS ~]# hbase clean --cleanAll
[root@CentOS ~]# rm -rf /usr/hbase-1.2.4/logs/*
[root@CentOS ~]# start-hbase.sh
4、通过sqlline.py
链接Hbase
[root@CentOS phoenix-4.10.0]# ./bin/sqlline.py CentOS
Setting property: [incremental, false]
Setting property: [isolation, TRANSACTION_READ_COMMITTED]
issuing: !connect jdbc:phoenix:CentOS none none org.apache.phoenix.jdbc.PhoenixDriver
Connecting to jdbc:phoenix:CentOS
....
Connected to: Phoenix (version 4.10)
Driver: PhoenixEmbeddedDriver (version 4.10)
Autocommit status: true
Transaction isolation: TRANSACTION_READ_COMMITTED
Building list of tables and columns for tab-completion (set fastconnect to true to skip)...
91/91 (100%) Done
Done
sqlline version 1.2.0
0: jdbc:phoenix:CentOS>
5、退出交互窗口
0: jdbc:phoenix:CentOS> !quit
基本使用
1、查看所有表
0: jdbc:phoenix:CentOS> !tables
2、创建表
0: jdbc:phoenix:CentOS> create table t_user(
. . . . . . . . . . . > id integer primary key,
. . . . . . . . . . . > name varchar(32),
. . . . . . . . . . . > age integer,
. . . . . . . . . . . > sex boolean
. . . . . . . . . . . > );
No rows affected (1.348 seconds)
3、查看表的字段信息
0: jdbc:phoenix:CentOS> !column t_user
4、插入/更新数据
0: jdbc:phoenix:CentOS> upsert into t_user values(1,'jiangzz',18,false);
1 row affected (0.057 seconds)
0: jdbc:phoenix:CentOS> upsert into t_user values(1,'jiangzz',18,true);
1 row affected (0.006 seconds)
0: jdbc:phoenix:CentOS> upsert into t_user values(2,'lisi',20,true);
1 row affected (0.023 seconds)
0: jdbc:phoenix:CentOS> upsert into t_user values(3,'wangwu',18,false);
1 row affected (0.006 seconds)
0: jdbc:phoenix:CentOS> select * from t_user;
+-----+----------+------+--------+
| ID | NAME | AGE | SEX |
+-----+----------+------+--------+
| 1 | jiangzz | 18 | true |
| 2 | lisi | 20 | true |
| 3 | wangwu | 18 | false |
+-----+----------+------+--------+
3 rows selected (0.085 seconds)
5、更改某个字段值
0: jdbc:phoenix:CentOS> upsert into t_user(id,name) values(1,'win7');
1 row affected (0.024 seconds)
0: jdbc:phoenix:CentOS> select * from t_user;
+-----+---------+------+--------+
| ID | NAME | AGE | SEX |
+-----+---------+------+--------+
| 1 | win7 | 18 | true |
| 2 | lisi | 20 | true |
| 3 | wangwu | 18 | false |
+-----+---------+------+--------+
3 rows selected (0.201 seconds)
6、执行某些统计操作
0: jdbc:phoenix:CentOS> select sex,avg(age),max(age),min(age),sum(age) from t_user group by sex;
+--------+-----------+-----------+-----------+-----------+
| SEX | AVG(AGE) | MAX(AGE) | MIN(AGE) | SUM(AGE) |
+--------+-----------+-----------+-----------+-----------+
| false | 18 | 18 | 18 | 18 |
| true | 19 | 20 | 18 | 38 |
+--------+-----------+-----------+-----------+-----------+
2 rows selected (0.123 seconds)
0: jdbc:phoenix:CentOS> select sex,avg(age),max(age),min(age),sum(age) total from t_user group by sex order by total desc;
+--------+-----------+-----------+-----------+--------+
| SEX | AVG(AGE) | MAX(AGE) | MIN(AGE) | TOTAL |
+--------+-----------+-----------+-----------+--------+
| true | 19 | 20 | 18 | 38 |
| false | 18 | 18 | 18 | 18 |
+--------+-----------+-----------+-----------+--------+
2 rows selected (0.072 seconds)
0: jdbc:phoenix:CentOS>
7、数据库操作
0: jdbc:phoenix:CentOS> create schema if not exists baizhi;
提示
必须同时修改HASE_HOME/conf/hbase-site.xml
文件和 PHOENIX_HOME/bin/hbase-site.xml
文件,修改完成重启Hbase服务
<property>
<name>phoenix.schema.isNamespaceMappingEnabled</name>
<value>true</value>
</property>
<property>
<name>phoenix.schema.mapSystemTablesToNamespace</name>
<value>true</value>
</property>
0: jdbc:phoenix:CentOS> create schema if not exists baizhi;
No rows affected (0.046 seconds)
0: jdbc:phoenix:CentOS> use baizhi;
No rows affected (0.049 seconds)
0: jdbc:phoenix:CentOS> create table if not exists t_user(
. . . . . . . . . . . > id integer primary key ,
. . . . . . . . . . . > name varchar(128),
. . . . . . . . . . . > sex boolean,
. . . . . . . . . . . > birthDay date,
. . . . . . . . . . . > salary decimal(7,2)
. . . . . . . . . . . > );
如果用户不指定schema,默认使用的是default数据库
8、查看建表详情,等价!column
0: jdbc:phoenix:CentOS> !desc baizhi.t_user;
9、删除表
0: jdbc:phoenix:CentOS> drop table if exists baizhi.t_user;
No rows affected (3.638 seconds)
如果有其他表指向该表,我们可以在删除的表时候添加cascade关键字
0: jdbc:phoenix:CentOS> drop table if exists baizhi.t_user cascade;
No rows affected (0.004 seconds)
10、修改表
①添加字段
0: jdbc:phoenix:CentOS> alter table t_user add age integer;
No rows affected (5.994 seconds)
②删除字段
0: jdbc:phoenix:CentOS> alter table t_user drop column age;
No rows affected (1.059 seconds)
③设置表的TimeToLive
0: jdbc:phoenix:CentOS> alter table t_user set TTL=100;
No rows affected (5.907 seconds)
0: jdbc:phoenix:CentOS> upsert into t_user(id,name,sex,birthDay,salary) values(1,'jiangzz',true,'1990-12-16',5000.00);
1 row affected (0.031 seconds)
0: jdbc:phoenix:CentOS> select * from t_user;
+-----+----------+-------+--------------------------+---------+
| ID | NAME | SEX | BIRTHDAY | SALARY |
+-----+----------+-------+--------------------------+---------+
| 1 | jiangzz | true | 1990-12-16 00:00:00.000 | 5E+3 |
+-----+----------+-------+--------------------------+---------+
1 row selected (0.074 seconds)
11、数据DML
①插入&更新
0: jdbc:phoenix:CentOS> upsert into t_user(id,name,sex,birthDay,salary) values(1,'jiangzz',true,'1990-12-16',5000.00);
1 row affected (0.014 seconds)
②删除记录
0: jdbc:phoenix:CentOS> delete from t_user where name='jiangzz';
1 row affected (0.014 seconds)
0: jdbc:phoenix:CentOS> select * from t_user;
+-----+-------+------+-----------+---------+
| ID | NAME | SEX | BIRTHDAY | SALARY |
+-----+-------+------+-----------+---------+
+-----+-------+------+-----------+---------+
No rows selected (0.094 seconds)
③查询数据
0: jdbc:phoenix:CentOS> select * from t_user;
+-----+-----------+--------+--------------------------+---------+
| ID | NAME | SEX | BIRTHDAY | SALARY |
+-----+-----------+--------+--------------------------+---------+
| 1 | jiangzz | true | 1990-12-16 00:00:00.000 | 5E+3 |
| 2 | zhangsan | false | 1990-12-16 00:00:00.000 | 6E+3 |
+-----+-----------+--------+--------------------------+---------+
2 rows selected (0.055 seconds)
0: jdbc:phoenix:CentOS> select * from t_user where name like '%an%' order by salary desc limit 10;
+-----+-----------+--------+--------------------------+---------+
| ID | NAME | SEX | BIRTHDAY | SALARY |
+-----+-----------+--------+--------------------------+---------+
| 2 | zhangsan | false | 1990-12-16 00:00:00.000 | 6E+3 |
| 1 | jiangzz | true | 1990-12-16 00:00:00.000 | 5E+3 |
+-----+-----------+--------+--------------------------+---------+
2 rows selected (0.136 seconds)
√代码集成Phoenix
JDBC集成
①将phoenix-{version}-client.jar驱动jar安装到本地maven仓库
C:\Users\513jiaoshiji>mvn install:install-file -DgroupId=org.apche.phoenix -DartifactId=phoenix -Dversion=phoenix-4.10-hbase-1.2 -Dpackaging=jar -Dfile=C:\Users\513jiaoshiji\Desktop\phoenix-4.10.0-HBase-1.2-client.jar
[INFO] Scanning for projects...
[INFO]
[INFO] ------------------< org.apache.maven:standalone-pom >-------------------
[INFO] Building Maven Stub Project (No POM) 1
[INFO] --------------------------------[ pom ]---------------------------------
[INFO]
[INFO] --- maven-install-plugin:2.4:install-file (default-cli) @ standalone-pom ---
[INFO] Installing C:\Users\513jiaoshiji\Desktop\phoenix-4.10.0-HBase-1.2-client.jar to D:\m2\org\apche\phoenix\phoenix\phoenix-4.10-hbase-1.2\phoenix-phoenix-4.10-hbase-1.2.jar
[INFO] Installing C:\Users\513JIA~1\AppData\Local\Temp\mvninstall6381038564796043649.pom to D:\m2\org\apche\phoenix\phoenix\phoenix-4.10-hbase-1.2\phoenix-phoenix-4.10-hbase-1.2.pom
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 0.657 s
[INFO] Finished at: 2020-10-13T11:39:16+08:00
[INFO] ------------------------------------------------------------------------
mvn install:install-file -DgroupId=groupID -DartifactId=artifactID -Dversion=版本 -Dpackaging=jar -Dfile=jar路径
②将hbase-site.xml文件导入到项目的Resource资源目录
③编写jdbc代码
Class.forName("org.apache.phoenix.jdbc.PhoenixDriver");
Connection conn = DriverManager.getConnection("jdbc:phoenix:CentOS:2181");
PreparedStatement pstm = conn.prepareStatement("select * from baizhi.t_user");
ResultSet resultSet = pstm.executeQuery();
while(resultSet.next()){
String name = resultSet.getString("name");
Integer id = resultSet.getInt("id");
System.out.println(id+"\t"+name);
}
resultSet.close();
pstm.close();
conn.close();
需要注意Phoenix的JDBC在执行修改的时候,默认情况下自动提交时false,这一点和MySQL或者Oracle不同。
Class.forName("org.apache.phoenix.jdbc.PhoenixDriver");
Connection conn = DriverManager.getConnection("jdbc:phoenix:CentOS:2181");
conn.setAutoCommit(true);//必须设置,否则数据不提交!
PreparedStatement pstm = conn.prepareStatement("upsert into baizhi.t_user(id,name,sex,birthDay,salary) values(?,?,?,?,?)");
pstm.setInt(1,2);
pstm.setString(2,"张三1");
pstm.setBoolean(3,true);
pstm.setDate(4,new Date(System.currentTimeMillis()));
pstm.setBigDecimal(5,new BigDecimal(1000.0));
pstm.execute();
pstm.close();
conn.close();
MapReduce集成
①准备输入表/输出表
CREATE TABLE IF NOT EXISTS STOCK (
STOCK_NAME VARCHAR NOT NULL ,
RECORDING_YEAR INTEGER NOT NULL,
RECORDINGS_QUARTER DOUBLE array[] CONSTRAINT pk PRIMARY KEY (STOCK_NAME , RECORDING_YEAR)
);
CREATE TABLE IF NOT EXISTS STOCK_STATS (
STOCK_NAME VARCHAR NOT NULL ,
MAX_RECORDING DOUBLE CONSTRAINT pk PRIMARY KEY (STOCK_NAME)
);
②插入模拟数据
UPSERT into STOCK values ('AAPL',2009,ARRAY[85.88,91.04,88.5,90.3]);
UPSERT into STOCK values ('AAPL',2008,ARRAY[199.27,200.26,192.55,194.84]);
UPSERT into STOCK values ('AAPL',2007,ARRAY[86.29,86.58,81.90,83.80]);
UPSERT into STOCK values ('CSCO',2009,ARRAY[16.41,17.00,16.25,16.96]);
UPSERT into STOCK values ('CSCO',2008,ARRAY[27.00,27.30,26.21,26.54]);
UPSERT into STOCK values ('CSCO',2007,ARRAY[27.46,27.98,27.33,27.73]);
UPSERT into STOCK values ('CSCO',2006,ARRAY[17.21,17.49,17.18,17.45]);
UPSERT into STOCK values ('GOOG',2009,ARRAY[308.60,321.82,305.50,321.32]);
UPSERT into STOCK values ('GOOG',2008,ARRAY[692.87,697.37,677.73,685.19]);
UPSERT into STOCK values ('GOOG',2007,ARRAY[466.00,476.66,461.11,467.59]);
UPSERT into STOCK values ('GOOG',2006,ARRAY[422.52,435.67,418.22,435.23]);
UPSERT into STOCK values ('MSFT',2009,ARRAY[19.53,20.40,19.37,20.33]);
UPSERT into STOCK values ('MSFT',2008,ARRAY[35.79,35.96,35.00,35.22]);
UPSERT into STOCK values ('MSFT',2007,ARRAY[29.91,30.25,29.40,29.86]);
UPSERT into STOCK values ('MSFT',2006,ARRAY[26.25,27.00,26.10,26.84]);
UPSERT into STOCK values ('YHOO',2009,ARRAY[12.17,12.85,12.12,12.85]);
UPSERT into STOCK values ('YHOO',2008,ARRAY[23.80,24.15,23.60,23.72]);
UPSERT into STOCK values ('YHOO',2007,ARRAY[25.85,26.26,25.26,25.61]);
UPSERT into STOCK values ('YHOO',2006,ARRAY[39.69,41.22,38.79,40.91]);
③编写代码
public class PhoenixStockApplication extends Configured implements Tool {
public int run(String[] strings) throws Exception {
//1.创建job
Configuration conf = getConf();
conf.set(HConstants.ZOOKEEPER_QUORUM,"CentOS");
conf= HBaseConfiguration.create(conf);
Job job= Job.getInstance(conf,"PhoenixStockApplication");
job.setJarByClass(PhoenixStockApplication.class);
//2.设置输入输出格式
job.setInputFormatClass(PhoenixInputFormat.class);
job.setOutputFormatClass(PhoenixOutputFormat.class);
//3.设置数据读入和写出路径
String selectQuery = "SELECT STOCK_NAME,RECORDING_YEAR,RECORDINGS_QUARTER FROM STOCK ";
PhoenixMapReduceUtil.setInput(job, StockWritable.class, "STOCK", selectQuery);
PhoenixMapReduceUtil.setOutput(job, "STOCK_STATS", "STOCK_NAME,MAX_RECORDING");
//4.设置代码片段
job.setMapperClass(StockMapper.class);
job.setReducerClass(StockReducer.class);
//5.设置Mapper和Reducer端的输出key,value类型
job.setMapOutputKeyClass(Text.class);
job.setMapOutputValueClass(DoubleWritable.class);
job.setOutputKeyClass(NullWritable.class);
job.setOutputValueClass(StockWritable.class);
TableMapReduceUtil.addDependencyJars(job);
//6.任务提交
return job.waitForCompletion(true)?0:1;
}
public static void main(String[] args) throws Exception {
ToolRunner.run(new PhoenixStockApplication(),args);
}
}
public class StockWritable implements DBWritable {
private String stockName;
private int year;
private double[] recordings;
private double maxPrice;
public void write(PreparedStatement pstmt) throws SQLException {
pstmt.setString(1, stockName);
pstmt.setDouble(2, maxPrice);
}
public void readFields(ResultSet rs) throws SQLException {
stockName = rs.getString("STOCK_NAME");
year = rs.getInt("RECORDING_YEAR");
Array recordingsArray = rs.getArray("RECORDINGS_QUARTER");
recordings = (double[])recordingsArray.getArray();
}
//get/set
}
public class StockMapper extends Mapper<NullWritable,StockWritable, Text, DoubleWritable> {
@Override
protected void map(NullWritable key, StockWritable value, Context context) throws IOException, InterruptedException {
double[] recordings = value.getRecordings();
double maxPrice = Double.MIN_VALUE;
for (double recording : recordings) {
if(maxPrice<recording){
maxPrice=recording;
}
}
context.write(new Text(value.getStockName()),new DoubleWritable(maxPrice));
}
}
public class StockReducer extends Reducer<Text, DoubleWritable, NullWritable,StockWritable> {
@Override
protected void reduce(Text key, Iterable<DoubleWritable> values, Context context) throws IOException, InterruptedException {
Double maxPrice=Double.MIN_VALUE;
for (DoubleWritable value : values) {
double v = value.get();
if(maxPrice<v){
maxPrice=v;
}
}
StockWritable stockWritable = new StockWritable();
stockWritable.setStockName(key.toString());
stockWritable.setMaxPrice(maxPrice);
context.write(NullWritable.get(),stockWritable);
}
}
需要将运行的jar添加到hadoop的类路径下。
Phoenix GUI使用
如果您希望使用客户端GUI与Phoenix进行交互,请下载并安装SQuirrel。由于Phoenix是JDBC驱动程序,因此与此类工具的集成是无缝的。以下是下载和安装步骤:
点击:http://squirrel-sql.sourceforge.net/
1、下载客户端软件包,然后解压
2、将phoenix-{version}-client.jar拷贝到该软件的lib
目录下
3、直接点击该软件下的squirrel-sql.bat
如果是mac
或者linux
系统用户,可以直接运行squirrel-sql.sh
4、点击Dirver选项卡,点击+号,添加驱动
5、填写相关模板参数
6、点击Aliasses选项卡,添加+号,添加驱动
7、点击Test按钮,确保能够连接成功!
该客户端存在缺陷,不支持自定义Schema映射,因此需要将Hbase的schame映射给关闭才可以使用。