Bagaimana cara menghubungkan database postgresql ke s3 bucket?

Pada artikel ini, kami akan menyelesaikan masalah kami dengan metode pertama, "salinan massal menggunakan boto3 dan psycopg2"

Diagram penyalinan massal Amazon S3 ke PostgreSQL (pada RDS).

Singkatnya, kami akan melakukannya

• unduh file CSV dari S3 ke penyimpanan lokal dengan boto3
• impor file-file ini ke PostgreSQL dengan psycopg2

Pro dan Kontra dari metode ini

Pro

• Konsumsi CPU dan memori minimum
• Waktu transfer data minimum (kinerja maksimum)

Kontra

• Penyimpanan lokal tidak mencukupi jika file terlalu besar
• Tidak adanya pemfilteran, transformasi, atau pengayaan data dalam file

Skenario dunia nyata

Kami akan mencoba solusi kami dalam skenario dunia nyata dengan S3 Event Notifications, AWS Lambda, dan ECS-Fargate

skenario salinan massal

• Pemicu akan memanggil fungsi Lambda setiap kali pengguna/layanan menambahkan objek ke bucket Amazon S3
• Fungsi Lambda akan menjalankan Fargate sesuai jalur file yang diunggah
• Tugas Fargate akan mentransfer konten file ke tabel PostgreSQL (pada RDS)

Pipa CICD

Setiap solusi yang baik layak mendapatkan pipa CICD yang baik. Untuk solusi ini, kami akan membuat pipeline CICD di atas Docker, Github Actions, ECR, dan ECS

pipa CICD

Buat pengguna IAM

Pengguna IAM diharuskan untuk dapat membuat dan mengelola sumber daya AWS. Saya tidak akan masuk ke IAM di artikel ini. Anda dapat meneliti secara eksternal apa itu IAM dan bagaimana pengguna dapat dibuat di IAM

Jadi, Anda sudah memiliki pengguna IAM (selain pengguna root)
Dengan itu, pertama-tama kita akan membuat bucket S3

Catatan. Sepanjang artikel, saya akan mencoba menggunakan aws cli sebanyak mungkin

Buat ember S3

Kami dapat dengan mudah membuat bucket S3 kami dengan perintah berikut

aws s3api create-bucket --bucket akocukcu-airbnb --region eu-central-1 --create-bucket-configuration LocationConstraint=eu-central-1

Anda dapat dengan mudah mengunggah file ke keranjang ini melalui cli, boto3, atau konsol
Saya telah mengunggah file daftar untuk Amsterdam, Berlin, London dan Paris dengan struktur folder di bawah ini

folder S3 1

folder S3 2

Buat database PostgreSQL di RDS

Layanan seperti RDS dan EC2 “idealnya” terletak di virtual private cloud (VPC). Akses ke mereka dikelola melalui definisi Grup Keamanan
Untuk mempersingkat artikel, saya menganggap VPC dan Grup Keamanan sudah siap

Dengan perintah cli ini, database PostgreSQL akan dibuat di RDS

aws rds create-db-instance --db-instance-identifier airbnb-db \
 --db-instance-class db.m6g.large \
 —-engine postgres \
 —-storage-type gp2 \
 —-allocated-storage 20 \
 —-master-username {{master_username}} \
 —-master-user-password {{master_password}} \
 —-no-multi-az \
 —-vpc-security-group-ids {{security_group_id}} \
 —-no-publicly-accessible \
 —-engine-version 13.3

_

Basis data PostgreSQL di RDS

Sekarang database kita sudah siap, kita bisa mencoba untuk terhubung

psql —-host=airbnb-db.cw3eufo8swwv.eu-central-1.rds.amazonaws.com —-port=5432 —-username={{master_username}} —-password —-dbname=postgres

Mari buat database baru alih-alih yang default (postgres) dan gunakan

CREATE DATABASE airbnbdb;
\c airbnbdb;

Saatnya membuat tabel “daftar” yang akan menampung semua detail daftar

Buat tabel untuk data listing

melelahkan. D

Sebagai praktik terbaik keamanan, kita harus membuat dan menggunakan pengguna khusus (bukan master)

CREATE USER {{user_name}} WITH PASSWORD ‘{{password}}’;
GRANT SELECT, UPDATE, INSERT, DELETE ON listings TO {{user_name}}

_

Mari terhubung dengan pengguna baru kami

psql —-host=airbnb-db.cw3eufo8swwv.eu-central-1.rds.amazonaws.com —-port=5432 —-username={{user_name}} —-password —-dbname=airbnbdb

