8 ms·

This was a nice read for the history. Honestly, being in a big legacy industry (Insurance), it's as though NoSQL never happened. We're too big, at least my or

This was a nice read for the history. Honestly, being in a big legacy industry (Insurance), it's as though NoSQL never happened. We're too big, at least my organization, to have made the wholesale change and we've been plugging along mostly in Teradata and DB2 for a long time. Teradata in particular has performed well across a variety of use cases, its only large downside being the cost. Anyhow, only point I'd add is that I'm not sure that SQL-like languages added on top of NoSQL solutions was really that bad of a thing. It's not as though there's one flavor of SQL to rule them all on RDBMSs. I get that the bones are largely the same - SELECT, FROM, WHERE, most join types, aggregates, etc. But take a really common analytic use case - persisted transactional data. Something like this - SELECT A, B, C, FROM SomeTbl WHERE SomeDate BETWEEN SomeBegin AND SomeEnd QUALIFY RANK() OVER (PARTITION BY A, B ORDER BY SomeOtherDate DESC) = 1 Pretty sure that SELECT, FROM, WHERE will work just about anywhere. That QUALIFY clause is written for Teradata, and once you get into the ordered analytic functions (as one example), you need to know the implementation relevant to the RDBMS you're dealing with. It's not uncommon for a large enterprise to have MS SQL, DB2, Teradata, Oracle, etc. all under one roof. As a data engineer, you still need to know the differences - first to get the data out, and then to optimize. Dunno, I could be way off on this, but that's been my experience at least. I think the bigger issue is that you can use an RDBMS to do NoSQL-like things, such as key-value stores, with the flexibility to structure your data if you need that later. So why not start with a relational database?

Clubber 9y ago
>So why not start with a relational database? I asked the same question. The biggest reason I've heard is RDBMS's don't horizontally scale well, meaning you can't easily have 50 replicated nodes across the globe and expect it to perform well, or setup easily, because it's fairly complicated with an RDBMS. There's things like Oracle's grid or SQL's high availability clusters, but they get complicated fast, particularly when you need a bunch of nodes. Obviously it was a solution for a very large amount of unstructured or semi-structured data that needed to be redundant across networks. With NoSQL, the key/values are a lot simpler to replicate apparantly. It's a Google/Twitter size problem that most industries wouldn't have, but since it's the new shiny thing, you know how that goes.
- SmellTheGlove 9y ago
  > It's a Google/Twitter size problem that most industries wouldn't have, but since it's the new shiny thing, you know how that goes. You nailed it on both points. Very few organizations are going to need that kind of scale, but many want to think that they will! Even when you get to horizontal scaling, you can simply replicate what needs to be replicated. Key-value is really just a subset of relational data, right? If so, I bet we can query and push only that data out across the nodes. It's not to say non-relational models don't have a place, but they're the exception as far as I'm concerned. I prefer to start with relational and only move off of that if I can justify it. As I said upthread, though, I work in insurance and you might imagine relational works well for us.
  - Clubber 9y ago
    Also, the unstructured data is another thing. For example, lets say you need to store a bunch of contracts (as in your industry) in PDF/TIFF or whatever. The filesystem isn't a really good place to store it because you have a loose coupling between the index in the table and the file on the filesystem. That can get messy and unless security is tight, some developer or admin could muck something up on the filesystem. This is particularly dangerous when you need to keep contracts over a period of time (say 7 years). If you have a really large amount of files, you'll either need a huge array, or figure out a way to distribute them across several NAS's or whatever, then that gets complicated. The RDBMS is a little better at storing this type of data, but it has it's own problems. Backups quickly become huge and a massive undertaking. Development databases now have a bunch of missing data (because who wants to replicate that down to lower levels), you still have the same problem when you have a large amount of data that you need to shard across NAS/arrays, etc. The NoSQL seems like a third option that may well be worth looking into. Again this is for unstructured data that you just need to reference and not necessarily query against. (like stored contracts).
    - sk5t 9y ago
      > That can get messy and unless security is tight, some developer or admin could muck something up on the filesystem This isn't a great reason not to use a filesystem--it's a reason not to let staff have at the filesystem like cowboys, and not to avoid replicating and backing up the keys and values on the filesystem as if they are really important. That said, there isn't really a solid replacement for a database transaction involving both fields + blob, but most situations are okay with, say, stowing a blob in S3, and later making a half-hearted attempt to delete it if the associated database transaction fails.
