Large data: Arrow, Parquet, DuckDB and databases

Bank and insurance data rarely fits in a data frame. credgate works on Arrow tables and Parquet files, and on tables in DuckDB and other databases through DBI and dbplyr, reading them in chunks so the data may be larger than memory. The packages are optional: install the ones you use.

All data in this vignette is synthetic.

library(credgate)

recs <- synth_indian_pii(2000, seed = 42)
templates <- c(
  "Shri %s called about a failed UPI payment to %s.",
  "Name: %s. PAN %s verified; wants the EMI date moved.",
  "Branch hours query, no customer details given."
)
n <- 20000
i <- (seq_len(n) - 1) %% nrow(recs) + 1
kind <- seq_len(n) %% 3 + 1
calls <- data.frame(
  call_id = seq_len(n),
  cust_pan = recs$pan[i],
  mobile = as.numeric(recs$mobile[i]),
  amount = round(seq_len(n) * 7.31 %% 250000, 2),
  notes = ifelse(kind == 1, sprintf(templates[1], recs$name[i], recs$upi_id[i]),
    ifelse(kind == 2, sprintf(templates[2], recs$name[i], recs$pan[i]), templates[3])
  )
)

Which columns hold personal data?

scan_pii() takes a data frame, an Arrow Table or Dataset, or a database table from dbplyr. Arrow and database tables are read one column at a time, in chunks, and sampled exactly as in memory: the report is the same whatever the size of the table.

scan_pii(calls, sample = 5000)
#> Scanned 5 columns; PII found in 3: cust_pan, mobile, and notes
#>    column        type rate      matched whole confidence         example
#>  cust_pan         pan 100% 5000 of 5000  5000       0.97      XXXXXX437R
#>    mobile   mobile_in 100% 5000 of 5000  5000       0.93      XXXXXX2557
#>     notes person_name  67% 3333 of 5000     0       0.80 XXXXXXXXX XXXXX
#>     notes         pan  33% 1667 of 5000     0       0.97      XXXXXX437R
#>     notes      upi_id  33% 1666 of 5000     0       0.99     XXXXXXX@ibl

Arrow and Parquet

path <- tempfile(fileext = ".parquet")
arrow::write_parquet(calls, path, chunk_size = 5000)

# Scan the file without loading it:
scan_pii(arrow::open_dataset(path), sample = 5000)
#> Scanned 5 columns; PII found in 3: cust_pan, mobile, and notes
#>    column        type rate      matched whole confidence         example
#>  cust_pan         pan 100% 5000 of 5000  5000       0.97      XXXXXX437R
#>    mobile   mobile_in 100% 5000 of 5000  5000       0.93      XXXXXX2557
#>     notes person_name  67% 3333 of 5000     0       0.80 XXXXXXXXX XXXXX
#>     notes         pan  33% 1667 of 5000     0       0.97      XXXXXX437R
#>     notes      upi_id  33% 1666 of 5000     0       0.99     XXXXXXX@ibl

redact_parquet() writes a redacted copy, batch_size rows at a time. Columns you do not name are copied unchanged, so drop or tokenise the structured identifier columns separately:

safe <- tempfile(fileext = ".parquet")
redact_parquet(path, safe, columns = "notes", min_confidence = 0.5)
head(arrow::read_parquet(safe, col_select = c("call_id", "notes")), 3)
#>   call_id                                                              notes
#> 1       1 Name: [PERSON_NAME]. PAN [PAN] verified; wants the EMI date moved.
#> 2       2                     Branch hours query, no customer details given.
#> 3       3  Shri [PERSON_NAME] called about a failed UPI payment to [UPI_ID].

redact(), tokenise() and detect_pii() also accept Arrow arrays and return an Arrow array:

redact(arrow::Array$create(c("PAN ABCPE1234F", NA, "no PII")))
#> Array
#> <string>
#> [
#>   "PAN [PAN]",
#>   null,
#>   "no PII"
#> ]

DuckDB and other databases

With dbplyr, scan_pii() works on any database table: DuckDB, Postgres, SQLite, and others with a DBI driver. The query is run once per column, in chunks.

