Category: Phoenix

Phoenix & Java: Connecting Secure

In this tutorial I will show you how to connect to an Secure Phoenix using Java. It’s rather straight forward.

POM.xml

<dependency>
	<groupId>org.apache.phoenix</groupId>
	<artifactId>phoenix-queryserver</artifactId>
	<version>5.0.0-HBase-2.0</version>
</dependency>

Imports:

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;

Initiate Kerberos Authentication

System.setProperty("java.security.krb5.conf", "C:\\Program Files\\Java\\jdk1.8.0_171\\jre\\lib\\security\\krb5.conf");
System.setProperty("java.security.krb5.realm", "REALM.CA");
System.setProperty("java.security.krb5.kdc", "REALM.CA");
System.setProperty("sun.security.krb5.debug", "true");
System.setProperty("javax.net.debug", "all");

Connect:

Now we create the connection.

Class.forName("org.apache.phoenix.jdbc.PhoenixDriver");
String url = "jdbc:phoenix:hadoop:2181:/hbase-secure:hbase/hadoop@REALM.CA:\\data\\hbase.service.keytab";
Connection connection = DriverManager.getConnection(url);

System.out.println("Connected");

Statement statement = connection.createStatement();

//Drop table
String deleteTableSql = "DROP TABLE IF EXISTS employee";		 
System.out.println("Deleting Table: " + deleteTableSql);
statement.executeUpdate(deleteTableSql);
System.out.println("Created Table");
 
//Create a table
String createTableSql = "CREATE TABLE employee ( eid bigint primary key, name varchar)";		 
System.out.println("Creating Table: " + createTableSql);
statement.executeUpdate(createTableSql);
System.out.println("Created Table");

//Insert Data
String insertTableSql = "UPSERT INTO employee VALUES(1, 'Oliver')";
System.out.println("Inserting Data: " + insertTableSql);
statement.executeUpdate(insertTableSql);
System.out.println("Inserted Data");

connection.commit();

//Select Data
String selectTablesSql = "select * from employee";
System.out.println("Show records: " + selectTablesSql);
ResultSet res = statement.executeQuery(selectTablesSql);
 
while (res.next()) {
	System.out.println(String.format("id: %s name: %s", res.getInt("eid"), res.getString("name")));
}

 

 

 

 

 

Phoenix: Kerberize Installation

In this tutorial I will show you how to use Kerberos with Phoenix. Before you begin ensure you have installed Kerberos Server, Hadoop, HBase and Zookeeper.

This assumes your hostname is “hadoop”

Install Phoenix

wget http://apache.forsale.plus/phoenix/apache-phoenix-5.0.0-HBase-2.0/bin/apache-phoenix-5.0.0-HBase-2.0-bin.tar.gz
tar -zxvf apache-phoenix-5.0.0-HBase-2.0-bin.tar.gz
sudo mv apache-phoenix-5.0.0-HBase-2.0-bin /usr/local/phoenix/
cd /usr/local/phoenix/

Setup .bashrc:

 sudo nano ~/.bashrc

Add the following to the end of the file.

