how to specify https protcol in SQLAlchemy > Presto connection?

Question

I'm trying to run Hive queries via Presto using SQLAlchemy. It uses LDAP authentication but I'm missing something in the connection string.

from sqlalchemy.engine import create_engine

conn_string = 'presto://' + user + ':' + passw + '@' + host + ':' + port + db \
    + ", connect_args={'auth':LDAP}"
   
eng = create_engine(conn_string)

I'm getting an error that says:

Protocol must be https when passing a password

I've seen some discussion about this when searching but don't see a clear resolution. I've tried many combinations with and without the port, db, etc. Do you know how to do it? Thank you!

exhuma · Answer 1 · 2020-09-05T15:08:52.627

3

The README of PyHive contains this snippet:

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

It seems that all you need to do is add 'protocol': 'https' to the connect_args.

edited Sep 05 '20 at 15:08

answered Sep 05 '20 at 12:20

exhuma

20,071
12
90
123

I just noticed in that example they don't specify a password. Not sure how that could work. Anyway I did like that and now I get "A connection attempt failed because the connected party did not properly respond after a period of time," – Chuck Sep 05 '20 at 13:50

score 3 · Answer 2 · answered Sep 05 '20 at 13:56

I ended up using a different library that worked:

import prestodb

conn=prestodb.dbapi.connect(
    host=host,
    port=port,
    user=user,
    catalog='db_name',
    schema='my_schema',
    http_scheme='https',
    auth=prestodb.auth.BasicAuthentication(user, passw)
)

Then I was able to retrieve the results and put into a dataframe. So no need for SQLAlchemy. It seems unnecessarily complicated.

score 0 · Answer 3 · answered May 08 '21 at 08:45

from sqlalchemy.engine import create_engine engine = create_engine('presto://username:password@vmwidpappprd.corp.net.com:8443/hive', connect_args={'protocol': 'https', 'requests_kwargs': {'verify': False}}) db = engine.raw_connection() s=engine.execute("SELECT * FROM idp_dim_prd.sales_territory") print(s) for r in s: print(r)

how to specify https protcol in SQLAlchemy > Presto connection?

3 Answers3