Multi-threading issues on information_schema queries about dbt-athena HOT 3 CLOSED

Could be that the regression was introduced by Share same boto session between every calls, I honestly think that switching to Glue API is better.

from dbt-athena.

I could reproduce starting from this adapter's first release (1.0.4): https://github.com/dbt-athena/dbt-athena/pull/7/files
The PR that looks suspicious seems to be #16

I made a local change and reverted one line in connections.py and it deploys my project again with 8 threads 👌

            handle = AthenaConnection(
                s3_staging_dir=creds.s3_staging_dir,
                endpoint_url=creds.endpoint_url,
                schema_name=creds.schema,
                work_group=creds.work_group,
                cursor_class=AthenaCursor,
                formatter=AthenaParameterFormatter(),
                poll_interval=creds.poll_interval,
-               session=get_boto3_session(connection),
+               profile_name=creds.aws_profile_name,
                retry_config=RetryConfig(
                    attempt=creds.num_retries,
                    exceptions=(
                        "ThrottlingException",
                        "TooManyRequestsException",
                        "InternalServerException",
                    ),
                ),
            )

💡 If this only happens with the information_schema queries, we might fix this by calling the Glue API (faster) instead of querying information_schema in Athena? See suggestion: #4 (comment)

from dbt-athena.

I have a branch with the Glue API implemented instead of information_schema but the multi-threading error in botocore are still happening when I use more than 1-2 threads.

Found this to be very interesting:

• https://stackoverflow.com/questions/52820971/is-boto3-client-thread-safe
• boto/botocore#1246 (comment)
• https://boto3.amazonaws.com/v1/documentation/api/latest/guide/resources.html#multithreading-or-multiprocessing-with-resources

The tl;dr is

... for boto3 you can see that they recommend one client per session as there is shared data between instance that can be mutated by individual threads.

So you need one session per thread. We use 1 global session right now, which is causing the multi-threading errors: https://github.com/dbt-athena/dbt-athena/blob/main/dbt/adapters/athena/session.py#L9-L15C10

Which was introduced in Tomme/dbt-athena#125
I can create a fix where we change session.py to the code below. This runs perfectly with 8 threads:

import boto3.session
from dbt.contracts.connection import Connection


def get_boto3_session(connection: Connection = None) -> boto3.session.Session:
    if connection is None:
        raise RuntimeError(
            "A Connection object needs to be passed to initialize the boto3 session"
        )

    return boto3.session.Session(
        region_name=connection.credentials.region_name,
        profile_name=connection.credentials.aws_profile_name,
    )

It still respects the profile_name but the session is no longer global. Each thread creates one AthenaConnection with one get_boto3_session 👍

from dbt-athena.