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

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) 

16 

17from sqlalchemy import ( 

18 select, exists, literal, and_, or_, false, text, 

19 Table, MetaData 

20) 

21 

22from datakiln.metadata.graph import DependencyGraph 

23 

24if TYPE_CHECKING: 

25 from datakiln.extraction.strategies import ExecutionStrategy 

26 

27 

28# Generate a unique run UUID shared across extraction components 

29UUID = str(uuid4()).rsplit("-", maxsplit=1)[-1] 

30 

31 

32def alias_column(column: str, default: str) -> str: 

33 """Impute table alias for column reference. 

34 

35 :param column: Column name or template string. 

36 :type column: str 

37 

38 :param default: Default table alias. 

39 :type default: str 

40 

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}" 

49 

50 

51def get_table(name: str, alias: Optional[str] = None) -> Any: 

52 """Helper to create a Table object, resolving schema-qualification. 

53 

54 :param name: Table name (potentially schema-qualified). 

55 :type name: str 

56 

57 :param alias: Optional table alias name. 

58 :type alias: str, optional 

59 

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 

69 

70 

71def _coerce(value: Any) -> Any: 

72 """Coerce value to datetime if it's an ISO formatted string. 

73 

74 :param value: Input value to coerce. 

75 :type value: Any 

76 

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 

86 

87 

88def _is_null(v: Any) -> bool: 

89 """Check if a value is None or NaN (Not a Number). 

90 

91 :param v: Value to inspect. 

92 :type v: Any 

93 

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 

102 

103 

104class ReferenceConfig: 

105 # pylint: disable=too-few-public-methods 

106 """Target reference table configuration. 

107 

108 :param columns: Reference column names. 

109 :type columns: list[str] 

110 

111 :param schema: Reference database schema. 

112 :type schema: str, optional 

113 

114 :param table: Reference table name. 

115 :type table: str, optional 

116 

117 :param operators: Comparison operators. 

118 :type operators: list[str], optional 

119 

120 :param alias: Table alias. 

121 :type alias: str, optional 

122 """ 

123 

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] 

138 

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 "" 

145 

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 

154 

155 

156class ColumnPair: 

157 """Base class for a single condition in a JoinConfig pair list.""" 

158 

159 def __init__(self, left: str, right: str): 

160 self.left = left 

161 self.right = right 

162 

163 @staticmethod 

164 def bare(col: str) -> str: 

165 """Strip the alias prefix from a qualified column name. 

166 

167 :param col: Qualified or bare column name (e.g. ``'alias.column'``). 

168 :type col: str 

169 

170 :return: The bare column name after the last dot. 

171 :rtype: str 

172 """ 

173 return col.split(".")[-1] 

174 

175 @staticmethod 

176 def extract_columns(expr: str, alias: str) -> List[str]: 

177 """Extract all column names associated with an alias. 

178 

179 :param expr: SQL column expression 

180 (e.g. ``'COALESCE(src.id, ref.pat_id)'``). 

181 :type expr: str 

182 

183 :param alias: Table alias (e.g. ``'ref'``). 

184 :type alias: str 

185 

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)] 

191 

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. 

200 

201 :param left: Processed left-side column. 

202 :type left: str 

203 

204 :param right: Processed right-side column. 

205 :type right: str 

206 

207 :param cohort_alias: Alias of the root reference (local cohort table). 

208 :type cohort_alias: str 

209 

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 

219 

220 # Handle seed columns (i.e., columns from the root cohort table) 

221 if left.startswith(f"{cohort_alias}."): 

222 return SeedPair(left, right) 

223 

224 # Handle cross columns (i.e., columns from other reference tables) 

225 return CrossPair(left, right) 

226 

227 def to_onclause(self, cohort_alias: str, ref_alias: str) -> str: 

228 """Return the SQL ON-clause fragment for this pair. 

229 

230 :param cohort_alias: Alias of the local cohort table. 

231 :type cohort_alias: str 

232 

233 :param ref_alias: Alias of the remote join table. 

234 :type ref_alias: str 

235 

236 :return: SQL equality fragment for use in a JOIN ON clause. 

237 :rtype: str 

238 """ 

239 raise NotImplementedError 

240 

241 

242class SeedPair(ColumnPair): 

243 """Links a local cohort column to a remote join-table column. 

244 

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 """ 

248 

249 @property 

250 def local(self) -> str: 

251 """Bare cohort column name: 'alias.col' -> 'col'.""" 

252 return self.bare(self.left) 

253 

254 @property 

255 def remote(self) -> str: 

256 """Bare remote column name: 'alias.col' -> 'col'.""" 

257 return self.bare(self.right) 

258 

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}" 

262 

263 

264class ConstantFilter(ColumnPair): 

265 """A constant equality constraint on a remote column. 

266 

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 """ 

271 

272 @property 

273 def value(self) -> str: 

274 """The literal constant: e.g. '15'.""" 

275 return self.left 

276 

277 @property 

278 def remote(self) -> str: 

279 """Bare remote column name: 'alias.col' -> 'col'.""" 

280 return self.bare(self.right) 

281 

282 def to_onclause(self, cohort_alias: str, ref_alias: str) -> str: 

283 """:inheritdoc:""" 

284 return f"{ref_alias}.{self.remote} = {self.value}" 

285 

286 

287class CrossPair(ColumnPair): 

288 """An equality condition between two remote aliased columns. 

