19 ms·

Practical SQL for Data Analysis

602 points by steve-chavez 5y ago

nerdponx 5y ago
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 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 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.
- ramraj07 5y ago
  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.
  - disgruntledphd2 5y ago
    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.
    - saltcured 5y ago
      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.
      - disgruntledphd2 5y ago
        Yeah, that's exactly the issue. One can do this in Python, but not in SQL, and this leads to boilerplate. I actually think that SparkSQL is a good solution here, as you can create easily reusable functions.
- rdedev 5y ago
  For apache arrow backed dataframes check out Polars(https://github.com/ritchie46/polars https://github.com/ritchie46/polars)
- dreyfan 5y ago
  > 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. You're entitled to your opinion and tooling choices of course, but the problem is you don't know SQL.
  - dunefox 5y ago
    That could be said about any tool, framework, language, library, etc. Invest enough time and you can do everything with malbolge but that doesn't mean it doesn't suck.
  - pandasusr 5y ago
    I am a pandas user considering refactoring part of my ETL pipeline to SQL. I see the trade off as memory efficiency vs expressiveness, and for simple queries on big data, SQL wins. Would you disagree that Pandas/Python is more expressive than SQL? I’m less experienced in SQL but based on my limited experience there, it seems Pandas is clearly more expressive. What is the SQL equivalent of Pandas .apply(lambda x) ?
    - higeorge13 5y ago
      Please define an example lambda function, in order to see whether there is an sql equivalent. Imo >90% of the data issues i have seen, can be solved with sql queries. I have seen some in the finance sector which would require super complex udfs, but other than these, sql is the first choise to solve a data issue.
      - pandasusr 5y ago
        I do work in finance sector and need complex functions :) And it’s interesting that pandas was invented at a hedge fund, AQR. I agree that SQL may be better for vanilla BI.
    - hodgesrm 5y ago
      ClickHouse has lambdas for arrays. They are very useful. Here's an example. WITH ['a', 'bc', 'def', 'g'] AS array SELECT arrayFilter(v -> (length(v) > 1), array) AS filtered ┌─filtered─────┐ │ ['bc','def'] │ └──────────────┘ The lambda in this case is a selector for strings with more than one character. I would not argue that they are as general as Pandas, but they are might useful. More examples from the following article. https://altinity.com/blog/harnessing-the-power-of-clickhouse-arrays-part-2 https://altinity.com/blog/harnessing-the-power-of-clickhouse...
- disgruntledphd2 5y ago
  > (i.e. not relying on data engineers to do basic ETL for you). Is this actually a thing? Surely it can't be a thing.
  - EForEndeavour 5y ago
    In a sufficiently large org, why not?
    - disgruntledphd2 5y ago
      My concern is not that DE's do ETL, just that a data scientist could actually avoid learning how to do this. 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.
  - denimnerd42 5y ago
    of course, that's my job... we do basic ETL on hundreds of data sources to provide data to the analysts and quants
- hodgesrm 5y ago
  > 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 no question that SQL databases (especially data warehouses) handle large datasets and low latency response far better than anything in the Python ecosystem.
  - nerdponx 5y ago
    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.
    - hodgesrm 5y ago
      OTOH Pandas/Numpy are great for results. One pattern that I'm hoping to try is running heavy lifting in SQL, transfer results via Apache Arrow, and manipulate the result set in Python with aforesaid libraries. 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.
  - neurocean 5y ago
    Yeah but that's not even close to being Pandas' value proposition. It's for the data scientist and analyst, not the db admin, data engineer or production applications.
beforeolives 5y ago
Nice article. Pandas gets the job done but it's such a step backwards in terms of useability, API consistency and code feel. You can do anything that you can possibly need with it but you regularly have to look up things that you've looked up before because the different parts of the library are patched up together and don't work consistenly in an intuitive way. And then you end up with long lines of().chained().['expressions'].like_this(0).
- nerdponx 5y ago
  I much prefer writing actual functions in a real programming language to the verbose non-reusable non-composable relic that is SQL syntax. Give me a better query language and I will gladly drop Pandas and Data.Table.
  - beforeolives 5y ago
    Yes, SQL does have the problem of bad composeability and some things being less explicit than they are when working with dataframes. Dplyr is probably the most sane API out of all of them.
    - alexilliamson 5y ago
      +1 for dplyr. I've used both pandas and dplyr daily for a couple years at different times in my career, and there is no comparison in mind when it comes to usability/verbosity/number of times I need to look at the documentation.
    - RA_Fisher 5y ago
      I agree, the degree of expressiveness that dplyr achieves is impressive. The library has had a great impact on my work. The syntax is easy to remember so you don't have to constantly look up minor variations in usage. Also, the pipelining aspect makes it dead-simple to debug. It's especially powerful when combined with the purrr library: https://purrr.tidyverse.org/ https://purrr.tidyverse.org/
  - semitones 5y ago
