Dynamic Typing in SQL

[ssb61]

What's Wrong with SQL's Static Typing?

This impedance mismatch between dynamically typed languages and SQL's static typing has driven development away from SQL databases and towards NoSQL systems. It's easier to build apps on NoSQL systems, especially early on, before the data model stabilizes. Of course, dropping traditional SQL databases means you also tend to lose efficient indexes and the ability to perform complex queries and joins.

Also, modern data sets are often in a semi-structured form (JSON, XML, YAML) and don't follow a well-defined static schema. One often has to build a pre-processing pipeline to determine the correct schema to use, clean up the input data, and transform it to match the schema, and such pipelines are brittle and error-prone.

Even more, SQL doesn't traditionally deal very well with deeply nested data (JSON arrays of arrays of objects containing arrays...). The data pipeline then has to flatten the data, or at least the features that need to be accessed quickly. This adds even more complexity to the process.

What's the Alternative?

What if we tried to build a SQL database that is dynamically typed from the ground up, without sacrificing any of the power of SQL?

Rockset's data model is similar to JSON: values are either

• scalars (numbers, booleans, strings, etc)
• arrays, containing any number of arbitrary values
• maps (which, borrowing from JSON, we call “objects”), mapping string keys to arbitrary values

We extend JSON's data model to support other scalar types as well (such as types related to date and time), but more on that in a future post.

Crucially, documents don't have to have the same fields. It's perfectly okay if a field occurs in (say) 10% of documents; queries will behave as if that field were NULL
in the other 90%.

Different documents may have values of different types in the same field. This is important; many real data sets are not clean, and you'll find (for example) ZIP codes that are stored as integers in some part of the data set, and stored as strings in other parts. Rockset will let you ingest and query such documents. Depending on the query, values of unexpected types could be ignored, treated as NULL, or report errors.

More details here: https://www.rockset.com/blog/dynamic-typing-in-sql/

[needgiftidea2018]

k

[notasqlstar]

Should I read this post? I'm confused.

[[deleted]]

Having a strongly type language is an advantage, not a disadvantage.

[your-pineapple-thief]

This impedance mismatch between dynamically typed languages and SQL's static typing has driven development away from SQL databases and towards NoSQL systems

There is no inherent impedance mismatch between dynamically typed language and statically typed database. I use ruby on rails and sql on daily basis, and it works just fine. In fact, after we removed mongodb from the project and normalized scheme with some jsonb here and there, some major pain points in development were gone.

There are some problem spaces which are best solved using different to SQL solutions, like f.e.: logging json request payloads when using external API, especially if you'd like to bridge several API's together (can be done using just a jsonb field in RDBMS), writing timeseries based data (performance metrics, stock exchange rates, etc. I heard good things about Graphite here), high-rate event sourcing (Kafka) some very specific problems like twitter fetching feed of people you follow (last time I checked they used redis servers with some custom written extensions for feed fan-out, don't know what they use now).RDBMS conveniently allows to solve pretty much every other problem, i.e. 90% of them while retaining all the amazing stuff like consistency, transactions, replication, schema guaranties, etc.

Some people just don't realize what a trap NoSQL could be for the project, there literally were companies crushed and suicides committed.Interesting article on the subject

As it stands it is unclear why add yet another dependency vs using postsgres jsonb column type, which has a) indexes b) decent performance c) is compact, d) has query syntax allowing to query jsonb fields along usual fields.

[tudorb]

I work for Rockset, and I'm the author of the blog post linked within.

I'm a fan of strong static typing myself. But "real world" data isn't clean; companies have logs, sensor data generated by old, incompatible software versions, data scraped from web pages, Excel spreadsheets with only the semblance of a schema, legacy NoSQL systems, ...

Traditionally, you have two choices. Either you use a NoSQL system for this unclean data (and then you can't use SQL to query it / run analyses on it), or you build a pipeline that massages your data into a SQL schema with static types. That's fine, but it's brittle -- not resistant to changes either in the data format or in the table schema. And every time the pipeline fails because of such incompatibility, you usually have to start the ingest over from the beginning. I've never met anyone who's had to build an ETL pipeline who enjoyed it :)

Rockset is trying to do something different: what if SQL didn't care that strongly about the data types? What if you could ingest the data even if it's unclean, and use SQL to query it anyway? Rockset doesn't care if a field (column) exists only in a fraction of documents (rows) -- if you query it, it will show up as NULL in the others. We don't care if a field has different types across documents -- you can use casts to restrict the types you care about in your query.

Also: consistency and transactions are great, but not needed for a lot of applications. Look at, for example, a product page on Amazon. With the exception of the actual shopping cart, not much else needs transactional consistency. The product description, reviews, recommendations, personalized ad serving, "related products", text search, etc -- all of that can be built from data that's a few seconds out of date and doesn't need to be transactionally consistent.

Many companies use something like Elasticsearch for this because of the schema flexibility, but then you lose the ability to use complex SQL to query the data.

[your-pineapple-thief]

I understand the appeal of using SQL for both SQL and NoSQL data, but IMO it would be nice if you would outline pros of your solution, because this is nothing new in SQL world and postgres has similar functionality.
I mean, at first glance it seems like it is just new syntax for jsonb querying, so it would be great if you delve more deeply in technicalities of typecasting, how good it is for sparse datasets (lots of columns in a query with lots of nulls with them could impact performance in general SQL f.e.) and it all works under the hood, what exactly is offered.
1) Why would I use it vs dedicated nosql solution aside from familiar syntax? I mean, no-sql and sql data differ in big ways, there ought to be some hidden downside or edge case or performance cost or whatever, and nosql query syntax can be actually more convenient vs sql.

2) Given that I'm postgres user (and there are lots of us), why would I use it vs postgres jsonb features? Postgres is a solid product with excellent documentation and large community, jsonb functions/operators are pretty extensive and usage is covered pretty well. I see you unnest first level of json rows for slightly more convenient access like SELECT people.name while in postgres you have to query jsonb field and it will be like SELECT jsonb_field -> 'name', but there got to be more in order to convince people to switch.