8 ms·

>The single SQL endpoint is well suited for a data marketplace. Data vendors currently ship data in CSV files or other ad-hoc formats. They have to maintain pag

>The single SQL endpoint is well suited for a data marketplace. Data vendors currently ship data in CSV files or other ad-hoc formats. They have to maintain pages of instructions on ingesting this data. With Splitgraph, data consumers will be able to acquire and interact with data directly from their applications and clients. I appreciate the effort to make it easier for users to access heterogeneous data sets, but I really hope data vendors keep shipping raw CSV files. I don't want a company to gate access to the data, merely offering a proxy. I want to be able to download the whole raw datasets from the vendor directly if I want to.

cbetti 6y ago
In my view our collective interest in CSV as a medium for data distribution has resulted in far too much information loss, and consequently, time wasted on input sanitization, validity checking, and unresolvable conversations about the intent of data values like ”1.12345E+11” and "".
- emersion 6y ago
  CSV is just an example, data vendors can choose e.g. JSON if they want more a sane and well-defined format.
  - pletnes 6y ago
    Json has many design flaws, e.g. support for large integers, floating point infinity and NaN, for instance.
    - shawnz 6y ago
      It is not a design flaw to make a reasonable choice about what data types you support. Especially given the ones captured in JSON are overwhelmingly the most commonly used and necessary types. In cases where that's not enough, you could roll your own types by putting the values in plain strings and it would still be strictly more expressive than CSV
  - still_grokking 6y ago
    Regarding "sane & well-defined": http://seriot.ch/parsing_json.php http://seriot.ch/parsing_json.php
- 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?
- hodgesrm 6y ago
  The thing that attracted me to SplitGraph from the very start is that they are proposing to make the PostgreSQL wire protocol and SQL dialect a general interface to remote data. The interface is not only well known but backed by permissively licensed, open source libraries. Plus there are hundreds of tools that already connect to PostgreSQL. This idea makes such sense it's a little surprising nobody did it before.
  - johnthescott 6y ago
    run-away queries become a problem when direct sql is exposed to public.
- rrrrrrrrrrrryan 6y ago
  Both JSON and XML are self-documenting, and most modern databases support directly importing and exporting them. Though the tools to accomplish this could be better, these formats are far better suited as a "medium for data distribution" than CSV files are. That said, a simple compressed .sql file of INSERT statements can often go a long way. The only reason CSV is widely used is because normal people think of data as spreadsheets, and asking them to fire up a database and shred JSON data into it is ridiculous when they just want to whip up a line graph or answer a simple question (e.g. "What was value X on a this particular date?").
mildbyte 6y ago
Absolutely, having ability to download the actual data and keep it is always going to be important. We want to facilitate access to data and think it should be available from the source. But, there will inevitably be fragmentation, so it's valuable to have a service available to catalog and aggregate it and make it available over a single protocol. For what it's worth, we run PostgREST [0] on top of the DDN, so you can get your query results in JSON and CSV files. For example: $ curl -sSH "Content-Type: text/csv" https://data.splitgraph.com/cityofchicago/covid19-daily-cases-deaths-and-hospitalizations-naz8-j4nc/latest/-/rest/covid19_daily_cases_deaths_and_hospitalizations | wc -l 172 It's limited to 10k rows but we might have an ability in the future to "order" a CSV dump asynchronously and place in a destination (like an S3 bucket) of choice. [0] https://postgrest.org/en/latest/ https://postgrest.org/en/latest/
- atombender 6y ago
  Did you mean to use the "Accept" header? "Content-Type" describes the format of the request body.
  - mildbyte 6y ago
    Yeah, sorry, got too excited! It will return JSON by default and CSV with Accept: text/csv: $ curl -sH "Accept: text/csv" https://data.splitgraph.com/cityofchicago/covid19-daily-cases-deaths-and-hospitalizations-naz8-j4nc/latest/-/rest/covid19_daily_cases_deaths_and_hospitalizations?select=lab_report_date,cases_total,deaths_total | head -n5 lab_report_date,cases_total,deaths_total 2020-03-01,0,0 2020-03-02,0,0 2020-03-03,0,0 2020-03-04,0,0
    - atombender 6y ago
      Very nice!
- mbostock 6y ago
  Any chance you’re willing to enable CORS on these endpoints? (Access-Control-Allow-Origin: * response header.)
justinclift 6y ago
As an interchange format for database data, CSV is pretty terrible. There's no comprehensive single specification. The closest is an RFC which completely misses major pieces (eg handling of binary data, and more). Using SQLite as the interchange format is better. :)
- chrisweekly 6y ago
  IIUC, JSON is available too...
  - justinclift 6y ago
    That's a good point. Any idea if there's a well spec'd JSON format around, for database data? eg something that handles binary data, trinary logic (eg null values), and hopefully referential integrity
