PyHive: a DB-API and SQLAlchemy client for Presto, Hive and Trino

The problem: three query engines, one Python surface

HiveServer2 speaks Thrift, Presto and Trino speak HTTP with their own JSON protocols, and none of them is what a Python application should have to know about. The usual outcome is three clients, three connection styles and three error handling conventions in the same codebase.

PyHive is a collection of Python DB-API and SQLAlchemy interfaces for Presto, Hive and Trino. The point is not that any of those engines lacks a driver. The point is that PyHive presents all three through the same interface, so a caller writes cursor.execute and cursor.fetchall and does not branch on which server answered.

The DB-API half follows PEP 249, so PyHive code is shaped like any other Python database client, which means the familiar tools around it, such as pandas read_sql or an ORM, can sit on top without special cases. The SQLAlchemy half exists for the same reason from the other direction: it registers a dialect so the engine, and anything built on the engine, treats Hive or Trino as a SQL database.

The audience is a Python shop that queries a warehouse rather than an application database, and the typical users are data engineers, analysts writing scheduled jobs, and applications that sit in front of a Hive or Trino cluster. This is infrastructure code, and the surrounding warehouse is what matters.

The project also states its own scope philosophy, which is worth reading before you extend it. The intent is to be a minimal Hive or Presto client that does that one thing and nothing else. Features that can be built on top of PyHive, such as integration with your favourite data analysis library, are called out as likely out of scope, and a small number of generic features is preferred over a large number of specialised ones.

The install extras, and the sasl problem on Python 3.11

Installation is by extra, because each interface pulls a different transport dependency. The documentation lists pyhive[hive] or pyhive[hive_pure_sasl] for the Hive interface, pyhive[presto] for Presto and pyhive[trino] for Trino, and the setup file confirms the mapping. The Presto and Trino extras need requests, the Hive extras need sasl, thrift and thrift_sasl, the hive_pure_sasl extra swaps in pure-sasl, and there are separate extras for sqlalchemy and for kerberos, which brings requests_kerberos.

That Hive split is not a preference, it is a workaround, and the documentation explains why. The sasl package does not support Python 3.11, and the linked GitHub issue tracks that. So on Python 3.11 and later you install pyhive[hive_pure_sasl], which uses the pure-sasl implementation instead, and the same Thrift and thrift_sasl dependencies are kept. The base install requires future and python-dateutil.

The supported environments are listed as Python 2.7 or Python 3, on the Presto installation, the Trino installation, and the HiveServer2 daemon respectively.

The kerberos extra is worth a second look for anyone in an enterprise environment, since it exists as a first-class install target and appears in the test requirements, which means it is exercised rather than merely declared.

The practical install advice, then, is to pick the extra that matches your engine and your interpreter, and to avoid the plain hive extra on Python 3.11 unless you have a reason to pin an old runtime.

A first query through the DB-API interface

The basic usage is three lines, and the import is where you choose the engine:

    from pyhive import presto  # or import hive or import trino
    cursor = presto.connect('localhost').cursor()  # or use hive.connect or use trino.connect
    cursor.execute('SELECT * FROM my_awesome_data LIMIT 10')
    print cursor.fetchone()
    print cursor.fetchall()

Note that print is written as a statement rather than a function call, which is Python 2 syntax in an example that also claims Python 3 support. Under Python 3 those two lines need parentheses. It is a small thing, but it is the kind of detail that tells you the examples were written long ago and have not been refreshed.

The connect call takes a host, and for a full connection string the SQLAlchemy section shows the shape of the URL: a Presto engine at presto://localhost:8080/hive/default, a Trino engine at trino+pyhive://localhost:8080/hive/default, and a Hive engine at hive://localhost:10000/default.

For the SQLAlchemy route there is one installation step that is easy to miss. The documentation says to first install the package so that it registers itself with SQLAlchemy, and points at the entry_points declaration in setup.py. In other words, the SQLAlchemy dialect is exposed through the package entry point, so it appears only once the distribution is installed rather than when the source directory happens to be on the path.

Asynchronous execution and polling the operation state

Long running queries are the reason the Hive path has an asynchronous mode, and it works through the Thrift service types rather than through anything Hive specific. The example imports the operation state enum from the generated Thrift bindings:

    from pyhive import hive
    from TCLIService.ttypes import TOperationState
    cursor = hive.connect('localhost').cursor()
    cursor.execute('SELECT * FROM my_awesome_data LIMIT 10', async=True)

    status = cursor.poll().operationState
    while status in (TOperationState.INITIALIZED_STATE, TOperationState.RUNNING_STATE):
        logs = cursor.fetch_logs()
        for message in logs:
            print message

        # If needed, an asynchronous query can be cancelled at any time with:
        # cursor.cancel()

        status = cursor.poll().operationState

    print cursor.fetchall()

The shape is a poll loop. You start the query, then repeatedly ask for the operation state and, while the query is still initialised or running, drain the accumulated log messages. A cancel method is available for the case where you decide to abandon the query.

