Package 'ggsql'

Create a reader backed by R callbacks

Description

Construct a reader whose behavior is defined entirely by R functions you supply. This makes it possible to plug in data sources that aren't provided natively by ggsql (e.g. an in-memory store, a custom HTTP API, a DBI connection, etc.) without touching the Rust side.

Usage

custom_reader(execute_sql, register = NULL, unregister = NULL)

Arguments

execute_sql	A function ⁠function(sql)⁠ that executes sql and returns either a data frame or a raw vector containing Arrow IPC stream bytes (as produced by nanoarrow::as_nanoarrow_array_stream() / arrow IPC writers).
register	Optional ⁠function(name, df, replace)⁠ that registers df as a table named name. replace is TRUE if the caller expects an existing table with the same name to be replaced.
unregister	Optional ⁠function(name)⁠ that removes a previously registered table.

Details

Only execute_sql is required. If register or unregister are omitted, calling ggsql_register() / ggsql_unregister() on the returned reader raises an error.

Value

A Reader object, usable anywhere the other ⁠*_reader()⁠ constructors are accepted.

See Also

Other readers: duckdb_reader(), odbc_reader(), snowflake_reader()

Examples

# A trivial reader backed by a list of data frames in an environment,
# delegating the actual SQL engine to an in-memory DuckDB.
store <- new.env(parent = emptyenv())
backend <- duckdb_reader()
reader <- custom_reader(
  execute_sql = function(sql) ggsql_execute_sql(backend, sql),
  register = function(name, df, replace) {
    store[[name]] <- df
    ggsql_register(backend, df, name, replace = replace)
  },
  unregister = function(name) {
    rm(list = name, envir = store)
    ggsql_unregister(backend, name)
  }
)
ggsql_register(reader, mtcars, "cars")
ggsql_execute_sql(reader, "SELECT mpg, disp FROM cars LIMIT 3")

---

Create a DuckDB reader

Description

Creates a DuckDB database connection that can execute SQL queries and register data frames as queryable tables. The default creates an empty in-memory database but you can also pass the path to a DuckDB database to directly interact with that.

Usage

duckdb_reader(database = NULL)

Arguments

database

Path to a DuckDB database file, or NULL (the default) for an in-memory database.

Value

A Reader object.

See Also

Other readers: custom_reader(), odbc_reader(), snowflake_reader()

Examples

reader <- duckdb_reader()
ggsql_register(reader, mtcars, "cars")
df <- ggsql_execute_sql(reader, "SELECT mpg, disp FROM cars LIMIT 5")

---

Execute a ggsql query

Description

Parses the query, and execute it against the reader's database. Returns either a visualization specification ready for rendering (ggsql_execute) or a data frame with the query result (ggsql_execute_sql).

Usage

ggsql_execute(reader, query)

ggsql_execute_sql(reader, query)

Arguments

reader	A Reader object created by e.g. duckdb_reader() or odbc_reader().
query	A ggsql query string (SQL + VISUALISE clause).

Value

ggsql_execute() returns Spec object. ggsql_execute_sql() returns a data frame or NULL

Examples

reader <- duckdb_reader()
ggsql_register(reader, mtcars, "cars")
spec <- ggsql_execute(reader,
  "SELECT * FROM cars VISUALISE mpg AS x, disp AS y DRAW point"
)

---

Register and unregisters a data frame as a queryable table

Description

After registration, the data frame can be queried by name in SQL statements. You can use ggsql_table to extract tables from the reader (both registered ones and those native to the backend) and ggsql_table_names to get a vector of all the tables in reader.

Usage

ggsql_register(reader, df, name, replace = FALSE)

ggsql_unregister(reader, name)

ggsql_table(reader, name)

ggsql_table_names(reader)

Arguments

reader	A Reader object created by e.g. duckdb_reader().
df	A data frame to register.
name	The name of the table.
replace	If TRUE, replace an existing table with the same name. Defaults to FALSE.

Value

reader for ggsql_register() and ggsql_unregister(). ggsql_table returns a data.frame if the table exists and NULL if not. ggsql_table_names return a character vector.

Examples

