使用 BigQuery 和 AI 平台筆記本分析臨床資料

1. 簡介

上次更新時間：2022 年 9 月 22 日

這個程式碼研究室會實作一個模式，讓您透過 BigQueryUI 和 AI 平台 Notebooks，存取及分析 BigQuery 中匯總的醫療照護資料。它說明在符合 HIPPA 法規的 AI 平台筆記本中，使用 Pandas、Mttillib 等熟悉的工具探索大型醫療照護資料集的資料。「錯覺」是在 BigQuery 中進行匯總的第一部分，取得 Pandas 資料集，然後在本機處理較小的 Pandas 資料集。AI 平台筆記本提供代管 Jupyter 服務，因此您不必自行執行筆記本伺服器。AI 平台筆記本與其他 GCP 服務 (例如 BigQuery 和 Cloud Storage) 緊密整合，可讓您在 Google Cloud Platform 中輕鬆快速地展開資料分析和機器學習旅程。

在這個程式碼研究室中，您將瞭解以下內容：

• 使用 BigQuery UI 開發及測試 SQL 查詢。
• 在 GCP 中建立並啟動 AI Platform Notebooks 執行個體。
• 從筆記本執行 SQL 查詢，並將查詢結果儲存在 Pandas DataFrame。
• 使用 MatDrawlib 建立圖表。
• 修訂筆記本並推送至 GCP 中的 Cloud Source Repository。

執行本程式碼研究室的必要條件為何？

• 您需要 GCP 專案的存取權。
• 您需要具備 GCP 專案的擁有者角色。
• 您需要 BigQuery 中的醫療照護資料集。

如果您沒有 GCP 專案，請按照這些步驟建立新的 GCP 專案。

注意：本程式碼研究室使用的是符合《健康保險流通與責任法案》規範的 AI 平台筆記本。

2. 專案設定

在這個程式碼研究室中，我們將使用 BigQuery 中的現有資料集 (hcls-testing-data.fhir_20k_patients_analytics)。這個資料集會預先填入合成的醫療照護資料。

取得綜合資料集的存取權

1. 透過您用來登入 Cloud 控制台的電子郵件地址，傳送電子郵件至 [e-mail] 要求加入。
2. 您會收到一封電子郵件，說明如何確認這項操作。
3. 使用回覆電子郵件加入群組。請勿點選「」按鈕。
4. 收到確認電子郵件後，您可以繼續進行本程式碼研究室的下一個步驟。

固定專案

1. 在 GCP 控制台中選取專案，然後前往 BigQuery。
2. 按一下「+新增資料」下拉式選單，然後選取「固定專案」>「輸入專案名稱」，直接在 Google Cloud 控制台實際操作。

3. 輸入專案名稱「hcls-testing-data」，然後按一下「PIN 碼」。BigQuery 測試資料集「fhir_20k_patients_analytics」可供使用。

注意：如要自行建立資料集，請參閱從 FHIR 到 BigQuery 的程式碼研究室。

3. 使用 BigQuery UI 開發查詢

BigQuery UI 設定

1. 從左上角 (「漢堡」) GCP 選單中選取 BigQuery，前往 BigQuery 控制台。
2. 在 BigQuery 控制台中，依序點選「更多」→「查詢設定」，並確認「舊版 SQL」選單未勾選 (我們將使用標準 SQL)。

建立查詢

在「Query Editor」(查詢編輯器) 視窗中輸入下列查詢，然後按一下 [Run] (執行) 執行。然後，在「Query results」(查詢結果) 視窗中查看結果。

注意：本節提供的程式碼區塊使用 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 平台筆記本頁面
2. 針對您要開啟的執行個體選取 [Open JupyterLab] (開啟 JupyterLab)。

3. AI Platform Notebooks 會將您導向筆記本執行個體的網址，

建立筆記本

1. 在 JupyterLab 中，前往「File」(檔案) ->「新功能 ->「Notebooks」(筆記本)，然後選取核心 (Python 3)或選取「Python 3」。

