Practical SQL for Data Analysis
hakibenita.com
hakibenita.com
You run a query to get your data as a bunch of objects, but you're copying the data over the db connection from the postgres wire protocol into Python objects in memory, which are typically then garbage collected at the end of the transaction, then copy the result to a JSON buffer that is also garbage collected, and then send the final result to send to the browser which has been waiting this whole time.
I regularly see Django apps require several gigs of RAM per worker and when you look at the queries, it's just a bunch of poorly rendered SELECTs. It's grotesque.
Contrast PostgREST: 100 megabytes of RAM per worker. I've seen Django DRF workers require 50 to 1 memory vs PostgREST for the same REST interface and result with PostgREST being much faster. Since the database is generating the json, it can do so immediately upon generating the first result row which is then streamed to the browser. No double buffering.
Unlike Django, it's not that I don't like Pandas, it's that people way overuse it for SQL tasks as this article points out. I've seen pandas scripts that could be a simpler psql script. If psql can't do what you want, resist the temptation to 'SELECT *' into a data frame and break the problem up into stages where you get the database to do the maximum work before it gets to the data frame.
Within a system of 1400 analysts, it makes a big difference if everyone's taking 2X or 4X the time pulling that they could be. Then, even the efficiently written pulls get slowed down, so you have to run things overnight...and what if you had an error on that overnight pull? Suffice to say, it'd have been much simpler if people wrote solid SQL from the start.
This is one of those things I just don't get about folks setting up their databases. If you have a rather large dataset that keeps building via daily transactions, then its time to recognize you really have some basic distinct scenarios and to plan for them.
The most common is adding or querying data about a single entity. Most application developers really only deal with this scenario since that is what most applications care about and how you get your transactional data. Basic database knowledge gets most people to do this ok with proper primary and secondary keys.
Next up is a simple rule, "if you put a state on an entity, expect someone to need to know all the entities with this state." This is a killer for application developers for some reason. It actually requires some database knowledge to setup correctly to be performant. If the data analysts have problems with those queries, then its to to get the DBA to fix the damn schema and write some stored procedures for the app team.
At some point, you will need to do actual reporting, excuse me, business intelligence. You really should have some process that takes the transactional data and puts it into a form where the queries of data analysts can take place. In the old days that would be something to load up Red Brick or some equivalent. Transactional systems make horrid reporting systems. Running those types of queries on the same database as the transactional system is currently trying to work is just a bad idea.
Of course, if you are buying something like IBM DB2 EEE spreading queries against a room of 40 POWER servers, then ignore the above. IBM will fix it for you.
And that building an aggregation ETL pipeline, maybe inspired by this post, could be the solution?
I come from a small country, in total comparable to the 6 million veterans mentioned here.
1400 analysts is just a lot and I wouldn't imagine for example our national healthcare service could ever employ that amount of analysts?
Hm, or would they? It would make a pretty big dent in the total amount of persons in that field in our country.
The reason there are 1400 analysts: research studies each require one or two analysts. At this very moment, there are thousands of research studies taking place in the US medical system. Without these number of analysts, you'd have to completely revamp the system, killing all current projects, all current code, and creating a HUGE HUGE headache for everyone, not to mention laying off 1000+ through a system which it is NOT easy to layoff individuals through.
As a matter of fact, they want to transition to a new data infrastructure at the VA, but it's been delayed many times and the logistics have been very vague.
We really should commission 2 more analysts to figure this out...
15 tables would've been not very common for us, but something between 5 and 10 was normal. Many of those tables would've had millions upon millions of rows (while some were simple 'key tables' with only hundreds or a few thousand records)
The one thing that I learned really fast is that yes, "prefiltering/subgrouping" as the OP calls it, is very important. If you cut down on the number of rows that your query "starts out with" was very very important, as it cuts down on the amount of data that needs to be dealt with in the rest of the query. This was for an old Sybase ASE based system. IIRC, ASE would only be able to automatically optimize this across 4 clauses (might misremember the number and I left before they upgraded to the new newer version with a better optimizer), so ordering of your join clauses was important. If the filtering that cut down on the amount of data needed from other tables came first, your query would run way faster, than if the filters were way down with the rest of the joins.
Just think about it, if you start out with getting 6 million patient records and then start collecting 6 million records from the next table for a join and so on and so forth, that's way more data that needs to be read and churned through than if you can 'start on the other end' so to speak, whittle it down to say 50000 records that now need to be looked up in the patient table.
You'd identified why we're all going to have jobs in 100 years.
Automation sounds great. It's exponentially augmenting to some users in a defined user space.
Until you get to someone like me, who looks at the production structure and goes: "this is wholly insufficient for what I need to build, but it has good bones, so I'm going to strip it out and rebuild it for my use case, and only my use case, because to wait for a team to prioritize it according to an arcane schedule will push my team deadlines exceedingly far."
This is why you don't roll everything into backend processes. Companies set up for production (high automation value ROI) and analytics (high labor value ROI) and has a hard time serving the mid tail. EVERYTHING on either direction works against the mid-tail -- security policies, data access policies, software approvals, you name it.
People, policy, and technology. These are the three pillars. If your org isn't performing the way it should, then by golly work at one of these and remember that technology is only one of three.
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.
To put it in more concrete terms(plain SQL): the tables could be aggregated on a set returning function or a view.
If you've got 1400 analysts, I hope you've exported your data from an OLTP database to an OLAP database. Those generally are much easier to scale to many users.
I'm very confused by this. I've used pandas for ever a decade, and in most cases it's a massive time saver. I can do a single query to bring data into local memory in a Jupyter notebook, and from there re-use that memory across hundreds or more executions of pandas functions to further refine an analysis or whatever task I'm up to.
Your "copy-object-copy" is not relevant in data analysis use cases, and exists in pretty much any system that pulls data from an external service be it SQL or not.
Every step along the way here is wrong. The database came with SFTP connectors. The orchestrator should have simply told the database server to pull from the SFTP site into a landing table. Even if not, Pandas has datatypes you can specify on ingestion, which is heaven if your CSV or other files don't record filetypes yet are consistent in structure. Further, if you have custom validation you're applying (not a bad thing!) you likely rarely even need pandas; the native CSV library or (XLRD/openpyxl) for Excel are fine.
Ultimately, it is a training issue. The toolspace is too complex, with too many buzzwords to describe simple things, that people get lost in the whirlwind.
SQL executed by the database is orders of magnitude more efficient, way more expressive, and doesn't require you to memorize pandas' absurd API.
And I say this as someone who is by no means a SQL wizard, and fully acknowledging all of SQL's blemishes.
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.
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 orders of magnitude because it utilizes indexes, and doing all calculations/aggregations/joins server-side. Where it absolutely is going to be orders of magnitude more efficient.
Traditional database client API's often aren't going to be particularly performant in your case, because they're usually designed to read and store the entire result of a query in-memory before you can access it. If you're lucky you can enable streaming of results that bypasses this. Other times you'll be far better off accessing the data via some kind of command-line table export tool and streaming its output directly into your program.
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.
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".
If you're reading many MB's or GB's of data from a database, it's a lot more performant to stream it from the database directly into your local data structure, rather than rely on default processing of query results as a single chunk which will be a lot worse for memory and speed.
If I need to stream data because the size of data is a constraint, I'm either working on the 0.1% of projects that require it or on an analytical product rather than an analytical project.
I'm not saying you shouldn't use pandas, it depends on the size of the data. I'm working right now on a project where a SELECT * of the entire fact table would be a couple hundred gigabytes.
The flow is SQL -> pandas -> manipulation, and as always in pipelines like those, the most work you can do at the earliest stage, the better.
OTOH, SQL is super limiting for a lot of data analysis tasks and you'll inevitably need the data in weird forms that require lots of munging.
Personally, I'm a big fan of using SQL/Airflow/whatever to generate whatever data I'll need all the time at a high level of granularity (user/action etc), and then just run a (very quick) SQL query to get whatever you need into your analytics environment.
Gives you the best of both worlds, IME.
OTGH, to some (I suspect often rather large) extent, that's because most people (I suspect including many "data scientists") are pretty bad at doing their data munging in SQL.
But, SQL is bad at lots of stuff. For example, if you need to compare id1 and id2 (with some kind of locality sensitive hash or something). This requires a full join, which is prohibitive in any large data environment.
And honestly, writing 50 case when statements when I could write a function in R or Python is not my idea of a good time.
I love SQL, and do a lot with it, but there are definitely cases where it's the wrong tool. As an example, retention analyses are really annoying to do in SQL, but pretty easy in R/Python (to be fair, the article actually provides a solution here, but it's not standard).
citation needed? I've seen plenty of cases where it would have taken the db ages to do something that pandas does fast, and I don't consider pandas to be particularly fast.
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
Most data analysis isn't against datasets larger than memory, and I'd rather have more data than I need to spend time waiting to bring it all local again because I forgot a few columns that turn out to be useful later on.
Used to.
I'm in the process of moving more and more processing "upstream" from local pandas to the DBMS and it's already proving to be a bit of a superpower. I regret not learning it earlier, but the second-best time is now.
> Not for data analysis, it's a pointless constraint unless you're having issues.
This will always happen, as time spent on the project scales up (unless you use some kind of autoscaling magic I suppose).
Select * has a few valid use cases.
If you're selecting from a cte or subquery then writing the same column list twice is redundant and increases complexity/risk of mistakes.
* is also harmless for count and exists.
There is a blind-man-and-the-Elephant drift here, which is not terrible, but might need calling out to improve..
BigTable, Redshift, Snowflake and every other "big data" storage system rely heavily on partitioning to achieve the scales they're able to achieve. Some of these systems don't even support indexes.
Have you ever tried Hasura? I'm thinking of giving it a go in a future project.
The cases where this pattern emerges are invariably because someone without insufficient access to the DB and its configuration bites the bullet and instead builds whatever they need to do it in the environment they have complete access to.
I've similar problems lead to dead cycles in processor design where the impact is critical. These problems aren't software technology problems. They're people coordination problems.
<disclaimer: I work at Hasura>
But in Django's case, the same arguments can be made as for Pandas. Django is a big complex framework and an application using it might consume more memory due to countless number of other reasons. There are also best practices to use Django ORM.
But to say if a given Django instance consumes more memory it is only because of "Copy-Object-Copy effect"... I don't think so.
I was thinking it would be cool to use parts of JavaScript db libraries to parse query results passed straight through in (compressed?) db wire protocol format via WebRTC.
Why are we introducing an additional tool (psql) here unnecessarily? Sure, if it can be a simpler postgres query, that can be useful, but introducing psql and psql scripting into a workflow that is still going to use python is...utterly wasteful. And even simplifying by optimizing the use of SQL, while a good tool to have in the toolbox, is probably pretty low value for the effort for lots of data analysis tasks.
psql is a separate scriptable client tool from the postgres database server.
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.
Edit: The argument is that there are operations that should be done in the SQL layer and it is worth time learning enough about that layer to understand when and how to use it for computation. Once you learn that, it isn't really relevant if you are using psql or some other client library to build those queries.
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" format, and makes it "wider".
In the article, the pandas version is:
>pd.pivot_table(df, values='name', index='role', columns='department', aggfunc='count')
Compared to the SQL version:
>SELECT role, SUM(CASE department WHEN 'R&D' THEN 1 ELSE 0 END) as "R&D", SUM(CASE department WHEN 'Sales' THEN 1 ELSE 0 END) as "Sales" FROM emp GROUP BY role;
Not only does the SQL code require you to know up front how many distinct columns you are creating, it requires you to write a line out for each new column. This is okay in simple cases, but is untenable when you are pivoting on a column with hundreds or more distinct values, such as dates or zip codes.
There are some SQL dialects which provide pivot functions like in pandas, but they are not universal.
There are other examples in the article where the SQL code is much longer and less flexible, such as binning, where the bins are hardcoded into the query.
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 < 30", and doing all the joining, filtering, and summarizing on my machine, is often less burdensome on the db than doing it up-front in SQL.
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.
And you need to define the columns in advance anyway, because query planners can’t handle columns that change at runtime.
-You still need to enumerate and label each new column and their types. This particular problem is fixed by crosstabN().
-You need to know upfront how many columns are created before performing the pivot. In the context of data analysis, this is often dynamic or unknown.
-The input to the function is not a dataframe, but a text string that generates the pre-pivot results. This means your analysis up to this point needs to be converted into a string. Not only does this disrupt the flow of an analysis, you also have to worry about escape characters in your string.
-It is not standard across SQL dialects. This function is specific to Postgres, and other dialects have their own version of this function with their own limitations.
The article contains several examples like this where SQL is much more verbose and brittle than the equivalent pandas code.
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.
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-prevalent SQL-based dashboard platform, why not starting from SQL? Copy-n-paste is your friend!
I still hate the SQL as a programmer, but as a non-expert data analyst I now have accepted it.
Give me a better query language and I will gladly drop Pandas and Data.Table.
More usefully, most databases allow you to write user-defined functions in other languages, including imperative SQL dialects.
Pyspark on the other hand just sticks in my brain, somehow. Chained pyspark method calls looks much neater.
It's such a shame that python doesn't have a better DF library.
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.
1. Not so easy way to rename columns during aggregation
2. The group by generates its own grouped by data and hence you almost always need `reset_index`
3. Sometimes group by can convert a dataframe to series
4. Now `.loc` has provided bit consistent indexing/slicing, but earlier you had `.ix` `.iloc` and what not
These are something I can remember from top of my head. Of course all of these have solutions, but it makes pandas much more verbose. In R, these are just much more succinct.
https://www.rstudio.com/resources/rstudioglobal-2021/bringin...
Wait... I had no idea dplyr's database support was this good. https://db.rstudio.com/dplyr/
Essentially, `inplace=True` rarely actually saves memory, and causes problems if you like chaining things together. The people who maintain the library/populate the discussion boards are generally pro-chaining, so `inplace` is slowly and quietly on its way out.
The documentation should clearly say if the inplace argument causes an internal copy, because the availability of it implies that it doesn't. I've used inplace many times with Pandas because I've had code that I know is working with large amounts of data and I've thought "well I probably shouldn't chain these and cause tons of unnecessary allocations just to have prettier code".
I learned Pandas first. I have no issue with indexing, different ways of referencing cells, modifying individual rows and columns, numerous ways of slicing and dicing. It gets a little sprawling but there's a method to the madness. I can come back to it months later and easily debug. With SQL, it's just madness and 10x more verbose.
That's fair, I was using the opportunity to complain about pandas and didn't point out all the problems SQL has for some tasks. What I really want is a dataframe library for Python that's designed in a more sensible way than pandas.
> long lines of().chained().['expressions'].like_this(0)
a _bad thing_?
IMHO these pandas chains are easy to read and communicate quite clearly what's being done. If anything, I've found that in my day-to-day while reading pandas I parse the meaning of those chains at least as efficiently as from comments of any level of specificity, or from what other languages (that I have had experience with) would've looked like.
...and then BOOM, pandas chain! One statement containing 29+ concepts and their implications.
Followed by more low density code. The rollercoaster leads to complaints because it feels harder. Not because of any actual change in difficulty.
/that's my current working theory, anyway
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 data from the database and use Pandas or Data.Table to actually analyze it.
That said, this is a very comprehensive review of SQL techniques which I think could be very useful for when you do have bigger datasets and/or just need to get data out of a database efficiently. Great writeup and IMO required reading for anyone looking to be a serious "independent" data scientist (i.e. not relying on data engineers to do basic ETL for you).
I'd be a huge fan of something like the PySpark DataFrame API that "compiles" to SQL* (but that doesn't require you to actually be using PySpark which is its own can of worms). I think this would be a lot nicer for data analysis than any traditional ORM style of API, at least for data analysis, while providing better composability and IDE support than writing raw SQL.
*I also want this for Numexpr: https://numexpr.readthedocs.io/en/latest/user_guide.html, but also want a lot of other things, like a Arrow-backed data frames and a Numexpr-like C library to interact with them.
Honestly, that's what ends up making me move away from doing analytics in SQL.
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.
I actually think that SparkSQL is a good solution here, as you can create easily reusable functions.
You're entitled to your opinion and tooling choices of course, but the problem is you don't know SQL.
Can't you do composibility with foreign references?
SQL views are rarely unit tested so you always end up in a regression nightmare when you need to make updates.
If you're going to go pure SQL, you should use something like dbt.
WITH ['a', 'bc', 'def', 'g'] AS array
SELECT arrayFilter(v -> (length(v) > 1), array) AS filtered
┌─filtered─────┐
│ ['bc','def'] │
└──────────────┘
https://altinity.com/blog/harnessing-the-power-of-clickhouse...
And it’s interesting that pandas was invented at a hedge fund, AQR.
I agree that SQL may be better for vanilla BI.
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 no question that SQL databases (especially data warehouses) handle large datasets and low latency response far better than anything in the Python ecosystem.
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.
I don't know enough about how Arrow handles streaming to understand how this would really work but it would break away from the single-threaded connectivity with expensive ser/deser databases have used since the days of Sybase Db-Library, the predecessor to ODBC. 36 years is probably long enough for that model.
Is this actually a thing? Surely it can't be a thing.
Certainly in all the successful orgs I've worked in, this would rarely be the case. Maybe it works if you have really well-standardized and static data, but I rarely work in this kind of environment.
The days of dedicated SQL programers are mostly gone.
I've never met a "dedicated SQL programmer" - all the C++ programmers I've worked with in the investment banking world were also expected to know, and did know, SQL pretty well - asking questions about it were normally part of the interview process.
For some reason, he couldn't understand that when you write SQL queries for a project, you typically do it once, and basically never again, with the exception of maybe adding or removing columns from the query.
The "hard work," if you can call it that, is all in the joins. Then, I completely forget about it.
You spend so much more time in development on everything else.
Now when the analysis is done and there's a conclusion about putting some of it in production, I am yet to see a single company that relies on pandas to do data transforms "online", i.e. on the live production data stream. Typically in my experience I would end up rewriting my data transforms in either a compiled language or SQL - i.e. something much more performant and maintainable.
As a result as you progress in your career you become very proficient in both pandas and SQL - and you also start glancing at the source code of some of the transforms performed by pandas when you need to port them to the language of choice of the company you're working for.
So I implemented Vinum, which allows to execute queries which may invoke Numpy or Python functions as UDFs available to the interpreter. For example: "SELECT value, np.log(value) FROM t WHERE ..".
https://github.com/dmitrykoval/vinum
Finally, DuckDB makes a great progress integrating pandas dataframes into the API, with UDFs support coming soon. I would certainly recommend giving it a shot for OLAP workflows.
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.
Postgres certainly comes from the days of single-box, many workloads, many users.. Currently, there are more varied scenarios, and that common case is a lot less common. As this article shows very well, Postgres is actually a very capable tool.
While if you just get the tables and to most (or at least some) of the merging and processing in pandas you can do the loading of the data in the middle of the night and that is the only time you are hitting the db.
I have personally experienced big analytical SQL queries hitting the db and busy times...
However, even if a pandas query takes 5X longer to run than a SQL query you must consider the efficiency to a developer. You can chain pandas commands together that will accomplish something that takes 10x the lines of SQL. With SQL you’re more likely to end up with many intermediate CTEs along the way. So while you can definitely save processor cycles by using SQL, I don’t think you’ll save clock-face time by using it in most one off tasks. Datascience is usually column oriented and Julia and pandas allow you to stay in that world.
.format csv
.import <path to csv file> <table name>
SQLite supports defining columns without a type and will use TEXT by default, so you can take the first line of your CSV that lists the document's dimensions, put those in the brackets of a CREATE TABLE statement, and then run the .import described above (so just CREATE TABLE foo(x,y,z); if x,y,z are your column names).
After importing the data don't forget to create indexes for the queries you'll be using most often, and you're good to go.
Another suggestion for once your data is imported, have SQLite report it in table format:
.mode column
.headers onhttps://wellsr.com/python/create-scalar-and-aggregate-functi...
However, I think the limited type system in SQLite means you would still want to extract more data to process in Python, whether via pandas, numpy, or scipy stats functions. Rather introducing new composite types, I think you might be stuck with just JSON strings and frequent deserialization/reserialization if you wanted to build up structured results and process them via layers of user-defined functions.
q "SELECT COUNT(*) FROM ./clicks_file.csv WHERE c3 > 32.3"
It uses sqlite under the hood.
1: https://csvkit.readthedocs.io/en/latest/scripts/csvsql.html
It does have some nice import features for CSV data though.
WITH dt AS (
SELECT unnest(array[1, 2]) AS n
)
SELECT * FROM dt;
SELECT n FROM unnest(array[1, 2]) n;
┌───┐
│ n │
├───┤
│ 1 │
│ 2 │
└───┘
(2 rows)
I know the code is from a section dealing with CTEs, but CTEs aren't needed for every situation including things like using VALUES lists. Most people in the target audience can probably ignore this next point (probably on a newer version of PostgreSQL), but older versions of PostgreSQL would materialize the CTE results prior to processing the main query, which is not immediately obvious.
It's crazy to me how people use SELECT * -> pandas, but also how people in SQL type a ton of code over and over.
Lately I am seeing rather really creative blog posts on HN. Keep it up guys.
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 create charts, source control, export and share the information, ect. Pandas is just a piece of that puzzle which is not possible to replicate in pure SQL.
2) In 2020, there are numerical methods you want to try beyond a traditional regression. Most real world data problems are not made for stats101 tools included in sql. Kurtosis? Autocorrelation?
3) Politics. Most database administrator are control freaks who hate the idea of somebody else doing stuff in their DB. Right now were I work we still have to use SSIS-2013 instead of stored procedures in order to avoid the DBA refusal bureaucratic process.
4) Eventual professional development. If your analysis is good and creates value, chances are it will become a 'real' program and you will have to explain what you are doing to turn in into an OOP tool. If you have CS101, good coding in python will make this process much easier than a 3000 lines spagetti-SQL SQL script.
5) Data cleaning. Dealing with outliers, NAN and all that jazz really depends on the problem you try to solve. The absence of a one size fits all solutions is a good case for R/pandas/etc. These issue will break an SQL script in no time.
6) Debugging in SQL. Hahahahahahahaha
If you are still preocupied with the ram usage of your PC to do your project, here are two solutions which infuriate a lot of DBAs I've worked with.
A) https://www.amazon.ca/s?k=ram&__mk_fr_CA=%C3%85M%C3%85%C5%BD...
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
What a great article.
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 are working directly with raw data from instruments, and still need to choose wich cleaning method you will use? What if you want to check the correlation with another dataset which is not yet in your database (ex: private dataset in excel from a potential vendor)?
If you know exactly what you want, sure SQL is the way to go. However the first step of a data project is to admit that you dont know what you want. You will perform a litterature review and might have a general idea, but starting with a precise solution in mind is a receipe for failure. This is why you could need to fetch all the data and then form results with another tool. How do you even know which factors and features must be extracted before doing some exploration?
If you know the process you need and only care about RAM-CPU optimization, sure SQL is the way to go. Your project is an ETL and your have the job of a programmer who is coding a report. There is no ''data analysis'' there...
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?
Disclosure: I develop high-performance analytic database systems with SQL frontends.
And what if I want to make graphs to present stuff to a colleague? Am I supposed to use... excel to do that?
This is gratuitous. You have a clear bias, granted, because it seems your domain is so specific, only a procedural language will do. But it seems you are unfamiliar with modern SQL tools. Some of the obvious ones that come to mind: Metabase[0] for visualisation or Apache MADlib[1] for in-database statistics and machine learning.
[0] https://github.com/metabase/metabase [1] http://madlib.apache.org/
As for Metabase of MADlib, you are right ; I was not aware of these new tools. They look great, and I'm certain they can help a lot of people. However you assume that they are available! Not all IT departments are open to the idea of buying new software, and if the suggestion comes from an outsider it will be perceived as an insult (been there, done that many many time). And when they refuse, now what? You go back to the usual procedural languages (R, Python, Julia, etc) which are free and don't require the perpetual oversight of some DBA who thinks that all you need is an AVG(X) and GROUP BY since kurtosis is domain specific anyway.
I've meet some Excel-VBA users who couldn't care less about pro devs or decorators since ''they can already do everything by themselves''. Same thing with the SQL only, Python only, Tableau only or wathever-only crowd.
If you were to find outliers in a 1 PB table, what tools would you use?
The actual work the analyst is doing is far more ad-hoc than that. It's iterative and frequently will get torn down, rebuilt, and joined to other sources, as they discover new aspects to the data. They need their hands on the raw data, not on a specifically summarized version of it.
Data analysis is a soft skill that you usually learn by getting your hands dirty in a graduate or professional environment. Its not something you can put on a CV like like ''X years of experience with Y programming language''.