best practice for architecture data pipeline with open source tools on premise

When it comes to open-source tools for implementing ETL and data warehousing, there are several widely recognised solutions that have gained significant industry acclaim. Notable among these are:

• Apache Airflow
• Apache Kafka
• DBT
• Singer.io
• Talend Open Studio

At DataOps.Live, we offer pre-configured orchestration solutions for some of these open-source tools, as well as for some of their paid counterparts. For detailed information and guidance on orchestrating these tools seamlessly, you can explore our documentation page at: DataOps.Live Documentation. It's important to note that while our platform may not explicitly list every tool, we offer the flexibility to create custom solutions tailored to specific requirements. Our approach is inherently tool-agnostic, allowing users to build and implement solutions that best suit their unique needs.

@mohammadnaghibi does @Traycho Milev answer help? 

Hi @mohammadnaghibi If you have limitations on using cloud-based services and you strongly prefer open-source tools, there are several options available for implementing ETL from MongoDB to PostgreSQL using open-source tools. Below, I'll provide a general overview and suggest some tools you can use:

1. Extract (E):

   • Use mongoexport: This is a command-line tool provided by MongoDB (link here) to export data in various formats. You can export data from MongoDB collections into JSON or CSV files.
   • Example command would be:

     mongoexport --db yourDB --collection yourCollection --out data.json

2. Transform (T):

   • Use a programming language: Open-source programming languages like Python can be used for data transformation. You can read the exported data, perform necessary transformations, and prepare it for loading into PostgreSQL.
   • Example Python script using pandas:

     import pandas as pd
     
     # Read data from MongoDB export we did in Step 1
     data = pd.read_json('data.json')
     
     # Perform transformations
     # ...
     
     # Save transformed data
     data.to_csv('transformed_data.csv', index=False)
     

      

3. Load (L):

   • Use psql (PostgreSQL command-line tool - link here): This tool allows you to interact with PostgreSQL from the command line. You can create tables and load data into PostgreSQL using SQL commands.

   • psql -U your_username -d your_database -c "CREATE TABLE your_table (column1 datatype, column2 datatype, ...);"
     

      

4. Orchestration:

   • For orchestrating the ETL process, you can use tools like Apache Airflow or Luigi  link here. These tools allow you to schedule and monitor your ETL workflows. While not open source, DataOps.live is a great choice for getting the job done effectively. Throughout each stage of your development process, you can leverage excellent support.

5. Challenges and Considerations:

   • Schema Mapping:
     • Ensure that you map the MongoDB schema to the PostgreSQL schema correctly.
   • Data Consistency:
     • Consider how to handle consistency between the MongoDB and PostgreSQL databases during the ETL process.
   • Incremental Updates:
     • If this is a recurring ETL process, think about how to handle incremental updates to the data.

6. Overall Goal: ETL from MongoDB to PostgreSQL:​​​​​​

   • The initial actions you need to undertake include:

     • Implement a script or workflow that regularly extracts data from a MongoDB collection, transforms it to match the PostgreSQL schema, and loads it into the PostgreSQL database.

     • You can use the provided examples as a starting point and expand upon them to meet your specific requirements.

Remember to install necessary libraries or tools, such as pandas for Python, and configure your MongoDB and PostgreSQL connections accordingly. Adjust the examples based on your specific use case and data structures.

If you have additional questions, feel free to reach out to us.