12 ms·

SQL is one the most amazing concepts I've ever experienced. It's nearly 5 decades old and there is no sign of a replacement. We've created countless other techn

SQL is one the most amazing concepts I've ever experienced. It's nearly 5 decades old and there is no sign of a replacement. We've created countless other technologies to store and process data, and we always seem to try to re-create SQL in those technologies (e.g. Hive, Presto, KSQL, etc). I run a early stage company that builds analytics infrastructure for companies. We are betting very heavily on SQL, and Craigs post rings true now more than ever. Increasingly, more SQL is written in companies by analysts and data scientists than typical software engineers. The advent of the MMP data warehouse (redshift, bigquery, snowflake, etc) has given companies with even the most limited budget the ability to warehouse and query an enormous amount of data just using SQL. SQL is more powerful and valuable today than it ever has been. When you look into a typical organization, most software engineers aren't very good at SQL. Why should they be? Most complex queries are analytics queries. ORMs can handle a majority of the basic functions application code needs to handle. Perhaps going against Craig's point is the simple fact that we've abstracted SQL away from a lot of engineers across the backend and certainly frontend and mobile. You can be a great developer and not know a lot about SQL. On the other end of the spectrum are the influx of "data engineers" with basic to intermediate knowledge of HDFS, streaming data or various other NoSQL technologies. They often know less about raw SQL than even junior engineers because SQL is below their high-power big data tools. But if you really understand SQL, and it seems few people truly today, you command an immense amount of power. Probably more than ever.

rjbwork 8y ago
(please excuse my typing. i have one hand to use ATM) yup. we bet super hard on sql too. Its fantastic if you know what you're doing. We ca import arbitrary data and expose it as normal tables to our customers for analysis/transform/export along with having a silod access controlled place for them to see just their data. sql is a great technology, and is extremely flexible. I love it. If you're looking to learn it and fundamentals, check out Jennifer Widom's course from Harvard. I can safely credit her w/ my career.
- Beskz 8y ago
  Do you mean this one from Stanford? https://lagunita.stanford.edu/courses/Engineering/db/2014_1/about https://lagunita.stanford.edu/courses/Engineering/db/2014_1/...
  - jessaustin 8y ago
    Presumably GP took this as a MOOC... some people are pretty blitzed in college but it would be hard to mistake California for Massachusetts!
sadness2 8y ago
> we always seem to try to re-create SQL This tells us that people think SQL is good, but it's unfortunate that this is attempted, because the good thing about SQL is how well it corresponds to its data structures. Attempting to imitate SQL's form, rather than its principal of correspondence, has been counter-productive and prevented the reproduction of its merits. Case in point: Cypher.
dominotw 8y ago
> It's nearly 5 decades old 5 decade old sql is nothing like modern sql with tons of proprietary extensions, partition, windows, collations, typecast, json and god knows what else. Your examples " Hive, Presto, KSQL, etc" are a proof of this, they are so vastly different from each other you cannot simply learn "sql" and expect to use those tools in any serious manner. This is precisely the proof of opposite that sql has not stood the test of time. How is this even close to sql of 5 decades old, CREATE STREAM pageviews (viewtime BIGINT, user_id VARCHAR, page_id VARCHAR) WITH (VALUE_FORMAT = 'JSON', KAFKA_TOPIC = 'my-pageviews-topic'); or this CREATE STREAM pageviews (viewtime BIGINT, user_id VARCHAR, page_id VARCHAR, `Properties` VARCHAR) WITH (VALUE_FORMAT = 'JSON', KAFKA_TOPIC = 'my-pageviews-topic'); anything that looks vaguely like a weirdly formed english sentence is sql?
- kamaal 8y ago
  It would be insane to expect somebody arrive at a perfect feature full working version of anything in the first try. That's true for any piece of software. SQL is not done yet, the fact that you can put in neat little features still into it is a proof to how well it was designed. Heck we still have Lisp evolving today. A lot of things got done right in the early days.
  - deleted 8y ago
    [deleted]
- chillacy 8y ago
  I understood when OP said the "concept of SQL" that they referred to a pretty broad idea of querying tabular data in rows and columns with some structured language.
  - dominotw 8y ago
    I find it hard to accept that ppl are talking about 'querying tabular data in rows and columns with some structured language' when they talk about sql. They are surely talking about the specific syntax. https://en.wikipedia.org/wiki/SQL https://en.wikipedia.org/wiki/SQL
