How to specify output bucket for query results in s3? about pyathena HOT 9 CLOSED

Yes!

from pyathena.

see https://docs.aws.amazon.com/athena/latest/APIReference/API_ResultConfiguration.html#athena-Type-ResultConfiguration-OutputLocation

and https://github.com/laughingman7743/PyAthenaJDBC/blob/master/pyathenajdbc/connection.py#L127

from pyathena.

Use the s3_staging_dir option to connect().
https://pypi.org/project/PyAthena/

from pyathena.

Hi @jkleint, sorry for not being more explicit. the s3_staging_dir option, as far as I know, is for the source of the data to run the query against as configured in the athena/glue table.

What I'm looking for is the location of the output files that athena generates (csv, txt, meta). I want to store them in a bucket other than the data itself.

from pyathena.

@hero0926 here is the documentation from botocore and athena. it seems to have a Resultconfiguration

'ResultConfiguration': {
            'OutputLocation': 'string',
            'EncryptionConfiguration': {
                'EncryptionOption': 'SSE_S3'|'SSE_KMS'|'CSE_KMS',
                'KmsKey': 'string'
            }
        },

It is being used here: https://github.com/laughingman7743/PyAthena/blob/master/pyathena/connection.py#L66

and boto3 has something similar in start_query_execution:
https://boto3.amazonaws.com/v1/documentation/api/latest/reference/services/athena.html#Athena.Client.start_query_execution

from pyathena.

https://github.com/laughingman7743/PyAthena/blob/master/pyathena/common.py#L125
This is because the option name in the early release JDBC driver was s3_staging_dir.

from pyathena.

Thanks @laughingman7743, I get it. So s3_staging_dir in connect does NOT mean the root of the data, but rather the output folder for the result files, which will be used in execute as OutputLocation

is this correct?

from pyathena.

You can also change it when you run the query.
https://github.com/laughingman7743/PyAthena/blob/master/pyathena/cursor.py#L43

from pyathena.

@laughingman7743 would AWS_ATHENA_S3_STAGING_DIR environment also do it?

from pyathena.