5 ms·

> SQL is a perfect language for analytics. Slightly off topic, but I strongly agree with this statement and wonder why the languages used for a lot of data sci

> SQL is a perfect language for analytics. Slightly off topic, but I strongly agree with this statement and wonder why the languages used for a lot of data science work (R, Python) don't have such a strong focus on SQL. It might just be my brain, but SQL makes so much logical sense as a query language and, with small variances, is used to directly query so many databases. In R, why learn the data.tables (OK, speed) or dplyr paradigms, when SQL can be easily applied directly to dataframes? There are libraries to support this like sqldf[1], tidyquery[2] and duckdf[3] (author). And I'm sure the situation is similar in Python. This is not a post against great libraries like data.table and dplyr, which I do use from time to time. It's more of a question about why SQL is not more popular as the query language de jour for data science. [1] https://cran.r-project.org/web/packages/sqldf/index.html https://cran.r-project.org/web/packages/sqldf/index.html [2] https://github.com/ianmcook/tidyquery https://github.com/ianmcook/tidyquery [3] https://github.com/phillc73/duckdf https://github.com/phillc73/duckdf

marcinzm 6y ago
SQL tends to be non-composable which makes complicated scripts really messy to refactor and modify (or read even). CTEs make it more sensible but they're also a rather recent addition and don't fully solve the problem. Data Science tends to involve a lot of modifying of the same code rather than creating one off or static scripts.
jfim 6y ago
SQL doesn't compose all that well. For example, imagine that you have a complex query that handles a report. If someone says "hey we need the same report but with another filter on X," your options are to copy paste the SQL query with the change, create a view that can optionally have the filter (assuming the field that you'd want to filter on actually is still visible at the view level), or parse the SQL query into its tree form, mutate the tree, then turn it back into SQL. If you're using something like dplyr, then it's just an if statement when building your pipeline. Dbplyr also will generate SQL for you out of dplyr statements, it's pretty amazing IMHO.
- phillc73 6y ago
  I agree that dbplyr is a nice way to query databases, if already familiar with dplyr (actually I think dtplyr is more interesting for operating on data.tables). However, I'm not sure I really understand your point about the "if" statement. If the data is already in a dataframe, why not still use the "if" statement, but one of the packages I mentioned earlier to further modify the data? E.g. if (x = 10) { duckdf("SELECT * WHERE y >= 20") } I guess I was thinking about one query language to rule them all. If SQL was used everywhere, it's just about learning one paradigm and largely being able to use that across platform and language.
  - jfim 6y ago
    It's for more complex queries, eg. active users per country: WITH active_users AS (SELECT DISTINCT user_id, user_country FROM ... WHERE last_login >= NOW() - 1 month) SELECT user_country, COUNT(user_id) AS user_count FROM active_users GROUP BY user_country ORDER BY user_count DESC Now imagine someone says "what about users that have at least 5 friends?" If you're using dplyr and want to reuse most of your logic, it's just a matter of doing something like active_users_with_friends = active_users %>% filter(friend_count >= 5) The SQL version is much hairier, since it's just code that's within a string.
    - phillc73 6y ago
      I think we're talking at cross purposes, and I don't want to belabour the point too much, but there's no need to modify the original SQL query (I'm imagining this pulls data from a database). If the data is already in a dataframe, use one of the libraries which can apply SQL to in-memory dataframes. e.g. active_users_with_friends <- duckdf("SELECT * FROM active_users WHERE friend_count >= 5")
      - jfim 6y ago
        There's an aggregation part that comes after the CTE, at which point the "friend_count" or "last_login" fields are not available anymore. "active_users" isn't a table, it's the result of the CTE in the query that returns distinct users that have a last_login in the last 30 days. Also, keep in mind that this is a pretty simple example, a more realistic one would probably have a half dozen to a dozen CTEs, some nested, at which point correctly mutating the SQL statement is not trivial.
