Comment by jinqueeny | Hacker News Reader

Comment by jinqueeny | Hacker News Reader

Parent Full thread

If I understand it correctly, the "business intelligence stack" you are looking for is something that bridges the gap between the online transactional processing (OLTP) and online analytical processing (OLAP). If that's the case, then some new jargons might help you:

- hybrid transactional and analytical processing (HTAP), coined by Gartner, - hybrid operational and analytical workloads (HOAP), by 451 Research - Translytical, by Forrester

If that's the solution you want to explore, TiDB (https://github.com/pingcap/tidb), the open source distributed scalable HTAP database, might be able to help you. ETL is no longer necessary with TiDB’s hybrid OLTP/OLAP architecture.

Here is a use case about how it helps the largest B2C fresh produce online marketplace in China to acquire real-time intelligence:

https://www.datanami.com/2018/02/22/hybrid-database-capturin...

Here is a tutorial about how you can try TiDB/TiSpark on your own laptop using Docker Compose: https://www.pingcap.com/blog/how_to_spin_up_an_htap_database...

Disclaimer: I work for TiDB.

The problem with hybrid solutions is that you usually want your data warehouse (which is queried by analysts) separate from your database (which is queried by your app). The data warehouse is getting random queries written by analysts, and its schema is constantly evolving as upstream data sources are added and changed. This is not a recipe for a high-availability system. Since you’re going to set up a separate data warehouse anyway, having a single database that can do both types of workload isn’t as useful as you might expect.

This is exactly how TiDB works like a charm. Internally, thanks to the Raft consensus algorithm (http://raft.github.io/), we could schedule and process the workloads separately: OLTP workloads to the leader replicas, OLAP to the follower replicas for the random or heavy analytics.

The past solution of the separate operational database and data warehouse poses great challenges for real-time analytics because it needs either data pipeline or the ETL process which could be the bottleneck of being "real-time", not to mention the waste of time, efforts and human resources maintaining multiple data warehouses. It was impossible for real-time analysis because, in the past, you would need a data pipeline, or message queue with the equivalent throughputs with your OLTP database, which I believe does not exist.

However, whether to adopt this hybrid solution depends on your specific usage scenario. For cases where users want to do real-time analysis in their data warehouse upon the same data table as in their OLTP database, TiDB is your choice.

It is absolutely a valuable feature to be able to update your data warehouse one-row-at-a-time, but this feature has to be weighed alongside all the other features. Also, the best commercial data warehouses are quite good at keeping up with small batches, so you can potentially run a "batch" data pipeline every minute or so, and get a "nearly-real-time" data warehouse in a conventional columnar design.

Reply on news.ycombinator.com