Collecting large tables safely • transferegovr

library(transferegovr)

The cap you cannot see

The service returns at most 1000 rows per request, however many are asked for, and it does so without complaint: ask for a hundred thousand and you are given a thousand and a 206 Partial Content. Nothing in the body says the result was truncated. Only the Content-Range header does.

This package reads that header, which is why .limit counts rows rather than requests, and why anything above 1000 is collected page by page:

plans <- tg_get("ted", "plano_acao", .limit = 2500)

nrow(plans)
#> [1] 2500
tg_metadata(plans)$pages
#> [1] 3

.page_size is capped at 1000 for the same reason. A larger value would be accepted by the package, silently truncated by the service, and the shortfall would look like missing data.

Ask how big it is first

tg_count("fundoafundo", "gestao_financeira_lancamentos")
#> [1] 1115444

To size everything at once, tg_tables() takes a counts argument. It is the only part of that function that touches the network — one request per table, cached, so the second call in a session is free:

tg_tables(counts = TRUE) |>
  dplyr::arrange(desc(rows))
#> # A tibble: 48 × 6
#>   module                  table                           columns    rows …
#>   <chr>                   <chr>                             <int>   <dbl>
#> 1 fundoafundo             gestao_financeira_lancamentos        32 1115444
#> 2 fundoafundo             gestao_financeira_subtransacoes      16  377666
#> 3 transferenciasespeciais historico_pagamento_especial          5  281163
#> 4 fundoafundo             plano_acao_historico                  5  183379
#> 5 transferenciasespeciais meta_especial                        16  156016
#> # ℹ 43 more rows

A million rows is over a thousand requests. The package throttles itself to sixty requests a minute by default, so that download takes something like twenty minutes. That is usually the wrong shape for the question being asked.

Narrow first, then collect

Two things make a large query smaller, and both happen on the server:

Select only the columns you need. gestao_financeira_lancamentos has 32 columns; if you want four of them, the other 28 need never cross the network.

tg_get(
  "fundoafundo", "gestao_financeira_lancamentos",
  .select = c(
    "id_lancamento_gestao_financeira",
    "id_plano_acao",
    "data_lancamento_gestao_financeira",
    "valor_lancamento_gestao_financeira"
  ),
  .limit = Inf
)

Filter before you fetch. A year’s worth of a table is usually what was wanted anyway:

tg_count(
  "fundoafundo", "gestao_financeira_lancamentos",
  data_lancamento_gestao_financeira = list(gte("2025-01-01"), lt("2026-01-01"))
)

Why the order matters

Offset pagination asks for rows 0–999, then 1000–1999, and so on. In Postgres a query without ORDER BY has no defined row order: the planner is free to return rows differently between two executions, and if it does, page two can repeat rows from page one and skip others entirely. The result has the right number of rows and the wrong contents.

So every request this package makes carries an explicit order. By default it is the table’s primary key where the API declares one, and its identifier columns otherwise:

tg_metadata(plans)$order
#> [1] "id_plano_acao.asc" "id_programa.asc"   "sq_instrumento.asc"

You can supply your own, and pagination will use it:

tg_get("ted", "plano_acao", .order = "vl_total_plano_acao.desc", .limit = 2500)

Bear in mind that ordering by a column with many ties leaves the order within each tie undefined, which brings the problem back. Prefer an identifier, or add one as a tiebreaker:

tg_get(
  "ted", "plano_acao",
  .order = c("vl_total_plano_acao.desc", "id_plano_acao.asc"),
  .limit = 2500
)

Resuming, and taking it in pieces

.offset skips rows that have already been collected, so a long download can be taken in sittings:

first <- tg_get("ted", "plano_acao_etapa", .limit = 20000)
rest <- tg_get("ted", "plano_acao_etapa", .limit = Inf, .offset = 20000)

Both parts must use the same order, or they are pages of two different sequences. The default order is deterministic for a given table, so leaving .order alone is the safe choice here.

When the count and the contents disagree

The number of rows collected is checked against the total the API reported:

Warning: Collected 4820 rows where the API reported 4900.
i The table may have changed while it was being read.
i Check `tg_metadata()` on the result.

This is a real possibility on a long download, since the underlying tables are refreshed daily. It is a warning rather than an error because a nearly complete result is still worth having — but it must be visible, and tg_metadata() records both numbers so the gap stays in the data rather than only in the console.

Caching

Every request is cached for an hour, so re-running an analysis does not re-fetch what it already has. Each page is cached separately, so an interrupted collection resumes from the network only where it has to.

tg_cache_dir()
#> [1] "/tmp/RtmpXXXX/transferegovr-cache"

The default is the session’s temporary directory, so nothing outlives the session unless you ask for it:

tg_cache_dir(tools::R_user_dir("transferegovr", "cache"))

Set TRANSFEREGOVR_CACHE_DIR in .Renviron to make that permanent, and adjust the lifetime with options(transferegovr.cache_ttl = 86400) — the data behind these APIs is refreshed once a day, so an hour is conservative.

tg_metadata(x)$cached tells you whether a result came from the network or from disk.

Rate limiting and retries

Requests are throttled to sixty a minute and retried up to four times on a 429 or a 5xx, with exponential backoff and jitter. A 400 is not retried: it means the service rejected the query itself and will reject it identically next time.

Both are adjustable, but the defaults exist to keep the package a considerate client of a public service:

options(
  transferegovr.requests_per_minute = 30,
  transferegovr.max_tries = 6,
  transferegovr.timeout = 120
)