kristjansson 6y ago
I think the answer to your question is that, until relatively recently it was not possible to run arbitrary SQL against data frames in either language. sqldf and duckdf pass data off to another program (albeit in-process), run the query there, and pull the result set back to R, paying data movement and transportation costs along the way. Tidyquery is neat and avoids that issue, but is also only 18 months old or so
- phillc73 6y ago
  I agree that Tidyquery and duckdf are quite new, but sqldf has been around for a long time. The first package upload to CRAN was 2007. Also, duckdf uses duckdb[1] to register a virtual in-memory table, so there's very little overhead for data transport. [1] https://duckdb.org/docs/api/r https://duckdb.org/docs/api/r
  - kristjansson 6y ago
    While sqldf has been around for a while, it's not been a performant choice, since it requires transforming and moving data into a database, albeit one that's in process. So with something like df <- data.frame(a=rnorm(5e8), b=runif(5e8)) sqldf::sqldf("select count(*) from df where a > 3") works, but takes 200 seconds and doubles the memory footprint of the process whereas nrow(df[df$a > 3, ]) sum(df$a > 3) take ~1.5 seconds and ~1s respectively on my machine. I appear to have been too pessimistic about duckdf/duckdb though. It's docs[1] claim that registering an R data.frame as a table > does not actually transfer data into DuckDB yet. Which implied to me that it just deferred transfer until the user runs a query. However, in a basic test just now: con = dbConnect(duckdb::duckdb(), ":memory:") duckdb::duckdb_register(con, "iris_view", iris) duckdb::duckdb_register(con, "df_view", df) dbGetQuery(con, "select count(*) from df_view where a > 0") it appears to execute the query directly against the data frame. At least, it runs in ~2.2s, and doesn't allocate more memory. Cool! As you've noted though, it's very new - looks like they released 0.1.0 last May? I think the point stands: until very recently, SQL-on-dataframes was not a viable choice for anyone working at even moderate scales in R or Python, so preference has been for APIs exposed by libraries (pandas, data.table, dplyr, ...) that offered users reasonable performance, even if SQL would have been be a more ergonomic interface. [1] https://duckdb.org/docs/api/r https://duckdb.org/docs/api/r
alexisread 6y ago
I'm late to the party here, but as stated in other comments, dyplr / Linq etc. Compose better so you can extend a query easier. Another advantage of composable syntaxes is that you can lazily evaluate parts of a query, and combine others, which allows the equivalent of a CTE on parts of a query you need to optimise. It's also true that SQL allows pushdown of queries to the database much easier than composable syntaxes, as SQL is usually the native language there. As such it can make for a more exploratory syntax than say graphQL where you need to define joins at design-time.

marcinzm 6y ago

SQL tends to be non-composable which makes complicated scripts really messy to refactor and modify (or read even). CTEs make it more sensible but they're also a rather recent addition and don't fully solve the problem. Data Science tends to involve a lot of modifying of the same code rather than creating one off or static scripts.

jfim 6y ago

SQL doesn't compose all that well. For example, imagine that you have a complex query that handles a report. If someone says "hey we need the same report but with another filter on X," your options are to copy paste the SQL query with the change, create a view that can optionally have the filter (assuming the field that you'd want to filter on actually is still visible at the view level), or parse the SQL query into its tree form, mutate the tree, then turn it back into SQL. If you're using something like dplyr, then it's just an if statement when building your pipeline. Dbplyr also will generate SQL for you out of dplyr statements, it's pretty amazing IMHO.

