Skip to contents

write data to parquet

Source: R/write.R
data_to_parquet.Rd

Writes a data frame, an sf object or a lazy DuckDB table to a parquet file, or to a directory of hive partitioned parquet files, locally, on R2 as r2://bucket/path or on S3 as s3://bucket/path. Rows get sorted so that filters on the sort_by columns, and spatial filters on sf data, only read the row groups they need. Lazy tables get written by DuckDB directly, without collecting the data into R.

Usage

data_to_parquet(
  data,
  path,
  sort_by = NULL,
  partition_by = NULL,
  row_group_size = 10000L,
  remove_stale = TRUE,
  metadata = NULL,
  compression = c("zstd", "snappy", "gzip", "brotli", "lz4", "lz4_raw", "uncompressed"),
  purge_cache = TRUE
)

Arguments

data

data frame, sf object or lazy DuckDB table.

path

local path, r2://bucket/path or s3://bucket/path of the parquet file, or of the directory with partition_by. A file in a bucket has to end in .parquet. Existing files get overwritten.

sort_by

column names to sort by, optional. Spatial data gets sorted spatially after these.

partition_by

column names to partition by, optional; path is then a directory.

row_group_size

rows per row group, default 10000, rounded up to a multiple of 2048 by DuckDB.

remove_stale

what happens to the files of an existing directory at path:

  • TRUE (default): parquet files in hive partition directories (key=value) get removed first, so no partitions of earlier writes remain, also if the partition columns changed. Other files are kept.

  • FALSE: existing files are kept and only the files at the paths being written get replaced, e.g. to add partitions.

metadata

named list of metadata to write into the parquet footer, see the Metadata section.

  • NULL (default): none of the source's metadata, with a warning if the source has any.

  • list(): none, without the warning.

compression

parquet compression codec: "zstd" (default), "snappy", "gzip", "brotli", "lz4", "lz4_raw" or "uncompressed". zstd compresses well and decompresses fast, which suits files that get read remotely.

purge_cache

TRUE (default) or FALSE: purge the changed files from the Cloudflare cache of the bucket's custom domains when writing to R2, like upload_parquet(). Ignored for S3 and local paths.

Value

(invisibly) path, normalized to r2://bucket/path or s3://bucket/path for buckets, ready for parquet_tbl(), with the written files (local paths or bucket paths) in the files attribute.

Spatial data

sf objects get written as GeoParquet 2.0 with the native parquet GEOMETRY type, which stores the crs with the column and a bounding box per row group. After the sort_by columns, rows get sorted along a Hilbert curve so that each row group covers a compact area and sf::st_filter() on lazy tables can skip row groups. The geometry column is always named geometry, following the GeoParquet convention.

Invalid geometries get fixed via sf::st_make_valid(), keeping multi geometries multi, with a message, and the repairs get recorded in the metadata:

  • processing: how many geometries were repaired, and with which version of GEOS (or s2 for geographic coordinates).

  • geometry_repairs: the repaired rows, up to 1000, as an object with columns, the key columns, and rows, the key values per row. The key is the declared primary_key, or else the first column or a column ending in ID if it identifies the rows; otherwise only the count gets recorded, with a warning.

Entries given in metadata are kept; repair the geometries before writing to avoid both.

Lazy tables with GEOMETRY columns get written as GeoParquet too, sorted by the column named geometry or else the first GEOMETRY column, without repairing geometries. The extent of the Hilbert curve gets computed in a separate query, so the source gets scanned twice, and a remote source downloads its geometry twice. Empty geometries sort last.

Row groups

Row groups are what readers can skip, so their size balances the selectivity of filters against the size of the file metadata. DuckDB writes row groups in multiples of 2048 rows, so data with fewer rows ends up in a single row group and filters can't skip any part of it.

Partitioning

With partition_by, path is a directory with one subdirectory per value, like year=2021/data_0.parquet. The partition values are only stored in the directory names, readers recover them with hive partitioning, DuckDB as strings ("001" stays "001"). Partitioned files get written single threaded, since multi threaded writes don't keep the sort order within partitions. They get written to a new directory next to path first and moved into place once complete, so a dataset can be rewritten from a lazy table reading it, and a failed write leaves the existing files as they were. A file at path gets replaced by the directory. Lazy tables reading path see the new data and metadata afterwards, as long as the columns stayed the same.

