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
« 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
10from decouple import config
11from paramiko import SSHClient, AutoAddPolicy
13from datakiln.metadata.column import ColumnMeta
16logger = logging.getLogger(__name__)
19class SSHLocation(ColumnMeta):
20 # pylint: disable=too-many-instance-attributes
21 """Column with local file locations."""
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
31 :param regex: Regular expression to use in order to
32 match file names against chosen column.
33 :type regex: str
35 :param match: Column to perform match against in table.
36 :type match: str
38 :param default_values: Default values for each match.
39 :type replace: list
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
51 super().__init__(**kwargs, dtype={"postgresql": "TEXT[]"})
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()
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])
67 # Return True if match was found
68 return extracted
70 return [''] * len(self.match)
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
81 def find(self, conn, table):
82 """Find file names in directory and store them in a staging table.
84 :param con: SQLAlchemy connection/session.
85 :type con: sqlalchemy.Engine
87 :param table: Table metadata
88 :type table: TableMeta
90 :return: None
91 """
93 logger.info("Table '%s'.'%s': Searching local file system.",
94 table.schema, table.name)
96 # List to append new rows and counter for number of matches
97 formatted_rows = []
98 n_matches = 0
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 )
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)
114 # Count matches if there’s any non-empty text in any of the groups
115 if "".join(matches).strip():
116 n_matches += 1
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)
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)
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)
136 # Create table on Stage
137 self.create_table(conn, formatted_rows, table)
139 # Join tables based on matched columns only
140 self.insert_file_locations(conn, self.match, table)
142 def create_table(self, conn, rows, table):
143 """Create LFS Location table.
145 :param conn: SQLAlchemy connection/session.
146 :type conn: sqlalchemy.Engine
148 :param rows: List of formatted rows to be inserted as values.
149 :type rows: list
151 :param table: Table metadata
152 :type table: TableMeta
154 :return: None
155 """
156 # Table stage name
157 stg_name = f"_{table.schema}.{table.name}_local_fs"
159 # Drop table statement
160 conn.execute(f"DROP TABLE IF EXISTS {stg_name}")
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)
180 def add_column(self, conn, table):
181 """Add local location column to table.
183 :param conn: SQLAlchemy connection/session.
184 :type conn: sqlalchemy.Engine
186 :param table: Table metadata
187 :type table: TableMeta
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)
203 def insert_file_locations(self, conn, match_columns, table):
204 """Add staged local file system column to loaded table.
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)