Skrip Python

Sekarang tabel kita di RDS dan file di S3 sudah siap, kita bisa melanjutkan dengan skrip python

Anda dapat mencapai seluruh skrip dari sini

Ada empat fungsi

• inisialisasi_s3_klien. Yang ini sangat sederhana dan standar
• list_s3_files. Mengembalikan kunci file S3 sesuai dengan bucket dan awalan (yang berasal dari variabel lingkungan)
• unduh_s3_file. Membuat struktur folder yang sama dari S3 dan
  menggunakan metode download_file boto3 untuk mengunduh file
• load_csv_to_sql. Memanggil metode copy_expert psycopg2 sesuai dengan detail database (yang berasal dari variabel lingkungan)

Bagian paling kritis di sini adalah pernyataan COPY

COPY {sql_table} FROM STDIN WITH CSV HEADER DELIMITER AS ‘,’ QUOTE AS ‘\”’ ;

_

Anda dapat mengelola dan membuat parameter detail salinan seperti pembatas

Proyek Kontainer

Untuk menampung skrip python, kami memerlukan persyaratan. txt dan file Dockerfile. Anda dapat mencapai semua ini dari bawah repo github

GitHub - akocukcu/s3-to-rds-postgresql. Menyalin data dari S3 Bucket ke dalam tabel RDS PostgreSQL

Menyalin data dari Bucket S3 ke dalam tabel RDS PostgreSQL - GitHub - akocukcu/s3-to-rds-postgresql. Menyalin data dari…

github. com

Pada titik ini kita dapat dengan mudah membuat image Docker kita dan bahkan menjalankannya sebagai container

docker build -t akocukcu/s3-to-rds-postgresql .

Seperti yang Anda lihat di skrip python ada 10 parameter berbeda yang berasal dari variabel lingkungan. Kita dapat melewati ini satu per satu atau dengan file env. (Saya lebih suka yang kedua. )

docker run -d --env-file ./env.txt akocukcu/s3-to-rds-postgresql

_

Jalankan Container-Task di AWS ECS

Proyek kami bekerja dengan baik di mesin lokal kami. Sekarang, kita bisa menjalankannya di AWS ECS

Sekali lagi untuk bekerja dengan AWS ECS dan ECR, kami perlu membuat pengguna, peran, dan kebijakan IAM

Ada dua peran yang diperlukan terkait dengan tugas ECS berbasis Fargate

• ESCARol. peran yang memberi wewenang kepada ECS untuk mengelola sumber daya atas nama Anda
  Buka halaman IAM. Arahkan ke peran
  Klik "buat peran". Pilih “Layanan Kontainer Elastis”
  Di bawah "pilih kasus penggunaan Anda", pilih "Layanan Kontainer Elastis"
  Beri nama "ecsRole"
• Peran Eksekusi ECSTask. peran yang melekat pada tugas ECS
  Sekali lagi, di dalam IAM/peran;
  klik "buat peran". Pilih “Layanan Kontainer Elastis”
  Di bawah "pilih kasus penggunaan Anda", pilih "Tugas Layanan Kontainer Elastis". Lampirkan kebijakan “AmazonECSTaskExecutionRolePolicy”
  Beri nama "ecsTaskExecutionRole"

Setelah pembuatan peran, kami dapat dengan mudah membuat klaster ECS kami melalui CLI

aws ecs create-cluster —-cluster-name “airbnb-ecs-cluster”

_

Repositori ECR juga dapat dibuat dengan cara yang sama

aws rds create-db-instance --db-instance-identifier airbnb-db \
 --db-instance-class db.m6g.large \
 —-engine postgres \
 —-storage-type gp2 \
 —-allocated-storage 20 \
 —-master-username {{master_username}} \
 —-master-user-password {{master_password}} \
 —-no-multi-az \
 —-vpc-security-group-ids {{security_group_id}} \
 —-no-publicly-accessible \
 —-engine-version 13.3

_0

Kami akan mendorong gambar lokal ke repositori ECR yang baru dibuat dengan perintah di bawah ini (Anda harus memiliki konfigurasi izin yang diperlukan secara lokal)

aws rds create-db-instance --db-instance-identifier airbnb-db \
 --db-instance-class db.m6g.large \
 —-engine postgres \
 —-storage-type gp2 \
 —-allocated-storage 20 \
 —-master-username {{master_username}} \
 —-master-user-password {{master_password}} \
 —-no-multi-az \
 —-vpc-security-group-ids {{security_group_id}} \
 —-no-publicly-accessible \
 —-engine-version 13.3

