NoSqlOnSql

  • Comments posted to this topic are about the item NoSqlOnSql

  • 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

  • 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.

  • 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.

  • Dan Bragg wrote:

    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.

    • This reply was modified 1 year, 9 months ago by  jsegarra.
    • This reply was modified 1 year, 9 months ago by  jsegarra.
  • xsevensinzx wrote:

    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:

    • You are not always allowed to choose your database server, so sometimes deploying a NoSQL store is not feasible and still you have unstructured data that needs to be stored. I have seen nasty things done on XML or JSON fields. In TSQL these fields are querieable, but performance decreases dramatically beyond a million records (as each XML/JSON value needs to be parsed at run time). I know about indexes on JSON columns, but they only work if your JSON data is pretty consistent (ie: more or less the same structure).
    • Integration, you can mix full SQL with NOSQL data within the same database engine (ie: they are all relational queries)
    • Being able to use the superb toolset of Sql Server (always on, replication, backups,...) for your JSON store
    • In my NOSQL implementation referential integrity is quite relaxed, you only need to keep all the properties for an object in a single table, and filtering is done bottom up. So, the current PARENT-ID hierarchy which is based in a "long" can be expanded to a "string long",  that would allow to shard the data and query using a simple map-reduce. So we would get unlimited scalability, regardless of the data model. I haven't tried this, but in theory it is possible.
    • When developing, it facilitates code first approaches or prototyping, it also simplifies security, no need for intermediate DAO objects.
    • Being able to track all changes in a record, I think this is pretty cool and useful in many business scenarios. No need for login tables, or "Created at", "Modified at" fields. Actually Bitcoin is built on the principle of keeping track of all changes.....

    • This reply was modified 1 year, 9 months ago by  jsegarra.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply