data-catalog-v2 / app.py
okiel21's picture
Update app.py
f5ea5a9 verified
Raw History Blame Contribute Delete
22.3 kB
# app.py β€” Unified Data Catalog (Streamlit, Hugging Face friendly)
# Features:
# - Upload CSV/XLSX (header-only mode supported)
# - Auto glossary: type, null%, PII flags, examples
# - Role-based PII masking (Marketing/CS vs Data Steward)
# - Manual key tagging + scored relationship inference (name/type/overlap)
# - Exports: CSV/JSON/Markdown for glossary; key_hints.json; relationships.csv
# - Optional DuckDB persistence
# - LLM Enrichment (OpenAI): embeddings -> clustering -> cluster naming -> interactive graph (pyvis)
import os
import json
import re
from typing import Dict, List
import pandas as pd
import numpy as np
import streamlit as st
# ---------------- Optional deps (graceful fallbacks) ----------------
HAS_DUCKDB = False
try:
import duckdb
HAS_DUCKDB = True
except Exception:
HAS_DUCKDB = False
HAS_RAPIDFUZZ = False
try:
from rapidfuzz import fuzz
HAS_RAPIDFUZZ = True
except Exception:
HAS_RAPIDFUZZ = False
HAS_SKLEARN = False
try:
from sklearn.decomposition import PCA
from sklearn.cluster import KMeans
HAS_SKLEARN = True
except Exception:
HAS_SKLEARN = False
HAS_PYVIS = False
try:
from pyvis.network import Network
import streamlit.components.v1 as components
import networkx as nx
HAS_PYVIS = True
except Exception:
HAS_PYVIS = False
# OpenAI (new SDK)
HAS_OPENAI = False
try:
from openai import OpenAI
HAS_OPENAI = True
except Exception:
HAS_OPENAI = False
# ---------------- App config ----------------
st.set_page_config(page_title="Unified Data Catalog β€” MVP", layout="wide")
EMAIL_RE = r'[^@\s]+@[^@\s]+\.[^@\s]+'
PHONE_RE_BR = r'^\+?\d{10,14}$'
CPF_RE = r'^\d{11}$'
KNOWN_ID_HINTS = {
"user_id","customer_id","client_id","id","contact_id",
"order_id","order","email","cpf","phone","document"
}
# ====================== Core helpers ======================
def infer_simple_type(s: pd.Series) -> str:
dt = str(s.dtype)
if dt.startswith(("int","Int")): return "integer"
if dt.startswith(("float","Float")): return "float"
if dt == "bool": return "boolean"
try:
pd.to_datetime(s.dropna().head(20), errors="raise", utc=True)
return "datetime"
except Exception:
return "string"
def detect_pii_values(vals: List[str]):
flags = {"email": False, "phone": False, "cpf": False}
for v in vals:
if v is None: continue
sv = str(v).strip()
if re.search(EMAIL_RE, sv, flags=re.I): flags["email"] = True
if re.match(PHONE_RE_BR, sv): flags["phone"] = True
if re.match(CPF_RE, sv): flags["cpf"] = True
return flags
def suggest_description(col: str) -> str:
c = (col or "").lower()
if "email" in c: return "Customer email address."
if "phone" in c or "telefone" in c: return "Customer phone number."
if "document" in c or "cpf" in c: return "Government ID (CPF)."
if "name" in c: return "Customer or receiver name."
if "order" in c: return "Order identifier or attribute."
if "sku" in c: return "Product SKU attribute."
if "utm" in c: return "Marketing attribution parameter (UTM)."
if "payment" in c: return "Payment-related field."
if "status" in c: return "Operational status."
if "date" in c or "created" in c: return "Datetime attribute."
if "city" in c or "uf" in c or "country" in c or "postal" in c: return "Geographic attribute."
return "Business attribute extracted from CSV."
def profile_dataframe(table_name: str, df: pd.DataFrame, headers_only: bool) -> pd.DataFrame:
cols = []
for col in df.columns:
series = df[col]
desc = suggest_description(col)
if headers_only:
inferred = "string"
distinct = None
null_pct = None
examples = []
pii_flags = {
"email": "email" in col.lower(),
"phone": "phone" in col.lower() or "telefone" in col.lower(),
"cpf": "cpf" in col.lower() or "document" in col.lower()
}
else:
inferred = infer_simple_type(series)
distinct = int(series.nunique(dropna=True))
null_pct = float(series.isna().mean() * 100.0)
examples = series.dropna().astype(str).head(3).tolist()
pii_flags = detect_pii_values(series.dropna().astype(str).head(50).tolist())
pii = any(pii_flags.values())
cols.append({
"table": table_name,
"column": col,
"inferred_type": inferred,
"distinct_count": distinct,
"null_pct": None if null_pct is None else round(null_pct, 2),
"pii": pii,
"pii_email": bool(pii_flags.get("email")),
"pii_phone": bool(pii_flags.get("phone")),
"pii_cpf": bool(pii_flags.get("cpf")),
"examples": examples,
"description_suggestion": desc
})
return pd.DataFrame(cols)
# ================= Relationships (scored) =================
def _type_bucket(s: pd.Series) -> str:
dt = str(s.dtype)
if dt.startswith(("int","Int")): return "int"
if dt.startswith(("float","Float")): return "float"
if dt == "bool": return "bool"
try:
pd.to_datetime(s.dropna().head(10), errors="raise")
return "date"
except Exception:
return "str"
def _name_similarity(a: str, b: str) -> float:
a, b = a.lower(), b.lower()
if HAS_RAPIDFUZZ:
return max(fuzz.token_sort_ratio(a,b), fuzz.partial_ratio(a,b))/100.0
# simple fallback
return 1.0 if a == b else (min(len(a), len(b))/max(len(a), len(b)) if a in b or b in a else 0.0)
def _uniqueness(series: pd.Series) -> float:
s = series.dropna()
if len(s)==0: return 0.0
return s.nunique()/len(s)
def infer_relationships_scored(
tables: Dict[str, pd.DataFrame],
headers_only: bool,
hints: Dict[str, list],
overlap_sample:int=5000,
base_thresh:float=0.55
) -> pd.DataFrame:
if headers_only:
return pd.DataFrame()
rows = []
names = list(tables.keys())
for i in range(len(names)):
for j in range(i+1, len(names)):
t1, t2 = names[i], names[j]
df1, df2 = tables[t1], tables[t2]
# candidate columns: manual hints first, then heuristics
cand1 = hints.get(t1) or [c for c in df1.columns if any(k in c.lower() for k in ["id","order","email","cpf","user","customer"])]
cand2 = hints.get(t2) or [c for c in df2.columns if any(k in c.lower() for k in ["id","order","email","cpf","user","customer"])]
cand1 = cand1 or list(df1.columns[:5])
cand2 = cand2 or list(df2.columns[:5])
for c1 in cand1:
s1 = df1[c1]
type1 = _type_bucket(s1)
uniq1 = _uniqueness(s1)
vals1 = set(s1.dropna().astype(str).unique()[:overlap_sample])
if not vals1:
continue
for c2 in cand2:
s2 = df2[c2]
type2 = _type_bucket(s2)
uniq2 = _uniqueness(s2)
vals2 = set(s2.dropna().astype(str).unique()[:overlap_sample])
if not vals2:
continue
name_sim = _name_similarity(c1, c2) # 0..1
type_match = 1.0 if (type1==type2 or {"int","float"}=={type1,type2}) else 0.0
inter = len(vals1 & vals2)
denom = max(1, min(len(vals1), len(vals2)))
value_overlap = inter/denom # 0..1
score = 0.40*name_sim + 0.30*type_match + 0.30*value_overlap
# bonus for likely keys
null1 = float(s1.isna().mean()); null2 = float(s2.isna().mean())
if uniq1>0.7 and uniq2>0.7 and null1<0.3 and null2<0.3:
score += 0.05
if score >= base_thresh:
rows.append({
"left_table": t1, "left_key": c1,
"right_table": t2, "right_key": c2,
"name_similarity": round(name_sim,3),
"type_match": int(type_match),
"value_overlap": round(value_overlap,3),
"left_uniqueness": round(uniq1,3),
"right_uniqueness": round(uniq2,3),
"score": round(min(score,1.0),3),
"relationship": "potential join"
})
df = pd.DataFrame(rows).sort_values("score", ascending=False)
return df
def keyword_search(glossary_df: pd.DataFrame, query: str, top_k: int = 12) -> pd.DataFrame:
if glossary_df is None or glossary_df.empty or not query.strip():
return pd.DataFrame()
q = query.lower()
mask = (glossary_df["column"].str.lower().str.contains(q)) | (
glossary_df["description_suggestion"].str.lower().str.contains(q)
)
df = glossary_df[mask].copy()
if df.empty:
tokens = [t for t in q.split() if len(t) > 2]
def hit(row):
text = f"{row['table']} {row['column']} {row['description_suggestion']}".lower()
return any(tok in text for tok in tokens)
df = glossary_df[glossary_df.apply(hit, axis=1)]
return df.head(top_k)
def export_markdown(glossary_df: pd.DataFrame) -> str:
lines = ["# Unified Data Dictionary", ""]
for t in sorted(glossary_df["table"].unique()):
lines.append(f"## {t}")
sub = glossary_df[glossary_df["table"] == t]
lines.append("| Column | Type | Null % | PII | Description | Examples |")
lines.append("|---|---:|---:|:---:|---|---|")
for _, r in sub.iterrows():
ex = ", ".join([str(x) for x in (r.get("examples") or [])])
lines.append(f"| {r['column']} | {r['inferred_type']} | {'' if r['null_pct'] is None else r['null_pct']} | {'βœ…' if r['pii'] else 'β€”'} | {r['description_suggestion']} | {ex} |")
lines.append("")
return "\n".join(lines)
def save_to_duckdb(glossary_df: pd.DataFrame, db_path: str = "catalog.duckdb"):
if not HAS_DUCKDB:
st.warning("DuckDB not installed. Skipping persistence.")
return
con = duckdb.connect(db_path)
con.execute("CREATE SCHEMA IF NOT EXISTS catalog")
con.execute("CREATE TABLE IF NOT EXISTS catalog.glossary AS SELECT * FROM glossary_df LIMIT 0")
con.register("glossary_df", glossary_df)
con.execute("DELETE FROM catalog.glossary")
con.execute("INSERT INTO catalog.glossary SELECT * FROM glossary_df")
con.close()
# ====================== UI: Header, Sidebar ======================
st.title("Unified Data Catalog β€” CSV/Excel MVP")
st.caption("Upload β†’ Glossary β†’ Relationships β†’ Q&A β†’ (optional) LLM Enrichment & Graph")
with st.sidebar:
st.header("1) Upload files")
uploaded = st.file_uploader("Upload CSV/Excel files", type=["csv","xlsx","xls"], accept_multiple_files=True)
st.header("2) Options")
headers_only = st.toggle("Header-only mode (no data profiling)", value=False)
role = st.selectbox("Viewer role (PII masking)", ["Marketing/CS (masked)", "Data Steward (full)"])
persist = st.toggle("Persist glossary to DuckDB", value=False, help="Saves to catalog.duckdb (requires duckdb)")
st.header("3) LLM Enrichment")
use_llm = st.toggle("Enrich with LLM (clusters + graph)", value=False)
openai_key = st.text_input("OPENAI_API_KEY", type="password") if use_llm else None
k_groups = st.slider("Number of semantic groups", 4, 12, 6) if use_llm else None
tables: Dict[str, pd.DataFrame] = {}
if uploaded:
for file in uploaded:
name = file.name
if name.lower().endswith(".csv"):
df = pd.read_csv(file) if not headers_only else pd.read_csv(file, nrows=0)
tables[name.replace(".csv","")] = df
else:
# Excel (first sheet)
xls = pd.ExcelFile(file)
first = xls.sheet_names[0] if xls.sheet_names else None
if first:
df = pd.read_excel(xls, sheet_name=first) if not headers_only else pd.read_excel(xls, sheet_name=first, nrows=0)
tables[name.rsplit(".",1)[0]] = df
if not tables:
st.info("Upload at least one CSV/Excel to begin.")
st.stop()
# ====================== Manual key tagging ======================
if "key_hints" not in st.session_state:
st.session_state.key_hints = {}
st.subheader("Key Fields (optional manual tagging)")
for tname, df in tables.items():
cols = list(df.columns)
default = [c for c in cols if c.lower() in {"id","order","order_id","email","cpf","user_id","customer_id"}]
picked = st.multiselect(
f"Select likely key columns in **{tname}**",
cols, default=default, key=f"keys_{tname}"
)
st.session_state.key_hints[tname] = picked
st.download_button(
"Download key hints (JSON)",
json.dumps(st.session_state.key_hints, indent=2).encode("utf-8"),
file_name="key_hints.json"
)
# ====================== Detected tables ======================
st.subheader("Detected Tables")
tabs = st.tabs([t for t in tables.keys()])
for i, tname in enumerate(tables.keys()):
with tabs[i]:
df = tables[tname]
st.write(f"**{tname}** β€” rows: {len(df):,} (headers only: {headers_only}), columns: {len(df.columns)}")
st.dataframe(df.head(10) if not headers_only else pd.DataFrame(columns=df.columns))
# ====================== Glossary ======================
st.subheader("Unified Glossary")
profiles = []
for t, df in tables.items():
tmp = df if not headers_only else pd.DataFrame(columns=df.columns)
prof = profile_dataframe(t, tmp, headers_only=headers_only)
profiles.append(prof)
glossary_df = pd.concat(profiles, ignore_index=True)
# Role-based masking of examples
if role.startswith("Marketing"):
def mask_examples(lst):
out = []
for v in lst or []:
if isinstance(v, str) and (re.search(EMAIL_RE, v) or re.match(PHONE_RE_BR, v) or re.match(CPF_RE, v)):
if len(v) <= 4: out.append("*"*len(v))
else: out.append(v[:2] + "*"*(len(v)-4) + v[-2:])
else:
out.append(v)
return out
glossary_df["examples"] = glossary_df["examples"].apply(mask_examples)
left, right = st.columns([3,1])
with left:
st.dataframe(glossary_df, use_container_width=True, height=420)
with right:
st.metric("Tables", len(tables))
st.metric("Total fields", len(glossary_df))
st.metric("PII fields", int(glossary_df["pii"].sum()))
# Downloads
st.download_button("Download glossary (CSV)", glossary_df.to_csv(index=False).encode("utf-8"), file_name="glossary.csv")
st.download_button("Download glossary (JSON)", glossary_df.to_json(orient="records").encode("utf-8"), file_name="glossary.json")
md = export_markdown(glossary_df)
st.download_button("Export data dictionary (Markdown)", md.encode("utf-8"), file_name="data_dictionary.md")
# Persistence
if persist:
save_to_duckdb(glossary_df, db_path="catalog.duckdb")
st.success("Glossary saved to DuckDB (catalog.duckdb).")
# ====================== Relationships ======================
st.subheader("Potential Relationships (scored)")
rels = infer_relationships_scored(
tables,
headers_only=headers_only,
hints=st.session_state.key_hints,
base_thresh=0.55
)
if rels.empty:
st.info("No strong relationships detected (or disabled in header-only mode).")
else:
st.dataframe(rels, use_container_width=True, height=320)
st.download_button("Download relationships (CSV)", rels.to_csv(index=False).encode("utf-8"), file_name="relationships.csv")
# ====================== Q&A ======================
st.subheader("Ask the Catalog")
q = st.text_input("Type a keyword or short question (e.g., 'delivery', 'utm campaign', 'order id')")
if st.button("Search"):
res = keyword_search(glossary_df, q, top_k=12)
if res.empty:
st.warning("No matches found. Try different keywords.")
else:
st.write(f"Top matches for: **{q}**")
st.dataframe(res[["table","column","inferred_type","null_pct","pii","description_suggestion","examples"]])
# ====================== LLM Enrichment (optional) ======================
def build_text_repr(row):
ex = ", ".join([str(x) for x in (row.get("examples") or [])])
return f"{row['table']}.{row['column']} | {row['inferred_type']} | PII={row['pii']} | {row['description_suggestion']} | ex: {ex}"
def get_embeddings_openai(texts, api_key):
# Keep batches small to avoid timeouts in Spaces
client = OpenAI(api_key=api_key)
vectors = []
for t in texts:
resp = client.embeddings.create(model="text-embedding-3-small", input=t)
vectors.append(resp.data[0].embedding)
return np.array(vectors, dtype=np.float32)
def cluster_columns(embeddings, k:int=6):
if not HAS_SKLEARN:
raise RuntimeError("scikit-learn not installed")
X = PCA(n_components=min(50, embeddings.shape[1])).fit_transform(embeddings)
km = KMeans(n_clusters=k, n_init="auto", random_state=42)
labels = km.fit_predict(X)
return labels
def label_cluster_via_llm(api_key, cluster_samples:list):
client = OpenAI(api_key=api_key)
prompt = f"""
You are a data catalog taxonomist. Name and describe this group of fields in 3-8 words (business-oriented), then list 3 bullets of what analysts can do with it.
Fields:
{chr(10).join(cluster_samples[:12])}
Return JSON with keys: name, description, bullets.
"""
resp = client.chat.completions.create(
model="gpt-4o-mini",
messages=[{"role":"user","content":prompt}],
temperature=0.2,
response_format={"type":"json_object"},
max_tokens=250
)
return json.loads(resp.choices[0].message.content)
def build_graph(glossary_df, rels_df, cluster_labels, cluster_meta):
if not HAS_PYVIS:
raise RuntimeError("pyvis/networkx not installed")
G = nx.Graph()
# cluster nodes
for cid, name in cluster_labels.items():
G.add_node(f"cluster::{cid}", label=name, color="#6C5CE7", shape="ellipse")
# table nodes
for t in sorted(glossary_df["table"].unique()):
G.add_node(f"table::{t}", label=t, color="#0984E3", shape="box")
# column nodes (can be heavy for very wide tables; keep for now)
for _, r in glossary_df.iterrows():
cid = int(r["cluster_id"])
node_id = f"col::{r['table']}.{r['column']}"
G.add_node(node_id, label=r["column"], color="#74B9FF", shape="dot")
G.add_edge(f"table::{r['table']}", node_id, weight=1)
G.add_edge(f"cluster::{cid}", node_id, weight=1)
# relationship edges between tables
for _, e in rels_df.iterrows():
lt = e["left_table"]; rt = e["right_table"]
w = float(e.get("score", 0.6))
G.add_edge(f"table::{lt}", f"table::{rt}", weight=5*w, color="#00B894")
return G
def render_pyvis(G, height="680px"):
net = Network(height=height, width="100%", notebook=False, cdn_resources="in_line")
net.from_nx(G)
net.toggle_physics(True)
html = net.generate_html()
components.html(html, height=700, scrolling=True)
if use_llm:
# Dependency/key checks
if not HAS_OPENAI:
st.warning("LLM enrichment unavailable: OpenAI SDK not installed.")
elif not openai_key:
st.info("Enter OPENAI_API_KEY in the sidebar to enable enrichment.")
elif not HAS_SKLEARN:
st.warning("LLM enrichment unavailable: scikit-learn not installed.")
else:
st.subheader("Semantic Groups (LLM-enriched)")
try:
# 1) texts & embeddings
texts = [build_text_repr(r) for _, r in glossary_df.iterrows()]
with st.spinner("Computing embeddings..."):
embs = get_embeddings_openai(texts, openai_key)
# 2) clustering
with st.spinner("Clustering columns..."):
labels = cluster_columns(embs, k=k_groups or 6)
glossary_df["cluster_id"] = labels
# 3) label clusters
cluster_labels, cluster_meta = {}, {}
for cid in sorted(set(labels)):
samples = [texts[i] for i in np.where(labels==cid)[0][:15]]
try:
meta = label_cluster_via_llm(openai_key, samples)
cluster_labels[cid] = meta.get("name") or f"Group {cid}"
cluster_meta[cid] = meta
except Exception:
cluster_labels[cid] = f"Group {cid}"
cluster_meta[cid] = {"description":"", "bullets":[]}
# show groups
for cid in sorted(cluster_labels.keys()):
sub = glossary_df[glossary_df["cluster_id"]==cid]
st.markdown(f"**{cluster_labels[cid]}** β€” {len(sub)} fields")
if cluster_meta[cid].get("description"):
st.caption(cluster_meta[cid]["description"])
cols_preview = ", ".join([f"{r['table']}.{r['column']}" for _, r in sub.head(8).iterrows()])
if cols_preview:
st.write(cols_preview)
# 4) graph
st.subheader("Visual Map β€” Data Ecosystem")
if not rels.empty and HAS_PYVIS:
G = build_graph(glossary_df, rels, cluster_labels, cluster_meta)
render_pyvis(G)
elif not HAS_PYVIS:
st.info("Install pyvis & networkx to render the interactive graph.")
else:
st.info("No relationships available to render between tables.")
# 5) downloads
payload = {
"cluster_labels": cluster_labels,
"cluster_meta": cluster_meta,
"columns": glossary_df[["table","column","cluster_id"]].to_dict(orient="records")
}
st.download_button(
"Download semantic groups (JSON)",
json.dumps(payload, indent=2).encode("utf-8"),
file_name="semantic_groups.json"
)
except Exception as e:
st.error(f"LLM enrichment failed: {e}")