November 2, 2020 at 12:00 am
Comments posted to this topic are about the item NoSqlOnSql
November 2, 2020 at 12:51 pm
Thanks for the interesting post. I believe it is cool from a "can I do this?" perspective, but what would be the practical use case of such a transpiler? If I were to require some type of NoSQL platform such as a document store, a relational database is most likely NOT the place I should be storing these documents. NoSQL document stores are optimized for storing and querying JSON documents. I would argue that although SQL Server "CAN" store JSON documents and T-SQL has syntax for this, its use as a full on document store is extremely limited and writing compiled code to treat it as such is probably going to incur a substantial amount of technical debt. It is nice to have the ability to store JSON here and there, but it still requires a relational setup and, IMHO, shouldn't be thought of as a "document store" or even NoSQL capabilities in SQL Server. That mindset may lead to some really poor design decisions. Again, very cool as an exercise, but I think the practical production use case may be very limited. Interested in your response 🙂
Jared
CE - Microsoft
November 2, 2020 at 3:23 pm
I'm also curious how, being a write-only solution, your NoSQL implementation handles deletes, and by "delete" I mean in the logical sense, where, for some reason, the Homer record and all of its children need to be removed from the database, maybe due to software requirements, or something more encompassing, like GDPR.
November 2, 2020 at 5:36 pm
Thanks for the interesting post. I believe it is cool from a "can I do this?" perspective, but what would be the practical use case of such a transpiler? If I were to require some type of NoSQL platform such as a document store, a relational database is most likely NOT the place I should be storing these documents. NoSQL document stores are optimized for storing and querying JSON documents. I would argue that although SQL Server "CAN" store JSON documents and T-SQL has syntax for this, its use as a full on document store is extremely limited and writing compiled code to treat it as such is probably going to incur a substantial amount of technical debt. It is nice to have the ability to store JSON here and there, but it still requires a relational setup and, IMHO, shouldn't be thought of as a "document store" or even NoSQL capabilities in SQL Server. That mindset may lead to some really poor design decisions. Again, very cool as an exercise, but I think the practical production use case may be very limited. Interested in your response 🙂
The most common would just be to store JSON data in a relational database for reporting purposes such as ad-hoc querying. For example, I work with a large amount of document stores that store related data from applications. While you can query the data directly in the document store, the relationships are not exactly defined via the document store. Dumping it into a RDBMS from the store and defining the relationships could be useful.
Howerver, there are still better middleware solutions for this that help pull data from document stores into tabular datasets without the use of such a thing, especially if you are in the cloud.
November 3, 2020 at 1:00 am
I'm also curious how, being a write-only solution, your NoSQL implementation handles deletes, and by "delete" I mean in the logical sense, where, for some reason, the Homer record and all of its children need to be removed from the database, maybe due to software requirements, or something more encompassing, like GDPR.
Every property has a timestamp, NOSQL select always returns the latest value for the property. By assigning null to each property in an object we can logically delete that property, thus {Name: "Homer", Children:[..]} would become an empty object { }.
As all queries are based on property values, an empty object is unsearchable as it doesn't have any query that can be matched. So, it cannot be returned by any query (except querying for the object ID which would return and emptt row). This way we get logical deletion, and at the same time, keep the physicial object and all its history.
Physicial deletion can be achieved by deleting objects with no properties and orphaned properties. This would compact the table and break the write-only principle.
It is trivial to implement, and you are free to do it if you need it. Nevertheles I see it more as a maintenance operation than a user operation.
November 3, 2020 at 2:02 am
Jared wrote:Thanks for the interesting post. I believe it is cool from a "can I do this?" perspective, but what would be the practical use case of such a transpiler? If I were to require some type of NoSQL platform such as a document store, a relational database is most likely NOT the place I should be storing these documents. NoSQL document stores are optimized for storing and querying JSON documents. I would argue that although SQL Server "CAN" store JSON documents and T-SQL has syntax for this, its use as a full on document store is extremely limited and writing compiled code to treat it as such is probably going to incur a substantial amount of technical debt. It is nice to have the ability to store JSON here and there, but it still requires a relational setup and, IMHO, shouldn't be thought of as a "document store" or even NoSQL capabilities in SQL Server. That mindset may lead to some really poor design decisions. Again, very cool as an exercise, but I think the practical production use case may be very limited. Interested in your response 🙂
The most common would just be to store JSON data in a relational database for reporting purposes such as ad-hoc querying. For example, I work with a large amount of document stores that store related data from applications. While you can query the data directly in the document store, the relationships are not exactly defined via the document store. Dumping it into a RDBMS from the store and defining the relationships could be useful.
Howerver, there are still better middleware solutions for this that help pull data from document stores into tabular datasets without the use of such a thing, especially if you are in the cloud.
I agree this is mostly a "can I do this?" thing, which has been really cool to implement. But I don't see what additional relational setup is needed. If it allows to store the same type of data as a "document store", query it in the same way as a "document store", and perform the same type of operations as a "document store", then I don´t see why it can´t be though of as a "document store". I reckon it is not production ready, but this is because of still unhandled edge cases in the transpiler, not because the underlying model, which is purely relational, is not solid enough.
Storing JSON data is definitely a use case. Using NOSQL syntax to define relationships and persist them in SQL VIEWs would be a refinement on such use case.
Middlewares, usually build intermediate tables into your SQL domain from the NOSQL store. They can't be used with live data, and usually defining the model takes more effort than issuing a SELECT. Also you have to deal with the impendance between database engines (ie: float precissions, strings collation,...).
On the other hand, NOSQLONSQL can be used with live data, and impendance between SQL and NOSQL data is zero, as you never leave the SQL RDMS domain.
About some other possible use cases:
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply