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

Re: SQL: One of the most valuable skills

#221

Post by groestl » Wed, Feb 13, 2019, 8:55 AM UTC

bogidon wrote: post #205

nostrademons wrote: post #115

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

It’s not surprising librarians use SQL. Library science is all about organizing (informational) data!

I mean, a library is basically a B+-tree ;)

Re: SQL: One of the most valuable skills

#222

Post by nostrademons » Wed, Feb 13, 2019, 8:56 AM UTC

barrkel wrote: post #193

EGreg wrote: post #184

Earlier quoted context omitted.

Can I ask a simple question about efficiency? It seems to me that graph databases are far more efficient than relational ones for most tasks. That’s because all lookups are O(1) instead of O(log N). That adds up. Also, copying a subgraph is far easier, and so is joining. Think about it, when you shard you are essentially approaching graph databases because your hash or range by which you find your shard is basically…

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.

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.

Re: SQL: One of the most valuable skills

#223

Post by nablaone » Wed, Feb 13, 2019, 9:00 AM UTC

tigerlily wrote: post #3

> Because so few actually know SQL well you can seem more elite than you actually are. Thank you Craig, I'm convinced. Anyone know where best to begin learning SQL?

https://academy.vertabelo.com/

Re: SQL: One of the most valuable skills

#224

Post by forrestthewoods » Wed, Feb 13, 2019, 9:03 AM UTC

I don’t know SQL. What’s the best resource to learn?

Re: SQL: One of the most valuable skills

#225

Post by tuyiown » Wed, Feb 13, 2019, 9:08 AM UTC

garyclarke27 wrote: post #214

nostrademons wrote: post #115

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

SQL certainly does not suck as a language, unfortunately this is a very common perception, almost a (false) consensus within the developer community. I changed careers from Finance - I was a ACA (akin to CPA) , wizard at Excel VBA etc - to software development, after 10 years learning, I’m now finally also proficient with SQL. I have also learnt other langauges, but SQL is by far my favourite, Yes it has some mistake…

You're confusing the language and the databases engine. I don't think that seasoned developpers think that relational databases sucks per se, since most of their constraints are technically justified, but don't like SQL as a language because it's grammar is completely awful, it's inconsistent (toward itself and databases engines), filled with specific perks and clumsy.

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.

Re: SQL: One of the most valuable skills

#226

Post by ako » Wed, Feb 13, 2019, 9:09 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…

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.

Re: SQL: One of the most valuable skills

#227

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

dhbradshaw wrote: post #17

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

Are you controlling all the writes? If so I’d go a phased migration .. easier to pull off and much lower risk than a big-bang one-off approach.

Re: SQL: One of the most valuable skills

#228

Post by xpil » Wed, Feb 13, 2019, 9:12 AM UTC

ROW_NUMBER(), LAG() and MERGE are the three most powerful operators I use in SQL that are out of the average Joe's knowledge. They are simple to learn yet extremely useful.

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)

Re: SQL: One of the most valuable skills

#229

Post by ako » Wed, Feb 13, 2019, 9:16 AM UTC

tuyiown wrote: post #225

garyclarke27 wrote: post #214

Earlier quoted context omitted.

SQL certainly does not suck as a language, unfortunately this is a very common perception, almost a (false) consensus within the developer community. I changed careers from Finance - I was a ACA (akin to CPA) , wizard at Excel VBA etc - to software development, after 10 years learning, I’m now finally also proficient with SQL. I have also learnt other langauges, but SQL is by far my favourite, Yes it has some mistake…

You're confusing the language and the databases engine. I don't think that seasoned developpers think that relational databases sucks per se, since most of their constraints are technically justified, but don't like SQL as a language because it's grammar is completely awful, it's inconsistent (toward itself and databases engines), filled with specific perks and clumsy. To illustrate on `select` queries, you start lis…

Starting with the end in mind (hi covey) is actually not such a bad idea. First state what result you want the query to produce,and then start describing where that data should come from.

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.

Re: SQL: One of the most valuable skills

#230

Post by garyclarke27 » Wed, Feb 13, 2019, 9:18 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…

Views - Create a general top level view, then build more specific views on views using more filters, to go down, ie more granular - then just join these with yet more views to combine, or aggregate to go back up. How is that not composable? If you hit performance issues, they are easily solved by using a few materialized views. Also CTEs and User defined Functions (I use pure SQL functions but in Postgres you can easily use Python or Javascript instead)