Achilles execution
#install libraries if necessary
#establish libraries
library(DatabaseConnector)
library(Achilles)
library(tidyverse)
## -- Attaching packages -------------------------------------------------------------------------------- tidyverse 1.3.0 --
## v ggplot2 3.3.0 v purrr 0.3.4
## v tibble 3.0.1 v dplyr 0.8.5
## v tidyr 1.0.3 v stringr 1.4.0
## v readr 1.3.1 v forcats 0.5.0
## -- Conflicts ----------------------------------------------------------------------------------- tidyverse_conflicts() --
## x dplyr::filter() masks stats::filter()
## x dplyr::lag() masks stats::lag()
library(knitr)
#steps below are only for the demo, skip those
library(fs);library(RSQLite);folder='m:/ohdsi/synn3c';dsname='synn3c'; dbfile<-fs::path(folder,dsname,ext = "sqlite")
#establish typical variables for connecting to database
connectionDetails <- DatabaseConnector::createConnectionDetails(dbms = "sqlite", server = dbfile)
cdmDatabaseSchema = 'main'
resultsDatabaseSchema= 'main' #typically it is a different schema than cdm
#achilles execution takes several hours. it is not quick.
#to avoid long wait - we will execute just some measures
#see the list of measures at the URL or from a package file
listOfAnalyses<-Achilles::getAnalysisDetails()
#it has many queries (=analyses)
nrow(listOfAnalyses)
## [1] 238
listOfAnalyses %>% head %>% kable
| 0 |
-1 |
0 |
|
Source name |
|
|
|
|
NA |
| 1 |
0 |
0 |
|
Number of persons |
|
|
|
|
NA |
| 2 |
0 |
0 |
|
Number of persons by gender |
gender_concept_id |
|
|
|
NA |
| 3 |
0 |
0 |
|
Number of persons by year of birth |
year_of_birth |
|
|
|
NA |
| 4 |
0 |
0 |
|
Number of persons by race |
race_concept_id |
|
|
|
NA |
| 5 |
0 |
0 |
|
Number of persons by ethnicity |
ethnicity_concept_id |
|
|
|
NA |
set=c(1,1815) #we will execute two analyses, add to this list from listOfAnalyses
#actual execution here (be patient and wait)
#MOST IMPORTANT SINGLE COMMAND
result<-Achilles::achilles(connectionDetails,cdmDatabaseSchema,resultsDatabaseSchema
,cdmVersion = '5'
,analysisIds = set #here is the restriction to just some analyses
,createTable = TRUE
,runCostAnalysis = FALSE, runHeel = FALSE,validateSchema = FALSE,verboseMode = TRUE
,numThreads = 1,createIndices = FALSE)
## Connecting using SQLite driver
## Connecting using SQLite driver
## Beginning single-threaded execution
## Connecting using SQLite driver
##
|
| | 0%
|
|=================================== | 50%
|
|======================================================================| 100%
## Executing SQL took 0.0279 secs
## Executing multiple queries. This could take a while
## Analysis 1 (Number of persons) -- START
##
|
| | 0%
|
|=================================== | 50%
|
|======================================================================| 100%
## Executing SQL took 0.017 secs
## [Main Analysis] [COMPLETE] 1 (0.026928 secs)
## Analysis 1815 (Distribution of numeric values, by measurement_concept_id and unit_concept_id) -- START
##
|
| | 0%
|
|======= | 10%
|
|============== | 20%
|
|===================== | 30%
|
|============================ | 40%
|
|=================================== | 50%
|
|========================================== | 60%
|
|================================================= | 70%
|
|======================================================== | 80%
|
|=============================================================== | 90%
|
|======================================================================| 100%
## Executing SQL took 1.58 mins
## [Main Analysis] [COMPLETE] 1815 (1.577078 mins)
## Merging scratch Achilles tables
##
|
| | 0%
|
|=================================== | 50%
|
|======================================================================| 100%
## Executing SQL took 0.0289 secs
##
|
| | 0%
|
|=================================== | 50%
|
|======================================================================| 100%
## Executing SQL took 0.0209 secs
## Done. Achilles results can now be found in schema main
#output is written to database
#to get results of an individual analysis, e.g, for analysis 1815 (=numeric result), we must ge
#fetch a single analysis
oneAnalysis=Achilles::fetchAchillesAnalysisResults(connectionDetails,resultsDatabaseSchema,analysisId = 1815)
## Connecting using SQLite driver
#the result object is a list and we need just one part of it
oneAnalysis$analysisResults %>% head %>% kable
| 1815 |
3000905.0 |
8848.0 |
NA |
NA |
NA |
337119 |
0.01 |
23.93 |
7.335622 |
4.182352 |
6.70 |
2.65 |
4.57 |
9.43 |
12.86 |
| 1815 |
3000963.0 |
8713.0 |
NA |
NA |
NA |
42681 |
6.30 |
15.80 |
10.987999 |
2.264524 |
11.00 |
7.90 |
9.10 |
12.80 |
14.00 |
| 1815 |
3003396.0 |
9557.0 |
NA |
NA |
NA |
17487 |
0.00 |
11.90 |
3.858581 |
2.926982 |
3.20 |
0.60 |
1.50 |
5.60 |
8.40 |
| 1815 |
3004249.0 |
8876.0 |
NA |
NA |
NA |
107904 |
79.00 |
163.00 |
120.131348 |
18.133816 |
120.00 |
97.00 |
107.00 |
133.00 |
145.00 |
| 1815 |
3004327.0 |
8848.0 |
NA |
NA |
NA |
89588 |
0.00 |
6.44 |
1.296361 |
0.878827 |
1.16 |
0.29 |
0.64 |
1.78 |
2.43 |
| 1815 |
3004501.0 |
8840.0 |
NA |
NA |
NA |
105653 |
17.00 |
247.00 |
118.139078 |
34.954474 |
108.00 |
85.00 |
94.00 |
133.00 |
168.00 |
#write to file
write_csv(oneAnalysis$analysisResults,path='a1815.csv')
#YOU ARE DONE!
#EXTRA OPTIONAL SECTION
#to export all analyses, we can do this command
conn=connect(connectionDetails)
## Connecting using SQLite driver
allAnalyses1<- DatabaseConnector::dbReadTable(conn,name = 'ACHILLES_RESULTS',schema=resultsDatabaseSchema)
allAnalyses2<- DatabaseConnector::dbReadTable(conn,name = 'ACHILLES_RESULTS_DIST',schema=resultsDatabaseSchema)
write_csv(allAnalyses1,path='ALL1.csv')
write_csv(allAnalyses2,path='ALL2DIST.csv')
#versions I used
devtools::session_info()
## - Session info ---------------------------------------------------------------
## setting value
## version R version 4.0.0 (2020-04-24)
## os Windows 10 x64
## system x86_64, mingw32
## ui RTerm
## language (EN)
## collate English_United States.1252
## ctype English_United States.1252
## tz America/New_York
## date 2020-06-19
##
## - Packages -------------------------------------------------------------------
## ! package * version date lib
## Achilles * 1.6.7 2020-06-03 [1]
## assertthat 0.2.1 2019-03-21 [1]
## backports 1.1.7 2020-05-13 [1]
## bit 1.1-15.2 2020-02-10 [1]
## bit64 0.9-7 2017-05-08 [1]
## blob 1.2.1 2020-01-20 [1]
## broom 0.5.6 2020-04-20 [1]
## callr 3.4.3 2020-03-28 [1]
## cellranger 1.1.0 2016-07-27 [1]
## cli 2.0.2 2020-02-28 [1]
## colorspace 1.4-1 2019-03-18 [1]
## crayon 1.3.4 2017-09-16 [1]
## DatabaseConnector * 2.4.4 2020-06-03 [1]
## DBI 1.1.0 2019-12-15 [1]
## dbplyr 1.4.3 2020-04-19 [1]
## desc 1.2.0 2018-05-01 [1]
## devtools 2.3.0 2020-04-10 [1]
## digest 0.6.25 2020-02-23 [1]
## dplyr * 0.8.5 2020-03-07 [1]
## ellipsis 0.3.0 2019-09-20 [1]
## evaluate 0.14 2019-05-28 [1]
## fansi 0.4.1 2020-01-08 [1]
## forcats * 0.5.0 2020-03-01 [1]
## fs * 1.4.1 2020-04-04 [1]
## generics 0.0.2 2018-11-29 [1]
## ggplot2 * 3.3.0 2020-03-05 [1]
## glue 1.4.1 2020-05-13 [1]
## gtable 0.3.0 2019-03-25 [1]
## haven 2.2.0 2019-11-08 [1]
## highr 0.8 2019-03-20 [1]
## hms 0.5.3 2020-01-08 [1]
## htmltools 0.4.0 2019-10-04 [1]
## httr 1.4.1 2019-08-05 [1]
## jsonlite 1.6.1 2020-02-02 [1]
## knitr * 1.28 2020-02-06 [1]
## lattice 0.20-41 2020-04-02 [2]
## lifecycle 0.2.0 2020-03-06 [1]
## lubridate 1.7.8 2020-04-06 [1]
## magrittr 1.5 2014-11-22 [1]
## memoise 1.1.0 2017-04-21 [1]
## modelr 0.1.7 2020-04-30 [1]
## munsell 0.5.0 2018-06-12 [1]
## nlme 3.1-147 2020-04-13 [2]
## ParallelLogger 1.2.0 2020-04-22 [1]
## pillar 1.4.4 2020-05-05 [1]
## pkgbuild 1.0.8 2020-05-07 [1]
## pkgconfig 2.0.3 2019-09-22 [1]
## pkgload 1.0.2 2018-10-29 [1]
## prettyunits 1.1.1 2020-01-24 [1]
## processx 3.4.2 2020-02-09 [1]
## ps 1.3.3 2020-05-08 [1]
## purrr * 0.3.4 2020-04-17 [1]
## R6 2.4.1 2019-11-12 [1]
## Rcpp 1.0.4.6 2020-04-09 [1]
## readr * 1.3.1 2018-12-21 [1]
## readxl 1.3.1 2019-03-13 [1]
## remotes 2.1.1 2020-02-15 [1]
## reprex 0.3.0 2019-05-16 [1]
## D rJava 0.9-12 2020-03-24 [1]
## rjson 0.2.20 2018-06-08 [1]
## rlang 0.4.6 2020-05-02 [1]
## rmarkdown 2.1 2020-01-20 [1]
## rprojroot 1.3-2 2018-01-03 [1]
## RSQLite * 2.2.0 2020-01-07 [1]
## rstudioapi 0.11 2020-02-07 [1]
## rvest 0.3.5 2019-11-08 [1]
## scales 1.1.1 2020-05-11 [1]
## sessioninfo 1.1.1 2018-11-05 [1]
## SqlRender 1.6.6 2020-05-19 [1]
## stringi 1.4.6 2020-02-17 [1]
## stringr * 1.4.0 2019-02-10 [1]
## testthat 2.3.2 2020-03-02 [1]
## tibble * 3.0.1 2020-04-20 [1]
## tidyr * 1.0.3 2020-05-07 [1]
## tidyselect 1.1.0 2020-05-11 [1]
## tidyverse * 1.3.0 2019-11-21 [1]
## usethis 1.6.1 2020-04-29 [1]
## vctrs 0.3.0 2020-05-11 [1]
## withr 2.2.0 2020-04-20 [1]
## xfun 0.13 2020-04-13 [1]
## xml2 1.3.2 2020-04-23 [1]
## yaml 2.2.1 2020-02-01 [1]
## source
## Github (ohdsi/Achilles@f33e5a7)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## Github (ohdsi/DatabaseConnector@a97dd32)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
## CRAN (R 4.0.0)
##
## [1] C:/Users/aahuserv/Documents/R/win-library/4.0
## [2] C:/Program Files/R/R-4.0.0/library
##
## D -- DLL MD5 mismatch, broken installation.