Miller: Like Awk, sed, cut, join, and sort for CSV, TSV, and tabular JSON
github.com
github.com
It does SQL; it supports all imaginable data formats, streaming processing, and connecting to external data sources. It also outperforms every other tool[2].
[1] https://clickhouse.com/blog/extracting-converting-querying-l...
[2] https://colab.research.google.com/github/dcmoura/spyql/blob/...
$ cat foo.tsv
name foo bar
Alice 10 8888
Bob 20 9999
$ cat foo.tsv | sqlite3 -batch \
-cmd ".mode tabs" \
-cmd ".import /dev/stdin x" \
-cmd "select foo from x where bar > 9000;"
20 select a,b,c from '*.jsonl.gz'
Jesus, this is disgusting. I'm not that picky and don't really complain about "... | sh" usually, but at least I took it for granted that I can always look at the script in the browser and assume that is has no actual evil intentions and doesn't rely on some fucking client-header magic to be modified on the fly.
curl https://clickhouse.com/ > install.sh
cat installThe underlying question is do you trust clickhouse.com or not? You don't have to; I've never met the team or talked to them, and I can't make that decision for you. But whether you go to the site, laboriously find the download page, right click, download a binary, install the deb/rpm, ask your package manager for what files just got installed, then find and run the clickhouse binary, or just let your computer do it for you via a shell script, the end result is the same. Code from clickhouse (and we're sure it was from clickhouse because of TLS) was downloaded to the target machine, and then got run. Does things have to be difficult and annoying in order for you to like it? (Psychology studies say yes, actually.)
yq: command-line YAML, JSON, XML, CSV and properties processor
https://news.ycombinator.com/item?id=34656022
Also mentions gojq, Benthos, xsv, Damsel, a 2nd yq, htmlq, cfn-flip, csvq, zq, and zsv.
APPlet Idea: an app that picks a random bookmark from your saves and sends you a reminder to click it and read it... and schedule when you want to see it - like every 7AM send me a link from my bookmarks. Set an alarm for 15 minutes to get me back on schedule.
I also discovered pawk on it which looks interesting, I made a somewhat related tool [1] which is probably out of scope for your list, but can be used in some of the same ways [2] and may of of interest nonetheless.
[1] https://github.com/elesiuta/pyxargs
[2] cat /etc/hosts | pyxargs -d \n --im json --pre "d={}" --post "print(dumps(d))" --py "d['{}'.split()[0]] = '{}'.split()[1]"
I also have https://github.com/01mf02/jaq#readme installed but just haven't needed it
New(ish) command line tools
Get-Content .\example.csv | ConvertFrom-Csv | Where-Object -Property color -eq red
Unfortunately after working with it for a few years I utterly despise PowerShell for many other reasons.
I've probably forgotten a few things.
The expressiveness is nice, but oftentimes modules won't support it or require weird ways of using the data to get the performance you want (mostly by dropping out of the pipe.)
The choices around Format- vs Out- vs Convert- are Very Confusing for new people and the "object in a shell but also text sometimes" way of displaying things is weird and until recently things like -NoTypeInformation or managing encoding in files was just pointlessly weird.
The module support and package management is still entirely in the stone ages and I regularly see people patching in C# in strings to get the behavior they want.
"Larger" modules tend to get Really Slow - the azure modules especially are just an example of how not to do it.
The way it automatically unwraps collections is cool, but gets weird when you have a 1 vs many option for your output, and you might find yourself defensively casting things to lists or w/e.
The typing system in general is nice to get started on but declaring a type does not fix it, so assignment can just break your entire world.
There's still a lot to love about the language when you are getting things done in a windows environment its great to glue together the various pieces of the system but I find the code "breaks" more often than equivalent python code.
PowerShell isn't even fast enough for basic interactive usage, never mind batch processing.
I appreciate that it auto-corrects capitalization and slash direction.
ipcsv example.csv | ? color -eq red
color shape flag index
----- ----- ---- -----
red square 1 15
red circle 1 16
red square 0 48
red square 0 7Searching for that comment, I came across relevant stories:
You’re right that standard Unix tools don’t have a concept of types in streams, but that decision got made deliberately. Types and formats got left as output details.
Analogously my Macbook Air doesn’t have a fan, by design, not by accidental omission.
Having human-readable text as the lowest common denominator is a laudable goal. Shell scripting would however probably be improved if most tools offered alternative typed streams, or something similar. I am not convinced Powershell's approach is the best, but their approach is at least interesting.
The original decision was about not proliferating specialized or proprietary binary formats, which was more of a norm back in the ‘70s than today. The goal was to make small single-purpose tools that communicated through a common interface (files) in a standard format (plain text). Unix succeeded and continues to succeed at that.
Nothing about those design decisions precludes tools using binary formats under Unix — image processing, for example. It just precludes using standard text-oriented tools on those formats.
It's one of the parents in that comment chain (top comment on the story): https://news.ycombinator.com/item?id=26791597
BUT, leaks memory like crazy.
Despite documentation stating the verbs are fully streaming.
> Fully streaming verbs > These don't retain any state from one record to the next. They are memory-friendly, and they don't wait for end of input to produce their output.
https://miller.readthedocs.io/en/6.7.0/streaming-and-memory/...
Huh?
a) Isn't it written in Golang, which has a GC? Does it do custom buffer based management?
b) Isn't it supposed to be run on a file and get some output - as opposed to an interactice session? Why would it matter if it leaks, then, and how could it leak, as the memory is returned to the OS when it ends?
Some gains were made on https://github.com/johnkerl/miller/pull/1133 and https://github.com/johnkerl/miller/pull/1132
Miller – tool for querying, shaping, reformatting data in CSV, TSV, and JSON - https://news.ycombinator.com/item?id=29651871 - Dec 2021 (33 comments)
Miller CLI – Like Awk, sed, cut, join, and sort for CSV, TSV and JSON - https://news.ycombinator.com/item?id=28298729 - Aug 2021 (66 comments)
Miller v5.0.0: Autodetected line-endings, in-place mode, user-defined functions - https://news.ycombinator.com/item?id=13751389 - Feb 2017 (20 comments)
Miller is like sed, awk, cut, join, and sort for name-indexed data such as CSV - https://news.ycombinator.com/item?id=10066742 - Aug 2015 (76 comments)
$ cat example.csv
color,shape,flag,index
yellow,triangle,1,11
red,square,1,15
red,circle,1,16
red,square,0,48
purple,triangle,0,51
red,square,0,77
# pretty printing
$ column -ts',' example.csv
color shape flag index
yellow triangle 1 11
red square 1 15
red circle 1 16
red square 0 48
purple triangle 0 51
red square 0 77
# sorting with skipped headers is a mess.
$ (head -n 1 example.csv && tail -n +2 example.csv | sort -r -k4 -t',') | column -ts','
color shape flag index
red square 0 77
purple triangle 0 51
red square 0 48
red circle 1 16
red square 1 15
yellow triangle 1 11I like these command line tools, but I think they can cripple someone actually learning programming language. For example, here is a short program that does your last example:
I like programming languages, but I think they can cripple someone actually learning Unix!
At the end of the day, you should just use whatever tools make you the most productive most quickly.
that's just it though, the last example is not a simple case, hence why the last example is awkward by the commenters own admission. command line tools are fine, but you need to know when to set the hammer down and pick up the chainsaw.
As far as shell scripting goes, this is hardly anything to write home about. Looks simple enough to me.
It just retains the header by printing the header first as is, and then sorting the lines after the header. It's immediately obvious how to do it to anybody who knows about head and tail.
And with Miller it's even simpler than that, still on the command line...
tail -n +2 example.csv | sort -r -k4 -t','
sort -r -k4 -t',' example.csv
import csv
filename = 'example.csv'
sort_by = 'index'
reverse = True
with open(filename) as f:
lines = [d for d in csv.DictReader(f)]
for line in lines:
line['index'] = int(line['index'])
lines.sort(key=lambda line: line[sort_by], reverse=reverse)
print(','.join(lines[0].keys()))
for line in lines:
print(','.join(str(v) for v in line.values())) import csv
import sys
filename = "example.csv"
sort_by = "index"
reverse = True
with open(filename, newline="") as f:
reader = csv.DictReader(f)
writer = csv.DictWriter(sys.stdout, fieldnames=reader.fieldnames)
writer.writeheader()
writer.writerows(sorted(reader, key=lambda row: int(row[sort_by]), reverse=reverse))But yours has the advantage of being able to support more complex CSVs.
(sed -u '1q' ; sort -r -k4 -t',') <example.csv | column -ts','Actually, `jq` can cope with trivial CSV input like your example, - `jq -R 'split(",")'` will turn a CSV into an array of arrays. To then sort it in reverse order by 3rd column and retain the header, the following fell out of my fingers (I'm beyond certain that a more skilled `jq` user than me could improve it):
jq -R 'split(",")' example.csv | jq -sr '[[.[0]],.[1:]|sort_by(.[4])|reverse|.[]]|.[]|@csv'
"color","shape","flag","index"
"red","square","0","77"
"purple","triangle","0","51"
"red","square","0","48"
"red","circle","1","16"
"red","square","1","15"
"yellow","triangle","1","11"
But seems like it cannot handle a simple use case: CSV without header.
$ mlr --csv head -n 20 pp-2002.csv
mlr: unacceptable empty CSV key at file "pp-2002.csv" line 1.
You have to explicitly pass it (FYI `implicit-csv-header` is terrible arg name)
$ mlr --csv --implicit-csv-header head -n 20 pp-2002.csv
While `head` obliges rightly
$ head -n 20 pp-2002.csv
Also head doesn't do anything with the data or the format, aside from printing line by line, so doesn't need to know any column names.
Miller is being offered as "like awk, sed, head.." (emphasis on head - mine) and yes it offers more, but it does not behave "like" the *nix tools it refers to.
It just reads lines, doesn't know anything about rows.
So if you want, say, the first 100 rows of a csv that has multiline rows, miller can give it, while head can't.
Head will just print the "N" first lines - whether those are 1, 22, 36 or N actual csv rows.
>Miller is being offered as "like awk, sed, head.." (emphasis on head - mine) and yes it offers more, but it does not behave "like" the nix tools it refers to.*
That's the whole idea.
That it behaves in a way more suited to the csv format, and more coherent than 5-6 different text-focused tools.
The claim is not "this is awk, sed, head, sort remade for csv with identical interfaces and behavior" but "this is a tool to work with csv files and do what you'd normally have to jump through hoops to do with awk, sed, head, sort which don't understand csv structure".
$ mlr -N --csv head -n 20 pp-2002.csv
-N is a shortcut for --implicit-csv-header and --headerless-csv-output
And it replied with
mlr --csv filter '$City == "Chicago" || $City == "Boston"' then cut -x -f State then put '$NewColumn = $Name . "_new"' test.csv > myfile.csv
I'm sure that someone familiar with Miller would write this command faster than writing the text to GPT, but as a newbie I'd have spent much longer. Also each steps is described perfectly
brew install csvkit and enjoy
That said, the most recent invocation for me was `mlr --icsv --ojson cat < a515b308-9a0e-4e4e-99a2-eafaa6159762.csv` to fix up CloudTrail csv and after that I happen to be more muscle-memory with jq but conceptually next I could have `filter '$eventname == "CreateNodegroup"'`
> "... ordering of key-value pairs"
order of appearance of key-value pairs in input does not matter.
> "... if row #100 introduces a new key-value pair"
this is sparse data. miller handles this with the "unsparsify" verb:
$ cat in.json
{ "a": 1, "b": 2 }
{ "a": 3, "b": 4 }
{ "a": 5, "b": 6, "c": 7 }
$ cat in.json | mlr --j2p cat
a b
1 2
3 4
a b c
5 6 7
$ cat in.json | mlr --j2p unsparsify then cat
a b c
1 2 -
3 4 -
5 6 7
$ cat in.json | mlr --j2p unsparsify --fill-with 0 then cat
a b c
1 2 0
3 4 0
5 6 7
Array value treatment seems to depend on output format. for output types that can represent arrays, they are preserved:
$ cat in-array.json
{ "a": [0,1,2], "b": [5,6,7] }
{ "a": [3,4,5], "b": [8,9,0] }
$ cat in-array.json | mlr --jsonl cat
{"a": [0, 1, 2], "b": [5, 6, 7]}
{"a": [3, 4, 5], "b": [8, 9, 0]}
$ cat in-array.json | mlr --j2p cat
a.1 a.2 a.3 b.1 b.2 b.3
0 1 2 5 6 7
3 4 5 8 9 0
$ cat in-array.json | mlr --j2p --flatsep _ cat
a_1 a_2 a_3 b_1 b_2 b_3
0 1 2 5 6 7
3 4 5 8 9 0That's the first question I have after reading the title. Haven't read the article.
Edited the first sentence. Originally it was "Why it is not called SQL if works on tabular data?".
My point, if one wants cat, sort, sed, join on tabular data, SOL is exactly that. Awk is too powerful, not sure about it.
2. Because it's closer to an amalgamation of the standard shell scripting tools (cut, sort, jq, etc) than it is to a SQL variant.
so, it remains simple and concise for the easy problems, yet can scale up to address the trickier ones as well.
i've used csvkit, sqlite, jq, etc. miller is my favorite tool for data-fu (with sqlite being a close 2nd)
It has far better handling of a CSV/TSV file on the command line directly and is compasable in shell pipelines.
>My point, if one wants cat, sort, sed, join on tabular data, SOL is exactly that.
SQL is a language for data in the form of tables in relational databases.
While it can do sorting or joining or some changes, it is meant for a different domain than these tools, which other constraints, other concerns, and other patterns of use...
You don't need to load anything to a db, for starters.
You also normally don't care for involving a DB in order to use in a shell script, or for quick shell exploration.
You also can't mix SQL and regular unix userland in a pipeline (well, with enough effort you can, but it's not something people do or need to do).
Doesn't need to load anything to DB
Can be used in shell
Can read from stdin and write to stdout
https://towardsdatascience.com/analyze-csvs-with-sql-in-comm...
The problem isn't in having a way to use SQL to query the data from the command line, it's that SQL is long winded and with syntax not really fit in a traditional shell pipeline.