Python Access to Trino Cluster with PyHive

Trino access is represented by many Python libraries among those are Dropbox/PyHive, trinodb/trino-python-client, PySpark, and PyJDBC. Mostly of libraries use Python DB-API interface to access Trino which uniforms commands.

Dropbox/PyHive library is universal one as it can be used to access both Hive and Trino. As a result, the library utilizes the Hive query language rather than SQL. It can cause queries to produce unexpected results or failures. If you are interested in native Trino access, Python Access to Trino Cluster with Trino Client article describes Trino client library usage created by Trino community.

The sample is run with Python 3.8 in Windows with Starburst distribution.

1. Install PyHive library

Linux

With sudo access.

sudo pip3 install 'pyhive[trino]'

Windows

Run as administrator.

pip install 'pyhive[trino]'

Also, it's possible to install the library without elevated privileges in Linux and Windows.

pip3 install --user 'pyhive[trino]'

It installs only Trino interface.

2. Include requested libraries

Access to Trino cluster without password.
```
from pyhive import trino
```

Trino cluster is secured by password.

from pyhive import trino
from requests.auth import HTTPBasicAuth

3. Establish connection

Access to Trino cluster without password.

conn = trino.connect(host='localhost',
                     port=8080,
                     catalog='system',
                     schema='runtime')

Trino cluster is secured by password but skip SSL verification. This case might be used during development stage.

conn = trino.connect(host='localhost',
                     port=443,
                     protocol='https',
                     catalog='system',
                     schema='runtime',
                     requests_kwargs={'auth': HTTPBasicAuth('<user name>', '<password>'),
                                      'verify':False})

Trino cluster is secured by password.

Option #1. Follow instructions in Convert Java Keystore to PEM File Format article to create trino.pem file. The file contains Trino SSL public certificate converted from Java keystore file.

Option #2. Extract trino.pem certificate from Internet Browser. Follow Export TLS/SSL Server Certificate from Internet Browser article.

conn = trino.connect(host='localhost',
                     port=443,
                     protocol='https',
                     catalog='system',
                     schema='runtime',
                     requests_kwargs={'auth': HTTPBasicAuth('<user name>', '<password>'),
                                      'verify':'trino.pem'})

4. Create cursor

cur = conn.cursor()

5. Retrieve data

cur.execute('SELECT * FROM nodes')
for row in cur.fetchall():
    print(row)

6. Improvements

To disable insecure warnings during https requests if verify=False, add the code in import section.

import urllib3
urllib3.disable_warnings()

7. Troubleshooting

In case of getting ssl.SSLError: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed (_ssl.c:777) error, check your certificate expiration date. The date has to be valid.

Resources

SSL Cert Verification