con <- DBI::dbConnect(duckdb::duckdb())
#> duckdb keeps downloaded extensions and secrets in a temporary directory:
#> ℹ /tmp/RtmprbHYdA/duckdb
#> This is removed when the R session ends.
#> • Extensions are re-downloaded each session.
#> • Secrets are lost.
#> ℹ Run duckdb(shared_home = TRUE) (or create ~/.duckdb) to keep them (suitable for most users).
#> ℹ Run duckdb(shared_home = FALSE) to accept the temporary directory (and silence this message).
#> ℹ See ?duckdb_storage for details and alternatives.
DBI::dbWriteTable(con, "calls", calls)

scan_pii(dplyr::tbl(con, "calls"), sample = 5000)
#> Scanned 5 columns; PII found in 3: cust_pan, mobile, and notes
#>    column        type rate      matched whole confidence         example
#>  cust_pan         pan 100% 5000 of 5000  5000       0.97      XXXXXX437R
#>    mobile   mobile_in 100% 5000 of 5000  5000       0.93      XXXXXX2557
#>     notes person_name  67% 3333 of 5000     0       0.80 XXXXXXXXX XXXXX
#>     notes         pan  33% 1667 of 5000     0       0.97      XXXXXX437R
#>     notes      upi_id  33% 1666 of 5000     0       0.99     XXXXXXX@ibl

# Any dbplyr query works, not only whole tables:
recent <- dplyr::filter(dplyr::tbl(con, "calls"), call_id > 15000)
scan_pii(recent, sample = 1000)
#> Scanned 5 columns; PII found in 3: cust_pan, mobile, and notes
#>    column        type rate      matched whole confidence       example
#>  cust_pan         pan 100% 1000 of 1000  1000       0.97    XXXXXX859K
#>    mobile   mobile_in 100% 1000 of 1000  1000       0.93    XXXXXX5037
#>     notes person_name  67%  667 of 1000     0       0.80 XXXXXXX XXXXX
#>     notes         pan  33%  334 of 1000     0       0.97    XXXXXX859K
#>     notes      upi_id  33%  333 of 1000     0       0.99  XXXXXXXX@ibl

DuckDB cannot call R functions from SQL, so redact_query() fetches a query’s result in chunks, redacts the text columns and appends them to a new table. With DuckDB the query can read files directly, and COPY writes the result back to Parquet:

redact_query(con, "SELECT call_id, amount, notes FROM calls", "calls_safe",
  min_confidence = 0.5
)
DBI::dbGetQuery(con, "SELECT * FROM calls_safe LIMIT 3")
#>   call_id amount
#> 1       1   7.31
#> 2       2  14.62
#> 3       3  21.93
#>                                                                notes
#> 1 Name: [PERSON_NAME]. PAN [PAN] verified; wants the EMI date moved.
#> 2                     Branch hours query, no customer details given.
#> 3  Shri [PERSON_NAME] called about a failed UPI payment to [UPI_ID].

out <- tempfile(fileext = ".parquet")
DBI::dbExecute(con, sprintf("COPY calls_safe TO '%s' (FORMAT parquet)", out))
#> [1] 20000
DBI::dbDisconnect(con, shutdown = TRUE)

For a Parquet file, redact_query(con, "SELECT * FROM 'calls.parquet'", ...) reads it without loading it into R first.

Other databases

Any database with a DBI driver works the same way: redact_query() needs only DBI, and scan_pii() takes a dbplyr table. For example, Postgres:

con <- DBI::dbConnect(RPostgres::Postgres(), dbname = "lending", host = "db.internal")
scan_pii(dplyr::tbl(con, "loan_notes"))                     # needs dbplyr
redact_query(con, "SELECT id, notes FROM loan_notes", "loan_notes_safe")

credgate is tested with DuckDB, SQLite and Postgres. scan_pii() reads each column twice (to count, then to sample), so its sample matches the in-memory one only if the database returns rows in the same order both times, as these do for plain tables. Postgres and SQLite cannot write on a connection while a query is open on it, so with one connection redact_query() keeps the redacted chunks in temporary files until the query has been read, then writes them in one transaction; pass a second connection as to_con to write as it reads.

Sizes and speed

credgate processes roughly 0.2-0.3 MB of text per second per core in R (see the throughput section of the repository’s benchmark/README.md). For very large tables, scan first: structured columns such as cust_pan are better dropped or tokenised as whole columns, leaving detection for free-text columns only.