Practical SQL for Data Analysis • Hacker News Board

Re: Practical SQL for Data Analysis

#131

Post by dmitrykoval » Mon, May 03, 2021, 7:53 PM UTC

justsomeuser wrote: post #130

dmitrykoval wrote: post #54

Following similar observations I was wondering if one can actually execute SQL queries inside of a Python process with the access to native Python functions and Numpy as UDFs. Thanks to Apache Arrow one can essentially combine DataFrame API with SQL within data analysis workflows, without the need to copy the data and write operators in a mix of C++ and Python, all within the confines of the same Python process. So I…

Also I think SQLite lets you call Python functions from the SQL program.

That's correct, but SQLite would require to serialize/deserialize the data sent to Python func (from C to Python and back), while Arrow allows to get a "view" of the same data without making a copy. Which is probably not an issue in OLTP workloads, but may become more visible in OLAP.

Re: Practical SQL for Data Analysis

#132

Post by jplr8922 » Mon, May 03, 2021, 7:57 PM UTC

sagarm wrote: post #129

jplr8922 wrote: post #112

Earlier quoted context omitted.

I re-read my comment, and I think that I expressed myself too harshly. If you like SQL and it get the job done for you, no problem. I would see myself using that SQL query. However in order to get there, you need to know why you need the ''hottest_temparature_last_three_days''. Why not 2 or 4 days? Why not using heating degree day? What about serial correlation with other regions, or metering disfunction? What if you…

If you know SQL well, then doing exploratory analysis in SQL is perfectly reasonable. Especially if you are using a high-performance analytic database. Disclosure: I develop high-performance analytic database systems with SQL frontends.

What do you mean by exploratory analysis? Lets assume that my dataset contains features which are not normally distributed, and I want to check for coskewness and cokurtosis matrices before and after I remove outliers with a method specific to my field of research, found in a academic paper. Am I supposed to code all of this in SQL? What is your definition of exporatory analysis, and which metrics do you have in mind?

And what if I want to make graphs to present stuff to a colleague? Am I supposed to use... excel to do that?

Re: Practical SQL for Data Analysis

#133

Post by tomnipotent » Mon, May 03, 2021, 7:59 PM UTC

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…

> But when most people think SQL and database

We're not talking about most people, but data analysts. If we're just doing simple sum/count/average aggregated by a column or two with some basic predicates, SQL and an RDBMS are your best friend. Always better to keep compute + storage as close together as possible.

> Traditional database client API's

I'm not sure what point you're trying to make with this. Most data analysts are not working against streaming data, but performing one-off analyses for business counterparts that can be as simple as "X,Y,Z by A,B,C" reports to "which of three marketing treatments for our snail mail catalogue was most effective at converting customers".

Re: Practical SQL for Data Analysis

#134

Post by smilbandit » Mon, May 03, 2021, 8:04 PM UTC

flakiness wrote: post #83

The more I learn SQL, the less I write Python. Although the SQL syntax is weird and so dated, its portability across tools trumps everything else. You finished the EDA and decided to port the insights to a dashboard? With SQL it's trivial. With Python... well, probably you'll have to port it to SQL unless you have Netflix-like, Jupyter-backed dashboard infrastructure in place. For many of us who only have much-more-p…

I'm neutral on SQL but very big on the right tool for the job. My rule of thumb is, if i'm querying data just to massage it and then stuff it back into the database then I first try to do that as a stored procedure. I remember learning about windowing in SQL late into a project and after redoing some python functions as stored procedures really improving performance.

Re: Practical SQL for Data Analysis

#135

Post by dragonwriter » Mon, May 03, 2021, 8:04 PM UTC

shkkmo wrote: post #80

dragonwriter wrote: post #73

Earlier quoted context omitted.

> I'm pretty sure the premise here is that the many gigabytes/terabytes of data reside in an RDBMS to begin with, so we aren't introducing another tool psql is a separate scriptable client tool from the postgres database server.

The point was about doing more work using the SQL DB engine, the client library you do that with seems irrelevant to making that point.

> The point was about doing more work using the SQL DB engine

The specific reference was to “psql script”, which runs in the psql client program, not the DB engine. Since I suspected that might be an error intending to reference SQL running on the server, I separately addressed, in my initial response, both what was literally said (psql script) and sql running in the DB.

Re: Practical SQL for Data Analysis

#136

Post by tomnipotent » Mon, May 03, 2021, 8:09 PM UTC

protomyth wrote: post #89

mistrial9 wrote: post #40

Earlier quoted context omitted.

