Lightweight data analytics using SQLite, Bash and DuckDB
Over the last 12 months, in parallel to using Google BigQuery, I have built my own processing pipeline using SQLite and DuckDB. What amazes me is that it works surprisingly well and costs much less than using BigQuery. Roughly speaking, here's what I do: A SQLite database receives IoT sensor data via a very simple PHP function. I currently use the FlightPHP framework for this. The data is written to a table within the SQLite database (WAL mode activated) and states are updated by the machines using triggers. Example of a trigger CREATE TRIGGER message_added AFTER INSERT ON messages BEGIN…
What it does
In the maker’s words, at launch
Over the last 12 months, in parallel to using Google BigQuery, I have built my own processing pipeline using SQLite and DuckDB. What amazes me is that it works surprisingly well and costs much less than using BigQuery. Roughly speaking, here's what I do: A SQLite database receives IoT sensor data via a very simple PHP function. I currently use the FlightPHP framework for this. The data is written to a table within the SQLite database (WAL mode activated) and states are updated by the machines using triggers. Example of a trigger CREATE TRIGGER message_added AFTER INSERT ON messages BEGIN INSERT OR REPLACE INTO states VALUES ( new.id, new.status, new.time_stamp, new.current_present, new.voltage_present) This allows me to query the current status of a machine in real time. To do this, I again use a simple PHP function that provides the data via SSE. In the frontend, a simple Javascript method (plain vanilla JS) retrieves the JSON data and updates the HTML in real time. const source_realtime = new EventSource("https://myapi/sse_realtime_json"); source_realtime.onmessage = function(event) { var json = JSON.parse(event.data); }; For a historical analysis - for example over 24 months - I create a CSV export from the SQLite database and convert the CSV files into Parquet format. I use a simple BASH script that I execute regularly via CronJob. Here is an excerpt # Loop through the arrays and export each table to a CSV, then convert it to a Parquet file and load into the DuckDB database for (( i=0; i<${arrayLength}; i++ )); do db=${databases[$i]} table=${tables[$i]} echo "Processing $db - $table" # Export the SQLite table to a CSV file sqlite3 -header -csv $db "SELECT * FROM $table;" > parquet/$table.csv # Convert the CSV file to a Parquet file using DuckDB $duckdb_executable $duckdb_database <<EOF -- Set configurations SET memory_limit='2GB'; SET threads TO 2; SET enable_progress_bar=true; COPY (SELECT * FROM read_csv_auto('parquet/$table.csv', header=True)) TO 'parquet/$table.parquet' (FORMAT 'PARQUET', CODEC 'ZSTD'); CREATE TABLE $table AS SELECT * FROM read_parquet('parquet/$table.parquet'); EOF Now finally my question: Am I overlooking something? This little system works well for currently 15 million events per month. No outtages, nothing like that. I read so much about fancy data pipelines, reactive frontend dashboards, lambda functions ... Somehow my system feels "too simple". So I'm sharing it with you in the hope of getting feedback.
Does the same job
all alternatives →- MIMongita is to MongoDB as SQLite is to SQL2021 · github.com · ▲126
- DCDatasette – Create and publish an API for SQLite databases2017 · simonwillison.net · ▲269
- DRDuckDB + React = SQLRooms Framework for Analytics Apps Without Backend2025 · sqlrooms.org · ▲9
We’ve built SQLRooms, an open-source framework for creating single-node data analytics apps powered by DuckDB. It lets you build fully client-side, data-centric apps using React and DuckDB running in the browser (via WebAssembly) or in Electron. No server or backend is required — apps can work offline, preserve data privacy, and run queries on large datasets with sub-second performance. Features: - Query large datasets in browser with DuckDB (WASM) - Modular design for building composable data UIs (query editors, dashboards, notebooks, etc.) - Data privacy-preserving AI assistant that can…
- SQSQLite query inside a Bash function2021 · ▲134
Part of the workflow for building my website is the generation of a table in tab-separated column format (.tsv). The source data is found in four other .tsv files. I use an SQLite query to perform a 4-way join and write out the new table. For convenience, I wrote a script that encapsulates the query inside a Bash function. The example below illustrates this technique. repertoire() { pushd $CMM_SOURCES/_data sqlite3 <<EOS .headers on .mode tabs .import category.tsv category .import composition.tsv composition .import concert.tsv concert .import program.tsv program .once repertoire.tsv…
- SSSQLFlow: SQL Stream Processing Using DuckDB2023 · github.com · ▲7
I'm a couple years late to the party but DuckDB is blowing my mind. I couldn't find an good embedded DuckDB stream processing solution so I hacked my own, calling it SQLFlow: https://github.com/turbolytics/sql-flow SQLFlow enables writing stream transformations in pure sql, executed using DuckDB. The goal was to create a lightweight, performant stream processing engine using pure SQL transformations, DuckDB didn't disappoint! I'd love your feedback, feature requests, impressions, or just comments. I'd love to turn this into a stable, usable project that people are…
More dev tools this month
the category →
Open-source GTM skills for technical founders
Dev tools · 29d ago · gtmcofounder.com
OpenTrailPaper is open-source bike computer firmware for the LilyGO T5S3 4.7" E-Paper PRO. It supports offline maps, GPX routes, FIT recording and Bluetooth sensors.
Dev tools · 1d ago · opentrailpaper.com
Launched alongside, December 2023
the whole month →- BM
Hi HN! I’m proud to share that we have built a real 3rd party iMessage client for Android. We did it by reverse engineering the iMessage protocol and encryption system. It's available to download today (no waitlist): https://play.google.com/store/apps/details?id=com.beeper.ima and there's a technical writeup here: https://blog.beeper.com/p/how-beeper-mini-works. Unlike every other attempt to build an iMessage app for Android (including our first gen app), Beeper Mini does not use a Mac server relay in the cloud. The app connects directly to Apple…
Commerce · 2023 · beeper.com
- HA
Hey! I made Heynote entirely for my own use case. For many years, I always had an Emacs instance running with the scratch buffer open, even long after I had abandoned Emacs as my programming editor in favor of more recent IDE:s. The simplicity of having just one big scratch buffer appeals to me, but I still want to separate the different things I jot down somehow (without using tabs or similar). Previously, my solution was to insert a bunch of blank lines between the notes, but hitting C-A would still select the entire buffer. That's why I came up with the concept of "blocks", which turned…
Work · 2023 · heynote.com
FuseBase▲941Client collaboration platform for professional services
Dev tools · 2023 · thefusebase.com
Beep! 2.0▲871Fastest tool to assign and share visual tasks with your team
Work · 2023 · justbeepit.com
