How to connect to Amazon Redshift and Presto using SQLAlchemy?

514 views
Skip to first unread message

Preutti Puawade

unread,
Jun 24, 2019, 3:33:20 AM6/24/19
to sqlalchemy
Hi there,

It could be a non-sense question for you but for I've struggling on this for weeks and search result from google cannot help so I'm end up here.

Normally I've use SQL client program as SQLwokbench/J and it's using JDBC connection below to connect 3 databases 1 Presto, 2 Redshift.

jdbc:presto://bdp-e2e-presto.xxx.com:xxxx/hive?user=x...@xxxx.com&SSL=true&SSLTrustStorePath=C:/Users/Downloads/bdp-ts.jks

jdbc:redshift://xxx-edm-oregon-prod02.cgdmyxu24bfx.us-west-2.redshift.amazonaws.com:xxxx/bdpdw?tcpKeepAlive=true&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory

jdbc:redshift:iam://bdp-dp-redshift-hdd01:us-west-2/drive?tcpKeepAlive=true&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory&plugin_name=com.amazon.redshift.plugin.AdfsCredentialsProvider&idp_host=access.xxxx.com&idp_port=xxx&DbGroups=grp_hdd_r&preferred_role=arn:aws:iam::xxxxxxxxxxxx:role/AWS-BDP-ENG-REDSHIFT-HDD-Prod

Couple weeks ago I'm try to use sqlalchemy to connect to those databases. But I don't know how can I fit the above information into standard form of sqlalchemy like

dialect+driver://username:password@host:port/database

I'm trying this 

create_engine('jdbc:postgres:redshift://hgst-edm-oregon-prod02.cgdmyxu24bfx.us-west-2.redshift.amazonaws.com:xxxx/bdpdw?tcpKeepAlive=true&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory/vqaa')

and error message came up like this.

ArgumentError: Could not parse rfc1738 URL from string 'jdbc:postgres:redshift://hgst-edm-oregon-prod02.cgdmyxu24bfx.us-west-2.redshift.amazonaws.com:xxxx/bdpdw?tcpKeepAlive=true&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory/vqaa'

Please kindly advise. 

thank you.

Mike Bayer

unread,
Jun 24, 2019, 11:23:22 AM6/24/19
to sqlal...@googlegroups.com

On Mon, Jun 24, 2019, at 3:33 AM, Preutti Puawade wrote:
Hi there,

It could be a non-sense question for you but for I've struggling on this for weeks and search result from google cannot help so I'm end up here.

Normally I've use SQL client program as SQLwokbench/J and it's using JDBC connection below to connect 3 databases 1 Presto, 2 Redshift.

jdbc:presto://bdp-e2e-presto.xxx.com:xxxx/hive?user=x...@xxxx.com&SSL=true&SSLTrustStorePath=C:/Users/Downloads/bdp-ts.jks

jdbc:redshift://xxx-edm-oregon-prod02.cgdmyxu24bfx.us-west-2.redshift.amazonaws.com:xxxx/bdpdw?tcpKeepAlive=true&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory

jdbc:redshift:iam://bdp-dp-redshift-hdd01:us-west-2/drive?tcpKeepAlive=true&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory&plugin_name=com.amazon.redshift.plugin.AdfsCredentialsProvider&idp_host=access.xxxx.com&idp_port=xxx&DbGroups=grp_hdd_r&preferred_role=arn:aws:iam::xxxxxxxxxxxx:role/AWS-BDP-ENG-REDSHIFT-HDD-Prod

Couple weeks ago I'm try to use sqlalchemy to connect to those databases. But I don't know how can I fit the above information into standard form of sqlalchemy like

dialect+driver://username:password@host:port/database

I'm trying this 

create_engine('jdbc:postgres:redshift://hgst-edm-oregon-prod02.cgdmyxu24bfx.us-west-2.redshift.amazonaws.com:xxxx/bdpdw?tcpKeepAlive=true&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory/vqaa')