phillc73 6y ago
I agree that dbplyr is a nice way to query databases, if already familiar with dplyr (actually I think dtplyr is more interesting for operating on data.tables). However, I'm not sure I really understand your point about the "if" statement. If the data is already in a dataframe, why not still use the "if" statement, but one of the packages I mentioned earlier to further modify the data? E.g. if (x = 10) { duckdf("SELECT * WHERE y >= 20") } I guess I was thinking about one query language to rule them all. If SQL was used everywhere, it's just about learning one paradigm and largely being able to use that across platform and language.
- jfim 6y ago
  It's for more complex queries, eg. active users per country: WITH active_users AS (SELECT DISTINCT user_id, user_country FROM ... WHERE last_login >= NOW() - 1 month) SELECT user_country, COUNT(user_id) AS user_count FROM active_users GROUP BY user_country ORDER BY user_count DESC Now imagine someone says "what about users that have at least 5 friends?" If you're using dplyr and want to reuse most of your logic, it's just a matter of doing something like active_users_with_friends = active_users %>% filter(friend_count >= 5) The SQL version is much hairier, since it's just code that's within a string.
  - phillc73 6y ago
    I think we're talking at cross purposes, and I don't want to belabour the point too much, but there's no need to modify the original SQL query (I'm imagining this pulls data from a database). If the data is already in a dataframe, use one of the libraries which can apply SQL to in-memory dataframes. e.g. active_users_with_friends <- duckdf("SELECT * FROM active_users WHERE friend_count >= 5")
    - jfim 6y ago
      There's an aggregation part that comes after the CTE, at which point the "friend_count" or "last_login" fields are not available anymore. "active_users" isn't a table, it's the result of the CTE in the query that returns distinct users that have a last_login in the last 30 days. Also, keep in mind that this is a pretty simple example, a more realistic one would probably have a half dozen to a dozen CTEs, some nested, at which point correctly mutating the SQL statement is not trivial.

kristjansson 6y ago

I think the answer to your question is that, until relatively recently it was not possible to run arbitrary SQL against data frames in either language. sqldf and duckdf pass data off to another program (albeit in-process), run the query there, and pull the result set back to R, paying data movement and transportation costs along the way. Tidyquery is neat and avoids that issue, but is also only 18 months old or so

phillc73 6y ago
I agree that Tidyquery and duckdf are quite new, but sqldf has been around for a long time. The first package upload to CRAN was 2007. Also, duckdf uses duckdb[1] to register a virtual in-memory table, so there's very little overhead for data transport. [1] https://duckdb.org/docs/api/r https://duckdb.org/docs/api/r
- kristjansson 6y ago
  While sqldf has been around for a while, it's not been a performant choice, since it requires transforming and moving data into a database, albeit one that's in process. So with something like df <- data.frame(a=rnorm(5e8), b=runif(5e8)) sqldf::sqldf("select count(*) from df where a > 3") works, but takes 200 seconds and doubles the memory footprint of the process whereas nrow(df[df$a > 3, ]) sum(df$a > 3) take ~1.5 seconds and ~1s respectively on my machine. I appear to have been too pessimistic about duckdf/duckdb though. It's docs[1] claim that registering an R data.frame as a table > does not actually transfer data into DuckDB yet. Which implied to me that it just deferred transfer until the user runs a query. However, in a basic test just now: con = dbConnect(duckdb::duckdb(), ":memory:") duckdb::duckdb_register(con, "iris_view", iris) duckdb::duckdb_register(con, "df_view", df) dbGetQuery(con, "select count(*) from df_view where a > 0") it appears to execute the query directly against the data frame. At least, it runs in ~2.2s, and doesn't allocate more memory. Cool! As you've noted though, it's very new - looks like they released 0.1.0 last May? I think the point stands: until very recently, SQL-on-dataframes was not a viable choice for anyone working at even moderate scales in R or Python, so preference has been for APIs exposed by libraries (pandas, data.table, dplyr, ...) that offered users reasonable performance, even if SQL would have been be a more ergonomic interface. [1] https://duckdb.org/docs/api/r https://duckdb.org/docs/api/r

alexisread 6y ago

I'm late to the party here, but as stated in other comments, dyplr / Linq etc. Compose better so you can extend a query easier. Another advantage of composable syntaxes is that you can lazily evaluate parts of a query, and combine others, which allows the equivalent of a CTE on parts of a query you need to optimise. It's also true that SQL allows pushdown of queries to the database much easier than composable syntaxes, as SQL is usually the native language there. As such it can make for a more exploratory syntax than say graphQL where you need to define joins at design-time.