FogLAMP SQL South Service Plugin

The foglamp-south-sql plugin is a comprehensive SQL South Service Plugin that provides historical data retrieval capabilities for SQL databases. This plugin supports various database operations including real-time data collection and historical data processing through ODBC connectivity.

Overview

The SQL South Service Plugin is designed to retrieve data from SQL databases and convert it into FogLAMP Reading objects. It supports both real-time data collection and historical data retrieval, making it ideal for scenarios where you need to:

  • Collect data from existing SQL databases

  • Process historical data for analysis

  • Integrate with legacy database systems

  • Support multiple database types (PostgreSQL, MySQL, SQL Server)

Key Features

  • Multi-Database Support: Compatible with PostgreSQL, MySQL, SQL Server, and other ODBC-compliant databases

  • Historical Data Retrieval: Configurable time windows for historical data processing

  • Flexible Query Building: Dynamic SQL query construction with WHERE clauses and ORDER BY support

  • Batch Processing: Efficient handling of large datasets through configurable batch sizes

  • Real-time and Historical Modes: Support for both continuous data collection and historical data retrieval

  • Configurable Column Mapping: Flexible mapping between database columns and asset datapoints

Configuration Details

The plugin configuration parameters are organized into logical groups for better organization and clarity:

Basic

sql_south_1

  • Asset Name: Name of the asset to which the readings belong

Connection

sql_south_2

Database connection and authentication parameters:

  • SQL Server URL: The SQL server hostname or IP address

  • ODBC Driver Name: The ODBC driver name for the target database

  • Server Port: The database server port number

  • Database Name: The name of the database to connect to

  • Username: Username for database authentication

  • Password: Password for database authentication

  • Additional Connection String: Additional ODBC connection parameters

Table

sql_south_3

Table configuration and query settings:

  • Source Table Name: The name of the source table for data retrieval

  • Timestamp Source: Source of timestamps (System Time/Database Column)

  • Source Table Timestamp Column: The timestamp column name in the source table (required when timestamp source is “Database Column”)

  • Insertion Mode: The mode of insertion for the readings (Fixed rows/Update)

  • Order By Column: The column to order the results by (required when insertion mode is “Update”)

  • Where Clause: Additional WHERE conditions for filtering data

  • Historical Data Window: Days of historical data to retrieve (required when insertion mode is “Update”)

  • Maximum Data Row Size: Maximum number of rows to fetch per query

Data

sql_south_4

Data processing and column mapping configuration:

  • Source Column Names: Configuration for mapping database columns to FogLAMP datapoints - Column Name: The name of the column in the source table - Datapoint Name: The name of the datapoint in FogLAMP - Datapoint Type: The type of the datapoint (String, Integer, Float, Timestamp)

Join Tables

sql_south_5

Configuration for joining additional tables with the base table for lookup data

  • Table Name: The name of the table to join

  • Join Key: The column name in the joined table to use for the join

  • Base Table Column: The column name in the base table to join with

  • Table Alias: The alias to use for the joined table in queries

Historical Data Processing

The plugin provides comprehensive historical data retrieval capabilities with intelligent infill processing:

Historical Data Window: - Configurable duration for historical data extraction (default: 30 days) - Minimum value of 0 (no historical data ingestion) - Automatic timestamp calculation from current date backwards - Persistent tracking of earliest ingested data timestamp

Intelligent Infill Processing: - Automatic detection of missing historical data periods - Infill process when historical data window is increased - Immediate response to configuration changes during ingestion - Support for both increasing and decreasing historical data windows - State persistence across plugin restarts

Batch Processing: - Configurable maximum data row size per poll (default: 200 rows) - Throttled historical data collection to reduce database load - Efficient data transfer with manageable response payloads - Progress tracking and logging during infill operations

Query Building and Execution

The plugin dynamically constructs SQL queries based on configuration and operates as a polled plugin:

Query Features: - Automatic WHERE clause generation for time windows - Support for custom WHERE conditions - ORDER BY clause configuration for new row insertion mode - LIMIT clause for result set size control - Parameterized queries to prevent SQL injection - Intelligent quoting of column names using database standard mechanisms

Polling Behavior: - Fixed Rows Mode: Returns data on every poll call when using system time, or only when timestamp changes when using database column - New Rows Mode: Returns data only when new rows are added to the source table - Historical data infill processing during poll calls - Configurable poll interval for data collection

Example Generated Queries:

-- Historical data retrieval with table joins
SELECT t.timestamp, t.temperature, t.humidity, lu.location_name
FROM sensor_readings t
LEFT JOIN location_lookup lu ON t.location_id = lu.id
WHERE t.timestamp >= '2024-01-01 00:00:00'
AND t.timestamp <= '2024-01-31 23:59:59'
ORDER BY t.timestamp ASC
LIMIT 200;

Data Processing

Reading Conversion: - Database result sets converted to FogLAMP Reading objects - Automatic data type conversion and validation - NULL value handling - Timestamp format normalization

Asset and Datapoint Mapping: - Configurable asset naming (defaults to table name if not specified) - Flexible column-to-datapoint mapping - Support for different data types (float, integer, string) - Automatic datapoint type detection - Support for joined table columns using table alias notation (e.g., lu.location_name)

Data Persistence: - Persistent storage of last ingested data between plugin calls - Shutdown and restart data persistence - Maintenance of earliest ingested data timestamp - State persistence for historical data infill operations

Data Types and Mapping

Supported FogLAMP Datapoint Types:

  • STRING: String representation of data

  • INTEGER: Integer values (when no fractional component)

  • FLOAT: Floating-point values and numeric types with fractional components