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

What SQL Analysts Need to Know About Python (2016)

segment.com

Post reply on HN
Print view View original

61–70 of 115 posts

Re: What SQL Analysts Need to Know About Python (2016)

#61

Post by tomnipotent »

oarabbus_ wrote: post #7
drej wrote: post #6

I'm worried about the inverse - what Python data analysts should know about SQL. Because I've met tons of analysts who wouldn't be able to even run a basic select. I've seen tons of (often non-reproducible) code written in place of a simple SQL query. I really wish bootcamps and other learning platforms focused on SQL a bit more. (I am a Python data analyst who properly learned SQL only after several years in the ind…

Or worse, they use cursors. Cursors should never be used in SQL. Ever. That's my philosophy.

To my knowledge, you can't get data from an RDBMS without a cursor - kinda required to do even a simple SELECT. I'm guessing what you're referring to, is keeping a cursor open from a Python process that should have been closed after the results were brought into memory.

Re: What SQL Analysts Need to Know About Python (2016)

#62

Post by bshipp »

aldoushuxley001 wrote: post #55
bshipp wrote: post #25

Earlier quoted context omitted.

select_related caught me when I first started using Django. It's a sneaky one because the queries worked fine when I first wrote the program. Then I started populating the database and, over the course of months, the queries got slower and slower and slower. Eventually I was forced to pop open the hood and horrified to find this spaghetti bowl of nested, duplicate queries that took a fair bit of work to simplify and…

What was it about select_related that slowed your queries? What'd you do to fix them? Did you have to abandon select_related, or just tweak its parameters? I'm just building out a Django app now and using select_related, or rather prefetch_related, for retrieving tags (m2m relationship). Seems to work well so far, but I'm I'm sure I'll run into a similar thing of having to optimize all these queries soon.

Similar to what @overcast said: initially I didn't use select_related at all. Almost immediately I saw huge DB utilization with hundreds of thousands of tuples returned for (what I assumed) were pretty simple queries. I realized, as @overcast said, that it was looping instead of asking for it all at once, so I added indexes and appended "select_related" to almost every query. Then I figured it was fixed.

Once my database hit 100GB and a few hundred million rows I had no choice but to sit down and actually learn what each of my ORM commands was asking my database to do. Sometimes I removed a select_related. Sometimes I replaced it with prefetch_related. Sometimes I eliminated an entire filter operation or moved it elsewhere. A few times I injected a greatly simplified raw SQL query instead of relying on complex ORM generated SQL. In four instances I replaced expensive join operations with periodically rebuilt "materialized views" to reduce CPU usage and DB I/O. All was timed with django-debug-toolbar and/or pghero to minimize database impacts and network congestion.

So select_related was sneaky in the sense that I thought I had solved the problem very early on, when I had merely delayed it until much later. If your database always remains small you'll likely never encounter this issue.

The solution is to not fire and forget the application, but to install something like django-debug-toolbar and monitor what your program is doing as the database grows in size. But for heaven's sake, don't worry about that problem today. Get your app working so you can make money. Once it's done, however, remember that your ORM has put a thick collar on your new puppy, and as it grows you'll need to expand that collar or you'll slowly strangle your pet.

Re: What SQL Analysts Need to Know About Python (2016)

#63

Post by AceJohnny2 »

ci5er wrote: post #59
oarabbus_ wrote: post #7

Earlier quoted context omitted.

Or worse, they use cursors. Cursors should never be used in SQL. Ever. That's my philosophy.

Why? (I am not taking a contrary position by asking). I'm not really an RDBMS guy (but I can write a select query from scratch!), but IIRC, the Netscape Server API (where Javascript got it's start - not in the browser!), there was heavy use of and expectation in the NSAPI of cursors for tabular data table scrolling and the like. I don't recall if they were in the DB or the HTTPD server. But I do know that if you didn…

How's PCIe Gen4 treating you? :)

Re: What SQL Analysts Need to Know About Python (2016)

#64

Post by medecau »

starpilot wrote: post #37
goatinaboat wrote: post #27

Earlier quoted context omitted.

I’ve seen devs just run select * from table then filter it and sort it in their own code. Then they complain “the database is slow” when it’s spending all its time shipping gigabytes of data they don’t need to them!

Is anyone working on a translator for pandas dataframe syntax to SQL?