parent post says "devs do SELECT *" and ... relational databases can have a lot more data in one table, or related tables, than one query needs. It is often very wasteful of RAM to get ALL and filter again

If your query does " * " it gets all the columns in all the tables. Often, the optimizer when all the columns you need are on the index, never visits the actual table (I remember the term covered index). " * " basically screws this up. "Select *" should never be used in anything in an actual production environment.

Data analysis workloads more often run into problems solved by partitioning rather than indexes.

Re: Practical SQL for Data Analysis

#137

Post by nerdponx » Mon, May 03, 2021, 8:23 PM UTC

hodgesrm wrote: post #87

nerdponx wrote: post #2

SQL syntax sucks for doing non-trivial data analysis. I've tried it. Verbose, no composability or code reuse, not really portable across different databases, no easy interoperability with other tools, limited editor/IDE support, etc. I guess if you have huge amounts of data (10m+ rows) already loaded into a database then sure, do your basic summary stats in SQL. For everything else, I'll continue using SQL to get the…

> I guess if you have huge amounts of data (10m+ rows) already loaded into a database then sure, do your basic summary stats in SQL. This is not huge. This is quite small. Pandas can't handle data that runs into billions of rows or sub-second response on arbitrary queries. Both are common requirements in many analytic applications. I like Pandas. It's flexible and powerful if you have experience with it. But there's…

This is not huge. This is quite small. Pandas can't handle data that runs into billions of rows or sub-second response on arbitrary queries. Both are common requirements in many analytic applications.

You're right. It's "huge" with respect to what you can expect to load into Pandas and get instant results from. But it's not even "medium" data on the small-medium-big spectrum.

I like Pandas. It's flexible and powerful if you have experience with it. But there's no question that SQL databases (especially data warehouses) handle large datasets and low latency response far better than anything in the Python ecosystem.

I agree with this 100%, but I think a lot of people missed it in my post.

Re: Practical SQL for Data Analysis

#138

Post by ellis-bell » Mon, May 03, 2021, 8:37 PM UTC

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…

agreed that pivoting can be a pain.

a pattern that i converged on --- at least in postgres --- is to aggregate your data into json objects and then go from there. you don't need to know how many attributes (columns) should be in the result of your pivot. you can also do this in reverse (pivot from wide to long) with the same technique.

so for example if you have the schema `(obj_id, key, value)` in a long-formatted table, where an `obj_id` will have data spanning multiple rows, then you can issue a query like

``` SELECT obj_id, jsonb_object_agg(key, value) FROM table GROUP BY obj_id; ```

up to actual syntax...it's been awhile since i've had to do a task requiring this, so details are fuzzy but pattern's there.

so each row in your query result would look like a json document: `(obj_id, `{"key1": "value", "key2": "value", ...})`

see https://www.postgresql.org/docs/current/functions-json.html for more goodies.

Re: Practical SQL for Data Analysis

#139

Post by qwertox » Mon, May 03, 2021, 8:40 PM UTC

jplr8922 wrote: post #112

qwertox wrote: post #93

Earlier quoted context omitted.

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

I re-read my comment, and I think that I expressed myself too harshly. If you like SQL and it get the job done for you, no problem. I would see myself using that SQL query. However in order to get there, you need to know why you need the ''hottest_temparature_last_three_days''. Why not 2 or 4 days? Why not using heating degree day? What about serial correlation with other regions, or metering disfunction? What if you…

> If you know exactly what you want, sure SQL is the way to go.

I've been doing similar aggregations with MongoDB, simply because when I start a project where I quickly want to store data and then a week later check what I can do with it, it's a tool which is trivial to use. I don't need to think about so many factors like where and how to store the data. I use MongoDB for its flexibility (schemaless), compared to SQL.

But I also use SQL mostly because of the power relations have, yet I use it only for storing stuff where it's clear how the data will look for forever, and what (mostly simple) queries I need to perform.

I've read your comment as if it was suggesting that these kind of articles are not good, yet for me it was a nice overview of some interesting stuff that can be done with SQL. I don't chase articles on SQL, so I don't get many to read, but this one is among the best I've read.

To get back to the quote: How will I know if SQL can do what I want, if I don't read these kind of articles?

Re: Practical SQL for Data Analysis

#140

Post by cgh » Mon, May 03, 2021, 8:47 PM UTC

steve-chavez wrote: post #113

protomyth wrote: post #86

Earlier quoted context omitted.

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.

> why isn't someone rolling those tables into something a bit easier with some backend process. To put it in more concrete terms(plain SQL): the tables could be aggregated on a set returning function or a view.

Yes, in these situations materialized views with indexes are generally the correct answer.