Skip to content

Home

Jump to bottom
Rob Oudendijk edited this page Nov 3, 2024 · 1 revision

Home

Welcome to the Measurements API Project

This API project enables ingestion and querying of measurement data using DuckDB as the backend database and FastAPI for the RESTful interface. It is designed to support flexible data ingestion and retrieval, allowing easy integration with monitoring and visualization tools like Grafana.

1. Getting Started

Requirements

  • DuckDB: Ensure DuckDB is installed and accessible.
  • Python: Requires Python 3.7+ and the following libraries: fastapi, uvicorn, and duckdb.

Installation

  1. Clone the Repository:
    bash

git clone https://github.com/Safecast/simple-gateway.git
cd simple-gateway

Install Python Dependencies:
bash
pip install fastapi uvicorn duckdb

Initialize the DuckDB Database: Open DuckDB and create the main table:
bash
duckdb devices.duckdb

sql
CREATE TABLE measurements (
bat_voltage DOUBLE,
dev_temp INTEGER,
device BIGINT,
device_sn VARCHAR,
device_urn VARCHAR,
env_temp INTEGER,
lnd_7128ec DOUBLE,
lnd_7318c DOUBLE,
lnd_7318u DOUBLE,
loc_country VARCHAR,
loc_lat DOUBLE,
loc_lon DOUBLE,
loc_name VARCHAR,
pms_pm02_5 DOUBLE,
when_captured TIMESTAMP,
received_at TIMESTAMP
);

Start the API Server:
bash
uvicorn grafana_duckdb_api:app --host 0.0.0.0 --port 8000

2. API Documentation

POST /measurements

  • Description: Insert new measurement data into the DuckDB database.
  • Parameters: api_key (required), along with measurement data in JSON format.
  • Sample Request:
    bash

curl -X POST "http://localhost:8000/measurements?api_key=q1LKu7RQyxunnDW" \
-H "Content-Type: application/json" \
-d '{
"bat_voltage": "3.7",
"dev_temp": "25",
"device": "2830364905",
"device_sn": "SN12345",
"device_urn": "urn:dev:12345",
"env_temp": "30",
"lnd_7128ec": "60",
"lnd_7318c": "",
"lnd_7318u": "20",
"loc_country": "US",
"loc_lat": "37.7749",
"loc_lon": "-122.4194",
"loc_name": "San Francisco",
"pms_pm02_5": "12.5",
"when_captured": "2024-11-02T15:30:00"
}'

GET /query

  • Description: Retrieve measurement data based on query filters.
  • Parameters: Any field in the measurements table can be used as a filter.
  • Example Requests:
    • Single Filter:
      bash

curl -X GET "http://localhost:8000/query?device_sn=SN12345"

Multiple Device Filters:
bash
curl -X GET "http://localhost:8000/query?device=2830364905&device=2830364906"

GET /status

  • Description: Check if the API server is running.
  • Sample Request:
    bash

curl -X GET "http://localhost:8000/status"

3. Grafana Integration

Connecting to Grafana with the Infinity Datasource

The Yesoreyeram Infinity datasource plugin in Grafana can be configured to retrieve data from this API.

  1. Add the Infinity Datasource in Grafana:
    • Go to Configuration -> Data sources -> Add data source.
    • Search for Infinity and select Yesoreyeram Infinity Datasource.
  2. Configure the Datasource:
    • URL: http://localhost:8000/query
    • Query Type: Select JSON or CSV depending on the format you want to use.
  3. Create Panels with Queries:
    • Add queries with the appropriate filters (e.g., device_sn, when_captured).

Example Grafana Query

Use a query that fetches data for a specific device_sn:

plaintext
http://localhost:8000/query?device_sn=SN12345

4. Advanced Configuration

Dynamic Column Addition

The API automatically adds new columns to the measurements table if fields not yet defined in the database are received in the JSON payload.

  • Supported Types: DOUBLE or VARCHAR, determined by the data type.
  • Note: The automatic addition applies to all fields in the JSON payload that are not currently in the database.

Handling NULL Values

Empty string values in the payload are converted to NULL values to maintain data integrity. This is especially useful when working with Grafana or other data visualization tools, as NULL values are more accurately represented in queries and charts.

Troubleshooting

If you encounter any issues:

  • Ensure DuckDB and Python dependencies are installed correctly.
  • Check the DuckDB version for compatibility.
  • Make sure the API server is running and accessible at the specified port.

5. Future Development

Planned Features

  • Additional API endpoints for more complex data analytics.
  • Support for custom aggregations and roll-ups.
Clone this wiki locally