Skip to contents

lazy table to query parquet files at URLs, on R2 or S3, or at local paths

Source: R/parquet.R
parquet_tbl.Rd

Lazy dplyr table on DuckDB for parquet files at public URLs (like public buckets or presigned URLs), in private buckets on R2 as r2://bucket/path or on S3 as s3://bucket/path, or at local paths. Nothing gets downloaded until the query is executed, e.g. via dplyr::collect(), and then only what the query needs: filters on partition columns restrict which files get read, other filters and the selected columns which parts of the files.

Usage

parquet_tbl(
  path,
  hive_partitioning = TRUE,
  con = NULL,
  cache = NULL,
  language = getOption("r2parquet.language")
)

Arguments

path

URL, r2://bucket/path, s3://bucket/path or local path of a parquet file or a directory of (partitioned) parquet files, or a vector of these.

hive_partitioning

TRUE (default) or FALSE: derive partition columns from key=value components of the file paths. This includes the directories above a single file, so reading data/year=2021/data_0.parquet adds a year column. Partition values are read as strings, whatever they look like, so PRUID=01 and PRUID=10 give the same type; on parquet_connection(), numbers compared with a partition column for equality (==, !=, %in%) are rendered as strings, with a message once per session, so that filter(year == 2021) only reads that partition. Order comparisons need the cast spelled out, as.integer(year) > 2020, since strings compare lexicographically.

con

DuckDB connection to create the table on. Tables can only be joined when they are on the same connection.

  • NULL (default): parquet_connection(), shared by all calls.

  • a connection of your own, to join with data that lives there. The httpfs and spatial extensions get installed and loaded on it as needed, and the R2 or S3 credentials get registered as secrets.

cache

how to use the local cache (see the Cache section):

  • NULL (default): read the cached copy if it is current, else the remote files, with a warning for copies that are out of date. A copy only gets checked the first time its path is read in the session, later calls reuse the result (see clear_cache_checks()).

  • TRUE: update the copy first via cache_parquet(), downloading the files on first use, and read it.

  • FALSE: read the remote files directly.

  • "offline": read the copy without checking it against the remote files, current or not; fails for paths without a copy.

language

language code like "en" or "fr", or NULL: with a language, the columns the data holds in several languages get selected in that language under their plain names, see the Languages section. Defaults to the r2parquet.language option.

Value

a lazy dplyr table on DuckDB.

Details

dplyr verbs get translated to SQL. Use sf::st_filter() to filter by location, collect_parquet() to collect the result with its metadata attached (as an sf object if it has geometry), parquet_metadata() for the metadata of the files and list_parquet() to list them. Tables with GEOMETRY columns, like GeoParquet files written by data_to_parquet(), get the spatial extension loaded, so that spatial functions like ST_Area work in queries.

Directories

  • Local directories and directories on R2 and S3 get read as all parquet files within them. Directories in buckets are often named like a parquet file, e.g. r2://bucket/data.parquet, which works too.

  • Directories at URLs can't be listed, they get read through the manifest next to their files (_manifest.json, written by upload_parquet(), data_to_parquet() and write_manifest()). A URL ending in a slash is read as a directory; a URL without one is read as a file first and, if there is no file at it, as a directory.

Integer literals

Whole numbers in comparisons (==, !=, <, <=, >, >=, %in%, between) get rendered as integers, with a message the first time in a session. A plain number like 2021 is a double in R, which dplyr renders as 2021.0, and DuckDB resolves that against an integer column by casting the column, which defeats the statistics it uses to skip row groups, so the filter downloads the whole column. Comparisons with double columns are unaffected, and arithmetic is not rewritten (x * 1000 stays a double multiplication). Write integers as 2021L to avoid the message, and on a connection of your own passed as con, which keeps DuckDB's own translation.

Languages

Datasets can carry labels and columns in several languages, see the Languages section of collect_parquet(). With a language the table gets its columns selected in that language through select_language(): a column in the language asked for takes the place and name of its plain column, name_fr becomes name, or stands in for a plain column the data doesn't have, and the columns in the other languages get left out before anything is read, so a bilingual dataset read with options(r2parquet.language = "fr") has name and no name_en. Which suffixes are languages is declared by the metadata of the files (see the Languages section of data_to_parquet()), which gets read once per table for this. The column names of the table therefore depend on the language. language = NULL gives the columns as stored, for selecting them by hand or with select_language() later, which also works on joins of such tables.

Errors

Paths without parquet files and denied requests fail with an error naming the path, of class r2parquet_not_found or r2parquet_access_denied (both also r2parquet_error) for use with tryCatch(). Denied requests say what access depends on: the credentials for buckets, the expiry of presigned URLs, public access for other URLs. Servers that can't be reached, e.g. offline, fail with an r2parquet_network_error, as does installing a DuckDB extension the first time it is needed. The files get read when the query is executed, collect_parquet() raises the same errors then, e.g. when the connection dropped after the table was created.

Cache

Remote files that get queried regularly can be kept in a local cache directory via cache_parquet(), and the table then reads the local copies, see the cache argument. The default costs nothing for paths without a copy, they are read remotely without any request to check them. With the default and with cache = TRUE a cached copy gets used, with a warning, when the remote can't be reached, e.g. offline. cache = "offline" also allows comparing the copy with the current remote files (cache = FALSE) before updating it. Directories at public URLs can only be cached if they have a manifest. The cache directory is the one in the R2PARQUET_CACHE_PATH environment variable, or the user cache directory if that is not set.

Credentials

Credentials come from environment variables, e.g. set in .Renviron, read at call time. Public URLs and local paths need none.

Paths on R2 (r2://bucket/path):

  • R2_ACCOUNT_ID: the R2 account id.

  • R2_ACCESS_KEY_ID and R2_SECRET_ACCESS_KEY: the access key id and secret access key of an R2 API token with access to the bucket.

  • R2_API_TOKEN, optional: the value of an API token with Zone Cache Purge permission (see create_r2_bucket()). Files that change on R2 then get purged from the Cloudflare cache of the bucket's custom domains, so that the new versions are served right away; the purge is skipped without it.

  • R2PARQUET_PURGE_ORIGINS, optional: the origins of web pages reading the bucket from a browser, comma separated, like https://example.com,https://www.example.com. Cloudflare caches the responses to their requests separately per Origin, which a purge of the plain URL misses, so each changed file also gets purged once per origin; copies cached for origins not listed are refreshed when the edge TTL runs out. This only concerns web apps, whose browsers send the Origin header; requests without one, like those of this package, DuckDB or curl, get the plain URL's copy, which is always purged.

Paths on S3 (s3://bucket/path), on Amazon S3 or another S3 compatible service like MinIO, use the variables AWS tools use:

  • AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY, and AWS_SESSION_TOKEN for temporary credentials.

  • AWS_REGION or AWS_DEFAULT_REGION: the region, default us-east-1.

  • AWS_ENDPOINT_URL_S3 or AWS_ENDPOINT_URL: the endpoint of other S3 compatible services, like http://localhost:9000, which get addressed with path style URLs (endpoint/bucket/key).

  • R2PARQUET_S3_URL_STYLE: path or vhost, overrides the URL style.

AWS profiles and single sign-on are not read, aws configure export-credentials --format env prints the variables for them.