Hacker News Reader

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.

Others are sharing out of the box solutions.

But I will say that many moons ago when I did actually write stuff for Mongo. The oplog was a god send. You can "tail" the oplog, and get every transaction in near real time. We used this for updating Elasticsearch indexes etc in what is basically realtime, without having to poll or modify existing code at all.

Yes you can tail oplog, but in a sharded setup with multiple clusters this becomes a pain.

The new change streams api resolved a lot of this, since it allows your to utilize the aggregation framework to subscribe to highly filtered/specific queries. The upcoming 4.0 release is expanded further.

The oplog is awesome! It provides an immutable record which is really useful - we materialize the oplog directly in Athena to get a time-travelling database for debugging purposes.

Would you mind sharing the process? I'm curious which flow you use to materialize the oplog in Athena/S3.

Mongo 3.6 introduced something called Change Streams[0] which is basically a safer way to tail the oplog. It is also supposed to work well in a sharded environment.

[0] https://docs.mongodb.com/manual/changeStreams/

Dev has decided to put all data into mongoDB. Yet all decisions are based on that data and the tools we have do not allow

Seems to me that it’s on your devs to explain to the business why their poor technical choices now necessitate a substantial additional investment to get a usable solution. When they could have just used Postgres, and they knew it.

In my experience these types of situations only come about through ignorance. This is anecdotal so YMMV but most people I've worked with who propose Mongo don't know SQL and don't generally want to take the time to learn it.

Quite often they will have also read somewhere that joins are slow and have managed to convince themselves that the solution is to avoid relational databases altogether. Or maybe that's just how they justify it.

You can connect Hadoop/Spark directly to MongoDB so in some cases you may not need to do an ETL at all.

You can also use something like NiFi which supports MongoDB and will allow you to shift data out to Avro/Parquet on HDFS/S3 for your data scientists to use.

As for BI stack. Not sure what you mean. There are hundreds of tools which blend data management and data analysis. You can do this with Hortonworks (Atlas + Spark) or Alteryx for example.

I feel your pain on the lack of love for analysts and data scientists. IT has just gone and implemented “shiny new database infrastructure” and have been saying we are way ahead of the curve.

Problem is that it doesn’t work for analysis and we are still using our old platform, which works just fine.

Beta of MongoDB Charts was announced at the annual conference. It lets you build visualizations on MongoDB data w/o moving data around / ETL. Or if you want to use your existing BI tools, use the BI Connector and the ODBC driver.

I think it’s less of an issue. The way analytics often work enterprise, is that you pay someone a lot of money to build cubes on your data that does a big chunk of the actual data science, before it’s handed off to economists who can’t code.

Then they’ll build their BI models in some high level drag and draw system, and pay extra whenever they realize they didn’t get everything they needed in a cube.

The only place I’ve seen actual data scientists is at the university or at the 100% software companies that sell both the solution and the data cube. I’ve never met a real world analytic who could actually code. :p

You’d want to keep a separate dB for your analytics either way though, as they typically eat up quite a lot of load and you don’t want that to interfere with your production environment when you don’t have to.

This exists: https://www.mongodb.com/download-center#bi-connector

> The MongoDB Connector for BI is available as part of the MongoDB Enterprise Advanced subscription,

So may one only use it with a subscription?

I got mongodb postgres foreign data wrapper[0] working in a previous life.

[0]: https://github.com/EnterpriseDB/mongo_fdw

> (ETL) from mongoDB

Why not query the data directly in MongoDB?

Because queries, specifically those that aggregate, consume memory and CPU on the live prod db. Something a simple scan cursor doesn't do. If the resource consumption is prohibitive, which it often is in mongo, and your use case is non-realtime, it's typically better to script the aggregation outside the DB query (or query an ETL'd aggregation store that doesn't impact customers when you lock it up)

Edit: changed "offline" to "non-realtime"

You should at the very least be doing analytics queries on a replica, or you could be affecting the database performance (and the customer experience) in production.

But even if you did that, you'll find that you'll need joins and aggregations that are painful to do in Mongo yet trivial to do in a system that is designed for them.