14 ms·
What SQL Analysts Need to Know About Python (2016)
- oarabbus_ 7y agoSQL+Python is extremely powerful, and the author makes some good points (multivariate regression should be done in python and not SQL for example), but the query example in the blog is not a good one. Every modern DB has aggregate and statistical functions like ntile, percent_Rank, median, min, max, etc. I’d honestly rather run these functions against the database than do it in python. Especially if you're working with billion+ row datasets. In fact if all I'm doing is these kinds of statisical/aggregate functions then I definitely wouldn't use python at all.
- n4r9 7y agoSQL Server doesn't make it easy to calculate median values. There are a number of slightly convoluted ways to do it but it's far from straightforward and usually I end up calculating it in C# if I can.
- bshipp 7y agoI wonder if people do timings for the variances between a function in the DB vs Python? Unless your queries are so small (like individual records) that a round-trip to the DB is impacted by network lag, I find it's almost always faster to ask the DB to do as much of the lifting as possible. The downside, of course, is that your code potentially becomes unreadable, harder to migrate between database backends, and more difficult to debug.
- teej 7y agoPushing computation back into the database has been a major trend in the last few years. Tools like DBT have made that transition easy in terms of clean code and testable SQL.
- sheepstrat 7y agoA few years ago I joined a Rails shop, and one thing that always struck me was how many of the engineers didn't know SQL. Most of them had learned to code on Rails, and had always had SQL abstracted away via ActiveRecord. I know this is not the point of this article, but as data analyst/scientist roles continue to climb in popularity, I'm curious if there won't be a similar trend with Python.
- overcast 7y agoPretty easy to see how, just look at the Python equivalent in Django. You're so far abstracted away from what is actually going on that it's no wonder no one understands it. There is a lot of gotchas, that most probably never investigate, particularly multiple calls to the database for very simple join operations that aren't made apparent in the ORM unless you're watching SQL logs(they aren't).
- nsomaru 7y agoThere’s tools to debug the lowest hanging fruit. You can go a long way with an ORM.
- andybak 7y ago> particularly multiple calls to the database for very simple join operations that aren't made apparent in the ORM unless you're watching SQL logs (they aren't). No need to check the SQL logs directly. First thing I learned about optimising Django was to check django-debug-toolbar to see how many queries were being generated per page. This is fairly common knowledge. However. I don't often bother because SQL calls aren't the most common bottleneck. It's nearly always a better use of my time to look at page weight or javascript blockage.
- bshipp 7y agodjango-debug-toolbar is excellent, and I use it on my django sites as well. In addition, the documentation does a pretty good job of highlighting some of the common gotchas. Unlike your environment, my page/js weight is very low but I'm querying against a few hundred million records joined across many tables. Even using materialized views to eliminate the impact of joins in postgres, it's required a fairly delicate touch to make the delay for page loads tolerable. In that respect I would likely redo the project in flask and sqlalchemy, if only because then I wouldn't have to remember the syntax nuances of two separate ORMs. They're similar, but not identical, and it's infuriating at times. Plus I'm very comfortable dipping down into raw SQL in sqlalchemy, and it hasn't been as intuitive for me with Django.
- drej 7y agoI'm worried about the inverse - what Python data analysts should know about SQL. Because I've met tons of analysts who wouldn't be able to even run a basic select. I've seen tons of (often non-reproducible) code written in place of a simple SQL query. I really wish bootcamps and other learning platforms focused on SQL a bit more. (I am a Python data analyst who properly learned SQL only after several years in the industry.)
- oarabbus_ 7y agoOr worse, they use cursors. Cursors should never be used in SQL. Ever. That's my philosophy.
- rpedela 7y agoServer-side cursors are great if you need to do streaming which I have in the past. It keeps both the client and server memory use in check.
- phumbe 7y agoWhat is a better approach? I have Python that directly connects to an Oracle database, and the Oracle blog tutorial[0] for using their Python package always uses a cursor. [0] https://blogs.oracle.com/oraclemagazine/perform-basic-crud-operations-with-cx-oracle-part-2 https://blogs.oracle.com/oraclemagazine/perform-basic-crud-o...
- ansgri 7y agoThey talk about client-side cursors, these are part of the Python DB API. What you shouldn't use is server-side cursor, which you create with DECLARE in PosgreSQL. These keep their state on the server and are intended for optimizations for special cases like streaming data processing or realtime updates. Basically, for deep internals of realtime systems, and not regular queries.
- goatinaboat 7y agoWhat you shouldn't use is server-side cursor, which you create with DECLARE in PosgreSQL. That entirely depends on the DB. Oracle for example always executes with a server-side cursor; all declaring it does is give you a handle to what it was doing anyway.
- DannyB2 7y agouMatrix prevents it from loading. Against my better judgement, I turned off uMatrix and loaded the page. Then had a look at uMatrix. Wow, I think we have a winner for a site with the most scripts, most 3rd party domains, and the sheer number of XHRs. Sorry for this being off topic.
- eximius 7y agoDo you have a sane setup you can share with uMatrix? Last time I looked at it, I was a bit overwhelmed and shelved it for later.
- DannyB2 7y agoI'm not sure I understand the question?
- burk96 7y agoI've found one of the best compromises is using setting uBlock Origin to advanced user mode, globally block 3rd party frames, and enabling a few extra filters that I don't know off hand. Most sites will still mostly work, and the few that don't usually just need a CDN to be enabled (you can do this per-site).
- 0xffff2 7y agouMatrix is not a "set and forget" thing like uBlock is. Using uMatrix really requires that you understand the (admittedly overwhelming) UI and make decisions for yourself about what you want to allow/block. Personally, I deny nearly everything third-party and make individual exceptions on a per-site basis, which I save for sites I visit regularly.
- arminiusreturns 7y agoSo, I don't know about setting rules beforehand that work very well universally, but what I personally think is the better method is to just slowly build up your ruleset as you use it every day. I don't think lots of people know how to do this so here it is: 1) Upon using a website you frequent, enable one root at a time until it functions properly. (or you can do the more granular per grid block enable) 2) Click umatrix, then click at the top where it says "uMatrix $ver" which will take you to the dashboard. 3) In the dashboard click on "my rules" 4) on the right click the "commit" button. What this does is commit your changes to the permanent ruleset and websites you frequent will start to "just work". A word of warning, if you blindly commit after browsing for a while when you may have done some temporary allows on random websites, those will be commited too. So I suggest either reviewing the commits first (always a good idea) and deselecting the ones you don't want, or having a new session for each of your frequented websites that you then commit from. I have been planning to write up a tutorial on things like this for family and friends, maybe I'll post it to show hn.
- geebee 7y agoI've found that SQL and data frames are pretty exceptional together in combination. I'm a big fan of a Python module called PandaSQL https://pypi.org/project/pandasql https://pypi.org/project/pandasql. What makes this work so well (for me, at least) is that I can combine pandas operations that transform data frames with SQL operations that transform data frames in the same pipeline, and if I really need to, I can just break it all apart with python and reassemble it back into a dataframe later. I don't need to recreate Boolean algebra with complex and potentially buggy dataframe operations[1], and I don't need to recreate loops, conditionals, stats operations and so forth with complex and potentially buggy SQL. For clustered work, I've found that Spark sql data frames give a lot of the same functionality (not quite all, though I think that's because there are some pandas operations that require a full in-memory dataframe and don't lend themselves to distributed solutions). [1] there have been so many attempts to replace SQL with a different relational-like language. the end result is a new syntax that doesn't work if you try to pull your queries out and run them against a database independently. I'm going on a tangent in a footnote here, but I remember reading "your data will outlast your application." I personally strive for a usable database outside the context of the application it was originally created to support. Trust me, eventually someone will want a set of reports that would (in many cases) be fair easier to write as queries if you'd made sure your back end database was a properly designed relational database.
- kumarvvr 7y agoPandaSQL was last updated 3 years ago. Do you know any other alternatives?
- vtrips 7y agoAlthough a little old, it works out fairly well
- geebee 7y agoNo, though it has been a while, and your question may prompt me to take a look around the landscape. Not that I have had a problem with pandasql, it does work nicely. This is one of those technologies that I was hoping would make its way into the framework, kind of how connection pooling for databases was once an external module but is now often built into the various web frameworks. DataFrames with common columns are such a natural match to relational tables that it seems that a way to call SQL would be (ok, in my opinion, should be) part of pandas (I think that this is the case for R data frames). I also just really enjoy writing SQL. To some extent, this is a personal preference - some people just mentally line up with certain ways of thinking. There are things that are clearly better to do in python, things that are clearly better to do in SQL, and then a grey area. I personally lean much farther to the SQL side of that grey area, but there's certainly nothing incorrect about going the other direction.
- mongol 7y agoPython + SQL seems like a good match for many analysis problems, but Excel + SQL is not bad either. I like the ability to combine complex SQL views or SQL functions with Excel pivot functionality, querying the database directly from Excel
- bshipp 7y agoEven after learning python, pandas, and SQL I still find myself firing up Excel first when exploring a new problem. The interface is incredibly simple for pasting, manipulating, and visualizing data. The warts really only start appearing when you try to build business applications on top of it and shortcomings with data integrity (amongst other issues) begin creeping in. But as a sketchpad for handling a problem? Excel is pretty hard to beat.
- avmich 7y ago> But as a sketchpad for handling a problem? Excel is pretty hard to beat. I'd recommend trying J. Download here - https://code.jsoftware.com/wiki/System/Installation#Installation https://code.jsoftware.com/wiki/System/Installation#Installa... . There is, of course, the famous barrier to entry, but the result, unsurprisingly, is also respectable.
- samvher 7y agoI also avoid pandas for simple stuff. But rather than using Excel I use dplyr and other R tidyverse packages. I think pandas is a little bit more powerful but I find R much easier to use (easier than Excel when you get used to it).
- bshipp 7y agoOne thing that hasn't been mentioned yet, that I found when I started using Python and SQL together, is the importance of well-considered indices. ORM's aren't always too clever when they generate an index, and often (for repeated queries) you can dramatically cut down the processing time by thoughtfully generating an index. If I know I'm going to be asking the database a certain question a bunch of times I'll even generate a temporary index to speed up my analysis, and then delete it when I'm done. No sense running a SQL query for an hour if I can cut it down to 5 minutes with an index on the target column. EDIT: I know this only applies to PostgreSQL, and there are numerous alternatives, but a big shout-out to pghero which helped me identify a bunch of duplicate and missing indexes in one of my databases, saving both time and hard drive space. Incidentally, it also sent me down the road to learn (but definitely not master) index optimization. https://github.com/ankane/pghero https://github.com/ankane/pghero
- teej 7y agoThe move to columnar databases for BI and data science has thankfully eliminated a lot of this type of performance cruft. There are different issues now, but needing to add indexes just to run a new type of query is something I haven’t thought about in awhile.
- alexhutcheson 7y agoI recommend "Use the Index, Luke"[1] for people who are relatively new to SQL performance issues and want to learn how to make their queries fast. [1] https://use-the-index-luke.com/ https://use-the-index-luke.com/
- fsloth 7y agoIs 'analyst' an actual job title or just one skill required in a job? What does an 'analyst' do?
- deleted 7y ago[deleted]
- teej 7y agoThis article is talking about a role typically called “data analyst”. They help product managers, marketing, operations, etc by running reports, building dashboards, (business intelligence), building data models, running an exploratory analysis, analyzing A/B tests (product analytics) or things like building marketing attribution models or timeseries forecasting. It’s the job that’s 80% of what a data scientist does but without Python or R. But mostly it depends because no one can decide what these roles should be called or what their responsibilities should be.
- just_myles 7y agoAgreed. One your last point, I don't think that will ever happen. There is a lot to benefit from your role being ambiguous.
- noobiemcfoob 7y agoA Data Analyst is a Data Scientist who can't prove Bayes Theorem.
- danso 7y agoTangential question: I'm curious how many people (here on HN, or in general) learned SQL before they learned more traditional programming (e.g. Python, Java, C)? I learned traditional programming (through college) and only stumbled upon SQL years later (someone left a "How to use Microsft Access" book around at work). I absolutely love SQL, and am in the middle of writing a book on how to use just SQLite for large, complicated data work. My target audience is programmers, but also non-programmers, because I think SQL is vastly easier to learn than something like Python. The tradeoff of course is that SQL is much more limited a framework, but it's more than enough to do data work (all the stuff that isn't visualization or complex stats). When I lived around Palo Alto, I met a few recent grads who didn't study STEM in college, but ended up working for tech firms in content and analytics roles. They'd know Excel and Tableau, but through co-workers, would pick up SQL to make their data work easier. But they hadn't yet had learned general programming, or tried Python/Javascript. But I can't imagine many other professional or academic career paths in which someone who ends up learning and using both Python and SQL started out with SQL, then learned Python (i.e. general programming). Would would they have done with SQL in school, or as a hobby?
- Whut 7y agoI'm a data engineer and I learned SQL before I learned python. I started out as a marketing specialist (mostly working in Google AdWords), then I graduated up to BI Analyst, and eventually to data engineer. I've picked up 100% of my python in my current job.
- danso 7y agoWhat did you think of Python, and was it difficult to grasp the differences between the two languages/paradigms? I ask because, for myself, any general language I pick up (Ruby,Python,JS,R), I have a good idea of underlying concepts like memory pointers and garbage collection, even if it's all abstracted by the language. With SQL, I have an incredible ignorance of the most basic programmatic concepts, like how to define variables or custom functions. The deepest underlying concept I have of SQL is that it has a query planner that does all the thinking for me.
- gamesbrainiac 7y agoIf you are working a lot with databases, and would like to automate some of the workflow in python, I'd highly recommend Pony ORM, which really feels like LINQ for python, and feels very close to the original SQL.
- whalesalad 7y agoPony is the LAST product I’d ever recommend to anyone, ever. It’s probably the worst open source software I’ve ever used. That being said, thanks to Pony I was called in to fix a shitshow and it likely earned me an additional 25% in consulting fees... so maybe I do like it in that regard. But in all seriousness, stay far away.
- gamesbrainiac 7y agoI suggest pony because it would be quite good for people who have a deep understanding of SQL, because the translation is quite nice. The alternative - SA is really overkill for most things.
- whalesalad 7y agoSQLAlchemy is a buzzkill but it’s not as heinous as Pony which is a straight up ticking time bomb in your project. Read the source code for 2 minutes and it should be enough to shy you away. As far as lightweight Python ORMs, I really enjoy Peewee and it’s usually my first choice.
- gamesbrainiac 7y agoI tried PeeWee, but it does not have support for SQLServer, which I need to work with on a daily basis.
- csours 7y agoI had a problem where there was some fixed width data in a sql database - basically someone put mainframe data in a database. There were 3 fields that had multiple entries in them, fixed width delimited. I had to split the fields by width and re-combine them, then also recombine them with another set of data with weird delimiters and rules. It took a day and half (not full time) to figure it out in python. I can't even imagine tackling the problem in a non-repl language.
- Doxin 7y agoTurns out a good database is really good at data munging. A solution for postgresql might look something like this: CREATE VIEW my_table_improved AS SELECT SUBSTRING(the_column, 0, 8) as col1, SUBSTRING(the_column, 8, 8) as col2, SUBSTRING(the_column, 16, 8) as col3 FROM my_table After which you can query my_table_improved as a normal table. col1, col2, and col3 contain the data split out from the_column. In practice you'd probably also want to do some type conversion, e.g. if col1 is supposed to be an integer you can simply update the view to select `CAST(SUBSTRING(the_column, 0, 8) as INTEGER) as col1` instead. In production use you might find this to be slow at which point you will want to create indexes for your new columns. Something like this (adjusted to which queries you're running of course) should work: CREATE INDEX ON my_table ((SUBSTRING(the_column, 0, 8))); Of course this is a lot of work if you're unfamiliar with SQL, and above examples aren't quite complete yet for your use case, but it should get you an idea of how SQL is the exact right tool for the job here. which is somewhat the point of many commenters here: get yourself familiar with SQL and save yourself a metric tonne of work in the future. An alternate approach might be to write a query that migrates the fixed-width format to a format where each entry is in their own column. The ease of this mostly depends on if applications depend on that column being in that format. side note: above sql code is untested but should be roughly correct.
- csours 7y agoIf it was just one column, that would be easy enough. Or if one object was on one row, it would be easy enough. In my case, up to 6 rows could be required to represent one object, and I had to slice 3 columns with an arbitrary number of slices. Oh, and there were two types of sub-record per object, and they had to be processed in database order.
- RocketSyntax 7y agoWhat, that psycopg2 is busted?