Objective :

We want to be able to validate that data matches some requirements. We would like to to perform this data validation by adding a JSON schema in Mongo (such as it is described here : https://docs.mongodb.com/manual/core/schema-validation/).

Problem is that current implementation of the current Mongo DB Kafka Sink connector does not implement the required elements to benefit from features brought by this KIP : https://cwiki.apache.org/confluence/display/KAFKA/KIP-610%3A+Error+Reporting+in+Sink+Connectors

So if we define such a validation on Mongo, if a message has a value that does not match the definition, it would not go in the dead letter queue, and the connector would "die".

We would like to enhance the current implementation of the Mongo Sink connector to benefit from it.

Added as a suppport case here : https://support.mongodb.com/case/00634630

When using the connector as a Source, i.e we capture change streams from the Source Mongo DB and stream that to a Kafka endpoint.

Imagine these are updates on financial transactions in mongodb and they are NOT tolerant to
1) missed data and
2) duplicated data
in that order.

So, we need to make sure that the Change Streams that we are observing(matching) on, are delivered once and exactly once to the Kafka pipeline. (Blog on the same : https://www.confluent.io/blog/exactly-once-semantics-are-possible-heres-how-apache-kafka-does-it/). If exactly-once semantics are enabled, it makes commits transactional by default.

We were informed verbally that the connector supports once-only-semantics, however if it is not available in the worst case, we would need atleast-once-semantics enabled to make sure that we do not loose data in the any case.

One of the issues that our team has been talking about is when getting data from MongoDB, via a Kafka connector, and sending it through to Kafka we try to enforce schemas in Kafka but that schema is not enforced on the MongoDB data. This leads to developers needing to make sure they let the Data Engineering team know when their schema evolves so we can accommodate that change in the Avro schema. Our thought is to potentially have the developers use the Confluent Schema Registry to serialize their data to Avro prior to writing it to MongoDB. This would ensure that downstream consumption is aware of any new fields without having to alert anyone. Is that something that MongoDB supports or recommends? We are completely open to any other recommendations.

The BI Connector facilitates large scale (1000s) of "personal data marts" by acting as a controlled go-between enduser tools like Tableau and a "main" data collection. It is not practical or even desirable to have pass-thru authentication of all these users to the backend mongodb database. Instead, the BI connector could use a special collection in a mongodb instance (not necessarily the target!) to hold SHA(password), name, and YAML equivalent. When started, the mongosqld would verify command line inputs of SHA(password) and name and context, etc. and if OK, would exec an appropriately password-protected endpoint at 3307 with the config already loaded from the special collection. Security is still end-to-end enabled, but now it becomes MUCH easier to manage many users because the capability scope has been both narrowed to read-only and expanded via the config. And the config should be stored as real MongoDB data, not YAML. In this way, the config itself is easily queryable. I can ask "what users are having customer ABC123 filtered out between date 1 and 2" or "when did user X have an initial setup" or "lock out users X and Y", etc. It is "almost" possible to do this now but too much information is exposed in getting configs and I do not think there is a practical way to expose an endpoint that is password protected for a single user. The passthru auth would force me to actually create that user on the target DB and that is what I want to avoid. If you're wondering, this whole thing would sit behind a self-service "warehouse data service" website. If entitled, the service would fire up a docker with mongosqld and the additional info required for it to hit the special collection as described above. After successful launch, the site would say "congrats; you now have a MySQL endpoint at machine:3307. Please connect using your name and password."

Usually in defining schemas for SQL databases, you can specify a max length for the size of a char or varchar column. It'd be nice to have the ability to do that in a schema that's passed to the mongosqld BI Connector process.

The only option now is to specify a max varchar size that applies to all varchar fields. It'd be nice to be able to define this on a per field basis.

This is an issue for a customer I'm working with because of the way their BI tool allocates memory for temporary objects created when bridging the SQL data source and their front-end app -- shorter varchars can significantly improve memory utilization and latency.