reader <- duckdb_reader()
ggsql_register(reader, mtcars, "cars")

ggsql_table_names(reader)

ggsql_table(reader, "cars")

ggsql_unregister(reader, "cars")

ggsql_table_names(reader)

---

Render a spec with a writer

Description

This function takes a Spec object as returned by ggsql_execute() and renders it with the provided writer.

Usage

ggsql_render(writer, spec)

Arguments

writer	A Writer object created by e.g. svg_writer().
spec	A Spec object returned by ggsql_execute().

Value

Writer dependent:

• svg_writer(): A string holding the SVG markup of the visualization

• pdf_writer(): A raw vector holding the PDF document

• hep_writer(): A raw vector holding the .hep plot document

• vegalite_writer(): A string holding the Vega-Lite JSON representation (deprecated)

Examples

reader <- duckdb_reader()
ggsql_register(reader, mtcars, "cars")
spec <- ggsql_execute(reader,
  "SELECT * FROM cars VISUALISE mpg AS x DRAW histogram"
)

ggsql_render(svg_writer(), spec)

---

Save a ggsql spec to a file

Description

This function renders a specification with one of the native ggsql writers and writes it to disk. The format is derived from the file extension: .svg, .pdf, and .hep are written directly by the corresponding writer, .png is converted from the SVG output using the rsvg package, and .json writes the (deprecated) Vega-Lite representation.

Usage

ggsql_save(spec, file, width = 600, height = 400, dpi = 96)

Arguments

spec	A Spec object returned by ggsql_execute().
file	Output file path. Extension determines format: .svg, .pdf, .hep, .png, or .json.
width	Width in pixels.
height	Height in pixels.
dpi	Resolution in dots per inch.

Value

file, invisibly.

Examples

reader <- duckdb_reader()
ggsql_register(reader, mtcars, "cars")
spec <- ggsql_execute(reader,
  "SELECT * FROM cars VISUALISE mpg AS x, disp AS y DRAW point"
)
spec_file <- tempfile(fileext = ".svg")
ggsql_save(spec, spec_file)

---

Set the ggsql reader for the current Shiny session

Description

Registers a duckdb_reader() for use by all renderGgsql() outputs in the current Shiny session. Must be called from within a Shiny server function (i.e., while a session is active). The reader is automatically cleaned up when the session ends.

Usage

ggsql_session_reader(reader, session = shiny::getDefaultReactiveDomain())

Arguments

reader	A Reader object created by duckdb_reader().
session	The Shiny session object. Defaults to the current session.

Value

The reader, invisibly.

Examples

library(shiny)

ui <- fluidPage(
  ggsqlOutput("chart")
)

server <- function(input, output, session) {
  ggsql_session_reader(duckdb_reader())

  output$chart <- renderGgsql({
    "SELECT * FROM r:mtcars VISUALISE mpg AS x, disp AS y DRAW point"
  })
}

shinyApp(ui, server)

---

Validate a ggsql query

Description

Checks query syntax and semantics without executing SQL. Returns a validation result that can be inspected for errors and warnings.

Usage

ggsql_validate(query)

ggsql_has_visual(x)

ggsql_is_valid(x)

Arguments

query	A ggsql query string.
x	A ggsql_validated object

Value

A ggsql_validated object for ggsql_validate(). A boolean for ggsql_has_visual() and ggsql_is_valid()

Examples

result <- ggsql_validate("SELECT 1 AS x, 2 AS y VISUALISE x, y DRAW point")
result

---

Create a ggsql htmlwidget

Description

Create a ggsql_hep htmlwidget from a spec. The spec is rendered to a .hep plot document server-side and displayed in the browser with the hephaestus SVG renderer compiled to WebAssembly, which re-lays out the plot as its container changes size.

Usage

ggsql_widget(spec, width = NULL, height = NULL)

Arguments

spec	A Spec object returned by ggsql_execute().
width, height	Optional widget dimensions passed to htmlwidgets::createWidget().

Value

An htmlwidget with class ggsql_hep.

---

Shiny bindings for ggsql

Description

Render ggsql visualizations in a Shiny application. renderGgsql() accepts either a ggsql query string or a Spec object (returned by ggsql_execute()). When given a string, it validates and executes the query against reader.

