3

I'm using JayDeBeAPI in PySpark (the Apache Spark Python API), and here's the beginning of my code (note, I'm actually running all this through an interactive shell with PySpark).

import jaydebeapi
import jpype

conn = jaydebeapi.connect('org.apache.phoenix.jdbc.PhoenixDriver',
                  ['jdbc:phoenix:hostname', '', ''])

I am querying Apache Phoenix, which is an SQL "front-end" for Apache HBase.

Here's my Python code for the SQL query:

curs = conn.cursor()
curs.execute('select "username",count("username") from "random_data" GROUP BY "username"')
curs.fetchall()

The output I'm getting is like this for all the rows:

(u'Username', <jpype._jclass.java.lang.Long object at 0x25d1e10>)

How can I fix it so that it actually shows the value of that returned column (the count column)?

From the Apache Phoenix datatypes page, the datatype of the count column is BIGINT, which is mapped to java.lang.Long, but for some reason jpype is not displaying the result.

I got JayDeBeAPI 0.1.4 and JPype 0.5.4.2 by python setup.py install when I downloaded them.

Alaa Ali
  • 896
  • 1
  • 12
  • 24

3 Answers3

11

The object returned by JPype is a Python version of Java's java.lang.Long class. To get the value out of it, use the value attribute:

>>> n = java.lang.Long(44)
>>> n
<jpype._jclass.java.lang.Long object at 0x2377390>
>>> n.value
44L

JayDeBeApi contains a dict (_DEFAULT_CONVERTERS) that maps types it recognises to functions that convert the Java values to Python values. This dict can be found at the bottom of __init__.py in the JayDeBeApi source code. BIGINT is not included in this dict, so objects of that database type don't get mapped out of Java objects into Python values.

It's fairly easy to modify JayDeBeApi to add support for BIGINTs. Edit the __init__.py file that contains most of the JayDeBeApi code and add the line

    'BIGINT': _java_to_py('longValue'),

to the _DEFAULT_CONVERTERS dict.

Luke Woodward
  • 63,336
  • 16
  • 89
  • 104
  • Thanks, I've added `BIGINT` to the dict. Is there a way to get rid of the tailing L without resorting to parsing? – Alaa Ali Nov 16 '14 at 16:56
  • @AlaaAli: The `L` at the end is just how Python 2 displays `long`s. `n.value` is a Python `long` with value 44, not a string `'44L'`. – Luke Woodward Nov 16 '14 at 22:29
  • I've added [an issue to the jaydebeapi github project](https://github.com/baztian/jaydebeapi/issues/3) – bastian Nov 30 '14 at 09:33
  • It should be fixed in https://github.com/baztian/jaydebeapi/tree/feature/improve-types-support. Appreciating any feedback. – bastian Feb 08 '15 at 15:20
  • Just an FYI -- This comment helped me bugfix a similar issue I ran into with migrating a legacy python program that used jaydebapi==0.2.0, JPype==0.6.3 and python 2.7.13, connecting to amazon redshift database. Error thrown was [UserWarning: No type mapping for JDBC type 'BIT']. Bugfix was to add the following lines of code: ```from jaydebeapi import _DEFAULT_CONVERTERS, _java_to_py _DEFAULT_CONVERTERS.update({'BIT':_java_to_py('booleanValue')}) ``` – Ouyang Ze Dec 07 '19 at 13:25
6

Adding to Luke's answer: in case modifying the source is not practical, you can add converters at runtime.

import jaydebeapi
from jaydebeapi.dbapi2 import _DEFAULT_CONVERTERS, _java_to_py
_DEFAULT_CONVERTERS.update({'BIGINT': _java_to_py('longValue')})

This fixed the issues I was having using jabdebeapi with Vertica.

dennisobrien
  • 1,148
  • 13
  • 7
  • 2
    This is a good fix, but in version `1.1.1` the line ` from jaydebeapi.dbapi2 import _DEFAULT_CONVERTERS, _java_to_py ` should be changed to ` from jaydebeapi import _DEFAULT_CONVERTERS, _java_to_py ` – tipanverella Jun 20 '17 at 11:34
0

The JayDeBeApi author adds that this method may need altering for very large integers, via the Java BigInteger's toString() method and then Python's int() function: https://github.com/baztian/jaydebeapi/issues/6

Rudolf Cardinal
  • 738
  • 8
  • 11