Sunday, August 29, 2010

JDBC Connectivity to Hypertable!

Hypertable (http://www.hypertable.org) is an implementation of the Google's BigTable which in short is a scalable, distributed sorted hashtable allowing for the storage of massive amounts of data across a cluster of commodity hardware. Hypertable has a rich query language (HQL) and a command line client akin to MySql's command line client; however, this can be limiting.

This article will cover two things:
1) JDBC Connectivity to Hypertable
2) Usage of this driver to graphically browse and modify a Hypertable instance

JDBC Driver

Hypertable uses the Thrift protocol to remotely connect and query an instance. This is a very useful method of reading/writing data but it's not standard and can be difficult to develop applications using Hypertable. For Ruby developers, there is a package called HyperRecord which provides an ActiveRecord like interface to Hypertable which makes integrating Hypertable into a Ruby (on Rails) application much easier. For Java, I recently released a JDBC driver that communicates through Thrift to Hypertable which now allows you to integrate Hypertable into a Java application via JDBC. One such application is integration with a graphical browser which was the motivation behind writing this driver.

Driver Limitations:
  1. Only the latest timestamped version data is returned.
  2. When retrieving metadata objects (ResultSet or Database), only the column families defined in the schema will be returned. In other words, column family qualifiers aren't going to be shown in the metadata objects although their value can be retrieved in code. Example: In code, you can do rs.getString("address:home") and rs.getString("address:work") while the metadata will only show "address" as a valid column name with no value unless 'address' explicitly has a value in the given table.
You can download the driver at http://github.com/downloads/ANithian/hyperjdbc/hypertable-jdbc_0.1.tar. Simply add all the jars to your classpath and use the driver "org.hokiesuns.hypertable.jdbc.HTDriver". An example URL is "jdbc:hypertable://192.168.116.128:38080" with no username, password or schema. An example application can be found by executing org.hokiesuns.hypertable.HypertableJDBCTester passing in the ThriftBroker hostname/ip as the command line argument.

Graphical Browser

