Coverage for datakiln/metadata/table.py: 76%

312 statements  

« prev     ^ index     » next       coverage.py v7.17.3, created at 2026-11-16 23:23 +0000

1""" 

2Table metadata. 

3""" 

4import re 

5import csv 

6import json 

7import gzip 

8import logging 

9from hashlib import md5 

10from pathlib import Path 

11from copy import deepcopy 

12from textwrap import dedent 

13from itertools import chain 

14from functools import partial 

15 

16from pandas._libs.parsers import STR_NA_VALUES # pylint: disable=no-name-in-module # noqa: E501 

17from sqlalchemy import MetaData as SQLAlchemyMetadata 

18from sqlalchemy import ForeignKey 

19from sqlalchemy.orm import ( 

20 DeclarativeBase, 

21 Mapped, 

22 mapped_column, 

23 relationship 

24) 

25 

26from datakiln.io import open_excel, NullStripper 

27from datakiln.metadata.column import ColumnMeta 

28from datakiln.metadata.files import LocalRepo 

29from datakiln.metadata.location import SSHLocation 

30from datakiln.metadata.foreign_key import ForeignKeyMeta 

31from datakiln.metadata.unpivot import UnpivotMeta 

32 

33 

34logger = logging.getLogger(__name__) 

35 

36 

37class Base(DeclarativeBase): 

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

39 """ 

40 Base ORM Class. 

41 """ 

42 metadata = SQLAlchemyMetadata(schema="__metadata") 

43 

44 

45# Defined before the other records: otherwise pylint reports Mapped[...] as 

46# unsubscriptable in the ones that follow 

47class Artifact(Base): 

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

49 """ 

50 Artifact metadata. 

51 """ 

52 __tablename__ = "artifact_" 

53 

54 path_: Mapped[str] = mapped_column(primary_key=True) 

55 name_: Mapped[str] = mapped_column( 

56 ForeignKey("table_.name_", ondelete="CASCADE")) 

57 checksum_: Mapped[str] = mapped_column() 

58 

59 table: Mapped["Metadata"] = relationship(back_populates="artifact") 

60 

61 

62class Object(Base): 

63 """ 

64 Object built in the database, from the metadata of a table or from a 

65 script. The metadata of the kinds of object are in their own tables. 

66 """ 

67 __tablename__ = "object_" 

68 

69 name_: Mapped[str] = mapped_column(primary_key=True) 

70 kind_: Mapped[str] = mapped_column() 

71 checksum_: Mapped[str] = mapped_column() 

72 

73 __mapper_args__ = {"polymorphic_on": "kind_", "with_polymorphic": "*"} 

74 

75 @property 

76 def schema(self): 

77 """Schema the object is in, if it is in one.""" 

78 raise NotImplementedError 

79 

80 @property 

81 def drops(self): 

82 """Statements that drop the object.""" 

83 raise NotImplementedError 

84 

85 

86class Metadata(Object): 

87 """ 

88 Table built from its metadata. 

89 """ 

90 __tablename__ = "table_" 

91 

92 name_: Mapped[str] = mapped_column( 

93 ForeignKey("object_.name_", ondelete="CASCADE"), primary_key=True) 

94 

95 artifact: Mapped["Artifact"] = relationship( 

96 back_populates="table", passive_deletes=True) 

97 

98 __mapper_args__ = {"polymorphic_identity": "table"} 

99 

100 @property 

101 def schema(self): 

102 return self.name_.partition(".")[0] 

103 

104 @property 

105 def drops(self): 

106 # The table and its stage 

107 schema, _, table = self.name_.partition(".") 

108 return [ 

109 f"DROP TABLE IF EXISTS {stage}.{table} CASCADE" 

110 for stage in (schema, f"_{schema}") 

111 ] 

112 

113 

114class Script(Object): 

115 """ 

116 Derive script, which creates one object. 

117 """ 

118 __tablename__ = "script_" 

119 