Writing to R2 and S3

Destinations in buckets get written to a temporary directory and uploaded via upload_parquet(), into a bucket that has to exist (see create_r2_bucket()). Each file goes up in a single request from memory, so files have to fit into memory and stay below 5 GB; partition large data with partition_by. A partitioned directory may be named like a parquet file, parquet_tbl() tells files and directories apart. Stale files get removed before the upload, so readers may see a partitioned dataset incomplete while it runs. Changed files get purged from the Cloudflare cache (R2 only) unless purge_cache = FALSE. Partitioned writes write a manifest of the directory, and all writes update the existing manifests of the directories above the destination, see upload_parquet().

Metadata

metadata gets written into the parquet footer, where other tools can read it too (DuckDB's parquet_kv_metadata, arrow's schema metadata). parquet_metadata() reads it back and collect_parquet() attaches it to collected data. Conventional entries:

  • source (URL of the original data), license, notes and description.

  • columns: a label and description per column, e.g. columns = list(pop = list(label = "Population", description = "...")); a string is short for the label. label attributes of data frame columns, as set by the haven or labelled packages, get added automatically.

  • languages and language: every language the dataset holds text in, and the language of its plain text, see the Languages section.

  • primary_key: the columns that together identify the rows, one like "DBUID" or several for long tables, like c("DAUID", "characteristic"). They have to exist, have no missing values and identify the rows, which gets checked before writing, at the cost of an extra pass over a lazy table. It gets written as a JSON array, and is how repaired geometries get listed.

  • geo is reserved for the GeoParquet metadata.

Single values get written as strings, anything else as JSON; NULL and NA entries get dropped with a warning. Changing the metadata means writing the data again.

Languages

Text can be stored in several languages, under keys with the language as suffix: title_fr next to title, or title_en and title_fr without a plain title; label_fr and description_fr in the column entries, columns = list(pop = list(label_en = "Population", label_fr = "Population", description_fr = "...")), with or without a plain label; and a column holding another column's text in a language is named the same way, name_fr next to name, or name_en and name_fr without a plain name. Which suffixes mean a language is an explicit contract, nothing is guessed from the keys. The languages of a dataset are

  • the languages entry: every language it holds text in, like c("en", "fr"), written as a JSON array;

  • the language entry: the language of its plain text (title, label) and plain columns, as a language code like "en", only needed where there is plain text, and declared by that alone.

Only a declared suffix marks a variant: source_url next to source and rate_men next to rate are entries and columns of their own, and title_fr is the French title only when the dataset declares fr. Suffixed labels and descriptions in a language the metadata doesn't declare are refused, so a bilingual dataset has to say languages = c("en", "fr"). Language codes are lowercase, two or three letters with optional subtags after a hyphen ("en", "fra", "pt-br"), and a suffix has to match exactly, name_FR is no translation. No language is privileged: a bilingual dataset can carry only suffixed entries, labels and columns.

Readers get one language at a time: parquet_tbl() and select_language() select the columns of that language under their plain names, collect_parquet() attaches its labels and parquet_metadata() resolves the entries, see the Languages section of collect_parquet() for the fallbacks. A language the dataset doesn't declare leaves it as stored.

Metadata doesn't get carried over by itself, since data read from parquet files describes something else once it got changed. With metadata = NULL, writing a lazy table from parquet_tbl() (the files of the left-hand table for joins) or data collected with collect_parquet() warns when the files it was read from have metadata:

  • metadata = parquet_metadata(data) writes it again;

  • metadata = modifyList(parquet_metadata(data), list(...)) changes entries (a NULL drops one);

  • metadata = list() writes none.

Metadata read with parquet_metadata() describes the source, so its columns entries for columns the data no longer has get dropped, and repaired geometries replace its processing and geometry_repairs entries. A primary_key gets checked like any other.

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.