PyHive 0.7.0

DB-API

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()

DB-API (asynchronous)

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()

In Python 3.7 async became a keyword; you can use async_ instead:

cursor.execute('SELECT * FROM my_awesome_data LIMIT 10', async_=True)

SQLAlchemy

First install this package to register it with SQLAlchemy, see entry_points in setup.py.

from sqlalchemy import *
from sqlalchemy.engine import create_engine
from sqlalchemy.schema import *
# 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')

# SQLAlchemy < 2.0
logs = Table('my_awesome_data', MetaData(bind=engine), autoload=True)
print select([func.count('*')], from_obj=logs).scalar()

# Hive + HTTPS + LDAP or basic Auth
engine = create_engine('hive+https://username:password@localhost:10000/')
logs = Table('my_awesome_data', MetaData(bind=engine), autoload=True)
print select([func.count('*')], from_obj=logs).scalar()

# SQLAlchemy >= 2.0
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')

with engine.connect() as con:
    data = [{ "id": 1, "title": "The Hobbit", "primary_author": "Tolkien" },
            { "id": 2, "title": "The Silmarillion", "primary_author": "Tolkien" }]
    con.execute(books.insert(), data[0])
    result = con.execute(text("select * from books"))
    print(result.fetchall())

Note: query generation functionality is not exhaustive or fully tested, but there should be no problem with raw SQL.

Passing session configuration

# 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'})
# SQLAlchemy
create_engine(
    'presto://user@host:443/hive',
    connect_args={'protocol': 'https',
                  'session_props': {'query_max_run_time': '1234m'}}
)
create_engine(
    'trino+pyhive://user@host:443/hive',
    connect_args={'protocol': 'https',
                  'session_props': {'query_max_run_time': '1234m'}}
)
create_engine(
    'hive://user@host:10000/database',
    connect_args={'configuration': {'hive.exec.reducers.max': '123'}},
)
# SQLAlchemy with LDAP
create_engine(
    'hive://user:password@host:10000/database',
    connect_args={'auth': 'LDAP'},
)