6 ms·
I've personally grown to love SQL and I think it is by far the clearest (if verbose) way to describe data transformation work. I learned traditional programming
by danso 7y ago
I've personally grown to love SQL and I think it is by far the clearest (if verbose) way to describe data transformation work. I learned traditional programming (e.g. languages like Python) long before I stumbled on SQL, but I'm not sure I could've understood Pandas/R as well without having learned SQL, particularly the concepts of joins.
That said, my affection for SQL correlates with my overall growth as a software engineer, particularly grokking the concept of piping data between scripts: e.g. writing a SQL script to extract, filter, and transform data, and then a separate Python/R script that creates a visualization. I think SQL seems so unattractive to data scientists (i.e. people who may not be particularly focused on software development) because it seems so alien and separate from the frameworks (R/Pandas) they use to do the actual analysis and visualization. And so it's understandable to just learn how to do it all in R/Pandas, rather than a separate language and paradigm for data wrangling.
- haolez 7y agoI love SQL as well, but SQL is too low level for "data scientists" that are used to copy paste scripts from the internet and working with pre-baked packages.
- danso 7y agoAdmittedly, there are data scientists in roles that consist of hacking together opague scripts until something comes out. But I do think people misperceive SQL as "low level" compared to R/Python, when ironically, SQL is actually high level, in terms of a programming language.
- gigatexal 7y agoSQL is indeed a powerful too. But it's just one of many. Just today I used: SQL to dump data to csv, python to modify it, and gRPCurl to upload it to another service. Different tools, different problems, use the right one. That being said ... RDBMs still suffer from scale and there are many efforts to fix this with sharding to disaggregating the data layer from the query layer with examples like AWS's Aurora and GCP's BigQuery etc -- in all likelihood data will likely live in many different places and will need to be adapted into things like a datastore or queried from things like Hive and Presto, or even a traditional ETL + EDW setup -- my point being there's no one thing that will solve everything as most single solutions break down when the number of rows is measured in the billions or more.
- nomel 7y ago> SQL to dump data Is this another way to say you use SQL to query specific data from a larger dataset?
- gigatexal 7y agoYeah sorry. I used the cli front end to my database from a container and had it dump the results to a file that I then massaged with python
- chrisjc 7y agoI imagine they mean SQL to unload from whatever SQL storage engine out to blob(s3,azure,etc), HDFS or local-file system. COPY INTO s3://mybucket/foobar/ from (select a, b, c from ...);
- alexhutcheson 7y agoI wish SQL had syntax more similar to dplyr[1] for complicated queries. I dislike having to do a nested sub-query every time I re-group the data - the query ends up being a deeply indented mess that needs to be read from the inside-out. Using "WITH" clauses helps a lot, but it's still not as easy to read as the more imperative dplyr style. [1] https://dplyr.tidyverse.org/ https://dplyr.tidyverse.org/
- gigatexal 7y agoBe wary of WITH statements as they are optimization barriers as some engines can't optimize them -- that's changing but just FYI. I believe the latest Postgres release is handling them properly now -- I could be wrong.
- alexhutcheson 7y agoGood note. Subqueries are sometimes optimization barriers as well - it depends on query engine. If you care about performance you need to look at the query plan.
- gigatexal 7y ago"If you care about performance you need to look at the query plan." Always I would say, once your data gets above toy size.
- mizay7 7y agowe use azure data explorer for a lot of data storage. It is a terribly boring name but it highly performant at a large range of data sizes, stores rectangular (table) data with clear schematization, and uses the piping type of syntax of tidyverse. I love it. I really wish they would rebrand and market it more.
- LessDmesg 7y agoThis, this so much. The thing I hate the most about SQL is its declarative, deeply-nested syntax with alias declarations way inside. In a normal language you can't use a variable outside its declared scope, but in SQL it's a Wild West. Pervasive CTEs and CASE statements are the best way out of this mess, I find.
- mumblemumble 7y agoI think it might go deeper than that. I wear a lot of hats - part software engineer, part data engineer, part data scientist - and I find that the only perspective from which RDBMSes feel like the clear front-runner is when I'm wearing the software engineer hat. When I'm wearing the data engineer hat, I start to get interested in some of the options that aren't even databases at all, such as storing the data in ORC files. Because they sometimes offer big performance advantages. The data intake pipelines I'm working on are batch-oriented, so they benefit little from how RDBMSes are optimized for random access to records, and maybe also ranges of records, but only at the cost of write performance if you're looking to filter on a natural key instead of a synthetic key. . . it gets thorny. ORC is less-than-stellar at selective ad-hoc queries, but I'm not doing selective ad-hoc queries against the internals of my data intake pipeline, and I will fight to the death to prevent the BI team from doing so. When I'm wearing my data scientist hat, I've got no idea what my next query on the data is, so there's no indexing strategy that could possibly meet my needs. That kills one of the RDBMS's biggest value propositions. So let's just wing it with map-reduce and get on with life. And, TBH, SQL is just too declarative for the way my brain works when I'm in analyst mode - the semi-imperative workflow I get out of something like Spark SQL or R or Pandas feels more natural.
- qorrect 7y ago> grokking Hey they brought me a smile this morning thank you ( I love Heinlein ).
- revscat 7y ago> I've personally grown to love SQL and I think it is by far the clearest (if verbose) way to describe data transformation work. I'm going to go against the grain here, at least as expressed in this thread, and say something that I think is defensible, but... SQL is a terrible language. 1. It's old. There have been many, many advancements in programming languages since its inception in the mid-80s, none of which have made it into the language itself. 2. SQL is naturally tedious, with few mechanisms for isolating common functionality into e.g. objects/interfaces/functions/monads. Stored procedures exist, but still suffer from the other shortcomings of the language. 3. There's no vendor-agnostic way to write unit tests. 4. The code itself is ugly. There isn't a standardized (or even semi-standardized) way to confidently format code, compared to languages like Python or Java. I've seen (and very occasionally written) code in languages like Ruby or Java that is elegant to the point of being artistic. SQL is almost universally ugly. 5. Over time SQL tends towards becoming unmaintainably complex, for reasons I've come to believe are endemic to the language itself. In my experience and almost without exception, SQL has been the source of the most complex and difficult to maintain/test code in every codebase I've worked with. Views that are 300+ lines long, with multiple layers of inner joins, are common. And no one wants to touch these monsters because it's almost inevitable that refactoring will break something way over yonder. 6. There's no type checking. 7. There's no standardized library sharing framework like RubyGems or Python's pip. 8. IDE support is limited, and is mainly limited to basic code formatting. Compare this to something like IntelliJ where you can just ctrl-click on a method or variable and go to the definition. Even TypeScript is better here. 9. Refactoring is dangerous, and the ability for your tools to lend a hand is limited. Want to rename a public method in Java? Right click. You can be pretty confident doing so won't break your app (with caveats). If you want to rename a column in SQL, you're not going to have nearly that amount of confidence. Now, I think there is a place for something like SQL: fast, able to easily access and update large amounts of data, etc. Something that executes directly against an RDBMS. I just wish it wasn't SQL.
- mr_overalls 7y agoJust curious if you've looked at Microsoft's LINQ language? It addresses many of the pain points you've mentioned, and would be (in my mind) a good model for the next iteration of the SQL standard.
- starpilot 7y agoJoins for pd.DataFrame.merge() and SQL are explained in exactly the same way, it doesn't matter which order you learn them in. I learned them first in pandas and found the syntax to be more intuitive there.
- oarabbus_ 7y agoI'm quite frankly stunned to be reading this. In my experience in industry it's almost universal that pandas syntax is overall pretty horrendous. SQL is light years more intuitive in my eyes.
- starpilot 7y agodf_merged = df1.merge(df2, on="common_key") or SELECT * INTO df_merged FROM (SELECT * FROM df1 JOIN df2 ON df1.common_key = df1.common_key) Python and its mature libraries are vastly more concise than most alternatives, SQL included. Especially when you get to beginner Pandas vs. beginner SQL. The latter is an absolute horror show.
- geebee 7y agoThis comparison between pandas and SQL (from the pandas site) is a good reference: https://pandas.pydata.org/pandas-docs/stable/getting_started/comparison/comparison_with_sql.html https://pandas.pydata.org/pandas-docs/stable/getting_started... Here's one example: -- tips by parties of at least 5 diners OR bill total was more than $45 SELECT * FROM tips WHERE size >= 5 OR total_bill > 45; # tips by parties of at least 5 diners OR bill total was more than $45 In [12]: tips[(tips['size'] >= 5) | (tips['total_bill'] > 45)] another one... SELECT day, AVG(tip), COUNT(1) FROM tips GROUP BY day; tips.groupby('day').agg({'tip': np.mean, 'day': np.size}) These are all very simple queries, and things can get very complicated, so this list of comparisons is hardly the last word. I've had to untangle sql, but then again, I've had to untangle python code as well. I do find the SQL expression of these two queries very clear when I read it, whereas I have to expend more mental effort on the others, especially the aggregation. And I think people who don't program would have an easier time reading the SQL as well. Also, I find it is valuable to be able to run queries directly against a database, or port them to a new environment. Pandas and R data frames are fairly similar, but if you've written them in sql, the transfer is zero effort. I also find that if you want to join several tables or more, select only a few columns, and do aggregations with HAVING clauses, with vertical stacking perhaps, the pandas code does get considerably more complicated. The SQL does too, and UNION queries are not pretty... but overall, I think the SQL code certainly can come out ahead, and times considerably ahead, in terms of expressing clarity of thought and intent to someone reading the code later. Oh one other thing you did add a bit of code by selecting into a data frame. With pandasql, you can run sql directly against a data frame and switch back and forth between pandas and sql operations, you don't need the additional step of creating and selecting into a new table. In this case, the code would look like df_merged = pysqldf("SELECT * FROM df1 JOIN df2 ON df1.common_key = df1.common_key") This is really nice, since there are some data frame operations that are much simpler in SQL and others that are much simpler in Pandas (actually, a lot of those aggregations would be handled through df.describe() -- but the querying and subsetting getting to that df may be more succinctly expressed in SQL).
- bluetwo 7y agoI also love it. Not even sure what the argument is here against it.