SQL: One of the most valuable skills
craigkerstiens.com
craigkerstiens.com
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.
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.
Unfortunately the vast majority of SQL users aren't that proficient. It's also fairly hard to learn because it's something that you only pick up with experience and specifically longer time experience with a sufficiently complex data model.
I personally would never have gotten good at SQL if I hadn't stayed in my first two jobs for 5 years each working daily with the data-models and the domain.
But as my career has gone forward, I'm touching it less and less; today, I hardly touch it at all outside of my personal usage.
Much of that has to do with the fact that I'm now employed building and maintaining an SPA using javascript and nodejs where the backend is accessed thru a RESTful API; we never get to touch the actual database.
The few times I have seen some queries for that DB - albeit not in our API, though I could probably find them somewhere - all I can hope is that the SQL engine being used does some kind of query optimization on-the-fly, because there's so many inner selects that make me cringe it ain't funny (like I wonder if they've heard of joins and such).
Before, I was involved in a lot of PHP web apps and backend server automation, where I needed to use SQL a lot; I feel like I am getting rusty in it.
To illustrate on `select` queries, you start listing the attributes then the table, while on `updates` you start by specifying the table and then the attributes on which on operate. This illustrate the grammar problem: in one case, you start by bringing what table you will use and set on which attributes, the other the attributes you need, while keeping in mind on which table name since you specify it after. It's not really a problem, but developer tends to hate any kind of cognitive load, and this one source of load.
I'm not an ORM fan, but developper often use the programming language of their application to build SQL queries string, and ie. with such tools you always start by specifying the target table.
Personally I really wished that RDMS would provide another intermediate language, or better, data structure, to interface with them.
I see it on a spectrum between declerative and imperative data structures, and where I think most people go wrong with it is trying to create a monolithic solution to a broad spectrum of problems. I think you need a graduated approach where each data layer is simplifying and satisfying the next, so you're using Tables, Procs, Views, and in-memory constructs in concert. The database is a powerful tool, and SQL is just part of that bigger puzzle :)
Updates aren't different from select statements: first you state what you want, update a table with some new column values, and then you state where this data should come from, and what data you want to update.
And this is why it sucks. Which is fine, the language comes from a different era when our understanding of computer languages was much more primitive. It is understandable that mistakes would be made. What is unfortunate is that there has been little to no progress in improving on those mistakes in this problem space.
In the procedural world, you could also say that C has some mistakes in its design. However, we've gone to great lengths to try and improve on C, for example in semi-recent times with Rust and Go. SQL could really benefit from the same. SQL gets a lot of things right. So does C. But we can do better on both fronts.
Unfortunately, it seems that people regularly confuse SQL with the underlying concepts that SQL is based on. Because there is no basically no competition in this space, it is assumed by many that this type of problem can be expressed in no other way, and that you simply do not understand SQL well enough if you do not see it as perfect. I guess it comes from the same place of those who argue that if you make memory management mistakes in C, you just don't understand C well enough.
That is, minimize the network bandwidth by putting the work on the DB engine.
This of course necessitated creating and understanding proper SQL query building practices. It was real easy to mess up if you didn't know what you were doing (ie - inner selects, improper joins, etc) and cause a combinatorial explosion that would consume all the RAM on the server and grind it to a halt.
That, or bring back a load of data that you then filtered on the "client" - better to let the DB server do that if you can. Of course, this was back when the clients were 486s and early Pentiums with maybe 8-16 MB RAM. Today it's a bit different, but you still want to minimize the network traffic.
SQL is troublesome to some programming types because it seems alien to ask what you want instead of telling the computer what to do and I find most programmers, especially ASD-types (who I think have an edge for some situations, like writing certain code in a huge org like Google) find this an unfamiliar and strange way of thinking.
You're right about some of it (especially string manipulation, which is brutal) but I think you look at it like most programmers. SQL engages more of a simulative mindset—one popular with analysts—than the acquisitive mindset that most software developers outside of data science employ.
My issue with SQL is that a programming language should allow you to compose and name building blocks, and then recombine them to build ever more useful software. SQL doesn't have this structure. If you have a query that almost does what you want but you need to add one more filter, you need to reissue the query (modulo views/temporary tables, which is why I said "very clumsily compositional" rather than "not compositional"). If you have an expression that computes some quantity and then you want to use it with slight modifications in a lot of places, you usually end up copy & pasting it (modulo stored procedures). Modern programming languages have made this sort of abstraction really easy, but it's quite clunky in SQL. You can do it (by using stored procedures, views, triggers, subqueries, etc.), but then most of your application ends up written in SQL and it starts to feel like something out of the 1970s.
My preferred interface would be something like the relational algebra where relations are represented as typed values in the host programming language and operators are normal method calls (or binary operators, depending on language flavor) and importantly, intermediate results can be assigned to variables. I don't care what the particular execution strategy is of the query, but I do think it should be possible to refine, join, project, and subquery using values you've already defined.
That's basically how most tools work outside of software development. When you use Google, you aren't telling it how to find your result; you're telling it what you want.
Even with myself, who has worked with Oracle writing PL/SQL (which is an abomination imo) in past jobs still has to sit down and get into the right mindset before writing efficient SQL for more complex queries.
Conceptually SQL is much more simple than programming, it basically reads like english:
SELECT customer, SUM(total)
FROM orders
GROUP BY customer
WHERE created BETWEEN '2018-01-01' AND '2018-12-31'`
ORDER BY SUM(total) DESC
totals = {}
for row in rows:
if row.created > '2018-12-31':
continue
if row.created < '2018-01-01':
continue
if row.customer not in customer_totals_2018:
totals[customer] = 0
totals[customer] = totals[customer] + row.total
def _sorter((customer, total)):
return -total
for customer, total in sorted(totals.items(), key=_sorter):
print(customer, total)
Now imagine adding an AVG(total) to both the SQL and programming version...
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.
It's way easier to teach someone to code who is already really good at manipulating/modeling data than the other way around, IMO.
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.
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.
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.
https://lagunita.stanford.edu/courses/Engineering/db/2014_1/...
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.
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?
First, a database can have hash indexes instead of btree indexes, so lookups can be O(1) too, but it turns out that btrees are often better because they can return range results efficiently, and finding the range in a btree is only logarithmic for the first lookup. If your index is clustered - if it covers the fields needed downstream in the plan - no further indirections are needed and locality is far better than a hash lookup. For example, a filter could be efficiently evaluated along with the index scan. And if your final results need to be sorted, major bonus if the index also covers this.
Second, it's best to think of databases doing operations on batches of data at a time. Depending on your database planner, different queries will tend to result in more row-based lookups (MySQL generally does everything that isn't a derived table, uncorrelated subquery or a filesort in a nested loop fashion) but others build lookups for more efficiency (a derived table in MySQL, or a hash join in Postgres). The flexibility to mix and match hash lookups with scans and sequential access - which are usually a few orders of magnitude faster than iterated indirection - means it can outperform graph traversal that is constantly getting caches misses at every level of the hierarchy.
The reason NoSQL and document databases suck from a relational standpoint is that they are bad at joins, and work better if you denormalize your joins and embed child structures in your rows and documents. Add decent indexes to NoSQL and they gradually turn back into relational databases - indexes are a denormalization of your data that is automatically handled by the database, but have nonlocality and write amplification consequences which can slow things down.
In terms of distribution, a relational plan can be parallelized and distributed somewhat easily if your data is also replicated - sometimes join results need to be redundantly recalculated or shuffled / copied around - and most analytics databases use this approach, though usually with column stores rather than row stores, again because scanning sequential access is so much faster than indirection. Joins don't always distribute well, is the main catch.
Many recent web & mobile apps have a lot of screens where you just want to grab one blob of heterogenous data and format it with the UI toolkit of choice. Or if they do display multiple results, it's O(10) rather than O(1000) or O(1M). Chasing pointers is fine for use-cases like this, because you do it once and you have all the information you're looking for.
This is also behind the recent popularity of key/value stores and document databases. If all you need is a key/value lookup, well, just do a key/value lookup and don't pay the overhead of query parsing, query planning, predicate matching, joins, etc. When I was working on Google Search > 50% of features could get by with read-only datasets that supported only key/value lookup. You don't need a database for that, just binary search or hash indexes over a big file.
The application I work on in my day job does not match the key/value lookup idiom at all. User-defined sorts and filters over user-defined schema, and mass automated operations over data matching certain criteria. If you squint a bit, the app even looks a bit like a database in terms of user actions.
And even relational databases (at least row-oriented with primarily disk storage) have their limit here. With increasing volumes of data, it can't keep up. We can't index all the columns, and indexes can't span multiple tables. We increasingly need more denormalization solutions that convert hotter bits of data into e.g. in-memory caches that are faster for ad-hoc sorts and filters. Database first is a decent place to start, though having a first-class event feed for updates would certainly be nice...
Yup. Thing is: with RDBMSs you are in control of both the storage patterns and the access patterns of your data. That's where a large part of the performance benefit comes from.
> 50% of features could get by with read-only datasets that supported only key/value lookup
Did you implement a storage pattern that was ordered by key (or hash(key) if you used hashing)?
The power of SQL comes from the fact that you can easily create new information out of the data: create new sets, group by certain features, aggregates on certain features.
It's a lot more powerful than just store and retrieve.
Checkout that platform that a full-blown Oracle license can provide to your DBA... It's definitely more than just CRUD.
-
[0] https://www.oreilly.com/library/view/discovering-sql-a/97811...
It also doesn't bother with tons of consistency guarantees and such that you (may) get from, say, PostgreSQL. Yet is still slower for many purposes.
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
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.
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.
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.
https://www.amazon.com/Art-SQL-Stephane-Faroult/dp/059600894...
'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.
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.
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?
1. You believe in your interpretation. This is true.
2. The parent poster has their interpretation. This is true.
3. The statement "They are surely talking about the specific syntax" is true if and only if their interpretation is the same as your interpretation.
4. This is a semantic argument now.
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.
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.
My bulk of experience in Sql was in MsSql. Recently I got into an Oracle shop and the dialect is a bit different though it didnt take too long to be productive in. However, being productive and such, if you ask me, oracle smells clunky and has way too many features, let's say it's not my cop of tea...
Also MS SQL let you throw a data object (say XML) at a stored procedure, convert it to a table structure with some XPath (another useful black art like regex) and use that as the input to a single INSERT.
You’re giving me flashbacks to stored procs that directly invoke java methods, and batch scrips that load client supplied data from and FTP server into external tables.
A legacy application I once worked at used this extensively, and good grief it gave me nightmares.
How anyone ever thought this is a good idea to use is beyond me; that stuff is not maintainable at all.
To make it worse, the app had about 300 scheduled tasks that were a mixture of batch files, SQL scripts and java classes. None of which had source code, most of which were slightly different and essentially operated as mysterious black boxes. We ended up having a copy of JD-GUI on all the production servers because we needed to decompile for debugging so often.
The sql server agent was easy and reliable for scheduling so it made sense to let it run the whole process.
We were doing this 20+ years ago, all code was in csv (we upgraded from rcs to csv), and we had a productized distributed scheduling system that would deploy all the sql scripts every night on a number of oracle databases running from aix, to solaris, to vms, to hpux, to irix, and later linux and windows NT.
Similar like you would now use jenkins to build, deploy and test your java apps.
Developers would never touch the test/production databases, only commit sql to csv. Develop local, sql text files, test on a local or shared development database, and then commit to csv.
Jump-to-definition when something calls something else. Unit tests. Atomic build/install.
> this can be a sql or shell script, or a gradle file or even make.
Precisely the problem. There's too many different ways to do it, and no consistency.
What is the best practice workflow using git with SQL server views/procedures? Can you actually somehow track changes in the views/procs themselves so that if someone happens to run ALTER VIEW, git diff is going to show something?
Outside of commercial tools dedicated to the purpose: you can query the DB for the content of the procedures/tables and compare them to a given set of scripts, or the most recent expected version. Auto-generated ORM models can be used to validate table/view composition for a given DB/App version, as well. Having these capacities baked into the versioning and upgrade process can do a lot over time to correct deviating schemas and train developers away from meddling with DBs outside the normal update procedure :)
If you are asking about SQL text then a good formatter would make it easy to see the physical diffs. Otherwise there are various vendors and tools that parse SQL and show the logical diffs in queries and schemas, along with doing deployments, backups, syncing changes, etc.
You basically shouldn't allow anyone to modify anything without it being scripted (bonus points if it comes with a rollback and is repeatable for testing). Your scripts then all go into Git.
I second the rollback and repeatability bonus. Every script should leave the database in either the new state or the previous good state no matter how many times it's run.
I wonder if there were somewhere a website to describe good idioms to achieve this?
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.
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.
I might reach for that kind of tooling at the hundreds of TB to PB scale, but in our production applications we have _tables_ that are multiple terabytes. SQL is just fine.
Yes, we also have have queries that run in the timescale of hours and they are always of the reporting/scheduled task variety and absolutely vital to our customers. Long running reporting queries are pretty acceptable (and pretty much the norm since forever) outside of the tech industry and your customers won't balk at it.
Big data was a reference to thinking about the problem in terms of the speed of the hardware. If it's 1000x slower than what the hardware can do, that's a sign you're using the wrong tool for the job.
Getting within 10x is reasonable, but not 100x or 1000x, which is VERY COMMON in my experience. These two situations are very common:
1) SQL queries that are orders of magnitude slower than a simple offline computation in Python or R (let alone C++). The underlying cause is usually due to bad query planning of joins / lack of indices.
You might not have the ability to add indices easily, and even if you did, that has its drawbacks for one-off queries.
2) You need to do some computation that's awkward inside SQL. Statistics beyond basic aggregations, iterative computations (loops), and tree structures are common problems.
From the Enterprise side I think too many developers have an unfounded expectations around data storage technology. There's this unchallenged belief that monolithic datastorage that will solve thier problems across the entire time/storage/complexity spectrum. By bringing multiple tools to bear, instead, you end up with more purpose built storage but far less domain impedence.
Slapping a denormalized NoSQL front-end for webscale onto a legacy RDBMS can be a cheap win/win to maximize the capabilities of both. SQL + R is oodles better than R or SQL in isolation.
If you are writing SQL regularly, understanding the basics of how the queries you write is not that hard for you engine of choice, and everyone should be required to understand the basics of reading an execution plan so they can find the right inflection points between data gathering and processing.
I regularly sigh write and maintain SQL procedures that are >10k LOC, and their runtime never would exceed minutes, much less hours.
I think we have a misunderstanding about the scale of data.
EDIT: Reading your post you mention that dataframes stores data in memory. Working with data in ram would provide a significant speedup. It just wasn't possible.
Some more detail here: https://news.ycombinator.com/item?id=19150971
There are many interesting queries that don't touch all the data in the database. The 'cat' thing was basically assuming the worst case, e.g. as a sanity check.
Reducing a highly used query's execution time by several orders of magnitude can be quite gratifying.
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.
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).
I would think there already is a Stack Exchange for SQL, possibly several (for different RDBMSes/ dialects); go have a look there, if this is what you meant.
In my experience, if you can grok lateral joins (aka cross apply), recursive CTEs, window functions, and fully understand all the join types, that's a gold star for understanding SQL!
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.
I'd wager more don't even know what a join is.
It's a powershell module that allows you to easily dump things directly to excel files, does pretty decent datatables, multi-tabs, etc.
- 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 ?
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).
I have the reverse problem: every time I motivate myself to learn Prolog I feel like I could just input the constraints in a db and use SQL to get my results.
(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
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.
There are certain situations where an SQL query is all that I can work with.
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.
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.
Just the basics, very clear and easy to understand.
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...".
EDIT: A CTE (Common Table Expression) might be what you're thinking you need. You can do some fun recursive queries with them.
It's like all my fears concentrated, like the power of a thousand suns, onto a single sentence.
Some problems can to be assembled from CTEs / subqueries when you might have reached for a procedural solution to 'finish' the problem. Grasping CTEs was a big thing for me and now I'm wrestling with issues of style mixing CTEs with tiny subqueries and wondering if the increase in readability (for me) is correct.
I'd love to see suggestions for digging deeper on this too.
1) Think in terms of excel spreadsheets. At each step in your SQL you are creating a set of data. Doesn't usually matter how many rites it is, you just want the right columns. 2) Once you have a recordset, decide how the next recordset goes together. I would mentally visualize putting one worksheet next to the other and think through what columns I had to join together to get the rows I wanted. 3) Make sure you know what your first recordset should be. This is the base data that everything can be joined into. I refer to this as the domain of records.
Using another tool for processing data often results in recreating SQL mechanics at application level. E.g. select this data, retrieve it, loop and if this, then set that, etc. SQL does it way better, guaranteed.
Of course that's often required for technical reasons (scalability etc.) or processing that's too complex to implement at data layer, or just for cleaner design.
But SQL is amazing at processing data!
I think it influences the mindset of the developer. As you say, “retrieve ... if this, then ... loop”. If you're in a “data processing” mindset, then you'll think of a problem like “Get the total number of car widgets in the warehouse” as fetch a widget row; if it's of type car, add number to total; loop until you've processed every row; there you have your total. If, OTOH, you're in an “asking questions” mindset, you'll go: What was the question again, exactly? Oh yes, get the sum of the number for all the widgets which are of type car widgets. Which is almost exactly the same as SELECT SUM(NUMBER) FROM WIDGET WHERE TYPE = 'CAR';.
Processing data is when you do it (in code); answering questions is when the RDBMS (i.e, its code) does it for you. :-)
(At least that's what I think the difference is _in terms of vvkumar's original question._)
We do >95% of our transformations with pure SQL and the queries are primarily in Looker.
DBs like Kafka who recognize this and instead offer SQL on top of their things take the right approach IMO KSQL.
No, its Datalog. Joking aside, they are equally powerful but one could argue about explicit vs implicit joins.
A recent up-and-coming software tool is the Open Policy Agent[0] and it uses Datalog -- though it's a custom implementation. This comment felt familiar so I went back and looked and it's come up on HN before[1].
If I'm going to bet my career on one or the other, it'd be SQL.
Slight aside, I think there are good lessons to be learned from LINQ still.
We just might happen to live in a world where all the RDBMSes have that same failing, but I'd argue Postgres doesn't fail as bad in the are with things like row level security[0].
[0]: https://www.postgresql.org/docs/current/ddl-rowsecurity.html
- It doesn't feel very interoperable nor flexible (it's a different "ecosystem") with regards to other software -- meaning that if it's much less "integrate with GraphQL" and more "build on top of GraphQL" or find the "GraphQL" way to do things. I find this to be different from approaches like OpenAPI/Swagger.
- Complicated GraphQL queries start to look like SQL to me, and I suspect that will become even more the case as time goes on, and people decide they want advanced things that are in SQL, like window functions.
- GraphQL locks you in to knowing the backend design from the frontend -- though this was mostly always the case with REST (need to know which endpoint to call), "true" REST/HATEOAS compliant backends held the promise of you just being able to ask for a shape of an object, and make decisions about whether to dive deeper as you go in a principled way. This kind of gets into the semantic web concepts -- but that's mostly a seas of never delivered promises so...
- It's features are mostly offered by tools like Postgrest (vertical/horizontal filtering[0], resource embedding[1]), which offers less complexity.
- GraphQL is going to very likely spend the next few years running into issues/features that the REST/HATEOAS has already solved (albeit not in a standardized way). I looked at a random issue[2] and this is definitely something that seems quite solved in the RESTish HATEOAS world (at the very least you don't have to wait for GraphQL to do something to solve it).
Basically, I wish someone had built the ease-of-use GraphQL offers on top of the HTTP+REST+HATEOAS model -- because it's wonderfully extensible. Excellent solutions often lose out to "good enough" solutions, and I just... don't want that to happen this time (I mean I don't think it will, it's pretty hard to beat out good ol' http).
We just got to a really good place with Swagger/OpenAPIv3 + jsonschema/hyperschema, in being able to create the standardized abstractions on top of HTTP (and bring with it all the benefits of HTTP1/2/3 as they arrive, and it feels like GraphQL is a distraction. The "entity" graph is just a rehashing of the relational database model and it doesn't feel like it offers much outside of a standardized way of presenting your DB -- if you want to present your DB to frontends why not just let them send SQL queries directly? No I'm not tone deaf, I know most devs don't want to write SQL, but it seems like they're about to get into bed with GraphQL despite the likelihood that it's just going to be another SQL once it has enough features.
That said, GraphQL is doing amazing things for developer velocity, and it deserves praise for that -- tools that grasp mind share this quickly usually offer a large amount of real benefits to the people using it, and I've seen that GraphQL does that.
[0]: https://postgrest.org/en/v5.1/api.html#vertical-filtering-co...
[1]: http://postgrest.org/en/v5.1/api.html#resource-embedding
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.
We had just a few minutes of downtime.
I did have few issues around permissions, but an AWS support person walked me through the process and we got it up and running. The support was great, and we just have standard business support.
EDIT: DMS might be a no go, as jpatokal points out. While Heroku allows external connections, it doesn't allow super user access which, AFAIK, DMS requires.
https://devcenter.heroku.com/articles/heroku-postgresql#conn...
Would it be ok to serve stale data for an extended amount of time? If so then you could modify your application to write to AWS RDS but to keep reading from Heroku. Then you start copying over the data from Heroku to AWS RDS, and once the copying is done then you point your application to read from AWS RDS.
Another thing: If your tables have foreign key constraints then you need to create the tables in the new DB without them first, since the writes coming to the new DB could be referencing data from the old DB which have not yet been copied over. Once you’ve completed copying the data over you add the FK constraints to the new DB. Depending on how your application was made this could be a problem or it could be ok.
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).
- 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.
For those unaware of LATERAL JOINs, it’s like a for each loop in SQL. Blew my mind and opened up so many possibilities.
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. :)
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.
I haven't found that particularly true. I've trained people in SQL and yes, they can do the basics pretty well, but it requires a particular skill to actually write effect complex SQL statements. Non-technical people, being non-technical, generally cannot do that.
> it’s extremely difficult to code review
I don't see why it's any more difficult than any other logic? It's always important to test.
Yes non technical people can’t write complex sql, but they can self service basic questions easier than learning Python or R for example.
And it’s difficult to code review SQL because complex business logic means complex data manipulations and those are hard to visualize and comprehend. You don’t have to think in a 3D space so much when you write python.
This is why SQL language and IP protocol are two most valuable things in computer world.
Thank you Craig, I'm convinced. Anyone know where best to begin learning SQL?
If you're on MSSQL server this guy is great. https://blog.sqlauthority.com/. I also love the mug shots he puts everywhere.
Explore some weird things in SQL https://wiki.postgresql.org/wiki/Mandelbrot_set. It's fun to learn how much you can do.
Try to do things you'd normally do in code. Many algorithms can be implimented in SQL. DON'T DO THIS IN PRODUCTION but it's a good way to have fun with SQL and learn a few things.
Find an open source project in an area you're interested in that uses a lot of SQL.
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.
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 think SQL is one of those essential "secondary" skills for developers.
SQL is a declarative set-oriented language. Read/writing SQL as a procedural language will cause a heap of trouble.
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.
The only real problem with it is that it's easy to get into some thorny-looking transformations because there is so much to work with.
When scripting in SQL I have seen that in certain SQL flavors there is no good way to split a large file into k smaller ones for a fixed k without creating a new column 1-<number of records>, selecting k new tables modulo the number in the new column, then writing the k files. The way this is implemented is usually naive so the performance is piss poor due to writing and subsequently reading so much data.
If this candidate was very good at Scala, assuming they were a Spark programmer, they could probably do lots of dataframe operations that were as good or better than SQL commands regarding performance no?
If someone has enough understanding of data and CS fundamentals to be very good at Spark/Scala, learning SQL isn't going to be much of a barrier for them. I find that the bigger problem is usually people who have a very strong understanding of SQL but no clue as to the resources needed to perform different kinds of operations.
Correct me if I'm wrong; I understand you can represent any relational model in hypergraph. And a lot that aren't representable in relational model, but are natural in hypergraph form.
Once you wrap your head around basics such as GROUP BYs, JOINs, CASE statements etc, you move on to advance concepts such as Window functions and there suddenly a new world of possibilities opens up to you for analytics! I've dabbled a bit in PL/pgSQL, but the syntax is way too arcane.
SQL can also be counter-intuitive sometimes. I rewrote a particular PG query and reduced time from ~30minutes down to 2 seconds by adding a subquery. :)
And no, ORMs can't really do what SQL can do in an equivalent manner.
I remember interviewing at FAANG and being asked to code up various tree traversal algorithms... and moments later I would be asked to write window function aggregations in SQL. And it was like this for all the interviews with that company - it was fairly bizarre as I wasn't sure what the aim there was. I understand that SQL is omnipresent, but surely people with algorithmic knowledge would be able to pick up SQL in hours or days, while the opposite doesn't quite hold.
(Oh and I agree with the article and I do like SQL for all sorts of workloads, that's not the point.)
As someone who knows essentially zero SQL, is this really true? How long would it take me to learn it well enough to be competent with it?
What are some good resources to learn?
I need to learn from the very beginning and it would be helpful if it had practical training excercizes as part of the course.
I've also seen this free online Stanford course recommended a lot:
https://lagunita.stanford.edu/courses/DB/SQL/SelfPaced/about
I've mostly been a backend developer... having the ability to go in and fix SQL and make things run in < 1 second instead of 5-10 minutes has been one of the best skills I could have picked up.
It is sad that so often "self described 10x programmers" build solutions to go around SQL that are horrible failures. Poor use of ORMs, weird abstractions at the application layer that force developers to use poor data access patterns, unnecessary "locking" at the application layer, unnecessary "existence checks" at the application layer, processing objects 1-by-1 in the application. All these things lead to terrible performance and huge wastes of application memory/IO.
I love some of the NoSQL solutions too as in some cases they can force teams to use better data organizations/patterns and scale so well. Those patterns are often possible in an RDBMS but the system doesn't guide a team to using those patterns. The way CQL in Cassandra forces you to think about data organization is great for example.
Isn't it obvious that when a column has the name of a table that the idea is that the columns of that refactored out tables become available as if it were a part of the main table?
When the query works regardless of the structure of the table, the database can be refactored with ease. Most such queries would simply specify column names, and a filter to apply on the records. Any joins needed, and which result from the structure of the database, would be inferred.
Such a "NoJoinDB" would clearly boost productivity, and lots of applications could be written with no explicit join at all. A language like SQL seems to hide the simplicity of most queries!
Please comment about the validity of this perspective.
This sort of paradigm really made me understand that writing 'high-level' code and performant code are not mutually exclusive.
Knowing SQL to me was and even now is extremly helpful to understand data and how to tackle data problems. There weren't many other technologies that had such profound long term applicability, besides maybe knowing and understanding HTML and C. Many new things are just variations and improvements of those core technologies, and those can easily be understood and learned with good foundational knowledge.
SQL is not only relevant to traditional databases. I never got into C#, but LINQ looks really interesting.
Also, I had the pleasure of meeting Craig at PyTennessee a few years back. Really great guy and yes, he does seem to wear a hat all the time!
As a data scientist, I spend a lot more time building queries than working with models.
I think the power comes from knowing something -- _anything_ -- well.
I feel the same about vim and common unix tools (bash, sed, awk etc). If you can use these effectively, these can be very effective productivity tools. The learning curve is steep, just like learning to ride a bicycle, but once you do, it is difficult to imagine life without it.
I commented to a friend the other day that my favorite thing to do on any project is project-wide query optimization. It's like pulling weeds or power washing.
Arguably _every_ application is simply a means of manipulating data. All the other parts are important, of course, but the data comes first. And SQL is a hell of a UI for data. I've tried quite a few other query languages (or structures), even inventing my own once or twice, but none come close.
But, like asphalt for roads and aluminum for airplanes, it still can stand a lot of improvement. By the way, it's just as important as asphalt and aluminum, and consequently just as hard to change. That's the curse of the customer base.
I wish the SQL world could agree on standards for string manipulation, and stored function / proc programming.
The first question would be for the statement retrieving an entire table without any restrictions or conditions and 50% would fail giving that query. If somebody would manage to actually build a query involving a JOIN, WHERE and a GROUP BY then that interviewee is pretty much hired ...
Yes, it can appear to be magic to people who don't think that way, but doing things with window functions etc and understanding what the output of EXPLAIN means have helped unbelievably when improving performance etc.
Being able to use a POSIX Shell with tools like curl is certainly one of them as it enables me to connect different technologies on a very practical level.
Furthermore, understanding the basic functional programming principles is invaluable if you want to build clean algorithms (doesn't matter if you use Excel, C or Lisp).
Also, after 25 years in the IT business I still can't write a proper PIVOT clause without googling it first. Shame on me.
(btw I'm a MSSQL nerd. Been in Oracle/ODI world for a couple of years but it was too scary)
It is a skill with the highest bang for buck and is a great way to introduce non-programmers to a basic querying language. It is intuitive, easy-to-read and unbelievably versatile.
Developing an data-driven app without understanding how your data-management solution works is like using just one rollerskate. Sure, you can get places, but...
- https://www.goodreads.com/author/show/93496.Joe_Celko
- https://www.goodreads.com/book/show/7959038-sql-antipatternsMaybe Python is more convenient, cognitively? Pandas and SQL are very similar in concept, though.
Can you suggest more skills and tools like these that are timeless and valuable?
People like other people who are easy to talk with. Also you will be more likely to build what is actually needed, rather than what someone thought they needed.
https://news.ycombinator.com/item?id=18001267
HTML is the quickest way to share something permanently. And its client is the most widely available (the browser).
Many people seem to use something like Dropbox or Google Docs now, but my opinion is that HTML still has many benefits, once you know it. I rsync my HTML to a shared hosting account.
In addition to bash, I'd suggest learning more about Unix in general.
I saw this page a few weeks ago. I haven't gone through this particular course, and I don't necessarily endorse it, but I'll just say that the subject matter could have been largely the same 20 years ago. Unix has proven to be an effective way of working and collaborating for a long time.
https://news.ycombinator.com/item?id=19078281
EDIT: I also suggest knowing some basic analysis outside of SQL, using data frames:
https://news.ycombinator.com/item?id=19150543
The need for that skill isn't going away anytime soon. You could argue that a large portion of jobs are munging Excel spreadsheets, and data frames are more of less the programmer's way of accomplishing the same tasks.
You could be a snob and "delegate" those tasks to others, but IME if you really want to get something done that other people can't, you have to roll up your sleeves a bit. (Just like this article suggests, e.g. SQL is useful for both programmers and product managers.)
Not only in SQL, but also in terms of config files, easy to edit data files for static data, large files, small numerous files, etc etc.
Having the right data model can mean a world of difference as to how flexible your application can be.
Also, decoupling between data models. For example, decoupling related data through proxies and procedural code.
i.e. distilling business problems down into tables and columns.
Skills acquired decades ago are still extremely useful.
I do say "seem" though, in that if you weren't around it's not like the team would fail without you. It's not hard to learn SQL or Google around for a little bit to figure out what you're trying to accomplish.
Sort of like that one guy on your team who's a wizard at regular expressions. He seems like a life saver in the moment, but you'd be able to figure it out without him, too.
Complex git problems are another one that can really throw people sometimes.
A significant percentage of my time over the past few years has been spent optimizing queries that other people did in obvious, but non-ideal ways.
Like all good abstractions, SQL is the practical expression of a mathematical theory. In the case of SQL, you use Zermelo-Fränckel (ZF) set theory to reason about data sets.
While it is easy to come up with merely conjectural implementations for haphazardly doing things -- arbitrary trial and error, really -- it is notoriously hard to develop a systematic axiomatization such as ZF. Mathematics do not change and are hard to defeat. That is why math-based knowledge and skills are able to span an entire career without having to re-learn.
SQL got kicked off in all earnest when David Child's March 1968 paper, "Description of A Set-Theoretic Data Structure", explained that programmers can query data using set-theoretic expressions instead of navigating through fixed structures. In August 1968, Childs published "Feasibility of a Set-Theoretic Data Structure. A General Structure Based on a Reconstituted Set-Theoretic Definition for Relations".
Data independence, by relying on set-theoretical constructs, was explicitly called out as one of the major objectives of the relational model by Ted Codd in 1970 in his famous paper "A Relational Model of Data for Large Shared Data Banks" (Communications of the ACM 13, No. 6, June 1970).
Using Codd's work, Donald Chamberlin and Raymond Boyce at IBM, developed in the early 1970s what would later turn out to become SQL.
Introduction
Project:M36 implements a relational algebra engine as inspired by the writings of Chris Date.
Description Unlike most database management systems (DBMS), Project:M36 is opinionated software which adheres strictly to the mathematics of the relational algebra. The purpose of this adherence is to prove that software which implements mathematically-sound design principles reaps benefits in the form of code clarity, consistency, performance, and future-proofing.
Project:M36 can be used as an in-process or remote DBMS.
Maybe we'll get to a better query language eventually. Distributed systems have some fundamental incompatibilities with SQL and theoretically could standardize on a different language, one that is not just about the data anymore, but that also lets users express and choose various performance and consistency trade-offs. I mean a query shouldn't, for example, invoke a transaction and consensus algorithm when all you need is to increment a view counter or give a one star rating or insert a log entry, those things can be propagated eventually without destroying latency and throughput and without complicating anything with transactional semantics.
The direction this line of thinking trends in is not thinking or caring about what data you're storing or caring about how it scales. If you're successful enough, this becomes an enormous problem. It literally is a problem that sinks otherwise successful companies.
Not thinking about your data model, not caring about the interactions of your data and not caring about the reliability of your data is a very expensive problem. It's also one where you don't know better until you do. Take some advice from the rest of us, please. We're saying this for your benefit, so that future you doesn't follow in our footsteps.