Coverage for datakiln/extraction/exporters.py: 0%

89 statements  

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

1""" 

2Data and metadata export logic for the extraction pipeline. 

3""" 

4import csv 

5import json 

6import gzip 

7import logging 

8import hashlib 

9import threading 

10from pathlib import Path 

11from typing import List, Dict, Optional 

12 

13from tenacity import ( 

14 retry, 

15 stop_after_attempt, 

16 wait_fixed, 

17 retry_if_exception_type, 

18 before_sleep_log 

19) 

20from sqlalchemy import MetaData, Table, Column, Select 

21from sqlalchemy.dialects import registry 

22from sqlalchemy.exc import SQLAlchemyError 

23 

24from datakiln.metadata.database import Session 

25 

26 

27logger = logging.getLogger(__name__) 

28db_retry = retry( 

29 stop=stop_after_attempt(3), 

30 wait=wait_fixed(10), 

31 retry=retry_if_exception_type((SQLAlchemyError, OSError)), 

32 before_sleep=before_sleep_log(logger, logging.WARNING) 

33) 

34 

35 

36class MetadataExtractor: 

37 """Class to handle metadata extraction and generation.""" 

38 

39 @staticmethod 

40 def get_metadata( 

41 session: Session, 

42 schema: str, 

43 tables: List[str] 

44 ) -> MetaData: 

45 """Generate column metadata from DBMS.""" 

46 logger.info("Schema '%s': Retrieving metadata.", schema) 

47 metadata = MetaData(schema=schema) 

48 metadata.reflect(bind=session.bind, only=tables, views=True) 

49 return metadata 

50 

51 @staticmethod 

52 def generate_column_meta( 

53 column: Column, 

54 dialect: str = None, 

55 compile_type: bool = True 

56 ) -> Dict: 

57 """Generate column metadata from DBMS.""" 

58 dialect_obj = registry.load(dialect) 

59 type_ = column.type.dialect_impl(dialect_obj()) 

60 return { 

61 "name": column.name, 

62 "dtype": { 

63 dialect: type_.compile() if compile_type else type_ 

64 } 

65 } 

66 

67 @staticmethod 

68 def generate_table_meta( 

69 meta_dir: Path, 

70 table: Table, 

71 dialect: str = None 

72 ) -> Dict: 

73 """Generate table metadata from DBMS.""" 

74 meta = { 

75 "name": table.name, 

76 "schema": "public", 

77 "encoding": "utf-8", 

78 "columns": [ 

79 MetadataExtractor.generate_column_meta(column, dialect) 

80 for column in table.columns 

81 ], 

82 "constraints": { 

83 "primary_key": [ 

84 column.name 

85 for column in table.primary_key 

86 ] 

87 } 

88 } 

89 meta_path = meta_dir / f"{table.name}.json" 

90 with open(meta_path, "w", encoding="utf-8") as file: 

91 json.dump(meta, file, indent=4) 

92 

93 

94class HashingFileWriter: 

95 """A file-like writer that calculates the MD5 hash on-the-fly.""" 

96 

97 def __init__(self, fp): 

98 """Initialize the hashing file writer.""" 

99 self.fp = fp 

100 self.hasher = hashlib.md5() # noqa: DUO130 

101 

102 def write(self, b): 

103 """Write bytes to the file and update the hash.""" 

104 self.fp.write(b) 

105 self.hasher.update(b) 

106 return len(b) 

107 

108 def flush(self): 

109 """Flush the file buffer.""" 

110 self.fp.flush() 

111 

112 def close(self): 

113 """Close the file.""" 

114 self.fp.close() 

115 

116 

117class DataExporter: 

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

119 """Class to handle data export logic.""" 

120 

121 @staticmethod 

122 @db_retry 

123 def stream_to_csv( 

124 session: Session, 

125 query: Select, 

126 export_path: Path, 

127 table_name: str = "unknown", 

128 insert_to: Optional[Table] = None, 

129 chunk_size: int = 10000, 

130 abort: Optional[threading.Event] = None 

131 ) -> str: 

132 # pylint: disable=too-many-locals, too-many-arguments 

133 # pylint: disable=too-many-positional-arguments 

134 """Streams a single SQL query into a compressed CSV. 

135 

136 Returns the MD5 checksum of the generated compressed CSV file. 

137 """ 

138 with open(export_path, 'wb') as f_raw: 

139 hash_writer = HashingFileWriter(f_raw) 

140 with gzip.open( 

141 hash_writer, 

142 mode='wt', 

143 encoding='utf-8', 

144 newline='' 

145 ) as o_file: 

146 logger.info("File '%s': Streaming data.", export_path.name) 

147 writer = csv.writer(o_file) 

148 count = 0 

149 idx = 0 

150 

151 result = session.execute( 

152 query, 

153 execution_options={ 

154 "stream_results": True, 

155 "skip_formatting": True 

156 } 

157 ) 

158 

159 # Write CSV header 

160 header = list(result.keys()) 

161 try: 

162 idx = header.index("meta_row_id") 

163 header.pop(idx) 

164 except ValueError: 

165 idx = len(header) 

166 writer.writerow(header) 

167 

168 # Write data in chunks 

169 chunk_idx = 0 

170 engine = session.get_bind() 

171 for partition in result.partitions(chunk_size): 

172 if abort and abort.is_set(): 

173 logger.warning( 

174 "Table '%s': Pipeline aborted, stopping " 

175 "extraction.", table_name 

176 ) 

177 break 

178 

179 chunk_idx += 1 

180 batch_inserts = [] 

181 for row in partition: 

182 # Remove meta_row_id if present 

183 data = row[:idx] + row[idx + 1:] 

184 

185 # Write row to CSV file 

186 writer.writerow(data) 

187 count += 1 

188 

189 # Convert row to dictionary for insertion 

190 if insert_to is not None: 

191 batch = dict(zip(result.keys(), row)) 

192 batch_inserts.append(batch) 

193 

194 # Insert batch into table using a separate connection 

195 if batch_inserts: 

196 @db_retry 

197 def _insert_batch(batch): 

198 with engine.begin() as conn: 

199 stmt = insert_to.insert().execution_options( 

200 skip_formatting=True 

201 ) 

202 conn.execute(stmt, batch) 

203 _insert_batch(batch_inserts) 

204 

205 logger.info( 

206 "Table '%s': Processed chunk %d, %d rows so far.", 

207 table_name, chunk_idx, count 

208 ) 

209 

210 logger.info( 

211 "Table '%s': Extracted %d rows.", table_name, count) 

212 

213 return hash_writer.hasher.hexdigest()