Registering R objects as virtual tables

An alternative approach to querying data from a dataframe, without needing to load it into a new table first, is to register a dataframe or tibble as a virtual table. This is similar to how a SQL view works, in that it involves creating an entry in the database catalog that points to a target dataframe, enabling DuckDB queries to be performed against the dataframe directly. We can also do the same thing with Apache Arrow tables, registering them as views within our DuckDB database’s catalog. In this section, we’ll provide some brief examples for registering both dataframes and Arrow tables as views that we can query.

We’ll use the nyc_dogs.duckdb database we created previously for this section. Since we closed our previous connection to this database, we’ll need to create a new one:

conn <- dbConnect(
    duckdb::duckdb(),
    dbdir = "nyc_dogs.duckdb"
)

...