Coverage for datakiln/metadata/location.py: 25%

67 statements  

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

1""" 

2File system location metadata. 

3""" 

4import re 

5import logging 

6from pathlib import Path 

7from stat import S_ISDIR 

8from textwrap import dedent 

9 

10from decouple import config 

11from paramiko import SSHClient, AutoAddPolicy 

12 

13from datakiln.metadata.column import ColumnMeta 

14 

15 

16logger = logging.getLogger(__name__) 

17 

18 

19class SSHLocation(ColumnMeta): 

20 # pylint: disable=too-many-instance-attributes 

21 """Column with local file locations.""" 

22 

23 def __init__(self, host, folder, regex, match, 

24 default_values=None, match_map=None, 

25 **kwargs): 

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

27 """ 

28 :param folder: Directory path to search for files. 

29 :type folder: str 

30 

31 :param regex: Regular expression to use in order to 

32 match file names against chosen column. 

33 :type regex: str 

34 

35 :param match: Column to perform match against in table. 

36 :type match: str 

37 

38 :param default_values: Default values for each match. 

39 :type replace: list 

40 

41 :param match_map: Replace values for each match using a dict. 

42 :type replace: list 

43 """ 

44 self.host = host 

45 self.folder = Path(folder) 

46 self.regex = regex 

47 self.match = match 

48 self.default_values = default_values 

49 self.match_map = match_map 

50 

51 super().__init__(**kwargs, dtype={"postgresql": "TEXT[]"}) 

52 

53 def _match(self, string): 

54 """Find matching pattern on string and normalize to default values.""" 

55 match = re.search(self.regex, string) 

56 if match: 

57 extracted = [] 

58 groups = match.groups() 

59 

60 # Iterates over each match to apply default values 

61 for i in range(len(self.match)): 

62 if groups[i] is not None: 

63 extracted.append(groups[i]) 

64 else: 

65 extracted.append(self.default_values[i]) 

66 

67 # Return True if match was found 

68 return extracted 

69 

70 return [''] * len(self.match) 

71 

72 def _glob(self, sftp, path): 

73 """Recursively iterate directory.""" 

74 for entry in sftp.listdir_attr(path.as_posix()): 

75 file_path = path / entry.filename 

76 if S_ISDIR(entry.st_mode): 

77 yield from self._glob(sftp, file_path) 

78 else: 

79 yield file_path 

80 

81 def find(self, conn, table): 

82 """Find file names in directory and store them in a staging table. 

83 

84 :param con: SQLAlchemy connection/session. 

85 :type con: sqlalchemy.Engine 

86 

87 :param table: Table metadata 

88 :type table: TableMeta 

89 

90 :return: None 

91 """ 

92 

93 logger.info("Table '%s'.'%s': Searching local file system.", 

94 table.schema, table.name) 

95 

96 # List to append new rows and counter for number of matches 

97 formatted_rows = [] 

98 n_matches = 0 

99 

100 # Establish SSH connection 

101 ssh = SSHClient() 

102 ssh.set_missing_host_key_policy(AutoAddPolicy()) 

103 ssh.connect( 

104 hostname=self.host, 

105 username=config("SSH_USER"), 

106 password=config("SSH_PASSWORD") 

107 ) 

108 

109 # Iterate through all files in folder and subfolders 

110 sftp = ssh.open_sftp() 

111 for file_path in self._glob(sftp, self.folder): 

112 matches = self._match(file_path.name) 

113 

114 # Count matches if there’s any non-empty text in any of the groups 

115 if "".join(matches).strip(): 

116 n_matches += 1 

117 

118 # Apply transformation mappings from self.match_map 

119 transformed_matches = [] 

120 for i, value in enumerate(matches): 

121 transformed_value = self.match_map[i].get(value, value) 

122 transformed_matches.append(transformed_value) 

123 

124 # Format the row as an SQL-compatible string 

125 row_string = "({values}, {file})".format( # pylint: disable=consider-using-f-string # noqa: E501 

126 values=", ".join(repr(val) for val in transformed_matches), 

127 file=repr(str(file_path)) 

128 ) 

129 formatted_rows.append(row_string) 

130 

131 # Log how many local matches were found 

132 logger.log(logging.INFO if formatted_rows else logging.WARNING, 

133 "Table '%s'.'%s': Found %d local files matches.", 

134 table.schema, table.name, n_matches) 

135 

136 # Create table on Stage 

137 self.create_table(conn, formatted_rows, table) 

138 

139 # Join tables based on matched columns only 

140 self.insert_file_locations(conn, self.match, table) 

141 

142 def create_table(self, conn, rows, table): 

143 """Create LFS Location table. 

144 

145 :param conn: SQLAlchemy connection/session. 

146 :type conn: sqlalchemy.Engine 

147 

148 :param rows: List of formatted rows to be inserted as values. 

149 :type rows: list 

150 

151 :param table: Table metadata 

152 :type table: TableMeta 

153 

154 :return: None 

155 """ 

156 # Table stage name 

157 stg_name = f"_{table.schema}.{table.name}_local_fs" 

158 

159 # Drop table statement 

160 conn.execute(f"DROP TABLE IF EXISTS {stg_name}") 

161 

162 # Create statement 

163 stmt = dedent(""" 

164 CREATE TABLE {stg_name} AS 

165 SELECT 

166 {columns} 

167 , ARRAY_AGG({name}) AS {name} 

168 FROM ( VALUES 

169 {values} 

170 ) t ({columns}, {name}) 

171 GROUP BY {columns} 

172 """).format( 

173 stg_name=stg_name, 

174 columns="\n , ".join(self.match), 

175 values=',\n '.join(rows), 

176 name=self.name 

177 ) 

178 conn.execute(stmt) 

179 

180 def add_column(self, conn, table): 

181 """Add local location column to table. 

182 

183 :param conn: SQLAlchemy connection/session. 

184 :type conn: sqlalchemy.Engine 

185 

186 :param table: Table metadata 

187 :type table: TableMeta 

188 

189 :return: None 

190 """ 

191 stmt = dedent(""" 

192 ALTER TABLE {schema}.{name} 

193 ADD COLUMN {column} TEXT[] 

194 {notnull} 

195 """).format( 

196 schema=table.schema, 

197 name=table.name, 

198 column=self.name, 

199 notnull='NOT NULL' if self.required else '' 

200 ) 

201 conn.execute(stmt) 

202 

203 def insert_file_locations(self, conn, match_columns, table): 

204 """Add staged local file system column to loaded table. 

205 

206 :return: str 

207 """ 

208 stmt = dedent(""" 

209 UPDATE {schema}.{table_name} ot 

210 SET {name} = fst.{name} 

211 FROM _{schema}.{stg_name} fst 

212 WHERE {cols_match} 

213 """).format( 

214 schema=table.schema, 

215 table_name=table.name, 

216 name=self.name, 

217 stg_name=f'{table.name}_local_fs', 

218 cols_match=" AND ".join( 

219 f"ot.{col} = fst.{col}" 

220 for col in match_columns 

221 ) 

222 ) 

223 conn.execute(stmt)