PyHive Documentation

PyHive registers itself as a dialect for SQLAlchemy. You can create engines using the following connection strings:

• Presto: presto://<host>:<port>/<database>
• Trino: trino+pyhive://<host>:<port>/<database>
• Hive: hive://<host>:<port>/<database>

Note: Query generation functionality in SQLAlchemy is not exhaustive; using raw SQL via text() is recommended for complex queries.

from sqlalchemy import *
from sqlalchemy.engine import create_engine

# Presto
engine = create_engine('presto://localhost:8080/hive/default')
# Trino
engine = create_engine('trino+pyhive://localhost:8080/hive/default')
# Hive
engine = create_engine('hive://localhost:10000/default')

Install PyHive using pip with the appropriate extras for the service you intend to connect to:

• Hive: pip install 'pyhive[hive]' or pip install 'pyhive[hive_pure_sasl]' (use hive_pure_sasl if you are using Python 3.11+ as the standard sasl extra may not support it).
• Presto: pip install 'pyhive[presto]'
• Trino: pip install 'pyhive[trino]'

PyHive provides a standard Python DB-API interface for Presto, Hive, and Trino. You can connect to a local instance and execute queries using a cursor.

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

For Hive, you can execute queries asynchronously. This allows you to poll for the operation state and fetch logs while the query runs.

Note for Python 3.7+: Since async is a reserved keyword, use async_ instead of async in the execute method.

If you need to stop a running asynchronous query, use cursor.cancel().

from pyhive import hive
from TCLIService.ttypes import TOperationState

cursor = hive.connect('localhost').cursor()
# Use async_=True for Python 3.7+
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

    # To cancel: cursor.cancel()
    status = cursor.poll().operationState

print cursor.fetchall()

You can pass connection arguments to the underlying PyHive drivers using the connect_args parameter in create_engine.

# Presto/Trino with session properties
create_engine(
    'presto://user@host:443/hive',
    connect_args={'protocol': 'https', 'session_props': {'query_max_run_time': '1234m'}}
)

# Hive with configuration
create_engine(
    'hive://user@host:10000/database',
    connect_args={'configuration': {'hive.exec.reducers.max': '123'}},
)

# Hive with LDAP authentication
create_engine(
    'hive://user:password@host:10000/database',
    connect_args={'auth': 'LDAP'},
)

When using the direct DB-API connect methods, use the configuration or session_props arguments to pass driver-specific settings.

# Hive configuration
hive.connect('localhost', configuration={'hive.exec.reducers.max': '123'})

# Presto/Trino session properties
presto.connect('localhost', session_props={'query_max_run_time': '1234m'})
trino.connect('localhost', session_props={'query_max_run_time': '1234m'})