Skip to main content

Flow Downloader System - Detailed Design

Overview​

The Flow Downloader System is an automated data ingestion pipeline that downloads river flow data from NOAA's SFTP server and processes it into either a TimescaleDB (Vanilla) or SQL Server (RSMS) database. The system runs daily at 5 PM (17:00) via cron job and consists of two main components:

  1. SFTP file downloader (shell script)
  2. Django data processor (Python)

System Architecture​

1. Components​

Vanilla Implementation​

Flow Downloader System
├── cron-ftp-flows-key-verbose.sh    # Shell script for SFTP operations
├── download_process_hdf.py          # Django management command
├── upsert_flows.py                  # Flow data processor
└── upsert_dailies.py                # Daily aggregator

RSMS Implementation​

Flow Downloader System
├── cron-ftp-flows-key-verbose.sh    # Shell script for SFTP operations
├── process_hdf_rsms.py              # Django management command
├── upsert_flows_rsms.py             # Flow data processor
└── [Windows-specific scripts]        # PowerShell scripts for Windows

2. Data Flow​

Vanilla Implementation​

RSMS Implementation​

Detailed Component Design​

1. SFTP Downloader​

Vanilla Configuration​

# Environment Variables
SFTP_USER="skshirsagar"
SFTP_HOST="data7.erh.noaa.gov"
SFTP_KEY="/home/gqc/.ssh/noaa-flows-ftp"
LOCAL_PATH="/dbdrive/orsanco/"
DJANGO_PROJECT_PATH="/home/gqc/riverflows_django"
PYTHON_PATH="/home/gqc/.virtualenvs/orsanco/bin/python"
LOG_FILE="/home/gqc/cron-ftp-flows.log"

RSMS Configuration​

# Environment Variables
SFTP_USER="skshirsagar"
SFTP_HOST="data7.erh.noaa.gov"
SFTP_KEY="/path/to/ssh/key"
LOCAL_PATH="/path/to/hdf_downloads/"
DJANGO_PROJECT_PATH="/path/to/project"
PYTHON_PATH="/path/to/python"
LOG_FILE="/path/to/log"

Process Flow​

  1. Initialization

    • Log start time
    • Verify local directory existence
    • Create directory if missing

  2. Remote File Discovery

    • Create temporary SFTP script
    • List remote *.tgz files
    • Parse file list

  3. Download Management

    • Compare remote and local files
    • Create download list for new files
    • Generate SFTP batch script
    • Execute downloads

  4. Error Handling

    • Directory creation failures
    • SFTP connection issues
    • File download failures
    • Log all errors

2. Data Processors​

Vanilla Implementation (download_process_hdf.py)​

  1. File Processing

    • Extract HDF5 files from .tgz archives
    • Process each HDF5 file
    • Track processed files in Django model

  2. Data Transformation

    • Convert HDF5 data to DataFrame
    • Normalize timestamps
    • Map station indices
    • Process velocity data

  3. Database Operations

    • Bulk upsert operations
    • Transaction management
    • Error handling

RSMS Implementation (process_hdf_rsms.py)​

  1. File Processing

    • Extract HDF5 files from .tgz archives
    • Process each HDF5 file
    • Track processed files in SQL Server

  2. Data Transformation

    • Convert HDF5 data to DataFrame
    • Calculate area from flow/velocity
    • Map station indices using RivermileIndex
    • Process area data

  3. Database Operations

    • SQL Server bulk upserts
    • Transaction management
    • Error handling

3. Data Aggregator (Vanilla only)​

Process Flow​

  1. Daily Aggregation
    • Calculate daily statistics
    • Store in DailyRiverFlow table
    • Maintain data consistency

Error Handling and Logging​

1. Logging System​

  • Shell Script Logs

    • Start/end times
    • File operations
    • SFTP operations
    • Error messages

  • Django Logs

    • Processing status
    • Database operations
    • Error tracking
    • Performance metrics

2. Error Recovery​

  • SFTP Errors

    • Connection retries
    • File verification
    • Partial download handling

  • Processing Errors

    • Transaction rollback
    • File tracking
    • Error reporting

Security Implementation​

1. Authentication​

  • SSH key-based SFTP authentication
  • Encrypted credentials
  • Secure file transfers

2. Access Control​

  • File system permissions
  • Database access control
  • Process isolation

Performance Considerations​

1. Optimization Strategies​

  • Bulk file downloads
  • Parallel processing
  • Efficient database operations
  • Memory management

2. Resource Management​

  • Disk space monitoring
  • Memory usage control
  • Process scheduling
  • Network bandwidth

Monitoring and Maintenance​

1. System Monitoring​

  • File system usage
  • Database performance
  • Process status
  • Error rates

2. Maintenance Procedures​

  • Log rotation
  • Disk cleanup
  • Database optimization
  • Backup verification

Deployment Configuration​

1. Cron Configuration​

# Run daily at 5 PM
0 17 * * * ~/cron-ftp-flows-key-verbose.sh >> ~/cron.log

2. Environment Setup​

  • Python virtual environment
  • Database configuration
  • SFTP key setup
  • Directory permissions

Future Enhancements​

1. Planned Improvements​

  • Parallel file processing
  • Enhanced error recovery
  • Performance monitoring
  • Automated testing

2. Scalability Considerations​

  • Distributed processing
  • Load balancing
  • Resource optimization
  • Data partitioning
Last updated on by Jake