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

1. 简介

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

此 Codelab 实现了一种模式，以使用 BigQueryUI 和 AI Platform Notebooks 访问和分析 BigQuery 中汇总的医疗保健数据。它展示了如何在符合 HIPPA 要求的 AI Platform Notebooks 中使用 Pandas、Matplotlib 等熟悉的工具对大型医疗保健数据集进行数据探索。“技巧”在 BigQuery 中执行聚合的第一部分，获取一个 Pandas 数据集，然后在本地处理较小的 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 Repositories 代码库。

您需要满足什么条件才能运行此 Codelab？

• 您需要访问某个 GCP 项目。
• 您需要被分配 GCP 项目的 Owner 角色。
• 您需要 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，然后点击 PIN 码。BigQuery 测试数据集“fhir_20k_patients_analytics”可供使用。

注意：如果您想创建自己的数据集，请参阅 FHIR to 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 Console 中的 AI Platform Notebooks 页面，打开一个笔记本实例。前往“AI Platform 笔记本”页面
2. 为您要打开的实例选择打开 JupyterLab。

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

创建笔记本

1. 在 JupyterLab 中，点击文件 ->新建 ->笔记本，然后选择内核“Python 3”或选择“Python 3”（在启动器窗口中的“笔记本”部分下创建 Untitled.ipynbnotebook）。

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 Repository

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 源代码库复制代码库克隆路径（见下方屏幕截图中的第 2 步）。

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

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

11. “my-ai-notebooks”该文件夹是在 Jupyterlab 中创建的

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

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 Repositories 中

8. 清理

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

删除 BigQuery 数据集

按照以下说明删除您在本教程中创建的 BigQuery 数据集。或者，转到 BigQuery 控制台，如果您使用了测试数据集 fhir_20k_patients_analytics，请取消固定项目 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 Repositories 代码库。

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

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