wizaRdry (version 0.6.4)

sql: Fetch data from SQL database to be stored in a data frame

Description

Retrieves data from a SQL table and optionally joins it with a primary keys table as specified in the configuration.

Usage

sql(
  table_name = NULL,
  ...,
  fields = NULL,
  where_clause = NULL,
  join_primary_keys = TRUE,
  custom_query = NULL,
  max_rows = NULL,
  date_format = NULL,
  batch_size = 1000,
  pii = FALSE,
  interview_date = NULL,
  all = FALSE
)

Value

A data frame containing the requested SQL data

Arguments

table_name

Name of the SQL table or view to query

...

Optional column names to filter for. Only rows with non-missing values in ALL specified columns will be returned.

fields

Optional vector of specific fields to select

where_clause

Optional WHERE clause to filter results (without the "WHERE" keyword)

join_primary_keys

Boolean, whether to join with the primary keys table (default: TRUE)

custom_query

Optional custom SQL query to execute instead of building one

max_rows

Optional limit on the number of rows to return

date_format

Optional format for date fields (default uses ISO format)

batch_size

Number of records to retrieve per batch for large datasets

pii

Logical; if FALSE (default), remove fields marked as PII. TRUE keeps PII.

interview_date

Optional; can be either: - A date string in various formats (ISO, US, etc.) to filter data up to that date - A boolean TRUE to return only rows with non-NA interview_date values

all

Logical; if TRUE, use LEFT OUTER JOIN instead of INNER JOIN (default: FALSE), similar to the 'all' parameter in base R's merge() function

Examples

if (FALSE) {
# Get data from a specific table
data <- sql("participants")

# Get data with a where clause
survey_data <- sql("vw_surveyquestionresults",
                  where_clause = "resultidentifier = 'NRS'")

# Get all records, including those without matching primary key
all_data <- sql("candidate", all = TRUE)
}