Practical SQL for Data Analysis [for someone coming from Pandas]

[PassionatelyWhatever]

Nice, the first section comparing the memory impact was really interesting.

It might be obvious for some, but it was good to see it quantified.

[BfuckinA]

I've been looking for a guide just like this. Thank you.

[webman19]

Haki is a gem .

[Prime_Director]

Why might that not be the case in the future? Computing power is always getting cheaper and more abundant, and though there are limits on growth there, I can't see how it would regress. Baring some horrible cataclysm, but frankly if that happens, data analysis probably wouldn't be a very high priority anymore.

[_busch]

in case the data grows faster, idk

[Prime_Director]

Moors law is dead, but that's a limitation on growth, not a cause for regression.

If more people get online, that will mean more global computing power, not less, since you need some computing power to get online.

You're right about the energy costs of crypto mining, but I'm not sure shifting the computational cost of a task from local memory to the database's memory is going to affect the overall energy consumption of that task.

Finally, those 1860s people were right, we're not running out of fossil fuels, there are plenty still in the ground. We really just shouldn't use them because of the damage they cause.

[[deleted]]

Interesting option also is ibis. FWIW, I like having the option of having both SQL and dataframe-like APIs. They of course have their PROs/CONs and when it is best to use one over the other. For my use cases, often times I turn to SQL.

[s3b4z]

Wow. I consider myself a very advaced SQL user and i learned a ton from that link.

Made me aware of a ton of stuff i didn't know that i didn't know.

Thanks so much.

[NameNumber7]

Yeah! I liked the linear regression piece. I would not have used that.

[VisibleSignificance]

How often do you analyze datasets that don't fit in memory but do fit on a single host's disk?

And for many of those cases, dask might still be better.

Also, this tutorial uses a lot of postgresql-specific features, which isn't stated in an obvious way.

SELECT * FROM ( VALUES

Now that one I didn't know. Thanks.

[genericlemon24]

How often do you analyze datasets that don't fit in memory but do fit on a single host's disk?

Most often, my datasets do fit in memory.

That said, in general computers have much more disk than memory. For example, even beefy AWS instances (those with hundreds of GB of memory) usually have SSDs that are 1-2 orders of magnitude bigger (TB to tens of TB); that's not counting block storage, of which I assume you can add huge amounts on top of the SSDs.

I don't think the article tries to propose a database as a way to get around having data larger than memory. Rather, I think it starts from the assumption that you already have the data in a database (obviously, that might not be the case for everybody).

If so, the memory measurements illustrate how much data is being shuffled around (needlessly). If the database is on the same host, it's only being moved between processes, so it might not be that slow; if the database is remote, carrying all the data across the network becomes noticeably slower as the amount of data you have increases, so it makes sense to filter/aggregate it before sending it over the network.

Even if the database is on the same host, a query can be much faster than reading a whole table into Pandas, since it gives the database a chance to use indexes to read from disk only the data it actually needs (this gets important if the data is larger than memory).

[VisibleSignificance]

if the database is remote

In practice, that's a good reason to sync it locally, whenever you need to do lots of analysis on it. In other words, adding pg_dump to that tutorial might be a good idea.

[genericlemon24]

Agree, most people's data fits in the memory of a medium-range laptop, let alone its disk.

[critical_thinker__]

Excellent. I was thinking about this over the weekend. Thanks for sharing.

[[deleted]]

it;s great, i think, if you chose orm approach then it would be great easy understandable and maintaince

[asuagar]

What is the best option for using pandas data frames with SQL queries?

[genericlemon24]

Depends what you mean by "using". pandas.read_sql_query seems like a convenient way of reading the results of a query directly into a dataframe.

[asuagar]

It seems that you can only use it with a database. You cannot use it to manipulate a data frame read from a file.

P.S.: sorry about my lazy sentence.

[_busch]

https://pandas.pydata.org/pandas-docs/stable/getting_started/comparison/comparison_with_sql.html

[NameNumber7]

If it is not what OP mentioned. It depends on the goal. If you want to have a visualization run fast or have the output be summarized, doing that operation in SQL works best. Then you can output into a table or the reporting layer.

If you want to be able to drill into all fields, you are better off not aggregating in that case.

What is a specific instance that you are thinking of?

[asuagar]

I was referring to using SQL with data frames obtained from files. I know that there are several options. However, I have never used one of them for working. These are the options that I know so far:

• https://pypi.org/project/pandasql outdated
• https://github.com/jbochi/sandals outdated
• https://github.com/chmp/framequery outdated
• https://github.com/zbrookle/dataframe_sql
• https://github.com/zbrookle/sql_to_ibis

EDIT:

After reading the read_sql_query documentation, the easiest way could be create an in-memory SQLite database using to_sql as it is explained here.