SQLAlchemy URLs don't use JDBC as that is a technology that only applies to the Java VM.    To connect to redshift from Python you should use the sqlalchemy-redshift driver from https://github.com/sqlalchemy-redshift/sqlalchemy-redshift .






and error message came up like this.

ArgumentError: Could not parse rfc1738 URL from string 'jdbc:postgres:redshift://hgst-edm-oregon-prod02.cgdmyxu24bfx.us-west-2.redshift.amazonaws.com:xxxx/bdpdw?tcpKeepAlive=true&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory/vqaa'

Please kindly advise. 

thank you.


--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
 
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Message has been deleted
Message has been deleted

Mike Bayer

unread,
Jun 25, 2019, 10:32:35 PM6/25/19
to noreply-spamdigest via sqlalchemy
please communicate with the sqlalchemy-redshift folks at https://github.com/sqlalchemy-redshift/sqlalchemy-redshift/issues as I know nothing about what special options it offers

On Tue, Jun 25, 2019, at 10:30 PM, Preutti Puawade wrote:
Hi,

When I set engine as below,


it's no error. But when I try to 

print(engine.table_names())

it gave me this error 

ProgrammingError: (psycopg2.ProgrammingError) invalid dsn: invalid connection option "tcpKeepAlive"
 (Background on this error at: http://sqlalche.me/e/f405)

and then I try this way

engine = sa.create_engine('redshift+psycopg2://user...@xxx-edm-oregon-prod02.cgdmyxu24bfx.us-west-2.redshift.amazonaws.com:5439/bdpdw')

then print(engine.table_names())

it gave me this error message

OperationalError: (psycopg2.OperationalError) fe_sendauth: no password supplied
 (Background on this error at: http://sqlalche.me/e/e3q8)

Not sure, how should i do?


On Monday, June 24, 2019 at 2:33:20 PM UTC+7, Preutti Puawade wrote:
Hi there,

It could be a non-sense question for you but for I've struggling on this for weeks and search result from google cannot help so I'm end up here.

Normally I've use SQL client program as SQLwokbench/J and it's using JDBC connection below to connect 3 databases 1 Presto, 2 Redshift.

jdbc:presto://bdp-e2e-presto.xxx.com:xxxx/hive?user=xxx@xxxx.com&SSL=true&SSLTrustStorePath=C:/Users/Downloads/bdp-ts.jks

jdbc:redshift://xxx-edm-oregon-prod02.cgdmyxu24bfx.us-west-2.redshift.amazonaws.com:xxxx/bdpdw?tcpKeepAlive=true&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory

jdbc:redshift:iam://bdp-dp-redshift-hdd01:us-west-2/drive?tcpKeepAlive=true&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory&plugin_name=com.amazon.redshift.plugin.AdfsCredentialsProvider&idp_host=access.xxxx.com&idp_port=xxx&DbGroups=grp_hdd_r&preferred_role=arn:aws:iam::xxxxxxxxxxxx:role/AWS-BDP-ENG-REDSHIFT-HDD-Prod

Couple weeks ago I'm try to use sqlalchemy to connect to those databases. But I don't know how can I fit the above information into standard form of sqlalchemy like

dialect+driver://username:password@host:port/database

I'm trying this 

create_engine('jdbc:postgres:redshift://hgst-edm-oregon-prod02.cgdmyxu24bfx.us-west-2.redshift.amazonaws.com:xxxx/bdpdw?tcpKeepAlive=true&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory/vqaa')

and error message came up like this.

ArgumentError: Could not parse rfc1738 URL from string 'jdbc:postgres:redshift://hgst-edm-oregon-prod02.cgdmyxu24bfx.us-west-2.redshift.amazonaws.com:xxxx/bdpdw?tcpKeepAlive=true&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory/vqaa'

Please kindly advise. 

thank you.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
 
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Reply all
Reply to author
Forward
0 new messages