289 

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 """ 

293 

294 def to_onclause(self, cohort_alias: str, ref_alias: str) -> str: 

295 """:inheritdoc:""" 

296 return f"{self.left} = {self.right}" 

297 

298 

299class JoinConfig: 

300 # pylint: disable=too-few-public-methods 

301 """Intermediate join configuration for reference chains. 

302 

303 :param columns: Source columns for the join. 

304 :type columns: list[str] 

305 

306 :param reference: Reference table configuration. 

307 :type reference: dict[str, Any] 

308 """ 

309 

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) 

318 

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 )) 

326 

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 

335 

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)) 

339 

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)) 

343 

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)) 

347 

348 def to_join(self, cohorts: Dict[str, str]): 

349 """Return the resolved table name to join against. 

350 

351 Prefers the temporary table created during this run if it exists, 

352 otherwise falls back to the permanent schema-qualified table name. 

353 

354 :param cohorts: Mapping of task name -> created temp table name, 

355 as maintained by the active ExecutionStrategy. 

356 :type cohorts: dict[str, str] 

357 

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 

364 

365 def to_onclause(self) -> Any: 

366 """Build a SQLAlchemy ON clause for this join as a text expression. 

367 

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 )) 

375 

376 

377class FilterConfig: 

378 """Filter configuration mapping a task to a cohort or reference table. 

379 

380 :param columns: Target columns to filter. 

381 :type columns: list[str] 

382 

383 :param reference: Target reference configuration. 

384 :type reference: ReferenceConfig, optional 

385 

386 :param cohort: Source cohort identifier. 

387 :type cohort: str, optional 

388 

389 :param join: List of intermediate join chains. 

390 :type join: list[JoinConfig], optional 

391 """ 

392 

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 = [] 

404 

405 valid_aliases = {self.reference.alias} 

406 for j in join or []: 

407 j_config = JoinConfig(**j, reference_alias=self.reference.alias) 

408 

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}'.") 

413 

414 self.join.append(j_config) 

415 valid_aliases.add(j_config.reference.alias) 

416 

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")) 

421 

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 

430 

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 

439 

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) 

446 

447 def to_exists( 

448 self, 

449 strategy: 'ExecutionStrategy' 

450 ) -> Any: 

451 """Generate a SQLAlchemy EXISTS subquery for this filter configuration. 

452 

453 :param strategy: Execution strategy (used to resolve cohort aliases). 

454 :type strategy: ExecutionStrategy 

455 

456 :return: SA exists() expression. 

457 :rtype: Exists 

458 """ 

459 # Resolve the primary table 

460 resolved = strategy.cohorts.get(self.resolved_cohort, self.cohort) 

461 

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 

478 

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 ] 

488 

489 # If there is no from_clause, return the conditions as a conjunction 

490 if from_clause is None: 

491 return and_(*conditions) 

492 

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()) 

498 

499 return exists( 

500 select(literal(1)) 

501 .select_from(from_clause) 

502 .where(and_(*conditions)) 

503 ) 

504 

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. 

508 

509 :param values: List of value tuples from the resolved cohort. 

510 :type values: list[tuple] 

511 

512 :return: SA ClauseElement. 

513 :rtype: ClauseElement 

514 """ 

515 

516 if not values: 

517 return false() 

518 

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 

529 

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)} 

534 

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 

541 

542 # Skip references that are NULL 

543 ref_val = values_set[ref_idx] 

544 if _is_null(ref_val): 

545 continue 

546 

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) 

553 

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)) 

562 

563 return or_(*or_blocks) if or_blocks else false() 

564 

565 

566class ExtractMeta: 

567 """Extraction metadata.""" 

568 

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 

582 

583 :param schema: Name of the database schema. 

584 :type schema: str 

585 

586 :param filters: Metadata for filters. 

587 :type filters: list[dict] | list[list[dict]] 

588 

589 :param encoding: encoding of the data file. 

590 :type encoding: str 

591 

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 = [] 

604 

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)]) 

610 

611 @property 

612 def full_name(self) -> str: 

613 """Returns the full table name (schema.table).""" 

614 return f"{self.schema}.{self.table}" 

615 

616 @property 

617 def temp_name(self) -> str: 

618 """Returns the temporary table name used during extraction.""" 

619 return f"tmp_{self.table}_{UUID}" 

620 

621 @property 

622 def requires(self) -> Set[str]: 

623 """Retrieve dependencies from filters. 

624 

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}") 

642 

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) 

647 

648 return dependencies 

649 

650 @classmethod 

651 def from_json(cls, path: Path) -> 'ExtractMeta': 

652 """Load extraction metadata from JSON. 

653 

654 :param path: Path to the extraction metadata file. 

655 :type path: Path 

656 

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) 

663 

664 

665class ExtractGraph(DependencyGraph): 

666 """Directed Acyclic Graph for extraction tasks. 

667 

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 """ 

671 

672 def __init__(self, tasks: Iterable[ExtractMeta], 

673 export: Optional[Set[ExtractMeta]] = None): 

674 """ 

675 :param tasks: Extraction tasks. 

676 :type tasks: Iterable[ExtractMeta] 

677 

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 

684 

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. 

689 

690 :param tasks: Extraction tasks. 

691 :type tasks: list[ExtractMeta] 

692 

693 :param include: Names of the tasks to export. All by default. 

694 :type include: list[str] 

695 

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 

703 

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) 

707 

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) 

712 

713 @property 

714 def tasks(self) -> Dict[str, ExtractMeta]: 

715 """Extraction tasks by name.""" 

716 return self.nodes