#PHOENIX VARIABLES START
export PHOENIX_HOME=/usr/local/phoenix
export PHOENIX_CLASSPATH=$PHOENIX_HOME/*
export PATH=$PATH:$PHOENIX_HOME/bin
#PHOENIX VARIABLES END

 source ~/.bashrc

Link Files

ln -sf $HBASE_CONF_DIR/hbase-site.xml $PHOENIX_HOME/bin/hbase-site.xml
ln -sf $HADOOP_CONF_DIR/core-site.xml $PHOENIX_HOME/bin/core-site.xml
ln -sf $PHOENIX_HOME/phoenix-5.0.0-HBase-2.0-server.jar $HBASE_HOME/lib/phoenix-5.0.0-HBase-2.0-server.jar

hbase-env.sh

nano /usr/local/hbase/conf/hbase-env.sh

#Ensure the following env variables are set

export HADOOP_CONF_DIR=${HADOOP_CONF_DIR:-/usr/local/hadoop/etc/hadoop}
export PHOENIX_CLASSPATH=${PHOENIX_CLASSPATH:-/usr/local/phoenix}
export HBASE_CLASSPATH="$HBASE_CLASSPATH:$CLASSPATH:$HADOOP_CONF_DIR:$PHOENIX_CLASSPATH/phoenix-5.0.0-HBase-2.0-server.jar:$PHOENIX_CLASSPATH/phoenix-core-5.0.0-HBase-2.0.jar:$PHOENIX_CLASSPATH/phoenix-5.0.0-HBase-2.0-client.jar"

hbase-site.xml

nano /usr/local/hbase/conf/hbase-site.xml

#Add the following properties

<property>
	<name>phoenix.functions.allowUserDefinedFunctions</name>
	<value>true</value>
	<description>enable UDF functions</description>
</property>
<property>
	<name>hbase.regionserver.wal.codec</name>
	<value>org.apache.hadoop.hbase.regionserver.wal.IndexedWALEditCodec</value>
</property>
<property>
	<name>hbase.region.server.rpc.scheduler.factory.class</name>
	<value>org.apache.hadoop.hbase.ipc.PhoenixRpcSchedulerFactory</value>
	<description>Factory to create the Phoenix RPC Scheduler that uses separate queues for index and metadata updates</description>
</property>
<property>
	<name>hbase.rpc.controllerfactory.class</name>
	<value>org.apache.hadoop.hbase.ipc.controller.ServerRpcControllerFactory</value>
	<description>Factory to create the Phoenix RPC Scheduler that uses separate queues for index and metadata updates</description>
</property>
<property>
	<name>hbase.defaults.for.version.skip</name>
	<value>true</value>
</property>
<property>
	<name>phoenix.queryserver.http.port</name>
	<value>8765</value>
</property>
<property>
	<name>phoenix.queryserver.serialization</name>
	<value>PROTOBUF</value>
</property>
<property>
	<name>phoenix.queryserver.keytab.file</name>
	<value>/etc/security/keytabs/hbase.service.keytab</value>
</property>
<property>
	<name>phoenix.queryserver.kerberos.principal</name>
	<value>hbase/hadoop@REALM.CA</value>
</property>
<property>
	<name>hoenix.queryserver.http.keytab.file</name>
	<value>/etc/security/keytabs/hbaseHTTP.service.keytab</value>
</property>
<property>
	<name>phoenix.queryserver.http.kerberos.principal</name>
	<value>hbaseHTTP/hadoop@REALM.CA</value>
</property>
<property>
	<name>phoenix.queryserver.dns.nameserver</name>
	<value>hadoop</value>
</property>
<property>
	<name>phoenix.queryserver.dns.interface</name>
	<value>enp0s3</value>
</property>
<property>
		<name>phoenix.schema.mapSystemTablesToNamespace</name>
		<value>true</value>
</property>
<property>
		<name>phoenix.schema.isNamespaceMappingEnabled</name>
		<value>true</value>
</property>

sqlline.py

sqlline.py hadoop:2181:/hbase-secure:hbase/hadoop@GAUDREAULT_KDC.CA:/etc/security/keytabs/hbase.service.keytab

 

HBASE Phoenix & Java: Unsecure Connection

In this tutorial I will show you how to do a basic connection to remote unsecure HBase Pheonix Query Server using Java. Phoenix allows you to run SQL commands over top HBASE. You can find the commands listed here.

POM.xml:

<dependency>
	<groupId>org.apache.phoenix</groupId>
	<artifactId>phoenix-server-client</artifactId>
	<version>4.7.0-HBase-1.1</version>
</dependency>

Imports:

import java.sql.DriverManager;
import java.sql.SQLException;

Connect:

Class.forName("org.apache.phoenix.queryserver.client.Driver");
Connection conn = DriverManager.getConnection("jdbc:phoenix:thin:url=http://localhost:8765;serialization=PROTOBUF");