7  mimiciv数据安装

7.1 下载数据

wget -r -N -c -np --user hulinhui --ask-password https://physionet.org/files/mimiciv/2.2/

7.1.1 下载时长

开始下载时间：2024-02-01 16:19:02 CST
FINISHED 2024-02-02 02:35:36
Total wall clock time: 10h 18m 55s
Downloaded: 38 files, 7.2G in 10h 18m 12s (202 KB/s)

7.2 装载数据

7.2.1 安装github mimic code

export GH_TOKEN=your_personal_access_token

gh repo clone MIT-LCP/mimic-code

注意：有时需要连接多几次才能连接上。

7.2.2 查看postgres的版本

psql --version

7.2.3 新建postgres数据库角色

如果在尝试使用 “psql” 命令时出现 “role ‘hulihuihong’ does not exist” 的错误消息，这表示在 PostgreSQL 中不存在名为 “hulihuihong” 的角色。

您可以按照以下步骤创建 “hulihuihong” 用户/角色，并将其添加到 PostgreSQL 中：

  1. 以管理员身份登录到终端或命令行。

  2. 使用以下命令切换到 PostgreSQL 的管理员用户 “postgres”：

    sudo -u postgres psql

  3. 在 psql 命令行提示符下，使用以下命令创建名为 “hulihuihong” 的角色：

    CREATE ROLE hulihuihong WITH LOGIN PASSWORD 'your_password';

    将 “your_password” 替换为您为 “hulihuihong” 用户设置的密码。请确保密码是强壮且难以猜测的。

  4. 授予 “hulihuihong” 角色所需的权限。例如，运行以下命令以将 “hulihuihong” 角色授予所有数据库的超级用户权限：

    ALTER ROLE hulihuihong WITH SUPERUSER;

    根据您的需求，您可以使用其他 ALTER ROLE 命令来授予更具体的权限。

  5. 在完成创建角色和分配权限后，您可以使用以下命令退出 psql：

    \q

现在，您应该能够使用 “hulihuihong” 用户连接到 PostgreSQL，并通过 “psql” 命令进行数据库操作。

7.2.4 新建数据库

sudo -u postgres psql
CREATE DATABASE hulihuihong;
GRANT ALL PRIVILEGES ON DATABASE hulihuihong TO hulihuihong;  # 授予hulihuihong角色该数据库的所有权限

7.2.5 安装postgres数据库

  • 导航至mimic-code/mimic-iv/buildmimic/postgres/README.md，查看装载方法。
  • 参阅渲染后版本

7.2.5.1 成功实践

  • 导入数据
psql -f mimic-code/mimic-iv/buildmimic/postgres/create.sql
psql -v ON_ERROR_STOP=1 -v mimic_data_dir=physionet.org/files/mimiciv/2.2 -f mimic-code/mimic-iv/buildmimic/postgres/load_gz.sql

开始导入时间：2024-02-02 18:14:45 CST 完成导入时间：2024-02-02 19:58:35 CST

  • 查看模式（schema）
psql -U hulihuihong -d hulihuihong
\dn
      Name       |    Owner    
-----------------+-------------
 mimiciv_derived | hulihuihong
 mimiciv_hosp    | hulihuihong
 mimiciv_icu     | hulihuihong
 public          | postgres
  • 查看模型里的数据表
\dt mimiciv_hosp.*
\dt mimiciv_icu.*
List of relations
    Schema    |        Name        | Type  |    Owner    
--------------+--------------------+-------+-------------
 mimiciv_hosp | admissions         | table | hulihuihong
 mimiciv_hosp | d_hcpcs            | table | hulihuihong
 mimiciv_hosp | d_icd_diagnoses    | table | hulihuihong
 mimiciv_hosp | d_icd_procedures   | table | hulihuihong
 mimiciv_hosp | d_labitems         | table | hulihuihong
 mimiciv_hosp | diagnoses_icd      | table | hulihuihong
 mimiciv_hosp | drgcodes           | table | hulihuihong
 mimiciv_hosp | emar               | table | hulihuihong
 mimiciv_hosp | emar_detail        | table | hulihuihong
 mimiciv_hosp | hcpcsevents        | table | hulihuihong
 mimiciv_hosp | labevents          | table | hulihuihong
 mimiciv_hosp | microbiologyevents | table | hulihuihong
 mimiciv_hosp | omr                | table | hulihuihong
 mimiciv_hosp | patients           | table | hulihuihong
 mimiciv_hosp | pharmacy           | table | hulihuihong
 mimiciv_hosp | poe                | table | hulihuihong
 mimiciv_hosp | poe_detail         | table | hulihuihong
 mimiciv_hosp | prescriptions      | table | hulihuihong
 mimiciv_hosp | procedures_icd     | table | hulihuihong
 mimiciv_hosp | provider           | table | hulihuihong
 mimiciv_hosp | services           | table | hulihuihong
 mimiciv_hosp | transfers          | table | hulihuihong
