Loading data into Amazon Redshift via Python Boto3

dianne_flores

I want to load data into amazon redshift from an s3 bucket using boto3 in python. I have the following code which is not working. can anyone help me with this? import boto3 import json import psycopg2 import pandas as pd s3 = boto3.resource('s3') def lambda_handler(event, context): try: bucket = s3.Bucket('mybucket') for obj in bucket.objects.filter(Prefix='folder1/folder2'): file_content = obj.get()['Body'].read().decode('utf-8') json_content = json.loads(file_content) df = pd.DataFrame(json_content) con = psycopg2.connect(dbname='mydb', host='mycluster.cluster-XXXXXXXXXXXXXXXX.us-east-1.redshift.amazonaws.com', port='5439', user='myuser', password='mypassword') df.to_sql('mytable', con=con, schema='myschema', if_exists='replace', index=False) con.close() return { 'statusCode': 200, 'body': json.dumps('Data loaded into redshift') } except Exception as e: print(e) return { 'statusCode': 500, 'body': json.dumps('Error') }

edgar_simmons

Loading data into Amazon Redshift via python boto3 can be done by creating a 'Table' object, setting the 'TableName' property, and calling the 'put_item' method. The following code shows an example:

import boto3

Create a Table object

table = boto3.resource('dynamodb').Table('my-table')

Set the TableName property

table.TableName = 'my-table'

Call the put_item method

table.put_item(Item={'id': 1, 'name': 'John'})

yellowkoala

The easiest way to load data into Amazon Redshift is to use the COPY command. This command can load data from Amazon S3, DynamoDB, and EMR clusters. The COPY command has a few different syntaxes, so be sure to check the Amazon Redshift documentation to see which one will work best for your data.

If you're using boto3 to interact with Amazon Redshift, you can use the following code to run a COPY command:


import boto3

redshift = boto3.client('redshift')

copy_command = "COPY {table_name} FROM '{s3_path}' CREDENTIALS 'aws_iam_role={iam_role}';"

redshift.execute(copy_command)

Replace {table_name} with the name of the table you want to load data into, {s3_path} with the path to the data in S3, and {iam_role} with the IAM role that gives Amazon Redshift access to your data.

For more information on how to load data into Amazon Redshift, check out this blog post: https://aws.amazon.com/blogs/big-data/loading-data-into-redshift-from-s3/.