cleandataengineering.aiDrug-Safety Agent on Databricks, drawn simply
You are here: Databricks › Unity Catalog

Drug-Safety Agent on Databricks · Databricks · Unity Catalog

Unity Catalog: the data and the tools

Unity Catalog is Databricks' single place for who may use what. Here it holds the tables and the three functions. Putting the tools in it means permissions, lineage and audit come with them, not as extra code.

The data. Ten drugs by eight side effects makes 80 pairs of report counts, plus ten labels and a synonym list, all from public openFDA data fetched on one date. The label check needs synonyms because the medical term (for example hepatotoxicity) is not always the word a label uses (liver injury).

The proof it matches. A parity script runs the SQL function and the Python tool for every pair and compares them. It prints how many pairs differ.

The real code

Cut from the repository each time this site is built, so it always matches what runs. Each header names the file and the line it was cut from.

sql/001_tables.sql:1The tables
CREATE OR REPLACE TABLE workspace.drugsafety.pair_counts AS
SELECT lower(drug) AS drug, upper(event) AS event, CAST(a AS BIGINT) AS a, CAST(b AS BIGINT) AS b, CAST(c AS BIGINT) AS c, CAST(d AS BIGINT) AS d, source, fetched
FROM read_files('/Volumes/workspace/drugsafety/raw/pairs.jsonl', format => 'json')
sql/002_synonyms.sql:1Synonyms
CREATE OR REPLACE TABLE workspace.drugsafety.event_synonyms AS
SELECT * FROM VALUES
 ('RHABDOMYOLYSIS','rhabdomyolysis'),
 ('LACTIC ACIDOSIS','lactic acidosis'),
 ('PULMONARY TOXICITY','pulmonary toxicity'),('PULMONARY TOXICITY','pulmonary fibrosis'),('PULMONARY TOXICITY','interstitial pneumonitis'),('PULMONARY TOXICITY','hypersensitivity pneumonitis'),
 ('HAEMORRHAGE','haemorrhage'),('HAEMORRHAGE','hemorrhage'),('HAEMORRHAGE','bleeding'),
 ('GASTROINTESTINAL HAEMORRHAGE','gastrointestinal bleeding'),('GASTROINTESTINAL HAEMORRHAGE','gastrointestinal hemorrhage'),('GASTROINTESTINAL HAEMORRHAGE','gastrointestinal haemorrhage'),('GASTROINTESTINAL HAEMORRHAGE','gi bleeding'),
 ('ANGIOEDEMA','angioedema'),
 ('SUICIDAL IDEATION','suicidal ideation'),('SUICIDAL IDEATION','suicidal thoughts'),('SUICIDAL IDEATION','suicidality'),('SUICIDAL IDEATION','suicidal thinking'),
 ('HEPATOTOXICITY','hepatotoxicity'),('HEPATOTOXICITY','hepatic failure'),('HEPATOTOXICITY','liver injury'),('HEPATOTOXICITY','liver damage'),('HEPATOTOXICITY','hepatic toxicity')
 AS t(event, term)
scripts/deploy_uc.py:18Deploy: each SQL file through the statement API
def run(wh: str, statement: str) -> dict:
    body = json.dumps({"warehouse_id": wh, "wait_timeout": "50s", "statement": statement})
    out = subprocess.run(["databricks", "api", "post", "/api/2.0/sql/statements", "-p", PROFILE, "--json", body],
                         capture_output=True, text=True)
    try:
        return json.loads(out.stdout)
    except json.JSONDecodeError:
        raise SystemExit(f"no JSON from the API: {out.stdout[:200]} {out.stderr[:200]}")