Skip to content

attribute_table_builder

SNOMED attribute table builder for pgvector.

Creates/populates PostgreSQL table {VOCAB_SCHEMA}.snomed_attribute with attribute-value concept rows and their embeddings.`

build_attribute_table(hierarchy_settings, if_exists='append')

Build the pgvector SNOMED attribute table from OMOP relationships.

Parameters:

Name Type Description Default
hierarchy_settings HierarchySettings

Hierarchy settings containing index_build defaults.

required
if_exists Literal['append', 'skip', 'rebuild']

Behavior when target tables already contain rows: - "append": insert without truncating. - "skip": skip populated tables. - "rebuild": truncate first, then insert.

'append'
Source code in src/ariadne/hierarchy/attribute_table_builder.py
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
def build_attribute_table(
    hierarchy_settings: HierarchySettings,
    if_exists: Literal["append", "skip", "rebuild"] = "append"
) -> None:
    """Build the pgvector SNOMED attribute table from OMOP relationships.

    Args:
        hierarchy_settings: Hierarchy settings containing ``index_build`` defaults.
        if_exists: Behavior when target tables already contain rows:
            - ``"append"``: insert without truncating.
            - ``"skip"``: skip populated tables.
            - ``"rebuild"``: truncate first, then insert.
    """
    if if_exists not in {"append", "skip", "rebuild"}:
        raise ValueError(f"Unsupported if_exists mode: {if_exists}")

    embedding_cache_dir = Path(hierarchy_settings.index_build.embedding_cache_folder)
    rebuild = if_exists == "rebuild"

    embedding_cache_dir.mkdir(parents=True, exist_ok=True)

    if if_exists == "skip":
        conn = _pg_connect()
        try:
            if check_populated(conn, "snomed_attribute"):
                logger.info("snomed_attribute already populated — skipping.")
                return
        finally:
            conn.close()

    # Build embeddings first (with parquet checkpoints), then create/upload tables.
    df_attr = load_attributes_from_db(hierarchy_settings.snomed_relationships)
    batch_files, dim = _prepare_attribute_embedding_batches(
        df_attr,
        embedding_batch_size=hierarchy_settings.index_build.embedding_batch_size,
        embedding_cache_dir=embedding_cache_dir,
    )
    if dim is None:
        probe = get_embedding_vectors(["probe"])
        dim = int(probe["embeddings"].shape[1])
    logger.info("Embedding dimension: %d", dim)

    conn = _pg_connect()
    try:
        create_table(conn, dim=dim)

        conn = insert_attributes(
            conn,
            batch_files,
            upload_batch_size=hierarchy_settings.index_build.embedding_batch_size,
            rebuild=rebuild,
        )

        create_embedding_index(conn)
    finally:
        conn.close()

    logger.info("Index build complete.")

check_populated(conn, table)

Return True if table already contains at least one row.

Parameters:

Name Type Description Default
conn Connection

psycopg connection (read access is enough).

required
table str

Table name to check.

required

Returns:

Type Description
bool

True if the table is non-empty.

Source code in src/ariadne/hierarchy/attribute_table_builder.py
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
def check_populated(conn: psycopg.Connection, table: str) -> bool:
    """Return True if *table* already contains at least one row.

    Args:
        conn: psycopg connection (read access is enough).
        table: Table name to check.

    Returns:
        ``True`` if the table is non-empty.
    """
    schema = get_environment_variable("VOCAB_SCHEMA")
    table_path = f"{schema}.{table}"
    with conn.cursor() as cur:
        cur.execute("SELECT to_regclass(%s)", (table_path,))
        if cur.fetchone()[0] is None:
            return False
        cur.execute(
            sql.SQL("SELECT 1 FROM {schema}.{table} LIMIT 1").format(
                schema=sql.Identifier(schema),
                table=sql.Identifier(table),
            )
        )
        return cur.fetchone() is not None

create_embedding_index(conn)

Create the HNSW embedding index for snomed_attribute if missing.

Source code in src/ariadne/hierarchy/attribute_table_builder.py
62
63
64
65
66
67
68
69
70
71
72
def create_embedding_index(conn: psycopg.Connection) -> None:
    """Create the HNSW embedding index for ``snomed_attribute`` if missing."""
    schema = get_environment_variable("VOCAB_SCHEMA")
    with conn.cursor() as cur:
            cur.execute(sql.SQL("""
                CREATE INDEX IF NOT EXISTS snomed_attribute_embedding_idx
                ON {schema}.snomed_attribute
                USING hnsw (embedding halfvec_cosine_ops)
            """).format(schema=sql.Identifier(schema)))
    conn.commit()
    logger.info("Embedding indexes created for attribute table")

create_table(conn, dim)

Create snomed_attribute table.

Safe to call when tables already exist (uses CREATE TABLE IF NOT EXISTS).

Parameters:

Name Type Description Default
conn Connection

Admin psycopg connection.

required
dim int

Embedding dimension

required
Source code in src/ariadne/hierarchy/attribute_table_builder.py
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
def create_table(conn: psycopg.Connection, dim: int) -> None:
    """Create ``snomed_attribute`` table.

    Safe to call when tables already exist (uses ``CREATE TABLE IF NOT EXISTS``).

    Args:
        conn: Admin psycopg connection.
        dim: Embedding dimension
    """
    schema = get_environment_variable("VOCAB_SCHEMA")
    with conn.cursor() as cur:
        cur.execute(sql.SQL("""
            CREATE TABLE IF NOT EXISTS {schema}.snomed_attribute (
                id                 SERIAL PRIMARY KEY,
                concept_id         INTEGER       NOT NULL,
                concept_code       VARCHAR(255)  NOT NULL,
                concept_name       VARCHAR(255)  NOT NULL,
                attribute_category VARCHAR(255)  NOT NULL,
                embedding          halfvec({dim})
            )
        """).format(schema=sql.Identifier(schema), dim=sql.Literal(dim)))
    conn.commit()
    logger.info("Tables created in schema '%s'.", schema)

insert_attributes(conn, batch_files, upload_batch_size, rebuild=False)

Insert pre-embedded parquet batches into snomed_attribute.

Parameters:

Name Type Description Default
conn Connection

Admin psycopg connection (returned — may be replaced on retry).

required
batch_files list[Path]

Parquet files containing pre-embedded attribute rows.

required
upload_batch_size int

Number of rows per INSERT chunk.

required
rebuild bool

When True, truncate the table first.

False

Returns:

Type Description
Connection

The (possibly reconnected) psycopg connection.

Source code in src/ariadne/hierarchy/attribute_table_builder.py
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
def insert_attributes(
    conn: psycopg.Connection,
    batch_files: list[Path],
    upload_batch_size: int,
    rebuild: bool = False,
) -> psycopg.Connection:
    """Insert pre-embedded parquet batches into ``snomed_attribute``.

    Args:
        conn: Admin psycopg connection (returned — may be replaced on retry).
        batch_files: Parquet files containing pre-embedded attribute rows.
        upload_batch_size: Number of rows per INSERT chunk.
        rebuild: When True, truncate the table first.

    Returns:
        The (possibly reconnected) psycopg connection.
    """
    schema = get_environment_variable("VOCAB_SCHEMA")
    if rebuild:
        with conn.cursor() as cur:
            cur.execute(
                sql.SQL("TRUNCATE TABLE {schema}.snomed_attribute RESTART IDENTITY")
                .format(schema=sql.Identifier(schema))
            )
        conn.commit()
        logger.info("Truncated snomed_attribute.")

    total_rows = 0
    insert_sql = sql.SQL(
        "INSERT INTO {schema}.snomed_attribute "
        "(concept_id, concept_code, concept_name, attribute_category, embedding) "
        "VALUES (%s, %s, %s, %s, %s)"
    ).format(schema=sql.Identifier(schema))

    for file_index, batch_file in enumerate(batch_files, start=1):
        df_batch = pd.read_parquet(batch_file)
        rows = [
            (int(r["concept_id"]), str(r["concept_code"]), str(r["concept_name"]),
             str(r["attribute_category"]), list(r["embedding"]))
            for _, r in df_batch.iterrows()
        ]
        total_rows += len(rows)

        for i in range(0, len(rows), upload_batch_size):
            chunk = rows[i: i + upload_batch_size]
            chunk_number = i // upload_batch_size + 1
            for attempt in range(1, _MAX_INSERT_ATTEMPTS + 1):
                try:
                    with conn.cursor() as cur:
                        cur.executemany(insert_sql, chunk)
                    conn.commit()
                    break
                except psycopg.Error as exc:
                    conn.close()
                    if attempt == _MAX_INSERT_ATTEMPTS:
                        raise RuntimeError(
                            f"File {file_index}/{len(batch_files)} chunk {chunk_number} failed after {attempt} attempts."
                        ) from exc
                    logger.warning(
                        "File %d/%d chunk %d attempt %d failed (%s), reconnecting...",
                        file_index,
                        len(batch_files),
                        chunk_number,
                        attempt,
                        exc,
                    )
                    conn = _pg_connect()

        logger.info("Loaded attribute file %d/%d: %s", file_index, len(batch_files), batch_file.name)

    logger.info("Inserted %d rows into %s.snomed_attribute.", total_rows, schema)
    return conn

load_attributes_from_db(snomed_relationships)

Load SNOMED attribute value rows from the OHDSI Vocabularies DB.

Source code in src/ariadne/hierarchy/attribute_table_builder.py
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
def load_attributes_from_db(snomed_relationships: list[str]) -> pd.DataFrame:
    """Load SNOMED attribute value rows from the OHDSI Vocabularies DB."""
    engine = create_engine(get_environment_variable("VOCAB_CONNECTION_STRING"))
    schema = get_environment_variable("VOCAB_SCHEMA")
    rel_list = ", ".join(f"'{r}'" for r in snomed_relationships)
    df = pd.read_sql(f"""
        SELECT DISTINCT c2.concept_id, 
            c2.concept_code, 
            c2.concept_name,
            relationship_id AS attribute_category
        FROM {schema}.concept c
        INNER JOIN {schema}.concept_relationship cr
            ON  c.concept_id   = cr.concept_id_1
        INNER JOIN {schema}.concept c2 ON c2.concept_id = cr.concept_id_2
        WHERE c.vocabulary_id = 'SNOMED'
            AND c.standard_concept = 'S'
            AND cr.relationship_id IN ({rel_list})
            AND cr.invalid_reason IS NULL
            AND c2.invalid_reason IS NULL;
    """, engine)
    logger.info("Loaded %d attribute rows from DB.", len(df))
    return df