Get random records of ATC and HemOnc Drug Classes that are:
Valid,
Class,
Belong to the Drug domain
get_test_drug_classes( 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:02:31] Connected to 'athena' #> [2021-05-26 16:02:31] Overriding cache... Querying Athena... #> [2021-05-26 16:02:31] ✓ Open connection #> [2021-05-26 16:02:31] ✓ JDBC connection #> [2021-05-26 16:02:31] SQL: SELECT * FROM omop_vocabulary.concept ORDER BY RANDOM() LIMIT 100 #> [2021-05-26 16:02:31] Querying... #> [2021-05-26 16:02:39] Querying...complete #> [2021-05-26 16:02:39] ✓ Returned data has more than 0 rows #> [2021-05-26 16:02:39] Caching resultset... #> [2021-05-26 16:02:39] 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 44842295 Acetaminophen 650 mg/1 … Drug NDC 9-digit NDC #> 2 44507483 Bos taurus brainstem pr… Drug RxNorm Ingredient #> 3 40988048 Ibuprofen Oral Solution… Drug RxNorm Exten… Branded Drug Fo… #> 4 42720808 Fluorine 0.997 MG/ML Or… Drug RxNorm Exten… Clinical Drug #> 5 2744818 Drainage of Nasal Septu… Procedure ICD10PCS ICD10PCS #> 6 41141804 desmopressin 0.002 MG/M… Drug RxNorm Exten… Branded Drug Co… #> 7 45594029 Nondisplaced segmental … Condition ICD10CM 7-char billing … #> 8 44447719 rosuvastatin calcium 40… Drug NDC 11-digit NDC #> 9 3597328 Ferrous glycine sul 141… Observati… SNOMED Context-depende… #> 10 43593004 30 ML Vitamin B 12 0.05… Drug RxNorm Exten… Quant Clinical … #> # … 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:02:39] Connected to 'athena' #> [2021-05-26 16:02:39] Overriding cache... Querying Athena... #> [2021-05-26 16:02:39] ✓ Open connection #> [2021-05-26 16:02:39] ✓ JDBC connection #> [2021-05-26 16:02:39] SQL: SELECT * FROM omop_vocabulary.concept ORDER BY RANDOM() LIMIT 25 #> [2021-05-26 16:02:39] Querying... #> [2021-05-26 16:02:47] Querying...complete #> [2021-05-26 16:02:47] ✓ Returned data has more than 0 rows #> [2021-05-26 16:02:47] Caching resultset... #> [2021-05-26 16:02:47] 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 45763336 Anesthesia workstation v… Device SNOMED Physical Object #> 2 3595353 Simplicity 2 flange two-… Observat… SNOMED Context-depende… #> 3 46098080 Helium 79% / Oxygen 21% … Drug SNOMED Pharma/Biol Pro… #> 4 46325338 aluminum chlorohydrate 7… Drug SPL OTC Drug #> 5 41984674 Gemarkung Burgfelden Geography OSM 9th level #> 6 42751024 Operations on the urethr… Procedure OPS Procedure #> 7 42064142 Cortland Geography OSM 8th level #> 8 45182355 Valproic Acid 50 MG/ML O… Drug NDC 11-digit NDC #> 9 44510726 Other specified other ex… Procedure OPCS4 Procedure #> 10 21460362 Germoloids Complete Drug dm+d AMP #> # … 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:02:47] Connected to 'athena' #> [2021-05-26 16:02:47] Overriding cache... Querying Athena... #> [2021-05-26 16:02:47] ✓ Open connection #> [2021-05-26 16:02:47] ✓ JDBC connection #> [2021-05-26 16:02:47] 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:02:47] Querying... #> [2021-05-26 16:02:50] Querying...complete #> [2021-05-26 16:02:50] ✓ Returned data has more than 0 rows #> [2021-05-26 16:02:50] Caching resultset... #> [2021-05-26 16:02:50] 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 43193054 Pramipexole 0.18 MG Ora… Drug RxNorm Extens… Marketed Product #> 2 43732461 montelukast 4 MG Chewab… Drug RxNorm Extens… Branded Drug #> 3 35790813 1010 MG Salicylic Acid … Drug RxNorm Extens… Quant Clinical … #> 4 35896995 Filgotinib 100 MG Oral … Drug RxNorm Extens… Branded Drug Box #> 5 41244259 Allium sativum whole ex… Drug RxNorm Extens… Clinical Drug #> 6 21059427 100 ML Pseudoephedrine … Drug RxNorm Extens… Marketed Product #> 7 43186663 50 ML gemcitabine 38 MG… Drug RxNorm Extens… Quant Branded B… #> 8 36261847 pregabalin Oral Capsule… Drug RxNorm Extens… Branded Drug Fo… #> 9 41020930 Strychnos nux-vomica wh… Drug RxNorm Extens… Clinical Drug #> 10 40703180 500000 MG Paraffin 0.1 … Drug RxNorm Extens… Quant Clinical … #> # … with 5 more variables: standard_concept <chr>, concept_code <chr>, #> # valid_start_date <date>, valid_end_date <date>, invalid_reason <chr># HemOnc/ATC Classes get_test_drug_classes(limit = 10)#> [2021-05-26 16:02:50] Connected to 'athena' #> [2021-05-26 16:02:50] Overriding cache... Querying Athena... #> [2021-05-26 16:02:50] ✓ Open connection #> [2021-05-26 16:02:50] ✓ JDBC connection #> [2021-05-26 16:02:50] 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:02:50] Querying... #> [2021-05-26 16:02:50] Querying...complete #> [2021-05-26 16:02:50] ✓ Returned data has more than 0 rows #> [2021-05-26 16:02:50] Caching resultset... #> [2021-05-26 16:02:51] 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 21602816 thiamphenicol; systemic Drug ATC ATC 5th #> 2 43534746 lorcaserin Drug ATC ATC 5th #> 3 43534829 tetramethylglycoluril Drug ATC ATC 5th #> 4 45893490 brinzolamide, combinatio… Drug ATC ATC 5th #> 5 21601214 electrolytes in combinat… Drug ATC ATC 5th #> 6 21600964 phenindione; oral Drug ATC ATC 5th #> 7 21603045 telavancin; systemic Drug ATC ATC 5th #> 8 21603947 bumadizone; oral Drug ATC ATC 5th #> 9 21604885 emetine, combinations Drug ATC ATC 5th #> 10 21600943 imiglucerase; parenteral Drug ATC ATC 5th #> # … 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:02:51] Connected to 'athena' #> [2021-05-26 16:02:51] Overriding cache... Querying Athena... #> [2021-05-26 16:02:51] ✓ Open connection #> [2021-05-26 16:02:51] ✓ JDBC connection #> [2021-05-26 16:02:51] SQL: SELECT * FROM omop_vocabulary.concept_relationship ORDER BY RANDOM() LIMIT 100 #> [2021-05-26 16:02:51] Querying... #> [2021-05-26 16:03:23] Querying...complete #> [2021-05-26 16:03:23] ✓ Returned data has more than 0 rows #> [2021-05-26 16:03:23] Caching resultset... #> [2021-05-26 16:03:23] 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 42855554 4287164 Has product comp 2014-12-01 2099-12-31 #> 2 3289315 3237496 Has method 2019-08-16 2099-12-31 #> 3 4061747 4062166 Subsumes 2011-07-31 2099-12-31 #> 4 4272604 40243060 Has drug-drug inter 2011-10-03 2099-12-31 #> 5 35909746 35918454 Schema to Variable 1970-01-01 2099-12-31 #> 6 43663305 19082103 RxNorm has dose fo… 2016-07-01 2099-12-31 #> 7 2834995 2834995 Maps to 2014-04-22 2099-12-31 #> 8 46159176 35626973 VMP has prescr stat 2018-07-31 2099-12-31 #> 9 41126988 41406624 Has quantified form 2017-07-18 2099-12-31 #> 10 45782395 19098694 SPL - RxNorm 1970-01-01 2099-12-31 #> # … with 90 more rows, and 1 more variable: invalid_reason <chr>#> [2021-05-26 16:03:23] Connected to 'athena' #> [2021-05-26 16:03:23] Overriding cache... Querying Athena... #> [2021-05-26 16:03:23] ✓ Open connection #> [2021-05-26 16:03:23] ✓ JDBC connection #> [2021-05-26 16:03:23] SQL: SELECT * FROM omop_vocabulary.concept_ancestor ORDER BY RANDOM() LIMIT 10 #> [2021-05-26 16:03:23] Querying... #> [2021-05-26 16:03:56] Querying...complete #> [2021-05-26 16:03:56] ✓ Returned data has more than 0 rows #> [2021-05-26 16:03:56] Caching resultset... #> [2021-05-26 16:03:56] 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 43700498 43700498 0 0 #> 2 45796668 41158074 2 2 #> 3 45669759 21093206 2 2 #> 4 44345075 35408653 2 3 #> 5 1163944 19029536 2 3 #> 6 21602751 43290309 5 6 #> 7 21605188 44098340 4 5 #> 8 41326126 41325950 1 1 #> 9 440371 36557117 3 3 #> 10 46302149 2049819 2 2