120 name_: Mapped[str] = mapped_column( 

121 ForeignKey("object_.name_", ondelete="CASCADE"), primary_key=True) 

122 drop_: Mapped[str] = mapped_column() 

123 

124 __mapper_args__ = {"polymorphic_identity": "script"} 

125 

126 @property 

127 def schema(self): 

128 # A function is not in a schema, and a schema is its own name 

129 if "(" in self.name_: 

130 return None 

131 return self.name_.partition(".")[0] 

132 

133 @property 

134 def drops(self): 

135 return [self.drop_] if self.drop_ else [] 

136 

137 

138class TableBase(): 

139 """Base metadata for table.""" 

140 # pylint: disable=too-few-public-methods, too-many-instance-attributes 

141 _row_id = "meta_row_id" 

142 

143 def __init__(self, name, schema, columns, na_values=None, 

144 encoding="utf-8", delimiter=",", checksum=None, 

145 artifact=None): 

146 # pylint: disable=too-many-arguments, too-many-positional-arguments 

147 """ 

148 :param name: Name of the table. 

149 :type name: str 

150 

151 :param schema: Name of the database schema. 

152 :type schema: str 

153 

154 :param columns: Metadata for table columns. 

155 :type columns: list[dict] 

156 

157 :param na_values: Values to consider as NULL. 

158 :type columns: set[str] 

159 

160 :param encoding: encoding of the data file. 

161 :type encoding: str 

162 

163 :param delimiter: Delimiter that separate values in data files. 

164 :type delimiter: str 

165 

166 :param hash: Hash of metadata file. 

167 :type hash: str 

168 

169 :return: Base table metadata. 

170 :rtype: TableBase 

171 """ 

172 self.name = name 

173 self.schema = schema 

174 self._columns = {} 

175 self.encoding = encoding 

176 self.delimiter = delimiter 

177 self.na_values = set(na_values or []) | STR_NA_VALUES 

178 self.checksum = checksum 

179 self.artifact = artifact 

180 

181 # Instantiate metadata object for all columns 

182 for column in columns: 

183 meta = ColumnMeta(**column) 

184 self._columns[meta.name] = meta 

185 

186 @property 

187 def columns(self): 

188 """Metadata for the columns, in order. 

189 

190 :rtype: list[ColumnMeta] 

191 """ 

192 return list(self._columns.values()) 

193 

194 def create(self, session): 

195 """Create statement. 

196 

197 :param session: Database Session. 

198 :type session: datakiln.metadata.database.Session 

199 

200 :return: None 

201 """ 

202 # Create the schema if it does not exists 

203 session.execute(f"CREATE SCHEMA IF NOT EXISTS {self.schema}") 

204 

205 # We replace the table if it already exists 

206 session.execute( 

207 f"DROP TABLE IF EXISTS {self.schema}.{self.name} CASCADE") 

208 

209 # Add columns from unpivot in correct order 

210 columns = self.columns.copy() 

211 if hasattr(self, "unpivot_meta"): 

212 for upv in self.unpivot_meta: 

213 for column in upv.target_columns + upv.target_values: 

214 column = deepcopy(column) 

215 column.required = False 

216 columns.insert(getattr(column, "order", -1), column) 

217 

218 # Prepare function to generate typed column names 

219 dialect = session.bind.dialect.name 

220 typed = partial(ColumnMeta.typed, dialect=dialect) 

221 

222 # Creates table. All columns initially have TEXT type. 

223 # Types are enforced after loading the data one column 

224 # at a time so we can identify the specific column 

225 # in the data that might be violating its type specification. 

226 # The copy command would otherwise issue unhelpful log message. 

227 stmt = dedent(""" 

228 CREATE TABLE {schema}.{name} ( 

229 {columns} 

230 , {row_id} SERIAL 

231 ) 

232 """).format( 

233 name=self.name, schema=self.schema, row_id=self._row_id, 

234 columns='\n , '.join(map(typed, columns)), 

235 ) 

