README.md 7.02 KB
Newer Older
cossins's avatar
cossins committed
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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
# Installation of WebAPI and Atlas 

We describe how we installed WebAPI and Atlas

## System Configuration:

### OS
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=18.04
DISTRIB_CODENAME=bionic
DISTRIB_DESCRIPTION="Ubuntu 18.04.5 LTS"

### Docker
Docker version 19.03.14

## Docker-compose
docker-compose version 1.25.4

### Java
openjdk 11.0.9.1 2020-11-04
OpenJDK Runtime Environment (build 11.0.9.1+1-Ubuntu-0ubuntu1.18.04)
OpenJDK 64-Bit Server VM (build 11.0.9.1+1-Ubuntu-0ubuntu1.18.04, mixed mode, sharing)

### Maven
Apache Maven 3.6.0
Maven home: /usr/share/maven
Java version: 1.8.0_275, vendor: Private Build, runtime: /usr/lib/jvm/java-8-openjdk-amd64/jre
Default locale: fr_FR, platform encoding: UTF-8
OS name: "linux", version: "5.4.0-58-generic", arch: "amd64", family: "unix"

### pgAdmin
Version
4.24
Copyright
Copyright (C) 2013 - 2020, The pgAdmin Development Team
Python Version
3.8.3 (default, May 20 2020, 20:46:20) [GCC 9.2.0]
Flask Version
1.0.2
Application Mode
Server

### postGres
postgres (PostgreSQL) 12.1 (Debian 12.1-1.pgdg100+1)

## WebAPI
We launched 3 services: PostgreSQL, pgadmin and tomcat:
```bash
docker-compose up -d
```

We followed the instructions to install WebAPI: https://github.com/OHDSI/WebAPI/wiki/WebAPI-Installation-Guide

### WebAPI Database
We used PostgreSQL for WebAPI database storage. pgAdmin was used to configure PostgreSQL.  
We created the different roles in the pgAdmin interface. 

#### pgAdmin interface
After login, create a new Server: 

* Name: ohdsi
* Host: webapi_postgres (container name, see docker-compose.yml)
* Port: 5432
* Username: postgres (by default, not an environment variable)
* Password: ${POSTGRES_PASSWORD}


### Build WebAPI

We cloned the github repository, moved to the latest release (v2.8.0)
```bash
git clone https://github.com/OHDSI/WebAPI.git
git checkout refs/tags/v2.8.0
```

cossins's avatar
cossins committed
76
77
78
79
80
We added 2 little fixes to WebAPI:
* https://github.com/OHDSI/WebAPI/issues/1730 (remove a ";" in a delete statement)
* https://github.com/OHDSI/WebAPI/issues/1731 added a Oracle driver to dependency
Our modifications can be found here: https://github.com/scossin/WebAPI

cossins's avatar
cossins committed
81
82
83
84
85
Then copy the WebAPIConfig to WebAPI folder:
```bash
cp ./WebAPIConfig ./WebAPI
```

86
87

