Load CSV from S3 into AWS RDS Aurora MySQL Serverless

Question

After looking around, I've found some documentation on reading a CSV from S3 into an AWS Aurora MySQL database table, but this does not seem to apply to SERVERLESS instances. Does anyone know how to do this?

This is roughly how one would proceed with a non-serverless instance:

with conn.cursor() as cur:
            query = """
                    LOAD DATA FROM S3 'filepath' 
                    INTO TABLE table 
                    FIELDS TERMINATED BY ','
                    LINES TERMINATED BY '\n'
                    """
            cur.execute(query)
            conn.commit()

In the serverless context, this produces the error:

Access denied; you need (at least one of) the LOAD FROM S3 privilege(s) for this operation

All solutions for this error are in terms of a non-serverless instance, and thus cannot be replicated in my use case.

It should be possible in serverless aurora, just the permission are not set right. Can you do this operation in classic Aurora instance? — Horatiu Jeflea
Yes it is possible in a classic Aurora instance -- documentation online indicates it's not possible in Serverless, so I'm hoping there is an alternative to load CSV data. — OJT
While you can't do this with the MySQL Serverless version, it is possible with the PostgreSQL version. You can do this using the aws_s3 extension with the aws_s3.table_import_from_s3 function and the credentials parameter (not the IAM role - that one cannot be assigned to the Serverless version of Aurora). — orcaman

The-Big-K The-Big-K · Accepted Answer · 2019-10-25T07:54:40

LOAD FROM S3 is not supported in Aurora Serverless. You'd need to stream data through the SQL layer for now. If this is a one time import, then create an Aurora provisioned cluster, load the data from S3 and backup restore into a serverless cluster.

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Integrating.LoadFromS3.html

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora-serverless.restorefromsnapshot.html

Load CSV from S3 into AWS RDS Aurora MySQL Serverless

1 Answers