Query a Common Data Model table with automatic joins with any Concept Id to the Concept table with the appropriate prefix.
queryCDM( sql_statement, write_schema = "patelm9", vocab_schema = "omop_vocabulary", conn, conn_fun = "connectAthena()", skip_cache = FALSE, override_cache = FALSE, cache_only = FALSE, cache_resultset = TRUE, render_sql = TRUE, render_only = FALSE, verbose = TRUE, sleepTime = 1 )
| sql_statement | SQL query |
|---|---|
| write_schema | Schema to write the staged data to for a join to Concept table. |
| vocab_schema | Schema where to Concept table can be found. |
| conn | Connection object. If provided, diverts queries to the connection instead of the local Athena instance without caching features. |
| skip_cache | Skip the caching altogether and directly query the database. |
| override_cache | If TRUE, the cache will not be loaded and will be overwritten by a new query. For override_cache to take effect, skip_cache should be FALSE. |
| cache_only | Loads from the cache and does not query the database. A NULL object is returned if a resultset was not cached. |
| cache_resultset | (deprecated) If TRUE, the resultset from the query will first be loaded from the cache. The query will be executed if a cached resultset is not retrieved for this particular query, after which the resultset will be cached. If FALSE, Athena or conn will be directly queried without any caching operations. |
| render_sql | If TRUE, the SQL will be printed back in the console prior to execution. Default: FALSE |
| verbose | If TRUE, prints loading and querying operations messages to the console. Default: FALSE |
| sleepTime | Argument for `Sys.sleep()` in between queries to allow for halting function execution, especially in cases where other chariot functions are executing multiple queries in succession and require cancellation. |
a [tibble][tibble::tibble-package]
parse_expr
str_remove
select_all,vars,reexports,mutate-joins
Other query functions:
queryAncestors(),
queryAthena(),
queryCode(),
queryConceptClassRelationships(),
queryConceptId(),
queryDescendants(),
queryRelationships(),
querySynonyms(),
queryVocabularyRelationships()
#> [2021-05-26 16:07:35] Connected to 'polyester'queryCDM(sql_statement = "SELECT * FROM omop_cdm.drug_exposure ORDER BY RANDOM() LIMIT 5;", write_schema = "omop_cdm", vocab_schema = "omop_cdm", conn = conn)#> [2021-05-26 16:07:35] Loading Cache... #> [2021-05-26 16:07:35] Cached SQL: SELECT * FROM omop_cdm.drug_exposure ORDER BY RANDOM() LIMIT 5; #> [2021-05-26 16:07:35] Cached resultset found... #> [2021-05-26 16:07:35] Target column: drug_concept_id #> [2021-05-26 16:07:35] Dropping omop_cdm.V20210526160735... #> [2021-05-26 16:07:35] ✓ Open connection #> [2021-05-26 16:07:35] ✓ JDBC connection #> [2021-05-26 16:07:35] SQL: DROP TABLE IF EXISTS omop_cdm.V20210526160735; #> [2021-05-26 16:07:35] Sending... #> [2021-05-26 16:07:35] Sending...complete #> [2021-05-26 16:07:35] Dropping omop_cdm.V20210526160735...complete #> [2021-05-26 16:07:35] ✓ Data 'data' has more than 0 rows #> [2021-05-26 16:07:35] Writing omop_cdm.V20210526160735... #> [2021-05-26 16:07:35] Writing omop_cdm.V20210526160735...complete #> [2021-05-26 16:07:35] ✓ Open connection #> [2021-05-26 16:07:35] ✓ JDBC connection #> [2021-05-26 16:07:35] SQL: SELECT a.*, b.* FROM omop_cdm.V20210526160735 a LEFT JOIN omop_cdm.concept b ON a.drug_concept_id = b.concept_id WHERE b.invalid_reason IS NULL #> [2021-05-26 16:07:35] Querying... #> [2021-05-26 16:07:35] Querying...complete #> [2021-05-26 16:07:35] ! Returned data has 0 rows #> [2021-05-26 16:07:35] Dropping omop_cdm.V20210526160735... #> [2021-05-26 16:07:35] ✓ Open connection #> [2021-05-26 16:07:35] ✓ JDBC connection #> [2021-05-26 16:07:35] SQL: DROP TABLE IF EXISTS omop_cdm.V20210526160735; #> [2021-05-26 16:07:35] Sending... #> [2021-05-26 16:07:35] Sending...complete #> [2021-05-26 16:07:35] Dropping omop_cdm.V20210526160735...complete#>#> [2021-05-26 16:07:35] Target column: drug_type_concept_id #> [2021-05-26 16:07:35] Dropping omop_cdm.V20210526160735... #> [2021-05-26 16:07:35] ✓ Open connection #> [2021-05-26 16:07:35] ✓ JDBC connection #> [2021-05-26 16:07:35] SQL: DROP TABLE IF EXISTS omop_cdm.V20210526160735; #> [2021-05-26 16:07:35] Sending... #> [2021-05-26 16:07:35] Sending...complete #> [2021-05-26 16:07:35] Dropping omop_cdm.V20210526160735...complete #> [2021-05-26 16:07:35] ✓ Data 'data' has more than 0 rows #> [2021-05-26 16:07:35] Writing omop_cdm.V20210526160735... #> [2021-05-26 16:07:35] Writing omop_cdm.V20210526160735...complete #> [2021-05-26 16:07:35] ✓ Open connection #> [2021-05-26 16:07:35] ✓ JDBC connection #> [2021-05-26 16:07:35] SQL: SELECT a.*, b.* FROM omop_cdm.V20210526160735 a LEFT JOIN omop_cdm.concept b ON a.drug_type_concept_id = b.concept_id WHERE b.invalid_reason IS NULL #> [2021-05-26 16:07:35] Querying... #> [2021-05-26 16:07:35] Querying...complete #> [2021-05-26 16:07:35] ! Returned data has 0 rows #> [2021-05-26 16:07:35] Dropping omop_cdm.V20210526160735... #> [2021-05-26 16:07:35] ✓ Open connection #> [2021-05-26 16:07:35] ✓ JDBC connection #> [2021-05-26 16:07:35] SQL: DROP TABLE IF EXISTS omop_cdm.V20210526160735; #> [2021-05-26 16:07:35] Sending... #> [2021-05-26 16:07:35] Sending...complete #> [2021-05-26 16:07:35] Dropping omop_cdm.V20210526160735...complete#>#> [2021-05-26 16:07:35] Target column: route_concept_id #> [2021-05-26 16:07:35] Dropping omop_cdm.V20210526160735... #> [2021-05-26 16:07:35] ✓ Open connection #> [2021-05-26 16:07:35] ✓ JDBC connection #> [2021-05-26 16:07:35] SQL: DROP TABLE IF EXISTS omop_cdm.V20210526160735; #> [2021-05-26 16:07:35] Sending... #> [2021-05-26 16:07:35] Sending...complete #> [2021-05-26 16:07:35] Dropping omop_cdm.V20210526160735...complete #> [2021-05-26 16:07:35] ✓ Data 'data' has more than 0 rows #> [2021-05-26 16:07:35] Writing omop_cdm.V20210526160735... #> [2021-05-26 16:07:35] Writing omop_cdm.V20210526160735...complete #> [2021-05-26 16:07:35] ✓ Open connection #> [2021-05-26 16:07:35] ✓ JDBC connection #> [2021-05-26 16:07:35] SQL: SELECT a.*, b.* FROM omop_cdm.V20210526160735 a LEFT JOIN omop_cdm.concept b ON a.route_concept_id = b.concept_id WHERE b.invalid_reason IS NULL #> [2021-05-26 16:07:35] Querying... #> [2021-05-26 16:07:35] Querying...complete #> [2021-05-26 16:07:35] ! Returned data has 0 rows #> [2021-05-26 16:07:35] Dropping omop_cdm.V20210526160735... #> [2021-05-26 16:07:35] ✓ Open connection #> [2021-05-26 16:07:35] ✓ JDBC connection #> [2021-05-26 16:07:35] SQL: DROP TABLE IF EXISTS omop_cdm.V20210526160735; #> [2021-05-26 16:07:35] Sending... #> [2021-05-26 16:07:35] Sending...complete #> [2021-05-26 16:07:35] Dropping omop_cdm.V20210526160735...complete#>#> [2021-05-26 16:07:35] Target column: drug_source_concept_id #> [2021-05-26 16:07:35] Dropping omop_cdm.V20210526160735... #> [2021-05-26 16:07:35] ✓ Open connection #> [2021-05-26 16:07:35] ✓ JDBC connection #> [2021-05-26 16:07:35] SQL: DROP TABLE IF EXISTS omop_cdm.V20210526160735; #> [2021-05-26 16:07:35] Sending... #> [2021-05-26 16:07:35] Sending...complete #> [2021-05-26 16:07:35] Dropping omop_cdm.V20210526160735...complete #> [2021-05-26 16:07:35] ✓ Data 'data' has more than 0 rows #> [2021-05-26 16:07:35] Writing omop_cdm.V20210526160735... #> [2021-05-26 16:07:35] Writing omop_cdm.V20210526160735...complete #> [2021-05-26 16:07:35] ✓ Open connection #> [2021-05-26 16:07:35] ✓ JDBC connection #> [2021-05-26 16:07:35] SQL: SELECT a.*, b.* FROM omop_cdm.V20210526160735 a LEFT JOIN omop_cdm.concept b ON a.drug_source_concept_id = b.concept_id WHERE b.invalid_reason IS NULL #> [2021-05-26 16:07:35] Querying... #> [2021-05-26 16:07:35] Querying...complete #> [2021-05-26 16:07:35] ! Returned data has 0 rows #> [2021-05-26 16:07:35] Dropping omop_cdm.V20210526160735... #> [2021-05-26 16:07:35] ✓ Open connection #> [2021-05-26 16:07:35] ✓ JDBC connection #> [2021-05-26 16:07:35] SQL: DROP TABLE IF EXISTS omop_cdm.V20210526160735; #> [2021-05-26 16:07:35] Sending... #> [2021-05-26 16:07:35] Sending...complete #> [2021-05-26 16:07:35] Dropping omop_cdm.V20210526160735...complete#>#> # A tibble: 5 x 59 #> drug_exposure_id person_id drug_concept_id drug_exposure_start_date #> <dbl> <dbl> <int> <date> #> 1 205992 3353 40163924 1973-01-31 #> 2 310122 4866 1511248 2020-12-03 #> 3 373629 5918 19009384 1990-11-17 #> 4 346270 5498 40213154 2018-02-26 #> 5 204409 3321 19009384 1952-11-18 #> # … with 55 more variables: drug_exposure_start_datetime <dttm>, #> # drug_exposure_end_date <date>, drug_exposure_end_datetime <dttm>, #> # verbatim_end_date <date>, drug_type_concept_id <int>, stop_reason <chr>, #> # refills <int>, quantity <dbl>, days_supply <int>, sig <chr>, #> # route_concept_id <int>, lot_number <chr>, provider_id <dbl>, #> # visit_occurrence_id <dbl>, visit_detail_id <dbl>, drug_source_value <chr>, #> # drug_source_concept_id <int>, route_source_value <chr>, #> # dose_unit_source_value <chr>, drug_concept_name <chr>, #> # drug_domain_id <chr>, drug_vocabulary_id <chr>, #> # drug_concept_class_id <chr>, drug_standard_concept <chr>, #> # drug_concept_code <chr>, drug_valid_start_date <date>, #> # drug_valid_end_date <date>, drug_invalid_reason <chr>, #> # drug_type_concept_name <chr>, drug_type_domain_id <chr>, #> # drug_type_vocabulary_id <chr>, drug_type_concept_class_id <chr>, #> # drug_type_standard_concept <chr>, drug_type_concept_code <chr>, #> # drug_type_valid_start_date <date>, drug_type_valid_end_date <date>, #> # drug_type_invalid_reason <chr>, route_concept_name <chr>, #> # route_domain_id <chr>, route_vocabulary_id <chr>, #> # route_concept_class_id <chr>, route_standard_concept <chr>, #> # route_concept_code <chr>, route_valid_start_date <date>, #> # route_valid_end_date <date>, route_invalid_reason <chr>, #> # drug_source_concept_name <chr>, drug_source_domain_id <chr>, #> # drug_source_vocabulary_id <chr>, drug_source_concept_class_id <chr>, #> # drug_source_standard_concept <chr>, drug_source_concept_code <chr>, #> # drug_source_valid_start_date <date>, drug_source_valid_end_date <date>, #> # drug_source_invalid_reason <chr>#> [2021-05-26 16:07:35] Postgres connection to 'polyester' closed