data-management/README.md

CIEF Extract Load

Built With

• Python 3.9.12
• mysql-connector-python 8.0.29
• google-cloud-storage 2.5.0
• crisp-api 1.1.13
• pandas 1.5.2

✨ Code-base structure

How To Run CMD:

###### pip3 install -r requirements.txt
###### python3 main.py

First time run playwright

###### playwright install
###### playwright install-deps

Some VM Tips

###### Host: data-managmenet project
###### Image: Debian 11
###### Region: US
###### Directory: /home/cief_data/business_intelligence/data-management
###### Cronjob run at: Everyday 12:30AM
###### Snowflake task run at: Everyday 2:00AM
###### Crontab config: crontab -e
###### Crontab log: grep CRON /var/log/syslog
###### Python log: grep 'mycmd' /var/log/syslog
###### Remove file cmd: rm -rf 
###### git clone https://gitlab.com/cief-data/data-management.git
###### pip3 install -r requirements.txt

Example:- EXCHANGE_CONFIG.json

{
    "host"      :   "127.0.0.1",
    "user"      :   "admin",
    "password"  :   "admin",
    "database"  :   "classicmodels"
}

Example:- EXCHANGE_EXTRACT_SQL.json

{
    "customers"    :   "SELECT *, now() AS '_source_loaded_at' FROM customers;",
    "employees"    :   "SELECT *, now() AS '_source_loaded_at' FROM employees;",
    "offices"      :   "SELECT *, now() AS '_source_loaded_at' FROM offices;"
}

Project Tree

data-management
│
├─ .gitignore                                               # Ignore the file to push into gitlab
│
├─ config                                                   # Folder store all the config file to use in the code
│  ├─ C2M_CONFIG.json                                       # C2M webscraping configuration
│  ├─ CLOUD-STORAGE-ADMIN-CREDENTIAL.json                   # Get from GCP Service Account cloud-storage-admin-service-account
│  ├─ CRISP_CONFIG.json                                     # Config for CRISP API key, secret & web-id
│  ├─ EXCHANGE_CONFIG.json                                  # Manually config the MySQL ID, Password, etc... 
│  ├─ EXCHANGE_EXTRACT_SQL.json                             # SQL use in MYSQL to extract the data we need to save in temp's csv
│  ├─ SHIPPING_CONFIG.json                                  # Manually config the MySQL ID, Password, etc... 
│  └─ SHIPPING_EXTRACT_SQL.json                             # SQL use in MYSQL to extract the data we need to save in temp's csv
│
├─ c2m                                                      # C2M webscrapping
│  ├─ scrape                                                # All script use to scrape the competitor data is in here
│  │  ├─ c2m_scrape_app.py                                  # C2M webscrap, call in main.py or seperate run it in your local environment
│  │  ├─ c2m_temp_scrape_files                              # Temp folder to store all the csv or error.png from webscraper (Auto Create)  
│  │  │   └─ .gitkeep                                        # Keep empty folder
│  │  └─ local_run_files                                    # Folder to store all the data or error from webscraper which run at local pc 
│  │     └─ error                                           # Folder to store error.png from webscraper which run at local pc 
│  │         └─ .gitkeep                                    # Keep empty folder
│  └─ load
│     └─ c2m_load_function.py                               # C2M load code function to import in main.py
│
├─ crisp                                                    # CRISP-CRM
│  ├─ extract                                               # All crisp extract needed code is in here
│  │  ├─ crisp_extract_function.py                          # Crisp extract code function to import in main.py
│  │  └─ crisp_temp_extract_csv                             # Temp folder to store all the csv extract from API (Auto Create)   
│  │     └─ .gitkeep                                        # Keep empty folder
│  └─ load                                                  # All load needed code is in here
│     └─ crisp_load_function.py                             # CRISP load code function to import in main.py
│
├─ exchange                                                 # Exchange web application
│  ├─ extract                                               # All extract needed code is in here
│  │  ├─ exchange_extract_function.py                       # Exchange extract code function to import in main.py
│  │  └─ exchange_temp_extract_csv                          # Temp folder to store all the csv extract from MYSQL (Auto Create)   
│  │     └─ .gitkeep                                        # Keep empty folder
│  │
│  └─ load                                                  # All load needed code is in here
│     └─ exchange_load_function.py                          # Exchange load code function to import in main.py
│
├─ shipping                                                 # Shipping web application
│  ├─ extract                                               # All extract needed code is in here
│  │  ├─ shipping_extract_function.py                       # Shipping extract code function to import in main.py
│  │  └─ shipping_temp_extract_csv                          # Temp folder to store all the csv extract from MYSQL (Auto Create)   
│  │     └─ .gitkeep                                        # Keep empty folder
│  │
│  └─ load                                                  # All load needed code is in here
│     └─ Ssipping_load_function.py                          # Shipping load code function to import in main.py
│
├─ general_function.py                                      # All customize general code function to import in main.py
├─ README.md                                                # Project guide
├─ requirements.txt                                         # Install needed library
└─ main.py                                                  # Main code to run the extract load process

Manage By

Name:- Yam Zheng Lim

Contact:- 0129747749

Email:- yamzhenglim0427@gmail.com