Skip to contents

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: pcp

Opening 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] 40

Query 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.05222791

A 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.0010439005

Values 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.006235942

xr_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.

q <- xr_query(vars = "pcp", sel = list(latitude = c(-12, -11)),
              isel = list(time = 0))
q
#> [1] "ds['pcp'].sel(latitude=slice(-12, -11)).isel(time=0)"
nrow(dbGetQuery(con, q))
#> [1] 160

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 rows

Printing 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 result

A 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.52237

Guards

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 = TRUE

As 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.

r <- xr_raster(con, "ds.pcp.isel(time=0)")
str(attr(r, "gis"))
#> List of 5
#>  $ type    : chr "raster"
#>  $ bbox    : num [1:4] -80 -20 -70 -10
#>  $ dim     : num [1:3] 40 40 1
#>  $ srs     : chr ""
#>  $ datatype: chr "Float32"

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