Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQL: One of the most valuable skills

craigkerstiens.com

Post reply on HN
Print view View original

241–250 of 390 posts

Re: SQL: One of the most valuable skills

#241

Post by bonesss »

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…

SQL is wildly powerful and important, but it's also got clear deficiencies for data traversal and manipulation.

I see it on a spectrum between declerative and imperative data structures, and where I think most people go wrong with it is trying to create a monolithic solution to a broad spectrum of problems. I think you need a graduated approach where each data layer is simplifying and satisfying the next, so you're using Tables, Procs, Views, and in-memory constructs in concert. The database is a powerful tool, and SQL is just part of that bigger puzzle :)

Re: SQL: One of the most valuable skills

#242

Post by Shacklz »

AmericanChopper wrote: post #185
dwd wrote: post #70

Earlier quoted context omitted.

You can do some insanely complicated stuff in a stored procedure. A favourite was versioning and updating a set of data across multiple tables as an atomic transaction. Also MS SQL let you throw a data object (say XML) at a stored procedure, convert it to a table structure with some XPath (another useful black art like regex) and use that as the input to a single INSERT.

>You can do some insanely complicated stuff in a stored procedure. You’re giving me flashbacks to stored procs that directly invoke java methods, and batch scrips that load client supplied data from and FTP server into external tables.

> You’re giving me flashbacks to stored procs that directly invoke java methods

A legacy application I once worked at used this extensively, and good grief it gave me nightmares.

How anyone ever thought this is a good idea to use is beyond me; that stuff is not maintainable at all.

Re: SQL: One of the most valuable skills

#243

Post by bonesss »

ako wrote: post #232
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…

Graph databases are great for retrieving existing data with associated data. The power of SQL comes from the fact that you can easily create new information out of the data: create new sets, group by certain features, aggregates on certain features. It's a lot more powerful than just store and retrieve.

People forget that SQL isn't just Query (DQL), it's also definition (DDL), manipulation (DML), control (DCL), and transaction control (TCL), language [0].

Checkout that platform that a full-blown Oracle license can provide to your DBA... It's definitely more than just CRUD.

-

[0] https://www.oreilly.com/library/view/discovering-sql-a/97811...

Re: SQL: One of the most valuable skills

#244

Post by lmm »

ako wrote: post #237
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…

There's no reason you can't store your sql/tsql/plsql in version control. We were doing this 20+ years ago, all code was in csv (we upgraded from rcs to csv), and we had a productized distributed scheduling system that would deploy all the sql scripts every night on a number of oracle databases running from aix, to solaris, to vms, to hpux, to irix, and later linux and windows NT. Similar like you would now use jenki…

There's no reason you can't - but there's limited tooling support and limited worker mindshare for that kind of approach.

Re: SQL: One of the most valuable skills

#246

Post by bonesss »

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?

You can version your scripts & DB as they get pushed with a common tracking number. Personally I think it's best to establish a workflow where manual changes to the DB would be pointless and likely to result in them being overwritten.

Outside of commercial tools dedicated to the purpose: you can query the DB for the content of the procedures/tables and compare them to a given set of scripts, or the most recent expected version. Auto-generated ORM models can be used to validate table/view composition for a given DB/App version, as well. Having these capacities baked into the versioning and upgrade process can do a lot over time to correct deviating schemas and train developers away from meddling with DBs outside the normal update procedure :)

Re: SQL: One of the most valuable skills

#247

Post by GolDDranks »

AlisdairO wrote: post #64
GolDDranks wrote: post #19

I consider myself functional, but not proficient in SQL. Is there a reliable and easy way to assess one's skills in SQL? I feel that most of the new things I learn about SQL these days are database specific. I wonder whether I'm missing out on something, or do I already have the "core" SQL knowledge down, and everything else is special cases?

(self plug) take a look at pgexercises.com . It's Postgres-focused but mostly cross-platform, and I think it covers most of the important stuff.

Thanks, I'll check it out!

Re: SQL: One of the most valuable skills

#248

Post by GolDDranks »

mongol wrote: post #53
GolDDranks wrote: post #19

I consider myself functional, but not proficient in SQL. Is there a reliable and easy way to assess one's skills in SQL? I feel that most of the new things I learn about SQL these days are database specific. I wonder whether I'm missing out on something, or do I already have the "core" SQL knowledge down, and everything else is special cases?

I recommend you look at one of Markus Winand's presentations, such as this one : https://youtu.be/xEDZQtAHpX8 I felt it summarized well the modern foundation of SQL databases.

Thanks, I'll be sure to watch it!

Re: SQL: One of the most valuable skills

#249

Post by bonesss »

chubot wrote: post #178
busterarm wrote: post #144

Earlier quoted context omitted.

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, wh…

ETL pipelines that hop between different kinds of storage/computing platforms to exploit local maxima, like you're pointing out with R and SQL working in concert, is pretty common in companies working their way up to BigData and academia.

From the Enterprise side I think too many developers have an unfounded expectations around data storage technology. There's this unchallenged belief that monolithic datastorage that will solve thier problems across the entire time/storage/complexity spectrum. By bringing multiple tools to bear, instead, you end up with more purpose built storage but far less domain impedence.

Slapping a denormalized NoSQL front-end for webscale onto a legacy RDBMS can be a cheap win/win to maximize the capabilities of both. SQL + R is oodles better than R or SQL in isolation.

Re: SQL: One of the most valuable skills

#250

Post by dsego »

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 from expert beginner territory.
Post reply on HN