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

21–30 of 52 posts

Re: The CSV Virtual Table

#21

Post by dotancohen »

hnlmorg wrote: post #16

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 use…

This looks terrific. But why create a new shell, why not a `select` executable (or, given that `select` seems to be a bash keyword for synchronous I/O multiplexing, an alias such as `sel`)?

The idea looks perfect for so many of my use cases, but the implementation as a shell is a significant roadblock to anyone invested in their current shell. I'd bet that's a significant portion of the people who would find this useful.

Re: The CSV Virtual Table

#22

Post by [deleted] »

hnlmorg wrote: post #16

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 use…

[deleted]

Re: The CSV Virtual Table

#23

Post by jcagalawan »

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://executeprogram.com has a SQL course. I’ve tried other resources but this one made it click for me after years if relying on SQLAlchemy.

Re: The CSV Virtual Table

#24

Post by DocTomoe »

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…

Not necessarily SQL, but to get a firm understanding of (relational) databases, I like to recommend "The Manga Guide to Databases". Yes, it's a comic. Yes, not everyone reacts well to the story of a teenage faux-fantasy princess being helped by the database fairy to get her fruit export business to run efficiently. But it teaches you the basic concepts you'll be using 95% of the time way better than any documentation I've ever read.

Re: The CSV Virtual Table

#25

Post by geokon »

wenc wrote: post #3
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…

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 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)

Re: The CSV Virtual Table

#26

Post by hnlmorg »

dotancohen wrote: post #21
hnlmorg wrote: post #16

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 use…

This looks terrific. But why create a new shell, why not a `select` executable (or, given that `select` seems to be a bash keyword for synchronous I/O multiplexing, an alias such as `sel`)? The idea looks perfect for so many of my use cases, but the implementation as a shell is a significant roadblock to anyone invested in their current shell. I'd bet that's a significant portion of the people who would find this use…

That's a fair question. The shell has been around for > 8 years (and been my primary shell for around 5 of those years) so it made sense to integrate it into the shell because I could then leverage the existing code that transparently handles different data formats. That shell also sends metadata about the documents being piped which means you effectively have typed pipes (like Powershell but backwards compatible with POSIX tools). So it means you can do more with the table afterwards too.

And since the shell is already good for handling structured data (like `jq` et al but not limited to JSON) it also made sense to allow inlining of SQL for when you're working with relational data.

An example of this last point is a recent problem I had at work where I wanted to see which users had 2FA enabled. Unfortunately this service provided a user list API (returns a complex JSON document) and a separate user meta data API (again JSON). The meta data didn't have names on it and the user list didn't have 2FA details on it. So I had two JSON documents that weren't tables but ostensibly held relational data. Using the shell I could convert the two JSON documents into two tables and then use the SQL inlining `select` command to do the relational query. The whole thing was 3 lines of code whereas to do the equivalent in Python / Typescript / etc would have been much more verbose.

Re: The CSV Virtual Table

#27

Post by dotancohen »

hnlmorg wrote: post #26
dotancohen wrote: post #21

Earlier quoted context omitted.

This looks terrific. But why create a new shell, why not a `select` executable (or, given that `select` seems to be a bash keyword for synchronous I/O multiplexing, an alias such as `sel`)? The idea looks perfect for so many of my use cases, but the implementation as a shell is a significant roadblock to anyone invested in their current shell. I'd bet that's a significant portion of the people who would find this use…

That's a fair question. The shell has been around for > 8 years (and been my primary shell for around 5 of those years) so it made sense to integrate it into the shell because I could then leverage the existing code that transparently handles different data formats. That shell also sends metadata about the documents being piped which means you effectively have typed pipes (like Powershell but backwards compatible wit…

  > That shell also sends metadata about the documents being piped which means
  > you effectively have typed pipes (like Powershell but backwards compatible
  > with POSIX tools). So it means you can do more with the table afterwards too.
It looks like such a shell would greatly benefit from a stdmeta file descriptor, as described here:

https://unix.stackexchange.com/questions/197809/propose-addi...

The idea is that such a file descriptor would output lines that are not part of the data yet are not errors. One example usage would be to output the headers of commands such as ps, and murex could use such stdmeta lines as column names.

Re: The CSV Virtual Table

#28

Post by makeitdouble »

mwcremer wrote: post #7

Seems to me the original SQLite database storage format was text, more or less CSV, or am I mis-remembering?

The current version seems to still be text base, or at least text readable https://sqlite.org/fileformat.html

Unsurprisingly there's still a lot of data flowing around with these pure text size header + contents structure files, I didn't know SQLite was in that camp as well.

Re: The CSV Virtual Table

#29

Post by hnlmorg »

dotancohen wrote: post #27
hnlmorg wrote: post #26

Earlier quoted context omitted.

That's a fair question. The shell has been around for > 8 years (and been my primary shell for around 5 of those years) so it made sense to integrate it into the shell because I could then leverage the existing code that transparently handles different data formats. That shell also sends metadata about the documents being piped which means you effectively have typed pipes (like Powershell but backwards compatible wit…

> That shell also sends metadata about the documents being piped which means > you effectively have typed pipes (like Powershell but backwards compatible > with POSIX tools). So it means you can do more with the table afterwards too. It looks like such a shell would greatly benefit from a stdmeta file descriptor, as described here: https://unix.stackexchange.com/questions/197809/propose-addi... The idea is that such…

That would be awesome but I think what we need is something a little more programmatic. eg a standard way of passing Content-Type / MIMEs with fields for meta-data. A hypothetical example might look like this:

  {
    "Content-Type": "application/table",
    "Stdin": "ls",
    "Meta": {
      "Columns": [ "filename", "date", "permissions", "owner" ]
    }
  }
I'm not suggesting that data should be JSON formatted though. Nor even have fields identical to the above. But a standard agreed base so that applications can understand what the pipe is without the developers having to write a thousand different case statements for each different stdmeta content but while still allowing some enhanced functionality for common commands.

In an idea world this would be an API rather than file descriptor but that would be backwards incompatible and even the FD use case feels like nice thing that is always going to be out of reach. :(

Thanks for sharing that by the way. I hadn't seen that post before.

Post reply on HN