236 session.execute(stmt) 

237 

238 # Create index to speed up validations 

239 session.execute(dedent(f""" 

240 CREATE INDEX ON {self.schema}.{self.name} (meta_row_id) 

241 """)) 

242 

243 

244class StageMeta(TableBase): 

245 """Metadata for table.""" 

246 _readers = { 

247 ".csv": open, 

248 ".csv.gz": partial(gzip.open, mode='rt'), 

249 ".xlsx": open_excel 

250 } 

251 

252 def __init__(self, **kwargs): 

253 r""" 

254 :param \**kwargs: TableBase attributes. 

255 :type \**kwargs: dict 

256 

257 :return: Stage metadata. 

258 :rtype: StageBase 

259 """ 

260 super().__init__(**kwargs) 

261 

262 self.schema = f"_{self.schema}" 

263 

264 # Do not enforce types in stage 

265 for column in self.columns: 

266 column.required = False 

267 column.dtype = dict.fromkeys(column.dtype.keys(), "TEXT") 

268 

269 def create(self, session): 

270 """Create statement. 

271 

272 :param session: Database Session. 

273 :type session: datakiln.metadata.database.Session 

274 

275 :return: None 

276 """ 

277 super().create(session) 

278 

279 # Add column to log staging errors 

280 session.execute(dedent(f""" 

281 ALTER TABLE {self.schema}.{self.name} ADD COLUMN meta_log TEXT 

282 """)) 

283 

284 # Create index to speed up validations 

285 session.execute(dedent(f""" 

286 CREATE INDEX ON {self.schema}.{self.name} (meta_log) 

287 """)) 

288 

289 def load(self, session): 

290 """Stage data. 

291 

292 :param session: Database Session. 

293 :type session: datakiln.metadata.database.Session 

294 

295 :return: None 

296 """ 

297 repo_dir = self.artifact["repo"].root_dir 

298 data_path = Path(repo_dir) / self.artifact["name"] 

299 

300 # Determine whether we need to load 

301 # one or multiple files 

302 if data_path.is_dir(): 

303 files = list(data_path.iterdir()) 

304 else: 

305 files = [data_path] 

306 

307 # Determine file type(s) 

308 for file in files: 

309 suffix = "".join(file.suffixes) 

310 if suffix not in StageMeta._readers: # pylint: disable=protected-access # noqa: E501 

311 continue 

312 data_path = file.absolute() 

313 

314 # Stage data 

315 logger.info("Table '%s'.'%s': Staging file '%s'.", 

316 self.schema[1:], self.name, file.name) 

317 

318 # Get header from file 

319 suffix = "".join(data_path.suffixes) 

320 reader = self._readers[suffix] 

321 buffer = reader(data_path, encoding=self.encoding) 

322 header = next(csv.reader(buffer, delimiter=self.delimiter)) 

323 

324 # To load the data, we need to order 

325 # the column list according to the file 

326 columns = filter(lambda c: not c.transform, self.columns) 

327 columns = sorted(columns, key=lambda c: header.index(c.name)) # pylint: disable=cell-var-from-loop # noqa: E501 

328 

329 # Generate statement 

330 stmt = dedent(""" 

331 COPY {schema}.{name} ( 

332 {columns} 

333 ) 

334 FROM STDIN 

335 WITH (FORMAT CSV, DELIMITER E'{delimiter}') 

336 """).format( 

337 name=self.name, schema=self.schema, 

338 columns='\n , '.join(map(str, columns)), 

339 delimiter=self.delimiter 

340 ) 

341 

342 # Execute and copy from file 

343 cursor = session.connection().connection.cursor() 

344 cursor.copy_expert(stmt, NullStripper(buffer)) 

345 buffer.close() 

346 

347 

348class TableMeta(TableBase): 

349 """Metadata for table.""" 

350 

351 def __init__(self, constraints=None, indexes=None, unpivot=None, 

352 location=None, external=False, **kwargs): 

353 # pylint: disable=too-many-locals 

