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

181–190 of 390 posts

Re: SQL: One of the most valuable skills

#181

Post by max76 »

DrPhish wrote: post #172
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…

I had a similar role where I was writing boring LOB apps in a very gross language, but since we were using an SQL backend for all the data, I instead challenged myself to using bare templates in the actual programming language and writing all the extraction, transforms and logic into SQL selects and (when unavoidable) programmatic bits. I also learned a crapload about obscure SQL since I would go to extreme lengths t…

I never personally wrote meta-SQL but it was used at that office and I read it. I didn't see beauty. When I read the code and understood it I felt terror.

Re: SQL: One of the most valuable skills

#182

Post by bayesian_horse »

I know SQL. But I tend to download data from SQL databases and then use Python and Pandas to deal with it, even though most things could be accomplished in SQL more efficiently.

Maybe Python is more convenient, cognitively? Pandas and SQL are very similar in concept, though.

Re: SQL: One of the most valuable skills

#183

Post by komali2 »

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…

Have you written more things I can read?

Re: SQL: One of the most valuable skills

#184

Post by EGreg »

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…

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 the “pointer” half of the way there. And then you do a bunch of O(log N) lookups.

Also, data locality and caching would be better in graph databases. Especially when you have stuff distributed around the world, you aren’t going to want to do relational index lookups. You shard — in the limit you have a graph database. Why not just use one from the start?

So it seems to me that something like Neo4J and Cypher would have taken off and beat SQL. Why is it hardly heard of, and NoSQL is instead relegated to documents without schemas?

Re: SQL: One of the most valuable skills

#185

Post by AmericanChopper »

dwd wrote: post #70
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…

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.

Re: SQL: One of the most valuable skills

#186

Post by 3pt14159 »

nostrademons wrote: post #115
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…

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

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 some of it (especially string manipulation, which is brutal) but I think you look at it like most programmers. SQL engages more of a simulative mindset—one popular with analysts—than the acquisitive mindset that most software developers outside of data science employ.

Re: SQL: One of the most valuable skills

#187

Post by tlarkworthy »

nostrademons wrote: post #115
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…

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

I think SQL is entrenched by network effects. It has a unique conceptual paradigm, which is why it is hard to learn. But any replacement is also likely to be conceptually strange, but noone is gonna put in the time in to learn something weird unless it has the adoption of SQL. This we are stuck on a local mini.a with SQL. (A pretty good one though)

Re: SQL: One of the most valuable skills

#188

Post by rtpg »

EGreg wrote: post #184
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…

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…

In practice enterprise applications do a lot of relational operations.

Graph DBs might be valuable for a lot of problems, and it does feel like something like Neo4J would make a lot of sense for stuff like social networks, but for business records stuff isn't really that spread out

Re: SQL: One of the most valuable skills

#189

Post by indigo945 »

EGreg wrote: post #184
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…

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…

A hash index is O(1) no matter what language it's called from. The reason most people don't use hash indices very often is that they do not allow you to do a lot of useful things that you often end up wanting to do, such as retrieving all values within a given range.

Re: SQL: One of the most valuable skills

#190

Post by chubot »

max76 wrote: post #180
chubot wrote: post #105

Earlier quoted context omitted.

Once your SQL gets into 500-1000 lines, and hours of runtime, I would suggest using data frames instead (in R or Python). I wrote this post to introduce the idea: What Is a Data Frame? (In Python, R, and SQL) https://www.oilshell.org/blog/2018/11/30.html It's often useful to treat SQL as an extraction/filtering language, and then use R or Pandas as a computation/reporting language. I think of it as separating I/O and…

If catting 10gb of files takes 5 minutes than catting 500gb of files takes 250 minutes. This is, of course, an over estimation of how long it takes to look at data on a disk. It's also the most trivial thing you can do with data, read it once. I think we have a misunderstanding about the scale of data. EDIT: Reading your post you mention that dataframes stores data in memory. Working with data in ram would provide a…

I'm not denying that you could have a query that takes hours and is within 10x the speed it should be, just saying that it's very common to have inefficient SQL queries for reporting. I would say it's almost the "default" state unless you do a bit of work.

Some more detail here: https://news.ycombinator.com/item?id=19150971

There are many interesting queries that don't touch all the data in the database. The 'cat' thing was basically assuming the worst case, e.g. as a sanity check.

Post reply on HN