nostrademons 8y ago
"we always seem to try to re-create SQL in those languages (e.g. Hive, Presto, KSQL, etc)." This is largely because of the number of non-programmers who know SQL. Add an SQL layer on top of your non-SQL database and you instantly open up a wide variety of reporting & analytics functionality to PMs, data scientists, business analysts, finance people, librarians (seriously! I have a couple librarian-as-in-dead-trees friends who know SQL), scientists, etc. In some ways this is too bad because SQL sucks as a language for many reasons (very clumsily compositional; verbose; duplicates math expressions & string manipulation of the host language, poorly; poor support for trees & graphs; easy to write insecure code; doesn't express the full relational algebra), but if it didn't suck in those ways it probably wouldn't have proven learnable by all those other professions that make it so popular.
- wvenable 8y ago
  I don't think it's non-programmers that keep SQL around; it's that there hasn't been a replacement for SQL that is better enough to replace it. I think C suffers from the same problem.
  - simplify 8y ago
    I find cypher to be a superior language to SQL. It expresses relationships more succinctly and intuitively, and has better support for aggregations.
    - ilikehurdles 8y ago
      I also find datalog superior.
- busterarm 8y ago
  So it's protectionism? It's way easier to teach someone to code who is already really good at manipulating/modeling data than the other way around, IMO.
- slap_shot 8y ago
  I don't disagree. I just don't think we've figured out the next step. Other paradigms (MapReduce and graph databases are perfect examples) have introduced very interesting and clever ways to querying data, but nothing has replaced SQL. If anything, it seems like it will be additive on top of what SQL has already done. I know a lot about data, but I can't solve that problem. To anyone out there, much smarter than me: this is a real problem. If you solve it, you'll cement yourself the history of computer science. I have no doubt it will get done, though.
manish_gill 8y ago
> On the other end of the spectrum are the influx of "data engineers" with basic to intermediate knowledge of HDFS, streaming data or various other NoSQL technologies. They often know less about raw SQL than even junior engineers because SQL is below their high-power big data tools. I always ask a basic SQL question (involving sorting/grouping and one simple join) in my interviews for backend and/or data. IMO if you are calling yourself a Data Engineer and do not know SQL, then you haven't really worked with data, you've mostly just worked on data infrastructure.
- slap_shot 8y ago
  It's mandatory. It's like saying you understand calculus but not being able to do algebra. I interview a lot of data engineers/analytics engineers and I always start with SQL. If you can't grasp SQL, you're in for a very bad time.
api 8y ago
SQL is so long lasting because its math, sort of. It's a language for expressing data relationships in a very systematic logical and set theoretic way, not a random ad hoc hack. Its syntax is a bit old fashioned and I do think efforts to make it more native to programming environments rather than a bolt on might be fruitful, but its concepts are timeless.
EGreg 8y ago
Can I ask a simple question about efficiency? It seems to me that graph databases are far more efficient than relational ones for most tasks. That’s because all lookups are O(1) instead of O(log N). That adds up. Also, copying a subgraph is far easier, and so is joining. Think about it, when you shard you are essentially approaching graph databases because your hash or range by which you find your shard is basically the “pointer” half of the way there. And then you do a bunch of O(log N) lookups. Also, data locality and caching would be better in graph databases. Especially when you have stuff distributed around the world, you aren’t going to want to do relational index lookups. You shard — in the limit you have a graph database. Why not just use one from the start? So it seems to me that something like Neo4J and Cypher would have taken off and beat SQL. Why is it hardly heard of, and NoSQL is instead relegated to documents without schemas?
- rtpg 8y ago
  In practice enterprise applications do a lot of relational operations. Graph DBs might be valuable for a lot of problems, and it does feel like something like Neo4J would make a lot of sense for stuff like social networks, but for business records stuff isn't really that spread out
  - slifin 8y ago
    Although if you want versioning to the attribute level, you'd want something that spread out, see Datomic
- indigo945 8y ago
  A hash index is O(1) no matter what language it's called from. The reason most people don't use hash indices very often is that they do not allow you to do a lot of useful things that you often end up wanting to do, such as retrieving all values within a given range.
  - EGreg 8y ago
    I still don’t think it’s O(1) in the limit. Why not just store pointers to the data, or one indirection if the data can be moved?
jwdunne 8y ago
Can you recommend good resources for engineers to up their skills with SQL that'll provide the understanding you discuss?
- Pamar 8y ago
  I am not the OP but I would suggest anyone who wants to get better at SQL to read this book: https://www.amazon.com/Art-SQL-Stephane-Faroult/dp/0596008945 https://www.amazon.com/Art-SQL-Stephane-Faroult/dp/059600894...
  - deleted 8y ago
    [deleted]
raverbashing 8y ago
> SQL is one the most amazing concepts I've ever experienced. ...and we always seem to try to re-create SQL in those technologies (e.g. Hive, Presto, KSQL, etc). It's not SQL that's the concept. The concept there is set theory/intersection/union and predicates. That's why you think you are "recreating" those, because they can be mapped using the same concept SQL is only one way of expressing that concept.
- michaelt 8y ago
  When people say "SQL" they often refer to a bunch of concepts, some better than others, all bundled together. SQL gets you tables and joins, sure. But it also gets you queries that (some) non-programmers can write, outputs that are always a table, a variety of GUI programs to compose queries and display the results, analytic functions to do things like find percentiles, and tools to connect from MS Excel. And it often means you get transactions, ACID compliance, compound indexes, explicit table schemas, multiple join types, arbitrary-precision decimal data types, constraints, views, and a statistics-based query optimiser. Of course it also gets you weird null handling, stored procedures, and triggers.
sonnyblarney 8y ago
Just because it is resilient, does not mean it is good. 'Being great' usually helps a product gain traction. When SQL came about, it was very useful because hey 'I can query data!'. But it's usually other reasons that drive incumbency.
clanrebornsx 8y ago
I disagree you can't use SQL for Google search. No engineer has ever made this possible. We've CommonCrawl data but you can't run SQL queries on that data this makes SQL useless. When smart people have figured out how to do that, come back claiming SQL is important.
- mikorym 8y ago
  Lisbeth Salander sure can.
radicalbyte 8y ago
Certain subsets of the developer community are usually excellent with SQL. It's especially evident with people who have been working with C# for > 10 years because Microsoft have always heavily pushed SQL Server with their developers. There are plenty of use-cases for SQL with developers: especially in batch processes such as invoicing. A well crafted SQL query can execute exponentially faster than iterative code takes a lot less time to implement.
collyw 8y ago
I blame the NoSQL movement for a lot of the lack of understanding in recent years. That was a step backwards.
- zzzcpan 8y ago
  People behind NoSQL movement understand SQL better, than any people glorifying SQL here. In fact, I think this is a testament of how poorly most people actually understand SQL, they can't even see basic on the surface problems with it.
  - GVIrish 8y ago
    I think there are people who went with NoSQL because it fit their specific needs better than a regular RDBMS, and people who went NoSQL because they found it easier to work with than a RDBMS (at first). The latter group often didn't want to learn SQL and went with NoSQL's as a shortcut, not because they had an intimate understanding of the tradeoffs between the approaches and decided NoSQL was the better option.
    - collyw 8y ago
      There was the third group of resume driven developers who bought into the MongoDB hype around 6 or 7 years ago, which probably overlapped with the second group a bit.
  - collyw 8y ago
    All I heard from NoSQL proponents was that "relational databases don't scale". These same people were usually dealing with data sets that would fit easily onto a single server.
    - zzzcpan 8y ago
      No, I'm pretty sure you only heard it from your fellow RDBMS friends. I have never argued for scale myself, but plenty of times tried to debunk this exact stereotype.
ASalazarMX 8y ago
SQLite is a testament to the power of SQL, it's a little wonder itself, and it's likely the most installed database globally.

rjbwork 8y ago

(please excuse my typing. i have one hand to use ATM) yup. we bet super hard on sql too. Its fantastic if you know what you're doing. We ca import arbitrary data and expose it as normal tables to our customers for analysis/transform/export along with having a silod access controlled place for them to see just their data. sql is a great technology, and is extremely flexible. I love it. If you're looking to learn it and fundamentals, check out Jennifer Widom's course from Harvard. I can safely credit her w/ my career.

Beskz 8y ago
Do you mean this one from Stanford? https://lagunita.stanford.edu/courses/Engineering/db/2014_1/about https://lagunita.stanford.edu/courses/Engineering/db/2014_1/...
- jessaustin 8y ago
  Presumably GP took this as a MOOC... some people are pretty blitzed in college but it would be hard to mistake California for Massachusetts!

sadness2 8y ago

> we always seem to try to re-create SQL This tells us that people think SQL is good, but it's unfortunate that this is attempted, because the good thing about SQL is how well it corresponds to its data structures. Attempting to imitate SQL's form, rather than its principal of correspondence, has been counter-productive and prevented the reproduction of its merits. Case in point: Cypher.

dominotw 8y ago

> It's nearly 5 decades old 5 decade old sql is nothing like modern sql with tons of proprietary extensions, partition, windows, collations, typecast, json and god knows what else. Your examples " Hive, Presto, KSQL, etc" are a proof of this, they are so vastly different from each other you cannot simply learn "sql" and expect to use those tools in any serious manner. This is precisely the proof of opposite that sql has not stood the test of time. How is this even close to sql of 5 decades old, CREATE STREAM pageviews (viewtime BIGINT, user_id VARCHAR, page_id VARCHAR) WITH (VALUE_FORMAT = 'JSON', KAFKA_TOPIC = 'my-pageviews-topic'); or this CREATE STREAM pageviews (viewtime BIGINT, user_id VARCHAR, page_id VARCHAR, `Properties` VARCHAR) WITH (VALUE_FORMAT = 'JSON', KAFKA_TOPIC = 'my-pageviews-topic'); anything that looks vaguely like a weirdly formed english sentence is sql?

kamaal 8y ago
It would be insane to expect somebody arrive at a perfect feature full working version of anything in the first try. That's true for any piece of software. SQL is not done yet, the fact that you can put in neat little features still into it is a proof to how well it was designed. Heck we still have Lisp evolving today. A lot of things got done right in the early days.
- deleted 8y ago
  [deleted]
chillacy 8y ago
I understood when OP said the "concept of SQL" that they referred to a pretty broad idea of querying tabular data in rows and columns with some structured language.
- dominotw 8y ago
  I find it hard to accept that ppl are talking about 'querying tabular data in rows and columns with some structured language' when they talk about sql. They are surely talking about the specific syntax. https://en.wikipedia.org/wiki/SQL https://en.wikipedia.org/wiki/SQL

nostrademons 8y ago

"we always seem to try to re-create SQL in those languages (e.g. Hive, Presto, KSQL, etc)." This is largely because of the number of non-programmers who know SQL. Add an SQL layer on top of your non-SQL database and you instantly open up a wide variety of reporting & analytics functionality to PMs, data scientists, business analysts, finance people, librarians (seriously! I have a couple librarian-as-in-dead-trees friends who know SQL), scientists, etc. In some ways this is too bad because SQL sucks as a language for many reasons (very clumsily compositional; verbose; duplicates math expressions & string manipulation of the host language, poorly; poor support for trees & graphs; easy to write insecure code; doesn't express the full relational algebra), but if it didn't suck in those ways it probably wouldn't have proven learnable by all those other professions that make it so popular.

wvenable 8y ago
I don't think it's non-programmers that keep SQL around; it's that there hasn't been a replacement for SQL that is better enough to replace it. I think C suffers from the same problem.
- simplify 8y ago
  I find cypher to be a superior language to SQL. It expresses relationships more succinctly and intuitively, and has better support for aggregations.
  - ilikehurdles 8y ago
    I also find datalog superior.
busterarm 8y ago
So it's protectionism? It's way easier to teach someone to code who is already really good at manipulating/modeling data than the other way around, IMO.
slap_shot 8y ago
I don't disagree. I just don't think we've figured out the next step. Other paradigms (MapReduce and graph databases are perfect examples) have introduced very interesting and clever ways to querying data, but nothing has replaced SQL. If anything, it seems like it will be additive on top of what SQL has already done. I know a lot about data, but I can't solve that problem. To anyone out there, much smarter than me: this is a real problem. If you solve it, you'll cement yourself the history of computer science. I have no doubt it will get done, though.

manish_gill 8y ago

> On the other end of the spectrum are the influx of "data engineers" with basic to intermediate knowledge of HDFS, streaming data or various other NoSQL technologies. They often know less about raw SQL than even junior engineers because SQL is below their high-power big data tools. I always ask a basic SQL question (involving sorting/grouping and one simple join) in my interviews for backend and/or data. IMO if you are calling yourself a Data Engineer and do not know SQL, then you haven't really worked with data, you've mostly just worked on data infrastructure.

slap_shot 8y ago
It's mandatory. It's like saying you understand calculus but not being able to do algebra. I interview a lot of data engineers/analytics engineers and I always start with SQL. If you can't grasp SQL, you're in for a very bad time.

api 8y ago

SQL is so long lasting because its math, sort of. It's a language for expressing data relationships in a very systematic logical and set theoretic way, not a random ad hoc hack. Its syntax is a bit old fashioned and I do think efforts to make it more native to programming environments rather than a bolt on might be fruitful, but its concepts are timeless.

EGreg 8y ago

Can I ask a simple question about efficiency? It seems to me that graph databases are far more efficient than relational ones for most tasks. That’s because all lookups are O(1) instead of O(log N). That adds up. Also, copying a subgraph is far easier, and so is joining. Think about it, when you shard you are essentially approaching graph databases because your hash or range by which you find your shard is basically the “pointer” half of the way there. And then you do a bunch of O(log N) lookups. Also, data locality and caching would be better in graph databases. Especially when you have stuff distributed around the world, you aren’t going to want to do relational index lookups. You shard — in the limit you have a graph database. Why not just use one from the start? So it seems to me that something like Neo4J and Cypher would have taken off and beat SQL. Why is it hardly heard of, and NoSQL is instead relegated to documents without schemas?

rtpg 8y ago
In practice enterprise applications do a lot of relational operations. Graph DBs might be valuable for a lot of problems, and it does feel like something like Neo4J would make a lot of sense for stuff like social networks, but for business records stuff isn't really that spread out
- slifin 8y ago
  Although if you want versioning to the attribute level, you'd want something that spread out, see Datomic
indigo945 8y ago
A hash index is O(1) no matter what language it's called from. The reason most people don't use hash indices very often is that they do not allow you to do a lot of useful things that you often end up wanting to do, such as retrieving all values within a given range.
- EGreg 8y ago
  I still don’t think it’s O(1) in the limit. Why not just store pointers to the data, or one indirection if the data can be moved?

jwdunne 8y ago

Can you recommend good resources for engineers to up their skills with SQL that'll provide the understanding you discuss?

Pamar 8y ago
I am not the OP but I would suggest anyone who wants to get better at SQL to read this book: https://www.amazon.com/Art-SQL-Stephane-Faroult/dp/0596008945 https://www.amazon.com/Art-SQL-Stephane-Faroult/dp/059600894...
- deleted 8y ago
  [deleted]

raverbashing 8y ago

> SQL is one the most amazing concepts I've ever experienced. ...and we always seem to try to re-create SQL in those technologies (e.g. Hive, Presto, KSQL, etc). It's not SQL that's the concept. The concept there is set theory/intersection/union and predicates. That's why you think you are "recreating" those, because they can be mapped using the same concept SQL is only one way of expressing that concept.

michaelt 8y ago
When people say "SQL" they often refer to a bunch of concepts, some better than others, all bundled together. SQL gets you tables and joins, sure. But it also gets you queries that (some) non-programmers can write, outputs that are always a table, a variety of GUI programs to compose queries and display the results, analytic functions to do things like find percentiles, and tools to connect from MS Excel. And it often means you get transactions, ACID compliance, compound indexes, explicit table schemas, multiple join types, arbitrary-precision decimal data types, constraints, views, and a statistics-based query optimiser. Of course it also gets you weird null handling, stored procedures, and triggers.

sonnyblarney 8y ago

Just because it is resilient, does not mean it is good. 'Being great' usually helps a product gain traction. When SQL came about, it was very useful because hey 'I can query data!'. But it's usually other reasons that drive incumbency.

clanrebornsx 8y ago

I disagree you can't use SQL for Google search. No engineer has ever made this possible. We've CommonCrawl data but you can't run SQL queries on that data this makes SQL useless. When smart people have figured out how to do that, come back claiming SQL is important.

mikorym 8y ago
Lisbeth Salander sure can.

radicalbyte 8y ago

Certain subsets of the developer community are usually excellent with SQL. It's especially evident with people who have been working with C# for > 10 years because Microsoft have always heavily pushed SQL Server with their developers. There are plenty of use-cases for SQL with developers: especially in batch processes such as invoicing. A well crafted SQL query can execute exponentially faster than iterative code takes a lot less time to implement.

collyw 8y ago

I blame the NoSQL movement for a lot of the lack of understanding in recent years. That was a step backwards.

zzzcpan 8y ago
People behind NoSQL movement understand SQL better, than any people glorifying SQL here. In fact, I think this is a testament of how poorly most people actually understand SQL, they can't even see basic on the surface problems with it.
- GVIrish 8y ago
  I think there are people who went with NoSQL because it fit their specific needs better than a regular RDBMS, and people who went NoSQL because they found it easier to work with than a RDBMS (at first). The latter group often didn't want to learn SQL and went with NoSQL's as a shortcut, not because they had an intimate understanding of the tradeoffs between the approaches and decided NoSQL was the better option.
  - collyw 8y ago
    There was the third group of resume driven developers who bought into the MongoDB hype around 6 or 7 years ago, which probably overlapped with the second group a bit.
- collyw 8y ago
  All I heard from NoSQL proponents was that "relational databases don't scale". These same people were usually dealing with data sets that would fit easily onto a single server.
  - zzzcpan 8y ago
    No, I'm pretty sure you only heard it from your fellow RDBMS friends. I have never argued for scale myself, but plenty of times tried to debunk this exact stereotype.

ASalazarMX 8y ago

SQLite is a testament to the power of SQL, it's a little wonder itself, and it's likely the most installed database globally.