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

Re: SQL: One of the most valuable skills

#341

Post by SilasX » Wed, Feb 13, 2019, 4:41 PM UTC

saagarjha wrote: post #209

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…

> 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. That's basically how most tools work outside of software development. When you use…

Meh, not quite. We've gotten so accustomed to phrasing our requests in search-engine-friendly terms that we don't realize we're doing it (e.g. cutting out all the small words).

Re: SQL: One of the most valuable skills

#342

Post by saint_abroad » Wed, Feb 13, 2019, 4:45 PM UTC

dsego wrote: post #250

Reading the comments here is like someone with years of experience with jQuery saying how jQuery is simple and powerful and nothing will replace it. SQL is to relational databases what jQuery is to the DOM, only shittier (maybe like mootools) and refuses to die, probably because all these SQL experts aren't really good programmers. Reading some compare SQL to 70s style procedural code tells me they haven't moved on f…

> Reading some compare SQL to 70s style procedural code tells me they haven't moved on from expert beginner territory.

SQL is a declarative set-oriented language. Read/writing SQL as a procedural language will cause a heap of trouble.

Re: SQL: One of the most valuable skills

#343

Post by svsucculents » Wed, Feb 13, 2019, 5:02 PM 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…

This is what glue languages do with data. Not what SQL does with data. Why ever would you want it to?

Re: SQL: One of the most valuable skills

#344

Post by grigjd3 » Wed, Feb 13, 2019, 5:20 PM UTC

_ptgt wrote: post #331

cirgue wrote: post #317

Earlier quoted context omitted.

I use python/Pandas every day for data analysis and the like, and I would never dream of not writing most of the aggregation and filtering logic in SQL. If you're working with large datasets, there is absolutely no reason to pull unnecessary data into memory.

Depending on the use case, I'd argue Spark can be a better choice for aggregating/filtering than SQL. SQL is great for simple queries, but once my queries start getting into the many hundreds of lines than I start to miss all of the complexity management features of a true programming language.

There are a few things, but not much that I have had a better experience in Spark with as compared to using something like apache pig with UDFs. Now this part might be a matter of how things are set up where I work, but I find that working with Tez for process management and debugging to be far easier than working with the process management built into Spark.

EDIT: when you read process management above, perhaps it's better to think task management.

Re: SQL: One of the most valuable skills

#345

Post by scatterwilds » Wed, Feb 13, 2019, 6:24 PM UTC

"like the other mechanistic software theories, the relational database model is a pseudoscience; that it is worthless as a programming concept; and that the relational systems became practical only after annulling their relational features, and after reinstating – in a more complicated form, and under new names – the traditional data management principles." --Software and Mind, pg. 676

Re: SQL: One of the most valuable skills

#346

Post by dorgo » Wed, Feb 13, 2019, 6:38 PM UTC

thom wrote: post #325

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…

This is a very real problem I bang my head against regularly. There just seems no way to achieve all three of readability, maintainability and performance in a large enough SQL codebase. You can piece together views right up until the moment the query planner forgets to push where clauses down. You can wrap a query in a function to guarantee the where clause is evaluated then and there, but now you have to maintain t…

>save the query plan forever

is this even possible? Afaik, the query plan can depend on the data distribution and needs to be re-optimized when the data distribution changes.

Re: SQL: One of the most valuable skills

#347

Post by cr0sh » Wed, Feb 13, 2019, 6:40 PM UTC

radicalbyte wrote: post #277

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 is amazing IF you understand it. You need to think in sets of things. It's like functional programming paradigms or recursion; once you really truly "get it" you start to feel like a Jedi master. 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…

I was fortunate that my early SE career experience involved a lot of SQL (and my first experience with a SQL-like query language happened under PICK).

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.

Re: SQL: One of the most valuable skills

#348

Post by ASalazarMX » Wed, Feb 13, 2019, 6:43 PM UTC

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…

SQLite is a testament to the power of SQL, it's a little wonder itself, and it's likely the most installed database globally.

Re: SQL: One of the most valuable skills

#349

Post by Tallain » Wed, Feb 13, 2019, 6:43 PM UTC

rspeele wrote: post #339

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 am the author of a small parser/typechecker for a subset[0] of SQL, roughly matching that supported by SQLite, and I agree with you COMPLETELY. What follows is my rant directed at those who don't. It is an ugly, verbose language. You can be very familiar with thinking in sets, and still not like SQL. It's what we've got, and the SQL databases available are very, very good products. But I do wish a cleaner language…

You make some good points about SQL's shortcomings but some of the claims you make are infactual or seem intentionally misleading. Since you mentioned SQL Server, I have to chime in with some corrections to your statements

While you can't declare new variables within a SELECT statement, you can modify them with functions:

  DECLARE @foo INT;

  SELECT
      @foo = dbo.ComputeSomething(...),
      @foo + @bar AS Val1
  FROM
      ...

Additionally, SQL Server does have a boolean type by another name: bit. It is 1/0/NULL and you can convert the string values TRUE and FALSE into their respective numerical values.

To modify your example to use proper SQL:

  DECLARE @x BIT;
  SELECT @x = 'TRUE' WHERE 1  2

Or, to use a more real-world example, you'd probably use a CASE statement:

  SELECT
    @x = CASE WHEN 1  2 THEN 1 ELSE 0 END

Yes, it's verbose. But it's possible. (SQL's slogan, probably, and understandably the root of a lot of your remarks.)

Finally, your GROUP BY / HAVING / WHERE example lacks an important distinction. HAVING WHERE. HAVING is specifically meant to deal with aggregate functions, while WHERE is a simple filter. You wouldn't use "HAVING PostId = @foo" any more than you might use "WHERE COUNT(*) > @bar". This is why it comes after the GROUP BY clause -- which you should think of as a "key" for the data that's being aggregated.

Look, SQL is not perfect or near perfect. As with any language, it could use improvements. But to criticize a language based on ignorance, misleading statements, or use cases that are not meant for the language is fairly dishonest, in my opinion.

Re: SQL: One of the most valuable skills

#350

Post by da_chicken » Wed, Feb 13, 2019, 6:43 PM UTC

zepolen wrote: post #337

da_chicken wrote: post #332

Earlier quoted context omitted.

I don't disagree with him (though I don't necessarily agree as strongly with respect to triggers), but the counter argument is that it's extremely easy to put up a web service that offers an API and interacts with JSON. Now all the complicated bits are in the API service, and the multiple apps don't need to know anything at all about the underlying data store. The counter to that is that it can be very difficult to g…

Your counter counter argument on not being as flexible as SQL is spot on. A DAL that sits in front of storage mechanisms is not a bad idea though. I've found moderate success in doing the reverse and using communication channels from SQL => services either via NOTIFY or queues. ie. Dispatching an event that signals a non SQL service to do an action. eg saving a file to S3, then updating the database when the action i…

> WRT JSON, please for the love of god no.

I don't like it either, but for most scenarios that this sort of thing works for a RESTful API or microservice or equivalent (or whatever you want to call it these days since I think the expiration date on those terms has elapsed) and not all data can be meaningfully coded without a structure like JSON or XML. Or, rather, you can, but, you're reinventing the wheel just like you would with an API trying to be as flexible as SQL. You may run into various system limits on URL length, too.

Events and queues are great, but not all requests work that way. If you're USPS and you're providing an address and ZIP code resolver, you've got different requirements than getting data from one system to cascade across a series of systems with a dozen different asynchronous widgets. You'd have a burden of using a format that your customers would prefer, too.