The motivation behind writing this driver was to be able to view data in Hypertable using a graphical browser instead of the command line. There are numerous java based graphical browsers and the one that I tested with was SQLWorkBench/J (http://www.sql-workbench.net/). Rather than providing screenshots with explanations, I have created a video that should help show this driver in action and how easy it is to start talking to Hypertable using a third party graphical interface!



Conclusion

Along with integration for graphical browsing of Hypertable data, I believe that releasing a JDBC driver to Hypertable will help make its use more widespread and make it easier to plug this great technology into the vast Java application landscape. If you see any bugs, please file a bug report along with all the necessary information at http://github.com/anithian/hyperjdbc/issues

Posted by at
Labels: , , ,

22 comments:

  1. Lance N.February 19, 2011 at 4:40 PM

    Great! I'll check it out. I see you've worked with Solr- have you tried this with the DataImportHandler?

    ReplyDelete
  2. ANithianFebruary 20, 2011 at 1:30 AM

    Hi lance

    thanks for the comment. I have worked with the dih but haven't tried plugging this driver in yet. That sounds like a great idea though!

    ReplyDelete
  3. UnknownMarch 7, 2011 at 7:21 AM

    Hi Amit,

    i am an engineer running a datawarehouse. One of the Databases we are sourcing from will change from mysql to hypertable. Do you think your JDBC driver is performant enough to use it in Kettle for ETL purposes?

    ReplyDelete
  4. ANithianMarch 7, 2011 at 9:46 PM

    Hi primedape,

    I haven't done much with Kettle and unfortunately I also haven't done much performance testing of my JDBC driver; however, there isn't much complexity in there (if you look at the source it's only a handful of classes). I would be curious to see your results of any large job with this driver for it'd go a long way in it's adoption.

    Thanks!
    Amit

    ReplyDelete
  5. AnonymousJuly 19, 2013 at 1:50 AM

    org.apache.thrift.transport.TTransportException: java.net.ConnectException: Connection timed out: connect

    I did exactly same as in the above video. I'm getting exception, what to do?

    ReplyDelete
    Replies
    1. ANithianJuly 21, 2013 at 12:39 PM

      It looks like something is up with your hypertable connection. Are you sure that the thrift broker is running and that you can connect to it? If you continue to have difficulties, I'd suggest posting a question on the hypertable users mailing list. Thanks!

      Delete
  6. AnonymousJuly 21, 2013 at 10:06 PM

    Thanks for reply.

    Now I'm not getting that exception. Instead of another problem.

    URL:jdbc:hypertable://localhost:38030
    User:anyuser
    password:anypassword

    DB connected.

    When i execute command like, create table anytable("name", "age")

    it is showing :



    An error occurred when executing the SQL command:
    create table user("name","age")

    Error executing create table user("name","age") because of java.net.SocketException: Connection reset

    Execution time: 0.03s

    1 statement(s) failed.


    I think i did wrong in either port number/user/password/ip address. Can u plz help me to fix this error.

    ReplyDelete
    Replies
    1. ANithianJuly 22, 2013 at 9:13 PM

      Can you test that your HT installation is working by running the same HQL commands from the shell? i.e. ht shell?

      Delete
    2. AnonymousJuly 22, 2013 at 9:56 PM

      From ht shell i.e hypertable.exe, I can do all basic operations like create,delete ,select etc., Do you've sample code of java using hyper-table? In http://hypertable.com/documentation/code_examples/java they written code for hypertable-0.9.3.3 which is deprecated. I want java sample code for hypertable-0.9.7.8. One more doubt is, should I install thrift compulsory? I think to run java code thriftlib.jar required then hypertable_(current version).jar required. Unfortunately i dint get after searching in google. I want you to help me.

      Thanks.

      Delete
  7. AnonymousJuly 22, 2013 at 5:04 AM

    The link what you provided contains old hyper-table jars. Where do I get new versions of hyper-table jars? Please send the link or update..

    Thanks n advance.

    ReplyDelete
    Replies
    1. ANithianJuly 22, 2013 at 9:14 PM

      I don't have a copy of HT handy (as it's been a few years since I wrote this :-)) but if you have a copy installed, do a find for *.jar and you'll see the java bindings. It should be named similar to what is there now. Unfortunately I was going to migrate this to a Maven based project but didn't get the time to do so. I'm sure Doug would welcome the help!

      Delete
  8. AnonymousJuly 23, 2013 at 4:18 AM

    Thanks for reply.

    You know how to install and configure hyper-table in windows? I want clear explanation. I referred https://code.google.com/p/hypertable/wiki/DeployingHypertable but i dint get. Can u plz tel me step by step?

    Thanks.

    ReplyDelete
    Replies
    1. ANithianJuly 23, 2013 at 9:31 AM

      Unfortunately I don't know how to setup HT on Windows. I'd suggest emailing the users list (https://groups.google.com/forum/#!forum/hypertable-user) to get some answers. Sorry!

      Delete
  9. AnonymousSeptember 3, 2013 at 3:54 AM

    Hi,

    When I give className as "org.hokiesuns.hypertable.jdbc.HTDriver". It is showing the specified driver class(org.hokiesuns.hypertable.jdbc.HTDriver) is not available. What to do?

    Thanks.

    ReplyDelete
    Replies
    1. ANithianSeptember 3, 2013 at 8:09 PM

      Thanks for posting your question. Can you provide some more context? What's your classpath like? Are you in Eclipse or outside of it? More information the better. Thanks!

      Delete
  10. UnknownFebruary 10, 2014 at 5:31 AM

    Hi!

    I got ClientExeption(code:589827,message: Incalid namespace id:0) What can I do?

    Zoli

    ReplyDelete
    Replies
    1. ANithianFebruary 10, 2014 at 9:27 AM

      What's the URI that you are using to access the table? Also what version of Hypertable are you using this with (I haven't tested this in a while in case something changed with respect to the API).

      Delete
  11. UnknownFebruary 11, 2014 at 12:22 AM

    Hi

    jdbc:hypertable://10.0.201.5:38080
    I think it is connected to DB because I can see "ready, if you are" in left bottom corner.
    I've downloaded from http://ht4w.softdev.ch/index.php/downloads latgest version I think 0.9.7.14

    ReplyDelete
  12. ANithianFebruary 11, 2014 at 9:47 AM

    Try jdbc:hypertable://10.0.201.5:38080// ? or jdbc:hypertable://10.0.201.5:38080/ to force the use of the '/' namespace.

    ReplyDelete
  13. UnknownFebruary 13, 2014 at 4:08 AM

    Hi!
    I've got same result. :(

    I can see all service are runnint:
    Hyperspace.Master.exe
    Hypertable.LocalBroker.exe
    Hypertable.Master.exe
    Hyapertable.RangeServer.exe
    Hypertable.Service.exe
    Hypertable.ThriftBroker.exe

    ReplyDelete
  14. TejutejuNovember 15, 2018 at 9:36 PM

    very informative blog and useful article thank you for sharing with us , keep posting learn more Ruby on Rails Online Training Bangalore

    ReplyDelete
  15. John FeiMay 19, 2024 at 1:27 AM

    This website offers IoT development services that are innovative and comprehensive. The information provided is clear and informative, showcasing the company's expertise in creating cutting-edge IoT solutions. The layout is professional and easy to navigate, making it simple for visitors to learn about the services offered.

    ReplyDelete

Subscribe to: Post Comments (Atom)