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

Practical SQL for Data Analysis

hakibenita.com

Post reply on HN
Print view View original

91–100 of 198 posts

Re: Practical SQL for Data Analysis

#91

Post by disgruntledphd2 »

EForEndeavour wrote: post #68
disgruntledphd2 wrote: post #60

Earlier quoted context omitted.

Pandas is just a bad API, to be honest. I know base-R very, very well (which was one of the inspirations, I believe) and I still spend most of my time looking stuff up. It's such a shame that python doesn't have a better DF library.

I've used pandas regularly for the past ~5 years and find its API intuitive enough not to complain. I can write and read decently long pandas chained expressions fluently, but I barely know any R. Am I unwittingly a hostage of an inferior API and don't know what I'm missing?

Yes.

Base R is OK, but dplyr is magical.

For instance, integer indexing in base R is df[row,col] rather than the iloc pandas stuff.

plot, print and summary (and generic function OOP more generally is really underappreciated).

Python is a better programming language, but R is a better data analysis environment.

And dplyr is an incredibly fluent DSL for doing data analysis (not quite as good for modelling though).

Seriously, I read the original vignette for dplyr in late 2013/early 2014 and within two weeks I'd switched most of my new analytical code over to it. So very, very good. Less idea-impedance match than any other environment, in my experience.

Re: Practical SQL for Data Analysis

#92

Post by muxator »

> This benchmark does not mention the memory consumed by the database itself - this is intentional. [...] Whether you decide to use the database or not, the memory is already paid for, so you might as well use it!

This sentence is a big red flag for me. An analysis of a stategy that pushes work towards a subsystem, and then purposedly ignores the perforance implications on that subsystem is methodologically unsound.

Personally, I am all for using the DB and writing good SQL. But if I weren't, this argument would not convince me.

Re: Practical SQL for Data Analysis

#93

Post by qwertox »

jplr8922 wrote: post #67

tldr ; if you hear ''but we can do this in SQL'', RUN!!! My eyes hurt as I read this article. There are reasons why analysts dont use SQL to do their job, and it has nothing to do with saving RAM and memory. 1) Data analysis is not a linear process, it involve playing and manipulating the data in different way and letting your mind drift a bit. You want your project in an IDE made for that purpose, the ability to cre…

What is your problem with this example, doesn't it get the job done?

  WITH temperatures AS ( /* ... */ )
  SELECT
      *,
      MAX(c) OVER (
          ORDER BY t
          ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
      ) AS hottest_temperature_last_three_days
  FROM
      temperatures;
  
       t      │ c  │ hottest_temperature_last_three_days
  ────────────┼────┼─────────────────────────────────────
   2021-01-01 │ 10 │                                  10
   2021-01-02 │ 12 │                                  12
   2021-01-03 │ 13 │                                  13
   2021-01-04 │ 14 │                                  14
   2021-01-05 │ 18 │                                  18
   2021-01-06 │ 15 │                                  18
   2021-01-07 │ 16 │                                  18
   2021-01-08 │ 17 │                                  17
Why should I fetch all the data and then form the result with another tool?

What a great article.

Re: Practical SQL for Data Analysis

#94

Post by kbelder »

m16ghost wrote: post #29

>Pandas is a very popular tool for data analysis. It comes built-in with many useful features, it's battle tested and widely accepted. However, pandas is not always the best tool for the job. SQL is very useful, but there are some data manipulations which are much easier to perform in pandas/dplyr/data.table than in SQL. For example, the article discusses how to perform a pivot table, which takes data in a "long" for…

I've been doing a lot of data analysis in Pandas recently. I started off thinking that for efficiency's sake, I should do as much initial processing in the DB as possible, and use Pandas just for the higher level functions that were difficult to do in SQL.

But after some trial and error, I find it much faster to pull relatively large, unprocessed datasets and do everything in Pandas on the local client. Faster both in total analysis time, and faster in DB cycles.

It seems like a couple of simple "select * from cars" and "select * from drivers where age Of course, this can change depending on the specific dataset, how big it is, how you're indexed, and all that jazz. Just wanted to mention how my initial intuition was misguided.

Re: Practical SQL for Data Analysis

#95

Post by 3pt14159 »

crazygringo wrote: post #74
tomnipotent wrote: post #43

Earlier quoted context omitted.

> a REST service This is a post about data analysis, and everyone wants to point out that pandas isn't good at real-time service requests. > SQL executed by the database is orders of magnitude more efficient Compared to pandas? No, it's not. Once I have all the data I need locally, it's MUCH faster to use pandas locally then to re-issue queries to a remote database.

> Once I have all the data I need locally, it's MUCH faster to use pandas locally then to re-issue queries to a remote database. Both of you are right. Sure, if you need to grab a huge chunk of the entire database and then do tons of processing on every row that SQL simply cannot do, then you're right. But when most people think SQL and database, they're thinking of grabbing a tiny fraction of rows, sped up by many o…

I agree, they're both right.

