This project is a web application for managing an espionage agency, utilizing a Docker environment with multiple servers dedicated to different use cases. The platform integrates both relational (MySQL) and non-relational (MongoDB) databases, allowing seamless interaction and data migration between the two through a user-friendly web interface.
Focuses on data integration, ETL processes, and managing large datasets across different database systems.
- Data Integration and ETL: The project involves migrating data between MySQL and MongoDB, which is a core data engineering task.
- Database Management: Setting up, managing, and interacting with both relational (MySQL) and non-relational (MongoDB) databases.
- Data Population: Automatically generating and inserting a large dataset (~25,000 entries) into the databases.
Utilizes Docker Compose to orchestrate multiple containers, ensuring a scalable and reproducible environment for development, testing, and deployment.
- Docker Environment: The use of Docker Compose to orchestrate multiple containers (web server, MySQL, MongoDB, Nginx) is a key aspect of containerization.
- Automation and Reproducibility: Docker ensures that the environment can be easily replicated, enhancing development, testing, and deployment processes.
- Isolation: Each service runs in its own container, which isolates dependencies and simplifies management.
- Docker
- Docker Compose
- Python 3.x
- pip
The project leverages Docker Compose to set up and manage a multi-container Docker environment. This environment includes:
- Web Server: Handles user interactions and serves the web application.
- MySQL Server: Manages relational data storage and provides robust querying capabilities.
- MongoDB Server: Manages non-relational data storage, offering flexible and scalable document-based storage.
- Nginx Server: Acts as a reverse proxy to distribute traffic across the web application.
To set up and start the entire Docker environment, follow these steps:
-
Build and Start Docker Containers: This command builds all necessary Docker containers and starts the services.
docker compose build --no-cache -
Starts and executes the docker environment
docker-compose up
The Docker Compose setup will automatically:
- Build the web application and database containers.
- Initialize the relational database schema in MySQL.
- Populate the MySQL database with approximately 25,000 autogenerated data entries.
The web application provides an intuitive interface for managing the espionage agency's data. Key functionalities include:
- Data Management: Users can interact with the relational data stored in MySQL.
- Data Migration: Users can migrate data from MySQL to MongoDB with a single button click within the web interface. After migration the web application can be operated like before, the user will not notice that the data is now being stored and processed on a nonrelational database.
If you need to manually create or drop the database schema, use the following commands:
-
CREATE Table Schema: bash:
docker exec -i mysqldb mysql -u root -p'Schikuta<3' mysql_queensmandb < relational/mysql_scripts/imse_project_mysql_create.sql
fish shell:
cat relational/mysql_scripts/imse_project_mysql_create.sql | docker exec -i mysqldb mysql -u root -p'Schikuta<3' mysql_queensmandb
-
DROP Table Schema:
bash:
docker exec -i mysqldb mysql -u root -p'Schikuta<3' mysql_queensmandb < relational/mysql_scripts/imse_project_mysql_drop.sql
fish shell:
cat relational/mysql_scripts/imse_project_mysql_drop.sql | docker exec -i mysqldb mysql -u root -p'Schikuta<3' mysql_queensmandb
-
Optional create virtual environment(e.g.: venv)
-
Import all Dependancies:
pip install -r backend/python/requirements.txt -
Run the Python Program:
python3 backend/python/Queensman_imse_Insert.py
-
Install Dependancies:
pip install -r non_relational/requirements.txt -
Run the Migration Program:
python3 non_relational/mongo_insert.py
-
Connect to MongoShell to Check Migration:
docker exec -it mongodb mongosh
-
(Essential) Mongo Shell Commands:
- Show all Databases:
show dbs
- Access to the Project Database:
use queensmandb
- List all Collections:
show collections
- Shows first Entries of a specified Collection:
db.<specific_collection_name>.find().pretty()
Example:
db.employees.find().pretty()- Show specific Object corresponding to an id attribute
db.<specific_collection_name>.findOne({ <collection_id_attribute_name>: <id_value> })
Example:
db.employees.findOne({ employee_id: 14 })
The project includes two main reports implemented to assist in managing the espionage agency. Both were implemented as SQL and NoSQL querries respectively to what type of database is currently being used to hold the agencies data.
-
Find Most Successful Agents Based on Unique Subjects:
- Analyzes agents' success rates with unique subjects.
-
List of History of Specific Employees' Past Assigned Branches and Possible Transfers:
- Provides detailed information on employees' assignment history.
For detailed implementation and comparison of MySQL and MongoDB statements, refer to the documentation.
For more details on the project, please refer to the reports in the project documentation.
This project was an extension of a previous web project written by QuirkyCroissant, and was further developed together with LOG1CBOMB as an assignment as part of the coursework for Information Management & Systems Engineering at the University of Vienna.
This project is licensed under the Creative Commons Attribution-NonCommercial-NoDerivatives 4.0 International License. See the LICENSE file for details.