354 r""" 

355 :param constraints: Metadata for table constraints. 

356 :type constraints: dict 

357 

358 :param unpivot: Metadata for unpivoting table columns. 

359 :type unpivot: list[dict] 

360 

361 :param external: Whether another repo builds the table, which is in 

362 the dependencies folder. 

363 :type external: bool 

364 

365 :param \**kwargs: TableBase attributes. 

366 :type \**kwargs: dict 

367 

368 :return: Table metadata. 

369 :rtype: TableMeta 

370 """ 

371 super().__init__(**kwargs) 

372 

373 self.external = external 

374 self.primary_key = constraints["primary_key"] 

375 self.uniques = constraints.get("uniques", []) 

376 self.indexes = indexes or [] 

377 self.foreign_keys = [] 

378 self.unpivot_meta = [] 

379 self.location = ( 

380 SSHLocation(**location) 

381 if location else None 

382 ) 

383 

384 # Instantiate metadata object for all foreign keys 

385 for fk in constraints.get("foreign_keys", []): 

386 # The types follow the columns of the key, which are compared 

387 # with the columns it references in the same order 

388 dtypes = [] 

389 for name in fk["columns"]: 

390 dtypes.append(self._columns[name].dtype) 

391 fk_meta = ForeignKeyMeta(table=self, dtypes=dtypes, **fk) 

392 self.foreign_keys.append(fk_meta) 

393 

394 # Unpivot 

395 for idx, upv in enumerate(unpivot or []): 

396 upv["alias"] = f"upv_{idx}" 

397 unpivot_meta = UnpivotMeta(**upv) 

398 self.unpivot_meta.append(unpivot_meta) 

399 for t_vals in unpivot_meta.target_values: 

400 for column in t_vals.columns: 

401 self._columns[column.name] = column 

402 

403 @property 

404 def full_name(self): 

405 """Returns the full table name (schema.table), as the database folds 

406 it, since it is created without quotes.""" 

407 return f"{self.schema}.{self.name}".lower() 

408 

409 @property 

410 def requires(self): 

411 """Objects (schema.table) referenced by foreign keys.""" 

412 return { 

413 f"{fk.reference.schema}.{fk.reference.table}".lower() 

414 for fk in self.foreign_keys 

415 } - {self.full_name} 

416 

417 @property 

418 def stage_meta(self): 

419 """Metadata for stage table. 

420 

421 :return: Table metadata. 

422 :rtype: TableMeta 

423 

424 :return: None 

425 """ 

426 stage_meta = StageMeta( 

427 name=self.name, schema=self.schema, 

428 encoding=self.encoding, delimiter=self.delimiter, 

429 columns=list(map(dict, self.columns)), 

430 artifact=self.artifact) 

431 

432 return stage_meta 

433 

434 def create(self, session): 

435 """Create statement. 

436 

437 :param session: Database Session. 

438 :type session: datakiln.metadata.database.Session 

439 

440 :return: None 

441 """ 

442 super().create(session) 

443 

444 # Create stage as well 

445 self.stage_meta.create(session) 

446 

447 def unpivot(self, session): 

448 """Unpivot a table. 

449 

450 :param session: Database Session. 

451 :type session: datakiln.metadata.database.Session 

452 

453 :return: None 

454 """ 

455 if not self.unpivot_meta: 

456 return 

457 

458 # Retrieve database dialect from engine 

459 dialect = session.bind.dialect.name 

460 

461 # Retrieve information from all metadata 

462 drop = [] 

463 columns = [] 

464 aliased = [] 

465 require = [] 

466 laterals = [] 

467 for upv in self.unpivot_meta: 

468 drop.extend(map(str, upv.columns())) 

469 for col in upv.target_columns + upv.target_values: 

470 if col.required: 

471 require.append(col.name) 

472 columns.append(col.name) 

473 aliased.append(f"{upv.alias}.{col.name}::{col.dtype[dialect]}") 