tomnipotent 9y ago
>> Pretty sure that SELECT, FROM, WHERE will work just about anywhere Cassandra & CQL is a prime example. CQL become the de facto interface for getting data out of Cassandra, even without the benefits of JOIN's & sets.
- pixelmonkey 9y ago
  CQL is not SQL. CQL does not even have SQL SELECT. There is no support for arbitrary WHERE clauses, for example. You need to either slice an ordered column, or select a row by primary key -- and nothing else! They have an ALLOW FILTERING option which lets you "do it anyway", but this reduces down to paging the whole dataset without the WHERE applied, and then doing client-side filtering. I always felt that CQL did more harm than good for Cassandra. It's not like Redshift -- which implemented a full mostly-Postgres compatibility layer. Cassandra's CQL "looks like" SQL in the same way JavaScript "looks like" Java. Similarity by superficial appearance only; under the hood, totally different.
  - ma2rten 9y ago
    It's not just a subset, but there are also incompatibilities like limit limits the number of rows scanned not the number of rows returned.
  - stubish 9y ago
    I think the opposite, and that CQL is the only reason people still adopt Cassandra today. Remember Thrift, which started by using terms like columns and rows for completely different concepts and went downhill from there? I felt it was a very interesting choice to have CQL only capable of expressing queries that Cassandra could perform quickly. It forced people to adjust their data models immediately, rather than defer the problem until the performance issues got too deep to ignore. Its a different mindset to relational databases, for different sorts of problems. If only they had used the same sort of restraint on other features attempting to make Cassandra do things it is unsuitable for, such as 'transactions' and counters, which are just an embarrassment of problems.
  - tomnipotent 9y ago
jjirsa 9y ago
The reason people don’t start with a relational DB has nothing to do with the data model or query language - people choose databases like Cassandra because scaling to 500 Postgres or MySQL instances holding a combined petabyte of data is horrific, but it’s dirt simple in things like Cassandra The query language is a side effect of the underlying storage engine - you don’t choose it because you want a key value store, you choose it because you want horizontal scalability and cross wan HA
- 6nf 9y ago
  You make it sound like many people are using petabyte scale databases. This is not even close to true. ESPECIALLY when you're just starting out. Most new projects will start with almost no data at all and the vast majority of projects will never ever get to the petabyte scales.
  - jjirsa 9y ago
    Many people ARE using petabyte scale databases. They don't start out that way, but they will be if they're successful, and if they're not they probably shouldn't be running their own databases anyway (go RDS it up or something).
    - deleted 9y ago
      [deleted]
    - eberkund 9y ago
      Unless the amount of data you are collecting is increasing at a vastly unsustainable rate, you will be making megabucks and be able to afford a team of engineers to undertake a major redesign for version 2. But picking technologies based on scale at the onset is going to make getting to that point all the more unlikely.
    - happymellon 9y ago
      What? No they aren't, I work with satellite imagery processing which is quite large in it raw data format, and after a decade we are not dealing with petabytes of active data, hundreds of gigs for a full earth coverage. Before that I have held positions in finance, dealing with realtime transaction processing. We did not work in petabytes. If you are working in petabytes you are storing crap in your production database, and 99% of that data is wasted.
