AWS Study Group

Tạo các tệp Excel từ Amazon RDS cho PostgreSQL hoặc Amazon Aurora PostgreSQL

Tháng 7 năm 2023: Bài viết này đã được xem xét để đảm bảo tính chính xác.

Khi sử dụng cơ sở dữ liệu, bạn có thể cần di chuyển dữ liệu vào các hệ thống khác, bao gồm cơ sở dữ liệu khác hoặc các công cụ khác, để thực hiện các phân tích khác nhau. Một phương pháp để làm điều này là thông qua các tệp phẳng, như danh sách giá trị ngăn cách bằng dấu phẩy (CSV), vì chúng cho phép bạn chuyển dữ liệu dưới dạng định dạng phổ biến có thể được nạp vào các hệ thống khác. PostgreSQL cung cấp lệnh “COPY” để hỗ trợ việc tạo các tệp phẳng.

Một số trường hợp sử dụng trích xuất dữ liệu có thể yêu cầu các bước bổ sung. Ví dụ, Microsoft Excel, thường được sử dụng để thực hiện phân tích hoặc biểu đồ thêm trên dữ liệu cơ sở dữ liệu, có một tính năng cho phép bạn giữ dữ liệu trong nhiều bảng tính cho các định dạng khác. Dữ liệu được xuất bằng định dạng CSV không chứa thông tin bổ sung giúp dễ dàng hình dung dữ liệu trong Excel.

Trong bài viết này, chúng tôi sẽ hướng dẫn bạn thông qua một giải pháp cung cấp cơ chế để tạo các tệp Excel từ dịch vụ  Amazon Relational Database Service (Amazon RDS) for PostgreSQL hoặc  Amazon Aurora PostgreSQL-Compatible Edition databases. Giải pháp bao gồm các thủ tục và chức năng tùy chỉnh trong cơ sở dữ liệu, một chức năng AWS Lambda được xây dựng bằng Python và một bucket Amazon Simple Storage Service (Amazon S3) để lưu trữ các tệp tin. Lưu ý rằng giải pháp này hoạt động cho cả Amazon RDS cho PostgreSQL và các cơ sở dữ liệu tương thích Amazon Aurora PostgreSQL. Chúng tôi đang minh họa giải pháp cho cơ sở dữ liệu Amazon RDS cho PostgreSQL trong bài viết này.

Tổng quan về giải pháp

Giải pháp bao gồm các thành phần sau:

  • Amazon RDS cho PostgreSQL
  • Amazon S3
  • AWS Lambda

Biểu đồ sau minh họa kiến trúc được sử dụng trong bài viết này.

Các bước cấp cao cho giải pháp là như sau:

  1. Người dùng cơ sở dữ liệu hoặc ứng dụng chạy các thủ tục hoặc chức năng cơ sở dữ liệu để xử lý dữ liệu cần thiết từ một bảng hoặc nhiều bảng dựa trên dữ liệu báo cáo và gọi chức năng aws_s3 để xuất dữ liệu dưới dạng tệp phẳng vào một bucket S3. Những tệp này có định dạng CSV.
  2. Các chức năng aws_s3 gọi chức năng Lambda để xử lý các tệp CSV trong một bucket S3 và tạo tệp Excel cần thiết.
  3. Chức năng Lambda lưu trữ tệp Excel được tạo trong cùng một bucket S3.
  4. Người dùng cơ sở dữ liệu hoặc ứng dụng có thể tải xuống các báo cáo Excel vào máy tính cục bộ của họ hoặc thực hiện các kịch bản để lưu trữ các báo cáo này trên một máy chủ tập trung.

Yêu cầu tiên quyết

Dưới đây là các yêu cầu tiên quyết trước khi bạn bắt đầu triển khai giải pháp:

Thực hiện giải pháp

Phần này sẽ trình bày các bước chi tiết cần thiết để triển khai giải pháp. Triển khai dựa trên các tập lệnh được lưu trữ trong kho aws-aurora-rds-postgresql-excel-generator, bao gồm các tệp sau:

  • aws-aurora-rds-postgresql-excel-generator/sql/utl_file_utility.sql – Tệp này chứa các thủ tục và hàm SQL (UTL_FILE) giúp lưu trữ dữ liệu trong các bucket S3
  • aws-aurora-rds-postgresql-excel-generator/sql/PostgreSQL_excel_generator_pkg.sql – Tệp này chứa các thủ tục và hàm SQL để định dạng dữ liệu theo kiểu Excel
  • aws-aurora-rds-postgresql-excel-generator/sql/sample_proc_to_format_cells.sql – Tệp này chứa một thủ tục mẫu để xử lý dữ liệu và tạo tệp CSV
  • aws-aurora-rds-postgresql-excel-generator/Python/zip_rename_to_xlsx.py – Tệp này chứa mã Python xử lý các tệp CSV và tạo các bảng tính Excel

Dưới đây là các bước chi tiết để triển khai:


Cài đặt Amazon S3 và Lambda:

