Integration with flatterer / csvs_convert

[kindly]

There are two libraries that I maintain that have a strong overlap and could potentially be integregrated with qsv. Both of these are rust libraries but currenly do not have rust CLI. I just descovered qsv and am wondering if it would be better to add them to qsv instead of making a seperate CLI for them.

flatterer

This flattens deeply nested JSON by producing muliple CSV files, one for each one-to-many (array of objects) relationship. It also outputs to other formats SQLITE/PARQUET/POSTGRES/XLSX.

csvs_convert - rust library

This is used by the above, but is a seperate library, to convert CSV files to SQLITE/PARQUET/POSTGRES/XLSX efficiently. It has its own type guesser (like the qsv stats command) but produces a datapackage.json as the metadata format instead of jsonschema (this is because foreign key relationships can be described in datapackages). One of the next things for this library was to provide more stats like the stats command. I also wonder here if there is any collaboration that could go on so that type guessing and stats parts could be shared.

So it would be great to have opinions on if integrating these libraries into new qsv commands is a good idea?

The main drawbacks are is they are fairly heavy in terms of dependancies and both sqlite and xlsx output require c binding (as the c libraries are the fastest and most fully featured, rustqlite and libxlsxwriter). I could easily add feature flags to remove these though. Mostly they compile easily though as the it uses non system versions of these dependancies.

[jqnatividad]

Yes @kindly , I would love to integrate flatterer and csvs_convert into qsv and get to output to other formats beyond CSV/TSV and JSONL.

