Akshay Narang

SQL Query select 'create table ' || table_name || ' (' || column_name || ' ' ||  DATA_TYPE || '(' ||  case data_type  when 'NUMBER' then data_precision when 'VARCHAR2' then CHAR_LENGTH when 'CHAR' then CHAR_LENGTH end  || '),' txt,table_name,column_id,data_type from user_tab_columns where table_name in (  'EMPLOYEE', 'EMPLOYEE_POSITION'  ) and column_id=1 union select column_name || ' ' ||  DATA_TYPE || '(' ||  case data_type  when 'NUMBER' then data_precision when 'VARCHAR2' then CHAR_LENGTH when 'CHAR' then CHAR_LENGTH end || '),' txt,table_name,column_id,data_type from user_tab_columns out1 where table_name in (  'EMPLOYEE', 'EMPLOYEE_POSITION' ) and column_id>1 and column_id<( select max(column_id) from user_tab_columns where table_name=out1.table_name ) union select column_name || ' ' ||  DATA_TYPE || '(' ||  case dat...

Bash Script that will recursively find all files under a directory and also under sub-directories. It will print all the files and also show the count for the words inside the files. count_words.sh for i in `find $1 -name "*" -type f` do wc -w $i done count_words.sh <directory_name>

 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)

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