[SPIKE] Async `RedshiftOperator` about astronomer-providers HOT 3 CLOSED

Botocore async support issue is still open for async support on botocore and boto3. But this thread is quite a good read for various trade offs and async implementation for boto3.

boto3async supports all AWS client APIs. This library supports our scenarios for different amazon operators across most of the AWS services. But the issue is that it's not actively maintained and has just three stars.

pulsar-cloud provides two asynchronous implementations of botocore and some part of module is taken from aiobotocore. Its is not maintained actively.

aiobotocore is a async client for amazon services using botocore and aiohttp/asyncio. It seems quite reasonable library to go ahead with as it has 138 stars, active maintainer with mailing list, release versioning and proper documentation.

Supported AWS Services for aiobotocore

This library used in production with only S3, SQS and Dynamo services.
This is a non-exuastive list of what tests aiobotocore runs against AWS services. Not all methods are tested but we aim to test the majority of
commonly used methods.

Due to the way boto3 is implemented, its highly likely that even if services are not listed above that you can take any boto3.client('service') and
stick await infront of methods to make them async, e.g. await client.list_named_queries() would asynchronous list all of the named Athena queries.

from astronomer-providers.

aiobotocore

• Full Asynchronous version of botocore and aiohttp/asyncio for AWS services
• Most likely we can use most of the boto3 client API's and commands in an async manner just by prefixing the command with await.
• Only few service are tested and in working condition (S3, DynamoDB, SNS, SQS, CloudFormation, Kinesis)
• can be used above Python 3.6+
• As of now EC2 service is not supported
• active maintainer with mailing list, release versioning and proper documentation.

aioboto3

• Act as a wrapper combining boto3 and aiobotocore.
• We can use boto3 APIs in an asynchronous manner
• Only few service are tested and in working condition (DynamoDB Table, DynamoDB Service Resource, S3, Kinesis, SSM Parameter Store, Athena)
• As of now EC2 service is not supported
• still in not active as aiobotocore github

We can go ahead with aiobotocore library considering the support, bug fix and issues and recent commits in github, even aioboto3 uses aiobotocore.

from astronomer-providers.

Based on the existing sync version of AWS Redshift, these are all the following operator classes which needs Async versions.

• RedshiftSQLOperator
• AwsRedshiftClusterSensor
• S3ToRedshiftOperator

RedshiftSQLOperator and AwsRedshiftClusterSensor uses python redshift_connector library to connect to redshift db, and execute the queries these needs Async version implementation.

• RedshiftSQLHookAsync
• RedshiftTrigger
• RedshiftSQLOperatorAsync
• RedshiftHookAsync
• AwsRedshiftClusterSensorAsync
• S3ToRedshiftOperatorAsync

S3ToRedshiftOperator uses S3Hook which uses python boto3 library to connect to s3 and copy file from s3 to redshift by copy command these needs S3HookAsync async version which will be a part of #14 implementation once its done we need to implement the S3ToRedshiftOperator async version

from astronomer-providers.