Akshay Narang

 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: JSON Definition to create the Data Pipeline Using "Edit Architect" ...

This article provides you a unix shell script which creates following resources under your AWS account. You can download the script from the link "VPC Setup Script" AWS Resources Created by the script VPC with CIDR prefix 10.0.0.0/24 Public Subnet with CIDR prefix 10.0.0.0/25 Private Subnets with CIDR prefix 10.0.0.128/25 Internet Gateway  Routing Tables for public and private subnets NAT Gateway Elastic IP EC2 instance 1 of type t2.micro in private subnet EC2 instance 2 of type t2.micro in public subnet Please ensure to delete these resources when you don't need them or else you will be charged by Amazon as per the pricing policy for these services. Pre-requisites: This program uses AWS command line interface. If you do not have it already, then install AWS CLI before running this script. You can download and install AWS CLI using the  AWS CLI Installer Link .  Linux Shell Script Code:  Copy the below code and put it in a text file. Save the text file with .sh ...

This article provides you a windows batch script which creates following resources under your AWS account. You can download this script from the link " Batch Script to Create Custom VPC " AWS Resources Created by the script VPC with CIDR prefix 10.0.0.0/24 Public Subnet with CIDR prefix 10.0.0.0/25 Private Subnets with CIDR prefix 10.0.0.128/25 Internet Gateway  Routing Tables for public and private subnets NAT Gateway Elastic IP EC2 instance 1 of type t2.micro in private subnet EC2 instance 2 of type t2.micro in public subnet Please ensure to delete these resources when you don't need them or else you will be charged by Amazon as per the pricing policy for these services. Pre-requisites: This program uses AWS command line interface. If you do not have it already, then install AWS CLI before running this script. You can download and install AWS CLI using the  AWS CLI Installer Link .  Follow my previous blog post  How to install and configure AWS CLI  to fo...

Python Script/Code to Read CSV and Upload Data to DynamoDB Table import  boto3 import  csv import  json import  pandas  as  pd import  decimal access_key= 'ABCDEFGHIKLMNOPQR' secret_key= 'ZYXVUABCDEF+-GHIJKL' region= 'ap-south-1' session = boto3.Session(aws_access_key_id=access_key,aws_secret_access_key=secret_key,region_name=region) s3 = session.resource( 's3' ) file_url = s3.Bucket( 'source-bucket104' ).download_file( 'prices.csv' ,  'local_prices.csv' ) dataframe=pd.read_csv( 'local_prices.csv' ) db1=boto3.resource( 'dynamodb' ,aws_access_key_id=access_key,aws_secret_access_key=secret_key,region_name=region) table=db1.Table( 'prices' ) cnt= len (dataframe.index) for  i  in   range (cnt):   row=dataframe.head(i+ 1 ).tail( 1 ).to_json(orient= 'records' , lines= True )   row_dict=json.loads(row,parse_float = decimal.Decimal)   table.put_item(Item=row_dict)

Follow these steps to install and configure AWS CLI on Windows: Installation of AWS CLI Download the AWS CLI installer for Windows (64-bit) from AWS CLI Installer Link Run the installer and follow the prompts to install the CLI on your machine. Once the installation is completed. open the windows command prompt and type the command:  aws --version You should see the command output similar to below Configure AWS CLI access your cloud resources Login to AWS management console Goto Services -> IAM -> Users -> Add User Provide a new username to create. This user will be used to make connection to AWS resources using the CLI. Select AWS Access Type as Programmatic Access and Click "Next". In the next screen we have to provide the required permissions to this user. You will have to decide on what AWS resources this user can access and what kind of privileges are provided on these resources. There are 3 ways to assign permissions to this user: We can add user to an existing...

Below Lambda function can used to add records to a dynamodb table. It also provides the steps to read a csv file from S3 bucket: import  boto3 import  csv  import  json s3 = boto3.client( 's3' ) dynamodb = boto3.resource( 'dynamodb' ) def   lambda_handler ( event ,  context ):     bucket = event[ 'Records' ][ 0 ][ 's3' ][ 'bucket' ][ 'name' ]     key = event[ 'Records' ][ 0 ][ 's3' ][ 'object' ][ 'key' ]      print ( "Bucket=" +bucket)      print ( "Key=" +key)      if   '.csv'   not   in  key:        return   'Not a csv file'     s3.download_file(bucket, key,  '/tmp/'  + key)     x= 1      with   open ( '/tmp/'  + key,  'r' )  as  infile: ...

Use Case:  Let's assume that there is some process which uploads a csv file in a S3 bucket. This file contains the information about different products and their prices.  We need to create a Lambda function, which picks the file from S3 bucket as soon as it is uploaded and adds the records/items in this file to a dynamodb table. We will use AWS Lambda service to get this working. High Level Steps 1. Create a S3 bucket. 2. Create IAM policy and role. 3. Create DynamoDB table. 4. Create Lambda function. 5. Create Lambda Trigger. 6. Enable Cloudwatch. 7. Monitor the Lambda function execution in Cloudwatch Detailed Steps: Create a S3 bucket:              Create a S3 bucket with any name. Follow my  previous post  for the steps to create S3 bucket. Bucket name that I have used in this example is "source-bucket104". You can use any bucket name that is not already in use. Create IAM Policy: Create an IAM policy which provides read ac...

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": "...

In this article we will query data stored in S3 buckets using an interactive query service provided by AWS, i.e., Athena. We will perform below steps: Create a bucket in AWS S3. Create an external table in Athena which points to S3 bucket Upload csv data files to S3 bucket. Query data from S3 using AWS Athena service. Login to AWS management console. First we need to create a new bucket inside S3. A bucket in AWS S3 is a public cloud storage.  We can create a bucket and upload files using S3 API or management console. Click on Services -> S3 -> Buckets -> Create bucket On the following page provide a name for the bucket. You can use any name, but keep in mind that the bucket name should be unique among all AWS S3 buckets.  You can select any region of your choice and click Next On the next screen, you can provide below information for your S3 bucket. I am not changing any values and leaving it to their defaults. Versioning: Versioning enables you to keep multiple vers...

Installing Splunk on AWS EC2 - Red Hat Linux In this article we will see how to install Splunk on Red Hat Linux using Amazon AWS EC2 instance. We would first need to create a EC2 instance on Amazon AWS. Steps to Create EC2 Instance Login to your AWS console.  Under Services -> Click on EC2 Click on Launch Instance Choose an Amazon Machine Image (AMI). In my case case, I am using "Red Hat Enterprise Linux 8 (HVM)" that is available as free tier. Choose an Instance Type - General purpose - t2.micro, as this is eligible as free tier. Click on Review and Launch In the next step, it will show your EC2 instance configuration. Click on "Launch". Another window will open saying to "Select an existing key pair or create a new key pair". You will need to create a new key pair or choose an existing if you already have one. Key-pair file is a .pem file which is used to connect to your AWS EC2 instance using password less authentication. Download key pair file and C...