4 ms·

I wonder how well-motivated the OLAP use cases are. Is there really a lot of demand of doing analytics on edge devices? I see a windmill example in the introd

I wonder how well-motivated the OLAP use cases are. Is there really a lot of demand of doing analytics on edge devices? I see a windmill example in the introduction of a paper linked from the post above. Is that really a big thing in the windmill space? Are modern windmills needing to use embedded devices on them to perform real time analytics that would be enabled by OLAP engines and queries like that? Are there other such cases that folks know where this would be very useful?

iknownothow 4y ago
A lot of recent tooling around SQLite has opened up some new possibilities. The biggest one I can think of is Litestream which has allowed SQLite to have distributed reads. This makes SQLite a strong contender for serverless and extremely cheap OLAP. There have been a lot of posts about serverless Postgres as well lately in the form of neon.tech. If SQLite had better performance for OLAP queries, there is no way I wouldn't explore it as an option for work because it's so darn easy to use. The operational or hosting or cloud costs of other OLAP database is insanely high in comparison. P.S. It's my personal opinion and experience that most companies have only about 1TB or less of analytics data. Large OLAP databases are just a waste for such small data but currently apart from DuckDB there aren't any cheap competitors for these small use cases. Postgres and SQLite are too slow (without some additional engineering).
- chrisjc 4y ago
  I've been thinking about very similar needs, but perhaps accomplished in a somewhat different way. It would be really neat if SQLite or DuckDB implemented a way to create (register) read-only external tables (like Apache Iceberg). That way your large OLAP database (Snowflake, etc) could manage (DDL & CRUD) the external table, while SQLite and DuckDB would consume from it and automatically get all of the updates/deltas to the external table. clicks --> Iceberg bits --> Snowflake/MPP <--> table --> DuckDB pieces --> on S3 Snowflake could take care of all the analytical heavy lifting continually preparing/updating/unloading data to the Iceberg table in an analytics ready/friendly form (lags, leads, etc built into data). DuckDB could watch for changes on the Iceberg table (manifest) and update its internal representation of the external table as it changes. DuckDB can already read parquet files out on S3 so it should be somewhat trivial to implement? DuckDB could then handle all of the lightweight slicing/dicing of the analytics data; projections, counts, pagination, etc Here's an example of Snowflake managing an Iceberg table: https://www.youtube.com/watch?v=Kz5cWY_vRwU https://www.youtube.com/watch?v=Kz5cWY_vRwU
  - 1egg0myegg0 4y ago
    DuckDB can actually read SQLite or Postgres directly! In the SQLite case, something like Litestream plus DuckDB could work really well! Also, with Pyarrow's help, DuckDB can already do this with Delta tables! https://github.com/duckdblabs/sqlite_scanner https://github.com/duckdblabs/sqlite_scanner https://github.com/duckdblabs/postgresscanner https://github.com/duckdblabs/postgresscanner
vinay_ys 4y ago
For comparison/reference, a typical single 2MW wind turbine platform costs about $3M USD. I would expect a wind park, that has several of wind turbines, to have traditional wired Ethernet connectivity (or 5G private network, in the future) for connecting all onboard turbine diagnostics electronics to send/receive signals/commands from a central control platform onsite. A typical part failure and downtime costs a lot of money. So, typically they would invest in this 'optional' observability/diagnostics platform which costs a few thousand $$s and can easily accommodate a full server stack for local collection, buffering/storage, aggregation, ETL and push to remote cloud for further real-time monitoring and long-term component failure prediction/analysis.
dominotw 4y ago
I think there is definitely some interest in building 'data apps' where you send data file to client/browser and user slices and dices as they please instead of a set of predetermined queries/analytics. I heard of companies like 'motherduck' exploring this space.

iknownothow 4y ago

A lot of recent tooling around SQLite has opened up some new possibilities. The biggest one I can think of is Litestream which has allowed SQLite to have distributed reads. This makes SQLite a strong contender for serverless and extremely cheap OLAP. There have been a lot of posts about serverless Postgres as well lately in the form of neon.tech. If SQLite had better performance for OLAP queries, there is no way I wouldn't explore it as an option for work because it's so darn easy to use. The operational or hosting or cloud costs of other OLAP database is insanely high in comparison. P.S. It's my personal opinion and experience that most companies have only about 1TB or less of analytics data. Large OLAP databases are just a waste for such small data but currently apart from DuckDB there aren't any cheap competitors for these small use cases. Postgres and SQLite are too slow (without some additional engineering).

chrisjc 4y ago
I've been thinking about very similar needs, but perhaps accomplished in a somewhat different way. It would be really neat if SQLite or DuckDB implemented a way to create (register) read-only external tables (like Apache Iceberg). That way your large OLAP database (Snowflake, etc) could manage (DDL & CRUD) the external table, while SQLite and DuckDB would consume from it and automatically get all of the updates/deltas to the external table. clicks --> Iceberg bits --> Snowflake/MPP <--> table --> DuckDB pieces --> on S3 Snowflake could take care of all the analytical heavy lifting continually preparing/updating/unloading data to the Iceberg table in an analytics ready/friendly form (lags, leads, etc built into data). DuckDB could watch for changes on the Iceberg table (manifest) and update its internal representation of the external table as it changes. DuckDB can already read parquet files out on S3 so it should be somewhat trivial to implement? DuckDB could then handle all of the lightweight slicing/dicing of the analytics data; projections, counts, pagination, etc Here's an example of Snowflake managing an Iceberg table: https://www.youtube.com/watch?v=Kz5cWY_vRwU https://www.youtube.com/watch?v=Kz5cWY_vRwU
- 1egg0myegg0 4y ago
  DuckDB can actually read SQLite or Postgres directly! In the SQLite case, something like Litestream plus DuckDB could work really well! Also, with Pyarrow's help, DuckDB can already do this with Delta tables! https://github.com/duckdblabs/sqlite_scanner https://github.com/duckdblabs/sqlite_scanner https://github.com/duckdblabs/postgresscanner https://github.com/duckdblabs/postgresscanner

vinay_ys 4y ago

For comparison/reference, a typical single 2MW wind turbine platform costs about $3M USD. I would expect a wind park, that has several of wind turbines, to have traditional wired Ethernet connectivity (or 5G private network, in the future) for connecting all onboard turbine diagnostics electronics to send/receive signals/commands from a central control platform onsite. A typical part failure and downtime costs a lot of money. So, typically they would invest in this 'optional' observability/diagnostics platform which costs a few thousand $$s and can easily accommodate a full server stack for local collection, buffering/storage, aggregation, ETL and push to remote cloud for further real-time monitoring and long-term component failure prediction/analysis.

dominotw 4y ago

I think there is definitely some interest in building 'data apps' where you send data file to client/browser and user slices and dices as they please instead of a set of predetermined queries/analytics. I heard of companies like 'motherduck' exploring this space.