Đăng nhập vào tài khoản AWS của bạn và thực hiện các bước sau:

  1. Tạo một bucket S3 và một thư mục gốc để lưu trữ các báo cáo Excel. Trong bài viết này, chúng ta tạo bucket pg-reports-testing và thư mục gốc là excel_reports.
  1. Tạo một chức năng Lambda bằng mã trong zip_rename_to_xlsx.py.

Bạn có thể xem mã sau khi tạo chức năng Lambda và tải mã lên như được hiển thị trong ảnh chụp màn hình dưới đây.

Bạn phải thêm các biến môi trường sau cho chức năng Lambda của bạn:

  • BUCKET_NAME – <your bucket name>
  • BASE_FOLDER – <your base folder>

Ảnh chụp màn hình sau đây là một tài liệu tham khảo. Đối với ví dụ của chúng tôi, tên bucket là pg-reports-testing và thư mục gốc là excel_reports.

Bước tiếp theo là cung cấp quyền truy cập vào cơ sở dữ liệu cho chức năng Lambda. Để biết hướng dẫn, tham khảo Khởi động một chức năng Lambda AWS từ một cụm DB Aurora PostgreSQL.

  1. Tạo một policies bằng mã như được hiển thị trong mẫu sau đây, cập nhật thuộc tính Resource với Amazon Resource Name (ARN) của chức năng Lambda của bạn.

{

  “Version”: “2012-10-17”,

  “Statement”: [

    {

      “Sid”: “AllowAccessToExampleFunction”,

      “Effect”: “Allow”,

      “Action”: “lambda:InvokeFunction”,

      “Resource”: “arn:aws:lambda:us-east-1:xxxx:function:CreateExcelReport”

    }

  ]

}

Một thông báo sẽ xuất hiện khi policies đã được tạo thành công.

  1. Tạo một role cho Amazon RDS cho PostgreSQL hoặc Aurora PostgreSQL bằng cách chọn RDS – Add Role to Database là mục đích sử dụng.
  1. Đính kèm policies vào role như được hiển thị trong ảnh chụp màn hình sau đây.
  1. Đính kèm role vào trường hợp như được hiển thị trong ảnh chụp màn hình sau đây.

Cài đặt PostgreSQL

Kết nối với trường hợp Amazon RDS for PostgreSQL của bạn và thực hiện các bước sau. Bạn phải chạy lệnh này với quyền rds_superuser:

  1. Kết nối với cơ sở dữ liệu của bạn và thêm tiện ích aws_s3.

psql -h pg-instance.xxxx.us-east-1.rds.amazonaws.com -p 5432 -U postgres -d excel_test

excel_test=> create extension aws_s3 CASCADE;

NOTICE: installing required extension “aws_commons”

CREATE EXTENSION

excel_test=> \dx aws_*

List of installed extensions

Name | Version | Schema | Description

————-+———+——–+———————————————

aws_commons | 1.2 | public | Common data types across AWS services

aws_s3 | 1.1 | public | AWS S3 extension for importing data from S3

(2 rows)

Lưu ý: Trong bài viết, đã tạo một cơ sở dữ liệu excel_test cho giải pháp.

  1. Cài đặt tiện ích UTL_FILE bằng cách chạy tập lệnh sql/utl_file_utility.sql trong cơ sở dữ liệu RDS cho PostgreSQL hoặc Aurora PostgreSQL.

Lưu ý rằng AWS Region nằm trong hàm init()của tập lệnh. Nếu bạn muốn sử dụng một AWS Region khác, bạn có thể thay đổi nó trong hàm trước khi chạy tập lệnh.


psql -h pg-instance.xxxx.us-east-1.rds.amazonaws.com -p 5432 -U postgres -d excel_test -f sql/utl_file_utility.sql > utl.log 2>&1 &

excel_test=> \dn utl_file_utility

List of schemas

Name | Owner

——————+———-

utl_file_utility | postgres

(1 row)

excel_test=> \df utl_file_utility.*

List of functions

Schema | Name | Result data type | Argument data types | Type

——————+————-+—————————-+——————————————————————————————————————————————-+——

utl_file_utility | fclose | numeric | p_path character varying, p_file_name character varying | func

utl_file_utility | fclose_csv | numeric | p_path character varying, p_file_name character varying | func

utl_file_utility | fclose_xlsx | numeric | p_path character varying, p_file_name character varying | func

utl_file_utility | fgetattr | bigint | p_path character varying, p_file_name character varying | func

utl_file_utility | fopen | utl_file_utility.file_type | p_path character varying, p_file_name character varying, p_mode character DEFAULT ‘W’::bpchar, OUT p_file_type utl_file_utility.file_type | func

utl_file_utility | get_line | text | p_path character varying, p_file_name character varying, p_buffer text | func

utl_file_utility | init | void | | func

utl_file_utility | is_open | boolean | p_path character varying, p_file_name character varying | func

utl_file_utility | put_line | boolean | p_path character varying, p_file_name character varying, p_line text, p_flag character DEFAULT ‘W’::bpchar | func

  1. Cập nhật chi tiết bucket và thư mục gốc bằng cách chèn một bản ghi vào bảng utl_file_utility.all_directories:


INSERT INTO utl_file_utility.all_directories VALUES (nextval(‘utl_file_utility.all_directories_id_seq’), ‘REPORTS’, ‘pg-reports-testing’, ‘excel_reports’);

Lưu ý rằng:

  • REPORTS là tên thư mục bạn sử dụng như một đầu vào khi tạo báo cáo
  • pg-reports-testing là tên bucket S3
  • excel_reports là một thư mục bên trong các bucket S3 để lưu trữ các tệp CSV.

excel_test=> SELECT * FROM utl_file_utility.all_directories;

id | directory_name | s3_bucket | s3_path

—-+—————-+——————–+—————

1 | REPORTS | pg-reports-testing | excel_reports

(1 row)

excel_test=>

Lưu ý rằng REPORTS là tên thư mục bạn sử dụng như một đầu vào khi tạo báo cáo. Nói cách khác, đó là việc phân loại báo cáo. Nếu bạn có nhiều báo cáo trong nhiều danh mục mà bạn lưu trong các bucket hoặc đường dẫn khác nhau, bạn có thể chèn nhiều hàng tương ứng.

  1. Cài đặt tiện ích Lambda bằng các lệnh sau:

excel_test=> CREATE EXTENSION IF NOT EXISTS aws_lambda CASCADE;

CREATE EXTENSION

excel_test=> \dx aws_la*

List of installed extensions

Name | Version | Schema | Description

————+———+——–+————————

aws_lambda | 1.0 | public | AWS Lambda integration

(1 row)

excel_test=>

  1. Cài đặt PGEXCEL_BUILDER_PKG bằng cách chạy tập lệnh sql/PostgreSQL_excel_generator_pkg.sql trong cơ sở dữ liệu Amazon RDS cho PostgreSQL hoặc Aurora PostgreSQL:


$ psql -h pg-instance.xxxx.us-east-1.rds.amazonaws.com -p 5432 -U postgres -d excel_test -f sql/PostgreSQL_excel_generator_pkg.sql > excel_procs.log 2>&1 &

excel_test=> \dn pgexcel*

List of schemas

Name | Owner

——————-+———-

pgexcel_generator | postgres

(1 row)

  1. Tạo một thủ tục mẫu để tạo báo cáo Excel bằng cách sử dụng tập lệnh sql/sample_proc_to_format_cells.sql


$ psql -h pg-instance.xxxx.us-east-1.rds.amazonaws.com -p 5432 -U postgres -d excel_test -f sql/sample_proc_to_format_cells.sql

excel_test=# \x

Expanded display is on.

excel_test=> \df sample_proc_to_format_cells

List of functions

-[ RECORD 1 ]——-+—————————————————————————————————————————————————————————————————————————————————————

Schema | public

Name | sample_proc_to_format_cells

Result data type |

Argument data types | p_directory character varying, p_report_name character varying, p_lambdafunctionname character varying, INOUT p_workbook pgexcel_generator.tp_book DEFAULT NULL::pgexcel_generator.tp_book, INOUT preturnval character varying DEFAULT NULL::character varying

Type | proc

  1. Tạo một bảng mẫu để xuất dữ liệu vào một trang công việc:


create table test_excel(id int,name varchar, eid varchar, mid varchar, nid int);

insert into test_excel values(generate_series(1,100),’name’||generate_series(1,100),’eid’||generate_series(1,100),’mid’||generate_series(1,100),generate_series(1,100));

  1. Chạy thủ tục sample_proc_to_format_cells:


call sample_proc_to_format_cells(‘REPORTS’,’excel_generate_test.xlsx’,’arn:aws:lambda:us-east-1:xxxxx:function:CreateExcelReport1′);

Lưu ý rằng bạn phải sử dụng ARN của chức năng Lambda của bạn như một đầu vào.

  1. Xác minh báo cáo Excel được tạo ra trong bucket S3.

Các bức ảnh chụp màn hình sau đây là ví dụ về các trang trong bảng tính Excel.



Cleanup

Dọn dẹp các tài nguyên đã tạo cho việc triển khai giải pháp sau khi bạn đã kiểm tra giải pháp.

Tóm lại

Trong bài viết này, chúng tôi đã hướng dẫn cách bạn có thể tạo các báo cáo bằng cách sử dụng công cụ aws-aurora-rds-postgresql-excel-generator. Mặc dù bài viết này cung cấp một thủ tục để tạo báo cáo đơn giản, bạn có thể sử dụng các chức năng Excel để có định dạng và số trang dựa trên yêu cầu của bạn.

Nếu bạn có bất kỳ câu hỏi hoặc đề xuất nào về bài viết này, hãy để lại một bình luận.

Xem bài viết gốc: https://aws.amazon.com/vi/blogs/database/generate-excel-workbooks-from-amazon-rds-for-postgresql-or-amazon-aurora-postgresql/

←
→

Leave a comment