AWS POSTGRESQL CREATE ROLE CODE

You can see the code after you create the Lambda function and upload the code as shown in the following screenshot. For this post, we create the bucket pg-reports-testing and base folder excel_reports.Ĭreate a Lambda function using the code in zip_rename_to_xlsx.py. Login to your AWS account and execute the following steps:Ĭreate an S3 bucket and a base folder to store the Excel reports. The following are the detailed steps for implementation: Amazon S3 and Lambda Setup: The implementation is based on the scripts stored in the aws-aurora-rds-postgresql-excel-generator repository, which consists of the following files:Īws-aurora-rds-postgresql-excel-generator/sql/utl_file_utility.sql – This file contains the SQL procedures and functions (UTL_FILE) that help store the data in the S3 bucketsĪws-aurora-rds-postgresql-excel-generator/sql/PostgreSQL_excel_generator_pkg.sql – This file contains the SQL procedures and functions to format the data in an Excel styleĪws-aurora-rds-postgresql-excel-generator/sql/sample_proc_to_format_cells.sql – This file contains a sample procedure to process the data and generate CSV filesĪws-aurora-rds-postgresql-excel-generator/Python/zip_rename_to_xlsx.py – This Python script processes the CSV files and generates the Excel sheets

This section outlines the detailed steps required to implement the solution. You can follow the instructions in Setting up access to an Amazon S3 bucket or refer to the Create IAM policies and roles and Integrate Amazon S3 and Aurora PostgreSQL sections of Work with files in Amazon Aurora PostgreSQL and Amazon RDS for PostgreSQL.

Setup Amazon RDS for instance to access Amazon S3 bucket. The following are the prerequisites before you begin to implement the solution:Īn existing AWS account or create a new AWS accountĪn existing Amazon RDS for PostgreSQL database or create the database if you are creating a new AWS account.

AWS POSTGRESQL CREATE ROLE DOWNLOAD

The Lambda function stores the Excel file generated in the same S3 bucket.ĭatabase or application users can download the Excel reports to their local machines or implement scripts to store these reports in a centralized server. The aws_s3 functions invoke the Lambda function to process the CSV files in an S3 bucket and generate the Excel file as needed. The high-level steps for the solution are as follows:ĭatabase or application users run database procedures or functions to process the required data from a table or multiple tables based on the reporting data, and invoke the aws_s3 function to export the data as flat files to an S3 bucket. The following diagram illustrates the architecture used in this post. The solution consists of the following components: We are demonstrating the solution for Amazon RDS for PostgreSQL database in this post. Note that the solution works for both Amazon RDS for PostgreSQL and Amazon Aurora PostgreSQL-compatible databases. The solution comprises custom procedures and functions in the database, an AWS Lambda function built using Python, and an Amazon Simple Storage Service (Amazon S3) bucket to store the files. In this post, we walk you through a solution that provides a mechanism to generate Excel workbooks from Amazon Relational Database Service (Amazon RDS) for PostgreSQL or Amazon Aurora PostgreSQL-Compatible Edition databases. Data that is exported using the CSV format does not contain the additional info that simplifies visualizing data in Excel.

For example, Microsoft Excel, which is often used to perform additional analysis or charting on database data, has a feature that lets you keep data in multiple worksheets for other formatting. Some data extraction use cases may require additional steps. PostgreSQL provides the “COPY” command to assist with the generation of flat files. One method to do this is through flat files, such as comma-separated value lists (CSVs), as these let you transfer data in a common format that can be loaded into other systems. When using databases, you may need to move data into different systems, including other databases or other tools, to perform different analysis.

Aws postgresql create role