Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Insert data in AWS Redshift via AWS Lambda

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
like image 769
user5488652 Avatar asked Jan 25 '16 14:01

user5488652


People also ask

Can Lambda store data to Redshift?

Funnel's Data Warehouse connector can export all your data to S3, so once you have a file in your bucket all you need to do is to configure a Lambda to periodically import the data into Redshift.

How do I add data to Amazon Redshift?

Basic. The simplest way to insert a row in Redshift is to to use the INSERT INTO command and specify values for all columns. If you have 10 columns, you have to specify 10 values and they have to be in order how the table was defined:.

How do I connect Redshift to AWS Lambda?

Redshift Lambda Step 1: Download the AWS Lambda Amazon Redshift Database Loader. Redshift Lambda Step 2: Configure Amazon Redshift Cluster to Permit Access from External Sources. Redshift Lambda Step 3: Enable the Amazon Lambda Function.


1 Answers

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

like image 60
Jon Strayer Avatar answered Sep 19 '22 07:09

Jon Strayer