Search for an exact phrase in the Concept Synonym table.
search_exact_phrase( phrase, case_insensitive = TRUE, vocab_schema = "omop_vocabulary", conn, conn_fun = "connectAthena()", cache_only = FALSE, skip_cache = FALSE, override_cache = FALSE, cache_resultset = TRUE, render_sql = TRUE, render_only = FALSE, verbose = TRUE, sleepTime = 1 )
| phrase | String to search. |
|---|---|
| case_insensitive | Should the search ignore case?, Default: TRUE |
| vocab_schema | OMOP Vocabulary schema, Default: 'omop_vocabulary' |
| conn | Connection object. If provided, diverts queries to the connection instead of the local Athena instance without caching features. |
| cache_only | Loads from the cache and does not query the database. A NULL object is returned if a resultset was not cached. |
| 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_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. |
Tibble of all the matching Concept Table fields and an added `concept_synonyms` field of a pipe-separated aggregate of all the synonyms, including `concept_name`, in the Concept Synonym table.
library(tidyverse) library(chariot) # Search the OMOP Vocabulary for an exact match to the phrase # "Myocardial Infarction" insensitive to case. search_exact_phrase(phrase = "Myocardial Infarction", case_insensitive = TRUE)#> [2021-05-26 16:07:43] Connected to 'athena' #> [2021-05-26 16:07:43] Loading Cache... #> [2021-05-26 16:07:43] Cached SQL: #> SELECT c.*, STRING_AGG(cs.concept_synonym_name, '|') AS concept_synonyms #> FROM omop_vocabulary.concept_synonym cs #> INNER JOIN omop_vocabulary.concept c #> ON c.concept_id = cs.concept_id #> WHERE LOWER(cs.concept_synonym_name) = LOWER('Myocardial Infarction') #> GROUP BY c.concept_id, #> c.concept_name, #> c.domain_id, #> c.vocabulary_id, #> c.concept_class_id, #> c.standard_concept, #> c.concept_code, #> c.valid_start_date, #> c.valid_end_date, #> c.invalid_reason; #> #> [2021-05-26 16:07:43] Cached resultset found... #> [2021-05-26 16:07:43] Postgres connection to 'athena' closed#> # A tibble: 1 x 11 #> concept_id concept_name domain_id vocabulary_id concept_class_id #> <int> <chr> <chr> <chr> <chr> #> 1 40623902 Coronary artery thrombosis Condition SNOMED Clinical Finding #> # … with 6 more variables: standard_concept <chr>, concept_code <chr>, #> # valid_start_date <date>, valid_end_date <date>, invalid_reason <chr>, #> # concept_synonyms <chr># Repeat with case sensitivity search_exact_phrase(phrase = "Myocardial Infarction", case_insensitive = FALSE)#> [2021-05-26 16:07:43] Connected to 'athena' #> [2021-05-26 16:07:43] Loading Cache... #> [2021-05-26 16:07:43] Cached SQL: #> SELECT c.*, STRING_AGG(cs.concept_synonym_name, '|') AS concept_synonyms #> FROM omop_vocabulary.concept_synonym cs #> INNER JOIN omop_vocabulary.concept c #> ON c.concept_id = cs.concept_id #> WHERE cs.concept_synonym_name = 'Myocardial Infarction' #> GROUP BY c.concept_id, #> c.concept_name, #> c.domain_id, #> c.vocabulary_id, #> c.concept_class_id, #> c.standard_concept, #> c.concept_code, #> c.valid_start_date, #> c.valid_end_date, #> c.invalid_reason; #> #> [2021-05-26 16:07:43] Cached resultset found... #> [2021-05-26 16:07:43] Postgres connection to 'athena' closed#> # A tibble: 0 x 11 #> # … with 11 variables: concept_id <int>, concept_name <chr>, domain_id <chr>, #> # vocabulary_id <chr>, concept_class_id <chr>, standard_concept <chr>, #> # concept_code <chr>, valid_start_date <date>, valid_end_date <date>, #> # invalid_reason <chr>, concept_synonyms <chr># Search concepts that contain the phrase "Myocardial Infarction" search_like_phrase(phrase = "Myocardial infarction")#> [2021-05-26 16:07:43] Connected to 'athena' #> [2021-05-26 16:07:43] Loading Cache... #> [2021-05-26 16:07:43] Cached SQL: #> SELECT c.*, STRING_AGG(cs.concept_synonym_name, '|') AS concept_synonyms #> FROM omop_vocabulary.concept_synonym cs #> INNER JOIN omop_vocabulary.concept c #> ON c.concept_id = cs.concept_id #> WHERE LOWER(cs.concept_synonym_name) LIKE LOWER('%Myocardial infarction%') #> GROUP BY c.concept_id, #> c.concept_name, #> c.domain_id, #> c.vocabulary_id, #> c.concept_class_id, #> c.standard_concept, #> c.concept_code, #> c.valid_start_date, #> c.valid_end_date, #> c.invalid_reason; #> #> [2021-05-26 16:07:43] Cached resultset found... #> [2021-05-26 16:07:43] Postgres connection to 'athena' closed#> # A tibble: 258 x 11 #> concept_id concept_name domain_id vocabulary_id concept_class_id #> <int> <chr> <chr> <chr> <chr> #> 1 312327 Acute myocardial infarct… Condition SNOMED Clinical Finding #> 2 314666 Old myocardial infarction Condition SNOMED Clinical Finding #> 3 319038 Postmyocardial infarctio… Condition SNOMED Clinical Finding #> 4 319039 Acute posterior myocardi… Condition SNOMED Clinical Finding #> 5 434376 Acute myocardial infarct… Condition SNOMED Clinical Finding #> 6 435561 Certain current complica… Condition SNOMED Clinical Finding #> 7 436706 Acute myocardial infarct… Condition SNOMED Clinical Finding #> 8 438170 Acute myocardial infarct… Condition SNOMED Clinical Finding #> 9 438172 Atrial septal defect due… Condition SNOMED Clinical Finding #> 10 438438 Acute myocardial infarct… Condition SNOMED Clinical Finding #> # … with 248 more rows, and 6 more variables: standard_concept <chr>, #> # concept_code <chr>, valid_start_date <date>, valid_end_date <date>, #> # invalid_reason <chr>, concept_synonyms <chr>#> [2021-05-26 16:07:44] Connected to 'athena' #> [2021-05-26 16:07:44] Loading Cache... #> [2021-05-26 16:07:44] Cached SQL: #> SELECT c.*, STRING_AGG(cs.concept_synonym_name, '|') AS concept_synonyms #> FROM omop_vocabulary.concept_synonym cs #> INNER JOIN omop_vocabulary.concept c #> ON c.concept_id = cs.concept_id #> WHERE cs.concept_synonym_name LIKE '%Myocardial infarction%' #> GROUP BY c.concept_id, #> c.concept_name, #> c.domain_id, #> c.vocabulary_id, #> c.concept_class_id, #> c.standard_concept, #> c.concept_code, #> c.valid_start_date, #> c.valid_end_date, #> c.invalid_reason; #> #> [2021-05-26 16:07:44] Cached resultset found... #> [2021-05-26 16:07:44] Postgres connection to 'athena' closed#> # A tibble: 29 x 11 #> concept_id concept_name domain_id vocabulary_id concept_class_id #> <int> <chr> <chr> <chr> <chr> #> 1 1014313 Myocardial infarction in… Observat… LOINC LOINC Component #> 2 4010422 Myocardial infarction ed… Procedure SNOMED Procedure #> 3 4077067 FH: MI - Myocardial infa… Observat… SNOMED Context-depende… #> 4 4108215 Coronary thrombosis not … Condition SNOMED Clinical Finding #> 5 4145793 FH: Myocardial infarction Observat… SNOMED Context-depende… #> 6 4154704 Myocardial infarction wi… Condition SNOMED Clinical Finding #> 7 4169510 FH: MI - Myocardial infa… Observat… SNOMED Context-depende… #> 8 4170094 Myocardial infarction in… Condition SNOMED Clinical Finding #> 9 4170147 FH: Myocardial infarctio… Observat… SNOMED Context-depende… #> 10 4173499 FH: Myocardial infarctio… Observat… SNOMED Context-depende… #> # … with 19 more rows, and 6 more variables: standard_concept <chr>, #> # concept_code <chr>, valid_start_date <date>, valid_end_date <date>, #> # invalid_reason <chr>, concept_synonyms <chr># Search concepts that start with or end with the phrase "Myocardial Infarction" search_starts_with_phrase(phrase = "Myocardial infarction")#> [2021-05-26 16:07:44] Connected to 'athena' #> [2021-05-26 16:07:44] Loading Cache... #> [2021-05-26 16:07:44] Cached SQL: #> SELECT c.*, STRING_AGG(cs.concept_synonym_name, '|') AS concept_synonyms #> FROM omop_vocabulary.concept_synonym cs #> INNER JOIN omop_vocabulary.concept c #> ON c.concept_id = cs.concept_id #> WHERE LOWER(cs.concept_synonym_name) LIKE LOWER('Myocardial infarction%') #> GROUP BY c.concept_id, #> c.concept_name, #> c.domain_id, #> c.vocabulary_id, #> c.concept_class_id, #> c.standard_concept, #> c.concept_code, #> c.valid_start_date, #> c.valid_end_date, #> c.invalid_reason; #> #> [2021-05-26 16:07:44] Cached resultset found... #> [2021-05-26 16:07:44] Postgres connection to 'athena' closed#> # A tibble: 18 x 11 #> concept_id concept_name domain_id vocabulary_id concept_class_id #> <int> <chr> <chr> <chr> <chr> #> 1 1014313 Myocardial infarction i… Observati… LOINC LOINC Component #> 2 4010422 Myocardial infarction e… Procedure SNOMED Procedure #> 3 4154704 Myocardial infarction w… Condition SNOMED Clinical Finding #> 4 4170094 Myocardial infarction i… Condition SNOMED Clinical Finding #> 5 4329847 Myocardial infarction Condition SNOMED Clinical Finding #> 6 36715182 Myocardial infarction c… Type Conc… SNOMED Qualifier Value #> 7 37309626 Myocardial infarction d… Condition SNOMED Clinical Finding #> 8 40225388 Inferior Wall Myocardia… Drug NDFRT Ind / CI #> 9 40225549 Anterior Wall Myocardia… Drug NDFRT Ind / CI #> 10 40323863 Myocardial infarction (… Condition SNOMED Clinical Finding #> 11 40345176 Myocardial infarction (… Condition SNOMED Clinical Finding #> 12 40398853 Myocardial infarction: … Condition SNOMED Clinical Finding #> 13 40436955 Myocardial infarction Condition SNOMED Clinical Finding #> 14 40623902 Coronary artery thrombo… Condition SNOMED Clinical Finding #> 15 40761439 Ever told by doctor tha… Observati… LOINC Clinical Observ… #> 16 40769284 Myocardial infarction [… Observati… LOINC Clinical Observ… #> 17 42537824 Myocardial infarction c… Type Conc… SNOMED Record Artifact #> 18 45616764 Myocardial Infarction Condition MeSH Main Heading #> # … with 6 more variables: standard_concept <chr>, concept_code <chr>, #> # valid_start_date <date>, valid_end_date <date>, invalid_reason <chr>, #> # concept_synonyms <chr>#> [2021-05-26 16:07:44] Connected to 'athena' #> [2021-05-26 16:07:44] Loading Cache... #> [2021-05-26 16:07:44] Cached SQL: #> SELECT c.*, STRING_AGG(cs.concept_synonym_name, '|') AS concept_synonyms #> FROM omop_vocabulary.concept_synonym cs #> INNER JOIN omop_vocabulary.concept c #> ON c.concept_id = cs.concept_id #> WHERE LOWER(cs.concept_synonym_name) LIKE LOWER('%Myocardial infarction') #> GROUP BY c.concept_id, #> c.concept_name, #> c.domain_id, #> c.vocabulary_id, #> c.concept_class_id, #> c.standard_concept, #> c.concept_code, #> c.valid_start_date, #> c.valid_end_date, #> c.invalid_reason; #> #> [2021-05-26 16:07:44] Cached resultset found... #> [2021-05-26 16:07:44] Postgres connection to 'athena' closed#> # A tibble: 55 x 11 #> concept_id concept_name domain_id vocabulary_id concept_class_id #> <int> <chr> <chr> <chr> <chr> #> 1 312327 Acute myocardial infarct… Condition SNOMED Clinical Finding #> 2 314666 Old myocardial infarction Condition SNOMED Clinical Finding #> 3 434376 Acute myocardial infarct… Condition SNOMED Clinical Finding #> 4 436706 Acute myocardial infarct… Condition SNOMED Clinical Finding #> 5 438170 Acute myocardial infarct… Condition SNOMED Clinical Finding #> 6 438172 Atrial septal defect due… Condition SNOMED Clinical Finding #> 7 438438 Acute myocardial infarct… Condition SNOMED Clinical Finding #> 8 438447 Acute myocardial infarct… Condition SNOMED Clinical Finding #> 9 718257 Non-ST Elevated Myocardi… Drug NDFRT Ind / CI #> 10 718267 ST Elevation Myocardial … Drug NDFRT Ind / CI #> # … with 45 more rows, and 6 more variables: standard_concept <chr>, #> # concept_code <chr>, valid_start_date <date>, valid_end_date <date>, #> # invalid_reason <chr>, concept_synonyms <chr># Search a phrase by splitting into smaller words that are each included in a # 'LIKE' clause search_split_phrase(phrase = "Myocardial infarction", split = " ")#> [2021-05-26 16:07:44] Connected to 'athena' #> [2021-05-26 16:07:44] Loading Cache... #> [2021-05-26 16:07:44] Cached SQL: #> SELECT c.*, STRING_AGG(cs.concept_synonym_name, '|') AS concept_synonyms #> FROM omop_vocabulary.concept_synonym cs #> INNER JOIN omop_vocabulary.concept c #> ON c.concept_id = cs.concept_id #> WHERE LOWER(cs.concept_synonym_name) LIKE LOWER('%Myocardial%') #> AND LOWER(cs.concept_synonym_name) LIKE LOWER('%infarction%') #> GROUP BY #> c.concept_id, #> c.concept_name, #> c.domain_id, #> c.vocabulary_id, #> c.concept_class_id, #> c.standard_concept, #> c.concept_code, #> c.valid_start_date, #> c.valid_end_date, #> c.invalid_reason; #> [2021-05-26 16:07:44] Cached resultset found... #> [2021-05-26 16:07:44] Postgres connection to 'athena' closed#> # A tibble: 261 x 11 #> concept_id concept_name domain_id vocabulary_id concept_class_id #> <int> <chr> <chr> <chr> <chr> #> 1 312327 Acute myocardial infarct… Condition SNOMED Clinical Finding #> 2 314666 Old myocardial infarction Condition SNOMED Clinical Finding #> 3 319038 Postmyocardial infarctio… Condition SNOMED Clinical Finding #> 4 319039 Acute posterior myocardi… Condition SNOMED Clinical Finding #> 5 434376 Acute myocardial infarct… Condition SNOMED Clinical Finding #> 6 435561 Certain current complica… Condition SNOMED Clinical Finding #> 7 436706 Acute myocardial infarct… Condition SNOMED Clinical Finding #> 8 438170 Acute myocardial infarct… Condition SNOMED Clinical Finding #> 9 438172 Atrial septal defect due… Condition SNOMED Clinical Finding #> 10 438438 Acute myocardial infarct… Condition SNOMED Clinical Finding #> # … with 251 more rows, and 6 more variables: standard_concept <chr>, #> # concept_code <chr>, valid_start_date <date>, valid_end_date <date>, #> # invalid_reason <chr>, concept_synonyms <chr>