[Blaze](https://blaze.readthedocs.io/en/latest/what-blaze-isnt.html#...)

Re: What SQL Analysts Need to Know About Python (2016)

#65

Post by gwbas1c »

tstrimple wrote: post #24
sheepstrat wrote: post #3

A few years ago I joined a Rails shop, and one thing that always struck me was how many of the engineers didn't know SQL. Most of them had learned to code on Rails, and had always had SQL abstracted away via ActiveRecord. I know this is not the point of this article, but as data analyst/scientist roles continue to climb in popularity, I'm curious if there won't be a similar trend with Python.

Isn't that one of the selling points of Rails? Time to market is king. Optimize your SQL query performance after you've released and proven that it's an actual bottleneck. Why spend more time and money on an optimized product that might never see the light of day?

There's a difference between basic optimization, and needing to refactor most of your business logic because your assumptions about databases are boneheaded.

In general, it doesn't matter what you're doing, your basic design patterns need to fit around how a database works. If you don't know this, you'll hit scalability issues far too soon, and it'll take too long to fix them.

> Optimize your SQL query performance after you've released and proven that it's an actual bottleneck.

I've seen one project fail because the design patterns around using the ORM were incorrect. Then I joined another project where the bottleneck (from incorrect use of the ORM) was so bad the product couldn't scale beyond being a demo. It took 2 months to refactor, all because one of the programmers used an ORM incorrectly to save a few hours at the beginning.

Re: What SQL Analysts Need to Know About Python (2016)

#66

Post by ci5er »

AceJohnny2 wrote: post #63
ci5er wrote: post #59

Earlier quoted context omitted.

Why? (I am not taking a contrary position by asking). I'm not really an RDBMS guy (but I can write a select query from scratch!), but IIRC, the Netscape Server API (where Javascript got it's start - not in the browser!), there was heavy use of and expectation in the NSAPI of cursors for tabular data table scrolling and the like. I don't recall if they were in the DB or the HTTPD server. But I do know that if you didn…

How's PCIe Gen4 treating you? :)

Ha! I'm in more "not open" systems, but if there is zero noise, it's great! :-)

Re: What SQL Analysts Need to Know About Python (2016)

#67

Post by SJetKaran »

goatinaboat wrote: post #27
drej wrote: post #6

I'm worried about the inverse - what Python data analysts should know about SQL. Because I've met tons of analysts who wouldn't be able to even run a basic select. I've seen tons of (often non-reproducible) code written in place of a simple SQL query. I really wish bootcamps and other learning platforms focused on SQL a bit more. (I am a Python data analyst who properly learned SQL only after several years in the ind…

I’ve seen devs just run select * from table then filter it and sort it in their own code. Then they complain “the database is slow” when it’s spending all its time shipping gigabytes of data they don’t need to them!

From what I encountered, this is generally the case when someone is in the "analysis/reports" mode. Rather than get summary statistics on each column, find number of nulls, etc by writing a sql query, they instead get the data into the Python/R instance, and use general purpose functions, utilities, etc. "Programmers are expensive" statement probably applies here as well. I'm not trying to be defensive here, just saying that this might be one reason.

Re: What SQL Analysts Need to Know About Python (2016)

#68

Post by danzig13 »

mongol wrote: post #33
danzig13 wrote: post #29

Earlier quoted context omitted.

PowerQuery/Get&Transform makes it go an even longer way.

Yes that is next on my todo list to learn!

I have eventually started learning Python/Pandas but PowerQuery plus having good SQL knowledge delayed it quite a bit.

Re: What SQL Analysts Need to Know About Python (2016)

#69

Post by faizshah »

starpilot wrote: post #37
goatinaboat wrote: post #27

Earlier quoted context omitted.

I’ve seen devs just run select * from table then filter it and sort it in their own code. Then they complain “the database is slow” when it’s spending all its time shipping gigabytes of data they don’t need to them!

Is anyone working on a translator for pandas dataframe syntax to SQL?

Ibis: https://docs.ibis-project.org/

https://docs.ibis-project.org/notebooks/tutorial/2-Basics-Ag...

Re: What SQL Analysts Need to Know About Python (2016)

#70

Post by danso »

Tangential question: I'm curious how many people (here on HN, or in general) learned SQL before they learned more traditional programming (e.g. Python, Java, C)? I learned traditional programming (through college) and only stumbled upon SQL years later (someone left a "How to use Microsft Access" book around at work).

I absolutely love SQL, and am in the middle of writing a book on how to use just SQLite for large, complicated data work. My target audience is programmers, but also non-programmers, because I think SQL is vastly easier to learn than something like Python. The tradeoff of course is that SQL is much more limited a framework, but it's more than enough to do data work (all the stuff that isn't visualization or complex stats).

When I lived around Palo Alto, I met a few recent grads who didn't study STEM in college, but ended up working for tech firms in content and analytics roles. They'd know Excel and Tableau, but through co-workers, would pick up SQL to make their data work easier. But they hadn't yet had learned general programming, or tried Python/Javascript.

But I can't imagine many other professional or academic career paths in which someone who ends up learning and using both Python and SQL started out with SQL, then learned Python (i.e. general programming). Would would they have done with SQL in school, or as a hobby?

Post reply on HN