Straight from the beginning when I have landed to our Hadoop project (HortonWorks) I have seen all our Python scripts directly connecting to our Hive server, with PyHive, bypassing Zookeeper coordination. I also noticed that every Beeline client connection where well using (obviously) the HiveServer2 JDBC URL. I have left this point open for later until we decided to improve our High Availability (HA) by making few components running on multiple servers. And when it came to our HiveServer2 that is now running on two edge nodes of our Hadoop cluster I have decided to dig out this thread…
The good way of connecting to HiveServer2 is to first get current status and configuration from Zookeeper and then use this information in PyHive (for example) to make a Hive connection. Zookeeper is acting here as a configuration keeper as well as an availability watcher, means Zookeeper will not return a dead HiveServer2 information.
Digging a bit on Internet I came quickly to the obvious conclusion that Kazoo Python package was a must try !
This blog post has been written using kazoo 2.5.0, Python 3.7.3. My Hadoop cluster is HortonWorks Data Platform (HDP) 2.6.4. All developed scripts are running on a Fedora 30 virtual machine.
Kazoo development environment installation
Anaconda is the preferred Python 3.7 environment management so started by downloading and installing it for Python 3.7 on my Fedora virtual machine. The release I have installed is:
As you have seen I am working with root which is a very bad idea so better to use a normal account (and keep root for more important tasks see below), to do so initialize Conda with (my shell is obviously bash):
[yjaquier@fedora1 ~]$ /opt/anaconda3/bin/conda init bash
no change /opt/anaconda3/condabin/conda
no change /opt/anaconda3/bin/conda
no change /opt/anaconda3/bin/conda-env
no change /opt/anaconda3/bin/activate
no change /opt/anaconda3/bin/deactivate
no change /opt/anaconda3/etc/profile.d/conda.sh
no change /opt/anaconda3/etc/fish/conf.d/conda.fish
no change /opt/anaconda3/shell/condabin/Conda.psm1
no change /opt/anaconda3/shell/condabin/conda-hook.ps1
no change /opt/anaconda3/lib/python3.7/site-packages/xonsh/conda.xsh
no change /opt/anaconda3/etc/profile.d/conda.csh
modified /home/yjaquier/.bashrc
==> For changes to take effect, close and re-open your current shell. <==
[yjaquier@fedora1 ~]$ /opt/anaconda3/bin/conda init bash
no change /opt/anaconda3/condabin/conda
no change /opt/anaconda3/bin/conda
no change /opt/anaconda3/bin/conda-env
no change /opt/anaconda3/bin/activate
no change /opt/anaconda3/bin/deactivate
no change /opt/anaconda3/etc/profile.d/conda.sh
no change /opt/anaconda3/etc/fish/conf.d/conda.fish
no change /opt/anaconda3/shell/condabin/Conda.psm1
no change /opt/anaconda3/shell/condabin/conda-hook.ps1
no change /opt/anaconda3/lib/python3.7/site-packages/xonsh/conda.xsh
no change /opt/anaconda3/etc/profile.d/conda.csh
modified /home/yjaquier/.bashrc==> For changes to take effect, close and re-open your current shell. <==
Logoff and logon again and use the newly created environment with:
You also need to configure your conda environment (.condarc) same as above…
For package management and search your reference will be https://anaconda.org. Here is an example of a search for Python (direct link is https://anaconda.org/search?q=python):
kazoo01
If you enter in the Python package most downloaded (good practice in my opinion) you will find the command to install it:
conda install-c conda-forge python
conda install -c conda-forge python
But then you cannot modify the environment with your own account and packages add must be done by root, which is, in my opinion, a very good practice:
EnvironmentNotWritableError: The current user does not have write permissions to the target environment.
environment location: /opt/anaconda3/envs/kazoo
uid: 1000
gid: 100
EnvironmentNotWritableError: The current user does not have write permissions to the target environment.
environment location: /opt/anaconda3/envs/kazoo
uid: 1000
gid: 100
So executing with root account (in kazoo conda environment):
The small source code (kazoo_testing.py file name in below) I have written is mainly coming (for the Kazoo part) from the official documentation so no restriction to visit it:
from kazoo.clientimport KazooClient,KazooState
def my_listener(state):
if state == KazooState.LOST:
# Register somewhere that the session was lostprint('Connection lost !!')elif state == KazooState.SUSPENDED:
# Handle being disconnected from Zookeeperprint('Connection suspended !!')else:
# Handle being connected/reconnected to Zookeeperprint('Connected !!')
zk = KazooClient(hosts='zookeeper_server01.domain.com:2181,zookeeper_server02.domain.com:2181,zookeeper_server03.domain.com:2181')
zk.add_listener(my_listener)#zk.start()
zk.start(timeout=5)# Display Zookeeper informationprint(zk.get_children('/'))#print(zk.get_children('hiveserver2')[0])print(zk.get_children(path='hiveserver2'))for hiveserver2 in zk.get_children(path='hiveserver2'):
array01=hiveserver2.split(';')[0].split('=')[1].split(':')
hive_hostname=array01[0]
hive_port=array01[1]print('Hive hostname: ' + hive_hostname)print('Hive port: ' + hive_port)
from kazoo.client import KazooClient,KazooStatedef my_listener(state):
if state == KazooState.LOST:
# Register somewhere that the session was lost
print('Connection lost !!')
elif state == KazooState.SUSPENDED:
# Handle being disconnected from Zookeeper
print('Connection suspended !!')
else:
# Handle being connected/reconnected to Zookeeper
print('Connected !!')zk = KazooClient(hosts='zookeeper_server01.domain.com:2181,zookeeper_server02.domain.com:2181,zookeeper_server03.domain.com:2181')zk.add_listener(my_listener)
#zk.start()
zk.start(timeout=5)# Display Zookeeper information
print(zk.get_children('/'))#print(zk.get_children('hiveserver2')[0])
print(zk.get_children(path='hiveserver2'))for hiveserver2 in zk.get_children(path='hiveserver2'):
array01=hiveserver2.split(';')[0].split('=')[1].split(':')
hive_hostname=array01[0]
hive_port=array01[1]
print('Hive hostname: ' + hive_hostname)
print('Hive port: ' + hive_port)
The list of Zookeeper server can be taken from the Hive Ambari page where you can copy/paste the so called HIVESERVER2 JDBC URL.
The above source code does not include the PyHive connection but once you get the Hive host name and port you can easily connect with something like (configuration parameter is optional):
from pyhive import hive
# Hive connection
connection=hive.connect(
host = hive_hostname,
port = hive_port,
configuration={'tez.queue.name': 'your_yarn_queue_name'},
username ="your_account")
pandas01=pd.read_sql("select * from ...", connection)print(pandas01.sample(10))
from pyhive import hive# Hive connection
connection=hive.connect(
host = hive_hostname,
port = hive_port,
configuration={'tez.queue.name': 'your_yarn_queue_name'},
username = "your_account"
)pandas01=pd.read_sql("select * from ...", connection)print(pandas01.sample(10))
Kazoo testing
I have the chance to have configured two HiveServer2 in my Hortonworks Hadoop cluster. Which is, by the way, strongly suggested if you aim to be Highly Available (HA). When the two HiveServer2 processes are up and running I get below result:
