使用 BigQuery 和 AI Platform Notebooks 分析临床数据

1. 简介

上次更新时间：2022 年 9 月 22 日

此 Codelab 实现了一种模式，用于使用 BigQueryUI 和 AI Platform Notebooks 访问和分析在 BigQuery 中汇总的医疗保健数据。本实验将演示如何在符合健康保险流通与责任法案 (HIPAA) 标准的 AI Platform Notebooks 中使用 Pandas、Matplotlib 等熟悉的工具探索大型医疗保健数据集。诀窍就是在 BigQuery 中执行汇总的第一部分，返回一个 Pandas 数据集，然后在本地处理这个较小的数据集。AI Platform Notebooks 提供代管式 Jupyter 体验，因此您无需自行运行笔记本服务器。AI Platform Notebooks 与 BigQuery 和 Cloud Storage 等其他 GCP 服务紧密集成，可让您在 Google Cloud Platform 上快速轻松地开始数据分析和机器学习之旅。

在此 Codelab 中，您将学习如何完成以下操作：

• 使用 BigQuery 界面开发和测试 SQL 查询。
• 在 GCP 中创建并启动 AI Platform Notebooks 实例。
• 从笔记本执行 SQL 查询，并将查询结果存储在 Pandas DataFrame 中。
• 使用 Matplotlib 创建图表和图形。
• 提交笔记本并将其推送到 GCP 中的 Cloud Source Repository。

运行此 Codelab 需要哪些条件？

• 您需要有 GCP 项目的访问权限。
• 您需要被分配 GCP 项目的所有者角色。
• 您需要在 BigQuery 中拥有医疗保健数据集。

如果您没有 GCP 项目，请按照这些步骤创建一个新的 GCP 项目。

注意：此 Codelab 使用符合 HIPAA 标准的 AI Platform Notebooks。

2. 项目设置

在此 Codelab 中，我们将使用 BigQuery 中的现有数据集 (hcls-testing-data.fhir_20k_patients_analytics)。此数据集已预先填充了合成医疗保健数据。

获取对合成数据集的访问权限

1. 使用您用于登录 Cloud 控制台的电子邮件地址，向 [e-mail] 发送电子邮件，申请加入。
2. 您会收到一封电子邮件，其中包含有关如何确认操作的说明。
3. 使用相应选项回复电子邮件，以加入群组。请勿点击 按钮。
4. 收到确认电子邮件后，您可以继续执行 Codelab 中的下一步。

置顶项目

1. 在 GCP Console 中，选择您的项目，然后前往 BigQuery。
2. 点击 +添加数据下拉菜单，然后依次选择“将项目置顶”>“输入项目名称”。

3. 输入项目名称“hcls-testing-data”，然后点击固定。BigQuery 测试数据集“fhir_20k_patients_analytics”可供使用。

注意：如果您想创建自己的数据集，请参阅 FHIR 到 BigQuery 的 Codelab。

3. 使用 BigQuery 界面开发查询

BigQuery 界面设置

1. 从左上角的（“汉堡”图标）GCP 菜单中选择 BigQuery，前往 BigQuery 控制台。
2. 在 BigQuery 控制台中，依次点击更多 → 查询设置，并确保未选中“旧版 SQL”菜单（我们将使用标准 SQL）。

构建查询

在查询编辑器窗口中，输入以下查询，然后点击“运行”以执行该查询。然后，在“查询结果”窗口中查看结果。

注意：本部分中提供的代码块使用 hcls-testing-data.fhir_20k_patients_analytics BQ 数据集。如果您决定使用自己的数据集，请替换它。

查询患者

#standardSQL - Query Patients
SELECT
  id AS patient_id,
  name[safe_offset(0)].given AS given_name,
  name[safe_offset(0)].family AS family,
  telecom[safe_offset(0)].value AS phone,
  birthDate AS birth_date,
  deceased.dateTime AS deceased_datetime,
  Gender AS fhir_gender_code,
  Address[safe_offset(0)].line AS address1_line_1,
  Address[safe_offset(0)].city AS address1_city,
  Address[safe_offset(0)].state AS address1_state,
  Address[safe_offset(0)].postalCode AS address1_postalCode,
  Address[safe_offset(0)].country AS address1_country
FROM 
  `hcls-testing-data.fhir_20k_patients_analytics.Patient` AS Patient 
LIMIT 10

“查询编辑器”中的查询和结果：

注意：本部分中的查询结果可能会因行数/列数过多而受到限制，以提高可读性。实际结果可能包含更多数据。

咨询从业者

#standardSQL - Query Practitioners
SELECT 
  id AS practitioner_id, 
  name[safe_offset(0)].given AS given_name,
  name[safe_offset(0)].family AS family_name, 
  gender 
FROM 
  `hcls-testing-data.fhir_20k_patients_analytics.Practitioner` 
