4 ms·
I've already posted on HN how I use SQL in my public affairs data journalism class [0]. To me, there is no better, in terms of accessibility and return on inves
by danso 10y ago
I've already posted on HN how I use SQL in my public affairs data journalism class [0]. To me, there is no better, in terms of accessibility and return on investment gateway language to the power of computation and programming than SQL, with the exception of spreadsheets and formulas. Even if you don't go further into programming, SQL provides the best way for describing what we always need to do with data for journalistic purposes -- joining records, filtering, sorting, and aggregating. Ironically, I learned SQL late in my programming career, and initially thought its declarative paradigm to be mysterious and inferior to procedural languages. In fact, I don't know how to do anything in SQL beyond declarative SELECT queries (and a handful of database create/update admin queries). Turns out this is just powerful enough for me for most app dev work (Rails, Django), and the simplicity is a boon for non-programmers.
ProPublica just published a bunch of data-related jobs and positions. The phrase "Proficiency in SQL is a must" makes an appearance: https://www.propublica.org/atpropublica/item/propublica-is-hiring-a-data-fellow-2017 https://www.propublica.org/atpropublica/item/propublica-is-h...
[0] https://news.ycombinator.com/item?id=8505000 https://news.ycombinator.com/item?id=8505000 https://news.ycombinator.com/item?id=10585009 https://news.ycombinator.com/item?id=10585009
- wodenokoto 10y agoI've recently started learning Pandas (dataframe library for python) and i find most queries cumbersome compared to the absolute minimal SQL I know
- lowmagnet 10y agoPandas is mostly columnar in nature. If you're heavy into Python idioms it's fairly easy to grok, imo, but there is a subtle learning curve about what is in and out of kernel. Since in-kernel operations are about 50-100x faster, it's obvious when you did the wrong thing, but it doesn't show until data sets are huge. I tried doing something regarding browser hits from Akamai data in Pandas and 3 different SQL databases (mysql, postgres, sqlite) and nothing came close to pandas for holding 150m hits (one day's worth across our properties) in memory as well as Pandas. Especially with Dask Dataframes mixed in. No competition for the effort involved.
- pweissbrod 10y agoUsing tools such as sqlmagic or pyodbc for ipython (below) you can let the sql database perform most of the heavy lifting of getting "raw" result sets into a dataframe and then perform some lighter, possibly more ptyhon-idiomatic tweaks to the data once the focused set is loaded in-memory. def getdataframe(sql): try: connection = pyodbc.connect("DSN=myDSN", autocommit=True) return pandas.read_sql(sql, connection) except Exception as e: print repr(e) finally: connection.close()
- arkh 10y ago> with the exception of spreadsheets and formulas You may want to check Oracles MODEL clause. Not as powerful as a spreadsheet but you can already do a lot of things in the middle of your SQL.
- danso 10y agoThanks for the tip, hadn't heard about it before but any SQL feature that inspires articles like "Using the SQL MODEL Clause to Generate Financial Statements" [0] is generally the mix of practicality and code that I'm looking for. I suppose one major roadblock is the use of Oracle SQL. Not the language but the database software. I used to teach MySQL and SQLite before settling on solely the latter. First, SQLite can easily handle any database that students have to deal with, which at max are in the gigabyte range (and usually much much smaller than that). Second, there's far fewer moving parts. I mean, they still have to deal with the idea that SQLite is a database, and that something like DB Browser for SQLite [1] is a client, but it's much less of an obtrusive detail than installing MySQL, a MySQL GUI, and then checking to see if the MySQL daemon is running on their personal laptops. [0] http://www.orafaq.com/node/69 http://www.orafaq.com/node/69 [1] http://sqlitebrowser.org/ http://sqlitebrowser.org/
- kwillets 10y agoI support a lot of data scientists using SQL and map-reduce technologies. The ones using SQL are about 10x more productive. The ones on map-reduce are building tools to figure out why their metrics are bad, because it takes too long to re-run them.