Keep your Ecowitt station's history in your own database
A guide for the Ecowitt community. Every command and output below comes from a run against the service on 2026-10-03.
Your Ecowitt gateway already knows how to post every reading to a server of your choice: the Customized upload in the WS View Plus or Ecowitt app. Point it at LakehouseBox and every post becomes rows in a table you own: one row per sensor reading, converted to metric units, timestamped from the gateway's own clock, kept for as long as you like. You query years of it with SQL (DuckDB, Python, Spark, anything that reads Apache Iceberg), export it, or publish it for anyone to read. There is nothing to run at home: no Raspberry Pi, no relay, no script.
What you need
An Ecowitt gateway with the Customized upload (GW1100, GW1200, GW2000 and the other Ecowitt gateways). The upload settings live in the WS View Plus / Ecowitt app under Others → DIY Upload Servers → Customized, or in the gateway's own web page. A GW1200A (firmware V1.3.4) posted to this route every minute through a 24-hour test on 2026-09-24/25; the run on this page replayed recorded posts from that gateway.
A LakehouseBox account (sign up, free) and the CLI, one Python 3 file:
curl -fsSL https://lakehousebox.com/install.sh | sh lhbox login # approve once in the browser; the key is saved, never shownFor queries on your machine, DuckDB 1.5.5 or newer (the run used 1.5.6).
lhbox queryworks without it.
1. A catalog and a table
A catalog holds your tables. The table has the six columns the Ecowitt mapping fills: one row per reading.
lhbox catalog create weather
lhbox table create weather.station.readings \
--column event_time:timestamptz:required --column sensor_id:string:required \
--column sensor_type:string:required --column metric_name:string:required \
--column metric_value:double:required --column metric_unit:string2. A sink with a device route and the ecowitt preset
A sink receives posts and commits them to the table every 5 minutes. --device gives it a URL the gateway can post to with no header (the URL is the credential); --preset ecowitt turns each post into tidy rows.
lhbox sink create --catalog weather --table station.readings --name ecowitt_in \
--device --require-field PASSKEY=<your gateway's PASSKEY> \
--preset ecowitt --param WS=ws-01 --param GW=gw-01 --save ~/weather-sink.jsonThe run's output (2026-10-03):
Sink ecowitt_in created: table station.readings of catalog weather, every 300 s (or 64 MiB, or 1000 batches).
...
device route: POST http://in.lakehousebox.com/d/********
no header needed; form, JSON, NDJSON or CSV, up to 256 KiB
the same path also answers over HTTPS on https://in.lakehousebox.com
the URL is the credential: 44 characters for the device's server + path fields
device token: saved to weather-sink.json with the URL (not shown; the API cannot show it again)
mapping: ecowitt (73 rules), applied at each roll; try it: lhbox sink test ecowitt_in <file>- The PASSKEY. Every Ecowitt post carries a
PASSKEYfield that identifies the gateway.--require-fieldpins it: a post with another PASSKEY is refused (401 invalid_device_secret), and the PASSKEY itself is removed before anything is stored. Only its SHA-256 leaves your machine. If you do not know your PASSKEY, use--strip PASSKEYinstead of--require-field: the URL alone is then the credential and the PASSKEY is still never stored. A pin with the wrong value refuses every post (lhbox sink getcounts them underrefused), so pin only a value you are sure of. WSandGWname the outdoor array and the gateway insensor_id(defaultsws-01andgw1200a-01). Extra sensors get their channel:soil-ch1,th-ch2,leaf-ch1,temp-ch1,lightning-01.~/weather-sink.json(mode 0600) holds the device URL. Lost it?lhbox sink device-token ecowitt_in --save ~/weather-sink.jsonmakes a new one and the old one stops working at once.
3. Point the gateway at it
Print the plain-HTTP URL once (it is a secret; it is your device's password):
jq -r .device_url_http ~/weather-sink.json # http://in.lakehousebox.com/d/<token>In the app, Customized:
| Field | Value |
|---|---|
| Customized | Enable |
| Protocol Type Same As | Ecowitt (not Wunderground) |
| Server IP / Hostname | in.lakehousebox.com (no http://) |
| Path | /d/<token> (the 22 characters after /d/ in the URL) |
| Port | 80 |
| Upload Interval | 60 seconds (anything of 10 s or more stays inside the limit below) |
Save. The gateway posts at the next interval.
Use in.lakehousebox.com, not api.lakehousebox.com. The gateway's Customized upload speaks plain HTTP only and does not follow redirects. Measured on 2026-10-03: POST http://in.lakehousebox.com/d/… reaches the route (port 80), while POST http://api.lakehousebox.com/d/… answers 308 (a redirect to HTTPS), which the gateway treats as a failed upload.
4. Check that it arrives
Before the first roll, lhbox sink get shows what is waiting; after it, what was committed. The run posted four recorded gateway posts exactly as the gateway does (a plain POST of the form body, no other header):
$ curl -X POST http://in.lakehousebox.com/d/<token> -H 'Content-Type: application/x-www-form-urlencoded' --data-binary @post1.txt
{"accepted":1,"rejected":0,"batch_id":"d-…","sink_id":"5db8f9b2-…","bytes":373,"received_at":"2026-10-03T22:58:40.465Z","state":"pending"}A repeat of the same post (the gateway retrying) answers 200 with "duplicate":true and is stored once; a wrong PASSKEY answers 401 invalid_device_secret and is counted. After the roll:
$ lhbox sink get ecowitt_in
last_roll {"at": "2026-10-03T23:03:45.947Z", "batches": 29, "rows": 148, "rejected_rows": 0, ...}
device {"enabled": true, ..., "require_field": "PASSKEY", "strip": ["PASSKEY"], "refused": {"invalid_device_secret": 1, "rate_limited": 10}}(29 batches because the run also posted 25 short bodies to measure the rate limit, and 10 more were refused by it.)
To see what the mapping makes of a post without storing anything, save one body to a file and dry-run it:
$ lhbox sink test ecowitt_in post1.txt
Dry run on sink ecowitt_in (application/x-www-form-urlencoded): 1 record, nothing stored.
pinned secret: matches
stripped: PASSKEY
mapping: ecowitt v1 -> 15 rows
event_time sensor_id sensor_type metric_name metric_unit metric_value
------------------------ --------- ----------- ----------------- ----------- ------------
2026-10-03T22:54:00.000Z ws-01 weather temperature celsius 26.39
2026-10-03T22:54:00.000Z ws-01 weather humidity percent 41.0
2026-10-03T22:54:00.000Z gw-01 indoor pressure_relative hPa 936.7
2026-10-03T22:54:00.000Z ws-01 weather wind_direction degrees 4.0
2026-10-03T22:54:00.000Z ws-01 weather wind_speed m/s 0.0
2026-10-03T22:54:00.000Z ws-01 weather solar_radiation W/m2 1.7
2026-10-03T22:54:00.000Z ws-01 weather uv_index index 0.0
2026-10-03T22:54:00.000Z ws-01 weather rain_rate mm/h 0.0
2026-10-03T22:54:00.000Z ws-01 weather rain_daily mm 1.52
2026-10-03T22:54:00.000Z soil-ch1 soil moisture percent 0.0
2026-10-03T22:54:00.000Z soil-ch1 soil conductivity uS/cm 0.0
2026-10-03T22:54:00.000Z soil-ch1 soil temperature celsius 25.72
2026-10-03T22:54:00.000Z soil-ch1 soil battery V 1.28
2026-10-03T22:54:00.000Z leaf-ch1 leaf wetness percent 0.0
2026-10-03T22:54:00.000Z ws-01 weather battery_low flag 1.0What the rows look like
One row per reading, long format: event_time (the gateway's dateutc; if it is missing or more than a day off, the time the post arrived), sensor_id, sensor_type, metric_name, metric_value, metric_unit. Units are converted from the gateway's imperial ones: °F → °C, inHg → hPa, in → mm, mph → m/s. Rain metrics (rain_rate, rain_event, rain_hourly, rain_daily, rain_weekly, rain_monthly, rain_yearly, rain_24h, rain_total) are the gateway's running counters, not increments: rain_daily resets at the gateway's local midnight. A numeric field the preset does not know is kept as a row with sensor_type = 'unknown', the field's own name and unit raw, so a new sensor is never lost. Long format means a new sensor never changes the table.
Query it
lhbox duckdb --catalog weather opens a DuckDB shell with the catalog attached as weather. The run's queries and their output (the values are the recorded posts', so they jump):
SET TimeZone = 'Europe/Madrid'; -- your station's zone, so days are your days
-- the latest value of each outdoor metric
SELECT metric_name, arg_max(metric_value, event_time) AS value, any_value(metric_unit) AS unit, max(event_time) AS at
FROM weather.station.readings
WHERE sensor_id = 'ws-01' AND metric_name IN ('temperature','humidity','dew_point','wind_speed','wind_gust','solar_radiation','uv_index','rain_daily')
GROUP BY metric_name ORDER BY metric_name;┌─────────────────┬────────┬─────────┬──────────────────────────┐
│ metric_name │ value │ unit │ at │
├─────────────────┼────────┼─────────┼──────────────────────────┤
│ dew_point │ 14.2 │ celsius │ 2026-10-04 00:56:00+02 │
│ humidity │ 88.0 │ percent │ 2026-10-04 00:55:00+02 │
│ rain_daily │ 0.3 │ mm │ 2026-10-04 00:57:00+02 │
│ solar_radiation │ 412.61 │ W/m2 │ 2026-10-04 00:55:00+02 │
│ temperature │ 16.3 │ celsius │ 2026-10-04 00:56:00+02 │
│ uv_index │ 4.0 │ index │ 2026-10-04 00:55:00+02 │
│ wind_gust │ 5.81 │ m/s │ 2026-10-04 00:56:00+02 │
│ wind_speed │ 3.14 │ m/s │ 2026-10-04 00:56:00+02 │
└─────────────────┴────────┴─────────┴──────────────────────────┘-- daily minimum, maximum and mean outdoor temperature
SELECT event_time::DATE AS day, min(metric_value) AS t_min, max(metric_value) AS t_max,
round(avg(metric_value), 1) AS t_mean, count(*) AS readings
FROM weather.station.readings
WHERE sensor_id = 'ws-01' AND metric_name = 'temperature'
GROUP BY day ORDER BY day;
-- rain per day: rain_daily is a counter reset at midnight, so the day's total is its maximum
SELECT event_time::DATE AS day, max(metric_value) AS rain_mm
FROM weather.station.readings
WHERE sensor_id = 'ws-01' AND metric_name = 'rain_daily'
GROUP BY day ORDER BY day;┌────────────┬────────┬────────┬────────┬──────────┐
│ day │ t_min │ t_max │ t_mean │ readings │
├────────────┼────────┼────────┼────────┼──────────┤
│ 2026-10-04 │ 15.61 │ 26.39 │ 16.1 │ 28 │
└────────────┴────────┴────────┴────────┴──────────┘
┌────────────┬─────────┐
│ day │ rain_mm │
├────────────┼─────────┤
│ 2026-10-04 │ 1.52 │
└────────────┴─────────┘-- back to one row per post (wide), e.g. for a spreadsheet
PIVOT (SELECT event_time, metric_name, metric_value FROM weather.station.readings WHERE sensor_id = 'ws-01')
ON metric_name IN ('temperature', 'humidity', 'wind_speed', 'rain_daily') USING first(metric_value)
ORDER BY event_time;┌──────────────────────────┬─────────────┬──────────┬────────────┬────────────┐
│ event_time │ temperature │ humidity │ wind_speed │ rain_daily │
├──────────────────────────┼─────────────┼──────────┼────────────┼────────────┤
│ 2026-10-04 00:54:00+02 │ 26.39 │ 41.0 │ 0.0 │ 1.52 │
│ 2026-10-04 00:55:00+02 │ 16.28 │ 88.0 │ 1.4 │ 0.89 │
│ 2026-10-04 00:56:00+02 │ 16.3 │ NULL │ 3.14 │ 0.9 │
│ 2026-10-04 00:57:00+02 │ NULL │ NULL │ NULL │ 0.3 │
└──────────────────────────┴─────────────┴──────────┴────────────┴────────────┘(The run's PIVOT also had event_time >= TIMESTAMPTZ '2026-10-04 00:54:00+02', to leave out the rate-limit posts.)
No DuckDB on this machine? The same SQL runs on the service, in UTC, up to 2,000 rows:
$ lhbox query "SELECT event_time::DATE AS day, min(metric_value) AS t_min, max(metric_value) AS t_max FROM weather.station.readings WHERE sensor_id = 'ws-01' AND metric_name = 'temperature' GROUP BY day ORDER BY day"
day t_min t_max
---------- ----- -----
2026-10-03 15.61 26.39
1 row · 172 msThe table is Apache Iceberg with Parquet files, so pandas, Polars, Spark and the rest read it too: engines.
Share it (optional)
A public catalog can be read by anyone, with no account and no credentials. Public means every table of the catalog, so keep the station in a catalog of its own:
lhbox catalog publish weather --confirm weather
lhbox catalog public-url weather --table station.readingsAnyone can then read it with a plain DuckDB, no LakehouseBox anything (the run, from a session with no secrets but this keyless one):
INSTALL iceberg; LOAD iceberg; INSTALL httpfs; LOAD httpfs;
CREATE SECRET lhbox_public (TYPE S3, ENDPOINT 's3.lakehousebox.com', URL_STYLE 'path', USE_SSL true);
SELECT sensor_type, count(*) AS rows
FROM iceberg_scan('https://s3.lakehousebox.com/<handle>--weather/station/readings')
GROUP BY ALL ORDER BY rows DESC;┌───────────────────┬───────┐
│ sensor_type │ rows │
├───────────────────┼───────┤
│ weather │ 106 │
│ soil │ 17 │
│ indoor │ 9 │
│ air │ 7 │
│ temperature_probe │ 3 │
│ lightning │ 3 │
│ leaf │ 3 │
└───────────────────┴───────┘<handle> is your organisation's handle; lhbox catalog public-url prints the exact URL. lhbox catalog unpublish weather makes it private again. More in public catalogs.
Limits and gotchas
- Plain HTTP on port 80, to
in.lakehousebox.com. That is all the Customized upload can do. The token and the PASSKEY cross the internet unencrypted, as with every Customized upload; someone on the path could post readings into this one sink, and nowhere else. If it happens,lhbox sink device-tokenreplaces the URL. - Rate: 6 posts a minute per sink sustained, bursts of 30. Measured: 25 posts back to back after five earlier ones were accepted, then
429 rate_limitedwithRetry-After: 3–5. An upload interval of 10 s or more never reaches it; 60 s is plenty for weather. - One Customized slot. The gateway has a single Customized server. Its uploads to ecowitt.net, Weather Underground and the others are separate settings and keep working.
- The 64-character field limit. Server + path for LakehouseBox is 44 characters (
in.lakehousebox.comand/d/+ 22), inside it. - Commit delay. Rows appear at the sink's roll, every 5 minutes by default (
--roll-seconds, at least 60). - Two rain gauges. The preset maps the tipping-bucket fields (
dailyrainin, …) and the piezo fields (drain_piezo, …) to the samerain_*names onws-01. With both a WS90-style piezo and a separate tipping bucket on one gateway, each post gives tworain_dailyrows (checked withlhbox sink teston a body carrying bothdailyraininanddrain_piezo). A mapping of your own can tell them apart: ingest. - Volume. A gateway with eleven sensors at 60 s made about 60 rows a post, ~87,000 rows a day. The free plan's 5 GB of storage and 10 device sinks apply;
lhbox whoamishows your limits. - No history from ecowitt.net. The route receives what the gateway posts from now on; this guide does not cover loading an export of the station's past.
Next
- Ingest: sinks, the device route and the mapping format, if you want your own columns.
- DuckDB: connecting, writing, persistent secrets.
- Public catalogs: what publishing exposes, and how others attach it.