Summarises the rows of a query as a csdb_profile, under disclosure
control. Every statistic comes from an aggregate query, and the profile
keeps no count below k.
Usage
profile_query(
connection,
sql,
columns = NULL,
field_types = NULL,
keys = NULL,
k = 5L,
noise = NULL,
max_levels = 50L,
group_sizes = NULL,
crosstabs = NULL,
dependencies = NULL,
source = NULL
)Arguments
- connection
A DBI connection, or a DBConnection_v9. For a
DBConnection_v9the function uses$autoconnection.- sql
The query to profile, as one string. The function runs it as the subquery
SELECT ... FROM (<sql>) q. The query is published with the profile asprovenance$query, so a caller MUST NOT put an identifier literal, such as a person number, in it.- columns
A named character vector or named list that gives the sensitivity of each column:
"full","coarsen","schema_only"or"exclude". A column thatcolumnsdoes not name is"schema_only".- field_types
A named character vector of csdb field types:
TEXT,INTEGER,DOUBLE,BOOLEAN,DATEorDATETIME. WithNULL, the function infers the types from the R classes of a zero-row fetch.- keys
A character vector of key column names, or
NULL.- k
The smallest count that the profile can hold.
- noise
NULL, orlist(epsilon = e). Withepsilon, the function adds Laplace noise of scale1 / eto each cell count and to each count of missing values. It adds the noise before suppression, then rounds each count and floors it at 0. Callset.seed()first for a reproducible profile.- max_levels
The largest number of distinct values for which a
fullcolumn publishes its values.- group_sizes
A named list, for example
list(rows_per_id = "Id"). Each element names one column. The profile holds how many values of that column have each row count.- crosstabs
A list of pairs of column names, for example
list(c("sex", "age_group")). Both columns MUST be"full".- dependencies
A list of pairs of column names. For each pair the profile holds whether the first column determines the second.
- source
A label for the data source, or
NULL. The profile publishes it.
Value
A list of class csdb_profile, with the elements provenance,
field_types, keys, n_rows, columns, group_sizes, crosstabs,
dependencies and notes. provenance holds format, source,
query, driver, profiled_at (UTC, ISO 8601), k, noise,
max_levels and csdb_version. driver is the class of the DBI
connection.
Details
Each column of field_types gets a summary from its sensitivity:
schema_only:type,sensitivityandmissing_share. All other summaries hold these three fields too. AnINTEGERorDOUBLEcolumn also holdsnonnegative, which isTRUEwhen its SQL minimum is at least 0.exclude: no entry incolumns. The column is infield_typesonly.fullTEXTorBOOLEAN, at mostmax_levelsdistinct values:levelsandn.fullINTEGER,DOUBLEorDATETIME, at mostmax_levelsdistinct values:levelsandnwhen every level holds at leastkrows. Otherwise the column takes the summary that it has abovemax_levels. AnINTEGERorDOUBLEcolumn with one distinct value always publisheslevels. When that value holds fewer thankrows, the function removes its cell, solevelsandnare empty.fullTEXT, abovemax_levels:n_distinct, rounded down to a multiple ofk.fullDATE: counts per day, asdaysandn.fullDATETIME:levelsandn, as above, otherwise counts per UTC day asdaysandn. The function truncates each level to the whole second.coarsenDATEorDATETIME: counts per ISO week, asweeksandn. The Monday of each week is its key. ADATETIMEfalls in the week of its UTC day.INTEGERandDOUBLE,fullabovemax_levelsorcoarsen:breaksandn, for the bins[breaks[i], breaks[i + 1]). The breaks are always double.coarsenTEXTorBOOLEAN: an error that names the column.
The breaks come from pretty(c(min, max), n = 20). An end break that
equals the observed minimum or maximum moves one step outward. The
function removes an inner break that equals either extreme. So no break
equals an observed extreme.
Suppression keeps every published count at k or above. On an ordered
dimension, a cell below k merges with its neighbours:
Days merge into ISO weeks, then calendar months, then calendar quarters. Weeks merge into months, then quarters. The column takes the finest of these resolutions at which every cell holds
k. The field name gives the resolution:days,weeks,monthsorquarters. Each key is the first day of its cell. A week merges into the month of its Monday.When a quarter holds fewer than
krows, adjacent quarters merge into runs. Each run takes quarters in order until it holdsk. A last run belowkjoins the run before it.quartersthen holds the first quarter of each run andquarters_tothe last.Bins merge into runs in the same way. A merged bin keeps the outer breaks of its run, so no break is added.
Group sizes merge into runs in the same way. Each run is published as
size_from,size_toandn, the number of values with a row count in that range. The ranges tile the row counts from 1 up with no gap. The first range starts at 1. Every othersize_fromis an observed row count, the smallest in its run. Eachsize_tois one below the nextsize_from. The last range has nosize_to, so it runs from itssize_fromup.A first run that holds only the smallest row count merges with the next run. A last run that holds only the largest merges with the run before it. So no exact smallest or largest row count is published.
A table whose present values number fewer than k publishes no cell.
Unordered cells, which are the levels of a TEXT or BOOLEAN column
and the cells of a crosstab, do not merge. Each cell below k is
removed. A column sums its removed counts into suppressed_n, and it
keeps suppressed_n only when that sum is at least k. With noise,
the function adds the noise to the finest cells, before it merges or
removes any cell.
missing_share is "suppressed" when the count of missing values, or
the count of values present, is from 1 to k - 1. n_rows holds no
noise. A query with fewer than k rows is an error.
The function reads the data with several queries. It runs them in one read snapshot where the driver gives one:
SQLite: one read transaction.
PostgreSQL: a transaction at
REPEATABLE READ,READ ONLY.SQL Server: a transaction at
SNAPSHOTwhen the database allows it, otherwise atREPEATABLE READ.REPEATABLE READdoes not stop the rows that other sessions insert. The function puts back the isolation level of the session after the profile.Other drivers, or a connection on which
DBI::dbBegin()fails, for example because a transaction is open: no snapshot, and a message says so.
The function sends only SELECT and transaction-control statements, and
ends the transaction with a rollback.
The function always refuses a torn profile, with or without a snapshot.
The check covers each column with levels, days, weeks, months,
quarters or breaks. Its counts before noise and suppression, plus its
count of missing values, MUST equal n_rows. For a group-size column,
the row counts of its values plus its missing count MUST equal n_rows. Otherwise the function stops with an error that
names the column and both totals. A crosstab and an n_distinct have no
total to check.
A crosstab counts the rows where both columns hold a value. Each of its
columns MUST have levels or be a DATE. A group-size range is a range
of row counts, so it can be below k. Only n must reach k. A
dependency holds when each value of the first column, NULL included,
goes with one value of the second. The profile stores no mapping.
See also
Other profile functions:
profile_data(),
profile_table(),
read_profile(),
write_profile()
Examples
con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:", extended_types = TRUE)
DBI::dbWriteTable(con, "visits", data.frame(
id = rep(1:20, each = 3),
sex = rep(c("female", "male"), 30),
age = rep(c(4, 15, 38, 52, 71, 90), 10)
))
p <- profile_query(
con,
"SELECT * FROM visits",
columns = c(sex = "full", age = "coarsen", id = "exclude"),
group_sizes = list(rows_per_id = "id")
)
p$columns$sex
#> $type
#> [1] "TEXT"
#>
#> $sensitivity
#> [1] "full"
#>
#> $missing_share
#> [1] 0
#>
#> $levels
#> [1] "female" "male"
#>
#> $n
#> [1] 30 30
#>
p$columns$age$breaks
#> [1] 0 5 20 40 55 75 95
DBI::dbDisconnect(con)