(22 rows)
List of relations
  Schema    |       Name       | Type  |    Owner    
-------------+------------------+-------+-------------
mimiciv_icu | caregiver        | table | hulihuihong
mimiciv_icu | chartevents      | table | hulihuihong
mimiciv_icu | d_items          | table | hulihuihong
mimiciv_icu | datetimeevents   | table | hulihuihong
mimiciv_icu | icustays         | table | hulihuihong
mimiciv_icu | ingredientevents | table | hulihuihong
mimiciv_icu | inputevents      | table | hulihuihong
mimiciv_icu | outputevents     | table | hulihuihong
mimiciv_icu | procedureevents  | table | hulihuihong
(9 rows)
  • 查看表占据的空间和行数
SELECT schemaname AS "Schema Name",
       relname AS "Table Name",
       n_live_tup AS "Number of Rows",
       pg_size_pretty(pg_total_relation_size(schemaname || '.' || relname)) AS "Total Size"
FROM pg_stat_user_tables
WHERE schemaname IN ('mimiciv_hosp', 'mimiciv_icu')
ORDER BY pg_total_relation_size(schemaname || '.' || relname) DESC;
Schema Name  |     Table Name     | Number of Rows | Total Size 
--------------+--------------------+----------------+------------
 mimiciv_icu  | chartevents        |      313589287 | 27 GB
 mimiciv_hosp | labevents          |      118120951 | 14 GB
 mimiciv_hosp | emar_detail        |       54733418 | 5816 MB
 mimiciv_hosp | poe                |       39356623 | 4408 MB
 mimiciv_hosp | emar               |       26857454 | 3589 MB
 mimiciv_hosp | pharmacy           |       13586007 | 2668 MB
 mimiciv_hosp | prescriptions      |       15415316 | 2616 MB
 mimiciv_icu  | inputevents        |        8980811 | 2057 MB
 mimiciv_icu  | ingredientevents   |       11627651 | 1621 MB
 mimiciv_hosp | microbiologyevents |        3231782 | 635 MB
 mimiciv_icu  | datetimeevents     |        7113267 | 589 MB
 mimiciv_hosp | omr                |        6439195 | 390 MB
 mimiciv_icu  | outputevents       |        4234943 | 341 MB
 mimiciv_hosp | poe_detail         |        3879585 | 324 MB
 mimiciv_hosp | diagnoses_icd      |        4756325 | 237 MB
 mimiciv_hosp | transfers          |        1890972 | 142 MB
 mimiciv_icu  | procedureevents    |         696092 | 126 MB
 mimiciv_hosp | admissions         |         431231 | 67 MB
 mimiciv_hosp | drgcodes           |         604377 | 52 MB
 mimiciv_hosp | procedures_icd     |         669186 | 35 MB
 mimiciv_hosp | services           |         468029 | 24 MB
 mimiciv_hosp | patients           |         299712 | 15 MB
 mimiciv_hosp | hcpcsevents        |         150771 | 12 MB
 mimiciv_hosp | d_icd_diagnoses    |         109775 | 12 MB
 mimiciv_icu  | icustays           |          73181 | 10064 kB
 mimiciv_hosp | d_icd_procedures   |          85257 | 9688 kB
 mimiciv_hosp | d_hcpcs            |          89200 | 5968 kB
 mimiciv_hosp | provider           |          40508 | 1464 kB
 mimiciv_icu  | caregiver          |          15468 | 576 kB
 mimiciv_icu  | d_items            |           4014 | 528 kB
 mimiciv_hosp | d_labitems         |           1622 | 136 kB