The CSV Virtual Table
sqlite.org
The CSV Virtual Table
1–10 of 52 posts
Re: The CSV Virtual Table
#2Piggybacking.. 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 files? I guess at a certain size (when it can't fit in RAM?) you gotta turn to SQL - or maybe I should be using SQL for smaller/simpler problems as well - but I don't really know where to start.
Re: The CSV Virtual Table
#3Piggybacking.. 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…
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 generally the table) is a super useful abstraction and often much more powerful than lists and dicts.
Data analysts need a few more like OVER (PARTITION BY) and maybe PIVOT/UNPIVOT.
Data engineers of course need many more specifically those related to database object manipulation, and stuff around performance like INDEX.
Really advanced SQL experts do stuff like this.
Re: The CSV Virtual Table
#4Piggybacking.. 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…
I found that it depends on where your interest lies.
If you just want to learn practical SQL then I have historically found the Celko books (https://en.wikipedia.org/wiki/Joe_Celko) as well as, more recently, the No Starch Press books (https://nostarch.com/practical-sql-2nd-edition) very well written.
Everyone learns differently however. I have a colleague who really enjoyed the O’Reilly books specific to SQL from a data analytics perspective. (Apologies, no link for that)
You might frequently find the No Starch and O’Reilly books on Humble Bundle, (https://www.humblebundle.com/books?hmb_source=navbar) if that is available in your location.There’s often loads of overlap between bundles. I’m sure I’ve bought the python book about 5 times so far but I don’t mind as it’s great value.
If you want to learn about database theory, however, as well as the practicalities of SQL, then I found that most of the resources I used when I did this at uni were online. The book we used was the Connolly/Begg book (https://www.pearson.com/us/higher-education/program/Connolly...)
I don’t think this effectively answered the “why” of your question, however. My guess is that SQL is a very well established domain language and when it comes to data normalised across many tuples it’s the standard for manipulation.
I don’t think that dataset size is the primary reason to manipulate data via SQL; I think that the moment your programme starts to need more than a single flat file data source, you naturally start to think about normalisation, indexes etc for performance and sanity.
If I may, however: I am coming at this from the other way in that I am far more often manipulating CSV and excel data and would like some good resources in how to quickly load that in to a dataframe in pandas or similar vs using SQL. (If I’m responding to a thread hijack I may as well go all in and totally derail it)
Re: The CSV Virtual Table
#5Piggybacking.. 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…
I don't want to advertise and am not affiliated in any way. For a first cursory understanding I do recommend the 'Mode Analytics' tutorial [0] to juniors interested in starting sql.
Re: The CSV Virtual Table
#6Piggybacking.. 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…
I found that it depends on where your interest lies. If you just want to learn practical SQL then I have historically found the Celko books ( https://en.wikipedia.org/wiki/Joe_Celko ) as well as, more recently, the No Starch Press books ( https://nostarch.com/practical-sql-2nd-edition ) very well written. Everyone learns differently however. I have a colleague who really enjoyed the O’Reilly books specific to SQL fro…
The SQLite shell supports importing and writing CSVs pretty easily - here are some snippets I reach for often enough to copy to a blog post:https://www.bbkane.com/blog/sqlite3-snippets/
Re: The CSV Virtual Table
#7Seems to me the original SQLite database storage format was text, more or less CSV, or am I mis-remembering?
Re: The CSV Virtual Table
#8Piggybacking.. 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…
I published a new introductory SQL tutorial last week, which I hope to keep improving and expanding over time. Would very much appreciate any feedback on what I've got so far!
Re: The CSV Virtual Table
#9Piggybacking.. 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 amounts of data on disk, it has built in json queries, there’s a great ecosystem of SQLite libraries, support for it is built into python.
Re: The CSV Virtual Table
#10Piggybacking.. 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…
Once you've learnt basic SQL, I found https://15445.courses.cs.cmu.edu/fall2021/homework1/ to be a nice little way of consolidating the basic concepts.