SQLAlchemy with temporary IAM Role credentials about pyathena HOT 4 CLOSED

Thanks for the details of the error.
I also confirmed that the same error occurred in the following simple script.

from urllib.parse import quote_plus
from sqlalchemy.engine import create_engine
from sqlalchemy.sql.expression import select
from sqlalchemy.sql.functions import func
from sqlalchemy.sql.schema import Table, MetaData


def main():
    conn_str = 'awsathena+rest://:@athena.{region_name}.amazonaws.com:443/' \
               '{schema_name}?s3_staging_dir={s3_staging_dir}'
    engine = create_engine(conn_str.format(
        region_name='us-west-2',
        schema_name='default',
        s3_staging_dir=quote_plus('s3://BUCKET/path/to/')))
    many_rows = Table('many_rows', MetaData(bind=engine), autoload=True)
    print(select([func.count('*')], from_obj=many_rows).scalar())


if __name__ == '__main__':
    main()

If you do not specify the username (aws_access_key_id) and password (aws_secret_access_key) in the URI of SQLAlchemy, they are assumed to be empty strings.
By passing the SQLAlchemy URI to the connection object argument as follows, it was possible to connect without any problems.
https://github.com/laughingman7743/PyAthena/blob/master/pyathena/sqlalchemy_athena.py#L92

def create_connect_args(self, url):
    # Connection string format:
    #   awsathena+rest://
    #   {aws_access_key_id}:{aws_secret_access_key}@athena.{region_name}.amazonaws.com:443/
    #   {schema_name}?s3_staging_dir={s3_staging_dir}&...
    opts = {
        'aws_access_key_id': url.username if url.username else None,
        'aws_secret_access_key': url.password if url.password else None,
        'region_name': re.sub(r'^athena\.([a-z0-9-]+)\.amazonaws\.com$', r'\1', url.host),
        'schema_name': url.database if url.database else 'default'
    }
    opts.update(url.query)
    return [[], opts]

The fixed branch is /pull/55.
Please make sure. 🙏

from pyathena.

Do you connect with the following connection string in the current implementation?

  connection_string = (
        f'awsathena+rest://:@athena.{region}.'
        f'amazonaws.com:445/{database}?s3_staging_dir='
        f'{quote_plus(s3_staging_dir)}'
    )

The botocore's session object will call get_credentials if aws_acceess_key_id and aws_secret_access_key_id are not specified.
https://github.com/boto/botocore/blob/develop/botocore/session.py#L798
If an error occurs, please let me know the details of it.

from pyathena.

Thank you for the suggestion. I tried implementing that with the following connection string:

    connection_string = (
        f'awsathena+rest://:@athena.{region}.'
        f'amazonaws.com:445/{database}?s3_staging_dir='
        f'{quote_plus(s3_staging_dir)}'
    )

I then receive the following: (pyathena.error.DatabaseError) An error occurred (UnrecognizedClientException) when calling the StartQueryExecution operation: The security token included in the request is invalid.
From the stack trace I can see this starts at the botocore level: botocore.exceptions.ClientError: An error occurred (UnrecognizedClientException) when calling the StartQueryExecution operation: The security token included in the request is invalid.

I've tried this with both user credentials as well as IAM role temporary credentials, and in both cases receive the same error.

from pyathena.

💯

Tried it with this branch and it's all good!

By not passing in any credentials to the connection string, this will address the issue of re-fetching the credentials every time a new connection to the database is made (and won't be using expired credentials).

Thank you for addressing this issue.

from pyathena.