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

31–40 of 115 posts

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

#31

Post by bshipp »

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.

It all depends on the database and the API in use. When handling sqlite with Python I often use Roger Binns' APSW. (https://rogerbinns.github.io/apsw/cursor.html#cursors)

With an in-memory database or running it on an NVMe drive you can get some ridiculous performance out of Sqlite using APSW cursors.

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

#32

Post by bshipp »

One thing that hasn't been mentioned yet, that I found when I started using Python and SQL together, is the importance of well-considered indices. ORM's aren't always too clever when they generate an index, and often (for repeated queries) you can dramatically cut down the processing time by thoughtfully generating an index.

If I know I'm going to be asking the database a certain question a bunch of times I'll even generate a temporary index to speed up my analysis, and then delete it when I'm done. No sense running a SQL query for an hour if I can cut it down to 5 minutes with an index on the target column.

EDIT: I know this only applies to PostgreSQL, and there are numerous alternatives, but a big shout-out to pghero which helped me identify a bunch of duplicate and missing indexes in one of my databases, saving both time and hard drive space. Incidentally, it also sent me down the road to learn (but definitely not master) index optimization. https://github.com/ankane/pghero

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

#33

Post by mongol »

danzig13 wrote: post #29
mongol wrote: post #14

Python + SQL seems like a good match for many analysis problems, but Excel + SQL is not bad either. I like the ability to combine complex SQL views or SQL functions with Excel pivot functionality, querying the database directly from Excel

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

Yes that is next on my todo list to learn!

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

#35

Post by teej »

bshipp wrote: post #32

One thing that hasn't been mentioned yet, that I found when I started using Python and SQL together, is the importance of well-considered indices. ORM's aren't always too clever when they generate an index, and often (for repeated queries) you can dramatically cut down the processing time by thoughtfully generating an index. If I know I'm going to be asking the database a certain question a bunch of times I'll even g…

The move to columnar databases for BI and data science has thankfully eliminated a lot of this type of performance cruft. There are different issues now, but needing to add indexes just to run a new type of query is something I haven’t thought about in awhile.

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

#37

Post by starpilot »

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!

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

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

#38

Post by overcast »

hombre_fatal wrote: post #30
overcast wrote: post #20

Earlier quoted context omitted.

When you have millions or billions of rows, SQL calls absolutely become a huge bottleneck. I deal with this all the time from shortsighted developers at the office in other ORM environments. Most recent being a SELECT...NOT IN('a','b','c'), causing an INDEX SCAN on 200 million rows, and then complaining it takes 6 minutes to run. Look at Django's select_related. It's one of those if you don't understand what's happen…

I like having some sort of db:seed task during early development that seeds 1MM+ rows in all of your tables to help you experience performance issues as they're created. It's even more important if your production application has millions of rows. Too easy to create a system that runs perfectly on 10 rows but will crash your production server as soon as you deploy it. Forgetting to create an index on the FKs is a cla…

Yes of course, but let's be realistic, would the people doing this type of clueless work, bother to seed a database? It's a self fulfilling prophecy of naivety. It's like all of the investing and saving advice on Yahoo Finance. The people reading it are already the ones doing it, because they are interested in learning more about it.

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

#39

Post by nicoburns »

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?

If you know SQL you can get the best of both worlds though. You still use the ORM for basic queries, but if you know you need to, you can apply optimisations like eager loading basically for free (dev time wise). The issue is devs not understanding what's going on underneath creating unnecesary performance problems

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

#40

Post by teej »

fsloth wrote: post #34

Is 'analyst' an actual job title or just one skill required in a job? What does an 'analyst' do?

This article is talking about a role typically called “data analyst”. They help product managers, marketing, operations, etc by running reports, building dashboards, (business intelligence), building data models, running an exploratory analysis, analyzing A/B tests (product analytics) or things like building marketing attribution models or timeseries forecasting.

It’s the job that’s 80% of what a data scientist does but without Python or R. But mostly it depends because no one can decide what these roles should be called or what their responsibilities should be.

Post reply on HN