-
Notifications
You must be signed in to change notification settings - Fork 1.5k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Difficulty Building Database Using PostgreSQL #259
Comments
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 - mimic-code/buildmimic/postgres/Makefile Line 156 in 9e05ab2
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 |
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 ***@***.***> 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/
9e05ab2/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
<#259 (comment)>,
or mute the thread
<https://github.com/notifications/unsubscribe-auth/AToq-ReCbCcAN9mOYlTPSrWsr-cp3pjYks5sPp4DgaJpZM4OdQE1>
.
|
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. |
where should that "--no-check-certificate" option be placed? in the
Makefile?
Thanks
…On Thu, Jul 20, 2017 at 6:09 AM, elfeto ***@***.***> 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
<#259 (comment)>,
or mute the thread
<https://github.com/notifications/unsubscribe-auth/AToq-dkahjWJQtLEUu9_L5abWfPbiCuiks5sP1GYgaJpZM4OdQE1>
.
|
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 |
@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. |
Is a build time exceeding 24hrs normal?
…On Thu, Jul 20, 2017 at 7:22 AM, Tom Pollard ***@***.***> 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
<#259 (comment)>,
or mute the thread
<https://github.com/notifications/unsubscribe-auth/AToq-RNZBrFIS9Gt01zrrdOqmhCA6hxcks5sP2K3gaJpZM4OdQE1>
.
|
@RJBeetel3 We have had reports of long loading times (see: #215 and #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) ? |
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 ***@***.***> wrote:
@RJBeetel3 <https://github.com/rjbeetel3> We have had reports of long
loading times (see: #215
<#215> and #181
<#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
<#259 (comment)>,
or mute the thread
<https://github.com/notifications/unsubscribe-auth/AToq-ZqPGSUjAqwoRjEICsDDd_Fu_r5fks5sQLf0gaJpZM4OdQE1>
.
|
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 |
If you'd like to confirm this, try logging into the database and querying the first three tables (e.g. |
the query worked. it looks like I'm in business.
thanks very much
…On Fri, Jul 21, 2017 at 10:42 AM, Tom Pollard ***@***.***> 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
<#259 (comment)>,
or mute the thread
<https://github.com/notifications/unsubscribe-auth/AToq-U6zQp2XEbIG5Bw-CQ6dsAsACTfHks5sQOL-gaJpZM4OdQE1>
.
|
Issue resolved. For more ppl information. I have run the command DBNAME := postgres 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
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? Qings-MacBook-Air:~ qing$ pg_ctl -D /usr/local/var/postgres status -- Checking for data --All data present! -- Building MIMIC-III --MIMIC_USER="postgres" MIMIC_DB="mimic" MIMIC_PASSWORD="" MIMIC_SCHEMA="mimiciii" ./create_mimic_user.sh
|
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. |
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,
The text was updated successfully, but these errors were encountered: