Mimic-code: Difficulty Building Database Using PostgreSQL

[RJBeetel3]

I'm not terribly experienced in building databases. I'm running on mac os X 10.10.5
I attempted to download the files using make mimic-download but got the following error:

---

-- Downloading MIMIC-III from PhysioNet --

wget --user --ask-password -P -A csv.gz -m -p -E -k -K -np -nd "https://physionet.org/works/MIMICIIIClinicalDatabase/files/"
--2017-07-19 13:32:39-- http://csv.gz/
Resolving csv.gz (csv.gz)... failed: nodename nor servname provided, or not known.
wget: unable to resolve host address ‘csv.gz’
--2017-07-19 13:32:39-- https://physionet.org/works/MIMICIIIClinicalDatabase/files/
Resolving physionet.org (physionet.org)... 128.30.30.88
Connecting to physionet.org (physionet.org)|128.30.30.88|:443... connected.
ERROR: cannot verify physionet.org's certificate, issued by ‘CN=Let's Encrypt Authority X3,O=Let's Encrypt,C=US’:
Unable to locally verify the issuer's authority.
To connect to physionet.org insecurely, use `--no-check-certificate'.
Converted links in 0 files in 0 seconds.
make[1]: * [mimic-download] Error 4
make: * [mimic-download] Error 2

I then manually downloaded the data files and decompressed them. I then downloaded and installed PostgreSQL. I then tried to run:
make mimic datadir=/path to data/

from the command line and had issues with the mimic/postgres password.
I modified the Makefile script to change the user to postgres so that I could use the password I specified on install.
I re-ran:
make mimic datadir=/path to data/

and am now getting the following error:

psql "dbname=mimic user=postgres options=--search_path=mimiciii" -f postgres_create_tables.sql
psql: FATAL: database "mimic" does not exist
make[1]: * [mimic-build] Error 2
make: * [mimic-build] Error 2

I'd love some help with this.
Thanks,

[alistairewj]

First off, not sure about the wget error regarding certificates, perhaps @elfeto you know whether that error message matters? For your info, the goal of the wget is simply to download the CSVs from physionet -
script here: https://github.com/MIT-LCP/mimic-code/blob/9e05ab27b947b664f7ddbc5dcfe0c3e573c69fcc/buildmimic/postgres/Makefile#L156

I think this isn't your fault.. what I imagine is happening is the script is trying to create a user named "postgres", which fails (because postgres already exists), and so the rest of the script (which creates the database) fails. We probably need to update the makefile. In the meantime, you can fix this by running:

psql -c "CREATE DATABASE mimic;"
psql -d mimic -c "CREATE SCHEMA mimiciii;"

After you do that, just run make mimic-build datadir=/path to data/ ... that should kick off the data install. Let me know how you fare.

[RJBeetel3]

Alistair,
Thank for the assistance. I followed your instructions and ran mimic-build
as described above last night. I get the following which hasn't
changed/progressed over night:

psql "dbname=mimic user=mimic options=--search_path=mimiciii" -f
postgres_create_tables.sql

psql:postgres_create_tables.sql:31: NOTICE: table "admissions" does not
exist, skipping

DROP TABLE

CREATE TABLE

psql:postgres_create_tables.sql:61: NOTICE: table "callout" does not
exist, skipping

DROP TABLE

CREATE TABLE

psql:postgres_create_tables.sql:95: NOTICE: table "caregivers" does not
exist, skipping

DROP TABLE

CREATE TABLE

psql:postgres_create_tables.sql:110: NOTICE: table "chartevents" does not
exist, skipping

DROP TABLE

CREATE TABLE

CREATE TABLE

CREATE TABLE

CREATE TABLE

CREATE TABLE

CREATE TABLE

CREATE TABLE

CREATE TABLE

CREATE TABLE

CREATE TABLE

CREATE TABLE

CREATE TABLE

CREATE TABLE

CREATE TABLE

CREATE TABLE

CREATE FUNCTION

CREATE TRIGGER

psql:postgres_create_tables.sql:185: NOTICE: table "cptevents" does not
exist, skipping

DROP TABLE

CREATE TABLE

psql:postgres_create_tables.sql:207: NOTICE: table "datetimeevents" does
not exist, skipping

DROP TABLE

CREATE TABLE

psql:postgres_create_tables.sql:231: NOTICE: table "diagnoses_icd" does
not exist, skipping

DROP TABLE

CREATE TABLE

psql:postgres_create_tables.sql:246: NOTICE: table "drgcodes" does not
exist, skipping

DROP TABLE

CREATE TABLE

psql:postgres_create_tables.sql:264: NOTICE: table "d_cpt" does not exist,
skipping

DROP TABLE

CREATE TABLE

psql:postgres_create_tables.sql:284: NOTICE: table "d_icd_diagnoses" does
not exist, skipping

DROP TABLE

CREATE TABLE

psql:postgres_create_tables.sql:299: NOTICE: table "d_icd_procedures" does
not exist, skipping

DROP TABLE

CREATE TABLE

psql:postgres_create_tables.sql:314: NOTICE: table "d_items" does not
exist, skipping

DROP TABLE

CREATE TABLE

psql:postgres_create_tables.sql:335: NOTICE: table "d_labitems" does not
exist, skipping

DROP TABLE

CREATE TABLE

psql:postgres_create_tables.sql:352: NOTICE: table "icustays" does not
exist, skipping

DROP TABLE

CREATE TABLE

psql:postgres_create_tables.sql:375: NOTICE: table "inputevents_cv" does
not exist, skipping

DROP TABLE

CREATE TABLE

psql:postgres_create_tables.sql:407: NOTICE: table "inputevents_mv" does
not exist, skipping

DROP TABLE

CREATE TABLE

psql:postgres_create_tables.sql:448: NOTICE: table "labevents" does not
exist, skipping

DROP TABLE

CREATE TABLE

psql:postgres_create_tables.sql:467: NOTICE: table "microbiologyevents"
does not exist, skipping

DROP TABLE

CREATE TABLE

psql:postgres_create_tables.sql:493: NOTICE: table "noteevents" does not
exist, skipping

DROP TABLE

CREATE TABLE

psql:postgres_create_tables.sql:514: NOTICE: table "outputevents" does not
exist, skipping

DROP TABLE

CREATE TABLE

psql:postgres_create_tables.sql:537: NOTICE: table "patients" does not
exist, skipping

DROP TABLE

CREATE TABLE

psql:postgres_create_tables.sql:556: NOTICE: table "prescriptions" does
not exist, skipping

DROP TABLE

CREATE TABLE

psql:postgres_create_tables.sql:585: NOTICE: table "procedureevents_mv"
does not exist, skipping

DROP TABLE

CREATE TABLE

psql:postgres_create_tables.sql:620: NOTICE: table "procedures_icd" does
not exist, skipping

DROP TABLE

CREATE TABLE

psql:postgres_create_tables.sql:635: NOTICE: table "services" does not
exist, skipping

DROP TABLE

CREATE TABLE

psql:postgres_create_tables.sql:651: NOTICE: table "transfers" does not
exist, skipping

DROP TABLE

CREATE TABLE

---

-- Loading data --

---

psql "dbname=mimic user=mimic options=--search_path=mimiciii" -f
postgres_load_data.sql -v
mimic_data_dir=/Users/robertbeetel/anaconda/projects/mimic-code-master/mimic_data

COPY 58976

COPY 34499

COPY 7567

On Wed, Jul 19, 2017 at 5:23 PM, Alistair Johnson [e-mail]
wrote:

First off, not sure about the wget error regarding certificates, perhaps
@elfeto https://github.com/elfeto you know whether that error message
matters? For your info, the goal of the wget is simply to download the CSVs
from physionet -
script here: https://github.com/MIT-LCP/mimic-code/blob/
9e05ab27b947b664f7ddbc5dcfe0c3e573c69fcc/buildmimic/postgres/Makefile#L156

I think this isn't your fault.. what I imagine is happening is the script
is trying to create a user named "postgres", which fails (because postgres
already exists), and so the rest of the script (which creates the database)
fails. We probably need to update the makefile. In the meantime, you can
fix this by running:

psql -c "CREATE DATABASE mimic;"
psql -d mimic -c "CREATE SCHEMA mimiciii;"

After you do that, just run make mimic-build datadir=/path to data/ ...
that should kick off the data install. Let me know how you fare.

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
https://github.com/MIT-LCP/mimic-code/issues/259#issuecomment-316560526,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AToq-ReCbCcAN9mOYlTPSrWsr-cp3pjYks5sPp4DgaJpZM4OdQE1
.

[elfeto]

Hi, I checked the certificate and there is no problem with them. Since they are from "Let's Encrypt Authority" it may cause problem with some people. But there should be the disclaimer and there should be the "OPTION" for the command "--no-check-certificate" that will bypass the certificate check, and download the files either way.

[RJBeetel3]

where should that "--no-check-certificate" option be placed? in the
Makefile?
Thanks

On Thu, Jul 20, 2017 at 6:09 AM, elfeto [e-mail] wrote:

Hi, I checked the certificate and there is no problem with them. Since
they are from "Let's Encrypt Authority" it may cause problem with some
people. But there should be the disclaimer and there should be the "OPTION"
for the command "--no-check-certificate" that will bypass the certificate
check, and download the files either way.

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
https://github.com/MIT-LCP/mimic-code/issues/259#issuecomment-316697813,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AToq-dkahjWJQtLEUu9_L5abWfPbiCuiks5sP1GYgaJpZM4OdQE1
.

[elfeto]

You downloaded the files mannually so there is no need for this any more, but, it was on this command.

wget --user --ask-password -P -A csv.gz -m -p -E -k -K -np -nd "https://physionet.org/works/MIMICIIIClinicalDatabase/files/" --no-check-certificate

[tompollard]

@RJBeetel3 it looks like your build is progressing as expected. The tables have been created and the data has started to load. You are currently waiting for chartevents to load, which is the largest table and so takes a while.

[RJBeetel3]

Is a build time exceeding 24hrs normal?

On Thu, Jul 20, 2017 at 7:22 AM, Tom Pollard [e-mail]
wrote:

@RJBeetel3 https://github.com/rjbeetel3 it looks like your build is
progressing as expected. The tables have been created and the data has
started to load. You are currently waiting for chartevents to load, which
is the largest table and so takes a while.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/MIT-LCP/mimic-code/issues/259#issuecomment-316719199,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AToq-RNZBrFIS9Gt01zrrdOqmhCA6hxcks5sP2K3gaJpZM4OdQE1
.

[tompollard]

@RJBeetel3 We have had reports of long loading times (see: https://github.com/MIT-LCP/mimic-code/issues/215 and https://github.com/MIT-LCP/mimic-code/issues/181) but 24h seems excessively long. Are you sure that (1) your machine/disk isn't going into sleep mode during that time and (2) you have sufficient space on your disk (~80GB) ?

[RJBeetel3]

it looks like i didn't include a backslash after "/mimic_data". does this
mean i have to re-do the build process?

---

-- Checking for data --

---

Unable to find
/Users/robertbeetel/anaconda/projects/mimic-code-master/mimic_dataADMISSIONS.csv

• exiting before build.

make[1]: * [mimic-check] Error 1

make: * [mimic-check] Error 2

On Fri, Jul 21, 2017 at 7:38 AM, Tom Pollard [e-mail]
wrote:

@RJBeetel3 https://github.com/rjbeetel3 We have had reports of long
loading times (see: #215
https://github.com/MIT-LCP/mimic-code/issues/215 and #181
https://github.com/MIT-LCP/mimic-code/issues/181) but 24h seems
excessively long. Are you sure that (1) your machine/disk isn't going into
sleep mode during that time and (2) you have sufficient space on your disk
(~80GB) ?

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/MIT-LCP/mimic-code/issues/259#issuecomment-317018723,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AToq-ZqPGSUjAqwoRjEICsDDd_Fu_r5fks5sQLf0gaJpZM4OdQE1
.

[tompollard]

it looks like i didn't include a backslash after "/mimic_data". does this mean i have to re-do the build process?

No, the path that you provided seems fine. Your earlier post includes the following progress report:

...

COPY 58976

COPY 34499

COPY 7567

...which indicates that three of the tables have already loaded (the admissions table is 58976 rows, callout is 34,499 rows, and caregivers is 7,567 rows). The next table, chartevents, is significantly bigger (~330,712,483 rows).

[tompollard]

If you'd like to confirm this, try logging into the database and querying the first three tables (e.g. SELECT * FROM mimiciii.admissions LIMIT 10; will display the first 10 rows of the admissions table).

[RJBeetel3]

the query worked. it looks like I'm in business.
thanks very much

On Fri, Jul 21, 2017 at 10:42 AM, Tom Pollard [e-mail]
wrote:

If you'd like to confirm this, try logging into the database and querying
the first three tables (e.g. SELECT * FROM mimiciii.admissions LIMIT 10;
will display the first 10 rows of the admissions table).

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/MIT-LCP/mimic-code/issues/259#issuecomment-317066159,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AToq-U6zQp2XEbIG5Bw-CQ6dsAsACTfHks5sQOL-gaJpZM4OdQE1
.

[Tinayay]

Issue resolved. For more ppl information. I have run the command

1. $ sql -U qing -d postgres
2. Then change the user name and DB user to corresponding values. I guess set -d and DBNAME as mimiciii is better.

DBNAME := postgres
DBUSER := qing

1. Call the makeFile.

And, Yeah!!, done!!

I couldn't install the mimic data into postgres database as well in Mac. by use the makefile.

I did the following before calling makeFile

1. installed postgres by homebrew
2. downloaded and discomposed all mimic3 tables stored into "/Users/qing/Documents/mimic/data/"
3. clone the mimic-code from GitHub
4. start the postgres server with command $brew service start postgresql

Then, I called the makeFile, by $make mimic datadir="/Users/qing/Documents/mimic/data/". Please refer to the error I had. Did I miss any steps? like creating a DB/schema? If any, how should I do it?
Thanks in advance.

Qings-MacBook-Air:~ qing$ pg_ctl -D /usr/local/var/postgres status
pg_ctl: server is running (PID: 2676)
/usr/local/Cellar/postgresql/9.6.3/bin/postgres "-D" "/usr/local/var/postgres"
Qings-MacBook-Air:~ qing$ egrep 'listen|port' /usr/local/var/postgres/postgresql.conf

listen_addresses = 'localhost' # what IP address(es) to listen on;

port = 5432 # (change requires restart)

                # supported by the operating system:
                # supported by the operating system:
                #   %r = remote host and port

Qings-MacBook-Air:~ qing$ cd Documents/workspace/github/mimic-code/buildmimic/postgres/
Qings-MacBook-Air:postgres qing$ make mimic datadir="/Users/qing/Documents/mimic/data/"

---

-- Checking for data --

All data present!

---

-- Building MIMIC-III --

MIMIC_USER="postgres" MIMIC_DB="mimic" MIMIC_PASSWORD="" MIMIC_SCHEMA="mimiciii" ./create_mimic_user.sh
MIMIC_PASSWORD is set
MIMIC_DB is set to 'mimic'
MIMIC_USER is set to 'postgres'
ERROR: role "postgres" already exists

ERROR: schema "mimiciii" already exists

-- Building MIMIC-III --

---

-- Creating tables --

psql "dbname=mimic user=postgres options=--search_path=mimiciii" -f postgres_create_tables.sql
psql:postgres_create_tables.sql:31: NOTICE: table "admissions" does not exist, skipping
DROP TABLE
psql:postgres_create_tables.sql:55: ERROR: no schema has been selected to create in
LINE 1: CREATE TABLE ADMISSIONS
^
make: * [mimic-build] Error 3
Qings-MacBook-Air:postgres qing$

[alistairewj]

Hi Tina - glad you were able to solve the issue. I recently tidied up the README to be a bit clearer and also tidied up the makefile. Hopefully other users won't have issues in the future.