Every query result in xrdbi becomes a data frame in the same way, so it pays to know that way. This article covers the shape of the rows, the types of the columns, and when the work happens.
The examples use a small Dataset with the shapes that matter: two
variables on (time, lat, lon), a scalar coordinate zlev,
and a 2-D coordinate cell_area on (lat, lon).
con <- dbConnect(xarray(), builder = function(xr) {
reticulate::py_run_string("
import xarray as xr, numpy as np, pandas as pd
rng = np.random.default_rng(42)
ocean = xr.Dataset(
{'sst': (('time', 'lat', 'lon'), rng.random((3, 4, 5)).astype('float32')),
'ice': (('time', 'lat', 'lon'), rng.random((3, 4, 5)).astype('float32'))},
coords={'time': pd.date_range('2026-01-01', periods=3),
'lat': np.linspace(-80, -60, 4),
'lon': np.linspace(0, 40, 5),
'zlev': 0.0,
'cell_area': (('lat', 'lon'), np.ones((4, 5)))})
")
reticulate::py_eval("ocean", convert = FALSE)
})
con
#> <XarrayConnection>
#> dims: time: 3, lat: 4, lon: 5
#> vars: sst, iceThe long form
A result renders as xarray’s
to_dataframe().reset_index() would give it. There is one
row per cell, in C order (the last dimension varies fastest). The
columns are the dimensions in the variable’s order, then its other
coordinates, broadcast to every row, then the variable itself.
dbListFields() names them in advance.
dbListFields(con, "sst")
#> [1] "time" "lat" "lon" "zlev" "cell_area" "sst"
head(dbGetQuery(con, "ds.sst"))
#> time lat lon zlev cell_area sst
#> 1 2026-01-01 -80.00000 0 0 1 0.77395606
#> 2 2026-01-01 -80.00000 10 0 1 0.43887845
#> 3 2026-01-01 -80.00000 20 0 1 0.85859793
#> 4 2026-01-01 -80.00000 30 0 1 0.69736803
#> 5 2026-01-01 -80.00000 40 0 1 0.09417735
#> 6 2026-01-01 -73.33333 0 0 1 0.97562236A Dataset renders a column per data variable on their shared
dimensions. After the dimensions come its other variables, data
variables and coordinates, in the Dataset’s own order (a scalar
time here, left by the selection):
head(dbGetQuery(con, "ds[['sst', 'ice']].isel(time=0)"), 3)
#> lat lon sst ice time zlev cell_area
#> 1 -80 0 0.7739561 0.6684030 2026-01-01 0 1
#> 2 -80 10 0.4388784 0.4710962 2026-01-01 0 1
#> 3 -80 20 0.8585979 0.5652361 2026-01-01 0 1Variables with fewer dimensions are broadcast, as
to_dataframe() does, so a per-time value repeats along lat
and lon:
head(dbGetQuery(con,
"ds[['sst']].assign(sst_mean=ds.sst.mean(['lat', 'lon']))"), 3)
#> time lat lon sst zlev cell_area sst_mean
#> 1 2026-01-01 -80 0 0.7739561 0 1 0.5738422
#> 2 2026-01-01 -80 10 0.4388784 0 1 0.5738422
#> 3 2026-01-01 -80 20 0.8585979 0 1 0.5738422Smaller shapes
A coordinate is a table too, and renders as a single column of its values:
dbGetQuery(con, "ds.lat")
#> lat
#> 1 -80.00000
#> 2 -73.33333
#> 3 -66.66667
#> 4 -60.00000A selection that drops every dimension renders one row. A data variable keeps the coordinates that were its dimensions, so a nearest-point lookup says where it landed; a 0-d coordinate renders just itself.
dbGetQuery(con, "ds.sst.sel(time='2026-01-02', lat=-71, lon=12, method='nearest')")
#> time lat lon zlev cell_area sst
#> 1 2026-01-02 -73.33333 10 0 1 0.466721
dbGetQuery(con, "ds.time.isel(time=-1)")
#> time
#> 1 2026-01-03A reduction is an ordinary result of whatever shape it has:
dbGetQuery(con, "ds.sst.mean('time')") |> head(3)
#> lat lon zlev cell_area sst
#> 1 -80 0 0 1 0.6563986
#> 2 -80 10 0 1 0.5420275
#> 3 -80 20 0 1 0.8431870
dbGetQuery(con, "ds.sst.mean()")
#> zlev sst
#> 1 0 0.5198094A statement may also end in pandas, which is returned as it is, or in
any other Python value, which becomes one row in a column named
value:
dbGetQuery(con,
"ds.sst.isel(time=0).to_dataframe().reset_index().nlargest(3, 'sst')")
#> lat lon time zlev cell_area sst
#> 1 -73.33333 0 2026-01-01 0 1 0.9756224
#> 2 -66.66667 10 2026-01-01 0 1 0.9267650
#> 3 -80.00000 20 2026-01-01 0 1 0.8585979
dbGetQuery(con, "len(ds.data_vars)")
#> value
#> 1 2Types
Each column converts on its own, from the array’s type:
| In xarray (numpy or pandas) | In R |
|---|---|
float16 to float64
|
double; missing values stay NaN
|
int8 to int32, uint8,
uint16
|
integer |
int64, uint32, uint64
|
double (exact up to 2^53) |
bool |
logical |
datetime64 |
POSIXct in UTC; NaT becomes
NA
|
| time-zone-aware times (pandas) |
POSIXct in their own zone |
timedelta64 |
difftime in seconds; NaT is
NA
|
| strings, bytes | character |
| cftime dates (non-standard calendars) | character, as ISO text |
| pandas categoricals | factor |
pandas nullable types (Int64,
boolean) |
integer, double or logical, with NA
|
| shapely geometry (an xvec cube) |
wk::wkb() with its CRS (needs wk) |
types <- dbConnect(xarray(), builder = function(xr) {
reticulate::py_run_string("
import xarray as xr, numpy as np, pandas as pd, cftime
types = xr.Dataset(
{'f32': ('station', np.array([1.5, np.nan, 3.25], dtype='float32')),
'i16': ('station', np.array([1, 2, 3], dtype='int16')),
'i64': ('station', np.array([1, 2, 2**40], dtype='int64')),
'flag': ('station', [True, False, True]),
'when': ('station', pd.to_datetime(['2026-01-01', '2026-01-02', None])),
'lag': ('station', pd.to_timedelta(['1h', '90min', None])),
'name': ('station', np.array(['Casey', 'Davis', 'Mawson'])),
'model_day': ('station', [cftime.DatetimeNoLeap(2028, 2, 27),
cftime.DatetimeNoLeap(2028, 2, 28),
cftime.DatetimeNoLeap(2028, 3, 1)])},
coords={'station': np.array([1, 2, 3], dtype='int32')})
")
reticulate::py_eval("types", convert = FALSE)
})
str(dbGetQuery(types, "ds"))
#> 'data.frame': 3 obs. of 9 variables:
#> $ station : int 1 2 3
#> $ f32 : num 1.5 NaN 3.25
#> $ i16 : int 1 2 3
#> $ i64 : num 1.0 2.0 1.1e+12
#> $ flag : logi TRUE FALSE TRUE
#> $ when : POSIXct, format: "2026-01-01" "2026-01-02" ...
#> $ lag : 'difftime' num 3600 5400 NA
#> ..- attr(*, "units")= chr "secs"
#> $ name : chr "Casey" "Davis" "Mawson"
#> $ model_day: chr "2028-02-27 00:00:00" "2028-02-28 00:00:00" "2028-03-01 00:00:00"
dbDisconnect(types)model_day is in a 365-day calendar, where 2028 has no 29
February and 28 February is followed by 1 March. R has no types for such
calendars, so these dates arrive as character: labels, not times.
When the work happens
Three moments matter:
-
dbSendQuery()evaluates the statement in Python. A selection (sel(),isel(), indexing) reads nothing here: xarray’s arrays are lazy until their values are asked for. A statement that computes, such as a mean or arithmetic, runs in full here, unless the data are dask-backed. -
The first
dbFetch()checks the guard, which sizes the result, and then reads its values.dbGetQuery()is send and fetch together. -
Every fetch builds its rows in R: only the values
cross from Python, and the coordinate columns are repeated with
rep(), sodbFetch(n = )builds only the rows it is asked for.
res <- dbSendQuery(con, "ds.sst.isel(time=0)")
dbFetch(res, n = 2)
#> lat lon time zlev cell_area sst
#> 1 -80 0 2026-01-01 0 1 0.7739561
#> 2 -80 10 2026-01-01 0 1 0.4388784
dbFetch(res, n = 2)
#> lat lon time zlev cell_area sst
#> 1 -80 20 2026-01-01 0 1 0.8585979
#> 2 -80 30 2026-01-01 0 1 0.6973680
dbHasCompleted(res)
#> [1] FALSE
dbClearResult(res)Because the guard sizes only the result, a reduction over a large variable runs at send time, before any guard. Select first, then reduce.
The guard counts the cells of the xarray result (for a Dataset, the
rows multiplied by the data variables) and errors above
max_cells, 10 million by default
(getOption("xrdbi.max_cells")):
try(dbGetQuery(con, "ds", max_cells = 50))
#> Error : materializing 120 cells exceeds the guard (50); subset first, raise options(xrdbi.max_cells=), or use force = TRUE
nrow(dbGetQuery(con, "ds", max_cells = 50, force = TRUE))
#> [1] 60
dbDisconnect(con)Where next
- Querying with dplyr builds these statements from verbs.
- Rasters renders a 2-D slab as pixels instead of rows.
- Curvilinear grids and vector cubes has 2-D coordinates and geometry columns.