Get random records of RxNorm and RxNorm Extension Concepts that are:
Valid,
Not a Class,
Belong to the Drug domain
get_test_drug_concepts( conn, conn_fun = "connectAthena()", schema = "omop_vocabulary", limit = 100, verbose = TRUE, render_sql = TRUE )
library(chariot) library(tidyverse) # Get Unfiltered Test Concepts (Invalid Concepts are included) get_test_concepts()#> [2021-05-26 16:03:58] Connected to 'athena' #> [2021-05-26 16:03:58] Overriding cache... Querying Athena... #> [2021-05-26 16:03:58] ✓ Open connection #> [2021-05-26 16:03:58] ✓ JDBC connection #> [2021-05-26 16:03:58] SQL: SELECT * FROM omop_vocabulary.concept ORDER BY RANDOM() LIMIT 100 #> [2021-05-26 16:03:58] Querying... #> [2021-05-26 16:04:06] Querying...complete #> [2021-05-26 16:04:06] ✓ Returned data has more than 0 rows #> [2021-05-26 16:04:06] Caching resultset... #> [2021-05-26 16:04:06] Postgres connection to 'athena' closed#> # A tibble: 100 x 10 #> concept_id concept_name domain_id vocabulary_id concept_class_id #> <int> <chr> <chr> <chr> <chr> #> 1 3255199 Motor vehicle traffic … Observation Nebraska Lex… Event #> 2 4015610 Manipulation of tissue… Procedure SNOMED Procedure #> 3 35915895 Unknown; extension not… Meas Value NAACCR NAACCR Value #> 4 45836804 haloperidol .5mg/1 ORA… Drug NDC 9-digit NDC #> 5 2044080 BIZONEX Drug KDC Brand Name #> 6 4125285 Does not suck Observation SNOMED Clinical Finding #> 7 3627553 SIGVARIS 200 medium/pl… Device SNOMED Physical Object #> 8 41243837 etofylline 154 MG / ho… Drug RxNorm Exten… Clinical Drug B… #> 9 4277824 Entire subcutaneous ti… Spec Anato… SNOMED Body Structure #> 10 46148444 Kerraboot dressing ext… Device SNOMED Physical Object #> # … with 90 more rows, and 5 more variables: standard_concept <chr>, #> # concept_code <chr>, valid_start_date <date>, valid_end_date <date>, #> # invalid_reason <chr>#> [2021-05-26 16:04:06] Connected to 'athena' #> [2021-05-26 16:04:06] Overriding cache... Querying Athena... #> [2021-05-26 16:04:06] ✓ Open connection #> [2021-05-26 16:04:06] ✓ JDBC connection #> [2021-05-26 16:04:06] SQL: SELECT * FROM omop_vocabulary.concept ORDER BY RANDOM() LIMIT 25 #> [2021-05-26 16:04:06] Querying... #> [2021-05-26 16:04:14] Querying...complete #> [2021-05-26 16:04:14] ✓ Returned data has more than 0 rows #> [2021-05-26 16:04:14] Caching resultset... #> [2021-05-26 16:04:14] Postgres connection to 'athena' closed#> # A tibble: 25 x 10 #> concept_id concept_name domain_id vocabulary_id concept_class_id #> <int> <chr> <chr> <chr> <chr> #> 1 42185755 Inpatient Care,discharg… Procedure EDI Procedure #> 2 42539961 LORAZEPAM 2MG TAB,UD Drug VA Product VA Product #> 3 45271221 Ibuprofen 600 MG Oral T… Drug NDC 11-digit NDC #> 4 45619235 Parotitis Condition MeSH Main Heading #> 5 36887713 Hydrocortisone 50 MG/ML… Drug RxNorm Extens… Clinical Drug #> 6 42855798 CALCIUM PANTOTHENATE 25… Drug VA Product VA Product #> 7 41996050 Bardolino Geography OSM 8th level #> 8 45819171 sotalol hydrochloride 1… Drug NDC 9-digit NDC #> 9 36217183 acyclovir Oral Liquid P… Drug RxNorm Clinical Dose G… #> 10 42206306 Reduction of Zygomatic … Procedure EDI Procedure #> # … with 15 more rows, and 5 more variables: standard_concept <chr>, #> # concept_code <chr>, valid_start_date <date>, valid_end_date <date>, #> # invalid_reason <chr># Concepts specific to the Drug domain can be sampled # RxNorm/RxNorm Extension Concepts get_test_drug_concepts(limit = 10)#> [2021-05-26 16:04:14] Connected to 'athena' #> [2021-05-26 16:04:14] Overriding cache... Querying Athena... #> [2021-05-26 16:04:14] ✓ Open connection #> [2021-05-26 16:04:14] ✓ JDBC connection #> [2021-05-26 16:04:14] SQL: SELECT * FROM omop_vocabulary.concept c WHERE c.invalid_reason IS NULL AND c.vocabulary_id IN ('RxNorm', 'RxNorm Extension') AND c.standard_concept <> 'C' AND c.domain_id = 'Drug' ORDER BY RANDOM() LIMIT 10 #> [2021-05-26 16:04:14] Querying... #> [2021-05-26 16:04:17] Querying...complete #> [2021-05-26 16:04:17] ✓ Returned data has more than 0 rows #> [2021-05-26 16:04:17] Caching resultset... #> [2021-05-26 16:04:17] Postgres connection to 'athena' closed#> # A tibble: 10 x 10 #> concept_id concept_name domain_id vocabulary_id concept_class_id #> <int> <chr> <chr> <chr> <chr> #> 1 2038102 ofloxacin Ophthalmic Sol… Drug RxNorm Exten… Branded Drug Fo… #> 2 43856711 Felodipine 2.5 MG [Felod… Drug RxNorm Exten… Branded Drug Co… #> 3 19125207 desonide 0.5 MG/ML Topic… Drug RxNorm Branded Drug #> 4 41289156 Prazepam 10 MG Oral Tabl… Drug RxNorm Exten… Marketed Product #> 5 41082474 Citric Acid / Sodium / t… Drug RxNorm Exten… Branded Drug Fo… #> 6 43819467 Hydrogen Peroxide 238 MG… Drug RxNorm Exten… Branded Drug Co… #> 7 41305124 Chloramphenicol 250 MG /… Drug RxNorm Exten… Clinical Drug #> 8 41405773 0.3 ML Nadroparin 9500 U… Drug RxNorm Exten… Quant Clinical … #> 9 35858763 cephradine 1500 MG [Fanj… Drug RxNorm Exten… Branded Drug Co… #> 10 40935883 Omeprazole 20 MG Oral Ca… Drug RxNorm Exten… Branded Drug Box #> # … with 5 more variables: standard_concept <chr>, concept_code <chr>, #> # valid_start_date <date>, valid_end_date <date>, invalid_reason <chr>#> [2021-05-26 16:04:17] Connected to 'athena' #> [2021-05-26 16:04:17] Overriding cache... Querying Athena... #> [2021-05-26 16:04:17] ✓ Open connection #> [2021-05-26 16:04:17] ✓ JDBC connection #> [2021-05-26 16:04:17] SQL: SELECT * FROM omop_vocabulary.concept c WHERE c.invalid_reason IS NULL AND c.vocabulary_id IN ('ATC', 'HemOnc') AND c.standard_concept = 'C' AND c.domain_id = 'Drug' ORDER BY RANDOM() LIMIT 10 #> [2021-05-26 16:04:17] Querying... #> [2021-05-26 16:04:17] Querying...complete #> [2021-05-26 16:04:17] ✓ Returned data has more than 0 rows #> [2021-05-26 16:04:17] Caching resultset... #> [2021-05-26 16:04:17] Postgres connection to 'athena' closed#> # A tibble: 10 x 10 #> concept_id concept_name domain_id vocabulary_id concept_class_id #> <int> <chr> <chr> <chr> <chr> #> 1 21602687 Thyrotropin Drug ATC ATC 4th #> 2 21600854 Combinations of vitamins Drug ATC ATC 4th #> 3 715861 rosuvastatin and ramipril Drug ATC ATC 5th #> 4 43534841 vilanterol and fluticaso… Drug ATC ATC 5th #> 5 21604825 Drugs used in opioid dep… Drug ATC ATC 4th #> 6 21604308 sodium salicylate; oral Drug ATC ATC 5th #> 7 21604298 tramadol; systemic, rect… Drug ATC ATC 5th #> 8 21600803 multivitamins and iron Drug ATC ATC 5th #> 9 21605123 pegaptanib; ophthalmic Drug ATC ATC 5th #> 10 35807364 Bispecific antibody medi… Drug HemOnc Component Class #> # … with 5 more variables: standard_concept <chr>, concept_code <chr>, #> # valid_start_date <date>, valid_end_date <date>, invalid_reason <chr># Test data can also be taken from tables other than Concept get_test_data(table = "concept_relationship")#> [2021-05-26 16:04:18] Connected to 'athena' #> [2021-05-26 16:04:18] Overriding cache... Querying Athena... #> [2021-05-26 16:04:18] ✓ Open connection #> [2021-05-26 16:04:18] ✓ JDBC connection #> [2021-05-26 16:04:18] SQL: SELECT * FROM omop_vocabulary.concept_relationship ORDER BY RANDOM() LIMIT 100 #> [2021-05-26 16:04:18] Querying... #> [2021-05-26 16:04:50] Querying...complete #> [2021-05-26 16:04:50] ✓ Returned data has more than 0 rows #> [2021-05-26 16:04:50] Caching resultset... #> [2021-05-26 16:04:50] Postgres connection to 'athena' closed#> # A tibble: 100 x 6 #> concept_id_1 concept_id_2 relationship_id valid_start_date valid_end_date #> <int> <int> <chr> <date> <date> #> 1 21053217 21082608 Marketed form of 2015-08-17 2099-12-31 #> 2 44160243 43161012 Marketed form of 2017-10-06 2099-12-31 #> 3 36280550 36280550 Mapped from 2018-01-10 2099-12-31 #> 4 43569605 43749039 Brand name of 2016-07-01 2099-12-31 #> 5 21142162 21020612 Has supplier 2015-08-17 2099-12-31 #> 6 19082573 43837626 RxNorm dose form of 2016-07-01 2099-12-31 #> 7 21019818 21143606 Supplier of 2015-08-17 2099-12-31 #> 8 4315201 4350851 Subsumes 2010-06-07 2099-12-31 #> 9 42908129 42979854 RxNorm - Source eq 2018-08-21 2099-12-31 #> 10 41252805 40823300 Tradename of 2017-07-18 2099-12-31 #> # … with 90 more rows, and 1 more variable: invalid_reason <chr>#> [2021-05-26 16:04:50] Connected to 'athena' #> [2021-05-26 16:04:50] Overriding cache... Querying Athena... #> [2021-05-26 16:04:50] ✓ Open connection #> [2021-05-26 16:04:50] ✓ JDBC connection #> [2021-05-26 16:04:50] SQL: SELECT * FROM omop_vocabulary.concept_ancestor ORDER BY RANDOM() LIMIT 10 #> [2021-05-26 16:04:50] Querying... #> [2021-05-26 16:05:22] Querying...complete #> [2021-05-26 16:05:22] ✓ Returned data has more than 0 rows #> [2021-05-26 16:05:22] Caching resultset... #> [2021-05-26 16:05:22] Postgres connection to 'athena' closed#> # A tibble: 10 x 4 #> ancestor_concept_… descendant_concep… min_levels_of_sepa… max_levels_of_sepa… #> <int> <int> <int> <int> #> 1 45678514 41187433 2 2 #> 2 43637901 43845443 1 1 #> 3 1563829 21140705 2 3 #> 4 2722669 2835009 3 3 #> 5 35508910 44102509 2 2 #> 6 46305803 21062508 2 2 #> 7 45622994 44037824 2 2 #> 8 36206173 3007145 4 5 #> 9 4279903 4013335 1 1 #> 10 4001202 42605398 5 5