ojosilva 9y ago
>I think the bigger issue is that you can use an RDBMS to do NoSQL-like things, such as key-value stores, with the flexibility to structure your data if you need that later. So why not start with a relational database? I've tried that in the past and failed miserably. 1) values in a key-value table will endup needing to hold nested data structures, such as a JS object/hash. Ie. mykey={...} 2) turning values to JSON (or some other serialization) makes it impossible to concurrently update or search subkeys. 3) so you convert your complex (sets, hashes) key-value data into several rows that hold one subkey per row, so now you have updatable rows but still no indexable solution and a serious performance problem. 4) so you create a multi column type-segregated table (one column for each DB type) for indexing your key-values and making them searcheable. That also requires a metadata table to go with it so that you know in which column your key or subkey is indexed. 5) say you successfully implemented your key-value store with your RDBMS. You still don't have usable joins (you don't have relational data) or a real migration path out of your key-values. Trust me, don't just put keys and values in a relational DB. Start with the right tool for the job, either make your schema relational from the beginning or use a proper KV or document store.
- kazagistar 9y ago
  1. Postgres has a json and jsonb datatype specifically to be your values. 2. It has built in query and modification tools that can manage the nested objects and array and whatnot. 3. It let's you build indexes to help with all this, including reverse indexes for asking something like "is this string in the array inside this object". 4. You don't have to specify your schema in sone horrid way, you can just throw what you want at it and specify later, and get performance numbers that are competitive and even exceed some popular NoSql stores. 5. You can mix and match freely with all the niceties of a proper SQL database with transactions, joins, constraints, etc, as you see fit to evolve. So yeah, don't just throw your keys and values into some text table somewhere; but use instead one of the best proper kv stores, that just happens to also be one of the best sql databases, and figure out how you want your schema you need it.
  - Mikushi 9y ago
    Same can be said of MySQL. Not comparing to Postgres, but I've been amazed at how many people do not know that either of these as a powerful JSON type.
INTPenis 9y ago
The thing about the NoSQL trend is that it's still very valid in some areas. Even big and slow companies have some project or supplier that uses a software with a NoSQL service in it. You just rarely see those bundled redis or elasticsearch servers that are crucial for some app to do its session management and search engine. The most disturbing part of the NoSQL trend is that people are treating it like a battle between two competing systems. I have at least one major system under my own belt that uses relational SQL for backend data, ES for search engine and cassandra for TSDB. I need and trust all those services to work as one unit.
- SmellTheGlove 9y ago
  > The thing about the NoSQL trend is that it's still very valid in some areas. Even big and slow companies have some project or supplier that uses a software with a NoSQL service in it. This is a good point and I didn't do a good job of acknowledging it in my prior comment. There are use cases for NoSQL, but to me it's a tool for a narrower set of circumstances. My default is relational, unless I have a clear idea of why that isn't the right fit.
eximius 9y ago
Not familiar with teradata, but that looks like the equivalent of select a, b, c from table where somedate between somebegin and someend and someotherdate = (select max(someotherdate) from table group by a, b) which will work in all sqls. My point being, the advanced features make life nice, but are by no means necessary. (would be more efficient as a join, but not sure if joining on subtables is always supported...)

Clubber 9y ago

>So why not start with a relational database? I asked the same question. The biggest reason I've heard is RDBMS's don't horizontally scale well, meaning you can't easily have 50 replicated nodes across the globe and expect it to perform well, or setup easily, because it's fairly complicated with an RDBMS. There's things like Oracle's grid or SQL's high availability clusters, but they get complicated fast, particularly when you need a bunch of nodes. Obviously it was a solution for a very large amount of unstructured or semi-structured data that needed to be redundant across networks. With NoSQL, the key/values are a lot simpler to replicate apparantly. It's a Google/Twitter size problem that most industries wouldn't have, but since it's the new shiny thing, you know how that goes.

SmellTheGlove 9y ago
> It's a Google/Twitter size problem that most industries wouldn't have, but since it's the new shiny thing, you know how that goes. You nailed it on both points. Very few organizations are going to need that kind of scale, but many want to think that they will! Even when you get to horizontal scaling, you can simply replicate what needs to be replicated. Key-value is really just a subset of relational data, right? If so, I bet we can query and push only that data out across the nodes. It's not to say non-relational models don't have a place, but they're the exception as far as I'm concerned. I prefer to start with relational and only move off of that if I can justify it. As I said upthread, though, I work in insurance and you might imagine relational works well for us.
- Clubber 9y ago
  Also, the unstructured data is another thing. For example, lets say you need to store a bunch of contracts (as in your industry) in PDF/TIFF or whatever. The filesystem isn't a really good place to store it because you have a loose coupling between the index in the table and the file on the filesystem. That can get messy and unless security is tight, some developer or admin could muck something up on the filesystem. This is particularly dangerous when you need to keep contracts over a period of time (say 7 years). If you have a really large amount of files, you'll either need a huge array, or figure out a way to distribute them across several NAS's or whatever, then that gets complicated. The RDBMS is a little better at storing this type of data, but it has it's own problems. Backups quickly become huge and a massive undertaking. Development databases now have a bunch of missing data (because who wants to replicate that down to lower levels), you still have the same problem when you have a large amount of data that you need to shard across NAS/arrays, etc. The NoSQL seems like a third option that may well be worth looking into. Again this is for unstructured data that you just need to reference and not necessarily query against. (like stored contracts).
  - sk5t 9y ago
    > That can get messy and unless security is tight, some developer or admin could muck something up on the filesystem This isn't a great reason not to use a filesystem--it's a reason not to let staff have at the filesystem like cowboys, and not to avoid replicating and backing up the keys and values on the filesystem as if they are really important. That said, there isn't really a solid replacement for a database transaction involving both fields + blob, but most situations are okay with, say, stowing a blob in S3, and later making a half-hearted attempt to delete it if the associated database transaction fails.

tomnipotent 9y ago

>> Pretty sure that SELECT, FROM, WHERE will work just about anywhere Cassandra & CQL is a prime example. CQL become the de facto interface for getting data out of Cassandra, even without the benefits of JOIN's & sets.

pixelmonkey 9y ago
CQL is not SQL. CQL does not even have SQL SELECT. There is no support for arbitrary WHERE clauses, for example. You need to either slice an ordered column, or select a row by primary key -- and nothing else! They have an ALLOW FILTERING option which lets you "do it anyway", but this reduces down to paging the whole dataset without the WHERE applied, and then doing client-side filtering. I always felt that CQL did more harm than good for Cassandra. It's not like Redshift -- which implemented a full mostly-Postgres compatibility layer. Cassandra's CQL "looks like" SQL in the same way JavaScript "looks like" Java. Similarity by superficial appearance only; under the hood, totally different.
- ma2rten 9y ago
  It's not just a subset, but there are also incompatibilities like limit limits the number of rows scanned not the number of rows returned.
- stubish 9y ago
  I think the opposite, and that CQL is the only reason people still adopt Cassandra today. Remember Thrift, which started by using terms like columns and rows for completely different concepts and went downhill from there? I felt it was a very interesting choice to have CQL only capable of expressing queries that Cassandra could perform quickly. It forced people to adjust their data models immediately, rather than defer the problem until the performance issues got too deep to ignore. Its a different mindset to relational databases, for different sorts of problems. If only they had used the same sort of restraint on other features attempting to make Cassandra do things it is unsuitable for, such as 'transactions' and counters, which are just an embarrassment of problems.
- tomnipotent 9y ago

jjirsa 9y ago

The reason people don’t start with a relational DB has nothing to do with the data model or query language - people choose databases like Cassandra because scaling to 500 Postgres or MySQL instances holding a combined petabyte of data is horrific, but it’s dirt simple in things like Cassandra The query language is a side effect of the underlying storage engine - you don’t choose it because you want a key value store, you choose it because you want horizontal scalability and cross wan HA

6nf 9y ago
You make it sound like many people are using petabyte scale databases. This is not even close to true. ESPECIALLY when you're just starting out. Most new projects will start with almost no data at all and the vast majority of projects will never ever get to the petabyte scales.
- jjirsa 9y ago
  Many people ARE using petabyte scale databases. They don't start out that way, but they will be if they're successful, and if they're not they probably shouldn't be running their own databases anyway (go RDS it up or something).
  - deleted 9y ago
    [deleted]
  - eberkund 9y ago
    Unless the amount of data you are collecting is increasing at a vastly unsustainable rate, you will be making megabucks and be able to afford a team of engineers to undertake a major redesign for version 2. But picking technologies based on scale at the onset is going to make getting to that point all the more unlikely.
  - happymellon 9y ago
    What? No they aren't, I work with satellite imagery processing which is quite large in it raw data format, and after a decade we are not dealing with petabytes of active data, hundreds of gigs for a full earth coverage. Before that I have held positions in finance, dealing with realtime transaction processing. We did not work in petabytes. If you are working in petabytes you are storing crap in your production database, and 99% of that data is wasted.

ojosilva 9y ago

>I think the bigger issue is that you can use an RDBMS to do NoSQL-like things, such as key-value stores, with the flexibility to structure your data if you need that later. So why not start with a relational database? I've tried that in the past and failed miserably. 1) values in a key-value table will endup needing to hold nested data structures, such as a JS object/hash. Ie. mykey={...} 2) turning values to JSON (or some other serialization) makes it impossible to concurrently update or search subkeys. 3) so you convert your complex (sets, hashes) key-value data into several rows that hold one subkey per row, so now you have updatable rows but still no indexable solution and a serious performance problem. 4) so you create a multi column type-segregated table (one column for each DB type) for indexing your key-values and making them searcheable. That also requires a metadata table to go with it so that you know in which column your key or subkey is indexed. 5) say you successfully implemented your key-value store with your RDBMS. You still don't have usable joins (you don't have relational data) or a real migration path out of your key-values. Trust me, don't just put keys and values in a relational DB. Start with the right tool for the job, either make your schema relational from the beginning or use a proper KV or document store.

kazagistar 9y ago
1. Postgres has a json and jsonb datatype specifically to be your values. 2. It has built in query and modification tools that can manage the nested objects and array and whatnot. 3. It let's you build indexes to help with all this, including reverse indexes for asking something like "is this string in the array inside this object". 4. You don't have to specify your schema in sone horrid way, you can just throw what you want at it and specify later, and get performance numbers that are competitive and even exceed some popular NoSql stores. 5. You can mix and match freely with all the niceties of a proper SQL database with transactions, joins, constraints, etc, as you see fit to evolve. So yeah, don't just throw your keys and values into some text table somewhere; but use instead one of the best proper kv stores, that just happens to also be one of the best sql databases, and figure out how you want your schema you need it.
- Mikushi 9y ago
  Same can be said of MySQL. Not comparing to Postgres, but I've been amazed at how many people do not know that either of these as a powerful JSON type.

INTPenis 9y ago

The thing about the NoSQL trend is that it's still very valid in some areas. Even big and slow companies have some project or supplier that uses a software with a NoSQL service in it. You just rarely see those bundled redis or elasticsearch servers that are crucial for some app to do its session management and search engine. The most disturbing part of the NoSQL trend is that people are treating it like a battle between two competing systems. I have at least one major system under my own belt that uses relational SQL for backend data, ES for search engine and cassandra for TSDB. I need and trust all those services to work as one unit.

SmellTheGlove 9y ago
> The thing about the NoSQL trend is that it's still very valid in some areas. Even big and slow companies have some project or supplier that uses a software with a NoSQL service in it. This is a good point and I didn't do a good job of acknowledging it in my prior comment. There are use cases for NoSQL, but to me it's a tool for a narrower set of circumstances. My default is relational, unless I have a clear idea of why that isn't the right fit.

eximius 9y ago

Not familiar with teradata, but that looks like the equivalent of select a, b, c from table where somedate between somebegin and someend and someotherdate = (select max(someotherdate) from table group by a, b) which will work in all sqls. My point being, the advanced features make life nice, but are by no means necessary. (would be more efficient as a join, but not sure if joining on subtables is always supported...)