Run a parameterized query against Microsoft Fabric SQL — fabric_sql

Opens a connection with fabric_sql_connect(), executes sql, and closes it automatically. This is the convenient choice for a single query; use fabric_sql_connect() when several queries should share one connection. Values in params are bound by DBI rather than pasted into the SQL string.

Usage

fabric_sql_query(
  server,
  sql,
  params = NULL,
  result = c("tibble", "arrow_stream"),
  database = NULL,
  target_type = c("auto", "lakehouse", "warehouse", "sql_database",
    "sql_analytics_endpoint"),
  backend = c("odbc", "adbc"),
  tenant_id = Sys.getenv("FABRICQUERYR_TENANT_ID"),
  client_id = Sys.getenv("FABRICQUERYR_CLIENT_ID", unset =
    "04b07795-8ddb-461a-bbee-02f9e1bf7b46"),
  token = NULL,
  auth_args = list(),
  odbc_driver = getOption("fabricqueryr.sql.driver", "ODBC Driver 18 for SQL Server"),
  adbc_driver = getOption("fabricqueryr.sql.adbc_driver", "mssql"),
  port = NULL,
  encrypt = "yes",
  trust_server_certificate = "no",
  timeout = 30L,
  read_only = FALSE,
  allow_custom_endpoint = FALSE,
  verbose = TRUE,
  max_tries = 3L,
  retry_delay = 5,
  idempotent = FALSE,
  ...
)

Arguments

server: A Fabric SQL server name, a complete connection string copied from the Fabric portal, or one Lakehouse, Warehouse, Warehouse snapshot, or SQL Database record returned by a discovery function. A discovered record is usually simplest because it also supplies the database name.
sql: One T-SQL statement. A Lakehouse SQL analytics endpoint supports read queries but not INSERT, UPDATE, or DELETE.
params: Optional list of values for DBI parameter placeholders (?). Strings, dates, missing values, and values containing SQL metacharacters are passed unchanged to the driver. With backend = "adbc", placeholders outside SQL strings, identifiers, and comments are safely translated to the SQL Server driver's native @p1, @p2, ... syntax.
result: Result representation. "tibble" collects the query result. "arrow_stream" returns a nanoarrow_array_stream from DBI::dbGetQueryArrow(). ADBC provides the native Arrow path; DBI may materialize results when adapting an ODBC connection. The stream implements the Arrow C Stream interface and can be converted directly with arrow::as_record_batch_reader() when the optional arrow package is installed. A stream is single-use. Prefer "tibble" for ordinary analysis and "arrow_stream" when avoiding collection into an R data frame matters.
database: Optional catalog/database. An explicit value overrides a database found in server. For a bare endpoint, supply the item database shown with its connection string in Fabric. If omitted, Warehouse and SQL analytics endpoints open Fabric's master context, which is useful for discovery but does not select the item's tables.
target_type: Label for the endpoint kind. Keep "auto" unless the hostname is custom or ambiguous. The explicit choices distinguish a Lakehouse SQL analytics endpoint, Warehouse, transactional SQL Database, or another read-only SQL analytics endpoint; they do not convert one kind of endpoint into another.
backend: SQL client backend. Use "odbc" for broad DBI compatibility and the easiest setup; use "adbc" for its native Arrow result path after separately installing the ADBC mssql driver.
tenant_id: Microsoft Entra tenant ID. Defaults to FABRICQUERYR_TENANT_ID.
client_id: Microsoft Entra application/client ID. Defaults to FABRICQUERYR_CLIENT_ID, then the Azure CLI application ID.
token: Optional AzureAuth::AzureToken, bearer-token string, or token-provider function. With NULL, AzureAuth reuses a matching cached token or starts its normal interactive login flow.
auth_args: Named list of additional arguments passed to AzureAuth::get_azure_token().
odbc_driver: ODBC driver name. ODBC Driver 18 for SQL Server is the default.
adbc_driver: ADBC driver name or shared-library path. The separately installed ADBC Driver Foundry mssql driver is the default.
port: Optional TCP port. An explicit value overrides a port in server; otherwise the standard SQL port, 1433, is used.
encrypt: Whether the driver encrypts the connection. Keep the secure default, "yes", for Fabric.
trust_server_certificate: Whether to accept a server certificate without validating its trust chain. Keep the secure default, "no", unless diagnosing a controlled test environment.
timeout: Non-negative whole-number login/connect timeout in seconds; 0 lets the driver use an unlimited or driver-specific timeout.
read_only: Logical. TRUE sends ApplicationIntent=ReadOnly as a connection hint; it is not a substitute for Fabric/SQL permissions.
allow_custom_endpoint: Logical. Fabric SQL and Microsoft SQL Database hostnames are trusted by default. Set to TRUE only when deliberately sending the SQL access token to another hostname, such as a controlled proxy or test server.
verbose: Logical. Show authentication, retry, and connection progress.
max_tries: Positive maximum number of attempts for transient Fabric SQL failures. Connections are always safe to retry. In fabric_sql_query(), execution failures are retried only when idempotent = TRUE.
retry_delay: Non-negative initial retry delay in seconds. Subsequent delays use exponential backoff with jitter, capped at 60 seconds.
idempotent: Logical. Set to TRUE only if running the entire statement a second time has no unwanted effect (usually a plain SELECT). This permits a retry when it is unclear whether Fabric executed the first attempt.
...: Additional arguments forwarded to DBI::dbConnect(). The former named access_token argument is consumed here as a deprecated alias for token and is not forwarded. For ODBC, a caller-supplied attributes named list is merged with the package-managed azure_token; that protected attribute cannot be overridden.

Value

With result = "tibble", a tibble containing the returned rows and driver-converted column types. With result = "arrow_stream", a single-use nanoarrow_array_stream that can be consumed by Arrow-compatible tools.

Examples

if (FALSE) { # \dontrun{
result <- fabric_sql_query(
  server = paste0(
    "Server=example.datawarehouse.fabric.microsoft.com;",
    "Database=SalesWarehouse;"
  ),
  sql = "SELECT * FROM dbo.Customers WHERE region = ?",
  params = list("West")
)

warehouse <- fabric_warehouses("Analytics")[[1]]
stream <- fabric_sql_query(
  warehouse,
  "SELECT * FROM dbo.Customers",
  backend = "adbc",
  result = "arrow_stream"
)
reader <- arrow::as_record_batch_reader(stream)
table <- reader$read_table()
} # }