can this be optional? for example if I run Lightdash in a self hosted environments (e.g. ECS) I would like to leverage the underlying role used by ECS to authenticate, without hard-coding credentials anywhere, simply because any call done by container are already authenticated.

They are, in fact that's how i was testing the build. The only thing needed is AWS_SDK_LOAD_CONFIG=true as environment variable.

Thanks for the clarification. So if I get right AWS_SDK_LOAD_CONFIG should be enough when running from ECS or EKS/pods with authentication via roles right?

Yup, I've added this to .env file, but it should definitely be mentioned in the docs.

this query is easy, but it's really really slow on scale. I've seen this query running ~2/3 minutes.
What we do in dbt-athena is to use glue apis. Also for example JDBC athena drivers uses the same concept, and in tools like Redash is possible to flag the usage of Glue APIs instead of using information_schema.

@hhhonzik I'm happy to have a chat with you to change the behvior of the above, and make the retrival of metadata faster. The API to use should be simply glue get_tables https://boto3.amazonaws.com/v1/documentation/api/latest/reference/services/glue/client/get_tables.html for example in boto3.

const getTables = (databaseName) => {
  const client = new GlueClient({});

  const command = new GetTablesCommand({
    DatabaseName: databaseName,
  });

  return client.send(command);
};

that supports only DatabaseName as filter, means that to to filter by a specific table we must do in memory.

just a thought, isn't there any JS library that takes care of the below? for example in python there is PyAthena, that pretty much take care of all the operations around firing a query and returning the result. I'm not really familiar with the JS world, but did you do a quick search to see if we can reusable some library? it will definitely reduce complexity.

There's athena-express - i would prefer that, but didn't want to add too many dependencies. I'll take this as a green light and use it :)

Perfect, just double check with the lightdash team and let's check if athena-express is well maintained. I see that it was published the last time 2 years ago, that is not a good indicator tbh.

I don't think we should use athena-express. Usage is dropping, there is no activity and they haven't been bumping their dependencies to patch security issues.
On the other hand, athena-express-plus ( fork from athena-express ) is gaining popularity. 👀

Note that we are switching our warehouse clients to stream the results instead of loading them all in memory. #10545
So we would need to adjust the code to support this as well.

Could we make the timeout configurable?
Also let me get it right, if a query takes more than 1 second will you timeout?

This is essentially a sleep, no? It's waiting one second before checking again for results.

@hhhonzik maybe the comment could clarify

Ah I got it, it's the waited time that determine how often we check when the query result is ready. In dbt-athena we call this poll_interval.

My suggestion would be to make the value configurable, with a default value of 500 ms.
I've seen very optimized tables and queries running below 1sec, often not the case, but definitely will for my serving layer I will aim to make my tables as optimized as possible.
Also, as suggested by @magnew let's add a comment please, to give more context 🙏🏻

AthenaCredentials?

@hhhonzik

Are database and schema effectively the same in Athena? Is there a reason to require both? I'm going to make database map to schema for now. If there is a reason we need to require both, let me know.

This is essentially a sleep, no? It's waiting one second before checking again for results.

@hhhonzik maybe the comment could clarify