<jupyter_start><jupyter_text>Bulk Data Tutorial: EHR Export to SQL Exploration *To get started, copy this notebook to your Google Drive by selecting "Save a copy in Drive" from the "File" menu*<jupyter_code>#add a repository and pull down an updated version of sqlite
!add-apt-repository -y ppa:sergey-dryabzhinsky/packages
!apt update
!apt install sqlite3
import sqlite3
print(sqlite3.sqlite_version)

!pip3 install python-jose

import json
import jose.jwk
import jose.jwt
import jose.constants
import uuid
import datetime
import time
import requests
import pandas as pd

%load_ext google.colab.data_table<jupyter_output>0% [Working]
            
Ign:1 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  InRelease

0% [Connecting to archive.ubuntu.com] [Connecting to security.ubuntu.com] [Wait
                                                                               
Hit:2 https://cloud.r-project.org/bin/linux/ubuntu bionic-cran40/ InRelease

0% [Connecting to archive.ubuntu.com] [Connecting to security.ubuntu.com] [Conn
0% [2 InRelease gpgv 3,626 B] [Connecting to archive.ubuntu.com] [Connecting to
                                                                               
Ign:3 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  InRelease

0% [2 InRelease gpgv 3,626 B] [Connecting to archive.ubuntu.com] [Connecting to
                                                                               
Hit:4 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Release

0% [2 InRelease gpgv 3,626 B] [Connecting to ar[...]<jupyter_text>Bulk Data Server Configuration<jupyter_code># Link to editable server conig
# https://bulk-data.smarthealthit.org/?auth_type=jwks&jwks=eyJrZXlzIjpbeyJrdHkiOiJSU0EiLCJhbGciOiJSUzM4NCIsIm4iOiJvaHlucXNyLVFOOTdKeTdSeTNycGNQY2xFWjRxOG5wUFNSN1VzQjRpd0cxZTNDVVI4V3VHOGFpZXlrT0FTWDF1ZzYxa3lpUkM3UUFOQlIwZU5kVFUwX3o1X3F0OTZDM2FxN1VhNjZqb0FnS0ZSa3RZYjBEV1BrTTRxN2lpb25ydFQxMFlSUGpKZ3ZoNU1MVkFfNUg3UUVBUExWLWFkMUhtUmpfUjJyUWRpUmkteWxWT2hzSjRkczdLUVBCNVMzdjBqemZraXpMNVFhR2x6SGhHQXB3N2tRR3oxRUpuU09Pb3l1eWZCdDU2RnQ4Qm40d0d0M0diWUhqSXJXQ0dTcV9LWVVQRkN3ZkJTeEdsLVhYZlVMQTdYYVkxZnZLbDBiMU9wOXZYUUVSQzVBRzZBOF95T0NraDJsWFEtYS1xVUlBa3FVa2xzYkliQUxFOW5NenVScWNicFEiLCJlIjoiQVFBQiIsImtleV9vcHMiOlsidmVyaWZ5Il0sImV4dCI6dHJ1ZSwia2lkIjoiMzIwNDNjM2M3YTQ4NGRmODE5ZjlkYjI0ZjYxZDNkOTgifSx7Imt0eSI6IlJTQSIsImFsZyI6IlJTMzg0IiwibiI6Im9oeW5xc3ItUU45N0p5N1J5M3JwY1BjbEVaNHE4bnBQU1I3VXNCNGl3RzFlM0NVUjhXdUc4YWlleWtPQVNYMXVnNjFreWlSQzdRQU5CUjBlTmRUVTBfejVfcXQ5NkMzYXE3VWE2NmpvQWdLRlJrdFliMERXUGtNNHE3aWlvbnJ0VDEwWVJQakpndmg1TUxWQV81SDdRRUFQTFYtYWQxSG1Sal9SMnJRZGlSaS15bFZPaHNKNGRzN0tRUEI1UzN2MGp6Zmtpekw1UWFHbHpIaEdBcHc3a1FHejFFSm5TT09veXV5ZkJ0NTZGdDhCbjR3R3QzR2JZSGpJcldDR1NxX0tZVVBGQ3dmQlN4R2wtWFhmVUxBN1hhWTFmdktsMGIxT3A5dlhRRVJDNUFHNkE4X3lPQ2toMmxYUS1hLXFVSUFrcVVrbHNiSWJBTEU5bk16dVJxY2JwUSIsImUiOiJBUUFCIiwiZCI6IkU2bWdHSklvaHB2VXB5c0JuVW1hT081eEc1THdxSnAzTldvODUtT3YyV0VxcVU4QnZ5T0lxWlVITlljOGJ5MThreEliOEh5M2RXR1ZNcktXY0hjUkhXbEl0VF85dElTalNrSmNNb3M1SU0xSGR3QTZpTHYzZ0hQTTB0OUJNcTN3VExrQThaU3ZkR2hJNHNTSlRyWlpJRERrNWw3N01FX0p1Mm1RUFpPdEpXeWVnYTFEeHNrLWVHWVF0U3FiN3I5WHYtZWJLVnF2M1RTX2hGdmQteHdSRGZVTGVieFJucDdtVG5iWk9DTXRiRDhhWV9nLS0wdS1ySjRvcy1ncEI0ZVVjUVExZ1NJaXR0c1NicHZkeGppeHV2X29DWm12US0tQlE2aXZ1OGNWVVRGcFFUTnUxeTcyWG1wdDZKbERDTTBRaXRQLWxGb1NRemRxSjRpWWdDeGJ3USIsInAiOiIwQUVpejJfOWptOUs4RHZSaHN2TExTekwxMU53emtOeXFPdXZJSGpLZklVRS1hc3p3b1o4d3dvNi1abEs2RWpKRWFDYTZWRkl5Y2NPNzctQmdJOW9qcHNWMWlKNVJRb3VEX0ktNXdkaE9mUnZ1bndndDNBSDRXRlpBRlZ6Z1Z1SDJuYl9uNFlRS0NObXhrR3JTNlVCVGtzUkJTU0huLWV4ZjdXa0tOV1kxblUiLCJxIjoieDRTanR6aWR4c2FySTRGTFd1M2JKU0JMQ2YtaEpvVVNaZjRsQkhiWlR5b3hUZlVtSEIwOGhJTmV1a3dxc09iVnF0bEYtamFpR3RyTHJVRzB2U2FaVTJURkJoWFJEd05xZkJ6NTFQUk53RVQtV0hBNl9UMlU3TjRhWXBPa1BzVkoxemlyM1lOUFFYendRVmFPN2NuTUpybEhVSjl3QmtlY1I2V1FxaHRVYW5FIiwiZHAiOiJjd2JOeTAweE9OMU80YjhTeGFwWmQyZjRhZUZZMXBmeUJud2ZZcU9weG1hQk05aDdUUzIyUE4yaGMzcFVORlJiN1FXVjVVVHdMYldKQ3dncWczX0s0R0hmRjRZRTdpc05BZ0NDekp3R0psaUltT1lmNlpGb3FqaDlVWFB2NWk5SjE0UkxKRnF0QUtWakptd1dZRVNsampodzl4a1diMVZma1RKUHo4MGZNaWsiLCJkcSI6ImlLU2F5MTBIRVpfMmpYWjdlV2IxYzFQT2tLWjJZNzgzZm9xMEozNk9EZDNEcnJSNnlUNXN1QVNRMTk1YVRwV0pPRjFkdzZPdkdkMUhLUDJiSHdCVWVmekZ3d2ttQ0xZNk00NVV2RGdsRjgwb3RNRUlaV19Ma3YzNTdCRlpwM1BPd291WmlUZnBDOXpaWHFZOWRUV0RqX0hNbWJiWUxqVFZIMnlReXktMGZNRSIsInFpIjoiZy1VQ3p4V3ppT1dnbjZxdGFLT0lQc29lNTlPNUZFV1VTTjJoYWg0YjNhUnJxc1d6MzFuclJsVklZaDZQTTNWYThVZDJMaHY3OWs0ODM3dXRsaU1RdlIxcVV4eGY3MWQ0SnloV3BSbi1sZ000UDc2a0tzazZlakkzdWpDTEhqY3RlWEtYcnFyeGw3VE11ckV1Sm5xbVV1ODQ3WDJROE5vVmVNLTlkaXZCMVgwIiwia2V5X29wcyI6WyJzaWduIl0sImV4dCI6dHJ1ZSwia2lkIjoiMzIwNDNjM2M3YTQ4NGRmODE5ZjlkYjI0ZjYxZDNkOTgifV19&stu=4

# private key that corresponds to the public key that was registered with the server in JWKS format
signing_key = {
  "kty": "RSA",
  "alg": "RS384",
  "n": "<KEY>",
  "e": "AQAB",
  "d": "<KEY>",
  "p": "<KEY>",
  "q": "<KEY>",
  "dp": "<KEY>",
  "dq": "<KEY>",
  "qi": "<KEY>",
  "key_ops": ["sign"],
  "ext": True,
  "kid": "32043c3c7a484df819f9db24f61d3d98"
}

# client_id and endpoints are provided by the server as part of app registration
client_id = "<KEY>"
token_endpoint = "https://bulk-data.smarthealthit.org/auth/token"
fhir_endpoint = "https://bulk-data.smarthealthit.org/eyJlcnIiOiIiLCJwYWdlIjoxMDAwMCwiZHVyIjoxMCwidGx0IjoxNSwibSI6MSwic3R1Ijo0LCJkZWwiOjB9/fhir"<jupyter_output><empty_output><jupyter_text>Authorize Bulk Data Connection (SMART Backend Services OAuth Profile)<jupyter_code>jwt_claims = {
  "iss": client_id,
  "sub": client_id,
  "aud": token_endpoint,
  "exp": datetime.datetime.utcnow() + datetime.timedelta(minutes=5),
  "jti": uuid.uuid4().hex
}

client_assertion = jose.jwt.encode(
    jwt_claims,
    signing_key,
    algorithm='RS384',
    headers={"kid": signing_key["kid"]}
  )

token_request = {
    "scope": "system/*.read",
    "grant_type": "client_credentials",
    "client_assertion_type": "urn:ietf:params:oauth:client-assertion-type:jwt-bearer",
    "client_assertion": client_assertion
}
token_response = requests.post(token_endpoint, data=token_request)
print(token_response.text)<jupyter_output>{"token_type":"bearer","expires_in":300,"access_token":"<KEY>"}<jupyter_text>Bulk Data Kickoff Request<jupyter_code>kickoff_request_headers = {
    'Prefer': 'respond-async', 
    'Accept': 'application/fhir+json',
    'Authorization': 'Bearer ' + token_response.json()["access_token"]
}
kickoff_request_params = {'_type': 'Observation,ExplanationOfBenefit'}
kickoff_response = requests.get(fhir_endpoint + '/Patient/$export', params=kickoff_request_params, headers=kickoff_request_headers)
print(kickoff_response.headers)<jupyter_output>{'Server': 'Cowboy', 'Connection': 'keep-alive', 'X-Powered-By': 'Express', 'Content-Location': 'https://bulk-data.smarthealthit.org/fhir/bulkstatus/6b0fe0e8423acce8cc5bd33363ae46ce', 'Content-Type': 'application/json; charset=utf-8', 'Content-Length': '644', 'Etag': 'W/"284-mOH2m6HqwkRzOWxzn7ZqMjGXBss"', 'Date': 'Wed, 13 Jan 2021 21:06:58 GMT', 'Via': '1.1 vegur'}<jupyter_text>Bulk Data Status Request<jupyter_code># poll the status endpoint (returned in the Content-Location header of the $export response)
status_request_headers = {
    'Authorization': 'Bearer ' + token_response.json()["access_token"]
}
status_response = requests.get(kickoff_response.headers['Content-Location'], headers=status_request_headers)
print('Response code: ' + str(status_response.status_code))

while(status_response.status_code != 200):
    time.sleep(20)
    status_response = requests.get(kickoff_response.headers['Content-Location'], headers=status_request_headers)
    print('Response code: ' + str(status_response.status_code))

print('Response body: ' + json.dumps(json.loads(str(status_response.text)), indent=2))<jupyter_output>Response code: 202
Response code: 200
Response body: {
  "transactionTime": "2021-01-13T21:06:58.127Z",
  "request": "https://bulk-data.smarthealthit.org/eyJlcnIiOiIiLCJwYWdlIjoxMDAwMCwiZHVyIjoxMCwidGx0IjoxNSwibSI6MSwic3R1Ijo0LCJkZWwiOjB9/fhir/Patient/$export?_type=Observation%2CExplanationOfBenefit",
  "requiresAccessToken": true,
  "output": [
    {
      "type": "ExplanationOfBenefit",
      "count": 3697,
      "url": "https://bulk-data.smarthealthit.org/eyJpZCI6IjZiMGZlMGU4NDIzYWNjZThjYzViZDMzMzYzYWU0NmNlIiwib2Zmc2V0IjowLCJsaW1pdCI6MzY5N30/fhir/bulkfiles/1.ExplanationOfBenefit.ndjson"
    },
    {
      "type": "Observation",
      "count": 10000,
      "url": "https://bulk-data.smarthealthit.org/eyJpZCI6IjZiMGZlMGU4NDIzYWNjZThjYzViZDMzMzYzYWU0NmNlIiwib2Zmc2V0IjowLCJsaW1pdCI6MTAwMDB9/fhir/bulkfiles/1.Observation.ndjson"
    },
    {
      "type": "Observation",
      "count": 6315,
      "url": "https://bulk-data.smarthealthit.org/<KEY>[...]<jupyter_text>Bulk Data File Download<jupyter_code>from collections import defaultdict 

file_request_headers = {
    'Authorization': 'Bearer ' + token_response.json()["access_token"]
}

resources = defaultdict(list)
for bulk_file in status_response.json()["output"]:
  file_response = requests.get(bulk_file["url"], headers=file_request_headers)
  resource_type =  bulk_file["type"];
  data = file_response.text.strip().split("\n")
  resources[resource_type] = resources[resource_type] + data
  print(data[0])<jupyter_output>{"resourceType":"ExplanationOfBenefit","id":"af093440-e2a5-4a1e-93a5-70b212699b02","contained":[{"resourceType":"ServiceRequest","id":"referral","status":"completed","intent":"order","subject":{"reference":"Patient/6c5d9ca9-54d7-42f5-bfae-a7c19cd217f2"},"requester":{"reference":"Practitioner/87a5ab63-4cbe-3f5c-9287-5388c7187ef1"},"performer":[{"reference":"Practitioner/87a5ab63-4cbe-3f5c-9287-5388c7187ef1"}]},{"resourceType":"Coverage","id":"coverage","status":"active","type":{"text":"Medicaid"},"beneficiary":{"reference":"urn:uuid:6c5d9ca9-54d7-42f5-bfae-a7c19cd217f2"},"payor":[{"display":"Medicaid"}]}],"identifier":[{"system":"https://bluebutton.cms.gov/resources/variables/clm_id","value":"001ae14e-3afb-4b8f-b23e-3ad65fec5e4e"},{"system":"https://bluebutton.cms.gov/resources/identifier/claim-group","value":"99999999999"}],"status":"active","type":{"coding":[{"system":"http://terminology.hl7.org/CodeSystem/claim-type","code":"institutional"}]},"use":"claim","patient":{"reference":"Pat[...]<jupyter_text>SQL Data Loading<jupyter_code>conn = sqlite3.connect(":memory:")
c = conn.cursor()

for resource_type in resources.keys():
  c.execute(f'DROP TABLE IF EXISTS {resource_type}')
  c.execute(f'CREATE TABLE {resource_type} (json)')
  for resource in resources[resource_type]:
    c.execute(f'INSERT INTO {resource_type} values (?)', [resource])
  conn.commit()

c.execute('SELECT count(*) FROM Observation')
print(c.fetchone()[0])<jupyter_output>16315<jupyter_text>Clinical / Care Management Example Query Find all patients whose most recent A1c result is elevated (defined as > 6 to return data from the sample data set :)<jupyter_code># if you get a syntax error here, the notebook may have restarted and
# reverted to a previous version of sqlite. Choose "Restart and run all"
# from the "Runtime" menu above to force it to rebuild.

query = """
  SELECT * FROM (
    SELECT 
      json_extract(Observation.json, '$.subject.reference') patient,
      json_extract(Observation.json, '$.effectiveDateTime') effectiveDateTime,
      json_extract(Observation.json, '$.valueQuantity.value') value,    
      row_number() OVER (
        PARTITION BY 	
          json_extract(Observation.json, '$.subject.reference')
        ORDER BY
          json_extract(Observation.json, '$.effectiveDateTime') DESC
      ) as rank
    FROM
      Observation
      JOIN json_each(json_extract(Observation.json, '$.code.coding')) coding
	WHERE
	  json_extract(coding.value, '$.system') = 'http://loinc.org'
	  AND json_extract(coding.value, '$.code') = '4548-4'
  )
  WHERE rank = 1 AND value > 6
"""


c.execute(query)
data = c.fetchall()
pd.DataFrame.from_records(data, columns=["patient", "effectiveDateTime", "value", "rank"])<jupyter_output><empty_output><jupyter_text>Urgent Care Utilization Example Query Find patients with more than 2 urgent care facility visits in 2019<jupyter_code># Adjusted to comment out the 2 visit requirement since none of the sample
# patients have more than one visit :).

query = """
  SELECT 
    json_extract(ExplanationOfBenefit.json, '$.patient.reference') patient,
    strftime('%Y', json_extract(ExplanationOfBenefit.json, '$.billablePeriod.start')) year,
    COUNT(DISTINCT json_extract(ExplanationOfBenefit.json, '$.billablePeriod.start')) visitCount
  FROM
    ExplanationOfBenefit
    JOIN json_each(json_extract(ExplanationOfBenefit.json, '$.item')) item
    JOIN json_each(json_extract(item.value, '$.locationCodeableConcept.coding')) itemCoding
   WHERE
    json_extract(itemCoding.value, '$.code') = '20'
    AND year = '2019'
  GROUP BY 1, 2
  -- HAVING visitCount > 2
  ORDER BY visitCount DESC
"""

c.execute(query)
data = c.fetchall()
pd.DataFrame.from_records(data, columns=["patient", "year", "count"])<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11197059048786091, 'fraction_numerical': 0.05644481887110362, 'mean_word_length': 6.890030211480362, 'pattern_counts': {'":': 75, '<': 41, '<?xml version=': 0, '>': 44, 'https://': 14, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 1, 'word_list_counts': {'cursed_substrings.json': 13, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '326731', 'license': 'Apache-2.0', 'n_tokens_mistral': 6163, 'n_tokens_neox': 5675, 'n_words': 619, 'path': 'BulkDataExampleSql.ipynb', 'repo_name': 'gingkolane/FHIR', 'size': '17859'}