Harvard Art Museums Etl Pipeline

# Harvard Art Museums ETL Pipeline & Analytics

> Skill by [ara.so](https://ara.so) — Data Skills collection.

This project provides an end-to-end data engineering solution for extracting, transforming, and analyzing art museum data from the Harvard Art Museums API. It demonstrates ETL pipeline development, SQL database design, and interactive analytics dashboards using Streamlit.

## What This Project Does

The Harvard Artifacts Collection Data Engineering & Analytics App:
- **Extracts** artifact data from Harvard Art Museums API with pagination
- **Transforms** nested JSON into normalized relational database schemas
- **Loads** data into MySQL/TiDB Cloud with batch operations
- **Analyzes** data using predefined SQL queries
- **Visualizes** insights through interactive Plotly charts in Streamlit

**Architecture Flow**: API → ETL → SQL → Analytics → Visualization

## Installation

```bash
# Clone the repository
git clone https://github.com/Manali0711/Harvard-Artifacts-Collection-Data-Engineering-Analytics-App.git
cd Harvard-Artifacts-Collection-Data-Engineering-Analytics-App

# Install dependencies
pip install -r requirements.txt
```

**Key Dependencies**:
```
streamlit
pandas
requests
mysql-connector-python
plotly
python-dotenv
```

## Configuration

### Environment Variables

Create a `.env` file in the project root:

```env
# Harvard Art Museums API
HARVARD_API_KEY=your_api_key_here

# Database Configuration
DB_HOST=your_database_host
DB_PORT=3306
DB_USER=your_db_user
DB_PASSWORD=your_db_password
DB_NAME=harvard_artifacts
```

### Get Harvard API Key

1. Visit [Harvard Art Museums API](https://docs.harvardartmuseums.org/api/)
2. Request an API key (free)
3. Add to your `.env` file

### Database Setup

```sql
-- Create database
CREATE DATABASE harvard_artifacts;

-- Create artifact metadata table
CREATE TABLE artifactmetadata (
    id INT PRIMARY KEY,
    title VARCHAR(500),
    culture VARCHAR(200),
    period VARCHAR(200),
    century VARCHAR(100),
    classification VARCHAR(200),
    division VARCHAR(200),
    department VARCHAR(200),
    dated VARCHAR(200),
    medium VARCHAR(500),
    dimensions VARCHAR(500),
    creditline TEXT,
    accessionyear INT,
    technique VARCHAR(300),
    verificationlevel INT
);

-- Create artifact media table
CREATE TABLE artifactmedia (
    media_id INT AUTO_INCREMENT PRIMARY KEY,
    artifact_id INT,
    iiifbaseuri VARCHAR(500),
    baseimageurl VARCHAR(500),
    publiccaption TEXT,
    FOREIGN KEY (artifact_id) REFERENCES artifactmetadata(id)
);

-- Create artifact colors table
CREATE TABLE artifactcolors (
    color_id INT AUTO_INCREMENT PRIMARY KEY,
    artifact_id INT,
    color VARCHAR(100),
    spectrum VARCHAR(100),
    hue VARCHAR(100),
    percent FLOAT,
    FOREIGN KEY (artifact_id) REFERENCES artifactmetadata(id)
);
```

## Running the Application

```bash
streamlit run app.py
```

The Streamlit app will open in your browser at `http://localhost:8501`.

## Key Components

### 1. API Data Extraction

**Fetching Artifacts with Pagination**:

```python
import requests
import os
from dotenv import load_dotenv

load_dotenv()
API_KEY = os.getenv('HARVARD_API_KEY')

def fetch_artifacts(num_pages=5, page_size=100):
    """Fetch artifacts from Harvard Art Museums API with pagination"""
    bas