6 ms·

I've been noticing more swiss army-knife-like cli tools in the last few years. It would be cool if there were some that could support avro/parquet/orc formats.

I've been noticing more swiss army-knife-like cli tools in the last few years. It would be cool if there were some that could support avro/parquet/orc formats. This one is notable because it's written in D lang by a mega corp. Some useful cli data wrangling tools -- https://github.com/BurntSushi/xsv https://github.com/BurntSushi/xsv https://github.com/dinedal/textql https://github.com/dinedal/textql https://github.com/n3mo/data-science https://github.com/n3mo/data-science https://stedolan.github.io/jq/ https://stedolan.github.io/jq/ https://gitlab.redox-os.org/redox-os/parallel https://gitlab.redox-os.org/redox-os/parallel https://github.com/willghatch/racket-rash https://github.com/willghatch/racket-rash Would you have any others you recommend?

j88439h84 7y ago
If you know a bit of Python, you can use it directly on streams in your shell. https://github.com/python-mario/mario https://github.com/python-mario/mario
- agumonkey 7y ago
  if one gets to port it to java, wario will make a great cousin
mechatroner 7y ago
RBQL: https://github.com/mechatroner/RBQL https://github.com/mechatroner/RBQL - SQL dialect with Python or JS expressions. I am the author, BTW
IanCal 7y ago
Big fan of visidata https://visidata.org/ https://visidata.org/ - one of those tools that does a lot but easily provides useful things with little complexity. For example open, select column, shift-f gives a histogram. Enter on one of those rows filters the data to just them. Can load a variety of data formats + anything pandas can (so parquet works). Edit - you can do a lot with it, quickly creating columns with python expressions, plots, joining tables, etc. but it's great for things like "pipe in kubectl output - get numbers of each job status, wait why are 10 failed, filter to those..." which easily works with any fixed width output.
- DSingularity 7y ago
  I’ve been looking for exactly this!
- austinjp 7y ago
  Thanks for this. Looks perfect for my needs. I've got to analyse a hideous generated CSV file with thousands of columns. I was planning to load it into SQLite3 but it has too many columns for import. I was also unpleasantly surprised to find that the column names were too long for PostegresQL too. Python + pandas can handle it, but a tool for quick interactive exploration is just what I need. Hopefully visidata will provide!
  - IanCal 7y ago
    If you still hit size issues, you might be able to throw things through xsv first (list columns, select just a few columns and pipe out, get stats on contents of columns, samples, etc). It's one of my other goto things https://github.com/BurntSushi/xsv https://github.com/BurntSushi/xsv
mbostock 7y ago
I wrote one for working with newline-delimited JSON and JavaScript expressions: https://github.com/mbostock/ndjson-cli https://github.com/mbostock/ndjson-cli
Arkanosis 7y ago
http://xmlstar.sourceforge.net/ http://xmlstar.sourceforge.net/
sstephenson 7y ago
jwalk parses JSON into a stream of TSV records: https://github.com/shellbound/jwalk/ https://github.com/shellbound/jwalk/
- uhhyeahdude 7y ago
  CRUSH looks interesting, but you’ll have to poke around to find out what the tool(s) can do: https://github.com/google/crush-tools/blob/wiki/CrushTutorial.md https://github.com/google/crush-tools/blob/wiki/CrushTutoria...
- DonHopkins 7y ago
  >parses large documents slowly, but steadily, in memory space proportional to the key depth of the document If parsing JSON with shell scripts and awk is your idea of the most ideal way to "slowly, but steadily" get the job done. https://github.com/shellbound/jwalk/blob/master/lib/jwalk/commands/parse.awk#L137 https://github.com/shellbound/jwalk/blob/master/lib/jwalk/co... I know that everything looks like a nail if your only tool is a hammer, and it's fun to nail together square wheels out of plywood, but there are actually other tools out there with built-in, compiled, optimized, documented, tested, well maintained, fully compliant JSON parsers.
- bradknowles 7y ago
  Gron -- Make JSON Greppable https://github.com/TomNomNom/gron https://github.com/TomNomNom/gron
