Skip to contents

edstr_import(): importing data

Source: vignettes/import.qmd

edstr_import() executes a SQL query against an Oracle database and saves the result as a Parquet file. It requires edstr_config() to be called first.

Prerequisites 

edstr_config(
  edstr_dirname = "output/my_study",
  edstr_filename = "my_study",
  edstr_text = "note_text",
  edstr_overwrite = FALSE
)

Running a query

The query argument accepts either a SQL string or a path to a .sql file. Connection parameters (user, password, tns) configure access to the Oracle database.

Connection file

edstr_import() reads the driver, address, and TNS entries from a YAML connection file loaded by the config package. Set its path once with edstr_config(edstr_connect_dir = "path/to/connect.yml"), or pass connect_dir directly. The function aborts if neither is set or the file is missing.

If password is omitted in an interactive session, edstr_import() prompts for it: the RStudio password dialog when available, otherwise askpass::askpass(), which reads the credential without echoing it to the console. Outside an interactive session there is no prompt to answer, so password is required: a prompt with nobody to answer it returns "", which the server counts as a failed authentication attempt against the account’s lockout counter. An empty string is rejected in every session, Sys.getenv() returning one for an unset variable.

df_import <- edstr_import(
  query = "SELECT * FROM clinical_notes WHERE rownum <= 1000",
  user = "my_user"
)

Using a .sql file keeps queries out of R scripts and makes them easier to version:

df_import <- edstr_import(
  query = "sql/clinical_notes.sql",
  user = "my_user"
)

Limiting rows

The head argument appends FETCH FIRST ... ROWS ONLY to the query. This is useful for development and testing without modifying the SQL itself.

df_import <- edstr_import(
  query = "sql/clinical_notes.sql",
  head = 500,
  user = "my_user"
)

Loading cached data

When the output file already exists, edstr_import() skips the database query entirely and loads from cache. The behaviour depends on the edstr_overwrite option set in edstr_config().

df_import <- edstr_import()

Column names

By default, lower = TRUE converts all column names to lowercase after import. Set lower = FALSE to preserve the original casing from the database.