hodgesrm 6y ago
You can generate CSV if you need it. See psql --csv. [1] What's brilliant about this approach is that you can generate any format that's supported by the interface defined by the PostgreSQL SQL dialect and wire protocol. (Obvious caveats about timeouts, network bandwidth, etc. apply.) [1] https://www.postgresql.org/docs/12/app-psql.html https://www.postgresql.org/docs/12/app-psql.html

cbetti 6y ago

In my view our collective interest in CSV as a medium for data distribution has resulted in far too much information loss, and consequently, time wasted on input sanitization, validity checking, and unresolvable conversations about the intent of data values like ”1.12345E+11” and "".

emersion 6y ago
CSV is just an example, data vendors can choose e.g. JSON if they want more a sane and well-defined format.
- pletnes 6y ago
  Json has many design flaws, e.g. support for large integers, floating point infinity and NaN, for instance.
  - shawnz 6y ago
    It is not a design flaw to make a reasonable choice about what data types you support. Especially given the ones captured in JSON are overwhelmingly the most commonly used and necessary types. In cases where that's not enough, you could roll your own types by putting the values in plain strings and it would still be strictly more expressive than CSV
- still_grokking 6y ago
  Regarding "sane & well-defined": http://seriot.ch/parsing_json.php http://seriot.ch/parsing_json.php
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?
hodgesrm 6y ago
The thing that attracted me to SplitGraph from the very start is that they are proposing to make the PostgreSQL wire protocol and SQL dialect a general interface to remote data. The interface is not only well known but backed by permissively licensed, open source libraries. Plus there are hundreds of tools that already connect to PostgreSQL. This idea makes such sense it's a little surprising nobody did it before.
- johnthescott 6y ago
  run-away queries become a problem when direct sql is exposed to public.
rrrrrrrrrrrryan 6y ago
Both JSON and XML are self-documenting, and most modern databases support directly importing and exporting them. Though the tools to accomplish this could be better, these formats are far better suited as a "medium for data distribution" than CSV files are. That said, a simple compressed .sql file of INSERT statements can often go a long way. The only reason CSV is widely used is because normal people think of data as spreadsheets, and asking them to fire up a database and shred JSON data into it is ridiculous when they just want to whip up a line graph or answer a simple question (e.g. "What was value X on a this particular date?").

mildbyte 6y ago

Absolutely, having ability to download the actual data and keep it is always going to be important. We want to facilitate access to data and think it should be available from the source. But, there will inevitably be fragmentation, so it's valuable to have a service available to catalog and aggregate it and make it available over a single protocol. For what it's worth, we run PostgREST [0] on top of the DDN, so you can get your query results in JSON and CSV files. For example: $ curl -sSH "Content-Type: text/csv" https://data.splitgraph.com/cityofchicago/covid19-daily-cases-deaths-and-hospitalizations-naz8-j4nc/latest/-/rest/covid19_daily_cases_deaths_and_hospitalizations | wc -l 172 It's limited to 10k rows but we might have an ability in the future to "order" a CSV dump asynchronously and place in a destination (like an S3 bucket) of choice. [0] https://postgrest.org/en/latest/ https://postgrest.org/en/latest/

atombender 6y ago
Did you mean to use the "Accept" header? "Content-Type" describes the format of the request body.
- mildbyte 6y ago
  Yeah, sorry, got too excited! It will return JSON by default and CSV with Accept: text/csv: $ curl -sH "Accept: text/csv" https://data.splitgraph.com/cityofchicago/covid19-daily-cases-deaths-and-hospitalizations-naz8-j4nc/latest/-/rest/covid19_daily_cases_deaths_and_hospitalizations?select=lab_report_date,cases_total,deaths_total | head -n5 lab_report_date,cases_total,deaths_total 2020-03-01,0,0 2020-03-02,0,0 2020-03-03,0,0 2020-03-04,0,0
  - atombender 6y ago
    Very nice!
mbostock 6y ago
Any chance you’re willing to enable CORS on these endpoints? (Access-Control-Allow-Origin: * response header.)

justinclift 6y ago

As an interchange format for database data, CSV is pretty terrible. There's no comprehensive single specification. The closest is an RFC which completely misses major pieces (eg handling of binary data, and more). Using SQLite as the interchange format is better. :)

chrisweekly 6y ago
IIUC, JSON is available too...
- justinclift 6y ago
  That's a good point. Any idea if there's a well spec'd JSON format around, for database data? eg something that handles binary data, trinary logic (eg null values), and hopefully referential integrity

hodgesrm 6y ago

You can generate CSV if you need it. See psql --csv. [1] What's brilliant about this approach is that you can generate any format that's supported by the interface defined by the PostgreSQL SQL dialect and wire protocol. (Obvious caveats about timeouts, network bandwidth, etc. apply.) [1] https://www.postgresql.org/docs/12/app-psql.html https://www.postgresql.org/docs/12/app-psql.html