Import data from s3 to rds mysql

Load records from csv file in S3 file to RDS MySQL database using AWS Data Pipeline

In this post we will see how to create a data pipeline in AWS which picks data from S3 csv file and inserts records in RDS MySQL table.

I am using below csv file which contains a list of passengers.

CSV Data stored in the file Passenger.csv

Upload Passenger.csv file to S3 bucket using AWS ClI

In below screenshot I am connecting the RDS MySQL instance I have created in AWS and the definition of the table that I have created in the database testdb.

Once we have uploaded the csv file we will create the data pipeline. There are 2 ways to create the pipeline. 

• Using "Import Definition" option under AWS console.

                We can use import definition option while creating the new pipeline. This would need a json file which contains the definition of the pipeline in the json format. You can use my Github link below to download the JSON definition:

• Using "Edit Architect" option under AWS console.

1. Create data pipeline using architect.
2. Add a Copy activity
3. Define S3 Data node as input and MySQL Data node as output in the Copy Activity
4. Under S3 Data node add additional field "File Path" and provide the full S3 path of the csv file
5. Under S3 Data node add additional field "Data Format"
6. Under newly added Data format node specify CSV as the "Type".
7. Under MySQL data node add an optional field "Database" -> create new database
8. In the new database node, provide the details of your RDS MySQL instance. Do remember to specify the region where your RDS instance is located.
9. Under Configuration node set the "Failure and Rerun Mode" to "Cascade"
10. Under Copy Activity data node add additional field "Runs On" and create new resource
11. Under New Resource box provide "EC2Resource", as we will spin up a new EC2 instance to run the copy activity. We would also provide the type of EC2 instance that will be used by this copy activity. In my example, I am giving the value of "t2.micro", which is eligible for free tier.
12. You can also provide a "worker group" instead of using "runs on". You will have to install aws task runner on an existing EC2 instance of your choice to use it as a worker group. When using this option, the pipeline will not have to wait for the time it takes to spin up the EC2 instance, which is the case in using "runs on"
13. Save and Activate the Pipeline

Create data pipeline using architect.

Copy activity

S3 Datanode and MySQL DataNode

Run as EC2 Node

CSV Data Node and Configuration Node

RDS Database Node used by MySQL DataNode

Save and Activate the Pipeline

Configure Oracle ASM Disks on AIX You can use below steps to configure the new disks for ASM after the raw disks are added to your AIX server by your System/Infrastructure team experts: # /usr/sbin/lsdev -Cc disk The output from this command is similar to the following: hdisk9 Available 02-T1-01 PURE MPIO Drive (Fibre) hdisk10 Available 02-T1-01 PURE MPIO Drive (Fibre) If the new disks are not listed as available, then use the below command to configure the new disks. # /usr/sbin/cfgmgr Enter the following command to identify the device names for the physical disks that you want to use: # /usr/sbin/lspv | grep -i none This command displays information similar to the following for each disk that is not configured in a volume group: hdisk9     0000014652369872   None In the above example hdisk9 is the device name and  0000014652369872  is the physical volume ID (PVID). The disks that you want to use may have a PVID, but they must not belong to a volume group. PVID must be cleared for

In this article we will use AWS Lambda service to copy objects/files from one S3 bucket to another. Below are the steps we will follow in order to do that: Create two buckets in S3 for source and destination. Create an IAM role and policy which can read and write to buckets.  Create a Lamdba function to copy the objects between buckets. Assign IAM role to the Lambda function. Create an S3 event trigger to execute the Lambda function.  1. Create S3 Buckets: Create 2 buckets in S3 for source and destination. You can refer to my previous post for the steps about creating a S3 bucket. I have created the buckets highlighted in blue below, that I will be using in this example: 2. Create IAM Policy and Role: Now go to Security -> IAM (Identity and Access Management). Click on Policies -> Create policy Click on JSON tab and enter below lines. You will need to modify line number 10 and 18 with the source and destination buckets that you have created. {     "Version": "2012