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 protected]"
        #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

You could try and use some print statements in your lambda handler ourtside of the IF statement to see if it actually enters that code block.. — vmachan
– vmachan, Commented Jan 25, 2016 at 14:17
I'd suggest adding in some logging so that you can see more clearly what is going on during the lifetime of your Lambda function execution. All that output block above shows is the timing and billing metrics of how long your function took to run. — wkl
– wkl, Commented Jan 25, 2016 at 14:27
@birryree : if i place "logger.info("text")" in my code. I can't check that in the logs file in amazon? Where can I check them? — user5488652
– user5488652, Commented Jan 25, 2016 at 14:46
@user5488652 The Amazon Cloudwatch logs for your lambda function will have the log output from your function. — wkl
– wkl, Commented Jan 25, 2016 at 14:49
@PhilMcParlane Is your issue concerning the logs (like it has been discussed above) or the actual lambda+redshift+python? I have something working on my side. Did you make sure to compile psycopg2 on an AMI and deliver its native dependencies as part of the package to upload to Lambda? — Ptah
– Ptah, Commented Apr 22, 2016 at 16:49

Jon Strayer · Accepted Answer · 2016-09-08 16:39:34Z

8

That's not an error. That's what success looks like.

answered Sep 8, 2016 at 16:39

Jon Strayer

2,0982 gold badges20 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert data in AWS Redshift via AWS Lambda

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related