The last step in the ETL pipeline is to load the cleaned and processed data stored in clean_data.csv into a PostgreSQL database.

Connecting to the database

First, we create a Bash script called load_data.sh that will switch users from “root” to “postgres,” start a Postgres database, and run an SQL script to load the data. The SQL script will be stored in a file called load_data.sql.

Shell

#!/bin/bash
# Setting the project location as an environment  variable 
export WORKDIR='/usercode/ETL_Lottery'
# Setting Postgres bin and data directories as environment variables
export POSTGRES_BIN='/usr/lib/postgresql/16/bin'
export POSTGRES_DATA='/var/lib/postgresql/data'
# Create and start a new PostgreSQL database cluster.
# Connect to Postgres and run the file: load_data.sql
su - postgres -c "
  ${POSTGRES_BIN}/initdb -D ${POSTGRES_DATA}
  ${POSTGRES_BIN}/pg_ctl -D ${POSTGRES_DATA} start
  psql postgres -f ${WORKDIR}/load_data.sql
"

Deploying a PostgreSQL database: load_data.sh

Introduction

E: Extract

T: Transform

L: Load

Orchestration

ETL Pipeline: Fraud Detection Preprocessing

Conclusion

Build a News ETL Data Pipeline Using Python and SQLite

Fraud Detection System

ETL Example—Load

Connecting to the database