hello - I am running aws athena on dagster serverless (via athena-dbt which uses pyathena) and getting the following error when running any query:

botocore.errorfactory.InvalidRequestException: An error occurred (InvalidRequestException) when calling the StartQueryExecution operation: The S3 location provided to save your query results is invalid. Please check your S3 location is correct and is in the same region and try again. If you continue to see the issue, contact customer support for further assistance.

, if I run the image and run it locally this error doesn't happen, any ideas what is happning?

Hi Leo - do you have example code that's triggering this? How are you specifying an s3 bucket / aws credentials?

it's generated by dbt, so it'll be a CTAS-like. I am specifying credentials and s3 output bucket using environmental variables passed during deploy, in particular

AWS_ATHENA_OUTPUT_BUCKET

is set to

s3://{bucket}/dbt/

What region is your S3 bucket in?

us-east-1, but i also have

AWS_DEFAULT_REGION

and

AWS_REGION

set to us-east-1

Yeah I was just wondering about that "Please check your S3 location is correct and is in the same region and try again" error message - i'm not an athena expert, so I'm not sure if that means "the same region as your credentials" or something else?

If it's possible to share the op that you're using that triggers this that would be helpful

this would be a dagster-dbt op, so I think it's probably

dagster_dbt.dbt_cloud_run_op

Got it - er I think it may be executing with dbt locally not dbt cloud? but point taken that it's dagster-dbt. And which env vars are you setting to tell it what AWS credentials to use?

I am setting

AWS_ACCESS_KEY_ID

and

AWS_SECRET_ACCESS_KEY

during deploy

as well as AWS_DEFAULT_REGION and AWS_REGION

yes

When you say "if I run the image and run it locally this error doesn't happen" - what command you running exactly to run it locally?

docker run -it <tag> sh

then

dbt build xyz

Serverless tasks currently run in AWS in a fargate task that isn't in us-east-1, but they shouldn't override any of those AWS_ variables that you set, so it's surprising that whatever dbt is using to authenticate with aws is behaving differently...

from what i can tell it's a mix of profile-based and environment-based auth, so I'd like to try setting an aws default profile in the image... i notice that there's a

dagster_cloud_pre_install.sh

that gets copied, would that be a good place to do taht?

checking with the team about that to double-check and will get back to you - that would be set at image build time though. just looking through https://boto3.amazonaws.com/v1/documentation/api/latest/guide/credentials.html - I would expect the two env vars you set to work based on the priority order they give there..

yes, we point it to the

AWS_REGION

variable (and then default it to

us-east-1

which service is expecting AWS_ATHENA_OUTPUT_BUCKET ? is that what you're pointing s3_staging_dir at in the profile?

yes, we map s3_staging_dir to AWS_ATHENA_OUTPUT_BUCKET

and it looks like the adapter is explicitly passing in the region... https://github.com/Tomme/dbt-athena/blob/83f775643ab6e1edea605bc3f187ef28c2d20947/dbt/adapters/athena/impl.py#L155

For the earlier question, you could put your config setup either in

dagster_cloud_pre_install.sh

or

dagster_cloud_post_install.sh

, depending on how you want the image layer caching to work (whether or not it depends on the code being copied over first).

we are using gitlab, so they are being set as

--env

arguments; I can see them in the env when I run the image

and just to confirm, you're not include an AWS profile or anything like that when you run it locally, right? By mounting volumes, etc.

update - i was running the job from the image using the dbt cli, but i tried out the dagster cli (

dagster job execute

) and it succeeded

hey @Leo Qin can you try providing

--env AWS_REGION=<your-region>

to the dagster-cloud deploy command?

hey joe - that is already part of the command

@Leo Qin are you sure the user associated with the AWS secrets has the correct permissions to run athena queries? Were you using those same creds when running locally?

the user has Administrator Access to the aws account

@Leo Qin can you look at cloudtrail events? There should be entries for the failed StartQueryExecution requests, if you could share the specifics of the request (userIdentity, region, source, userAgent) that'd be userful. We've verified that your config is being set properly on the run and are a little stumped atm.

i can see them - any particular event/request id to look for?

I don't have an event/request ids available unfortunately, you could maybe log the failed requests ids?

interesting, i'm actually not seeing ANY events with the access key i shipped in the past 12 hours (other than a few ListUserPolicies and GetCallerIdentity that I did)

oh that is interesting

access key status is active... plus i feel like you'd get a different error if it weren't

do you see any events that would've happened when you ran locally (using that access key)?

yes, i do see a few

StartQueryExecution

from my source ip address

@Leo Qin just for a sanity check, your aws account doesn't have any exotic deny iam policies that are blocking based on things like ip/region/etc?

don't think so, but I can ask around... any particular IPs or regions?

tbh I'm not sure of specifics but dagster cloud does run in us-west-2

yeah,. basic awscli calls from the docker image seem to work

when its running in dagster serverless or locally?

locally

sts.get_caller_identity()

would be useful as well as something that gets logged to cloudtrail https://docs.aws.amazon.com/IAM/latest/UserGuide/cloudtrail-integration.html

alright, so some simple boto calls worked! Was able to do sts.GetCallerIdentity and athena.ListDatabases and saw userAgent

"Boto3/1.24.63 Python/3.8.14 Linux/4.14.287-215.504.amzn2.x86_64 exec-env/AWS_ECS_FARGATE Botocore/1.27.71"

nice! was caller identity what you expected? did you see the event in your cloudtrail?

yes, the access key is as expected

gotcha so theres some disconnect between your dagster code and dbt

update: i turned on debugging and found that the problematic query is as follows:

-- /* {"app": "dbt", "dbt_version": "1.1.2", "profile_name": "data_infra_poc", "target_name": "dev", "connection_name": "list_awsdatacatalog"} */
select distinct schema_name
from awsdatacatalog.INFORMATION_SCHEMA.schemata

ahhh were you missing glue iam permissions?

the credentials have administrator access, so I don't think that is the issue

do other queries work?

yes - locally, from dbt and dagit

what about from serverless?

i was able to do a list databases from serverless using boto, yeah

hmm but no luck getting any dbt-athena operations to work?

not yet, but i'm tracing right now...

@Leo Qin i wonder if the aws region for the dbt profile is getting set correctly https://github.com/Tomme/dbt-athena#configuring-your-profile, if it was unset it might be whats throwing that error?

i just added a log to see what region was being passed to boto by pyathena and on cloud its

us-west-2

Do you have a callsite in pyathena by any chance? Like something we could point to in their github repo?

dbt-athena

creates an athena connection here (https://github.com/Tomme/dbt-athena/blob/83f775643ab6e1edea605bc3f187ef28c2d20947/dbt/adapters/athena/connections.py#L120-L172) which references

pyathena.connection.Connection

here (https://github.com/laughingman7743/PyAthena/blob/master/pyathena/connection.py)

I was under the impression that it came from the region_name in the profile here: https://github.com/Tomme/dbt-athena#configuring-your-profile

and i have mine configured to

us-east-1

in my project

and where did you log where it was saying us-west-2?

i added a log of

self.region_name

here: https://github.com/laughingman7743/PyAthena/blob/7cb509d15605d1fa45a6d52c8f217b089fd92bf1/pyathena/connection.py#L138

hooray!

I guess something is clobbering

AWS_REGION

wonderful 🎉

OK, we're pretty sure that this is because ECS Fargate (where serverless runs the tasks) automatically overrides those env vars. We'll make that much clearer in the docs / build script

and thanks for help in troubleshooting!