Execute a SQL query on the All of Us database

Description

Executes an SQL query on the All of Us database

Usage

aou_sql(
  query,
  collect = FALSE,
  debug = FALSE,
  ...,
  con = getOption("aou.default.con"),
  CDR = getOption("aou.default.cdr")
)

Arguments

query A SQL query (BigQuery dialect) to be executed. Interpreted with glue::glue(), so expressions enclosed with braces will be evaluated. References to “{CDR}” or “{cdr}” will be evaluated automatically (see examples).
collect Whether to bring the resulting table into local memory (collect = TRUE) as a dataframe or leave as a reference to a database table (for continued analysis using, e.g., dbplyr). Defaults to FALSE.
debug Print the query to the console; useful for debugging.
… All other arguments passed to bigrquery::bq_table_download() if collect = TRUE.
con Connection to the allofus SQL database. Defaults to getOption(“aou.default.con”), which is created automatically with aou_connect(). Only needed if collect = FALSE.
CDR The name of the "curated data repository" that will be used in any references of the form “{CDR}” or “{cdr}” in the query (see examples). Defaults to getOption(“aou.default.cdr”), which is Sys.getenv(‘WORKSPACE_CDR’) if not specified otherwise (i.e., the "mainline" CDR). On the controlled tier, specify the "base" CDR with CDR = paste0(Sys.getenv(‘WORKSPACE_CDR’), “_base”).

Value

A dataframe if collect = TRUE; a reference to a remote database table if not.

Examples

library("allofus")



con <- aou_connect()

# Examples based on AoU snippets
aou_sql("
  -- Compute the count of unique participants in our All of Us cohort.
  SELECT
  COUNT(DISTINCT person_id) AS total_number_of_participants
  FROM
  `{CDR}.person`
", collect = TRUE)
# A tibble: 1 × 1
  total_number_of_participants
                         <int>
1                       747029
MEASUREMENT_OF_INTEREST <- "hemoglobin"
aou_sql('
-- Compute summary information for our measurements of interest for our cohort.
--
-- PARAMETERS:
--   MEASUREMENT_OF_INTEREST: a case-insensitive string, such as "hemoglobin", to be compared
--                            to all measurement concept names to identify those of interest

WITH
  --
  -- Use a case insensitive string to search the measurement concept names of those
  -- measurements we do have in the measurements table.
  --
  labs_of_interest AS (
  SELECT
    measurement_concept_id,
    measurement_concept.concept_name AS measurement_name,
    unit_concept_id,
    unit_concept.concept_name AS unit_name
  FROM
    `{CDR}.measurement`
  LEFT JOIN `{CDR}.concept` AS measurement_concept
  ON measurement_concept.concept_id = measurement_concept_id
  LEFT JOIN `{CDR}.concept` AS unit_concept
  ON unit_concept.concept_id = unit_concept_id
  WHERE
    REGEXP_CONTAINS(measurement_concept.concept_name, r"(?i){MEASUREMENT_OF_INTEREST}")
  GROUP BY
    measurement_concept_id,
    unit_concept_id,
    measurement_concept.concept_name,
    unit_concept.concept_name
)
  --
  -- Summarize the information about each measurement concept of interest that our
  -- prior query identified.
  --
SELECT
  measurement_name AS measurement,
  IFNULL(unit_name, "NA") AS unit,
  COUNT(1) AS N,
  COUNTIF(value_as_number IS NULL
    AND (value_as_concept_id IS NULL
      OR value_as_concept_id = 0)) AS missing,
  MIN(value_as_number) AS min,
  MAX(value_as_number) AS max,
  AVG(value_as_number) AS avg,
  STDDEV(value_as_number) AS stddev,
  APPROX_QUANTILES(value_as_number, 4) AS quantiles,
  COUNTIF(value_as_number IS NOT NULL) AS num_numeric_values,
  COUNTIF(value_as_concept_id IS NOT NULL
      AND value_as_concept_id != 0) AS num_concept_values,
  COUNTIF(operator_concept_id IS NOT NULL) AS num_operators,
  IF(src_id = "PPI/PM", "PPI", "EHR") AS measurement_source,
  measurement_concept_id,
  unit_concept_id
FROM
  `{CDR}.measurement`
INNER JOIN
 labs_of_interest USING(measurement_concept_id, unit_concept_id)
LEFT JOIN
  `{CDR}.measurement_ext` USING(measurement_id)
GROUP BY
  measurement_concept_id,
  measurement_name,
  measurement_source,
  unit_concept_id,
  unit_name
ORDER BY
  N DESC
', collect = TRUE)
# A tibble: 672 × 15
   measurement       unit       N missing   min    max     avg  stddev quantiles
   <chr>             <chr>  <int>   <int> <dbl>  <dbl>   <dbl>   <dbl> <list>   
 1 Hemoglobin [Mass… gram… 6.88e6    2045   0   1   e8 1.27e+3 3.39e+5 <dbl [5]>
 2 Hemoglobin A1c/H… perc… 1.21e6    1280   0   1   e7 4.63e+3 2.15e+5 <dbl [5]>
 3 Hemoglobin [Mass… No m… 5.93e5     220   0   1.37e2 1.18e+1 2.72e+0 <dbl [5]>
 4 Glucose mean val… mill… 4.59e5      75   0   1.00e3 1.38e+2 4.26e+1 <dbl [5]>
 5 Hemoglobin [Pres… No m… 3.86e5   30289 -50   1   e7 3.12e+5 1.74e+6 <dbl [5]>
 6 Hemoglobin A1c m… No m… 2.77e5  259605   0   2.24e2 6.81e+0 5.31e+0 <dbl [5]>
 7 Hemoglobin A1c/H… perc… 2.71e5      27   0   1   e7 1.26e+3 1.12e+5 <dbl [5]>
 8 Hemoglobin [Mass… gram… 2.70e5      21   2.1 2.49e1 1.17e+1 2.44e+0 <dbl [5]>
 9 Urinalysis, by d… No m… 2.07e5  182347   1   1   e0 1   e+0 0       <dbl [5]>
10 Hemoglobin [Pres… mill… 1.72e5   63040   0   3   e0 9.24e-2 2.43e-1 <dbl [5]>
# ℹ 662 more rows
# ℹ 6 more variables: num_numeric_values <int>, num_concept_values <int>,
#   num_operators <int>, measurement_source <chr>,
#   measurement_concept_id <int>, unit_concept_id <int>