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

1""" 

2Unpivot metadata. 

3""" 

4import re 

5from textwrap import dedent 

6from collections import defaultdict 

7 

8from datakiln.metadata.column import ColumnMeta 

9 

10 

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 

17 

18 :param regex: Pattern for target column. 

19 :type regex: str 

20 

21 :param order: Position of the column in the target table. 

22 :type order: int 

23 

24 :param \**kwargs: `ColumnMeta` attributes. 

25 :type \**kwargs: dict 

26 

27 :return: Unpivot column metadata. 

28 :rtype: UnpivotColumnMeta 

29 """ 

30 self.unpivot = unpivot 

31 self.regex = regex 

32 self.order = order 

33 

34 super().__init__(**kwargs) 

35 

36 def value(self, column): 

37 """Value for the column to be unpivoted. 

38 

39 :param column: Name of the column to be unpivoted. 

40 :type column: dict 

41 

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())}'" 

49 

50 

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 

57 

58 :param columns: Columns storing the values. 

59 :type columns: list[str] 

60 

61 :return: Column metadata. 

62 :rtype: ColumnMeta 

63 """ 

64 self.unpivot = unpivot 

65 super().__init__(**kwargs) 

66 

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) 

76 

77 

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 

84 

85 :param target_columns: Metadata for target columns. 

86 :type target_columns: list[str] 

87 

88 :param target_values: Metadata for target value column. 

89 :type target_values: dict 

90 

91 :param \**kwargs: `UnpivotColumnMeta` attributes. 

92 :type \**kwargs: dict 

93 

94 :return: Unpivot metadata. 

95 :rtype: UnpivotMeta 

96 """ 

97 self.alias = alias 

98 

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) 

104 

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) 

110 

111 def columns(self): 

112 """Unpivot columns. 

113 

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 

122 

123 def values(self): 

124 """SQL with VALUES to use in LATERAL JOIN. 

125 

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) 

136 

137 values = [] 

138 # Extract values from columns 

139 for target in self.target_columns: 

140 values.append(list(map(target.value, value_columns))) 

141 

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) 

152 

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 

160 

161 def lateral(self): 

162 """Returns the LATERAL JOIN to unpivot a table. 

163 

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:]