LIMIT 10

查询结果：

查询组织

更改组织 ID 以与您的数据集匹配。

#standardSQL - Query Organization
SELECT
  id AS org_id,
  type[safe_offset(0)].text AS org_type,
  name AS org_name,
  address[safe_offset(0)].line AS org_addr,
  address[safe_offset(0)].city AS org_addr_city,
  address[safe_offset(0)].state AS org_addr_state,
  address[safe_offset(0)].postalCode AS org_addr_postalCode,
  address[safe_offset(0)].country AS org_addr_country
FROM 
  `hcls-testing-data.fhir_20k_patients_analytics.Organization` AS Organization
WHERE 
  id = "b81688f5-bd0e-3c99-963f-860d3e90ab5d"

查询结果：

按患者查询就诊记录

#standardSQL - Query Encounters by Patient
SELECT
  id AS encounter_id,
  period.start AS encounter_start,
  period.end AS encounter_end,
  status AS encounter_status,
  class.code AS encounter_type,
  subject.patientId as patient_id,
  participant[safe_OFFSET(0)].individual.practitionerId as parctitioner_id,
  serviceProvider.organizationId as encounter_location_id,
  type[safe_OFFSET(0)].text AS encounter_reason
FROM
  `hcls-testing-data.fhir_20k_patients_analytics.Encounter` AS Encounter
WHERE
  subject.patientId = "900820eb-4166-4981-ae2d-b183a064ac18"
ORDER BY
  encounter_end

查询结果：

按就诊类型获取就诊的平均时长

#standardSQL - Get Average length of Encounters by Encounter type 
SELECT
  class.code encounter_class,
  ROUND(AVG(TIMESTAMP_DIFF(TIMESTAMP(period.end),    TIMESTAMP(period.start), HOUR)),1) as avg_minutes
FROM
  `hcls-testing-data.fhir_20k_patients_analytics.Encounter` AS Encounter
WHERE
  period.end >= period.start
GROUP BY
  1
ORDER BY
  2 DESC

查询结果：

获取所有 A1C 率 >= 6.5 的患者

# Query Patients who have A1C rate >= 6.5
SELECT 
  id AS observation_id,
  subject.patientId AS patient_id,
  context.encounterId AS encounter_id,
  value.quantity.value,
  value.quantity.unit,
  code.coding[safe_offset(0)].code,
  code.coding[safe_offset(0)].display AS description
FROM
  `hcls-testing-data.fhir_20k_patients_analytics.Observation` 
WHERE 
  code.text like '%A1c/Hemoglobin%' AND 
  value.quantity.value >= 6.5 AND 
  status = 'final'

查询结果：

4. 创建 AI Platform Notebooks 实例

按照此链接中的说明创建新的 AI Platform Notebooks (JupyterLab) 实例。

请务必启用 Compute Engine API。

您可以选择“使用默认选项创建新的笔记本”或“创建新的笔记本并指定您的选项”。

5. 构建数据分析笔记本

打开 AI Platform Notebooks 实例

在本部分中，我们将从头开始编写新的 Jupyter 笔记本。

1. 在 Google Cloud Platform 控制台中前往 AI Platform Notebooks 页面，打开笔记本实例。前往 AI PLATFORM NOTEBOOKS 页面
2. 为您要打开的实例选择打开 JupyterLab。

3. AI Platform Notebooks 会将您定向到您的笔记本实例网址。

创建笔记本

1. 在 JupyterLab 中，依次前往 File -> New -> Notebook，然后在弹出式窗口中选择内核“Python 3”，或在启动器窗口的“Notebook”部分下选择“Python 3”，以创建 Untitled.ipynb 笔记本。

2. 右键点击 Untitled.ipynb，然后将笔记本重命名为“fhir_data_from_bigquery.ipynb”。双击以打开该笔记本，构建查询，然后保存笔记本。
3. 您可以下载笔记本，方法是右键点击 *.ipynb 文件，然后从菜单中选择“下载”。

4. 您还可以点击“向上箭头”按钮上传现有笔记本。

构建并执行笔记本中的每个代码块

复制并逐个执行本部分中提供的每个代码块。如需执行代码，请点击“运行”（三角形）。

注意：本部分中提供的代码块使用 hcls-testing-data.fhir_20k_patients_analytics BQ 数据集。如果您决定使用自己的数据集，请替换它。

获取就诊时长（以小时为单位）

from google.cloud import bigquery

client = bigquery.Client()

lengthofstay="""
SELECT
    class.code as encounter_class,
    period.start as start_timestamp,
    period.end as end_timestamp, 
    TIMESTAMP_DIFF(TIMESTAMP(period.end), TIMESTAMP(period.start), HOUR) 
        as length_of_stay_in_hours
FROM 
    `hcls-testing-data.fhir_20k_patients_analytics.Encounter`
WHERE
    period.end >= period.start
ORDER BY
    4 DESC
LIMIT 10
"""
df = client.query(lengthofstay).to_dataframe()
df.head()

