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

361–370 of 390 posts

Re: SQL: One of the most valuable skills

#361

Post by ako »

OliverJones wrote: post #284
ako wrote: post #237

Earlier quoted context omitted.

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…

umm. That's cvs. csv is something else, and it matters if you're a datashoveler.

Oops, you're right. it's been a long time. Csv has aged better than cvs.

Re: SQL: One of the most valuable skills

#362

Post by Beskz »

rjbwork wrote: post #94
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…

(please excuse my typing. i have one hand to use ATM) yup. we bet super hard on sql too. Its fantastic if you know what you're doing. We ca import arbitrary data and expose it as normal tables to our customers for analysis/transform/export along with having a silod access controlled place for them to see just their data. sql is a great technology, and is extremely flexible. I love it. If you're looking to learn it an…

Do you mean this one from Stanford?

https://lagunita.stanford.edu/courses/Engineering/db/2014_1/...

Re: SQL: One of the most valuable skills

#363

Post by jessaustin »

Beskz wrote: post #362
rjbwork wrote: post #94

Earlier quoted context omitted.

(please excuse my typing. i have one hand to use ATM) yup. we bet super hard on sql too. Its fantastic if you know what you're doing. We ca import arbitrary data and expose it as normal tables to our customers for analysis/transform/export along with having a silod access controlled place for them to see just their data. sql is a great technology, and is extremely flexible. I love it. If you're looking to learn it an…

Do you mean this one from Stanford? https://lagunita.stanford.edu/courses/Engineering/db/2014_1/...

Presumably GP took this as a MOOC... some people are pretty blitzed in college but it would be hard to mistake California for Massachusetts!

Re: SQL: One of the most valuable skills

#364

Post by jessaustin »

sk5t wrote: post #96
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…

The only question that remains is--Accenture or KPMG?

It could have been Deloitte, or as I convinced most of the contractors on several projects to pronounce it, "Delouche".

Re: SQL: One of the most valuable skills

#365

Post by magicalhippo »

jbverschoor wrote: post #326
magicalhippo wrote: post #238

Earlier quoted context omitted.

At least in the DB we're using, Sybase SQLAnywhere, materialized views comes with a hefty price tag. They must be dropped and recreated every time you touch any of the base tables, like adding a column, which in turn requires any indexes on the materialized views to be recreated. For a few of our customers, that meant that a 15 minute DB change (adding a column) turned into a several hour DB change (rebuilding materi…

What's the difference between that and a non sql approach? Writing a program that makes a temporary table somewhere, and if there's a mistake you have to start all over again?

Well, for immediate refresh materialized views, how do you keep your non-sql solution up to date?

For manual refresh, I agree, either non-sql or just plain temp tables is a decent alternative. In our case the views do a lot of joins and subqueries, so we've mostly used temp tables.

Re: SQL: One of the most valuable skills

#366

Post by paulryanrogers »

sly010 wrote: post #299
zepolen wrote: post #219

Earlier quoted context omitted.

True, if you modulo all the features like views, stored procedures, functions, foreign keys, triggers there is no reusability in SQL... On the serious side: It's not a bad idea to contain critical business logic in the database. It's shared by any app using the database. Foreign keys esp link and let you cascade changes with no extra code. Less overall code. Higher guarantees. Everyone agrees it's a good idea to use…

Not sure why are you being downvoted, I would like to at least hear the counter argument.

Not a down voter, but I imagine it could be because triggers are associated with write amplification and visibility concerns. They have their place, just like stored procedures.

Re: SQL: One of the most valuable skills

#367

Post by biggio »

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…

You need to think in bags of things

Re: SQL: One of the most valuable skills

#368

Post by CRConrad »

justinclift wrote: post #65
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…

Any recommendations of a place for sharing "extremely advanced" SQL skills? Asking from wanting to make use of such a place, and haven't seen anything like it. So, probably need to bootstrap one instead (etc).

Are you asking for “sharing skills” as in something like StackOverflow, but for SQL?

I would think there already is a Stack Exchange for SQL, possibly several (for different RDBMSes/ dialects); go have a look there, if this is what you meant.

Re: SQL: One of the most valuable skills

#369

Post by CRConrad »

eleclady wrote: post #126
conception wrote: post #82

Earlier quoted context omitted.

I think what your are doing is fine. SQL is strongest at answering questions not processing data. I think metabase has the right approach: What question do you want to ask your data? If you want to process and transform your data, I think your tools you are using are great for that.

Not trying to play word games, but what is the difference between answering a question and processing data? Aren't they effectively the same? Using another tool for processing data often results in recreating SQL mechanics at application level. E.g. select this data, retrieve it, loop and if this, then set that, etc. SQL does it way better, guaranteed. Of course that's often required for technical reasons (scalabilit…

«what is the difference between answering a question and processing data? Aren't they effectively the same?»

I think it influences the mindset of the developer. As you say, “retrieve ... if this, then ... loop”. If you're in a “data processing” mindset, then you'll think of a problem like “Get the total number of car widgets in the warehouse” as fetch a widget row; if it's of type car, add number to total; loop until you've processed every row; there you have your total. If, OTOH, you're in an “asking questions” mindset, you'll go: What was the question again, exactly? Oh yes, get the sum of the number for all the widgets which are of type car widgets. Which is almost exactly the same as SELECT SUM(NUMBER) FROM WIDGET WHERE TYPE = 'CAR';.

Processing data is when you do it (in code); answering questions is when the RDBMS (i.e, its code) does it for you. :-)

(At least that's what I think the difference is _in terms of vvkumar's original question._)

Re: SQL: One of the most valuable skills

#370

Post by grigjd3 »

cr0sh wrote: post #351
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.

I'm not sure what it is today - I would hope the same mindset applies, but maybe not - but back when I was using SQL, the idea was to let the database engine do everything it could with the data, before sending the results over the pipe. That is, minimize the network bandwidth by putting the work on the DB engine. This of course necessitated creating and understanding proper SQL query building practices. It was real…

The hardware progress since the 486 era, along with massive parallelization, has only reinforced this approach.
Post reply on HN