Then enters the folder and compile the project with maven (don't forget to change the ohdsi_admin_user and ohdsi_app_user passwords):
cossins's avatar
cossins committed
88
89
90
91
```bash
mvn clean package -DskipTests -s WebAPIConfig/settings.xml -P webapi-postgresql
```

92
Then we moved it to the tomcat: 
cossins's avatar
cossins committed
93
```bash
94
95
docker cp target/WebAPI.war webapitomcat:/usr/local/tomcat/webapps
docker logs webapitomcat -f # check no error connecting 
cossins's avatar
cossins committed
96
97
98
99
100
101
102
103
104
105
106
107
108
109
```

Check installation by going to: http://localhost:8080/WebAPI/info


### CMD Configuration

#### WebAPI
The next step (https://github.com/OHDSI/WebAPI/wiki/CDM-Configuration) is to configure WebAPI to connect to:
* CDM: containing patient level data
* Vocab: OMOP vocabulary
* Results: cohort results
* Temp: temp table

cossins's avatar
cossins committed
110
111
112
In our configuration, CDM and Vocab are in the same schema (OMOP), Results are in the OMOP_COHORT schema and TEMP in the OMOP_TEMP schema. The OMOP_COHORT user can read the OMOP schema but it can't write in the TEMP schema. So only 2 schemas are used by WebAPI:  
* OMOP_COHORT to write Results and Temp tables
* OMOP (patient level data)
cossins's avatar
cossins committed
113
114
115
116
117
118
119
120

We go back to pgAdmin to enter data in the webapi.source and webapi.source_daimon tables.  
The following commands were executed:

```sql
TRUNCATE TABLE webapi.source CASCADE
TRUNCATE TABLE webapi.source_daimon CASCADE

cossins's avatar
cossins committed
121
-- SOURCE OMOP_COHORT
cossins's avatar
cossins committed
122
INSERT INTO webapi.source (source_id, source_name, source_key, source_connection, source_dialect, username, password) 
cossins's avatar
cossins committed
123
SELECT nextval('webapi.source_sequence'), 'OMOP_COHORT', 'OMOP_COHORT', 'jdbc:oracle:thin:@{IP}:{PORT}:{DATABASE}', 'oracle','OMOP_COHORT','{PASSWORD}';
cossins's avatar
cossins committed
124

cossins's avatar
cossins committed
125
-- CDM (OMOP schema)
cossins's avatar
cossins committed
126
INSERT INTO webapi.source_daimon (source_daimon_id, source_id, daimon_type, table_qualifier, priority) 
cossins's avatar
cossins committed
127
SELECT nextval('webapi.source_sequence'), source_id, 0, 'OMOP', 0
cossins's avatar
cossins committed
128
FROM webapi.source
cossins's avatar
cossins committed
129
WHERE source_key = 'OMOP_COHORT'
cossins's avatar
cossins committed
130
131
;

cossins's avatar
cossins committed
132
-- VOCAB (OMOP schema)
cossins's avatar
cossins committed
133
INSERT INTO webapi.source_daimon (source_daimon_id, source_id, daimon_type, table_qualifier, priority) 
cossins's avatar
cossins committed
134
SELECT nextval('webapi.source_sequence'), source_id, 1, 'OMOP', 1
cossins's avatar
cossins committed
135
FROM webapi.source
cossins's avatar
cossins committed
136
WHERE source_key = 'OMOP_COHORT'
cossins's avatar
cossins committed
137
138
;

cossins's avatar
cossins committed
139
-- RESULTS (OMOP_COHORT schema)
cossins's avatar
cossins committed
140
INSERT INTO webapi.source_daimon (source_daimon_id, source_id, daimon_type, table_qualifier, priority) 
cossins's avatar
cossins committed
141
SELECT nextval('webapi.source_sequence'), source_id, 2, 'OMOP_COHORT', 1
cossins's avatar
cossins committed
142
143
144
145
FROM webapi.source
WHERE source_key = 'OMOP_COHORT'
;

cossins's avatar
cossins committed
146
-- TEMP (OMOP_COHORT schema)
cossins's avatar
cossins committed
147
INSERT INTO webapi.source_daimon (source_daimon_id, source_id, daimon_type, table_qualifier, priority) 
cossins's avatar
cossins committed
148
SELECT nextval('webapi.source_sequence'), source_id, 5, 'OMOP_COHORT', 0
cossins's avatar
cossins committed
149
FROM webapi.source
cossins's avatar
cossins committed
150
WHERE source_key = 'OMOP_COHORT'
cossins's avatar
cossins committed
151
152
153
154
155
156
157
158
159
;

-- check:
SELECT * FROM webapi.source;
SELECT * FROM webapi.source_daimon;
```

Check sources: http://localhost:8080/WebAPI/source/sources

cossins's avatar
cossins committed
160
161
162
163
164
165
166
167
168
Make a backup of the database: 
* in the pgAdmin interface, right click on ohdsi "Backup Globals", save the file
* retrieve the backup file in the pgAdmin container
* move the file to the postgres container to restore a backup
* execute the following command: 
```bash
psql -U postgres -d ohdsi < OHDSI_BACKUP
```

cossins's avatar
cossins committed
169
170
171
172
173
174
175
#### Oracle

(This script is handy to delete all tables in a schema:)
```sql
SELECT 'DROP TABLE "' || TABLE_NAME || '" CASCADE CONSTRAINTS;' FROM user_tables;
```

cossins's avatar
cossins committed
176
By going to: http://localhost:8080/WebAPI/ddl/results?dialect=oracle&schema=OMOP_COHORT&vocabSchema=OMOP&tempSchema=OMOP_TEMP&initConceptHierarchy=true  
cossins's avatar
cossins committed
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
We get the Oracle scripts to execute to create new tables in our OMOP_COHORT and OMOP_TEMP schema

We tried to execute all the commands in Oracle SQL developer but we got an error because of the BEGIN-END syntax. We needed to replace all 
```sql
END;
```

by 

```sql
END; -- newline and /
/
```
Then, we needed to split the commands concerning the OMOP_COHORT schema and the OMOP_TEMP schema. 
Excecuting the script was very long (multiple hours).

cossins's avatar
cossins committed
193
194
195
196
197
198
## Atlas
We cloned the github repository, moved to the latest release (v2.8.0)
```bash
git clone https://github.com/OHDSI/Atlas.git
git checkout refs/tags/v2.8.0
```
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215

### Local configuration
Only nginx service is exposed with basic authentification required. WebAPI is access through Nginx with reverse proxy (default.conf):   
```
    location /WebAPI {
        proxy_set_header Accept-Encoding "";
        proxy_pass http://webapitomcat:8080;
    } 
```
Our config-local.js file looks like this:

```js
define([], function () {
	var configLocal = {};

	configLocal.api = {
		name: 'EDS CHU Bordeaux',
cossins's avatar
cossins committed
216
217
218
		url: 'http://{HOSTNAME}:{NGINX_PORT}/WebAPI/' 
		// in production: 'https://eds.chu-bordeaux.fr/WebAPI/'
		// in development: 'http://iam:5000/WebAPI'
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
	};
	return configLocal;
});
```
where  
* {HOSTNAME} is the name of the server where the docker-compose services are deployed 
* {NGINX_PORT} is the NGINX port set in the .env file

move this configuration file to the Atlas folder:
```bash
npm run build
tar -zcf atlas.tar.gz *
sudo mv atlas.tar.gz ../atlas/ # move it to the nginx volume 
cd ../atlas/
sudo tar -xf atlas.tar.gz  # deploy  
```