2

I have a program, in which I have been using the phoenixdb package developed by Lukas Lalinsky but during the past few days it seems to have become very unstable. I think this is due to the size of the database (as it is constantly growing). By unstable I mean that around half my queries are failing with a runtime exception.

So I have moved on and tried to find a more stable way to connect with my Phoenix "server". Therefore I want to try out a JDBC connection. As far as I have understood Phoenix should have great integration with JDBC.

I do however have problems with understanding how to set up the initial connection.

I read the following Usage section of the JayDeBeApi package, but I don't know what the Driver Class is or where it is located? If I have to download it myself? How to set it up? And so forth.

I was hoping someone in here would know and hopefully explain it in detail.

Thanks!

EDIT:

I've managed to figure out that my connect statement should be something along this:

import jaybedeapi as jdbc
conn = jdbc.connect('org.apache.phoenix.jdbc.PhoenixDriver', ['jdbc:phoenix:<ip>:<port>:', '', ''], '<location-of-phoenix-client.jar>')

However I still don't know where to get my hands on that phoenix-client.jar file and how to reference to it.

Community
  • 1
  • 1
Zeliax
  • 4,987
  • 10
  • 51
  • 79
  • How familiar are you with Java and JDBC? I'd recommend putting Python to one side for a bit and getting a few simple Java classes working with your Phoenix database. Once that's done and working it should be straightforward to move to JayDeBeApi. – Luke Woodward Mar 27 '17 at 13:16
  • I'm pretty familiar with Java, however JDBC is a little out of my league. My problem is that I don't know which inputs to give to the JayDeBeApi.connect statement. As far as I have been able to gather from my Google searches I could use something called JPype to include my java classes, but that proves a little bit troublesome to install on Windows.. – Zeliax Mar 27 '17 at 13:37
  • in that case, learn JDBC first. Once you've learnt JDBC, you'll understand what to pass to JayDeBeApi. I don't believe anybody here will want to help someone to use JayDeBeApi if they don't already know JDBC. – Luke Woodward Mar 27 '17 at 14:18
  • Are you really using jaydebeapi3 as the title says or jaydebeapi on Python 3? If your're using the latter one, could you please update the title to avoid confusion? AFAIK jaydebeapi3 started as a fork to support Python 3 at a time when jaydebeapi didn't. Now jaydebeapi3 uses a different technology stack involving a socket technology instead of jpype or jython that jaydebapi uses. (I'm the author of jaydebeapi) – bastian Apr 04 '17 at 15:00
  • As far as I remember I installed only jaydebeapi3, and not jaydebeapi but I can check tomorrow morning. Thanks for letting me know, and I will ofc update the title if I'm wrong. – Zeliax Apr 04 '17 at 18:07
  • @bastian I've checked my python installation by doing a `pip freeze` and I don't have __jaydebeapi__ installed. I only have __JayDeBeApi3__. Here's a snippet of the output: `ipywidgets==5.2.2 isort==4.2.5 itsdangerous==0.24 JayDeBeApi3==1.3.2 jdcal==1.3 jedi==0.9.0` – Zeliax Apr 05 '17 at 08:31

1 Answers1

3

I managed to find the solution after having set up a Java project and testing out JDBC in that development environment and getting a successful connection.

To get the JDBC connection working in Java I used the JDBC driver found in the Phoenix distribution from Apache here. I used the driver that matched my Phoenix and HBase versions - phoenix-4.9.0-HBase-1.2-client.jar

Once that setup was completed and I could connect to Phoenix using Java I started trying to set it up using Python. I started a connection to Phoenix with the following: 

import jaydebeapi as jdbc
import os
cwd = os.getcwd()
jar = cwd + '/phoenix-4.9.0-HBase-1.2-client.jar'
drivername = 'org.apache.phoenix.jdbc.PhoenixDriver'
url = 'jdbc:phoenix:<ip>:<port>/'
conn = jdbc.connect(drivername, url, jar)

Now I had a successful connection through JDBC to Phoenix using Python. Hope someone else out there can use this question in the future.

I created a cursor using the following and could issue commands like in the following: 

cursor = conn.cursor()
sql = """SELECT ...."""
cursor.execute(sql)
resp = cursor.fetchone() # could use .fetchall() or .fetchmany() if needed

I hope this helps someone out there!

Zeliax
  • 4,987
  • 10
  • 51
  • 79
  • it should not be necessary to import and set up jpype. jaydebeapi does all that for you. Could you please check this and update your answer. Further more the shutdownJVM command does not work. AFAIK it is a JVM bug that has never been fixed. – bastian Mar 30 '17 at 20:53
  • Yeah. I noticed the jvm shutdown not working. I will try to see if JPype isn't needed. I will return once I have tested it. – Zeliax Mar 31 '17 at 08:42
  • @bastian I have not changed my answer to illustrate that JPype wasn't needed and tested that it wasn't. I do however find it a bit wierd as it didn't seem to work on my initial tests before I installed JPype. But now it does, so meh. – Zeliax Mar 31 '17 at 08:56
  • The first not should be a *Now* – Zeliax Apr 04 '17 at 18:09