The keyword problem is handled in the documentation rather than ignored. In Python 3.7 async became a reserved word, so the keyword argument is written as async_=True in modern code, and the async=True spelling remains in the older example.

The TCLIService/ directory at the top of the repository is the generated Thrift bindings, and it ships as a package of its own, listed in the setup file alongside pyhive. That is worth knowing, because it means the operation state enum you poll against is part of the distributed package rather than something imported from an external service library.

SQLAlchemy dialects, and the 2.0 difference

The SQLAlchemy interface covers the same three engines through URL dialects, and the documentation shows both a pre-2.0 and a post-2.0 style.

The old style reflects an engine with a bound MetaData, autoloading the table definition from the database:

    engine = create_engine('presto://localhost:8080/hive/default')
    logs = Table('my_awesome_data', MetaData(bind=engine), autoload=True)
    print select([func.count('*')], from_obj=logs).scalar()

The new style builds the metadata explicitly, creates the table, inspects its columns and then inserts and selects rows through a connection:

    metadata_obj = MetaData()
    books = Table("books", metadata_obj, Column("id", Integer), Column("title", String), Column("primary_author", String))
    metadata_obj.create_all(engine)
    inspector = inspect(engine)
    inspector.get_columns('books')

The dialect prefixes carry meaning. Presto uses presto://, Trino uses trino+pyhive://, and Hive uses hive://. The +pyhive part is the dialect implementation, which is how you tell SQLAlchemy to use this project's Trino client rather than another one.

One caveat the project states plainly: query generation functionality is not exhaustive or fully tested, but there should be no problem with raw SQL. In other words, the ORM is there, and the mature path is emitting SQL yourself and passing it through.

The extras list a sqlalchemy requirement of 1.3.0 or higher, and the examples show both eras of the API, so the library supports the transition rather than assuming you have already migrated.

Session configuration: the arguments that reach the server

A query engine often needs per session settings, and the two interfaces expose them differently. Through the DB-API, Hive takes a configuration dictionary and Presto and Trino take session properties:

    # DB-API
    hive.connect('localhost', configuration={'hive.exec.reducers.max': '123'})
    presto.connect('localhost', session_props={'query_max_run_time': '1234m'})
    trino.connect('localhost',  session_props={'query_max_run_time': '1234m'})

Through SQLAlchemy the same settings are passed as connect arguments, which is how they reach a URL that carries credentials and a protocol:

    create_engine(
        'presto://user@host:443/hive',
        connect_args={'protocol': 'https',
                      'session_props': {'query_max_run_time': '1234m'}}
    )

The naming difference is not cosmetic. Hive calls them configuration and Presto and Trino call them session props, and the values are engine specific strings, so a value that makes sense for one server is meaningless to another. The examples also show HTTPS with LDAP or basic auth for Hive, and a separate SQLAlchemy form that passes the auth mode as connect_args.

The documentation also notes the underlying HTTP client can be customised: the Presto code takes an arbitrary requests_session argument so that HTTP behaviour is configured in one place, rather than having a separate parameter for every requests option. That is the generic-over-specialised principle stated in the scope section, applied in the code.

The project has been donated to Apache Kyuubi

The most important line in this repository is the banner at the top of the README. PyHive has been donated to Apache Kyuubi, and development and issue reporting are supposed to happen there, in the python/pyhive directory of the Kyuubi repository. Everything below that banner is labelled legacy notes and instructions.

The dates back that up. The published releases are v0.6.3 from 2020-08-05, v0.6.2 from 2020-03-16 and v0.5.1 from 2018-02-15. The last push to this repository was on 2026-04-13, so the code is not abandoned, but the released line is six years old and the active work has moved.

The licence deserves a note too. The licence field on the repository is recorded as a custom licence that GitHub cannot classify, while the setup file declares the Apache License, Version 2.0 and the classifiers list the Apache Software License. Read the LICENSE file in the repository before you rely on either, and if you are contributing, note that the project asks pull requests to reference the Dropbox Contributor License Agreement.

There is also a testing expectation. Changes must come with tests, with the exception of trivial things such as fixing comments, and the test environment is described through the .travis.yml file, which uses Cloudera's CDH 5 and needs credentials to download. The documentation acknowledges that this is impractical for many contributors and points to a Docker Compose setup for Presto and Hive as an alternative.

The contributor's guidance is short enough to repeat here, because it explains the design. Small number of generic features over a large number of specialised and inflexible ones. One thing done well.

Editorial conclusion

Adopt PyHive only if you are maintaining code that already depends on it, or if you need a single Python driver that speaks HiveServer2, Presto and Trino with one API and no extra service. Do not start new work on it, because the project has been donated to Apache Kyuubi and the follow-on development lives there. Before you pin a version, read the LICENSE file in the repository, because the licence field on the repository and the licence string in setup.py do not agree, and confirm that the Hive extra you need is the pure-sasl one if you are on Python 3.11.

Frequently asked questions

Official sources

1. dropbox/PyHive on GitHub
2. Issues
3. README
4. Releases