Usage

ggsqlOutput(outputId, width = "100%", height = "400px")

renderGgsql(expr, reader = NULL, env = parent.frame(), quoted = FALSE)

Arguments

outputId	Output variable to read from.
width, height	CSS dimensions for the output container.
expr	An expression that returns a ggsql query string or a Spec object. Strings may contain r:varname references that resolve variables from the expression's local scope (see Examples).
reader	A Reader object created by duckdb_reader(). When NULL (the default), the session reader set by ggsql_session_reader() is used.
env	The environment in which to evaluate expr.
quoted	Logical. Is expr a quoted expression?

Value

ggsqlOutput() returns a Shiny UI element. renderGgsql() returns a Shiny render function.

Examples

library(shiny)

ui <- fluidPage(
  ggsqlOutput("chart")
)

server <- function(input, output, session) {
  ggsql_session_reader(duckdb_reader())

  output$chart <- renderGgsql({
    "SELECT * FROM r:mtcars VISUALISE mpg AS x, disp AS y DRAW point"
  })
}

shinyApp(ui, server)

---

Create an ODBC reader

Description

Creates a connection to a database through an ODBC driver. Can execute SQL queries and, where the backend supports it, register R data frames as temporary tables. Requires an ODBC driver manager (unixODBC/iODBC on Unix, built into Windows) and the appropriate ODBC driver for the target database to be installed.

Usage

odbc_reader(
  dsn = NULL,
  driver = NULL,
  server = NULL,
  database = NULL,
  uid = NULL,
  pwd = NULL,
  ...,
  connection_string = NULL
)

Arguments

dsn	Name of a data source configured in odbc.ini / odbcinst.ini.
driver	ODBC driver name (e.g. "{PostgreSQL}", "{Snowflake}"). Curly brackets are optional.
server, database, uid, pwd	Common ODBC parameters.
...	Additional named key = value parameters appended to the connection string (e.g. Port = 5432, Warehouse = "COMPUTE_WH").
connection_string	A full ODBC connection string, e.g. "Driver={PostgreSQL};Server=localhost;Database=mydb;UID=user;PWD=pass". A leading ⁠odbc://⁠ is accepted and stripped.

Details

Either pass a full ODBC connection string via connection_string, or supply named components (dsn, driver, server, database, uid, pwd, plus any extra key = value pairs in ...) and they will be assembled into a connection string. If connection_string is supplied, the other named arguments are ignored.

Value

A Reader object.

Credentials

Connection strings are stored in-memory for the life of the reader. Prefer configuring credentials through a DSN, ⁠~/.odbc.ini⁠, or environment variables rather than hard-coding passwords in scripts.

See Also

Other readers: custom_reader(), duckdb_reader(), snowflake_reader()

Examples

## Not run: 
# Using a preconfigured DSN
reader <- odbc_reader(dsn = "mydsn")

# Building a connection string from components
reader <- odbc_reader(
  driver = "{PostgreSQL}",
  server = "localhost",
  database = "mydb",
  uid = "user",
  pwd = "secret",
  Port = 5432
)

# Passing a full connection string
reader <- odbc_reader("Driver={SQLite3};Database=:memory:")

## End(Not run)

---

Create a Snowflake reader

Description

Convenience constructor for Snowflake connections. Uses the ODBC reader under the hood with the Snowflake driver, and takes advantage of the dedicated Snowflake handling in the ggsql Rust core:

Usage

snowflake_reader(
  account = NULL,
  warehouse = NULL,
  database = NULL,
  schema = NULL,
  role = NULL,
  user = NULL,
  password = NULL,
  authenticator = NULL,
  connection_name = NULL,
  driver = NULL,
  ...,
  connection_string = NULL
)

Arguments

