4 ms·

Having spent way too much time wrangling vendor-provided CSVs, I 100% agree. I'd love for there to be a common, well-understood format for typed tabular data th

by paulgb 6y ago

Having spent way too much time wrangling vendor-provided CSVs, I 100% agree. I'd love for there to be a common, well-understood format for typed tabular data that supports multiple tables and enforces foreign keys between them. Ideally with a concept of "patching" to enable incremental updates. Probably the closest thing I'm aware of is handing around a sqlite file, but I'm a little uneasy using a format that's meant to be a database as a transfer format. Dolt looks promising here too. Are there other ways?

itroot 6y ago
https://www.sqlite.org/appfileformat.html https://www.sqlite.org/appfileformat.html - it is OK to use sqlite that way! =)
mildbyte 6y ago
The Splitgraph core code on GitHub [0], around which we've built the DDN, is all about managing "data images" which are basically snapshots of PostgreSQL schemata. You can build them with a format similar to Dockerfiles as well as do a "checkout" into a local instance of Splitgraph (which you can connect to with any PG client) -- this enables change tracking and delta compression too. Behind the scenes, we store them as cstore_fdw [2] files which is a columnar storage format that helps with analytical queries. [0] https://github.com/splitgraph/splitgraph/ https://github.com/splitgraph/splitgraph/ [1] https://splitgraph.com/docs/concepts/images https://splitgraph.com/docs/concepts/images [2] https://github.com/citusdata/cstore_fdw https://github.com/citusdata/cstore_fdw
Fiahil 6y ago
At work, we use Parquet (https://parquet.apache.org/ https://parquet.apache.org/) for almost everything related to a dataframe. We don't really care about performance gains (although, it's nice to have), but we really like to have a schema. Note, we use mostly Python, some R, and a various range of ML or Optimisation tools, depending on the project.
- jeremyjh 6y ago
  Does Parquet provide a way to define foreign keys between different tables in a single dataset?
  - xyzzy_plugh 6y ago
    Parquet files don't reference anything outside of the file, usually. A group of parquet files in a folder is usually considered a table, where the schema is union of the schema of the files.
- xyzzy_plugh 6y ago
  Parquet is kind of a royal pain in the ass compared to CSV/JSON/plaintext mostly because it uses a ton of Thrift encodings, resulting in mostly terrible/broken implementations anywhere outside of the Java/JVM ecosystem. If you're running Apache <Whatever> then sure, it'll probably be fine, but I'd recommend avoiding it if you start having to go down the rabbit hole of implementing support for things in your language du jour.
  - Fiahil 6y ago
    The Rust and python impl are fine. But I get it, Parquet may not be perfect or optimal or whatever. It works as a simple, typed, columnar format. We had to pick a single file format recommendation for sending 100GB+ tables on FTP servers or dropbox, scanning terabytes of useless stuff only to grap an key-value pair, and properly reading integer and UTF-8 columns. Turns out, Parquet is practical. Enough for users to start using it instead of CSV. It could be Avro, but it's just not as easy.
    - xyzzy_plugh 6y ago
      > But I get it, Parquet may not be perfect or optimal or whatever. I actually think Parquet is pretty great in practice, I just have some issues with the sheer volume of abstractions necessary to implement it. I just wish it was anything other than Thrift. I would probably choose Parquet over anything else, though.
ForHackernews 6y ago
Avro is a strong contender for this use case: https://avro.apache.org/docs/current/ https://avro.apache.org/docs/current/ https://medium.com/ssense-tech/csv-vs-parquet-vs-avro-choosing-the-right-tool-for-the-right-job-79c9f56914a8 https://medium.com/ssense-tech/csv-vs-parquet-vs-avro-choosi... It's well-supported in Pandas and Kafka, has good schema support, and reasonably small compressed file sizes.
pletnes 6y ago
Seconding parquet and sqlite, but hdf5 and netcdf4 certainly deserve a mention, especially for multidimensional, scientific, and generally large datasets.
ris 6y ago
See "CSV on the Web": https://www.w3.org/TR/tabular-data-primer/ https://www.w3.org/TR/tabular-data-primer/

by paulgb 6y ago

itroot 6y ago

https://www.sqlite.org/appfileformat.html https://www.sqlite.org/appfileformat.html - it is OK to use sqlite that way! =)

mildbyte 6y ago

The Splitgraph core code on GitHub [0], around which we've built the DDN, is all about managing "data images" which are basically snapshots of PostgreSQL schemata. You can build them with a format similar to Dockerfiles as well as do a "checkout" into a local instance of Splitgraph (which you can connect to with any PG client) -- this enables change tracking and delta compression too. Behind the scenes, we store them as cstore_fdw [2] files which is a columnar storage format that helps with analytical queries. [0] https://github.com/splitgraph/splitgraph/ https://github.com/splitgraph/splitgraph/ [1] https://splitgraph.com/docs/concepts/images https://splitgraph.com/docs/concepts/images [2] https://github.com/citusdata/cstore_fdw https://github.com/citusdata/cstore_fdw

Fiahil 6y ago

At work, we use Parquet (https://parquet.apache.org/ https://parquet.apache.org/) for almost everything related to a dataframe. We don't really care about performance gains (although, it's nice to have), but we really like to have a schema. Note, we use mostly Python, some R, and a various range of ML or Optimisation tools, depending on the project.

jeremyjh 6y ago
Does Parquet provide a way to define foreign keys between different tables in a single dataset?
- xyzzy_plugh 6y ago
  Parquet files don't reference anything outside of the file, usually. A group of parquet files in a folder is usually considered a table, where the schema is union of the schema of the files.
xyzzy_plugh 6y ago
Parquet is kind of a royal pain in the ass compared to CSV/JSON/plaintext mostly because it uses a ton of Thrift encodings, resulting in mostly terrible/broken implementations anywhere outside of the Java/JVM ecosystem. If you're running Apache <Whatever> then sure, it'll probably be fine, but I'd recommend avoiding it if you start having to go down the rabbit hole of implementing support for things in your language du jour.
- Fiahil 6y ago
  The Rust and python impl are fine. But I get it, Parquet may not be perfect or optimal or whatever. It works as a simple, typed, columnar format. We had to pick a single file format recommendation for sending 100GB+ tables on FTP servers or dropbox, scanning terabytes of useless stuff only to grap an key-value pair, and properly reading integer and UTF-8 columns. Turns out, Parquet is practical. Enough for users to start using it instead of CSV. It could be Avro, but it's just not as easy.
  - xyzzy_plugh 6y ago
    > But I get it, Parquet may not be perfect or optimal or whatever. I actually think Parquet is pretty great in practice, I just have some issues with the sheer volume of abstractions necessary to implement it. I just wish it was anything other than Thrift. I would probably choose Parquet over anything else, though.

ForHackernews 6y ago

Avro is a strong contender for this use case: https://avro.apache.org/docs/current/ https://avro.apache.org/docs/current/ https://medium.com/ssense-tech/csv-vs-parquet-vs-avro-choosing-the-right-tool-for-the-right-job-79c9f56914a8 https://medium.com/ssense-tech/csv-vs-parquet-vs-avro-choosi... It's well-supported in Pandas and Kafka, has good schema support, and reasonably small compressed file sizes.

pletnes 6y ago

Seconding parquet and sqlite, but hdf5 and netcdf4 certainly deserve a mention, especially for multidimensional, scientific, and generally large datasets.

ris 6y ago

See "CSV on the Web": https://www.w3.org/TR/tabular-data-primer/ https://www.w3.org/TR/tabular-data-primer/