41–50 of 52 posts
Any possibility to also add index to a virtual table in SQLite? Normally you can’t: https://sqlite.org/vtab.html
You could just INSERT INTO regular_table SELECT * FROM csv_table and then create an index for regular_table
Earlier quoted context omitted.
Jennifer Widom’s Databases course is great https://online.stanford.edu/courses/soe-ydatabases-databases Otherwise SQL is best learned by solving problems and googling when you’re stuck. Most developers only need a few SQL constructs like SELECT, WHERE, WITH, JOIN, INSERT, CREATE and GROUP BY. Most devs learn and forget because they don’t use SQL very often. It’s too bad because the relational data structure (or more…
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 d…
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.
All my data are in TSV files, separated by tabs instead of comma and strings are not quoted. It makes parsing or manually editing data so much easier. You need to escape only like 5 characters \0 \n \r \\ \t and even binary data will fit in there. I was checking if sqlite's CSV supports custom separator and unquoted strings for this to work. Found it doesn't.
Earlier quoted context omitted.
Jennifer Widom’s Databases course is great https://online.stanford.edu/courses/soe-ydatabases-databases Otherwise SQL is best learned by solving problems and googling when you’re stuck. Most developers only need a few SQL constructs like SELECT, WHERE, WITH, JOIN, INSERT, CREATE and GROUP BY. Most devs learn and forget because they don’t use SQL very often. It’s too bad because the relational data structure (or more…
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 d…
It probably says that every day. I've seen that in a lot of self-paced courses
All my data are in TSV files, separated by tabs instead of comma and strings are not quoted. It makes parsing or manually editing data so much easier. You need to escape only like 5 characters \0 \n \r \\ \t and even binary data will fit in there. I was checking if sqlite's CSV supports custom separator and unquoted strings for this to work. Found it doesn't.
Use the data-delimiter characters actually defined in the ASCII / Unicode specifications to, you know, delimit your frigging data, and you won't have to bother about whether to quote or not to quote, nor about painstakingly "escaping" (and un-escaping) whatever characters you're (mis)using for delimiters.
Sure as Hell blew away at least my mind when I came across it.
(Not that I got to implement it at work, of course.)
___
[1]: https://en.wikipedia.org/wiki/C0_and_C1_control_codes#Field_...
[2]: https://en.wikipedia.org/wiki/Delimiter#ASCII_delimited_text
Tangently, I didn't about the double quotes rules of the rfc4180 > If fields are not enclosed with double quotes, then double quotes may not appear inside the fields. Feels to me like a lot of manual implementations will break this assumption if they naively only try to deal with line breaks and delimiter escaping. Either way, CSV is fraught enough format that I wouldn't try to ingest files of from unknown sources or…
Should fix all those issues, AFAICS.
So of course abloslutely nobody uses it. :-(
Earlier quoted context omitted.
You could just INSERT INTO regular_table SELECT * FROM csv_table and then create an index for regular_table
Genuine question - why start with a virtual table at all in this case? Why not just import the data to regular_table from the csv file?
Earlier quoted context omitted.
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 d…
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…
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
Piggybacking.. What's a good resource to learn SQL from scratch? Maybe out of ignorance, but in all the years I've programmed I've never had a situation when I said to myself "I need a database"! I either have data in maps/vectors in memory or if I need tables I'll use some table datastructure (like in R/Matlab). So reading a CSV file into a SQL table seems a bit weird - though maybe some people have multi-gig CSV fi…
Two major reasons I use SQLite are the built in full text search https://www.sqlite.org/fts5.html and fuzzy matching https://www.sqlite.org/spellfix1.html these are two things you often want to do with your data frames but other solutions are usually pretty slow whereas SQLite is fast. Other reasons: every language can use a SQLite db, you can add indexes, you can add multiple tables in one file, you can store large…
Earlier quoted context omitted.
Two major reasons I use SQLite are the built in full text search https://www.sqlite.org/fts5.html and fuzzy matching https://www.sqlite.org/spellfix1.html these are two things you often want to do with your data frames but other solutions are usually pretty slow whereas SQLite is fast. Other reasons: every language can use a SQLite db, you can add indexes, you can add multiple tables in one file, you can store large…
So then the question is.. why have R/Python/etc. reinvented the wheel with their own datatable structures instead of using SQL?
Dataframe abstractions are really easy to understand so I have worked with journalists and biologists who are able to work productively with pandas or R. Transforming and analyzing data is often easier to write with pyspark while spark sql makes it easy to access and extract data from some data sources.
It all really depends on who is your customer, who are your coworkers, how much volume of data do you have, how much time do you have to accomplish your task etc.