xrdbi treats an xarray Dataset, open in a Python session that R reaches through reticulate, as a database. You query it with DBI or with dplyr verbs, and each query comes back as a tidy data frame: one row per cell, a column per coordinate. The arrays stay in Python until you fetch, and only the cells you select are read.
This vignette uses a small file that ships with the package, so it needs nothing but Python with xarray and netCDF4. reticulate provisions those for you if no Python is configured; the Python environments article covers the other setups.
Connect
dbConnect() with the xarray() driver opens
anything xarray can open: a path, a URL, a zarr store. This file is
January 2011 of TRMM’s monthly rainfall product (3B43), cut to a 10
degree box over Peru and the Pacific off its coast.
f <- system.file("extdata/autotest/trmm.nc", package = "xrdbi")
con <- dbConnect(xarray(), f)
con
#> <XarrayConnection>
#> dsn: <library>/xrdbi/extdata/autotest/trmm.nc
#> dims: time: 1, latitude: 40, longitude: 40
#> vars: pcpOpening reads the metadata only. The tables of the connection are its variables, coordinates included, and the fields of a table are the columns its rows will have: its dimensions, then any other coordinates, then the variable itself.
dbListTables(con)
#> [1] "pcp" "longitude" "latitude" "time"
dbGetInfo(con)$tables # data variables only
#> [1] "pcp"
dbListFields(con, "pcp")
#> [1] "time" "latitude" "longitude" "pcp"
dbGetInfo(con)$sizes
#> $time
#> [1] 1
#>
#> $latitude
#> [1] 40
#>
#> $longitude
#> [1] 40Query with Python
A query is a Python expression over ds, the Dataset (and
xr, the xarray module). Anything xarray can select
works:
dbGetQuery(con, "ds.pcp.isel(time=0, latitude=0, longitude=slice(0, 4))")
#> longitude latitude time pcp
#> 1 -79.875 -19.875 2011-01-01 0.0028225805
#> 2 -79.625 -19.875 2011-01-01 0.0044354838
#> 3 -79.375 -19.875 2011-01-01 0.0000000000
#> 4 -79.125 -19.875 2011-01-01 0.0003225806
dbGetQuery(con, "ds.pcp.sel(latitude=-15, longitude=-75, method='nearest')")
#> time longitude latitude pcp
#> 1 2011-01-01 -74.875 -14.875 0.05222791A selection is lazy: dbSendQuery() describes the cells
and reads nothing, and dbFetch() renders them, in pages if
you like.
res <- dbSendQuery(con, "ds.pcp.sel(latitude=slice(-12, -10))")
res
#> <XarrayResult>
#> ds.pcp.sel(latitude=slice(-12, -10))
dbFetch(res, n = 3)
#> time latitude longitude pcp
#> 1 2011-01-01 -11.875 -79.875 0.000000000
#> 2 2011-01-01 -11.875 -79.625 0.000000000
#> 3 2011-01-01 -11.875 -79.375 0.001572581
dbFetch(res, n = 3)
#> time latitude longitude pcp
#> 1 2011-01-01 -11.875 -79.125 0.000000000
#> 2 2011-01-01 -11.875 -78.875 0.001304372
#> 3 2011-01-01 -11.875 -78.625 0.001241936
dbGetRowCount(res)
#> [1] 6
dbClearResult(res)A statement that computes, such as a mean, runs in full when it is sent, so use it on a selection rather than on a whole variable that is too big to compute.
dbGetQuery(con, "ds.pcp.mean(['time', 'longitude'])") |> head()
#> latitude pcp
#> 1 -19.875 0.0003801403
#> 2 -19.625 0.0005170994
#> 3 -19.375 0.0007295859
#> 4 -19.125 0.0010951101
#> 5 -18.875 0.0009085938
#> 6 -18.625 0.0010439005Values from R go in by name with params. They become
Python variables in the statement’s scope: numbers and vectors as numpy,
dates and times as datetime64.
dbGetQuery(con,
"ds.pcp.isel(time=0).sel(latitude=lat, longitude=lon, method='nearest')",
params = list(lat = -15.1, lon = -75.3))
#> longitude latitude time pcp
#> 1 -75.375 -15.125 2011-01-01 0.006235942xr_query() writes a statement from a structured
selection, if you would rather not write the Python. The result is just
a string, and isel positions are written into it as given,
so they count from zero, as Python’s do.
Query with dplyr
tbl() gives a lazy table over one variable. Filters on
dimensions are pushed into xarray as a selection, so only those cells
are read, and show_query() prints the statement the verbs
compile to. (A table can also span a grid, the variables that share one
set of dimensions, with a column each: xr_grids() lists
them. This file has one data variable, so Querying
with dplyr shows them on ERA5.)
library(dplyr, warn.conflicts = FALSE)
rain <- tbl(con, "pcp") |>
filter(latitude > -14, latitude < -13, longitude <= -78)
show_query(rain)
#> ds['pcp'].sel(latitude=slice(-14, -13), longitude=slice(None, -78)).pipe(lambda a: a.isel(latitude=(a['latitude'].to_index() > -14) & (a['latitude'].to_index() < -13)))
collect(rain)
#> # A tibble: 32 × 4
#> time latitude longitude pcp
#> <dttm> <dbl> <dbl> <dbl>
#> 1 2011-01-01 00:00:00 -13.875 -79.875 0
#> 2 2011-01-01 00:00:00 -13.875 -79.625 0
#> 3 2011-01-01 00:00:00 -13.875 -79.375 0.000080645
#> 4 2011-01-01 00:00:00 -13.875 -79.125 0
#> 5 2011-01-01 00:00:00 -13.875 -78.875 0
#> 6 2011-01-01 00:00:00 -13.875 -78.625 0
#> 7 2011-01-01 00:00:00 -13.875 -78.375 0
#> 8 2011-01-01 00:00:00 -13.875 -78.125 0
#> 9 2011-01-01 00:00:00 -13.625 -79.875 0.000080645
#> 10 2011-01-01 00:00:00 -13.625 -79.625 0.00024194
#> # ℹ 22 more rowsPrinting a lazy table shows the statement and a preview of its first rows, reading at most one chunk:
rain
#> <tbl_xarray> pcp
#> python: ds['pcp'].sel(latitude=slice(-14, -13), longitude=slice(None, -78)).pipe(lambda a: a.isel(latitude=(a['latitude'].to_index() > -14) & (a['latitude'].to_index() < -13)))
#> sizes : time: 1, latitude: 4, longitude: 8
#> # A tibble: 6 × 4
#> time latitude longitude pcp
#> <dttm> <dbl> <dbl> <dbl>
#> 1 2011-01-01 00:00:00 -13.875 -79.875 0
#> 2 2011-01-01 00:00:00 -13.875 -79.625 0
#> 3 2011-01-01 00:00:00 -13.875 -79.375 0.000080645
#> 4 2011-01-01 00:00:00 -13.875 -79.125 0
#> 5 2011-01-01 00:00:00 -13.875 -78.875 0
#> 6 2011-01-01 00:00:00 -13.875 -78.625 0
#> # lazy preview of the first cells (reads at most one chunk); collect() for the full resultA filter on the values themselves cannot be a selection, so it runs in Python on the selected cells before they come to R. These are the wettest cells of the month:
tbl(con, "pcp") |> filter(pcp > 0.5) |> collect()
#> # A tibble: 14 × 4
#> time latitude longitude pcp
#> <dttm> <dbl> <dbl> <dbl>
#> 1 2011-01-01 00:00:00 -13.375 -70.875 0.61099
#> 2 2011-01-01 00:00:00 -13.375 -70.625 0.69071
#> 3 2011-01-01 00:00:00 -13.375 -70.375 0.71474
#> 4 2011-01-01 00:00:00 -13.375 -70.125 0.83052
#> 5 2011-01-01 00:00:00 -13.125 -71.125 0.56773
#> 6 2011-01-01 00:00:00 -13.125 -70.875 0.76258
#> 7 2011-01-01 00:00:00 -13.125 -70.625 0.74295
#> 8 2011-01-01 00:00:00 -13.125 -70.375 0.69813
#> 9 2011-01-01 00:00:00 -13.125 -70.125 0.62510
#> 10 2011-01-01 00:00:00 -12.875 -71.375 0.51760
#> 11 2011-01-01 00:00:00 -12.875 -71.125 0.60051
#> 12 2011-01-01 00:00:00 -12.875 -70.875 0.55412
#> 13 2011-01-01 00:00:00 -12.875 -70.625 0.58541
#> 14 2011-01-01 00:00:00 -10.875 -73.625 0.52237Guards
Reading a whole variable is one call, but every read that
materializes cells is guarded: above
getOption("xrdbi.max_cells") (10 million by default) it
errors with the size, unless you pass force = TRUE. Here
the limit is set low to show it.
nrow(dbReadTable(con, "pcp"))
#> [1] 1600
try(dbReadTable(con, "pcp", max_cells = 100))
#> Error : materializing 1,600 cells exceeds the guard (100); subset first, raise options(xrdbi.max_cells=), or use force = TRUEAs a raster
The same statement can render as a raster instead of rows:
xr_raster() returns the pixel values with a
gis attribute, in the form gdalraster’s
read_ds() returns.
The Python objects
The connection is a veneer: xr_dataset() hands you the
Dataset itself, unconverted, for anything the DBI methods cannot
express.
ds <- xr_dataset(con)
ds$pcp$attrs$long_name
#> 'precipitation:Precipitation'
dbDisconnect(con)Where next
-
?xrdbimaps the functions by task and lists the options. - The articles on the package website go further,
with live examples on public data:
- Querying with dplyr: how each verb compiles, and what gets read
- Parameters: R values and geometries in statements
- From arrays to rows: the shape and types of results, and when work happens
- Data sources: netCDF over HTTPS, zarr on cloud storage, GDAL, many files
- Rasters, Chunk maps and Curvilinear grids and vector cubes
- Python environments: reticulate, conda, and the gdal-r-python image