474 laterals.append(upv.lateral()) 

475 

476 # Include columns that are not affected 

477 for column in self.columns: 

478 # Skip columns that we are dropping 

479 if column.name in drop: 

480 continue 

481 columns.append(column.name) 

482 aliased.append(f"upv.{column.name}") 

483 

484 # Insert unpivoted data 

485 stmt = dedent(""" 

486 WITH to_unpivot AS 

487 ( 

488 DELETE FROM {schema}.{name} 

489 RETURNING * 

490 ) 

491 INSERT INTO {schema}.{name} ( 

492 {columns} 

493 , meta_row_id 

494 ) 

495 SELECT 

496 {aliased} 

497 , upv.meta_row_id 

498 FROM to_unpivot AS upv 

499 CROSS JOIN {laterals} 

500 """).format( 

501 schema=self.schema, 

502 name=self.name, 

503 columns="\n , ".join(columns), 

504 aliased="\n , ".join(aliased), 

505 laterals="".join(laterals) 

506 ) 

507 session.execute(stmt) 

508 

509 # Drop unpivoted columns 

510 stmt = dedent(""" 

511 ALTER TABLE {schema}.{name} 

512 DROP COLUMN {columns} 

513 ; 

514 """).format( 

515 schema=self.schema, 

516 name=self.name, 

517 columns="\n, DROP COLUMN ".join(drop) 

518 ) 

519 session.execute(stmt) 

520 

521 # Enforce primary key 

522 self.enforce_pk(session) 

523 

524 # Enforce required target columns 

525 stmt = dedent(""" 

526 ALTER TABLE {schema}.{name} 

527 ALTER COLUMN {columns} SET NOT NULL 

528 ; 

529 """).format( 

530 schema=self.schema, 

531 name=self.name, 

532 columns=' SET NOT NULL\n , ALTER COLUMN '.join(require), 

533 ) 

534 session.execute(stmt) 

535 

536 def resolve_columns(self, session): 

537 """Resolve NULL values and apply description/comment. 

538 

539 :param session: Database Session. 

540 :type session: datakiln.metadata.database.Session 

541 

542 :return: None 

543 """ 

544 logger.info("Table '%s'.'%s': Resolving NULL values.", self.schema, 

545 self.name) 

546 # Enforce constraints 

547 for column in self.columns: 

548 # Resolve NULL values 

549 column.resolve_na(session, self.schema, self.name, self.na_values) 

550 

551 # Add comment to columns 

552 column.comment(session, self.schema, self.name) 

553 

554 def enforce_fks(self, session): 

555 """Enforce foreign key constraints. 

556 

557 :param session: Database Session. 

558 :type session: datakiln.metadata.database.Session 

559 

560 :return: None 

561 """ 

562 logger.info("Table '%s'.'%s': Enforcing foreign keys.", self.schema, 

563 self.name) 

564 # Enforce foreign keys 

565 for fk in self.foreign_keys: 

566 fk.create(session) 

567 

568 def create_indexes(self, conn): 

569 """Create table indexes. 

570 

571 :param conn: SQLAlchemy connection or session. 

572 :type conn: sqlalchemy.Engine 

573 

574 :return: None 

575 """ 

576 for index in self.indexes: 

577 logger.info("Table '%s'.'%s': Creating index %s.", self.schema, 

578 self.name, tuple(index)) 

579 stmt = dedent(""" 

580 CREATE INDEX ON {schema}.{name} ({columns}) 

581 """).format( 

582 schema=self.schema, 

583 name=self.name, 

584 columns=", ".join(index) 

585 ) 

586 conn.execute(stmt) 

587 

588 def create_uniques(self, conn): 

589 """Create table unique indexes. 

590 

591 :param conn: SQLAlchemy connection or session. 

592 :type conn: sqlalchemy.Engine 

593 

594 :return: None 

595 """ 

596 for index in self.uniques: 

597 logger.info("Table '%s'.'%s': Creating unique index %s.", 

598 self.schema, self.name, tuple(index)) 

