5 ms·

Oh the course starts today.. Is there way to view the lectures without enrolling? Just more generally, do you have some mental model when you move from using s

by geokon 5y ago

Oh the course starts today.. Is there way to view the lectures without enrolling? Just more generally, do you have some mental model when you move from using something like a dataframe to using SQL? For instance people working in R rarely talk about using an SQL database - even when they have huge datasets. And I guess I don't really see when you should be thinking about making the switch over. The only thing I see distinct about databases is it's always hand in hand with web-tech - and it more focused on mutability (which if you are doing some stats your data is usually immutable)

wenc 5y ago
I’m not aware of other ways to get at the material though I’m sure Widom’s website at Stanford might have some. It’s not just a size thing but also a concurrency thing. Data frames are like tiny local databases. SQL databases are like central databases that many parties read and write from. There’s no switchover point as such. A common use case is to use SQL to query a subset of data from a central database, and that subset becomes a local data frame that an R user can work off of. Data frames and relational databases are very similar. When you’re using dplyr many of the operations conceptually map 1-to-1 to SQL. SQL is just a data frame manipulation DSL for databases. It’s all about filtering, aggregation and projections. Also not true about mutability and web tech. Most SQL operations do not mutate. Also SQL is more associate with data analytics, data engineering and report creation from data warehouses than web tech. The web tech use case came about because of PHP/MySQL and Django etc. but there SQL is usually used as an API language for the underlying data store rather than the full query engine that it is.
- geokon 5y ago
  Right! the dplyr functionality seems to match 1-to-1 to what a cursory look at SQL told me - hence my confusion as to why they reinvented the wheel instead of using an industry standard The async table access is an interesting angle - assuming you're doing mutations that probably places some constraints. But in the case of in-app databasese my SQLite that's generally not a concern. From what you've said, at the end of the day, it sounds like the people using tables/dataframes are just too lazy to setup and use a SQL? I don't really see any distinction btwn the two domains then
  - wenc 5y ago
    I think it's more of a case of preference and ergonomics. For local dataframes, it's still more ergonomic to use a language's own constructs than to adopt a totally separate DSL for data manipulation. SQL is a different "language" which has its own cognitive overheads. So things like dplyr, pandas, and LINQ (in C#) were invented as language-native ways to manipulate tabular data structures without the overhead of context-switching [1]. After all, learning SQL and being good at it on top of mastering one's own programming language takes something extra. That said, it's possible and indeed sometimes preferable to use pure SQL for local data tables. I use duckdb to do complex manipulations on dataframes in Python because it's a lot faster than Pandas (due to some columnar optimizations) and because I can express certain things more succinctly in SQL than in Pandas (it's true -- I appreciate Pandas for the achievements that it is, but its syntax can be verbose with all its .apply() and df[df[col=="abc"]] incantations). Using SQL via sqlite to manipulate local tables confer similar benefits. [1] To be fair, pandas, dplyr and LINQ -- by virtue of being built into their respective languages -- can do things that SQL can't (or can't do easily) because it's not constrained by some of SQL's design.
loco5niner 5y ago
> Oh the course starts today.. It probably says that every day. I've seen that in a lot of self-paced courses

by geokon 5y ago

wenc 5y ago

I’m not aware of other ways to get at the material though I’m sure Widom’s website at Stanford might have some. It’s not just a size thing but also a concurrency thing. Data frames are like tiny local databases. SQL databases are like central databases that many parties read and write from. There’s no switchover point as such. A common use case is to use SQL to query a subset of data from a central database, and that subset becomes a local data frame that an R user can work off of. Data frames and relational databases are very similar. When you’re using dplyr many of the operations conceptually map 1-to-1 to SQL. SQL is just a data frame manipulation DSL for databases. It’s all about filtering, aggregation and projections. Also not true about mutability and web tech. Most SQL operations do not mutate. Also SQL is more associate with data analytics, data engineering and report creation from data warehouses than web tech. The web tech use case came about because of PHP/MySQL and Django etc. but there SQL is usually used as an API language for the underlying data store rather than the full query engine that it is.

geokon 5y ago
Right! the dplyr functionality seems to match 1-to-1 to what a cursory look at SQL told me - hence my confusion as to why they reinvented the wheel instead of using an industry standard The async table access is an interesting angle - assuming you're doing mutations that probably places some constraints. But in the case of in-app databasese my SQLite that's generally not a concern. From what you've said, at the end of the day, it sounds like the people using tables/dataframes are just too lazy to setup and use a SQL? I don't really see any distinction btwn the two domains then
- wenc 5y ago
  I think it's more of a case of preference and ergonomics. For local dataframes, it's still more ergonomic to use a language's own constructs than to adopt a totally separate DSL for data manipulation. SQL is a different "language" which has its own cognitive overheads. So things like dplyr, pandas, and LINQ (in C#) were invented as language-native ways to manipulate tabular data structures without the overhead of context-switching [1]. After all, learning SQL and being good at it on top of mastering one's own programming language takes something extra. That said, it's possible and indeed sometimes preferable to use pure SQL for local data tables. I use duckdb to do complex manipulations on dataframes in Python because it's a lot faster than Pandas (due to some columnar optimizations) and because I can express certain things more succinctly in SQL than in Pandas (it's true -- I appreciate Pandas for the achievements that it is, but its syntax can be verbose with all its .apply() and df[df[col=="abc"]] incantations). Using SQL via sqlite to manipulate local tables confer similar benefits. [1] To be fair, pandas, dplyr and LINQ -- by virtue of being built into their respective languages -- can do things that SQL can't (or can't do easily) because it's not constrained by some of SQL's design.

loco5niner 5y ago

> Oh the course starts today.. It probably says that every day. I've seen that in a lot of self-paced courses