codeulike 5y ago
I'm so glad to have been around long enough that SQL is now being seen as exotic again
- thinkharderdev 5y ago
  Not long after I got into software engineering is when the first NoSQL craze began. I imagine that those who started not long after me and spent a few years wrangling with MongoDB queries (shudder) and trying to do joins, transactions and consistency guarantees in application code must see RDBMS as some sort of magical new technology.
sbuttgereit 5y ago
The code: WITH dt AS ( SELECT unnest(array[1, 2]) AS n ) SELECT * FROM dt; Is more complex than necessary. This produces the same result: SELECT n FROM unnest(array[1, 2]) n; ┌───┐ │ n │ ├───┤ │ 1 │ │ 2 │ └───┘ (2 rows) I think I see some other opportunities as well. 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.
- simonw 5y ago
  I think that was a deliberate choice by the author to consistently demonstrate CTEs.
  - sbuttgereit 5y ago
    Sorry was editing to address this point probably while you were typing your response. It's well taken, but we should be clear that it's not required.
jonas_b 5y ago
Question, if I have a CSV file that I'd like to do some quick SQL queries on before moving the results into Pandas. What would be good resource to do this? Preferably compatible with the rest of the Python-dataframe ecosystem and as simple as pd.read_csv()
- Vaslo 5y ago
  Try SQL Alchemy?
- rahulj51 5y ago
  Try duckdb to query and even transform the data and then export to a pandas df.