account	Snowflake account identifier (e.g. "xy12345" or "xy12345.us-east-1"). Translated to ⁠Server={account}.snowflakecomputing.com⁠ in the connection string.
warehouse, database, schema, role	Snowflake session defaults.
user, password	User credentials. Prefer a DSN, connection_name, or authenticator = "externalbrowser" over hard-coded passwords.
authenticator	Snowflake authenticator (e.g. "externalbrowser", "snowflake_jwt", "oauth").
connection_name	Named entry in ⁠~/.snowflake/connections.toml⁠ whose fields will fill in the remaining connection parameters.
driver	Override the ODBC driver name (defaults to "Snowflake").
...	Additional named key = value parameters appended to the connection string.
connection_string	A full raw connection string, bypassing the named arguments. Driver={Snowflake}; is prepended if it isn't already present.

Details

Special handling of Snowflake includes:

• If connection_name is supplied (or ⁠ConnectionName=⁠ appears in the connection string), it is resolved against ⁠~/.snowflake/connections.toml⁠.

• When running inside Posit Workbench, an OAuth token is automatically injected if one is available.

• Schema introspection uses Snowflake's ⁠SHOW DATABASES / SCHEMAS / TABLES⁠ commands rather than information_schema.

Requires the Snowflake ODBC driver to be installed on the system.

Value

A Reader object.

See Also

Other readers: custom_reader(), duckdb_reader(), odbc_reader()

Examples

## Not run: 
# Using a named connection from ~/.snowflake/connections.toml
reader <- snowflake_reader(connection_name = "my_workbench")

# Browser-based SSO
reader <- snowflake_reader(
  account = "xy12345.us-east-1",
  user = "[email protected]",
  authenticator = "externalbrowser",
  warehouse = "COMPUTE_WH",
  database = "ANALYTICS",
  schema = "PUBLIC",
  role = "ANALYST"
)

## End(Not run)

---

Utility functions for visualization specifications

Description

These functions allow you to extract various information from a Spec object returned by ggsql_execute().

Usage

ggsql_metadata(spec)

ggsql_sql(spec)

ggsql_visual(spec)

ggsql_layer_count(spec)

ggsql_layer_data(spec, index = 1L)

ggsql_stat_data(spec, index = 1L)

ggsql_layer_sql(spec, index = 1L)

ggsql_stat_sql(spec, index = 1L)

ggsql_warnings(spec)

Arguments

spec	A Spec object as returned by ggsql_execute()
index	Layer index

Value

• ggsql_metadata: A list with elements rows, columns, and layer_count

• ggsql_sql: A character string with the SQL portion of the query

• ggsql_visual: A character string with the visual portion of the query

• ggsql_layer_count: An integer giving the number of layers

• ggsql_layer_data: A data frame, or NULL if no data is available for this layer

• ggsql_stat_data: A data frame, or NULL if the layer doesn't use a stat transform

• ggsql_layer_sql: A character string with the SQL query used by the layer to fetch its data, or NULL if the layer doesn't have any data.

• ggsql_stat_sql: A character string with the SQL query used by the layers stat transform, or NULL if the layer doesn't have a stat transform.

• ggsql_warnings: A data.frame with columns message, line, and column giving the validation warnings for the spec

Examples

reader <- duckdb_reader()
ggsql_register(reader, mtcars, "cars")
spec <- ggsql_execute(reader,
  "SELECT * FROM cars VISUALISE mpg AS x DRAW histogram"
)

ggsql_metadata(spec)

ggsql_visual(spec)

---

Create a writer for a ggsql spec

Description

Writers turn a Spec object (as returned by ggsql_execute()) into an output artifact. ggsql provides the following writers:

Usage

svg_writer(width = 600, height = 400, dpi = 96)

pdf_writer(width = 600, height = 400, dpi = 96)

hep_writer()

Arguments

width, height	Canvas dimensions in pixels. For hep_writer() these are hints to the consumer rather than a fixed size.
dpi	Resolution of the canvas in dots per inch.

Details

• svg_writer(): A vector graphic in SVG format

• pdf_writer(): A vector graphic in PDF format

• hep_writer(): A .hep plot document — a self-contained description of the resolved plot that a consumer (e.g. the ggsql htmlwidget) can render and re-layout at any size

• vegalite_writer(): A Vega-Lite JSON specification. Deprecated — the Vega-Lite backend is being phased out of ggsql in favour of the native writers above

Value

A Writer object.

Examples

svg_writer()
pdf_writer()
hep_writer()

---