Enabling the creation of datapackage files thru the csvs_convert library would be a welcome addition as well! (A little sidenote, I was originally looking to use JSON Table Schema when we first started implementing the schema command, but the only viable crate we saw that can we can leverage didn't support it.)

As for "heavy" dependencies, I'm not as concerned, so long as that at the end of the day, we can produce binaries for Linux, MacOS and Windows, and Homebrew can continue to package it up for quick installation.

As it is, qsv has some "heavy" dependencies with c bindings - it uses the mimalloc allocator by default via the mimalloc_rust crate; and the luau command which uses luau via the mlua crate.

Like you suggested, we can manage these dependencies across the different target platform by making judicious use of feature flags and accounting for them in the publishing GH Action workflows.

[jqnatividad]

@kindly to/from is a good idea - short, sweet and to the point. And yes, we can eventually move tojsonl too.

And there's no need to use qsv's stats functionality - we can use csvs_convert's type-guessing.

We just took advantage of stats as we needed a way to infer for the schema command...

So do you want to start drafting the usage text for the to command?

When creating a new command, I usually start by duplicating an existing command with a similar interface, and modifying it from there. Right now, apply is the only command with subcommands, but I wouldn't recommend using that as a jumping point as its a bit convoluted. search would be a good one to use as a template, as it's fairly short and has all the common options along with the progressbar.

[kindly]

I am fairly busy this week but will try when I have the time. I have coppied over search cmd and am reading up on docopt.

I think I will start with to postgres as it is the most tricky as it involves connections strings (not files) and is the most strict about what data it allows.

I am not sure how feesable a progress bar will be for some of the conversions. For example, in postgres I use the copy command as it is much faster than inserts. csvs_converts job is to make the table definition in a way that copy will always accept and then stream the data from the file to postgres. Spliting this stream into different copy commands is slower as it streaming at the bytes level not as CSV row level so you can not easily split the stream. I want to avoid having rust parse the csv, just so that postgres can do it again.
It might be possible though to get the file size and use a customised Read object that takes the progress bar and can do progress based on file size.

[jqnatividad]

Sounds like a plan.
I'm a big fan of Postgres COPY - that's the main reason why Datapusher+ is so much faster than regular Datapusher which inserts a CSV record-by-record via the CKAN Datastore API.

Similar to how csv_converts infers the table definition, I use qsv stats in DP+ as well to create the SQL DDL with its guaranteed data type inferences, before COPYing the CSV directly into it - bypassing the CSV parsing and messytables with its somewhat unreliable data type guesses... CKAN stuff that I think you're familiar with 😉

[kindly]

@jqnatividad ha, I am partially responsible for messytables, or a wrote a prototype and someone else made it real. I remember discussing how the type checking should work but never really played a part in its implementation. Since then I always wanted to write another version of it with the type checking how I wanted and csvs_convert is nearly there..
I have lots of ideas in this area, which I think could be looked into, that nobody does that well.

Also by the way I was lead develepor on CKAN when working at OKF also, and was responsible for the inital version of CKAN in data.gov. This feels like a very very long time ago now.

[jqnatividad]

Small world!
I'm glad we've made the connection with our shared fascination (some may say obsession) with CSVs 😄

You'll be glad to know that CKAN 3.0 is shaping up, and we and our partners at University of Pittsburgh just got a grant to ensure the civic data ecosystem, with CKAN at its heart, becomes more robust and vibrant going forward.

Would love to compare notes and pick your brain one of these days as we conduct our research...

[kindly]

@jqnatividad thanks for merging! Your changes look good.

I will try to add some testing next. For my other projects I am using a testing library called:

https://docs.rs/insta/latest/insta/

It is by far my favourite way to test output and would make adding tests for this much easier as I already have them in csvs_convert.
However, I do not want to start down that route without your consent. I do recommend it though as it makes testing data outputs really much more pleasent.

Also I am going to get csvs_convert to detect and use index files.

[jqnatividad]

insta.rs looks good!

As you may have noticed, I often point to the tests as extended examples in the docopt usage text. So as long as the test makes sense to a regular qsv user, go for it!

And yeah, anything to make qsv commands faster... as speed, apart from composability and simplicity, is a main goal of qsv.

Speaking of composability, once the tests are setup, maybe we can revisit that so users can pipe CSVs to to...

[kindly]

I am not sure of the feesability of piping/streaming CSV files to these conversions. The design of them is so that as long as you have a well formed CSV file they will always work no matter the contents of the data. This is particularly tricky for postgres and parquet as they need to type information up front. So the approach is to analyse the whole file upfront (which is pretty quick). Just sniffing the first x lines of the stream for types (which is a common approach) can easily lead to errors and in my experiance normally does. It would be possible to stream if the user supplied a correct datapackage along with the file but I am not sure anyone would do that. We could also cheat and just save the stream to disk whilst analysing then load the saved file but that defeats the idea of the stream.

Also sreaming would work for only single files and these commands are useful when supplying multiple.

There may be some workarounds for postgres/sqlite using the evolve feature so we save chunks of the stream to disk and then alter the db schema if there is a change of type. This would require quite a bit of work though.

[jqnatividad]

Thanks @kindly for the thorough response.

And yeah, composability is a goal and not a must-have, and I wouldn't want to overly complicate the code just to support it, as the to command is a great addition to qsv as it stands.

BTW, on the postgres converter, have you tried it with AWS RDS? AWS RDS takes away the superuser role and replaces it with rds_superuser with more limited capabilities and COPY doesn't seem to work...

[kindly]

@jqnatividad I have not tried it, have you? My understanding is that COPY FROM STDIN should work for any user but COPY FROM "file.csv" requires superuser and will not work on AWS as you do not get disk access, hence the special superuser.

I am using COPY FROM STDIN and streaming the local file over STDIN for this reason. It "should" work but I currently do not have a billing account on AWS to test it.

[jqnatividad]

Yes. I actually use COPY FROM STDIN in Datapusher+, and it's been problematic with AWS RDS.

https://github.com/dathere/datapusher-plus/blob/e1ee9abe959b1397990f3f4e0356d9f6883fea34/datapusher/jobs.py#L739-L744

But another user says it works, so it may be just figuring out the GRANTs.