2. 在「Untitled.ipynb」上按一下滑鼠右鍵，然後將筆記本重新命名為「fhir_data_from_bigquery.ipynb」。按兩下即可開啟、建立查詢及儲存筆記本。
3. 如要下載筆記本，請在 *.ipynb 檔案上按一下滑鼠右鍵，然後從選單中選取「下載」。

4. 你也可以按一下「向上箭頭」，上傳現有筆記本按鈕。

建構及執行筆記本中的每個程式碼區塊

逐一複製並執行本節提供的程式碼區塊。如要執行程式碼，請按一下「Run」(三角形)。

注意：本節提供的程式碼區塊使用 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 平台筆記本中建立圖表

執行筆記本「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 ->，然後按一下「+ 新增存放區」來建立新的存放區。

3. 選取「Create a new Repository」，然後按一下「Continue」。
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 UI (在「未追蹤」區域中的檔案按一下滑鼠右鍵，選取 [追蹤]，然後將檔案移至「追蹤」區域，反之亦然)。變更區域含有修改過的檔案)。

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

15. 使用 Jupyter 終端機或 Jupyter UI 提交變更 (輸入訊息，然後按一下 [已勾選] 按鈕)。

git commit -m "message goes here"

16. 使用 Jupyter 終端機或 Jupyter UI (按一下「推送修訂變更」圖示 )，將變更推送至遠端存放區。

git push --all

17. 在 GCP 控制台中，前往「Source Repositories」。按一下「my-ai-notebooks」。請注意，「fhir_data_from_bigquery.ipynb」都會儲存在 GCP 原始碼存放區中

8. 清除

完成教學課程後，如要避免系統向您的 Google Cloud Platform 帳戶收取這個程式碼研究室中所用資源的費用，您可以清除在 GCP 上建立的資源，這樣資源就不會佔用配額，您日後也無須為其付費。下列各節將說明如何刪除或停用這些資源。

刪除 BigQuery 資料集

按照操作說明刪除您在這個教學課程中建立的 BigQuery 資料集。或者，如果您使用測試資料集 fhir_20k_patients_analytics，請前往 BigQuery 控制台登入 UnPIN 專案 hcls-testing-data。

關閉 AI Platform Notebooks 執行個體

按照這個連結中的操作說明關閉筆記本執行個體 |AI Platform Notebooks)，關閉 AI 平台筆記本執行個體。

刪除專案

如要避免付費，最簡單的方法就是刪除您針對教學課程建立的專案。

如要刪除專案，請進行以下操作：

注意：刪除專案會出現以下結果：

• 專案中的所有內容都會遭到刪除。 若使用現有專案進行本教學課程，當您刪除專案時，會一併刪除您在該專案中完成的任何其他工作。
• 自訂專案 ID 會遺失。在您建立這個專案時，可能採用日後要使用的自訂專案 ID。如要保留使用該專案 ID 的網址 (例如 appspot.com 網址)，請刪除在專案中選取的資源，而不是刪除整個專案。

如果打算進行多個教學課程及快速入門導覽課程，重複使用專案有助於避免超出專案配額限制。

1. 在 GCP Console 中，前往「Projects」(專案) 頁面。前往「PROJECTS」(專案) 頁面
2. 在專案清單中選取您要刪除的專案，並按一下 [Delete] (刪除)。
3. 在對話方塊中輸入專案 ID，然後按一下「Shut down」(關閉) 即可刪除專案。

9. 恭喜

恭喜！您已成功完成程式碼研究室，透過 BigQuery 和 AI 平台筆記本存取、查詢及分析 FHIR 格式的醫療照護資料。

您已在 GCP 中存取公開 BigQuery 資料集。

您已使用 BigQuery UI 開發並測試 SQL 查詢。

您已建立並啟動 AI Platform Notebooks 執行個體。

您在 JupyterLab 中執行 SQL 查詢，並將查詢結果儲存在 Pandas DataFrame。

您可以使用 Matchartlib 建立圖表。

您已提交筆記本，並推送至 GCP 中的 Cloud Source Repository。

您現已瞭解在 Google Cloud Platform 上使用 BigQuery 和 AI 平台筆記本，踏上醫療照護資料分析之旅的重要步驟。

©Google, Inc. 或其關係企業。版權所有。請勿散佈。