Using python, standard approach to load data from S3 to AWS RDS Postgres?

Question

Per these AWS Amazon RDS docs, it looks like AWS offers an aws_s3 PostgreSQL extension for transferring data from S3 to Postgres in RDS.

We're using airflow to orchestrate our data ingestion pipelines, and it would be great if there was a python solution here. I have little experience with PostgreSQL and I've never used any PostgreSQL extensions, and being able to move data around using python is going to help us a ton. For the time being, we are avoiding AWS tools such as AWS Data Pipeline and AWS Glue in favor of building our own architecture with python and airflow.

For reference, we have the following for our GCP architecture for ingesting data from GCS into BigQuery using python:

from google.cloud import bigquery

# create BiqQuery client object + load job config
client = bigquery.Client()
job_config = bigquery.LoadJobConfig(
    schema=None, # autodetech for now
    source_format=bigquery.SourceFormat.NEWLINE_DELIMITED_JSON, # use ndjson
    write_disposition=bigquery.WriteDisposition.WRITE_APPEND, # append to existing
    autodetect=True
)
    
# and load into Bigquery
table_id = "our_gcp_project.our_model.our_table"
gcs_uri = "gs://our_bucket/path-to-our/file.json"
load_job = client.load_table_from_uri(gcs_uri, table_id, job_config=job_config) # location="US"  # Make an API request.
load_job.result()  # Waits for the job to complete

# check for success
destination_table = client.get_table(table_id)
print("Loaded {} rows.".format(destination_table.num_rows))

We're pretty much looking to port this code from GCS/BigQuery into S3/Postgres RDS, and want to get started in the right direction.

S3/Redshift looks more appropriate as the equivalent to GCS/BigQuery. You can use SQL commands to ingest data from S3 to Redshift. — kgiannakakis
– kgiannakakis, Commented Nov 15, 2021 at 20:56
What would you say is the difference between Redshift and Postgres for this? — Canovice
– Canovice, Commented Nov 15, 2021 at 21:16
Postgres is a transactional SQL database. Redshift is a Data Warehouse, similar to BigQuery. You shouldn't expect to be able to process big data with Postgres. — kgiannakakis
– kgiannakakis, Commented Nov 15, 2021 at 21:18
We're using postgres because we need to connect this database to our web application, to showcase data on our web app. (unlike our bigquery database,) this is not for big data analysis. — Canovice
– Canovice, Commented Nov 15, 2021 at 21:37

Tobie van der Merwe · Accepted Answer · 2021-11-15 21:16:13Z

1

You have the option in PostgreSQL to invoke Lambda functions.

PostgreSQL-Lambda

The Lambda Runtime can be set to use Python and you can use the Boto3 library to access the AWS services (Like S3) from the Lambda.

Boto3

Be aware of the limitations of Lambda like the maximum 15 minute run time and payload sizes.

Lambda Limits

Also when creating a Lambda that needs access to the DB you will need to create a layer that contains the drivers that you can assign to your Lambda.

Lambda Layers

answered Nov 15, 2021 at 21:16

Tobie van der Merwe

8057 silver badges13 bronze badges

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

Collectives™ on Stack Overflow

Using python, standard approach to load data from S3 to AWS RDS Postgres?

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