- simonw 5y ago
  If you want to try it in SQLite (pros: no need to run a server or install anything since it's in the Python standard library, cons: not nearly as many advanced statistical analysis features as PostgreSQL) my sqlite-utils CLI tool may help here: it can import from CSV/TSV/JSON into a SQLite database: https://sqlite-utils.datasette.io/en/stable/cli.html#inserting-csv-or-tsv-data https://sqlite-utils.datasette.io/en/stable/cli.html#inserti...
  - saltcured 5y ago
    The sqlite3 connection object in Python allows you to register callables which you can use as scalar or aggregate functions in your SQL queries. With this, you can fill some of the gaps compared to PostgreSQL by essentially importing Python libraries. I just found this nice tutorial while looking for relevant docs: https://wellsr.com/python/create-scalar-and-aggregate-functions-for-python-sqlite/ https://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.
- 5y ago
spamalot159 5y ago
I think for a lot of people, SQL is a skill that doesn't stick. You learn enough to do the queries you need for your project, they work then you forget about them as you work on the rest of your project. These skills are perishable. Left outer join? Yeah, I knew what that was some time ago, but not anymore The days of dedicated SQL programers are mostly gone.
- zabzonk 5y ago
  > 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.
- simonw 5y ago
  I started learning SQL twenty years ago and it's one of the only skills from the start of my career that has been consistently useful ever since.
- nisegami 5y ago
  I held this view before graduating and moving back to my 3rd world home country. For some reason, people here (the established ones with 5+ years of experience) see SQL as a general purpose programming language with which they do as much as possible.
- slver 5y ago
  We have a lot more clueless developers on average, which makes it seem like no one uses SQL anymore beyond basics. But we're still here, believe me. No one today is a dedicated X programmer, but doesn't mean we're all clueless about X.
- andrewmcwatters 5y ago
  Exactly. I own a software consultancy, and I tried explaining this to an engineering manager and he just didn't get it. 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.
simonw 5y ago
This article is so useful. It starts out with SQL basics but then quickly leaps into all kinds of PostgreSQL tricks that I didn't know about - binning, efficient sampling, calculating, even linear regression.
michelpp 5y ago
This is an excellent example of what I call the Copy-Object-Copy effect. It's particularly apparent in frameworks with ORMs like Django. In Pandas case, devs will do 'SELECT *' and use pandas as a sort of pseudo ORM. 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.
- password4321 5y ago
  I would appreciate pointers to any other low/no-copy db -> browser technologies. 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.
- tomnipotent 5y ago
  > it's that people way overuse it for SQL tasks as this article points out 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.
m16ghost 5y ago
>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" 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.
- deleted 5y ago
  [deleted]
- ziml77 5y ago
  I've always been disappointed by the SQL pivot. It's hardly useful for me if I have to know up-front all of the columns it's going to pivot out into. The solution would be to use another SQL query to generate a dynamic SQL query, but at that point I would rather just use Pandas
- deleted 5y ago
  [deleted]
- michelpp 5y ago
  Does `tablefunc.crosstab()` do what you want? https://www.postgresql.org/docs/13/tablefunc.html https://www.postgresql.org/docs/13/tablefunc.html
  - 5y ago
truth_seeker 5y ago
Pure SQL porn. Very exciting! Lately I am seeing rather really creative blog posts on HN. Keep it up guys.
dmitrykoval 5y ago
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 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 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.
- justsomeuser 5y ago
  Also I think SQLite lets you call Python functions from the SQL program.
  - dmitrykoval 5y ago
    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.
muxator 5y ago
> 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.
- mistrial9 5y ago
  oddly, I was having this conversation with a consulting client very recently. The client asked for an "in-memory datastore" .. personally, I am fluent in PostgreSQL and had already done some early versions of a solution using Postgres. Speaking, I was trying to get across the idea that Postgres can be configured specifically to a RAM balance such that, it starts to act like an "in-memory datastore". You can think of the server instance as a "wrapper" around the action of tables and access. The client was/is skeptical and also knows no Postgres personally. Perhaps there are some architectural parts missing in his mind that blur multi-node, cloud'y datastores, versus single node. 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.
jplr8922 5y ago
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 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%C3%95%C3%91&ref=nb_sb_noss_2 https://www.amazon.ca/s?k=ram&__mk_fr_CA=%C3%85M%C3%85%C5%BD... B) https://aws.amazon.com/ https://aws.amazon.com/
- qwertox 5y ago
  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.
liprais 5y ago
it seems the only reason people on hn hate sql is that they don't understand sql and/or already buy in another toolset,if you only have a hammer at hand,everything is a nail
flakiness 5y ago
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-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.
- arcticfox 5y ago
  After 15 years programming I have come to love SQL - 10 yrs ago I used to do everything in the book to avoid it out of hate, even though I knew how to use it, but over time I have moved more and more into the database / SQL and my code is just better and better as a result.
- smilbandit 5y ago
  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.
racl101 5y ago
I think I do a healthy mixture of both.
twobitshifter 5y ago
I now do most of my data analysis in Julia instead of pandas, but used pandas for a long time. SQL is a valuable skill and useful when you need to optimize a query for performance, which can sometimes happen with pandas and is much less likely with Julia. 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.
- nomel 5y ago
  For me, the main use case of a database server is so I can do operations that require more memory than I have on my local computer. Reading through this comment section makes me think my use case is rare or something. Maybe everyone is ok with chunked operations?
ojosilva 5y ago
I'm not a Pandas dev or a Pythonista for that matter but I'm pretty sure Pandas Dataframe should be able to handle SQL cursors or materializing data JIT for the aggregation. Getting all rows in with a fetchall is an antipattern and just completely impractical for a lot of use cases! Didn't Pandas have a SQL Alchemy integration already? Although I'm not sure it would run the aggregation progressively or again with fetchall internally...
- deleted 5y ago
  [deleted]
closed 5y ago
For what it's worth, I maintain a library called siuba that lets you generate SQL code from pandas methods. It's crazy to me how people use SELECT * -> pandas, but also how people in SQL type a ton of code over and over. https://github.com/machow/siuba https://github.com/machow/siuba
bayan1234 5y ago
I think one of the less discussed advantages of SQL and reasons why it might be so prevalent in the analytics world is that users don't really need to spend much time to set up their environment. I can send a query to a less technical person with my analysis for them to run. They can put the processed data into a spreadsheet and do whatever they want.
085654056681 5y ago
saya gak bisa masuk ke akun facebook saya
yewenjie 5y ago
OT but what is the best resource for learning SQL?
afiodorov 5y ago
I have been working with data for more than 5 years now and my 2 cents is that Pandas shines with exploratory analysis. Because you have the data in memory it's easy to empirically arrive at the exact data transformation you need and figure out whether your analysis is worth pursuing further. At this stage of the development your ability to iterate and experiment quickly is most valuable. I see no value in worrying about the deployment of the work you are doing if you haven't got any results just yet. 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.
lysecret 5y ago
Hmm one thing that people forget about when it comes to Pandas vs SQL is that for the kind of data you would use in pandas, 1 day and sometimes even 1 week or longer doesn't make a big difference. So say you are building some complicated data processing pipeline and you base it on a bunch of SQL queries. You are hitting the Database every single time. 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...

602 points by steve-chavez 5y ago

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 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 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.

ramraj07 5y ago
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.
- disgruntledphd2 5y ago
  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.
  - saltcured 5y ago
    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.
    - disgruntledphd2 5y ago
      Yeah, that's exactly the issue. One can do this in Python, but not in SQL, and this leads to boilerplate. I actually think that SparkSQL is a good solution here, as you can create easily reusable functions.
rdedev 5y ago
For apache arrow backed dataframes check out Polars(https://github.com/ritchie46/polars https://github.com/ritchie46/polars)
dreyfan 5y ago
> 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. You're entitled to your opinion and tooling choices of course, but the problem is you don't know SQL.
- dunefox 5y ago
  That could be said about any tool, framework, language, library, etc. Invest enough time and you can do everything with malbolge but that doesn't mean it doesn't suck.
- pandasusr 5y ago
  I am a pandas user considering refactoring part of my ETL pipeline to SQL. I see the trade off as memory efficiency vs expressiveness, and for simple queries on big data, SQL wins. Would you disagree that Pandas/Python is more expressive than SQL? I’m less experienced in SQL but based on my limited experience there, it seems Pandas is clearly more expressive. What is the SQL equivalent of Pandas .apply(lambda x) ?
  - higeorge13 5y ago
    Please define an example lambda function, in order to see whether there is an sql equivalent. Imo >90% of the data issues i have seen, can be solved with sql queries. I have seen some in the finance sector which would require super complex udfs, but other than these, sql is the first choise to solve a data issue.
    - pandasusr 5y ago
      I do work in finance sector and need complex functions :) And it’s interesting that pandas was invented at a hedge fund, AQR. I agree that SQL may be better for vanilla BI.
  - hodgesrm 5y ago
    ClickHouse has lambdas for arrays. They are very useful. Here's an example. WITH ['a', 'bc', 'def', 'g'] AS array SELECT arrayFilter(v -> (length(v) > 1), array) AS filtered ┌─filtered─────┐ │ ['bc','def'] │ └──────────────┘ The lambda in this case is a selector for strings with more than one character. I would not argue that they are as general as Pandas, but they are might useful. More examples from the following article. https://altinity.com/blog/harnessing-the-power-of-clickhouse-arrays-part-2 https://altinity.com/blog/harnessing-the-power-of-clickhouse...
disgruntledphd2 5y ago
> (i.e. not relying on data engineers to do basic ETL for you). Is this actually a thing? Surely it can't be a thing.
- EForEndeavour 5y ago
  In a sufficiently large org, why not?
  - disgruntledphd2 5y ago
    My concern is not that DE's do ETL, just that a data scientist could actually avoid learning how to do this. 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.
- denimnerd42 5y ago
  of course, that's my job... we do basic ETL on hundreds of data sources to provide data to the analysts and quants
hodgesrm 5y ago
> 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 no question that SQL databases (especially data warehouses) handle large datasets and low latency response far better than anything in the Python ecosystem.
- nerdponx 5y ago
  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.
  - hodgesrm 5y ago
    OTOH Pandas/Numpy are great for results. One pattern that I'm hoping to try is running heavy lifting in SQL, transfer results via Apache Arrow, and manipulate the result set in Python with aforesaid libraries. 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.
- neurocean 5y ago
  Yeah but that's not even close to being Pandas' value proposition. It's for the data scientist and analyst, not the db admin, data engineer or production applications.

beforeolives 5y ago

Nice article. Pandas gets the job done but it's such a step backwards in terms of useability, API consistency and code feel. You can do anything that you can possibly need with it but you regularly have to look up things that you've looked up before because the different parts of the library are patched up together and don't work consistenly in an intuitive way. And then you end up with long lines of().chained().['expressions'].like_this(0).

nerdponx 5y ago
I much prefer writing actual functions in a real programming language to the verbose non-reusable non-composable relic that is SQL syntax. Give me a better query language and I will gladly drop Pandas and Data.Table.
- beforeolives 5y ago
  Yes, SQL does have the problem of bad composeability and some things being less explicit than they are when working with dataframes. Dplyr is probably the most sane API out of all of them.
  - alexilliamson 5y ago
    +1 for dplyr. I've used both pandas and dplyr daily for a couple years at different times in my career, and there is no comparison in mind when it comes to usability/verbosity/number of times I need to look at the documentation.
  - RA_Fisher 5y ago
    I agree, the degree of expressiveness that dplyr achieves is impressive. The library has had a great impact on my work. The syntax is easy to remember so you don't have to constantly look up minor variations in usage. Also, the pipelining aspect makes it dead-simple to debug. It's especially powerful when combined with the purrr library: https://purrr.tidyverse.org/ https://purrr.tidyverse.org/
- semitones 5y ago

codeulike 5y ago

I'm so glad to have been around long enough that SQL is now being seen as exotic again

thinkharderdev 5y ago
Not long after I got into software engineering is when the first NoSQL craze began. I imagine that those who started not long after me and spent a few years wrangling with MongoDB queries (shudder) and trying to do joins, transactions and consistency guarantees in application code must see RDBMS as some sort of magical new technology.

sbuttgereit 5y ago

The code: WITH dt AS ( SELECT unnest(array[1, 2]) AS n ) SELECT * FROM dt; Is more complex than necessary. This produces the same result: SELECT n FROM unnest(array[1, 2]) n; ┌───┐ │ n │ ├───┤ │ 1 │ │ 2 │ └───┘ (2 rows) I think I see some other opportunities as well. 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.

simonw 5y ago
I think that was a deliberate choice by the author to consistently demonstrate CTEs.
- sbuttgereit 5y ago
  Sorry was editing to address this point probably while you were typing your response. It's well taken, but we should be clear that it's not required.

jonas_b 5y ago

Question, if I have a CSV file that I'd like to do some quick SQL queries on before moving the results into Pandas. What would be good resource to do this? Preferably compatible with the rest of the Python-dataframe ecosystem and as simple as pd.read_csv()

Vaslo 5y ago
Try SQL Alchemy?
rahulj51 5y ago
Try duckdb to query and even transform the data and then export to a pandas df.
simonw 5y ago
If you want to try it in SQLite (pros: no need to run a server or install anything since it's in the Python standard library, cons: not nearly as many advanced statistical analysis features as PostgreSQL) my sqlite-utils CLI tool may help here: it can import from CSV/TSV/JSON into a SQLite database: https://sqlite-utils.datasette.io/en/stable/cli.html#inserting-csv-or-tsv-data https://sqlite-utils.datasette.io/en/stable/cli.html#inserti...
- saltcured 5y ago
  The sqlite3 connection object in Python allows you to register callables which you can use as scalar or aggregate functions in your SQL queries. With this, you can fill some of the gaps compared to PostgreSQL by essentially importing Python libraries. I just found this nice tutorial while looking for relevant docs: https://wellsr.com/python/create-scalar-and-aggregate-functions-for-python-sqlite/ https://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.
5y ago

spamalot159 5y ago

I think for a lot of people, SQL is a skill that doesn't stick. You learn enough to do the queries you need for your project, they work then you forget about them as you work on the rest of your project. These skills are perishable. Left outer join? Yeah, I knew what that was some time ago, but not anymore The days of dedicated SQL programers are mostly gone.

zabzonk 5y ago
> 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.
simonw 5y ago
I started learning SQL twenty years ago and it's one of the only skills from the start of my career that has been consistently useful ever since.
nisegami 5y ago
I held this view before graduating and moving back to my 3rd world home country. For some reason, people here (the established ones with 5+ years of experience) see SQL as a general purpose programming language with which they do as much as possible.
slver 5y ago
We have a lot more clueless developers on average, which makes it seem like no one uses SQL anymore beyond basics. But we're still here, believe me. No one today is a dedicated X programmer, but doesn't mean we're all clueless about X.
andrewmcwatters 5y ago
Exactly. I own a software consultancy, and I tried explaining this to an engineering manager and he just didn't get it. 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.

simonw 5y ago

This article is so useful. It starts out with SQL basics but then quickly leaps into all kinds of PostgreSQL tricks that I didn't know about - binning, efficient sampling, calculating, even linear regression.

michelpp 5y ago

This is an excellent example of what I call the Copy-Object-Copy effect. It's particularly apparent in frameworks with ORMs like Django. In Pandas case, devs will do 'SELECT *' and use pandas as a sort of pseudo ORM. 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.

password4321 5y ago
I would appreciate pointers to any other low/no-copy db -> browser technologies. 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.
tomnipotent 5y ago
> it's that people way overuse it for SQL tasks as this article points out 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.

m16ghost 5y ago

>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" 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.

deleted 5y ago
[deleted]
ziml77 5y ago
I've always been disappointed by the SQL pivot. It's hardly useful for me if I have to know up-front all of the columns it's going to pivot out into. The solution would be to use another SQL query to generate a dynamic SQL query, but at that point I would rather just use Pandas
deleted 5y ago
[deleted]
michelpp 5y ago
Does `tablefunc.crosstab()` do what you want? https://www.postgresql.org/docs/13/tablefunc.html https://www.postgresql.org/docs/13/tablefunc.html
- 5y ago

truth_seeker 5y ago

Pure SQL porn. Very exciting! Lately I am seeing rather really creative blog posts on HN. Keep it up guys.

dmitrykoval 5y ago

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 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 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.

justsomeuser 5y ago
Also I think SQLite lets you call Python functions from the SQL program.
- dmitrykoval 5y ago
  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.

muxator 5y ago

> 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.

mistrial9 5y ago
oddly, I was having this conversation with a consulting client very recently. The client asked for an "in-memory datastore" .. personally, I am fluent in PostgreSQL and had already done some early versions of a solution using Postgres. Speaking, I was trying to get across the idea that Postgres can be configured specifically to a RAM balance such that, it starts to act like an "in-memory datastore". You can think of the server instance as a "wrapper" around the action of tables and access. The client was/is skeptical and also knows no Postgres personally. Perhaps there are some architectural parts missing in his mind that blur multi-node, cloud'y datastores, versus single node. 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.

jplr8922 5y ago

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 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%C3%95%C3%91&ref=nb_sb_noss_2 https://www.amazon.ca/s?k=ram&__mk_fr_CA=%C3%85M%C3%85%C5%BD... B) https://aws.amazon.com/ https://aws.amazon.com/

qwertox 5y ago
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.

liprais 5y ago

it seems the only reason people on hn hate sql is that they don't understand sql and/or already buy in another toolset,if you only have a hammer at hand,everything is a nail

flakiness 5y ago

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-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.

arcticfox 5y ago
After 15 years programming I have come to love SQL - 10 yrs ago I used to do everything in the book to avoid it out of hate, even though I knew how to use it, but over time I have moved more and more into the database / SQL and my code is just better and better as a result.
smilbandit 5y ago
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.

racl101 5y ago

I think I do a healthy mixture of both.

twobitshifter 5y ago

I now do most of my data analysis in Julia instead of pandas, but used pandas for a long time. SQL is a valuable skill and useful when you need to optimize a query for performance, which can sometimes happen with pandas and is much less likely with Julia. 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.

nomel 5y ago
For me, the main use case of a database server is so I can do operations that require more memory than I have on my local computer. Reading through this comment section makes me think my use case is rare or something. Maybe everyone is ok with chunked operations?

ojosilva 5y ago

I'm not a Pandas dev or a Pythonista for that matter but I'm pretty sure Pandas Dataframe should be able to handle SQL cursors or materializing data JIT for the aggregation. Getting all rows in with a fetchall is an antipattern and just completely impractical for a lot of use cases! Didn't Pandas have a SQL Alchemy integration already? Although I'm not sure it would run the aggregation progressively or again with fetchall internally...

deleted 5y ago
[deleted]

closed 5y ago

For what it's worth, I maintain a library called siuba that lets you generate SQL code from pandas methods. It's crazy to me how people use SELECT * -> pandas, but also how people in SQL type a ton of code over and over. https://github.com/machow/siuba https://github.com/machow/siuba

bayan1234 5y ago

I think one of the less discussed advantages of SQL and reasons why it might be so prevalent in the analytics world is that users don't really need to spend much time to set up their environment. I can send a query to a less technical person with my analysis for them to run. They can put the processed data into a spreadsheet and do whatever they want.

085654056681 5y ago

saya gak bisa masuk ke akun facebook saya

yewenjie 5y ago

OT but what is the best resource for learning SQL?

afiodorov 5y ago

I have been working with data for more than 5 years now and my 2 cents is that Pandas shines with exploratory analysis. Because you have the data in memory it's easy to empirically arrive at the exact data transformation you need and figure out whether your analysis is worth pursuing further. At this stage of the development your ability to iterate and experiment quickly is most valuable. I see no value in worrying about the deployment of the work you are doing if you haven't got any results just yet. 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.

lysecret 5y ago

Hmm one thing that people forget about when it comes to Pandas vs SQL is that for the kind of data you would use in pandas, 1 day and sometimes even 1 week or longer doesn't make a big difference. So say you are building some complicated data processing pipeline and you base it on a bunch of SQL queries. You are hitting the Database every single time. 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...