You can follow it's development and report any issues you are experiencing here: https://github.com/apache/kyuubi/tree/master/python/pyhive
PyHive is a collection of Python DB-API and SQLAlchemy interfaces for Presto , Hive and Trino.
frompyhiveimportpresto# or import hive or import trinocursor=presto.connect('localhost').cursor() # or use hive.connect or use trino.connectcursor.execute('SELECT * FROM my_awesome_data LIMIT 10')
printcursor.fetchone()
printcursor.fetchall()frompyhiveimporthivefromTCLIService.ttypesimportTOperationStatecursor=hive.connect('localhost').cursor()
cursor.execute('SELECT * FROM my_awesome_data LIMIT 10', async=True)
status=cursor.poll().operationStatewhilestatusin (TOperationState.INITIALIZED_STATE, TOperationState.RUNNING_STATE):
logs=cursor.fetch_logs()
formessageinlogs:
printmessage# If needed, an asynchronous query can be cancelled at any time with:# cursor.cancel()status=cursor.poll().operationStateprintcursor.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)First install this package to register it with SQLAlchemy, see entry_points in setup.py.
fromsqlalchemyimport*fromsqlalchemy.engineimportcreate_enginefromsqlalchemy.schemaimport*# Prestoengine=create_engine('presto://localhost:8080/hive/default')
# Trinoengine=create_engine('trino+pyhive://localhost:8080/hive/default')
# Hiveengine=create_engine('hive://localhost:10000/default')
# SQLAlchemy < 2.0logs=Table('my_awesome_data', MetaData(bind=engine), autoload=True)
printselect([func.count('*')], from_obj=logs).scalar()
# Hive + HTTPS + LDAP or basic Authengine=create_engine('hive+https://username:password@localhost:10000/')
logs=Table('my_awesome_data', MetaData(bind=engine), autoload=True)
printselect([func.count('*')], from_obj=logs).scalar()
# SQLAlchemy >= 2.0metadata_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')
withengine.connect() ascon:
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.
# DB-APIhive.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'})
# SQLAlchemycreate_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 LDAPcreate_engine(
'hive://user:password@host:10000/database',
connect_args={'auth': 'LDAP'},
)Install using
pip install 'pyhive[hive]'orpip install 'pyhive[hive_pure_sasl]'for the Hive interfacepip install 'pyhive[presto]'for the Presto interfacepip install 'pyhive[trino]'for the Trino interface
Note: 'pyhive[hive]' extras uses sasl that doesn't support Python 3.11, See github issue.
Hence PyHive also supports pure-sasl via additional extras 'pyhive[hive_pure_sasl]' which support Python 3.11.
PyHive works with
- Python 2.7 / Python 3
- For Presto: Presto installation
- For Trino: Trino installation
- For Hive: HiveServer2 daemon
See https://github.com/dropbox/PyHive/releases.
- Please fill out the Dropbox Contributor License Agreement at https://opensource.dropbox.com/cla/ and note this in your pull request.
- Changes must come with tests, with the exception of trivial things like fixing comments. See .travis.yml for the test environment setup.
- Notes on project scope:
- This project is intended to be a minimal Hive/Presto client that does that one thing and nothing else. Features that can be implemented on top of PyHive, such integration with your favorite data analysis library, are likely out of scope.
- We prefer having a small number of generic features over a large number of specialized, inflexible features.
For example, the Presto code takes an arbitrary
requests_sessionargument for customizing HTTP calls, as opposed to having a separate parameter/branch for eachrequestsoption.
You can setup test environment by following .travis.yaml in this repository. It uses Cloudera's CDH 5 which requires username and password for download.
It may not be feasible for everyone to get those credentials. Hence below are alternative instructions to setup test environment.
You can clone this repository which has Docker Compose setup for Presto and Hive. You can add below lines to its docker-compose.yaml to start Trino in same environment:
trino: image: trinodb/trino:351 ports: - "18080:18080" volumes: - ./trino:/etc/trino
Note: ./trino for docker volume defined above is trino config from PyHive repository
- Then run::
- docker-compose up -d
Run the following in an environment with Hive/Presto:
./scripts/make_test_tables.sh virtualenv --no-site-packages env source env/bin/activate pip install -e . pip install -r dev_requirements.txt py.test
WARNING: This drops/creates tables named one_row, one_row_complex, and many_rows, plus a
database called pyhive_test_database.
The TCLIService module is autogenerated using a TCLIService.thrift file. To update it, the
generate.py file can be used: python generate.py <TCLIServiceURL>. When left blank, the
version for Hive 2.3 will be downloaded.