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.
"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.
> 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.
(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.
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.
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.
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.
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?
> 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
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.
My first job out of university was on an analytics team at a consulting firm (big enough that you know them) that used MS SQL Server for absolutely everything.
Data cleaning? SQL.
Feature engineering? SQL.
Pipelines of stored procedures, stored in other stored procedures. Some of these procedures were so convoluted that they outputted tables with over 700 features, and had queries that were hundreds of lines long.
Every input, stored procedure, and output timestamped, so a change to one script involved changing every procedure downstream of it. My cries to use git were unheeded (would have required upskilling everyone on the team).
It was probably the worst year of my life. By the end of it I built a framework in T-SQL that would generate T-SQL scripts. In the final week of a project (which had been consistent 60-70 hour weeks), the partner on the project came in, saw the procedures written in my framework and demanded that they all be converted back into raw SQL. I moved teams a few weeks later.
The only good bit looking at it, is that now I'm REALLY good with SQL. It's incredibly powerful stuff, and more devs should work on it.
I spent a year in a role where 50% of my duties was writing sql reports. These reports where usually between 500 and 1000 lines of sql a pop. Sometimes the runtime of the report was measured in hours, so learning efficient sql was important. The company had a lot of people that had been writing sql for awhile, and there were lots of cool code snippets floating around. I learned a lot in that year.
I've moved to writing backend code. I'm surprised most of my peers cannot write anything more complicated than a join. Most people are perfectly happy to let the orm do all the work, and never care to dig into the data directly. Every once in a while my sql skills save the day and several people in other departments contact me directly when they need excel files of data in our database we don't have UIs to pull yet.
It's often useful to treat SQL as an extraction/filtering language, and then use R or Pandas as a computation/reporting language.
I think of it as separating I/O and computation. SQL does enough filtering to cut the data down to a reasonable size, and maybe some preliminary logic. And then you compute on that smaller data set in R or Pandas -- iterating in SECONDS instead of hours. The code will likely be shorter as well, so it's a win-win (see examples in my blog post).
I can't think of many situations where hours of runtime is "reasonable" for an SQL query. In 2 hours you could probably do a linear scan over every table in most production databases 10-100 times.
For example, if your database is 10 GB, you could cat all of its files in less than 5 minutes (probably much less on a modern SSD). In 2 hours, you can do a five minute operation 24 times. I can't think of many reports that should take longer than 24 full passes over all the data in the database (i.e. pretending that you're not bothering to use indices in the most basic way). If it takes longer than that, the joins should be expressible with something orders of magnitude more efficient.
I've mainly worked with big data frameworks, but I think that almost any SQL database (sqlite, MySQL, Postgres) should be able to do a scan of a single table with some trivial predicates within 10x the speed of 'cat' (should be within 2x really). They can probably do better on realistic workloads because of caching.
I had a similar role where I was writing boring LOB apps in a very gross language, but since we were using an SQL backend for all the data, I instead challenged myself to using bare templates in the actual programming language and writing all the extraction, transforms and logic into SQL selects and (when unavoidable) programmatic bits.
I also learned a crapload about obscure SQL since I would go to extreme lengths to achieve this. There was a lot of meta-SQL programming, where I would use SQL to generate more SQL and execute that within my statement, sometimes multiple layers deep. It was beautiful in its own way, expanding out in intricate patterns.
> Most people are perfectly happy to let the orm do all the work, and never care to dig into the data directly.
ORM all-too-often defines data structures from code.
Linus Torvalds wrote,
> I will, in fact, claim that the difference between a bad programmer and a good one is whether he considers his code or his data structures more important. Bad programmers worry about the code. Good programmers worry about data structures and their relationships.
SQL is a mind bender for me. I do a lot of work on Data, use python and pandas to do a lot of data magic, but the problem with me is my mind is too procedural in thinking.
- Step 1
- Step 2
- Loop through results in Step 2
- Curate and finish output.
I try very hard to transform the above steps into an SQL statement spanning multiple tables, but always fail and I usually fallback to python for manually extracting and processing the data.
Does anyone else face this problem?
Any suggested guides / books to make me think more SQL'ley ?
I took a course in prolog, really seemed to help get my mind in the right place.
Basically, instead of thinking in terms of how to get to the result, think instead of the result and figure out how to get there. For example, let's say I need to get a mapping of students to their classes. One way would be to get the students, then loop through that to get classes for each one. Another way to do it would be to ask the database to combine those sets of data for us instead and return the result (join students and class tables on the student ID).
Basically, find the data you want from each table, tell the database about the relationship between them (join on <field>), and set up your constraints. I guess you could think about the query as a function, and you're just writing the unit tests for it (when I call X with parameters Y, I expect Z).
(1) Think of a database as a "big pile of stuff in a room". Some of it's ordered in a sane way, some of it's not. There is "a person at the door" to the room preventing you from entering - this is the Database Engine, not to be confused with the Database itself. You need to get things out of the room, and you need to put things in the room. You're not allowed to enter. If you want something out of the room, you must ask for it (query) by specifying what the thing looks like and which part of the pile you think it's in. If you want to put something in the room, you must provide explicit instructions for where it must go. When you write SQL, you are either asking a question of or giving instructions to the database engine. The DB Engine is distinct from the Database (the big pile of stuff...which you never have direct access to).
(2) As others have pointed out, SQL is very much the mathematics of Set Theory put into a programming language. Stanford has a free class on Databases. I took it a few years ago (it may have changed), and most of it had no SQL at all. It was fairly very straight forward - things you can do with pencil and paper. It's free, so no pressure. Go sign up and do some of the homework assignments. Do them until you get them right (you can re-take wrongly answered problems immediately). It'll help break your procedural mindset. https://online.stanford.edu/courses/soe-ydatabases-databases
I've thought about the same problem and think I have an explanation. SQL inherently avoids operations that cannot scale. This means that while the same operation can be written much more simpler as code if you're doing with pandas or spark, trying to do it with SQL would be less forgiving if your underlying data schema is not optimal or if your logic isn't scalable. But more often than not, the SQL query will run faster and more reliably given similar amounts of compute power to the two engines.
Of course you can always screw up and write suboptimal SQL that will take forever to finish, but it's just harder to do unless you're really trying to be dumb about it.
So I do think there's still a lot of merit in trying to learn and use SQL more.
Try some functional programming. It will liberate your brain from thinking operationally. You will soon no longer think in terms of steps, loops or anything like that. You will get used to thinking about data transformations, which is what SQL is good at.
I work a lot with data as well primarily I use SAS, R and SQL. I'm also very procedurally oriented (as in I prefer breaking task down into discrete chunks), R's functional way of chaining operations with pipe operator feels so alien to me.
I find myself having the opposite problem I probably use (abuse?) SQL more than should.
My world (industrial plant) is very DB/historian heavy everything speaks SQL it's pretty much the common tongue connecting everything. I think this is slowly changing some PLC/historians now offer a webservice which returns JSON objects via an ajax query - personally I vastly prefer SQL to ajax.
I'd estimate for a typical problem I'm working with maybe 90% is done in SQL vs 10% in R/SAS code.
When I need Regression, Principal Component Analysis, Time Series manipulation, Plots etc I have to break into dedicated language.
For most other things - extract, merge, filtering, high level aggregation (Count sum etc) type of operations using SQL feels more natural and expressive to me.
I used to think this too, and wondered what magical SQL perspective my brain was missing. I'd see these elegant but seemingly impenetrable queries and wonder why my brain wasn't in the "SQL dimension" yet.
But over time, and very heavy SQL reporting, I realized the procedural mindset still applies, it's just not expressed as such.
You need to think through the steps of how you want to retrieve, transform, aggregate etc. the data and build up the query to accomplish those steps. The resulting query doesn't look procedural, but the thought process was. Of course you need to know the sometimes opaque constructs/tricks/syntax to express the steps, which again, look like magic at face value or just reading docs.
I think this is why people struggle with understanding SQL. The thought process is still procedural, but the query isn't. You need to translate the query back into procedural terms for your own understanding, which is a PITA.
I still struggle with thinking too "proceduraly" sometimes, but what really helps out with me is drawing / envisioning the sets of data as a series of circles and Venn diagrams.
It also helps to change the language you use in your inner monologue. Instead of thinking, "For each row in table A...", you should think, "For all the rows in table A that match on...".
SQL is the most powerful query language ever invented and widely implemented over relation databases IMO (qualified heavily for lurking RDF zealots). Every time you see someone start to invent their own query language, I almost always mark is as folly (similar to when people invent their own configuration languages). Prometheus and GraphQL stand out as recent examples.
DBs like Kafka who recognize this and instead offer SQL on top of their things take the right approach IMO KSQL.
SQL is nice on a surface level and helpful in practice.
Having a working intuition for relational databases is valuable on a deep level. I mean having a sense of how to organize the tables, what sizes are large and small, when to add what kind of index and what the size and speed limits are likely to be for a given data structure. That's extremely valuable.
BTW, we're preparing to move a postgres database that's a few TB from Heroku to AWS RDS. The catch is that we can't afford more than a few minutes of downtime. If this is in your wheelhouse, reach out! We'd like to talk.
I've been saying this for years, because I started doing web apps when we pretty much were sending raw SQL over to a database. Even after ORMs got bigger, I still stress knowing SQL even if you don't use it often, because it helps you understand the ORM.
And oddly enough, now I'm a data engineer. Everything I do, even when I'm not doing pure SQL, is influenced by years of experience in SQL. Either the languages of my big tools are still based on SQL in some fashion, or it simply helps to have an understanding of the ecosystem to figure out what's going on at scale.
Everything else has come and gone and come again, but SQL has help up pretty nicely. The only other skills that come close for me are working in languages that branched out of C (because the syntax isn't so different over time) and years of on again off again procedural languages (ColdFusion, vanilla Javascript, etc. leads to it being way easier to pick up Python).
I consider myself functional, but not proficient in SQL. Is there a reliable and easy way to assess one's skills in SQL? I feel that most of the new things I learn about SQL these days are database specific. I wonder whether I'm missing out on something, or do I already have the "core" SQL knowledge down, and everything else is special cases?
Hell Yes.
Although it takes some time to switch from Procedural or OOP to highly Declarative world of SQL, but man, its worth pursuing.
Only thing which really helped me build my confidence was solving more real world problems which involves 2NF and 3NF design, JOINs, Triggers, Indexing, Views & Materialized Views for denormalization, CTE and Recursive CTE's.
You may call me an extremist but from server side programming point of view with Postgres and FDW (Foreign Data Wrappers) which has ton of features other than SQL only thing i miss is HTTP server. :)
I’ve been a career analyst for about 12 years so I write SQL every day. I know it very well. I’ve developed a very strong love / hate relationship with it over the years.
On one hand, you write what you want to find in fairly common language and you almost always get back what you want if you do it correctly. In many ways, it’s like a precursor to Alexa but in written form. It’s super easy to pick up for non technical people.
On the other hand, it’s extremely difficult to code review, and on a very complicated piece of business logic, errors could mean the difference between hiring 10 more people or laying off 100. So almost always it’s just easier to re-write.
Imagine if engineers couldn’t understand each other’s work, and had to rewrite code every time someone leaves the team. It’s insane to me that this is standard practice.
SQL is for data systems what IP protocol is for networks: it is the neck of the hourglass. You can build plenty of various things on it (the top of the hourglass: applications, reporting frameworks and so on) using various underlying technologies (the bottom: storage engines etc.) but you can't remove the neck without breaking the hourglass.
This is why SQL language and IP protocol are two most valuable things in computer world.
In University, I needed one more CS elective course, and the ONLY class available that fit my schedule was SQL. I had no interest in SQL, wanted nothing to do with it, and only took the class under duress. Even to the point of admitting as much to the prof in a casual conversation (he was a good guy, easy to talk to).
Within two months into my first job out of school, I was assigned to implement a SQL parser as a modern new interface for an ancient proprietary database. Every job since, I've written tons of SQL queries. SQL rocks.
It's very interesting how many application developers shun SQL. I believe a lot of it is due to a pervading sentiment that SQL is unruly or inelegant. There are certainly quirks to the language, and it is a difficult transition to think in a declarative rather than imperative manner, but once you make the jump, it's an invaluable skill to have. Fortunately, I have the luxury of being able to manipulate our codebase from any level of the stack. What this means is that rather than jump through hoops on the API or frontend side to accomplish some task that would require reams of code, I can just get my output via a simple query, and it will perform faster in almost all cases.
From an analytics point of view, I can't imagine not using SQL. I've seen people pull reports from multiple websites, text files, etc., spend an entire day manipulating them in Excel, and still not get their data model working as expected, not to mention that it is very slow. A couple of queries with some temp tables and voila, magic happens. It really does make you look like a superhero when you can deliver more accurate results in a fraction of the time it originally took. I'm surprised there isn't more of a market for this skill, surely there's a lot of programmers from the 80's and 90's who have this skillset in abundance.
I'm a frontend web developer, but for a year I worked on a custom ETL system and wrote lots & lots of SQL. I still find myself using knowledge I learned back then and applying it today - for example, being able to review BI's queries is immensely useful. I'm also much less intimidated when I look at backend code, since a lot of it is interfacing with a database in some way.
I think SQL is one of those essential "secondary" skills for developers.
Reading the comments here is like someone with years of experience with jQuery saying how jQuery is simple and powerful and nothing will replace it. SQL is to relational databases what jQuery is to the DOM, only shittier (maybe like mootools) and refuses to die, probably because all these SQL experts aren't really good programmers. Reading some compare SQL to 70s style procedural code tells me they haven't moved on from expert beginner territory.
SQL has opened a lot of doors for me. It's a starting point, not an end though. Learning about dictionaries, lists, and other data structures has proven valuable and compliments SQL and tabular datasets very nicely. I got into those areas by working with SQL generators (ORM's) written in Python (airflow).
My thinking on SQL has evolved and lately I see it as a set definition tool. "Do action X on dataset Y." It's really useful for understanding data structures and data meaning too.
I've worked with more than a few SDE's who look down on SQL, but it's a really good tool when used properly, and it cuts across many technologies. Writing code to write SQL can be very powerful. And sometimes coded or scripted data wrangling without SQL is very useful too.
15 years ago SQL knowledge was not that widespread and it was easy to get tagged as a report writer. Today, a lot more business, product, finance, and accounting people are really strong with SQL, and rely heavily on exporting data to excel for further analysis. Knowing how to answer business questions, get insights out of the data, and define or categorize sets of data are all enhanced by SQL. Report writing is not as much of a thing anymore because people want to view the data in diverse ways.
The barrier to entry is low with SQL, but learning it well takes time and some mistakes to get efficient and precise with it. 15 years later I am still learning new uses for it. One example is JSON querying and transformation which is supported by hive, presto, and some other compute platforms. It's easy to mix and match JSON, arrays, and tabular data structures, in one or more tables, from the same SQL query.
I recently had a candidate that had very good Scala skills and very poor SQL skills. Despite Scala and spark being powerful, you can over complicate a solution in those languages that could otherwise be easily solved in SQL. That’s interesting to me because I see SQL as a fundamental tool that you build on top of and some people these days seems to skip it.
[+] [-] slap_shot|7 years ago|reply
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.
[+] [-] nostrademons|7 years ago|reply
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.
[+] [-] manish_gill|7 years ago|reply
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.
[+] [-] rjbwork|7 years ago|reply
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.
[+] [-] radicalbyte|7 years ago|reply
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.
[+] [-] sadness2|7 years ago|reply
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.
[+] [-] api|7 years ago|reply
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|7 years ago|reply
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?
[+] [-] raverbashing|7 years ago|reply
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.
[+] [-] collyw|7 years ago|reply
[+] [-] ASalazarMX|7 years ago|reply
[+] [-] jwdunne|7 years ago|reply
[+] [-] sonnyblarney|7 years ago|reply
'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|7 years ago|reply
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.
[+] [-] dominotw|7 years ago|reply
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?
[+] [-] JimmyAustin|7 years ago|reply
Data cleaning? SQL. Feature engineering? SQL.
Pipelines of stored procedures, stored in other stored procedures. Some of these procedures were so convoluted that they outputted tables with over 700 features, and had queries that were hundreds of lines long.
Every input, stored procedure, and output timestamped, so a change to one script involved changing every procedure downstream of it. My cries to use git were unheeded (would have required upskilling everyone on the team).
It was probably the worst year of my life. By the end of it I built a framework in T-SQL that would generate T-SQL scripts. In the final week of a project (which had been consistent 60-70 hour weeks), the partner on the project came in, saw the procedures written in my framework and demanded that they all be converted back into raw SQL. I moved teams a few weeks later.
The only good bit looking at it, is that now I'm REALLY good with SQL. It's incredibly powerful stuff, and more devs should work on it.
[+] [-] max76|7 years ago|reply
I've moved to writing backend code. I'm surprised most of my peers cannot write anything more complicated than a join. Most people are perfectly happy to let the orm do all the work, and never care to dig into the data directly. Every once in a while my sql skills save the day and several people in other departments contact me directly when they need excel files of data in our database we don't have UIs to pull yet.
[+] [-] chubot|7 years ago|reply
I wrote this post to introduce the idea:
What Is a Data Frame? (In Python, R, and SQL) https://www.oilshell.org/blog/2018/11/30.html
It's often useful to treat SQL as an extraction/filtering language, and then use R or Pandas as a computation/reporting language.
I think of it as separating I/O and computation. SQL does enough filtering to cut the data down to a reasonable size, and maybe some preliminary logic. And then you compute on that smaller data set in R or Pandas -- iterating in SECONDS instead of hours. The code will likely be shorter as well, so it's a win-win (see examples in my blog post).
I can't think of many situations where hours of runtime is "reasonable" for an SQL query. In 2 hours you could probably do a linear scan over every table in most production databases 10-100 times.
For example, if your database is 10 GB, you could cat all of its files in less than 5 minutes (probably much less on a modern SSD). In 2 hours, you can do a five minute operation 24 times. I can't think of many reports that should take longer than 24 full passes over all the data in the database (i.e. pretending that you're not bothering to use indices in the most basic way). If it takes longer than that, the joins should be expressible with something orders of magnitude more efficient.
I've mainly worked with big data frameworks, but I think that almost any SQL database (sqlite, MySQL, Postgres) should be able to do a scan of a single table with some trivial predicates within 10x the speed of 'cat' (should be within 2x really). They can probably do better on realistic workloads because of caching.
[+] [-] dwd|7 years ago|reply
Reducing a highly used query's execution time by several orders of magnitude can be quite gratifying.
[+] [-] DrPhish|7 years ago|reply
I also learned a crapload about obscure SQL since I would go to extreme lengths to achieve this. There was a lot of meta-SQL programming, where I would use SQL to generate more SQL and execute that within my statement, sometimes multiple layers deep. It was beautiful in its own way, expanding out in intricate patterns.
[+] [-] justinclift|7 years ago|reply
Asking from wanting to make use of such a place, and haven't seen anything like it. So, probably need to bootstrap one instead (etc).
[+] [-] saint_abroad|7 years ago|reply
ORM all-too-often defines data structures from code.
Linus Torvalds wrote,
> I will, in fact, claim that the difference between a bad programmer and a good one is whether he considers his code or his data structures more important. Bad programmers worry about the code. Good programmers worry about data structures and their relationships.
https://lwn.net/Articles/193245/
[+] [-] weka|7 years ago|reply
I'd wager more don't even know what a join is.
[+] [-] Scaevolus|7 years ago|reply
[+] [-] bas|7 years ago|reply
[+] [-] kumarvvr|7 years ago|reply
- Step 1 - Step 2 - Loop through results in Step 2 - Curate and finish output.
I try very hard to transform the above steps into an SQL statement spanning multiple tables, but always fail and I usually fallback to python for manually extracting and processing the data.
Does anyone else face this problem?
Any suggested guides / books to make me think more SQL'ley ?
[+] [-] beatgammit|7 years ago|reply
Basically, instead of thinking in terms of how to get to the result, think instead of the result and figure out how to get there. For example, let's say I need to get a mapping of students to their classes. One way would be to get the students, then loop through that to get classes for each one. Another way to do it would be to ask the database to combine those sets of data for us instead and return the result (join students and class tables on the student ID).
Basically, find the data you want from each table, tell the database about the relationship between them (join on <field>), and set up your constraints. I guess you could think about the query as a function, and you're just writing the unit tests for it (when I call X with parameters Y, I expect Z).
[+] [-] daveslash|7 years ago|reply
(1) Think of a database as a "big pile of stuff in a room". Some of it's ordered in a sane way, some of it's not. There is "a person at the door" to the room preventing you from entering - this is the Database Engine, not to be confused with the Database itself. You need to get things out of the room, and you need to put things in the room. You're not allowed to enter. If you want something out of the room, you must ask for it (query) by specifying what the thing looks like and which part of the pile you think it's in. If you want to put something in the room, you must provide explicit instructions for where it must go. When you write SQL, you are either asking a question of or giving instructions to the database engine. The DB Engine is distinct from the Database (the big pile of stuff...which you never have direct access to).
(2) As others have pointed out, SQL is very much the mathematics of Set Theory put into a programming language. Stanford has a free class on Databases. I took it a few years ago (it may have changed), and most of it had no SQL at all. It was fairly very straight forward - things you can do with pencil and paper. It's free, so no pressure. Go sign up and do some of the homework assignments. Do them until you get them right (you can re-take wrongly answered problems immediately). It'll help break your procedural mindset. https://online.stanford.edu/courses/soe-ydatabases-databases
[+] [-] ramraj07|7 years ago|reply
Of course you can always screw up and write suboptimal SQL that will take forever to finish, but it's just harder to do unless you're really trying to be dumb about it.
So I do think there's still a lot of merit in trying to learn and use SQL more.
[+] [-] kccqzy|7 years ago|reply
[+] [-] bigger_cheese|7 years ago|reply
I find myself having the opposite problem I probably use (abuse?) SQL more than should.
My world (industrial plant) is very DB/historian heavy everything speaks SQL it's pretty much the common tongue connecting everything. I think this is slowly changing some PLC/historians now offer a webservice which returns JSON objects via an ajax query - personally I vastly prefer SQL to ajax.
I'd estimate for a typical problem I'm working with maybe 90% is done in SQL vs 10% in R/SAS code.
When I need Regression, Principal Component Analysis, Time Series manipulation, Plots etc I have to break into dedicated language.
For most other things - extract, merge, filtering, high level aggregation (Count sum etc) type of operations using SQL feels more natural and expressive to me.
[+] [-] eleclady|7 years ago|reply
But over time, and very heavy SQL reporting, I realized the procedural mindset still applies, it's just not expressed as such.
You need to think through the steps of how you want to retrieve, transform, aggregate etc. the data and build up the query to accomplish those steps. The resulting query doesn't look procedural, but the thought process was. Of course you need to know the sometimes opaque constructs/tricks/syntax to express the steps, which again, look like magic at face value or just reading docs.
I think this is why people struggle with understanding SQL. The thought process is still procedural, but the query isn't. You need to translate the query back into procedural terms for your own understanding, which is a PITA.
[+] [-] bluetwo|7 years ago|reply
Just the basics, very clear and easy to understand.
[+] [-] bashmydotfiles|7 years ago|reply
It also helps to change the language you use in your inner monologue. Instead of thinking, "For each row in table A...", you should think, "For all the rows in table A that match on...".
[+] [-] hardwaresofton|7 years ago|reply
DBs like Kafka who recognize this and instead offer SQL on top of their things take the right approach IMO KSQL.
[+] [-] dhbradshaw|7 years ago|reply
Having a working intuition for relational databases is valuable on a deep level. I mean having a sense of how to organize the tables, what sizes are large and small, when to add what kind of index and what the size and speed limits are likely to be for a given data structure. That's extremely valuable.
BTW, we're preparing to move a postgres database that's a few TB from Heroku to AWS RDS. The catch is that we can't afford more than a few minutes of downtime. If this is in your wheelhouse, reach out! We'd like to talk.
[+] [-] triplee|7 years ago|reply
And oddly enough, now I'm a data engineer. Everything I do, even when I'm not doing pure SQL, is influenced by years of experience in SQL. Either the languages of my big tools are still based on SQL in some fashion, or it simply helps to have an understanding of the ecosystem to figure out what's going on at scale.
Everything else has come and gone and come again, but SQL has help up pretty nicely. The only other skills that come close for me are working in languages that branched out of C (because the syntax isn't so different over time) and years of on again off again procedural languages (ColdFusion, vanilla Javascript, etc. leads to it being way easier to pick up Python).
[+] [-] teej|7 years ago|reply
- Common Table Expressions (CTEs). They will equip you to write clean, organized, and expressive SQL.
- CREATE TABLE AS SELECT. This command powers SQL-driven data transformation.
- Window functions, particularly SUM, LEAD, LAG, and ROW_NUMBER. These enable complex calculations without messy self-joins.
Learning those three will change your relationship with SQL and blow the door open of problems you can solve with the language.
[+] [-] GolDDranks|7 years ago|reply
[+] [-] vietvu|7 years ago|reply
[+] [-] truth_seeker|7 years ago|reply
You may call me an extremist but from server side programming point of view with Postgres and FDW (Foreign Data Wrappers) which has ton of features other than SQL only thing i miss is HTTP server. :)
[+] [-] physcab|7 years ago|reply
On one hand, you write what you want to find in fairly common language and you almost always get back what you want if you do it correctly. In many ways, it’s like a precursor to Alexa but in written form. It’s super easy to pick up for non technical people.
On the other hand, it’s extremely difficult to code review, and on a very complicated piece of business logic, errors could mean the difference between hiring 10 more people or laying off 100. So almost always it’s just easier to re-write.
Imagine if engineers couldn’t understand each other’s work, and had to rewrite code every time someone leaves the team. It’s insane to me that this is standard practice.
[+] [-] xpil|7 years ago|reply
This is why SQL language and IP protocol are two most valuable things in computer world.
[+] [-] tigerlily|7 years ago|reply
Thank you Craig, I'm convinced. Anyone know where best to begin learning SQL?
[+] [-] diggernet|7 years ago|reply
Within two months into my first job out of school, I was assigned to implement a SQL parser as a modern new interface for an ancient proprietary database. Every job since, I've written tons of SQL queries. SQL rocks.
Life is funny that way.
[+] [-] richpimp|7 years ago|reply
From an analytics point of view, I can't imagine not using SQL. I've seen people pull reports from multiple websites, text files, etc., spend an entire day manipulating them in Excel, and still not get their data model working as expected, not to mention that it is very slow. A couple of queries with some temp tables and voila, magic happens. It really does make you look like a superhero when you can deliver more accurate results in a fraction of the time it originally took. I'm surprised there isn't more of a market for this skill, surely there's a lot of programmers from the 80's and 90's who have this skillset in abundance.
[+] [-] johnmarinelli|7 years ago|reply
I think SQL is one of those essential "secondary" skills for developers.
[+] [-] dsego|7 years ago|reply
[+] [-] jlj|7 years ago|reply
My thinking on SQL has evolved and lately I see it as a set definition tool. "Do action X on dataset Y." It's really useful for understanding data structures and data meaning too.
I've worked with more than a few SDE's who look down on SQL, but it's a really good tool when used properly, and it cuts across many technologies. Writing code to write SQL can be very powerful. And sometimes coded or scripted data wrangling without SQL is very useful too.
15 years ago SQL knowledge was not that widespread and it was easy to get tagged as a report writer. Today, a lot more business, product, finance, and accounting people are really strong with SQL, and rely heavily on exporting data to excel for further analysis. Knowing how to answer business questions, get insights out of the data, and define or categorize sets of data are all enhanced by SQL. Report writing is not as much of a thing anymore because people want to view the data in diverse ways.
The barrier to entry is low with SQL, but learning it well takes time and some mistakes to get efficient and precise with it. 15 years later I am still learning new uses for it. One example is JSON querying and transformation which is supported by hive, presto, and some other compute platforms. It's easy to mix and match JSON, arrays, and tabular data structures, in one or more tables, from the same SQL query.
[+] [-] iblaine|7 years ago|reply