MySQL Aurora and AWS S3: Need an alternate way of MySQL's "LOAD DATA" for loading doc-CodePudding

I need to import data from files stored in S3 into an MySQL Aurora db.

I have Eventbridge setup so when the file is added to S3 it fires an event that calls a lambda.

The lambda needs to import the file data into MySQL. The MySQL "LOAD DATA FROM S3" feature would be great for this..... but.... you will get the error: This command is not supported in the prepared statement protocol yet.

LOAD DATA has a lot of limitations such as this, it cannot be be in a stored procedure, cannot be in dynamic SQL (really needed here). I cannot find a hack work-around for this and need an alternate way to import data directly from S3 to MySQL. I don't want to move the data from S3 to Lambda to MySQL as that extra step in the middle adds a lot of exposure for failure.

Does anyone know any good ideas (and even not so good) for moving data from S3 to MySQL Aurora?

Thanks.

CodePudding user response：

One possible way to import data from Amazon S3 into a MySQL Aurora database is to use the mysql command-line client to connect to the database and run the LOAD DATA command. This allows you to specify the S3 location of the data file and the MySQL table where the data should be imported, and the mysql client will handle the details of transferring the data from S3 to the database.

Here is an example of how you might use the mysql command-line client to import data from S3 into a MySQL Aurora database:

Install the mysql command-line client on your local machine or on an EC2 instance.
Use the mysql client to connect to your MySQL Aurora database. You will need to specify the hostname, port, username, and password for your database. For example:

mysql -h mydb.cluster-xyz.us-east-1.rds.amazonaws.com -P 3306 -u myuser -p

Once you are connected to the database, use the LOAD DATA command to import the data from the S3 file into the MySQL table. You will need to specify the S3 location of the file, the name of the MySQL table where the data should be imported, and the format of the data in the file. For example:

LOAD DATA FROM S3 's3://mybucket/myfile.csv'
INTO TABLE mytable
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'

This LOAD DATA command will transfer the data from the myfile.csv file in the mybucket S3 bucket and import it into the mytable table in the MySQL Aurora database.

Note that you will need to grant the appropriate permissions to the mysql client and the EC2 instance (if applicable) to allow them to access the S3 bucket and read the data file. You can do this by attaching an IAM role with the appropriate S3 and RDS permissions to the EC2 instance, or by using IAM user credentials with the aws command-line tool to manage the permissions.

Overall, using the mysql command-line client to import data from S3 into a MySQL Aurora database can be a convenient and efficient way to transfer large amounts of data without having to move the data through an intermediate step. However, keep in mind that this approach may not be suitable for all use cases, and you may need to consider other options depending on your specific requirements and constraints.

If calling LOAD DATA from a lambda function is not an option try this:

aws s3 cp s3://mybucket/myfile.csv /tmp/myfile.csv

Then

mysqlimport -h myhost -P 3306 -u myuser -pmypassword mytable /tmp/myfile.csv

If the mysqlimport command is successful, it will return a message indicating that the data was imported

CodePudding user response：

Check whether the user has permission to import data, which requires AWS_LOAD_S3_ACCESS permission
Examples of import statements can be referred to as follows:

LOAD DATA FROM S3 's3://mybucket/data.txt'
     INTO TABLE table1
     (column1, column2)
     SET column3 = CURRENT_TIMESTAMP;

For more detailed information, please refer to https://docs.amazonaws.cn/en_us/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Integrating.LoadFromS3.html