大数据之Phoenix使用代码或客户端连接
原创
前言
Phoenix分为胖客户端和瘦客户端
- 瘦客户端通过Phoenix Query Server 来解析SQL语言。
- 胖客户端对SQL解析=>Hbase读写原理API=>直接作用于Zookeeper
注意
胖客户端不用启动Phoenix Query Server
Thin Client(瘦客户端)
1）启动query server
queryserver.py start
lsof -i:87652）创建项目并导入依赖
<dependency>
<groupId>org.apache.phoenix</groupId>
<artifactId>phoenix-queryserver-client</artifactId>
<version>6.0.0</version>
</dependency>3）编写代码
import java.sql.*;
public class PhoenixTest {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
Class.forName("org.apache.phoenix.queryserver.client.Driver");
String url = "jdbc:phoenix:thin:url=http://192.168.7.101:8765;serialization=PROTOBUF";
Connection conn = DriverManager.getConnection(url);
Statement st = conn.createStatement();
String sql = "select * from \"zdb\".\"tuser\"";
ResultSet rs = st.executeQuery(sql);
while (rs.next()) {
System.out.println("id=" + rs.getString("id") + ";name=" + rs.getString("name"));
}
rs.close();
st.close();
conn.close();
}
}添加数据
import java.sql.*;
public class PhoenixTest {
public static void main(String[] args) throws SQLException {
Connection conn = DriverManager.getConnection("jdbc:phoenix:thin:url=http://192.168.7.101:8765;serialization=PROTOBUF");
Statement st = conn.createStatement();
String sql = "upsert into MDB.TUSER(ID,NAME) VALUES ('" + 1005 + "','" + "wangwu" + "')";
System.out.println("Running: " + sql);
int i = st.executeUpdate(sql);
System.out.println(i);
conn.commit();
st.close();
conn.close();
}
}Thick Client(胖客户端)
1）在pom中加入依赖
<dependency>
<groupId>org.apache.phoenix</groupId>
<artifactId>phoenix-core</artifactId>
<version>5.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.phoenix</groupId>
<artifactId>phoenix-hbase-compat-2.1.6</artifactId>
<version>5.1.2</version>
</dependency>2）编写代码
import org.apache.phoenix.jdbc.PhoenixDriver;
import java.sql.*;
import java.util.Properties;
public class PhoenixTest {
public static void main(String[] args) throws Exception {
Properties properties = getProp();
PhoenixDriver instance = PhoenixDriver.INSTANCE;
Connection conn;
try {
conn = instance.connect("jdbc:phoenix:hadoop01,hadoop02,hadoop03:2181", properties);
PreparedStatement ps = conn.prepareStatement("select * from \"zdb\".\"tuser\"");
ResultSet rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getString(1) + ":" + rs.getString(2));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
private static Properties getProp() {
Properties prop = new Properties();
prop.put("phoenix.schema.isNamespaceMappingEnabled", "true");
prop.put("username", "");
prop.put("password", "");
prop.put("initialSize", 20);
prop.put("maxActive", 0);
prop.put("defaultAutoCommit", true);
return prop;
}
}Kerberos环境下连接
Kerberos环境下使用胖客户端连接
public static void loginPhoenix(String principal, String keytabPath) throws IOException, ClassNotFoundException, SQLException {
Configuration conf = ZLoadConfig.loadHbase();
Properties prop = new Properties();
conf.getValByRegex(".*?").forEach(prop::setProperty);
Class.forName("org.apache.phoenix.jdbc.PhoenixDriver");
// kerberos环境下Phoenix的jdbc字符串为 jdbc:phoenix:zk:2181:/znode:principal:keytab
String url = "jdbc:phoenix:hadoop01,hadoop02,hadoop03:/hbase:" + principal + ":" + keytabPath;
PhoenixConnection con = DriverManager.getConnection(url, prop).unwrap(PhoenixConnection.class);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM SYSTEM.CATALOG");
int n = rs.getMetaData().getColumnCount();
for (int i = 0; i < n; i++) {
String cn = rs.getMetaData().getColumnName(n);
System.out.println(cn);
}
rs.close();
stmt.close();
con.close();
}获取配置
public static Configuration loadHbase() {
String hadoopConfPath = getHadoopConfigRootPath();
String hbaseConfPath = getHbaseConfigRootPath();
Configuration config = HBaseConfiguration.create();
config.addResource(new Path(hadoopConfPath + "/core-site.xml"));
config.addResource(new Path(hadoopConfPath + "/hdfs-site.xml"));
config.addResource(new Path(hadoopConfPath + "/yarn-site.xml"));
config.addResource(new Path(hbaseConfPath += "/hbase-site.xml"));
return config;
}DBeaver连接
DBeaver中搜索phoenix
修改DBeaver配置
修改dbeaver.ini文件，增加如下内容
-vm
D:/Tools/Java/jdk1.8.0_102/bin连接配置
连接属性
image-20221215135637569
驱动属性中也添加
属性
phoenix.schema.isNamespaceMappingEnabled值
true自动下载的Jar版本较老
我们要引用对应版本的Jar
phoenix-client-hbase-2.1-5.1.2.jar服务配置
如果报错
CATALOG is found but client does not have phoenix.schema.isNamespaceMappingEnabled enabled
查询
select * from SYSTEM."CATALOG";查看表的 TABLE_SCHEM 发现有些表这个属性为空。 那么如果你没有指定自动映射命名空间，就会报错。
image-20221215130549298
在 hbase 的 conf 目录下 hbase-site.xml 文件加入下面的属性：
<property>
<name>phoenix.schema.isNamespaceMappingEnabled</name>
<value>true</value>
</property>然后，重启 hbase。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
评论
登录后参与评论
推荐阅读
目录