For each table, the number of rows and an order-independent hash of its
content (the sum of the MD5 hashes of its rows). Two copies of a database
(the original and a compacted copy, yours and a co-author's, before and
after a migration) can then be compared by exchanging a small table
instead of the database itself (compare_fingerprints()).
Details
Each row is turned into text (every column cast to VARCHAR, NULL
written <NULL>, columns joined by | in the order of the table), hashed
with MD5, and the lower 64 bits of the hashes are summed. The sum does not
depend on the order of the rows, and the text form does not depend on the
DuckDB version. Two tables with the same content but columns in another
order get different hashes. Fingerprinting reads every row, but streams
it: 10 million rows of 600-character texts took 3 seconds, and the memory
peak follows DuckDB's limit (2.3 GB under a 2 GB limit, as fast). On tables
of tens of gigabytes it takes minutes.
See also
Other fingerprints:
compare_fingerprints()
Examples
con <- db_connect(tempfile(fileext = ".duckdb"))
#> duckdb is storing downloaded extensions and secrets under ~/.duckdb:
#> ℹ /home/agoutsmedt/.duckdb
#> This persists across sessions and is shared with the DuckDB CLI and other clients.
#> ℹ Run duckdb(shared_home = FALSE) to use a temporary directory instead.
#> ℹ See ?duckdb_storage for details and alternatives.
DBI::dbWriteTable(con, "document", data.frame(doc_id = c("a:1", "a:2")))
db_fingerprint(con)
#> table n_rows hash
#> <char> <num> <char>
#> 1: document 2 20014335649509092173
db_disconnect(con)