599 stmt = dedent(""" 

600 CREATE UNIQUE INDEX ON {schema}.{name} ({columns}) 

601 """).format( 

602 schema=self.schema, 

603 name=self.name, 

604 columns=", ".join(index) 

605 ) 

606 conn.execute(stmt) 

607 

608 def enforce_pk(self, conn): 

609 """Enforce primary key. 

610 

611 :param conn: SQLAlchemy connection or session. 

612 :type conn: sqlalchemy.Engine 

613 

614 :return: None 

615 """ 

616 stmt = dedent(""" 

617 ALTER TABLE {schema}.{name} ADD PRIMARY KEY ({pk}) 

618 """).format( 

619 name=self.name, schema=self.schema, 

620 pk=', '.join(self.primary_key) 

621 ) 

622 conn.execute(stmt) 

623 

624 def load(self, session, batch_size=1000000): 

625 # pylint: disable=too-many-locals 

626 """Load data from stage. 

627 

628 :param session: Database Session. 

629 :type session: datakiln.metadata.database.Session 

630 

631 :return: None 

632 """ 

633 # Get database dialect from connection 

634 dialect = session.bind.dialect.name 

635 

636 # Generate JOIN statements for foreign keys 

637 join_fks = [] 

638 null_fks = [] 

639 for idx, fk in enumerate(self.foreign_keys): 

640 join_fks.append(fk.join(f"fk_{idx}", dialect)) 

641 null_fks.append(fk.distinct_reference(f"fk_{idx}", dialect)) 

642 

643 typed = [] 

644 columns = [] 

645 where_req = ["0 = 1"] 

646 for column in self.columns: 

647 # Append to list of columns to be retrieved 

648 columns.append(str(column)) 

649 typed.append(f"{column.rename}::{column.dtype[dialect]}") 

650 

651 # Add required columns to where statement filtering not null values 

652 if not column.required and column.name not in self.primary_key: 

653 continue 

654 where_req.append(f"{self.name}.{column} IS NULL") 

655 

656 # Move rows from staging to final schema 

657 logger.info("Table '%s'.'%s': Loading.", self.schema, self.name) 

658 

659 # Skipped if we are pivoting the table later 

660 if not self.unpivot_meta: 

661 self.enforce_pk(session) 

662 

663 # Flag rows missing required columns 

664 stmt = dedent(""" 

665 UPDATE _{schema}.{name} 

666 SET meta_log = 'Required' 

667 WHERE 1 = 1 

668 AND meta_log IS NULL 

669 AND ( 

670 {where_reqs} 

671 ) 

672 """).format( 

673 name=self.name, schema=self.schema, 

674 where_reqs="\n OR ".join(where_req) 

675 ) 

676 session.execute(stmt, buffer_size=5) 

677 

678 # Flag rows violating a foreign key constraint 

679 stmt = dedent(""" 

680 WITH missing_fk AS ( 

681 SELECT {name}.{row_id} 

682 FROM _{schema}.{name} 

683 {join_fks} 

684 WHERE {n_fks} > 0 

685 AND {name}.meta_log IS NULL 

686 AND ( 

687 0 = 1 

688 {null_fks} 

689 ) 

690 ) 

691 UPDATE _{schema}.{name} 

692 SET meta_log = 'Constraint violation (foreign key)' 

693 FROM missing_fk 

694 WHERE {name}.{row_id} = missing_fk.{row_id} 

695 """).format( 

696 name=self.name, schema=self.schema, row_id=self._row_id, 

697 join_fks="\n ".join(chain(*join_fks)), 

698 n_fks=len(join_fks), 

699 null_fks="\n ".join(chain(*null_fks)), 

700 ) 

701 session.execute(stmt, buffer_size=1) 

702 

703 # Retrieve max id staged for insert 

