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

31–40 of 52 posts

Re: The CSV Virtual Table

#31

Post by Erwin »

Another cool thing about sqlite is that the virtual tables seen here can be extended through your own custom code.

You can then run SQL queries that in the end call your own data functions to generate data. I had a project where I had several diverse data files (each with its own set of functions to iterate over, but no useful querying) where wrapping each data file with a SQL Virtual table allowed med to use SQL to join them up and query them.

Here's a 2014 article where the author exposes redis as a virtual table within your sqlite code using this mechanism: https://charlesleifer.com/blog/extending-sqlite-with-python/

Postgres has some support for this (you run your SQL code but it really talks to some other service, or another postgres database) through foreign data wrappers. There's some Python library to also make adding that easy.

Interestingly a few projects do it in reverse: you are talking the postgres protocol (using library, psql client) to your database server, but it's not postgres but something else entirely (CockroachDB does this).

I like the idea of the entire world being just SQL, but you have to be careful with those wrappers and expose any indexing you might have, otherwise it's lots of expensive table scans.

Re: The CSV Virtual Table

#32

Post by habibur »

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.

Re: The CSV Virtual Table

#34

Post by jasode »

tantalor wrote: post #33

What about this is "virtual" and not a regular table?

>What about this is "virtual" and not a regular table?

The article's first sentence describes what makes it "virtual". Note the "as if" in the fragment:

>, and returns that content _as_ _if_ it were rows and columns of an SQL table.

In other words, instead of creating a real SQLite empty table and then doing bulk INSERTs or ".import" from the csv file, you treat the existing the .csv on disk _as_if_ you did that. The csv is treated as a "virtual" table you can execute SQL queries against.

Compare that to the alternative way of using SQLite ".import" command : https://stackoverflow.com/questions/14947916/import-csv-to-s...

Re: The CSV Virtual Table

#36

Post by frabert »

punnerud wrote: post #30

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

Re: The CSV Virtual Table

#37

Post by tantalor »

jasode wrote: post #34
tantalor wrote: post #33

What about this is "virtual" and not a regular table?

>What about this is "virtual" and not a regular table? The article's first sentence describes what makes it "virtual". Note the "as if" in the fragment: >, and returns that content _as_ _if_ it were rows and columns of an SQL table. In other words, instead of creating a real SQLite empty table and then doing bulk INSERTs or ".import" from the csv file, you treat the existing the .csv on disk _as_if_ you did that. The…

Is that an important difference? Who cares how the table is created.

Is it a table or not?

Re: The CSV Virtual Table

#38

Post by giraffe_lady »

habibur wrote: post #32

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.

Interesting I think that makes it the first mainstream real world CSV tool I've used that is truly comma separated values then, rather than delimiter-separated-defaulting-to-comma.

Re: The CSV Virtual Table

#39

Post by jasode »

tantalor wrote: post #37
jasode wrote: post #34

Earlier quoted context omitted.

>What about this is "virtual" and not a regular table? The article's first sentence describes what makes it "virtual". Note the "as if" in the fragment: >, and returns that content _as_ _if_ it were rows and columns of an SQL table. In other words, instead of creating a real SQLite empty table and then doing bulk INSERTs or ".import" from the csv file, you treat the existing the .csv on disk _as_if_ you did that. The…

Is that an important difference? Who cares how the table is created. Is it a table or not?

>Is that an important difference? Who cares how the table is created.

You seem to be argumentative instead of trying to understand why they called it "virtual".

I'll try with another example. Let's pretend you have a 10 gigabyte csv file on disk::

Using a real SQLite table:

  CREATE TABLE t1 (...);    -- an empty db file of 8192 bytes
  .import thefile.csv t1    -- total disk space consumed now 20 GB because .db file is now +10 GB larger
  SELECT FROM t1;   -- SQLite reads from .db file and not the .csv file
Using a "virtual" SQLite table:

  CREATE VIRTUAL TABLE temp.t1 USING csv(filename='thefile.csv'); -- disk space stays the same
  SELECT from t1;   -- SQLite directly reads from csv disk file
>Is it a table or not?

At the risk of stating the obvious, the extra "VIRTUAL" keyword in between "CREATE" and "TABLE" thus makes a virtual table.

It is a "table" in the sense that it "acts like a table" via an interface for SQL queries. However, it's not a real table in the sense that there's no data blocks within the .db that represents that table. It's virtual.

I'm not understanding what annoys you about their description.

Re: The CSV Virtual Table

#40

Post by giraffe_lady »

tantalor wrote: post #37
jasode wrote: post #34

Earlier quoted context omitted.

>What about this is "virtual" and not a regular table? The article's first sentence describes what makes it "virtual". Note the "as if" in the fragment: >, and returns that content _as_ _if_ it were rows and columns of an SQL table. In other words, instead of creating a real SQLite empty table and then doing bulk INSERTs or ".import" from the csv file, you treat the existing the .csv on disk _as_if_ you did that. The…

Is that an important difference? Who cares how the table is created. Is it a table or not?

I think you might be getting mixed up between the query language and the implementation. Virtual tables define an interface that you can use to write an extension allowing you to query resources using sql semantics. The underlying representation of those resources doesn't change.

So yeah the difference matters depending on your use. I don't think you can add indexes for example, unless they are specifically supported by the underlying resource, or implemented in the virtual table extension you're using.

It's not a table.

Post reply on HN