mirashii 7y ago
https://github.com/dflemstr/rq https://github.com/dflemstr/rq jq but supports avro, messagepack, protocol buffers, etc.
kasperset 7y ago
https://www.gnu.org/software/datamash/ https://www.gnu.org/software/datamash/
vips7L 7y ago
PowerShell.
trimbo 7y ago
Crush Tools No idea how it compares to others but has been very handy. https://github.com/google/crush-tools https://github.com/google/crush-tools
akbo 7y ago
These tools probably do a good job at processing CSV/TSV/DSV (haven't tried them). However, I would love if we could just stop using delimiter-separated value files alltogether. Why? Because the file format is an underspecified, unsafe mess. When you get this kind of a file you have to manually specify its schema when reading it because the file doesn't contain it. Also, due to its underspecification, there are many unsafe implementations that produce broken files that cannot be read without manual fixing in a text editor. Let's just start using safe, well-specified file formats like AVRO, Parquet or ORC. As a data scientist, I have had lots of issues because the data I got for a project was a CSV/TSV/DSV file. I recently spat out a rant on this topic, so if you want more details, check out https://haveagooddata.net/posts/why-you-dont-want-to-use-csv-files/ https://haveagooddata.net/posts/why-you-dont-want-to-use-csv...
- mhd 7y ago
  > These tools probably do a good job at processing CSV/TSV/DSV (haven't tried them). However, I would love if we could just stop using delimiter-separated value files alltogether. I hear ya. I have no doubt that "we" could, as in IT professionals. Maybe even the "surrounding" science fields that provide data could make an effort. But you're out of luck when it comes to almost any other field that serves you data, in my experience. Any org that you can't even tell details about the CSV you want (what's the "C"? UTF8? Quoting?) will have no chance of providing you with something more complex. It's partly our fault: The tools we provide them with suck. Excel's CSV handling is atrocious. Salesforce and similar tools seem to spit out barely consistent data dumps. Sometimes I feel like 80% of the industry is dealing with sanitizing input.
- burntsushi 7y ago
  This is addressed in the xsv readme: https://github.com/BurntSushi/xsv#motivation https://github.com/BurntSushi/xsv#motivation We can't stop, because it's a de facto standard format for exchange with spreadsheet programs. So long as that's ubiquitous, we might as well write tools to make processing them easier. Also, I'm not sure why you called CSV unsafe. It's certainly the case that it's severely under-specified, but I don't think there's anything unsafe about it.
cube2222 7y ago
Another one is the recent https://github.com/cube2222/octosql/ https://github.com/cube2222/octosql/ with a sql-based approach (I'm one of the authors)
etatoby 7y ago
I recommend "gron" https://github.com/tomnomnom/gron https://github.com/tomnomnom/gron > gron transforms JSON into discrete assignments to make it easier to grep for what you want and see the absolute 'path' to it. It eases the exploration of APIs that return large blobs of JSON but have terrible documentation. ▶ gron "https://api.github.com/repos/tomnomnom/gron/commits?per_page=1" | fgrep "commit.author" json[0].commit.author = {}; json[0].commit.author.date = "2016-07-02T10:51:21Z"; json[0].commit.author.email = "mail@tomnomnom.com"; json[0].commit.author.name = "Tom Hudson";
unhammer 7y ago
My go-to tools: * vd (visidata) * xsv (often piping into vd) * jq (though I often forget the syntax =P) * gron (nice for finding the json-path to use in the jq script) * xmlstarlet (think jq for xml) * awk (or mawk when I need even more speed) * GNU parallel (or just xargs for simple stuff)
mooss 7y ago
https://github.com/johnkerl/miller https://github.com/johnkerl/miller Miller (mlr) is now my goto tool for csv/tsv manipulation. I've used it from time to time during the past year and it's been a joy to use. I must admit that I did not try any other tool for the purpose so I don't know how it compares. It has a nice verb system, verbs can be combined with "then" statements : https://johnkerl.org/miller/doc/reference-verbs.html https://johnkerl.org/miller/doc/reference-verbs.html . There is also a small DSL to use with the "put" and "filter" verbs: https://johnkerl.org/miller/doc/reference-dsl.html https://johnkerl.org/miller/doc/reference-dsl.html . Another verb I find very useful is "join", which does pretty much what you would expect it to do.
petre 7y ago
Miller https://github.com/johnkerl/miller https://github.com/johnkerl/miller I'd normally pass but this is written in D so I'm looking forward to try it out.

j88439h84 7y ago

If you know a bit of Python, you can use it directly on streams in your shell. https://github.com/python-mario/mario https://github.com/python-mario/mario

agumonkey 7y ago
if one gets to port it to java, wario will make a great cousin

mechatroner 7y ago

RBQL: https://github.com/mechatroner/RBQL https://github.com/mechatroner/RBQL - SQL dialect with Python or JS expressions. I am the author, BTW

IanCal 7y ago

Big fan of visidata https://visidata.org/ https://visidata.org/ - one of those tools that does a lot but easily provides useful things with little complexity. For example open, select column, shift-f gives a histogram. Enter on one of those rows filters the data to just them. Can load a variety of data formats + anything pandas can (so parquet works). Edit - you can do a lot with it, quickly creating columns with python expressions, plots, joining tables, etc. but it's great for things like "pipe in kubectl output - get numbers of each job status, wait why are 10 failed, filter to those..." which easily works with any fixed width output.

DSingularity 7y ago
I’ve been looking for exactly this!
austinjp 7y ago
Thanks for this. Looks perfect for my needs. I've got to analyse a hideous generated CSV file with thousands of columns. I was planning to load it into SQLite3 but it has too many columns for import. I was also unpleasantly surprised to find that the column names were too long for PostegresQL too. Python + pandas can handle it, but a tool for quick interactive exploration is just what I need. Hopefully visidata will provide!
- IanCal 7y ago
  If you still hit size issues, you might be able to throw things through xsv first (list columns, select just a few columns and pipe out, get stats on contents of columns, samples, etc). It's one of my other goto things https://github.com/BurntSushi/xsv https://github.com/BurntSushi/xsv

mbostock 7y ago

I wrote one for working with newline-delimited JSON and JavaScript expressions: https://github.com/mbostock/ndjson-cli https://github.com/mbostock/ndjson-cli

Arkanosis 7y ago

http://xmlstar.sourceforge.net/ http://xmlstar.sourceforge.net/

sstephenson 7y ago

jwalk parses JSON into a stream of TSV records: https://github.com/shellbound/jwalk/ https://github.com/shellbound/jwalk/

uhhyeahdude 7y ago
CRUSH looks interesting, but you’ll have to poke around to find out what the tool(s) can do: https://github.com/google/crush-tools/blob/wiki/CrushTutorial.md https://github.com/google/crush-tools/blob/wiki/CrushTutoria...
DonHopkins 7y ago
>parses large documents slowly, but steadily, in memory space proportional to the key depth of the document If parsing JSON with shell scripts and awk is your idea of the most ideal way to "slowly, but steadily" get the job done. https://github.com/shellbound/jwalk/blob/master/lib/jwalk/commands/parse.awk#L137 https://github.com/shellbound/jwalk/blob/master/lib/jwalk/co... I know that everything looks like a nail if your only tool is a hammer, and it's fun to nail together square wheels out of plywood, but there are actually other tools out there with built-in, compiled, optimized, documented, tested, well maintained, fully compliant JSON parsers.
bradknowles 7y ago
Gron -- Make JSON Greppable https://github.com/TomNomNom/gron https://github.com/TomNomNom/gron

mirashii 7y ago

https://github.com/dflemstr/rq https://github.com/dflemstr/rq jq but supports avro, messagepack, protocol buffers, etc.

kasperset 7y ago

https://www.gnu.org/software/datamash/ https://www.gnu.org/software/datamash/

vips7L 7y ago

PowerShell.

trimbo 7y ago

Crush Tools No idea how it compares to others but has been very handy. https://github.com/google/crush-tools https://github.com/google/crush-tools

akbo 7y ago

These tools probably do a good job at processing CSV/TSV/DSV (haven't tried them). However, I would love if we could just stop using delimiter-separated value files alltogether. Why? Because the file format is an underspecified, unsafe mess. When you get this kind of a file you have to manually specify its schema when reading it because the file doesn't contain it. Also, due to its underspecification, there are many unsafe implementations that produce broken files that cannot be read without manual fixing in a text editor. Let's just start using safe, well-specified file formats like AVRO, Parquet or ORC. As a data scientist, I have had lots of issues because the data I got for a project was a CSV/TSV/DSV file. I recently spat out a rant on this topic, so if you want more details, check out https://haveagooddata.net/posts/why-you-dont-want-to-use-csv-files/ https://haveagooddata.net/posts/why-you-dont-want-to-use-csv...

mhd 7y ago
> These tools probably do a good job at processing CSV/TSV/DSV (haven't tried them). However, I would love if we could just stop using delimiter-separated value files alltogether. I hear ya. I have no doubt that "we" could, as in IT professionals. Maybe even the "surrounding" science fields that provide data could make an effort. But you're out of luck when it comes to almost any other field that serves you data, in my experience. Any org that you can't even tell details about the CSV you want (what's the "C"? UTF8? Quoting?) will have no chance of providing you with something more complex. It's partly our fault: The tools we provide them with suck. Excel's CSV handling is atrocious. Salesforce and similar tools seem to spit out barely consistent data dumps. Sometimes I feel like 80% of the industry is dealing with sanitizing input.
burntsushi 7y ago
This is addressed in the xsv readme: https://github.com/BurntSushi/xsv#motivation https://github.com/BurntSushi/xsv#motivation We can't stop, because it's a de facto standard format for exchange with spreadsheet programs. So long as that's ubiquitous, we might as well write tools to make processing them easier. Also, I'm not sure why you called CSV unsafe. It's certainly the case that it's severely under-specified, but I don't think there's anything unsafe about it.

cube2222 7y ago

Another one is the recent https://github.com/cube2222/octosql/ https://github.com/cube2222/octosql/ with a sql-based approach (I'm one of the authors)

etatoby 7y ago

I recommend "gron" https://github.com/tomnomnom/gron https://github.com/tomnomnom/gron > gron transforms JSON into discrete assignments to make it easier to grep for what you want and see the absolute 'path' to it. It eases the exploration of APIs that return large blobs of JSON but have terrible documentation. ▶ gron "https://api.github.com/repos/tomnomnom/gron/commits?per_page=1" | fgrep "commit.author" json[0].commit.author = {}; json[0].commit.author.date = "2016-07-02T10:51:21Z"; json[0].commit.author.email = "mail@tomnomnom.com"; json[0].commit.author.name = "Tom Hudson";

unhammer 7y ago

My go-to tools: * vd (visidata) * xsv (often piping into vd) * jq (though I often forget the syntax =P) * gron (nice for finding the json-path to use in the jq script) * xmlstarlet (think jq for xml) * awk (or mawk when I need even more speed) * GNU parallel (or just xargs for simple stuff)

mooss 7y ago

https://github.com/johnkerl/miller https://github.com/johnkerl/miller Miller (mlr) is now my goto tool for csv/tsv manipulation. I've used it from time to time during the past year and it's been a joy to use. I must admit that I did not try any other tool for the purpose so I don't know how it compares. It has a nice verb system, verbs can be combined with "then" statements : https://johnkerl.org/miller/doc/reference-verbs.html https://johnkerl.org/miller/doc/reference-verbs.html . There is also a small DSL to use with the "put" and "filter" verbs: https://johnkerl.org/miller/doc/reference-dsl.html https://johnkerl.org/miller/doc/reference-dsl.html . Another verb I find very useful is "join", which does pretty much what you would expect it to do.

petre 7y ago

Miller https://github.com/johnkerl/miller https://github.com/johnkerl/miller I'd normally pass but this is written in D so I'm looking forward to try it out.