PostgreSQL aws_s3 extension | An Introduction

Learn more about the PostgreSQL aws_s3 extension. Our PostgreSQL Support team is here to help you with your questions and concerns.

PostgreSQL aws_s3 extension | An Introduction

In the world of data management, moving data between storage solutions is a common challenge.
When dealing with AWS, data often resides in S3 but needs to be moved to a database like Postgres.

AWS offers a solution to this with the `aws_s3` extension for Postgres. This extension simplifies the process of loading data from S3 into a Postgres database.

An Example

Imagine we have a website that captures structured event data from its users. These events are stored in batches in an S3 bucket, and we want to analyze this data via a Postgres database.

1. To begin with, we need to add the `aws_s3` extension to our Postgres database.

   CREATE EXTENSION aws_s3;Copy Code
2. Next, we have to create a table in our Postgres database to store the event data. We have to make sure that the table structure matches the format of our data.

   
   CREATE TABLE events (
   event_id uuid primary key,
   event_name varchar(120) NOT NULL,
   event_value varchar(256) NOT NULL
   );
   Copy Code
3. Then, if we have the AWS access keys and the S3 bucket name where our data resides, we can use the `aws_s3.table_import_from_s3` function to import the S3 CSV file into our Postgres table.

   
   SELECT aws_s3.table_import_from_s3(
   'events', 'event_id,event_name,event_value',
   '(format csv, header true)',
   'bucket_name',
   'optional_folder_name/file_name',
   'region',
   'aws_access_key',
   'aws_secret_key',
   'optional_session_token'
   );
   Copy Code

   Here:

   • `events`: Name of the target table in Postgres.
   • `event_id,event_name,event_value`: Columns in the Postgres table to match the data columns.
   • `(format csv, header true)`: Specifies the file format.
   • `bucket_name`: Name of the S3 bucket where the data resides.
   • `optional_folder_name/file_name`: Path to the file within the bucket if it’s located in a specific folder.
   • `region`: AWS region where the S3 bucket is located.
   • `aws_access_key` and `aws_secret_key`: AWS access key and secret key.
   • `optional_session_token`: Session token, if required for authentication.

The `aws_s3` extension for Postgres simplifies the process of loading data from AWS S3 into a Postgres database. In other words, it helps avoid unnecessary data movements, reduces manual efforts, and keeps our data processing within the AWS ecosystem.

[Need assistance with a different issue? Our team is available 24/7.]

Conclusion

In brief, our Support Experts introduced us to the PostgreSQL aws_s3 extension with an example.