SQL: One of the most valuable skills • Hacker News Board

Re: SQL: One of the most valuable skills

#261

Post by michaelt » Wed, Feb 13, 2019, 11:11 AM UTC

raverbashing wrote: post #200

slap_shot wrote: post #67

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 p…

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

When people say "SQL" they often refer to a bunch of concepts, some better than others, all bundled together.

SQL gets you tables and joins, sure. But it also gets you queries that (some) non-programmers can write, outputs that are always a table, a variety of GUI programs to compose queries and display the results, analytic functions to do things like find percentiles, and tools to connect from MS Excel. And it often means you get transactions, ACID compliance, compound indexes, explicit table schemas, multiple join types, arbitrary-precision decimal data types, constraints, views, and a statistics-based query optimiser.

Of course it also gets you weird null handling, stored procedures, and triggers.

Re: SQL: One of the most valuable skills

#262

Post by tremon » Wed, Feb 13, 2019, 11:23 AM UTC

ako wrote: post #226

nostrademons wrote: post #196

Earlier quoted context omitted.

Most programmers who are at all familiar with functional programming, DSLs, configuration languages, or optimizing compilers are very well versed with asking the computer for what you want rather than telling it what to do . At least when I was there, this was a very large percentage of Googlers. My issue with SQL is that a programming language should allow you to compose and name building blocks, and then recombine…

I don't see views and subqueries as clumsy. Especially with sets, you think in creating new sets, and combining those into other sets. In postgres i've created financial year reports, with monthly summaries per category just by having a few layers of views. I think it's really valuable i can think in logical sets, and the database will takes all those layers of views and combine those into one optimized query plan.

But views and functions still require you to persist those objects in the database first. There's no such thing as a query "variable" that you can then re-use in multiple subsequent statements. Of course, you can use table variables or temporary tables to hold intermediate data, but those are eagerly evaluated, whereas functions, views and CTE's are lazily evaluated, and that allows for a massive performance boost (due to optimization).

I can see the appeal of such a construct in SQL. The requirement that reusable objects are persisted in the database requires a top-down design approach, and that doesn't really blend well with modern coding practices.

Re: SQL: One of the most valuable skills

#263

Post by phonebucket » Wed, Feb 13, 2019, 11:25 AM UTC

Another aspect of SQL that I have come to appreciate is it's underlying philosophy: state what you want at a high level with a strict syntax; in exchange, your backend will make aggressive optimisations for you.

This sort of paradigm really made me understand that writing 'high-level' code and performant code are not mutually exclusive.

Re: SQL: One of the most valuable skills

#264

Post by wilsonnb3 » Wed, Feb 13, 2019, 11:30 AM UTC

Hippocrates wrote: post #2

I would say the same about bash.

As someone currently employed as a .net developer, I’m not sure I would say the same.

Re: SQL: One of the most valuable skills

#265

Post by [deleted] » Wed, Feb 13, 2019, 11:34 AM UTC

Pamar wrote: post #204

jwdunne wrote: post #197

Earlier quoted context omitted.

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

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

[deleted]

Re: SQL: One of the most valuable skills

#266

Post by gherkin1 » Wed, Feb 13, 2019, 11:34 AM UTC

>You seem like a superhero. You seem extra powerful when you know it because of the amount of people that aren’t fluent That's exactly what I said to newer developers about SQL. Another two examples of this sort of skills are git and regex. Most people knows how to use them but don't dive into them that deep and you just need to spend very little time -hours, or days at most- and you will better than most people at them.

Re: SQL: One of the most valuable skills

#267

Post by tremon » Wed, Feb 13, 2019, 11:35 AM UTC

nostrademons wrote: post #222

barrkel wrote: post #193

Earlier quoted context omitted.

Chasing pointers (on the same medium) is usually slower than the access patterns that databases usually use. 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…

Depends a lot on the actual access patterns of your data. 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.…

Depends a lot on the actual access patterns of your data.

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)?

Re: SQL: One of the most valuable skills

#268

Post by goto11 » Wed, Feb 13, 2019, 11:41 AM UTC

nostrademons wrote: post #196

3pt14159 wrote: post #186

Earlier quoted context omitted.

I understand your perspective, but I look at it a different way. 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 s…

Most programmers who are at all familiar with functional programming, DSLs, configuration languages, or optimizing compilers are very well versed with asking the computer for what you want rather than telling it what to do . At least when I was there, this was a very large percentage of Googlers. My issue with SQL is that a programming language should allow you to compose and name building blocks, and then recombine…

'With'-clauses (aka Common Table Expressions) allows you to name and compose subqueries much more cleanly.

Your preferred interface sounds like Linq. An IQueryable interface represent a query and can be composed and assigned to variables, and the type T represent the type of the relation or projection. Linq composes a lot more elegantly than SQL itself. The instances does not represent intermediate results though, they represent composed queries, which are executed in one go on demand. But I think that is what you want anyway.

Re: SQL: One of the most valuable skills

#269

Post by veritas3241 » Wed, Feb 13, 2019, 11:50 AM UTC

I fell in love again with SQL while working in my current position. What _really_ made me fall in love was the project dbt[0] which is a SQL compiler and executor. You can build a DAG of transformations all the way from your raw data to tables ready for viewing in your BI tool or consumption by your ML model. I'm still amazed at the things I can do with SQL alone without having to bring Python into the picture.

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!

[0] https://www.getdbt.com/

Re: SQL: One of the most valuable skills

#270

Post by veritas3241 » Wed, Feb 13, 2019, 11:52 AM UTC

DrPhish wrote: post #172

max76 wrote: post #31

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 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 t…

You might be interested in this project then https://www.getdbt.com/ it's a SQL compiler and executor which has many features like you're talking about (macros to generate SQL and so on). It makes it pretty easy to build up a complex DAG of SQL queries and transformations.