8 ms·

This is a huge concern for me at my current organization. Dev has decided to put all data into mongoDB. Yet all decisions are based on that data and the tools

This is a huge concern for me at my current organization. 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 for seamless flow (ETL) from mongoDB. That data is important for deriving decisions that affect revenue and costs. Where are solutions for the data analysts and scientists? Frankly I'm pretty sick of hearing it can just be automated. In my mind there has to be a decent "business intelligence stack". I'm not sure I'm coining that because I didn't get good search results from that phrase. Believe me I've been trying to find solutions. I believe there is big opportunity in building out this sort of stack that bridges data management and data analysis. Sure you can call IBM, Microsoft, Dell, HP but be prepared for big costs and huge software bloat. I would like simplified solutions and options that can fit with most industry standard tools. I'm also willing to work with anyone on this as well.

ty64 8y ago
This exists: https://www.mongodb.com/download-center#bi-connector https://www.mongodb.com/download-center#bi-connector
- oneweekwonder 8y ago
  > 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 https://github.com/EnterpriseDB/mongo_fdw
threeseed 8y ago
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.
mindingdata 8y ago
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.
- sateesh 8y ago
  Yes you can tail oplog, but in a sharded setup with multiple clusters this becomes a pain.
  - sbr464 8y ago
    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.
- chiaolun 8y ago
  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.
  - kalendos 8y ago
    Would you mind sharing the process? I'm curious which flow you use to materialize the oplog in Athena/S3.
- Ankhers 8y ago
  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/ https://docs.mongodb.com/manual/changeStreams/
mr_toad 8y ago
> (ETL) from mongoDB Why not query the data directly in MongoDB?
- squirrelicus 8y ago
  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"
- twblalock 8y ago
  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.
deleted 8y ago
[deleted]
jinqueeny 8y ago
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 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-capturing-perishable-insights-yiguo/ 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_in_5_minutes_with_tidb_tispark/ https://www.pingcap.com/blog/how_to_spin_up_an_htap_database... Disclaimer: I work for TiDB.
- georgewfraser 8y ago
  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.
  - jinqueeny 8y ago
    This is exactly how TiDB works like a charm. Internally, thanks to the Raft consensus algorithm (http://raft.github.io/ 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.
eksemplar 8y ago
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.
tixocloud 8y ago
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.
gaius 8y ago
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.
- tjpnz 8y ago
  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.
alittletooraph 8y ago
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.

ty64 8y ago

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

oneweekwonder 8y ago
> 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 https://github.com/EnterpriseDB/mongo_fdw

threeseed 8y ago

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.

mindingdata 8y ago

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.

sateesh 8y ago
Yes you can tail oplog, but in a sharded setup with multiple clusters this becomes a pain.
- sbr464 8y ago
  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.
chiaolun 8y ago
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.
- kalendos 8y ago
  Would you mind sharing the process? I'm curious which flow you use to materialize the oplog in Athena/S3.
Ankhers 8y ago
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/ https://docs.mongodb.com/manual/changeStreams/

mr_toad 8y ago

> (ETL) from mongoDB Why not query the data directly in MongoDB?

squirrelicus 8y ago
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"
twblalock 8y ago
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.

deleted 8y ago

[deleted]

jinqueeny 8y ago

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 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-capturing-perishable-insights-yiguo/ 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_in_5_minutes_with_tidb_tispark/ https://www.pingcap.com/blog/how_to_spin_up_an_htap_database... Disclaimer: I work for TiDB.

georgewfraser 8y ago
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.
- jinqueeny 8y ago
  This is exactly how TiDB works like a charm. Internally, thanks to the Raft consensus algorithm (http://raft.github.io/ 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.

eksemplar 8y ago

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.

tixocloud 8y ago

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.

gaius 8y ago

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.

tjpnz 8y ago
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.

alittletooraph 8y ago

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.