Coverage for datakiln/metadata/unpivot.py: 22%
64 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"""
2Unpivot metadata.
3"""
4import re
5from textwrap import dedent
6from collections import defaultdict
8from datakiln.metadata.column import ColumnMeta
11class UnpivotColumnMeta(ColumnMeta):
12 """Metadata for columns that are unpivoting targets."""
13 def __init__(self, unpivot, regex=None, order=1000, **kwargs):
14 r"""
15 :param unpivot: Metadata of this column's unpivot.
16 :type unpivot: UnpivotMeta
18 :param regex: Pattern for target column.
19 :type regex: str
21 :param order: Position of the column in the target table.
22 :type order: int
24 :param \**kwargs: `ColumnMeta` attributes.
25 :type \**kwargs: dict
27 :return: Unpivot column metadata.
28 :rtype: UnpivotColumnMeta
29 """
30 self.unpivot = unpivot
31 self.regex = regex
32 self.order = order
34 super().__init__(**kwargs)
36 def value(self, column):
37 """Value for the column to be unpivoted.
39 :param column: Name of the column to be unpivoted.
40 :type column: dict
42 :return: Unpivot value.
43 :rtype: str
44 """
45 match = re.search(self.regex, column)
46 if self.regex and not match:
47 return "NULL"
48 return f"'{''.join(match.groups())}'"
51class UnpivotValueMeta(ColumnMeta):
52 """Metadata for columns that will store unpivoted values."""
53 def __init__(self, unpivot, columns, **kwargs):
54 r"""
55 :param unpivot: Metadata of this column's unpivot.
56 :type unpivot: UnpivotMeta
58 :param columns: Columns storing the values.
59 :type columns: list[str]
61 :return: Column metadata.
62 :rtype: ColumnMeta
63 """
64 self.unpivot = unpivot
65 super().__init__(**kwargs)
67 self.columns = []
68 for column in columns:
69 column_meta = ColumnMeta(
70 dtype={
71 "postgresql": "TEXT"
72 },
73 name=column
74 )
75 self.columns.append(column_meta)
78class UnpivotMeta():
79 """Metadata for unpivoting columns."""
80 def __init__(self, alias, target_columns, target_values, **kwargs):
81 r"""
82 :param alias: Alias for the LATERAL join.
83 :type alias: str
85 :param target_columns: Metadata for target columns.
86 :type target_columns: list[str]
88 :param target_values: Metadata for target value column.
89 :type target_values: dict
91 :param \**kwargs: `UnpivotColumnMeta` attributes.
92 :type \**kwargs: dict
94 :return: Unpivot metadata.
95 :rtype: UnpivotMeta
96 """
97 self.alias = alias
99 # Target columns
100 self.target_columns = []
101 for target in target_columns:
102 target_meta = UnpivotColumnMeta(self, **target, **kwargs)
103 self.target_columns.append(target_meta)
105 # Target value
106 self.target_values = []
107 for value in target_values:
108 value_meta = UnpivotValueMeta(self, **value, **kwargs)
109 self.target_values.append(value_meta)
111 def columns(self):
112 """Unpivot columns.
114 :return: Pivot columns.
115 :rtype: list[ColumnMeta]
116 """
117 # List original columns
118 columns = []
119 for value in self.target_values:
120 columns.extend(value.columns)
121 return columns
123 def values(self):
124 """SQL with VALUES to use in LATERAL JOIN.
126 :return: Values.
127 :rtype: list[str]
128 """
129 # Generate expressions
130 exp_dict = defaultdict(list)
131 for idx, value in enumerate(self.target_values):
132 value_columns = []
133 # Retrieve name for columns being pivoted
134 for pivot in value.columns:
135 value_columns.append(pivot.name)
137 values = []
138 # Extract values from columns
139 for target in self.target_columns:
140 values.append(list(map(target.value, value_columns)))
142 # Add columns to list for each value found
143 expression = dict(zip(zip(*values), value_columns))
144 keys = exp_dict.keys() | expression.keys()
145 for key in keys:
146 value = expression.get(key, 'NULL')
147 # Prepend NULL if value not seen before
148 if idx and key not in exp_dict:
149 count = len(list(exp_dict.items())[0]) + 1
150 exp_dict[key].extend(['NULL'] * count)
151 exp_dict[key].append(value)
153 # Parse unpivoting columns to string
154 expressions = []
155 for vals, v_cols in exp_dict.items():
156 expression = list(vals) + v_cols
157 expression = f"({', '.join(expression)})"
158 expressions.append(expression)
159 return expressions
161 def lateral(self):
162 """Returns the LATERAL JOIN to unpivot a table.
164 :return: LATERAL JOIN expression.
165 :rtype: list[str]
166 """
167 return dedent("""
168 LATERAL ( VALUES
169 {values}
170 ) AS {alias} (
171 {columns}
172 )
173 """).format(
174 values="\n , ".join(self.values()),
175 alias=self.alias,
176 columns="\n , ".join(map(
177 str, self.target_columns + self.target_values
178 ))
179 )[1:]