大数据开发phoenix连接hbase流程详解

一、安装phoennix添加配置

1、将phoenix-server-hbase-2.4-5.1.2.jar拷贝至hbase的的lib下

cp phoenix-server-hbase-2.4-5.1.2.jar ../hbase/lib/

2、配置phoenix可以访问hbase的系统表

（1）将以下配置添加至hbase-site.xml中

<property>
<name>phoenix.schema.isNamespaceMappingEnabled</name>
<value>true</value>
</property>
<property>
<name>phoenix.schema.mapSystemTablesToNamespace</name>
<value>true</value>
</property>

（2）将hbase-stie.xml拷贝到phoenix/bin目录下

cp ../hbase/conf/hbase-site.xml ../phoenix/bin/

二、启动phoenix服务

1、启动hbase

../hbase/bin/start-hbase.sh

2、启动phoenix

python3 ../phoenix/bin/sqlline.py server200:2181

server200:2181为zookeeper地址

三、phoenix常用语法

官网文档 https://phoenix.apache.org/language/index.html

(1)创建表

create table test1(id varchar primary key,a varchar,b varchar);

id主键可视为hbase的rowkey

(2)插入数据

upsert into TEST1 values(\’202211160089\’,\’liuping\’,\’chenyingying\’);

（3）查询数据

select * from TEST1;

(4)视图/表映射

由于phoenix 无法直接访问hbase创建的非系统表，可以通过视图/表映射对非系统表进行查询，但视图不可修改，表映射可读可写

在hbase上创建表名为eftb列族为fm1 、fm2的表

create \’reftb\’,\’fm1\’,\’fm2\’

向表中添加数据

put \’reftb\’,\’010101\’,\’fm1:name\’,\’zhangsan\’
put \’reftb\’,\’010101\’,\’fm2:age\’,\’九千岁\’

<1>视图映射

create view \”reftb\”(id varchar primary key,\”fm1\”.\”name\” varchar,\”fm2\”.\”age\” varchar);

<2>表映射

create table \”reftb\”(id varchar primary key,\”fm1\”.\”name\” varchar,\”fm2\”.\”age\” varchar);

<3>查看数据

（5）添加修改数据（增改语法相同）

upsert into \”reftb\” values(\’010102\’,\’诸葛村夫\’,\’五十\’);
upsert into \”reftb\” values(\’010101\’,\’常山赵子龙\’,\’七十\’);

（6）删除数据

delete from \”reftb\” where ID=\’010101\’;

（7）创建schema(数据库名，对用hbase是的namespace)

CREATE SCHEMA IF NOT EXISTS \”my_schema\”;

四、java代码集成phoenix

1、添加依赖

implementation \’org.apache.phoenix:phoenix-client-hbase-2.4:5.1.2\’

2、编写代码

public class PhoenixJdbcUtils {
private final static Logger LOGGER = LoggerFactory.getLogger(PhoenixJdbcUtils.class);
private static Connection connection;
static {
Properties properties =new Properties();
PhoenixDriver instance = PhoenixDriver.INSTANCE;
try {
connection = instance.connect(\”jdbc:phoenix:server200:2181\”, properties);
///connection = DriverManager.getConnection(\”jdbc:phoenix:server200:2181\”, properties);
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 插入数据
* @throws SQLException
*/
public static void testUpsertData() throws SQLException {
PreparedStatement psUpsert = connection.prepareStatement( \” upsert into \\\”reftb\\\” values(\’168936\’,\’刘备\’,\’63\’)\”);
boolean addData = psUpsert.execute();
LOGGER.info(\”addData———\”+addData);
connection.commit();
}
/**
* 查询数据
* @throws SQLException
*/
public static void testQueryData() throws SQLException {
PreparedStatement psQuery = connection.prepareStatement(\” select * from \\\”reftb\\\” \”);
ResultSet resultSet = psQuery.executeQuery();
while (resultSet.next()) {
LOGGER.info(\”id–{}\”,resultSet.getString(1));
LOGGER.info(\”name–{}\”,resultSet.getString(2));
LOGGER.info(\”age–{}\”,resultSet.getString(3));
}
}
public static void main(String[] args) {
try {
testQueryData();
testUpsertData();
} catch (Exception e) {
e.printStackTrace();
}
}
}