Insert data in AWS Redshift via AWS Lambda

Question

I'm trying to do the following:

When I upload a csv file in AWS S3, AWS Lambda need to detect it and create a table in AWS Redshift and store the data in it. This procedure works without lambda. But I want to automate it.

So, I made a lambda function to detect the upload op a csv file and so on..

Now after some error solving I get an error that says nothing to me..

Loading function
START RequestId: e8baee71-c36b-11e5-b1cb-87083ac95a25 Version: $LATEST
END RequestId: e8baee71-c36b-11e5-b1cb-87083ac95a25
REPORT RequestId: e8baee71-c36b-11e5-b1cb-87083ac95a25  Duration: 67.04 ms  Billed Duration: 100 ms     Memory Size: 512 MB Max Memory Used: 44 MB

This is my lambda python file. It is in the root of my zip file. In the zip file their is one other map 'psycopg2'

from __future__ import print_function

import json
import urllib
import boto3
import psycopg2
import linecache

print('Loading function')

s3 = boto3.client('s3')


def lambda_handler(event, context):
    bucket = event['Records'][0]['s3']['bucket']['name']
    key = urllib.unquote_plus(event['Records'][0]['s3']['object']['key']).decode('utf8')
    try:
        response = s3.get_object(Bucket=bucket, Key=key)

        #SEND MAIL WHEN CREATED

        #from = "email@gmail.com"
        #password = "password.email"
        #mail = smtplib.SMTP("smtp.gmail.com",587)
        #mail.ehlo()
        #mail.starttls()
        #mail.login(from,password)

        #recipient = "recipient.email"
        #mail.sendmail(from,recipient,key)


        #CREATE REDSHIFT TABLE WHEN CSV FILE UPLOADED
        if(key == "*.csv"):
            conn_string = "dbname=" + "xxxx" + " port=" + "5439" + " user=" + "yyyyy" + " password=" + "xxxxx*" + " host=" + "xxxxxxx.amazonaws.com";
            connection = psycopg2.connect(conn_string)
            cursor = connection.cursor();

            cursor.execute("select exists(select * from information_schema.tables where table_name=%s)", (key,))
            if(cursor.fetchone()[0]):
                return
            else:
                sqlcommand = 'create table ' + key + '('

                line = linecache.getline(key,1)
                line = line.replace(' ', '')
                line = line.replace('/', '')
                line = line.replace(':', '')
                line2 = linecache.getline(key,2)
                df1 = line
                df2 = line2
                output = ''
                output2 = ''
                for row1 in df1:
                    output = output + row1

                for row2 in df2:
                    output2 = output2 + row2

                new = output.split(',')
                new2 = output2.split(',')
                i = 0;
                for var in new:
                    new2[i] = new2[i].replace(' ', '')
                    sqlcommand = sqlcommand + var + ' ' + self._strType(new2[i])
                    i = i + 1;
                sqlcommand = sqlcommand[:-1]
                sqlcommand = sqlcommand + ');'

                cursor.execute(sqlcommand)
                connection.commit();

                print("CONTENT TYPE: " + response['ContentType'])
                return response['ContentType']
    except Exception as e:
        print(e)
        print('Error getting object {} from bucket {}. Make sure they exist and your bucket is in the same region as this function.'.format(key, bucket))
        raise e

Insert data in AWS Redshift via AWS Lambda

Answers (1)

Related Questions