I’ve met far too many devs who hide behind ORMs instead of writing raw SQL and it makes me sad. There’s so much one gives up by allowing an ORM to handle everything for you.
SQL: One of the most valuable skills
171–180 of 390 posts
Re: SQL: One of the most valuable skills
#172I 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 writi…
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.
Re: SQL: One of the most valuable skills
#173I would say the same about bash.
You can be a good developer and not know bash. Bash is, in some ways, programming horror. But SQL is pretty fundamental. It's a thing onto itself. There are plenty of shells and shell-like languages but there is really only one SQL.
wrt to shells - the combination of find/grep/sed/awk for locating and transforming textual data makes for its own kind of extremely powerful query language. I've always valued being able to think in pipelines made up of these tools.
Re: SQL: One of the most valuable skills
#174Earlier quoted context omitted.
And sed, grep, and awk (though awk by itself can often do the job of the first two).
Yeah, I use AWK constantly, not just for reading files line by line, but whenever I need to e.g. create data in some format, like CSV or XML or for my programs, or to write repetitive code in a program or LaTeX file (e.g. for a lot of images) etc. It's just so quick and easy, such a versatile tool, a pleasure to use. I learnt sed and grep too but rarely use them.
I only got into AWK much later than grep/sed but I was glad to discover some of awks power. Sed is still and will always my first love, but more and more I reach for awk to learn it as there is still so much I don't know.
Re: SQL: One of the most valuable skills
#175Earlier quoted context omitted.
"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 fr…
I don't disagree. I just don't think we've figured out the next step. Other paradigms (MapReduce and graph databases are perfect examples) have introduced very interesting and clever ways to querying data, but nothing has replaced SQL. If anything, it seems like it will be additive on top of what SQL has already done. I know a lot about data, but I can't solve that problem. To anyone out there, much smarter than me:…
I think graph databases with time will eventually be eating some of the RDBMs market (used for the same purpose), but they haven't had the time to mature as SQL/RDBMs.
Re: SQL: One of the most valuable skills
#176Earlier quoted context omitted.
Hmm... I'm not sure. I've seen people take hours or even days (in the case of more junior developers) trying to sort out these kind of issues. It's pretty helpful if you have (or are) someone who can waltz over and fix it in a couple of minutes. Complex git problems are another one that can really throw people sometimes.
Oh it's definitely helpful! And it's something I should finally take the time to dedicate a weekend to shoring up my knowledge. Just every time I have to deal with a sql database, my lack of knowledge (at least to this point) has never been a show stopper. It takes me a little more time than a real pro to get my statements figured out, but it's rarely so complex to require canvasing the shop for a true expert.
This is pretty much where I'm at. Though I'm on a mission to really nail fundamentals this year and SQL plus query tuning is the next stop for me. I've got 20 hours of courses related to SQL fundamentals lined up Pluralsight and another 30 odd hours SQL Server / Query Tuning.
The half-life of the knowledge is so long it's just a big boost for a really long time to become an order of magnitude better at it.
Re: SQL: One of the most valuable skills
#177I believe, that the way data is structured in Prolog is easier and more flexible than in SQL. Sadly I was busy with other things, so I can't tell for sure how it looks for bigger projects and I haven't tried Datalog yet. But I wouldn't consider SQL as a holy grail, after I run into various issues when dealing with not so relational data.
Re: SQL: One of the most valuable skills
#178Earlier quoted context omitted.
Once your SQL gets into 500-1000 lines, and hours of runtime, I would suggest using data frames instead (in R or Python). 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…
This isn't meant to offend, rather as a point of consideration, but seeing your example use case being 10GB and then talk about big data frameworks makes it hard for me to take this advice seriously. 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…
It seems like you misunderstood what I wrote. I'm saying you should consider using R or Python if your reports are taking a long time, not big data frameworks.
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.
Re: SQL: One of the most valuable skills
#179[deleted]
Re: SQL: One of the most valuable skills
#180I 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 writi…
Once your SQL gets into 500-1000 lines, and hours of runtime, I would suggest using data frames instead (in R or Python). 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…
If catting 10gb of files takes 5 minutes than catting 500gb of files takes 250 minutes. This is, of course, an over estimation of how long it takes to look at data on a disk. It's also the most trivial thing you can do with data, read it once.
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.