Ask HN: Data diff tool for tabular data?

Example script:

  library(tidyverse)
  old = read_tsv("path/to/old/db.tsv")
  new = read_tsv("path/to/new/db.tsv")
  deleted   = anti_join(old, new)
  new       = anti_join(new, old, by = "id")
  unchanged = semi_join(old, new)
  changed   = semi_join(new, old, by = "id") %>%
                anti_join(unchanged)

  cat old.csv old.csv new.csv | sort | uniq -u # only the added/changed records
  cat old.csv new.csv new.csv | sort | uniq -u # only the ol d/deleted records
  cat old.csv new.csv | sort | uniq -d # only the unchanged records

[1]https://lnav.org

https://github.com/paulfitz/daff

The easiest way to do this is to use an ETL tool as hand coding this can be a bit hairy. You can use an open source etl tool like Talend for this as it has inbuilt Postgres or MySQL scd modules.

You can totally join two 'sheets' by primary key and save and replay that workflow, I think you may have to write a python function to iterate over the sets of columns to look for differences. Not sure if there's a cleaner way

[0] https://github.com/saulpw/visidata

http://augeas.net/docs/references/lenses/files/csv-aug.html

But will require a bit of coding

Another option that might fit would be a sort(1) pass on your TSV files (unless you already ORDER BY on the primary key), then using Awk to detect differences, and immediately print out a list of API commands to update your external DB. Awk conveniently handles tabular data, just set the field-separator (FS) to \t in this case (default it splits on all whitespace).

I made Quick to solve your exact problem! we use it to test our batch processes that either generate flat files or write data into database tables.

please contact me if you need help with the tool, i know that the docs are not as complete as i would like them to be.

here is the wiki: https://github.com/MouslihAbdelhakim/Quick/wiki

here is a repo of some examples: https://github.com/MouslihAbdelhakim/Quick-examples

It is not free but I find it much better than the usual visual diff free tools like kdiff3 or meld.

Supports delimited files and Postgres tables. Outputs a large JSON-based summary of record diffs or it emits an event stream as changes are found (and thus does not hold the summary in memory).

Prebuilt binaries: https://github.com/chop-dbhi/diff-table/releases

Edit: add link to releases and description

For example:

  $ echo "1\t2\t3" > one.tsv
  $ echo "1\tx\t3" > two.tsv
  $ git diff --word-diff-regex="[^\t]" one.tsv two.tsv
  diff --git a/one.tsv b/two.tsv
  index d43017e..e2d5856 100644
  --- a/one.tsv
  +++ b/two.tsv
  @@ -1 +1 @@
  1       [-2-]{+x+}      3

    >>> set(open('a.tsv')) ^ set(open('b.tsv'))

So, keep a table of the latest "data" you have. Then import into a separate table. After that, your requirements are solvable with a few SQL joins. The upside is that you also have a local copy of the data in a table, which is always a plus when diagnosing/supporting your deployed product.

The data sets you then need:

    Deleted: Orig_table left outer join Import_Table with a null filter on Import_table.
    New: Import_Table left outer join Orig_table with a null filter on Orig_table.
    Changed: Inner join with not-equal check on all columns, joined by OR.

I know it sounds a bit verbose and maybe not an "all in one" solution that you're looking for. But sometimes, you just need to solve the problem.

https://www.xlcompare.com

Import two snapshots of data like this:

  csv-import --dest-type="map:0" dump0.csv /noms/mydb::data
  csv-import --dest-type="map:0" dump1.csv /noms/mydb::data

  noms log --max-lines=-1 /noms/mydb::data

https://github.com/capitalone/datacomparer