I've been doing data science since around 2008 and it's a balance between local needs and repeated analysis and an often more efficient one off query. Sure the SQL optimizer is going to read off the index for a count(*), but it doesn't really help if I need all the rows locally for data mining anyway. The counts need to line up! So I'll take the snapshot of the data locally for the one off analysis and call it a day. If I need this type of report to be run nightly, it will be off of the data warehouse infrastructure not the production DB server.

Shrug. These things take type and experience to fully internalize and appreciate.

Re: Practical SQL for Data Analysis

#96

Post by saltcured »

disgruntledphd2 wrote: post #64
ramraj07 wrote: post #11

Earlier quoted context omitted.

Completely disagree. I have a choice of using snowflake and spark/pandas to do my EDA and I’ll choose sql every time. The code is significantly more readable once you get used to it and you can most definitely do things one step at a time using udfs and temp tables / cte s. I’ve come back to EDA I did a year back and it’s always easier to read a long sql script than a notebook with pandas code.

There's pluses and minuses to both. That being said, how do I write a function in SQL which can abstract over something I do a lot (like the pivoting example earlier), or even some date function like iff(date>'important_date', 'before', 'after') as grouper. Honestly, that's what ends up making me move away from doing analytics in SQL.

Edit: oops, I think I replied a level deeper than intended. I was responding to the composition/abstraction topic. I think I should just leave this here now though?

I assume you are talking about composition of generic set-processing routines, but I wonder if others realize that? It is easy enough to write a set-returning function and wrap it in arbitrary SQL queries to consume its output. But, it is not easy to write a set-consuming function that can be invoked on an arbitrary SQL query to define its input. Thus, you cannot easily build a library of set-manipulation functions and then compose them into different pipelines.

I think different RDBMS dialects have different approaches here, but none feel like natural use of SQL. You might do something terrible with cursors. Or you might start passing around SQL string arguments to EXECUTE within the generic function, much like an eval() step in other interpreted languages. Other workarounds are to do everything as macro-processing (write your compositions in a different programming language and "compile" to SQL you pass to the query engine) or to abuse arrays or other variable-sized types (abuse some bloated "scalar" value in SQL as a quasi-set).

What's missing is some nice, first-class query (closure) and type system. It would be nice to be able to write a CTE and use it as a named input to a function and to have a sub-query syntax to pass an anonymous input to a function. Instead, all we can do is expand the library of scalar and aggregate functions but constantly repeat ourselves with the boilerplate SQL query structures that orchestrate these row-level operations.

Re: Practical SQL for Data Analysis

#97

Post by shankr »

disgruntledphd2 wrote: post #91
EForEndeavour wrote: post #68

Earlier quoted context omitted.

I've used pandas regularly for the past ~5 years and find its API intuitive enough not to complain. I can write and read decently long pandas chained expressions fluently, but I barely know any R. Am I unwittingly a hostage of an inferior API and don't know what I'm missing?

Yes. Base R is OK, but dplyr is magical. For instance, integer indexing in base R is df[row,col] rather than the iloc pandas stuff. plot, print and summary (and generic function OOP more generally is really underappreciated). Python is a better programming language, but R is a better data analysis environment. And dplyr is an incredibly fluent DSL for doing data analysis (not quite as good for modelling though). Seri…

I was actually using data.table The syntax can be bit cryptic, but you get used to it.

Re: Practical SQL for Data Analysis

#98

Post by [deleted] »

EForEndeavour wrote: post #68
disgruntledphd2 wrote: post #60

Earlier quoted context omitted.

Pandas is just a bad API, to be honest. I know base-R very, very well (which was one of the inspirations, I believe) and I still spend most of my time looking stuff up. It's such a shame that python doesn't have a better DF library.

I've used pandas regularly for the past ~5 years and find its API intuitive enough not to complain. I can write and read decently long pandas chained expressions fluently, but I barely know any R. Am I unwittingly a hostage of an inferior API and don't know what I'm missing?

[deleted]

Re: Practical SQL for Data Analysis

#99

Post by [deleted] »

protomyth wrote: post #86
solaxun wrote: post #84

Earlier quoted context omitted.

I understand your point generally but I don't understand this example. If you need data from 15 tables, you need to do those joins, regardless of prefiltering or subgrouping, right?

Well, yes but. Why do you need 15 tables for analysis queries and why isn't someone rolling those tables into something a bit easier with some backend process.

[deleted]

Re: Practical SQL for Data Analysis

#100

Post by alextheparrot »

protomyth wrote: post #86
solaxun wrote: post #84

Earlier quoted context omitted.

I understand your point generally but I don't understand this example. If you need data from 15 tables, you need to do those joins, regardless of prefiltering or subgrouping, right?

Well, yes but. Why do you need 15 tables for analysis queries and why isn't someone rolling those tables into something a bit easier with some backend process.

This is where I think the original data admins were deluding themselves. Expecting 1,400 analysts to write better code is a really non-trivial problem, but easy to proclaim.

An actual solution is creating pre-joined tables and having processes ("Hey your query took forever, have you considered using X?") or connectors (".getPrejoinedPatientTable()") that make sure those tables are being used in practice.

Post reply on HN