代码和执行输出：

注意：为了提高可读性，本部分中的执行输出可能会限制行数/列数。实际结果可能包含更多数据。

获取观测结果 - 胆固醇值

observation="""
SELECT
  cc.code loinc_code,
  cc.display loinc_name,
  approx_quantiles(round(o.value.quantity.value,1),4) as quantiles,
  count(*) as num_obs
FROM
  `hcls-testing-data.fhir_20k_patients_analytics.Observation` o, o.code.coding cc
WHERE
  cc.system like '%loinc%' and lower(cc.display) like '%cholesterol%'
GROUP BY 1,2
ORDER BY 4 desc
"""
df2 = client.query(observation).to_dataframe()
df2.head()

执行输出：

获取近似相遇分位数

encounters="""
SELECT
  encounter_class,
  APPROX_QUANTILES(num_encounters, 4) num_encounters_quantiles
FROM (
  SELECT
    class.code encounter_class,
    subject.reference patient_id,
    COUNT(DISTINCT id) AS num_encounters
  FROM
    `hcls-testing-data.fhir_20k_patients_analytics.Encounter`
  GROUP BY
    1,2
  )
GROUP BY 1
ORDER BY 1
"""
df3 = client.query(encounters).to_dataframe()
df3.head()

执行输出：

获取每次遭遇的平均时长（以分钟为单位）

avgstay="""
SELECT
  class.code encounter_class,
  ROUND(AVG(TIMESTAMP_DIFF(TIMESTAMP(period.end), TIMESTAMP(period.start), MINUTE)),1) as avg_minutes
FROM
  `hcls-testing-data.fhir_20k_patients_analytics.Encounter`
WHERE
  period.end >= period.start
GROUP BY
  1
ORDER BY
  2 DESC
  """
df4 = client.query(avgstay).to_dataframe()
df4.head()

执行输出：

获取每位患者的就诊次数

patientencounters="""
SELECT
  id AS encounter_id,
  period.start AS encounter_start,
  period.end AS encounter_end,
  status AS encounter_status,
  class.code AS encounter_type,
  subject.patientId as patient_id,
  participant[safe_OFFSET(0)].individual.practitionerId as parctitioner_id,
  serviceProvider.organizationId as encounter_location_id,
  type[safe_OFFSET(0)].text AS encounter_reason
FROM
  `hcls-testing-data.fhir_20k_patients_analytics.Encounter` AS Encounter
WHERE
  subject.patientId = "900820eb-4166-4981-ae2d-b183a064ac18"
ORDER BY
  encounter_end
"""

df5 = client.query(patientencounters).to_dataframe()
df5.head()

执行输出：

获取组织

orgs="""
SELECT
  id AS org_id,
  type[safe_offset(0)].text AS org_type,
  name AS org_name,
  address[safe_offset(0)].line AS org_addr,
  address[safe_offset(0)].city AS org_addr_city,
  address[safe_offset(0)].state AS org_addr_state,
  address[safe_offset(0)].postalCode AS org_addr_postalCode,
  address[safe_offset(0)].country AS org_addr_country
FROM 
  `hcls-testing-data.fhir_20k_patients_analytics.Organization` AS Organization
WHERE 
  id = "b81688f5-bd0e-3c99-963f-860d3e90ab5d"
"""

df6 = client.query(orgs).to_dataframe()
df6.head()

执行结果：

获取患者

patients="""
SELECT
  id AS patient_id,
  name[safe_offset(0)].given AS given_name,
  name[safe_offset(0)].family AS family,
  telecom[safe_offset(0)].value AS phone,
  birthDate AS birth_date,
  deceased.dateTime AS deceased_datetime,
  Gender AS fhir_gender_code,
  Address[safe_offset(0)].line AS address1_line_1,
  Address[safe_offset(0)].city AS address1_city,
  Address[safe_offset(0)].state AS address1_state,
  Address[safe_offset(0)].postalCode AS address1_postalCode,
  Address[safe_offset(0)].country AS address1_country
FROM 
  `hcls-testing-data.fhir_20k_patients_analytics.Patient` AS Patient 
LIMIT 10
"""

df7 = client.query(patients).to_dataframe()
df7.head()

执行结果：

6. 在 AI Platform Notebooks 中创建图表和图形

执行笔记本“fhir_data_from_bigquery.ipynb”中的代码单元，以绘制条形图。

例如，获取以分钟为单位的平均相遇时长。

df4.plot(kind='bar', x='encounter_class', y='avg_minutes');

代码和执行结果：

7. 将笔记本提交到 Cloud Source Repositories

1. 在 GCP Console 中，前往 Source Repositories。如果您是首次使用，请依次点击“开始使用”和“创建代码库”。

