一、安装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(); } } }