704 stmt = dedent(""" 

705 SELECT MAX({row_id}) 

706 FROM _{schema}.{name} 

707 WHERE meta_log IS NULL 

708 """).format( 

709 name=self.name, 

710 schema=self.schema, 

711 row_id=self._row_id, 

712 ) 

713 # (there is none when the stage is empty or all its rows are flagged) 

714 max_row_id = session.execute(stmt, buffer_size=1).first()[0] or 0 

715 

716 # Move rows to final schema 

717 for batch_start in range(0, max_row_id, batch_size): 

718 stmt = dedent(""" 

719 INSERT INTO {schema}.{name} ( 

720 {columns} 

721 , {row_id} 

722 ) 

723 SELECT 

724 {typed} 

725 , {row_id} 

726 from _{schema}.{name} 

727 WHERE 1 = 1 

728 AND meta_log IS NULL 

729 AND {row_id} > {batch_start} 

730 AND {row_id} <= {batch_max} 

731 ON CONFLICT DO NOTHING 

732 """).format( 

733 name=self.name, schema=self.schema, row_id=self._row_id, 

734 columns="\n , ".join(columns), 

735 typed="\n , ".join(typed), 

736 batch_start=batch_start, 

737 batch_max=batch_start+batch_size 

738 ) 

739 session.execute(stmt, buffer_size=1) 

740 

741 # Deleted moved rows 

742 stmt = dedent(""" 

743 DELETE from _{schema}.{name} 

744 USING {schema}.{name} moved 

745 WHERE {name}.meta_row_id = moved.meta_row_id 

746 """).format( 

747 name=self.name, schema=self.schema 

748 ) 

749 session.execute(stmt, buffer_size=5) 

750 

751 # Rows that still remain in staging but did not 

752 # get flagged before this point are PK duplicates 

753 stmt = dedent(""" 

754 UPDATE _{schema}.{name} 

755 SET meta_log = 'Duplicate' 

756 WHERE 1 = 1 

757 AND meta_log IS NULL 

758 """).format( 

759 name=self.name, schema=self.schema 

760 ) 

761 session.execute(stmt, buffer_size=5) 

762 

763 # Warn of any remaining staged records 

764 count = session.execute(dedent(f""" 

765 SELECT COUNT(meta_row_id) from _{self.schema}.{self.name} 

766 """)).first()[0] 

767 if count > 0: 

768 logger.warning( 

769 "Table '%s'.'%s': %d rows remain staged.", 

770 self.schema, self.name, count 

771 ) 

772 

773 # Unpivot 

774 self.unpivot(session) 

775 

776 # Local File System location 

777 if self.location: 

778 # Add file location column to table 

779 self.location.add_column(session, table=self) 

780 # Find all files in specified bucket/folder and join them to table 

781 self.location.find(session, table=self) 

782 

783 def create_metadata(self, session): 

784 """ 

785 :param session: Database Session. 

786 :type session: datakiln.metadata.database.Session 

787 """ 

788 # Table metadata 

789 meta = Metadata(name_=self.full_name, checksum_=self.checksum) 

790 session.merge(meta) 

791 

792 # Artifact metadata 

793 repo_dir = Path(self.artifact["repo"].root_dir) 

794 data_path = Path(repo_dir.name) / self.artifact["name"] 

795 artifact = Artifact( 

796 path_=str(data_path), name_=self.full_name, 

797 checksum_=self.artifact["md5"]) 

798 session.merge(artifact) 

799 

800 def pull(self): 

801 """Fetch the data file, if it is not in the folder of the data files. 

802 It is pulled from the default DVC remote, if there is one. Otherwise, 

803 a file that DVC imported from a URL (``dvc import-url``) is downloaded 

804 from there. 

805 

806 :return: None 

807 """ 

808 repo = self.artifact["repo"] 

809 path = Path(repo.root_dir) / self.artifact["name"] 

810 

811 # Nothing to fetch: the file is there, or the folder is not DVC 

812 if isinstance(repo, LocalRepo) or path.exists(): 

813 return 

814 

815 # With a default remote, the file has to come from it 