2. 后续，请依次前往 GCP -> Cloud Source Repositories，然后点击“+ 添加代码库”以创建新代码库。

3. 选择“创建新代码库”，然后点击“继续”。
4. 提供代码库名称和项目名称，然后点击“创建”。

注意：在此处以及与代码库相关的所有命令中，我们将使用名称“my-ai-notebooks”。如果您要更改它，请注意，您必须在后续本部分中的所有与代码库相关的命令中更改它。

5. 选择“将您的代码库克隆到本地 Git 代码库”，然后选择“手动生成的凭据”。
6. 按照第 1 步“生成并存储 Git 凭据”中的说明操作（请参阅下文）。复制屏幕上显示的脚本。

7. 在 Jupyter 中启动终端会话。

8. 将“配置 Git”窗口中的所有命令粘贴到 Jupyter 终端中。
9. 从 GCP Cloud Source Repositories 复制代码库克隆路径（请参阅下方屏幕截图中的第 2 步）。

10. 将此命令粘贴到 JupiterLab 终端中。命令将如下所示：

git clone https://source.developers.google.com/p/<your -project-name>/r/my-ai-notebooks

11. 系统会在 Jupyterlab 中创建“my-ai-notebooks”文件夹。

12. 将笔记本 (fhir_data_from_bigquery.ipynb) 移至“my-ai-notebooks”文件夹。
13. 在 Jupyter 终端中，将目录更改为“cd my-ai-notebooks”。
14. 使用 Jupyter 终端暂存更改。或者，您也可以使用 Jupyter 界面（右键点击“Untracked”区域中的文件，选择“Track”，然后文件会移至“Tracked”区域，反之亦然）。更改区域包含修改后的文件）。

git remote add my-ai-notebooks https://source.developers.google.com/p/<your -project-name>/r/my-ai-notebooks

15. 使用 Jupyter 终端或 Jupyter 界面提交更改（输入消息，然后点击“勾选”按钮）。

git commit -m "message goes here"

16. 使用 Jupyter 终端或 Jupyter 界面（点击“推送已提交的更改”图标 ）将更改推送到远程代码库。

git push --all

17. 在 GCP 控制台中，前往 Source Repositories。点击 my-ai-notebooks。请注意，“fhir_data_from_bigquery.ipynb”现已保存在 GCP Source Repository 中。

8. 清理

为避免系统因本 Codelab 中使用的资源向您的 Google Cloud Platform 账号收取费用，您可以在完成本教程后清理在 GCP 上创建的资源，以免这些资源占用您的配额，并让您在未来支付费用。以下部分介绍如何删除或关闭这些资源。

删除 BigQuery 数据集

请按照以下说明删除您在本教程中创建的 BigQuery 数据集。或者，如果您使用了测试数据集 fhir_20k_patients_analytics，请前往 BigQuery 控制台，取消固定项目 hcls-testing-data。

关闭 AI Platform Notebooks 实例

按照此链接中的说明关停笔记本实例 | AI Platform Notebooks 关停 AI Platform Notebooks 实例。

删除项目

若要避免产生费用，最简单的方法是删除您为本教程创建的项目。

如需删除项目，请执行以下操作：

警告：删除项目会产生以下影响：

• 项目中的所有内容都会被删除。如果您在本教程使用现有项目，则删除该项目后，还将删除您已在该项目中完成的其他所有工作。
• 自定义项目 ID 会丢失。创建此项目时，您可能创建了要在将来使用的自定义项目 ID。如需保留使用该项目 ID 的网址（例如 appspot.com 网址），请删除项目内的所选资源，而不是删除整个项目。

如果您打算浏览多个教程和快速入门，重复使用项目可以帮助您避免超出项目配额上限。

1. 在 GCP Console 中，前往项目页面。前往“项目”页面
2. 在项目列表中，选择要删除的项目，然后点击删除。
3. 在对话框中输入项目 ID，然后点击关停以删除项目。

9. 恭喜

恭喜，您已成功完成本 Codelab，学会了如何使用 BigQuery 和 AI Platform Notebooks 访问、查询和分析 FHIR 格式的医疗保健数据。

您在 GCP 中访问了公共 BigQuery 数据集。

您使用 BigQuery 界面开发并测试了 SQL 查询。

您已创建并启动 AI Platform Notebooks 实例。

您在 JupyterLab 中执行了 SQL 查询，并将查询结果存储在 Pandas DataFrame 中。

您使用 Matplotlib 创建了图表和图形。

您已将笔记本提交并推送到 GCP 中的 Cloud Source Repository。

现在，您已经了解了在 Google Cloud 平台上使用 BigQuery 和 AI Platform Notebooks 开启医疗保健数据分析之旅所需的主要步骤。

©Google, Inc. 或其关联公司。保留所有权利。请勿分发。