Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

The CSV Virtual Table

sqlite.org

Post reply on HN
Print view View original

11–20 of 52 posts

Re: The CSV Virtual Table

#11

Post by jodrellblank »

geokon wrote: post #2

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…

For the very basics, Julia Evans has https://jvns.ca/blog/2020/02/03/new-zine--become-a-select-st...

(I think most/all of the comics are entries on her blog if you desperately don't want to pay).

Re: The CSV Virtual Table

#12

Post by geokon »

pierredewet wrote: post #4
geokon wrote: post #2

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…

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…

So uhh, when are you gunna use a dataframe and when will you stick to SQL? My understanding is that SQL can sorta do a very powerful (and standardized/fast) subset of what a dataframe can

Re: The CSV Virtual Table

#13

Post by thejosh »

geokon wrote: post #2

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…

You'll find with decent SQL and a good backend your SQL will be much quicker than your R/Matlab/Python programs depending on what you want to do.

Re: The CSV Virtual Table

#14

Post by makeitdouble »

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 from unknown generators.

Re: The CSV Virtual Table

#15

Post by fulafel »

makeitdouble wrote: post #14

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…

Note that the interpretation of CSV files in the wild is all over the map, that RFC is not a very high authority on how things actually work with CSV processing in software you encounter.

Re: The CSV Virtual Table

#16

Post by hnlmorg »

Shameless plug but if you’re wanting to load raw data into CSV then it might be worth looking at my shell.

You can import CSV, jsonlines, even human formatted tabulated data like the output from ‘ls -l’ and ‘ps’. And you can import from files directly, gzip archives, pipes, and shell variables.

Eg

    ps aux | select count(*), user GROUP BY user ORDER BY 1
(FROM is dropped here because you’re importing from STDIN)

It uses sqlite3 under the hood and was written to solve exactly the problem of needing to quickly validate a bunch of CSV files. But it proved so useful that I ended up making it an official part of the shell.

https://GitHub.com/lmorg/murex

Re: The CSV Virtual Table

#17

Post by goto11 »

geokon wrote: post #2

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…

Relational databases are most useful when you have multiple tables you need to query across and when the data changes over time.

Re: The CSV Virtual Table

#18

Post by jamapy »

geokon wrote: post #2

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…

https://selectstarsql.com/ is an interactive book which aims to be the best place on the internet for learning SQL. It helps you learn by running queries against a real-world dataset to complete projects of consequence. It is free of charge, free of ads and doesn't require registration or downloads.

Re: The CSV Virtual Table

#19

Post by 8n4vidtmkvmk »

geokon wrote: post #2

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…

never needed a database huh? you and i live in different worlds. every web app I've written has needed a database. need somewhere to put user data and transaction logs

Re: The CSV Virtual Table

#20

Post by tonyedgecombe »

8n4vidtmkvmk wrote: post #19
geokon wrote: post #2

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…

never needed a database huh? you and i live in different worlds. every web app I've written has needed a database. need somewhere to put user data and transaction logs

I don't think it's that uncommon. I've done very little database work over a forty year career. Web development is a subset of the industry, there is a whole lot of other stuff going on.
Post reply on HN