Can I load data to redshift serverless directly fom SQS

You're correct - you need some "middle" service that does the data ingest for you. Glue and Lambda (in that order) are probably the right things to look at but there are other ways such as running the data import in a container or on an EC2 instance.

You definitely have several optiions:

• You can use AWS Lambda to retrieve messages from the SQS queue. The Lambda function would then load the data into Redshift using the COPY command.
• You can use AWS Glue to orchestrate the data flow. A Glue trigger would be configured to watch the SQS queue. When a message is received, Glue would retrieve it and use a Glue job to load the data into Redshift.
• The AWS Data Pipeline service can also be used to set up a data flow that reads from SQS and loads into Redshift. A pipeline would be created with an SQS source and a Redshift destination.
• In all the above cases, the data would need to be first written to an S3 bucket from the Lambda/Glue job before using the COPYcommand to load it into Redshift. Redshift only supports loading data directly from S3 but not SQS. So an intermediate processing step is required to retrieve the messages from SQS and stage the data in S3.