_1

Gambar kami dipublikasikan di repositori ECR sekarang

Karena semuanya sudah siap, mari buat definisi tugas pertama di klaster ECS kita melalui konsol (setelah itu semua definisi tugas (revisi) akan dibuat/diperbarui oleh pipa CICD)

• Di dalam halaman ECS, arahkan ke "Definisi Tugas"
• Klik "Buat Definisi Tugas baru"
• Pilih FARGATE sebagai "kompatibilitas jenis peluncuran"
• Masukkan "Nama definisi tugas" (s3-to-rds-postgresql-td)
• Pilih ecsTaskExecutionRole sebagai "Peran tugas" (opsional)
• Pilih ecsTaskExecutionRole sebagai "Peran eksekusi tugas"
• Berikan nilai "Ukuran tugas" (Memori tugas = 1024, CPU Tugas = 512)
• Di bawah "Definisi penampung", masukkan url gambar ECR dan variabel lingkungan

Akhirnya, kita bisa menjalankan tugas Fargate kita

• Di dalam "Definisi Tugas" Anda yang baru dibuat (revisi terbaru s3-ke-rds-postgresql-td) klik Tindakan> Jalankan Tugas;
• Pilih FARGATE sebagai "jenis peluncuran"
• Pilih "Cluster VPC" (idealnya VPC yang menjadi milik instans RDS Anda)
• Pilih “Subnet(s)”
• Pilih "Grup Keamanan" (pilih yang ada atau buat baru jika Anda mau)
• Terakhir klik "Jalankan Tugas"

Saya berjanji setelah itu kami akan menjalankan tugas kami secara terprogram. )

Skenario dunia nyata

Untuk membangun skenario dunia nyata (yang telah kita bicarakan), kita perlu membuat fungsi cetak biru dari Lambda UI

• Di dalam halaman Lambda, klik "Buat Fungsi"
• Pilih "Gunakan cetak biru"
• Pilih "s3-get-object-python" dari Blueprints dan klik "configure"
• Masukkan "Nama fungsi"
• Pilih/Buat peran dengan izin untuk mengunggah log ke Amazon CloudWatch Logs
• Di bawah pemicu S3; . G ". /data”), akhiran (mis. g. “. csv. gz”)
• Klik "Buat Fungsi"

Dengan konfigurasi ini, setiap kali file diunggah ke S3bucket kami, fungsi ini akan dipicu

Untuk menjalankan tugas Fargate, kita perlu memanipulasi kode. Bagaimanapun, ini akan terlihat seperti ini

Hal penting di sini adalah mengganti variabel lingkungan S3 FILE PATH dengan kunci acara S3. Dengan melakukan ini, tugas Fargate hanya akan berfungsi untuk file yang diunggah

Catatan. Logika utama benar-benar diabstraksikan dalam wadah. Jadi, misalnya, kita dapat dengan mudah menggunakan Airflow ECSOperator dan mengatur prosesnya secara terjadwal. Tapi ini adalah topik artikel yang akan datang. )

Buat pipa CICD

Seperti yang saya janjikan di awal, kami menyelesaikan pengembangan kami dengan pipeline CICD sederhana

Dengan munculnya Tindakan Github, banyak pengembang dapat membuat saluran pipa CICD sederhana mereka sendiri. Dalam posting ini kita juga akan menggunakan alur kerja Tindakan GitHub (Karena ini sangat mudah)

Di dalam. github/alur kerja/aws. yml ada tujuh langkah

• aws-actions/configure-aws-credentials@v1
• aws-actions/amazon-ecr-login@v1
• Bangun, beri tag, dan dorong gambar ke Amazon ECR (dengan ID gambar baru)
• Unduh definisi tugas yang ada dari ECS
• Isi ID gambar baru di definisi tugas Amazon ECS
• Daftarkan revisi definisi tugas baru di ECS

Dengan langkah-langkah ini, dalam hal apa pun yang kita inginkan, gambar Docker dan definisi tugas ECS kita akan diperbarui

Catatan
Sejak saya mulai. workflow_dispatch itu hanya berjalan secara manual

Jika Anda mendefinisikannya seperti di bawah ini, Anda akan mendapatkan alur kerja yang diinginkan (setelah komit/gabungkan ke cabang utama, jalankan).

Bagaimana cara menghubungkan database PostgreSQL di AWS?

Bagaimana cara menghubungkan RDS saya ke S3 saya?

Bagaimana cara terhubung ke database postgres?

Bisakah saya menggunakan PostgreSQL dengan AWS?