generated from opensafely/research-template
/
getdata.R
33 lines (28 loc) · 1.19 KB
/
getdata.R
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
##
## How to access OS SQL server via project pipelines,
##
# # # # # # # # # # # # # # # # # # # # # # # #
#### create connection to DB and query it ####
# # # # # # # # # # # # # # # # # # # # # # # #
## 1. read in environment variable
dbconn_string <- Sys.getenv(x = "OPENSAFELY_FULL_DATABASE_URL")
## 2. create the connection using the connection string
dbconn <- odbc::dbConnect(
drv = odbc::odbc(),
.connection_string = dbconn_string
)
## 3. now you can query the database with SQL
data <- odbc::dbGetQuery(dbconn, "SELECT
DrugName,
DerivedSNOMEDFromName,
DerivedVTM,
DerivedVTMName,
count(Patient_ID) as Num_Issues
FROM OpenCorona.dbo.HighCostDrugs
GROUP BY
DrugName,
DerivedSNOMEDFromName,
DerivedVTM,
DerivedVTMName")
## 4. write data to csv file
write.csv(data, file = here::here("output", "data.csv"))