Redact the result of a database query into a table

Runs query on a DBI connection, fetches the result chunk_size rows at a time, redacts the chosen text columns and appends each chunk to the table to. The result may be larger than memory. Works with any DBI database; with DuckDB, query can read files directly ("SELECT * FROM 'calls.parquet'"), and COPY to TO 'out.parquet' writes the redacted table back out.

Usage

redact_query(
  con,
  query,
  to,
  columns = NULL,
  chunk_size = 10000L,
  append = FALSE,
  to_con = con,
  ...
)

Arguments

con: A DBI connection.
query: SQL query whose result is redacted.
to: Name of the table to create (or, with append = TRUE, add to).
columns: Columns to redact; NULL redacts every text column.
chunk_size: Rows fetched at a time.
append: Append to an existing table to instead of creating it.
to_con: Connection to write to with; by default con. Most databases (Postgres, SQLite) cannot write on a connection while a query result is open on it, so with one connection the redacted chunks are kept in temporary files until the query has been read, then written in one transaction. With DuckDB, or with a second connection here, each chunk is written as it is read. (For SQLite, a second connection to the same file needs WAL mode: PRAGMA journal_mode = WAL.)
...: Passed to redact(): detectors, min_confidence.

Value

The number of rows written, invisibly.

Examples

con <- DBI::dbConnect(duckdb::duckdb())
DBI::dbWriteTable(con, "calls", data.frame(id = 1:2, notes = c("PAN ABCPE1234F", "no PII")))
redact_query(con, "SELECT * FROM calls", "calls_redacted")
DBI::dbReadTable(con, "calls_redacted")
#>   id     notes
#> 1  1 PAN [PAN]
#> 2  2    no PII
DBI::dbDisconnect(con, shutdown = TRUE)