Coverage for datakiln/extraction/models.py: 0%
256 statements
« prev ^ index » next coverage.py v7.17.3, created at 2026-11-16 23:23 +0000
« prev ^ index » next coverage.py v7.17.3, created at 2026-11-16 23:23 +0000
1"""
2Configuration domain models and task graphs for DBMS extraction pipeline.
3"""
4import re
5import math
6import json
7from uuid import uuid4
8from datetime import datetime
9from pathlib import Path
10from itertools import repeat
11from functools import cached_property
12from typing import (
13 List, Dict, Set, Optional, Any, Tuple, Iterator, Iterable, Union,
14 TYPE_CHECKING
15)
17from sqlalchemy import (
18 select, exists, literal, and_, or_, false, text,
19 Table, MetaData
20)
22from datakiln.metadata.graph import DependencyGraph
24if TYPE_CHECKING:
25 from datakiln.extraction.strategies import ExecutionStrategy
28# Generate a unique run UUID shared across extraction components
29UUID = str(uuid4()).rsplit("-", maxsplit=1)[-1]
32def alias_column(column: str, default: str) -> str:
33 """Impute table alias for column reference.
35 :param column: Column name or template string.
36 :type column: str
38 :param default: Default table alias.
39 :type default: str
41 :return: Aliased column name.
42 :rtype: str
43 """
44 if "." in column:
45 return column
46 if value := re.search(r'\{(.*?)\}', column):
47 return value.group(1)
48 return f"{default}.{column}"
51def get_table(name: str, alias: Optional[str] = None) -> Any:
52 """Helper to create a Table object, resolving schema-qualification.
54 :param name: Table name (potentially schema-qualified).
55 :type name: str
57 :param alias: Optional table alias name.
58 :type alias: str, optional
60 :return: SA Table or Alias object.
61 :rtype: Table | Alias
62 """
63 if "." in name:
64 schema, table = name.split(".", 1)
65 tbl = Table(table, MetaData(), schema=schema)
66 else:
67 tbl = Table(name, MetaData())
68 return tbl.alias(alias) if alias else tbl
71def _coerce(value: Any) -> Any:
72 """Coerce value to datetime if it's an ISO formatted string.
74 :param value: Input value to coerce.
75 :type value: Any
77 :return: Coerced datetime object or the original value.
78 :rtype: Any
79 """
80 if isinstance(value, str) and '-' in value:
81 try:
82 return datetime.fromisoformat(value)
83 except ValueError:
84 pass
85 return value
88def _is_null(v: Any) -> bool:
89 """Check if a value is None or NaN (Not a Number).
91 :param v: Value to inspect.
92 :type v: Any
94 :return: True if the value is None or a float NaN, False otherwise.
95 :rtype: bool
96 """
97 if v is None:
98 return True
99 if isinstance(v, float) and math.isnan(v):
100 return True
101 return False
104class ReferenceConfig:
105 # pylint: disable=too-few-public-methods
106 """Target reference table configuration.
108 :param columns: Reference column names.
109 :type columns: list[str]
111 :param schema: Reference database schema.
112 :type schema: str, optional
114 :param table: Reference table name.
115 :type table: str, optional
117 :param operators: Comparison operators.
118 :type operators: list[str], optional
120 :param alias: Table alias.
121 :type alias: str, optional
122 """
124 def __init__(
125 self,
126 columns: List[str],
127 schema: Optional[str] = None,
128 table: Optional[str] = None,
129 operators: Optional[List[str]] = None,
130 alias: Optional[str] = "ref"
131 ):
132 # pylint: disable=too-many-arguments, too-many-positional-arguments
133 self.schema = schema
134 self.table = table
135 self.operators = operators or repeat("=")
136 self.alias = alias or "ref"
137 self.columns = [alias_column(c, self.alias) for c in columns]
139 @property
140 def full_name(self) -> str:
141 """Returns the full table name (schema.table)."""
142 if self.schema and self.table:
143 return f"{self.schema}.{self.table}"
144 return self.table or ""
146 @cached_property
147 def patterns(self) -> List[Any]:
148 """Precompiled regex patterns for reference columns."""
149 pattern_list = []
150 for column in self.columns:
151 pattern_str = rf"(?<![\w\.\:]){re.escape(column)}(?![\w\.])"
152 pattern_list.append(re.compile(pattern_str))
153 return pattern_list
156class ColumnPair:
157 """Base class for a single condition in a JoinConfig pair list."""
159 def __init__(self, left: str, right: str):
160 self.left = left
161 self.right = right
163 @staticmethod
164 def bare(col: str) -> str:
165 """Strip the alias prefix from a qualified column name.
167 :param col: Qualified or bare column name (e.g. ``'alias.column'``).
168 :type col: str
170 :return: The bare column name after the last dot.
171 :rtype: str
172 """
173 return col.split(".")[-1]
175 @staticmethod
176 def extract_columns(expr: str, alias: str) -> List[str]:
177 """Extract all column names associated with an alias.
179 :param expr: SQL column expression
180 (e.g. ``'COALESCE(src.id, ref.pat_id)'``).
181 :type expr: str
183 :param alias: Table alias (e.g. ``'ref'``).
184 :type alias: str
186 :return: List of extracted bare column names.
187 :rtype: list[str]
188 """
189 pattern = rf"(?<![\w\.]){re.escape(alias)}\.([a-zA-Z0-9_]+)(?![\w\.])"
190 return [match.group(1) for match in re.finditer(pattern, expr)]
192 @classmethod
193 def classify(
194 cls,
195 left: str,
196 right: str,
197 cohort_alias: str
198 ) -> "ColumnPair":
199 """Classify a processed column pair by inspecting its syntax.
201 :param left: Processed left-side column.
202 :type left: str
204 :param right: Processed right-side column.
205 :type right: str
207 :param cohort_alias: Alias of the root reference (local cohort table).
208 :type cohort_alias: str
210 :return: Appropriate :class:`ColumnPair` subclass instance.
211 :rtype: ColumnPair
212 """
213 # Handle constant filters (i.e., columns without an alias)
214 if "." not in left:
215 return ConstantFilter(left, right)
216 if "." not in right:
217 # normalise: value always left
218 return ConstantFilter(right, left) # pylint: disable=arguments-out-of-order # noqa: E501
220 # Handle seed columns (i.e., columns from the root cohort table)
221 if left.startswith(f"{cohort_alias}."):
222 return SeedPair(left, right)
224 # Handle cross columns (i.e., columns from other reference tables)
225 return CrossPair(left, right)
227 def to_onclause(self, cohort_alias: str, ref_alias: str) -> str:
228 """Return the SQL ON-clause fragment for this pair.
230 :param cohort_alias: Alias of the local cohort table.
231 :type cohort_alias: str
233 :param ref_alias: Alias of the remote join table.
234 :type ref_alias: str
236 :return: SQL equality fragment for use in a JOIN ON clause.
237 :rtype: str
238 """
239 raise NotImplementedError
242class SeedPair(ColumnPair):
243 """Links a local cohort column to a remote join-table column.
245 At query time the local cohort column is matched against the remote table
246 via a VALUES construct, passing seed IDs across database boundaries.
247 """
249 @property
250 def local(self) -> str:
251 """Bare cohort column name: 'alias.col' -> 'col'."""
252 return self.bare(self.left)
254 @property
255 def remote(self) -> str:
256 """Bare remote column name: 'alias.col' -> 'col'."""
257 return self.bare(self.right)
259 def to_onclause(self, cohort_alias: str, ref_alias: str) -> str:
260 """:inheritdoc:"""
261 return f"{cohort_alias}.{self.local} = {ref_alias}.{self.remote}"
264class ConstantFilter(ColumnPair):
265 """A constant equality constraint on a remote column.
267 Applied as a WHERE predicate in StreamStrategy and as an ON-clause fragment
268 in TempTableStrategy. Normalised so the constant value is always stored in
269 ``left``.
270 """
272 @property
273 def value(self) -> str:
274 """The literal constant: e.g. '15'."""
275 return self.left
277 @property
278 def remote(self) -> str:
279 """Bare remote column name: 'alias.col' -> 'col'."""
280 return self.bare(self.right)
282 def to_onclause(self, cohort_alias: str, ref_alias: str) -> str:
283 """:inheritdoc:"""
284 return f"{ref_alias}.{self.remote} = {self.value}"
287class CrossPair(ColumnPair):
288 """An equality condition between two remote aliased columns.
290 Applied as part of the JOIN ON clause when joining remote tables to one
291 another (e.g. ``hx.pat_id = enc.pat_id``).
292 """
294 def to_onclause(self, cohort_alias: str, ref_alias: str) -> str:
295 """:inheritdoc:"""
296 return f"{self.left} = {self.right}"
299class JoinConfig:
300 # pylint: disable=too-few-public-methods
301 """Intermediate join configuration for reference chains.
303 :param columns: Source columns for the join.
304 :type columns: list[str]
306 :param reference: Reference table configuration.
307 :type reference: dict[str, Any]
308 """
310 def __init__(
311 self,
312 columns: List[str],
313 reference: Dict[str, Any],
314 reference_alias: str = "ref"
315 ):
316 self.reference_alias = reference_alias
317 self.reference = ReferenceConfig(**reference)
319 self.pairs = []
320 for left, ref_col in zip(columns, self.reference.columns):
321 self.pairs.append(ColumnPair.classify(
322 alias_column(left, reference_alias),
323 ref_col,
324 reference_alias,
325 ))
327 @property
328 def columns(self) -> List[str]:
329 """Left-side column references, other than the constants."""
330 columns = []
331 for pair in self.pairs:
332 if not isinstance(pair, ConstantFilter):
333 columns.append(pair.left)
334 return columns
336 def seed_pairs(self) -> Iterator["SeedPair"]:
337 """Pairs that link a local cohort column to a remote column."""
338 return (p for p in self.pairs if isinstance(p, SeedPair))
340 def const_filters(self) -> Iterator["ConstantFilter"]:
341 """Pairs that apply a constant equality constraint."""
342 return (p for p in self.pairs if isinstance(p, ConstantFilter))
344 def cross_pairs(self) -> Iterator["CrossPair"]:
345 """Pairs that join two remote aliased columns."""
346 return (p for p in self.pairs if isinstance(p, CrossPair))
348 def to_join(self, cohorts: Dict[str, str]):
349 """Return the resolved table name to join against.
351 Prefers the temporary table created during this run if it exists,
352 otherwise falls back to the permanent schema-qualified table name.
354 :param cohorts: Mapping of task name -> created temp table name,
355 as maintained by the active ExecutionStrategy.
356 :type cohorts: dict[str, str]
358 :return: Resolved table name string (used to build SA TableClause).
359 :rtype: str
360 """
361 if self.reference.full_name in cohorts:
362 return cohorts[self.reference.full_name]
363 return self.reference.full_name
365 def to_onclause(self) -> Any:
366 """Build a SQLAlchemy ON clause for this join as a text expression.
368 :return: SA text clause for the JOIN ON condition.
369 :rtype: ClauseElement
370 """
371 return text(" AND ".join(
372 pair.to_onclause(self.reference_alias, self.reference.alias)
373 for pair in self.pairs
374 ))
377class FilterConfig:
378 """Filter configuration mapping a task to a cohort or reference table.
380 :param columns: Target columns to filter.
381 :type columns: list[str]
383 :param reference: Target reference configuration.
384 :type reference: ReferenceConfig, optional
386 :param cohort: Source cohort identifier.
387 :type cohort: str, optional
389 :param join: List of intermediate join chains.
390 :type join: list[JoinConfig], optional
391 """
393 def __init__(
394 self,
395 columns: List[str],
396 reference: Dict[str, Any],
397 cohort: Optional[str] = None,
398 join: Optional[List[Dict[str, Any]]] = None
399 ):
400 self.columns = [alias_column(c, "src") for c in columns]
401 self.reference = ReferenceConfig(**reference)
402 self.cohort = cohort
403 self.join = []
405 valid_aliases = {self.reference.alias}
406 for j in join or []:
407 j_config = JoinConfig(**j, reference_alias=self.reference.alias)
409 # Validate that joins do not reference tables outside its scope
410 for col in j_config.columns:
411 if col.partition(".")[0] not in valid_aliases:
412 raise ValueError(f"Invalid alias in join column '{col}'.")
414 self.join.append(j_config)
415 valid_aliases.add(j_config.reference.alias)
417 @property
418 def is_sql_cohort(self) -> bool:
419 """Evaluates whether the filter targets an explicit SQL cohort file."""
420 return bool(self.cohort and self.cohort.endswith(".sql"))
422 @property
423 def resolved_cohort(self) -> Optional[str]:
424 """Return the resolved cohort table name."""
425 if self.cohort:
426 return self.cohort
427 if self.reference.schema and self.reference.table:
428 return f"{self.reference.schema}.{self.reference.table}"
429 return None
431 @property
432 def cohort_columns(self) -> List[str]:
433 """Return the target columns to fetch from the cohort table."""
434 # Extract seed columns from joins
435 columns = {}
436 for j in self.join:
437 for pair in j.seed_pairs():
438 columns[pair.local] = None
440 # Extract any identifier that starts with "alias."
441 alias = self.reference.alias
442 for col in self.reference.columns:
443 for col_name in ColumnPair.extract_columns(col, alias):
444 columns[col_name] = None
445 return list(columns)
447 def to_exists(
448 self,
449 strategy: 'ExecutionStrategy'
450 ) -> Any:
451 """Generate a SQLAlchemy EXISTS subquery for this filter configuration.
453 :param strategy: Execution strategy (used to resolve cohort aliases).
454 :type strategy: ExecutionStrategy
456 :return: SA exists() expression.
457 :rtype: Exists
458 """
459 # Resolve the primary table
460 resolved = strategy.cohorts.get(self.resolved_cohort, self.cohort)
462 if resolved:
463 if resolved.strip().startswith("("):
464 # resolved is a subquery like "( SELECT ... ) cohort_name"
465 # Strip the cohort name and replace it with the reference alias
466 query_body = resolved.strip().rsplit(")", 1)[0] + ")"
467 from_clause = text(f"{query_body} {self.reference.alias}")
468 else:
469 from_clause = get_table(resolved, self.reference.alias)
470 elif self.reference.schema and self.reference.table:
471 from_clause = Table(
472 self.reference.table,
473 MetaData(),
474 schema=self.reference.schema
475 ).alias(self.reference.alias)
476 else:
477 from_clause = None
479 # Build WHERE conditions
480 conditions = [
481 text(f"{col} {op} {ref}")
482 for col, op, ref in zip(
483 self.columns,
484 self.reference.operators,
485 self.reference.columns
486 )
487 ]
489 # If there is no from_clause, return the conditions as a conjunction
490 if from_clause is None:
491 return and_(*conditions)
493 # Build join chain
494 for j in self.join:
495 join_name = j.to_join(strategy.cohorts)
496 join_tbl = get_table(join_name, j.reference.alias)
497 from_clause = from_clause.join(join_tbl, j.to_onclause())
499 return exists(
500 select(literal(1))
501 .select_from(from_clause)
502 .where(and_(*conditions))
503 )
505 def to_where(self, values: List[Tuple]) -> Any:
506 # pylint: disable=too-many-locals
507 """Generate a SQLAlchemy WHERE clause from pre-fetched cohort data.
509 :param values: List of value tuples from the resolved cohort.
510 :type values: list[tuple]
512 :return: SA ClauseElement.
513 :rtype: ClauseElement
514 """
516 if not values:
517 return false()
519 or_blocks = []
520 for row_idx, values_set in enumerate(values):
521 conditions = []
522 for col_idx, (col, op, val) in enumerate(zip(
523 self.columns,
524 self.reference.operators,
525 values_set
526 )):
527 if _is_null(val):
528 continue
530 # Set the first parameter and bind
531 expr = col
532 param_name = f"p_{row_idx}_{col_idx}"
533 used_params = {param_name: _coerce(val)}
535 # Replace any reference column in `col`
536 # with its corresponding bind parameter
537 for ref_idx, ref_col in enumerate(self.reference.columns):
538 # Skip references that are not present in the expression
539 if ref_col not in expr:
540 continue
542 # Skip references that are NULL
543 ref_val = values_set[ref_idx]
544 if _is_null(ref_val):
545 continue
547 # Replace the reference column with a bind parameter
548 pattern = self.reference.patterns[ref_idx]
549 ref_param = f"p_{row_idx}_{ref_idx}"
550 expr, count = pattern.subn(f":{ref_param}", expr)
551 if count > 0:
552 used_params[ref_param] = _coerce(ref_val)
554 # Append the condition
555 conditions.append(
556 text(f"{expr} {op} :{param_name}").bindparams(
557 **used_params
558 )
559 )
560 if conditions:
561 or_blocks.append(and_(*conditions))
563 return or_(*or_blocks) if or_blocks else false()
566class ExtractMeta:
567 """Extraction metadata."""
569 def __init__(
570 self,
571 table: str,
572 schema: str,
573 filters: List[Union[Dict, List[Dict]]] = None,
574 encoding: str = "utf-8",
575 chunk_size: int = 10000,
576 **kwargs
577 ):
578 # pylint: disable=too-many-arguments, too-many-positional-arguments
579 """
580 :param table: Name of the table.
581 :type table: str
583 :param schema: Name of the database schema.
584 :type schema: str
586 :param filters: Metadata for filters.
587 :type filters: list[dict] | list[list[dict]]
589 :param encoding: encoding of the data file.
590 :type encoding: str
592 :param chunk_size: Chunk size for cohort seed loading during
593 extraction.
594 Smaller values reduce payload size and memory
595 pressure for large tables.
596 :type chunk_size: int
597 """
598 self.table = table
599 self.schema = schema
600 self.encoding = encoding
601 self.chunk_size = chunk_size
602 self.kwargs = kwargs
603 self.filters = []
605 for f in (filters or []):
606 if isinstance(f, list):
607 self.filters.append([FilterConfig(**c) for c in f])
608 else:
609 self.filters.append([FilterConfig(**f)])
611 @property
612 def full_name(self) -> str:
613 """Returns the full table name (schema.table)."""
614 return f"{self.schema}.{self.table}"
616 @property
617 def temp_name(self) -> str:
618 """Returns the temporary table name used during extraction."""
619 return f"tmp_{self.table}_{UUID}"
621 @property
622 def requires(self) -> Set[str]:
623 """Retrieve dependencies from filters.
625 :return: Set of dependency task names.
626 :rtype: set[str]
627 """
628 dependencies = set()
629 for group in self.filters:
630 for table_filter in group:
631 # Cohort dependencies
632 if table_filter.cohort:
633 dependencies.add(table_filter.cohort)
634 # Table dependencies from primary reference
635 elif (
636 table_filter.reference
637 and table_filter.reference.schema
638 and table_filter.reference.table
639 ):
640 ref = table_filter.reference
641 dependencies.add(f"{ref.schema}.{ref.table}")
643 # Join-chain dependencies
644 for j in table_filter.join:
645 if j.reference.schema and j.reference.table:
646 dependencies.add(j.reference.full_name)
648 return dependencies
650 @classmethod
651 def from_json(cls, path: Path) -> 'ExtractMeta':
652 """Load extraction metadata from JSON.
654 :param path: Path to the extraction metadata file.
655 :type path: Path
657 :return: Extraction metadata.
658 :rtype: ExtractMeta
659 """
660 with path.open("r", encoding="utf-8") as file:
661 meta = json.load(file)
662 return cls(**meta)
665class ExtractGraph(DependencyGraph):
666 """Directed Acyclic Graph for extraction tasks.
668 Dependencies that are not tasks (e.g. cohorts and tables that are not
669 extracted) are kept, so they can be orchestrated by the executor.
670 """
672 def __init__(self, tasks: Iterable[ExtractMeta],
673 export: Optional[Set[ExtractMeta]] = None):
674 """
675 :param tasks: Extraction tasks.
676 :type tasks: Iterable[ExtractMeta]
678 :param export: Tasks whose data is exported. The other tasks are only
679 extracted, because they are needed. All by default.
680 :type export: set[ExtractMeta]
681 """
682 super().__init__(tasks, keep_unresolved=True)
683 self.export = set(tasks) if export is None else export
685 @classmethod
686 def select(cls, tasks: List[ExtractMeta],
687 include: Optional[List[str]] = None) -> 'ExtractGraph':
688 """Graph of the tasks to export, and the tasks they need.
690 :param tasks: Extraction tasks.
691 :type tasks: list[ExtractMeta]
693 :param include: Names of the tasks to export. All by default.
694 :type include: list[str]
696 :raises ValueError: If any of the names is not a task.
697 """
698 # The graph of all the tasks, which is the one to export by default,
699 # and which tells what each task needs
700 graph = cls(tasks)
701 if not include:
702 return graph
704 # The names must be tasks of the graph, since they are the ones the
705 # user wants to export
706 export = graph.find(include)
708 # Keep only the tasks to export and the ones they need, directly or
709 # indirectly, since they have to be extracted first. The others are
710 # left out of the graph
711 return cls(graph.requirements(export), export)
713 @property
714 def tasks(self) -> Dict[str, ExtractMeta]:
715 """Extraction tasks by name."""
716 return self.nodes