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

Re: SQL: One of the most valuable skills

#151

Post by neokantian » Wed, Feb 13, 2019, 5:42 AM UTC

kccqzy wrote: post #91

neokantian wrote: post #39

> Learning SQL once will allow you to re-use it heavily across your career span without having to re-learn. 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 -- i…

Relational algebra is a much closer foundation for SQL than ZFC (or plain ZF, because for finite sets the difference doesn't matter). You can say SQL has ZFC as its foundation but that's not very useful. In the same way you can say ZFC is the foundation of everything in mathematics. SQL doesn't even use anything beyond naïve set theory because we are dealing with finite sets, let alone the full power of ZF.

> In the same way you can say ZFC is the foundation of everything in mathematics.

Yes, technically, that is indeed true of every Turing-complete axiomatic theory.

You could conceivably even use the SKI combinator calculus as the foundation of everything in mathematics. Still, something tells me that a database query language in widespread use, based on the SKI combinator calculus is rather unlikely to emerge (certainly not at this point).

Hence, since ZFC is one of the many alternative, equivalent foundations of everything in mathematics, picking ZFC still amounts to making a choice, no matter how small ...

> SQL doesn't even use anything beyond naïve set theory ...

Agreed. However, we also have to consider what situation we came from. David Childs objected to the use of custom, application-specific code, data types and functions, because these custom structures could (more easily) be represented by one, general abstraction. Given the fact that back then, people were not even using naive set theory, his considerations were clearly a step forward in the right direction.

Re: SQL: One of the most valuable skills

#152

Post by kumarvvr » Wed, Feb 13, 2019, 5:43 AM UTC

daveslash wrote: post #145

kumarvvr wrote: post #76

SQL is a mind bender for me. I do a lot of work on Data, use python and pandas to do a lot of data magic, but the problem with me is my mind is too procedural in thinking. - 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…

Two Things. (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 roo…

Thank you for the Stanford class reference. Will check it out.

Re: SQL: One of the most valuable skills

#153

Post by api » Wed, Feb 13, 2019, 5:50 AM 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…

SQL is so long lasting because its math, sort of. It's a language for expressing data relationships in a very systematic logical and set theoretic way, not a random ad hoc hack.

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.

Re: SQL: One of the most valuable skills

#154

Post by tartoran » Wed, Feb 13, 2019, 5:52 AM UTC

kumarvvr wrote: post #118

kccqzy wrote: post #85

Earlier quoted context omitted.

Try some functional programming. It will liberate your brain from thinking operationally. You will soon no longer think in terms of steps, loops or anything like that. You will get used to thinking about data transformations, which is what SQL is good at.

Any books that teach function programming with python?

https://docs.python.org/2/howto/functional.html

Re: SQL: One of the most valuable skills

#155

Post by dwd » Wed, Feb 13, 2019, 5:58 AM UTC

kumarvvr wrote: post #149

dwd wrote: post #140

Earlier quoted context omitted.

If raw speed is not an issue you can always fall back on temp tables or a cursor for those cases where you might have extracted an initial dataset to then iterate over. EDIT: A CTE (Common Table Expression) might be what you're thinking you need. You can do some fun recursive queries with them.

> recursive queries It's like all my fears concentrated, like the power of a thousand suns, onto a single sentence.

I should have mentioned cursors are conceptually a C pointer to really put the fear into people.

Re: SQL: One of the most valuable skills

#156

Post by ilikehurdles » Wed, Feb 13, 2019, 5:58 AM UTC

simplify wrote: post #137

wvenable wrote: post #132

Earlier quoted context omitted.

I don't think it's non-programmers that keep SQL around; it's that there hasn't been a replacement for SQL that is better enough to replace it. I think C suffers from the same problem.

I find cypher to be a superior language to SQL. It expresses relationships more succinctly and intuitively, and has better support for aggregations.

I also find datalog superior.

Re: SQL: One of the most valuable skills

#157

Post by beefield » Wed, Feb 13, 2019, 5:59 AM UTC

JimmyAustin wrote: post #49

My first job out of university was on an analytics team at a consulting firm (big enough that you know them) that used MS SQL Server for absolutely everything. 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. Ev…

> My cries to use git were unheeded (would have required upskilling everyone on the team).

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?

Re: SQL: One of the most valuable skills

#158

Post by diggernet » Wed, Feb 13, 2019, 6:01 AM UTC

In University, I needed one more CS elective course, and the ONLY class available that fit my schedule was SQL. I had no interest in SQL, wanted nothing to do with it, and only took the class under duress. Even to the point of admitting as much to the prof in a casual conversation (he was a good guy, easy to talk to).

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.

Re: SQL: One of the most valuable skills

#159

Post by manigandham » Wed, Feb 13, 2019, 6:04 AM UTC

beefield wrote: post #157

JimmyAustin wrote: post #49

My first job out of university was on an analytics team at a consulting firm (big enough that you know them) that used MS SQL Server for absolutely everything. 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. Ev…

> My cries to use git were unheeded (would have required upskilling everyone on the team). 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?

It's mostly about using git to ensure a consistent snapshot as changes are made rather than updating one procedure at a time.

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.

Re: SQL: One of the most valuable skills

#160

Post by hardwaresofton » Wed, Feb 13, 2019, 6:05 AM UTC

brazzledazzle wrote: post #120

hardwaresofton wrote: post #56

SQL is the most powerful query language ever invented and widely implemented over relation databases IMO (qualified heavily for lurking RDF zealots). Every time you see someone start to invent their own query language, I almost always mark is as folly (similar to when people invent their own configuration languages). Prometheus and GraphQL stand out as recent examples. DBs like Kafka who recognize this and instead of…

What don’t you like about GraphQL? I haven’t used it enough to form a strong opinion but it seems ok so far.

I would 1000% not take my opinion as gospel on GraphQL -- I know how it works, but I have not written an implementation myself, and have not even spun up Apollo. With that heavy grain of salt, here's how I feel about GraphQL:

- 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

[2]: https://github.com/hasura/graphql-engine/issues/1598