The purpose of this pipeline is to ingest data from the postgres database and integrate with part of the data in a CSV file, thus centralizing in a Mysql database so that it can be consumed.

The execution is divided into 3 steps:

• First Step - Extraction, where information is collected from the postgress database and from a .csv file to the local file system.
• Second step - Loading information saved in the local file system and sorted by export day to a destination Mysql database.
• Third step - Data import validation with a query that lists these imported data and generate a "query_result.csv" by joining the final tables "order" and "order_details" on Mysql database.

• Python 3
• Pipenv
• SqlAlchemy
• Pandas
• Docker
• Mysql

The pipeline execution is done within the python virtual environment, we use the pandas library for data extraction and export, with it we work with dataframes, simplifying the process.

• Python 3
• Pipenv

  pip install pipenv

• Configure connection, edit the file and configure it according to your source (pg) and destination (mysql) connection data in the dictionary "connection_data" and CSV file name to import

  ./libs/settings.py
    
    connection_data = {
        'source_pg':{
            'host': '127.0.0.1',
            'db': 'northwind',
            'user': 'northwind_user',
            'password':'thewindisblowing'
        },
        'target_mysql':{
            'host': '127.0.0.1',
            'db': 'northwind',
            'user': 'northwind_user',
            'password':'thewindisblowing'
        }
    }
  
    csv_file_to_import = 'order_details'

After installing pipenv we need to initialize the source and target database instances and install the packages (dependencies).

1. Initialize the Postgres source database instance and the Mysql target database.

   docker-compose up

2. Initialize the Mysql target database. (See Mysql Docs to configure your instance)

3. Package installation

   pipenv install

4. Initialize pipenv virtual environment

   pipenv shell

To run the complete pipeline, inside the "pipenv shell" run:

   python main.py

As no parameter was informed, the script will run the 3 steps in sequence until the export of the file "query_result.csv".

To run on a specific date (format has to be YYYY-MM-DD) in full excution or step by step add "-d" option.

   python main.py -d 2021-11-30

we can perform the first 2 steps separately:

• For Postgres / CSV extraction only

   python main.py -e

• For loading data to Mysql only (can only be executed if there is an extraction on the date defined as parameter).

   python main.py -l

• To export the "query_result.csv" file along with any of the steps add "-q" option.

   python main.py -l -q

• To see all commands use the help option "-h".

   python main.py -h

Send me an email: nicke.dev@gmail.com