aou_connect – allofus

Create a connection to the database in All of Us

Description

Connects to the All of Us database and returns a BigQueryConnection object. You can reference this object to query the database using R and or SQL code. A message is printed with the connection status (successful or not).

Usage

aou_connect(CDR = getOption("aou.default.cdr"), ...)

Arguments

CDR The name of the "curated data repository" to connect to. 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”).

… Further arguments passed along to DBI::dbConnect().

Details

You can reference this object to connect to the All of Us database and run SQL code using, e.g., dbplyr or DBI. A message is printed with the connection status (successful or not). For RStudio users, setting quiet = TRUE will silence most (but not all) billing messages.

Value

A BigQueryConnection object. This object is also saved as an option (getOption(“aou.default.con”)).

Examples

library("allofus")


con <- aou_connect()
# reference the observation table in the database
dplyr::tbl(con, "observation")

# Source:   table<`observation`> [?? x 21]
# Database: BigQueryConnection
   observation_id person_id observation_concept_id observation_date
          <int64>   <int64>                <int64> <date>          
 1           7e16   9929706                1704135 2023-07-19      
 2           7e16   3128683                1704135 2023-08-19      
 3           7e16   8984266                1704135 2024-02-22      
 4           7e16   3757069                1704135 2023-08-29      
 5           7e16   3350503                1704135 2023-08-19      
 6           7e16   2061569                1704135 2023-07-21      
 7           7e16   7561169                1704135 2023-07-18      
 8           7e16   6258330                1704135 2024-01-30      
 9           7e16   1869655                1704135 2023-07-20      
10           7e16   1550351                1704135 2023-07-18      
# ℹ more rows
# ℹ 17 more variables: observation_datetime <dttm>,
#   observation_type_concept_id <int64>, value_as_number <dbl>,
#   value_as_string <chr>, value_as_concept_id <int64>,
#   qualifier_concept_id <int64>, unit_concept_id <int64>, provider_id <int64>,
#   visit_occurrence_id <int64>, visit_detail_id <int64>,
#   observation_source_value <chr>, observation_source_concept_id <int64>, …

# print a list of the tables in the database
DBI::dbListTables(con)

  [1] "_cdr_metadata"                   "activity_summary"               
  [3] "aou_death"                       "attribute_definition"           
  [5] "attribute_definition_ext"        "care_site"                      
  [7] "care_site_ext"                   "cb_criteria"                    
  [9] "cb_criteria_ancestor"            "cb_criteria_attribute"          
 [11] "cb_criteria_menu"                "cb_criteria_relationship"       
 [13] "cb_review_all_events"            "cb_review_survey"               
 [15] "cb_search_all_events"            "cb_search_person"               
 [17] "cb_survey_attribute"             "cb_survey_version"              
 [19] "cb_variant_to_person"            "cdm_source"                     
 [21] "cohort"                          "cohort_attribute"               
 [23] "cohort_definition"               "cohort_definition_ext"          
 [25] "concept"                         "concept_ancestor"               
 [27] "concept_class"                   "concept_relationship"           
 [29] "concept_synonym"                 "condition_era"                  
 [31] "condition_era_ext"               "condition_occurrence"           
 [33] "condition_occurrence_ext"        "cost"                           
 [35] "cost_ext"                        "death"                          
 [37] "delaydiscounting"                "device"                         
 [39] "device_cost"                     "device_cost_ext"                
 [41] "device_exposure"                 "device_exposure_ext"            
 [43] "domain"                          "dose_era"                       
 [45] "dose_era_ext"                    "drug_cost"                      
 [47] "drug_cost_ext"                   "drug_era"                       
 [49] "drug_era_ext"                    "drug_exposure"                  
 [51] "drug_exposure_ext"               "drug_strength"                  
 [53] "ds_activity_summary"             "ds_condition_occurrence"        
 [55] "ds_data_dictionary"              "ds_delaydiscounting_metadata"   
 [57] "ds_delaydiscounting_outcomes"    "ds_delaydiscounting_trial_data" 
 [59] "ds_device"                       "ds_drug_exposure"               
 [61] "ds_emorecog_metadata"            "ds_emorecog_outcomes"           
 [63] "ds_emorecog_trial_data"          "ds_fitbit_device"               
 [65] "ds_flanker_metadata"             "ds_flanker_outcomes"            
 [67] "ds_flanker_trial_data"           "ds_gradcpt_metadata"            
 [69] "ds_gradcpt_outcomes"             "ds_gradcpt_trial_data"          
 [71] "ds_heart_rate_intraday"          "ds_heart_rate_summary"          
 [73] "ds_linking"                      "ds_measurement"                 
 [75] "ds_observation"                  "ds_person"                      
 [77] "ds_procedure_occurrence"         "ds_sleep_daily_summary"         
 [79] "ds_sleep_daily_summary_30dayavg" "ds_sleep_daily_summary_counts"  
 [81] "ds_sleep_level"                  "ds_sleep_level_short"           
 [83] "ds_steps_intraday"               "ds_survey"                      
 [85] "ds_visit_occurrence"             "ds_zip_code_socioeconomic"      
 [87] "emorecog"                        "fact_relationship"              
 [89] "flanker"                         "gradcpt"                        
 [91] "heart_rate_intraday"             "heart_rate_summary"             
 [93] "location"                        "location_ext"                   
 [95] "measurement"                     "measurement_ext"                
 [97] "metadata"                        "observation"                    
 [99] "observation_ext"                 "observation_period"             
[101] "observation_period_ext"          "payer_plan_period"              
[103] "payer_plan_period_ext"           "person"                         
[105] "person_ext"                      "procedure_cost"                 
[107] "procedure_cost_ext"              "procedure_occurrence"           
[109] "procedure_occurrence_ext"        "provider"                       
[111] "provider_ext"                    "relationship"                   
[113] "sleep_daily_summary"             "sleep_daily_summary_30dayavg"   
[115] "sleep_daily_summary_counts"      "sleep_daily_summary_ext"        
[117] "sleep_level"                     "sleep_level_short"              
[119] "source_to_concept_map"           "specimen"                       
[121] "specimen_ext"                    "steps_intraday"                 
[123] "survey_conduct"                  "survey_conduct_ext"             
[125] "visit_cost"                      "visit_cost_ext"                 
[127] "visit_detail"                    "visit_detail_ext"               
[129] "visit_occurrence"                "visit_occurrence_ext"           
[131] "vocabulary"                      "wear_study"                     
[133] "zip3_ses_map"