816 if repo.config.get("core", {}).get("remote"): 

817 repo.pull(targets=[str(path)]) 

818 logger.info("Table '%s'.'%s': Pulled file '%s'.", 

819 self.schema, self.name, self.artifact["name"]) 

820 return 

821 

822 # Otherwise, it was imported: download it if it has not changed 

823 dvc_file = path.with_name(path.name + ".dvc") 

824 stage = repo.stage.load_one(str(dvc_file)) 

825 if stage.deps[0].changed_checksum(): 

826 raise ValueError( 

827 f"The file at the URL of '{self.artifact['name']}' changed " 

828 "since it was imported.") 

829 repo.update(targets=[str(dvc_file)]) 

830 logger.info("Table '%s'.'%s': Downloaded file '%s'.", 

831 self.schema, self.name, self.artifact["name"]) 

832 

833 def record(self, session): 

834 """Record of the table in the metadata, if it was built. 

835 

836 :param session: Database Session. 

837 :type session: datakiln.metadata.database.Session 

838 

839 :rtype: datakiln.metadata.table.Metadata 

840 """ 

841 return session.get(Metadata, self.full_name) 

842 

843 def needs_update(self, session): 

844 """Whether the table was not built, or its metadata or file changed 

845 since it was. 

846 

847 :param session: Database Session. 

848 :type session: datakiln.metadata.database.Session 

849 

850 :rtype: bool 

851 """ 

852 metadata = self.record(session) 

853 

854 return ( 

855 metadata is None or 

856 metadata.checksum_ != self.checksum or 

857 metadata.artifact.checksum_ != self.artifact["md5"] 

858 ) 

859 

860 @classmethod 

861 def order_columns(cls, columns, order): 

862 """Sort table columns in the order presented 

863 in a CSV file to be used in a COPY statement. 

864 

865 :param columns: Columns to be ordered. 

866 :type columns: list[ColumnMeta] 

867 

868 :param order: Column names in the order they are to be returned. 

869 :type order: list[str] 

870 

871 :return: Ordered list of columns. 

872 :rtype: list[ColumnMeta] 

873 """ 

874 order = order or [col.name for col in columns] 

875 unique = set(columns) 

876 

877 # Remove new line and quotes from header 

878 for i, header in enumerate(order): 

879 header = re.sub(r'(\\n|\")', "", header) 

880 order[i] = header 

881 

882 # Remove newline character from last field 

883 if "\n" in order[-1]: 

884 order[-1] = order[-1][:-1] 

885 

886 # Subset to columns present in csv 

887 filtered = filter(lambda x: x.name in order, unique) 

888 

889 # Order columns according to csv 

890 ordered = sorted(filtered, key=lambda c: order.index(c.name)) 

891 return list(ordered) 

892 

893 @classmethod 

894 def from_json(cls, meta_path, data_files=None, external=False): 

895 """Load metadata. 

896 

897 :param meta_path: Path to the metadata 

898 :type meta_path: str 

899 

900 :param data_files: Data files of the tables, unless it is external. 

901 :type data_files: datakiln.metadata.files.DataFiles 

902 

903 :param external: Whether another repo builds the table. 

904 :type external: bool 

905 

906 :return: Table metadata. 

907 :rtype: TableMeta 

908 """ 

909 # Load json 

910 with open(meta_path, "rb") as file: 

911 content = b"" 

912 meta_hash = md5() # noqa: DUO130 

913 for chunk in iter(lambda: file.read(4096), b""): 

914 content += chunk 

915 meta_hash.update(chunk) 

916 meta_json = json.loads(content.decode("utf-8")) 

917 meta_name = meta_path.stem 

918 

919 # Determine path to data file(s), if there are any 

920 artifact = None 

921 if not external: 

922 artifact = data_files.find(meta_name) 

923 

924 return TableMeta( 

925 artifact=artifact, checksum=meta_hash.hexdigest(), 

926 external=external, **meta_json)