Перед системным администратором, поддерживающим систему QlikView, периодически может вставать следующая задача: требуется выполнить сравнение двух идентичных по структуре QVD-файлов и выявить различающиеся между ними строки данных. В этой статье рассмотрим пример подобных задач, а затем рассмотрим универсальные алгоритм их реализации.
Сравнение двух QVD: Примеры
Примеры подобных ситуаций:
- Сегодня система-источник вернула заметно изменившиеся по сравнению со вчерашним днем данные, и вам нужно понять, в каких масштабах произошли изменения, чтобы расспросить о них администратора той системы.
Для этого вы хотите сравнить сегодняшнюю выгрузку в QVD с ее вчерашней архивной копией, найти изменившиеся строки данных и наглядно представить их на экране.
- Вы вносите небольшие изменения в алгоритмы трансформаций и хотите проконтролировать, что в QVD на выходе получится именно то, что вы ожидаете. Для этого вы хотите создать новый QVD в «песочнице» и сравнить его с аналогичным QVD в продуктиве, найти отличающиеся строки данных и тщательно проверить, что изменения соответствуют ожиданиям.
Алгоритм решения задачи
Я сам несколько раз сталкивался с такой задачей на проекте и решил сделать достаточно универсальный алгоритм для ее решения. Результатом стало приложение QlikView, которое делает следующее:
- Загружает данные из двух указанных QVD и объединяет их (т.е. выполняет Join).
- Добавляет к структуре данных флаги, указывающие на статус «одинаковости» строк.
- С помощью простого пользовательского интерфейса визуализирует строки данных и позволяет выполнять навигацию/фильтрацию по одинаковым/неодинаковым строкам.
- И как дополнение — позволяет выгрузить в отдельные QVD различающиеся строки для их более детального анализа, в случае когда присутствует неуникальность строк в QVD.
Вот основной код скрипта с комментариями, который решает задачи 1 и 2:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 |
/* пути сравниваемых QVD-файлов */ LET vFilePath_Qvd01 = '\\dm-qpub\PublisherDisk\Backup.QLIKVIEW_SAP\20160126\EXTRACT\QVD\TRANSACTIONALDATA\ZIC_V02\ZIC_V02_201501.QVD'; LET vFilePath_Qvd02 = '\\dm-qpub\PublisherDisk\QLIKVIEW_SAP\EXTRACT\QVD\TRANSACTIONALDATA\ZIC_V02\ZIC_V02_201501.QVD'; /* включение режима NULLASVALUE */ SET NullValue = 'NULL'; NULLASVALUE * ; /* первый QVD и присоединение флага */ [Qvd01]: NoConcatenate /* DISTINCT - обязателен, чтобы в случае наличия двух одинаковых строк - не было задвоений при дальнейшем объединении */ LOAD Distinct * From [$(vFilePath_Qvd01)] (qvd) Where /* отключение QVD Optimized загрузки для корректной работы NULLASVALUE */ ( 1 = 1 ) ; Left Join LOAD 1 as [_Qvd01] AutoGenerate (1) ; /* второй QVD и присоединение флага */ [Qvd02]: NoConcatenate /* DISTINCT - обязателен, чтобы в случае наличия двух одинаковых строк - не было задвоений при дальнейшем объединении */ LOAD Distinct * From [$(vFilePath_Qvd02)] (qvd) Where /* отключение QVD Optimized загрузки для корректной работы NULLASVALUE */ ( 1 = 1 ) ; Left Join LOAD 1 as [_Qvd02] AutoGenerate (1) ; /* выключение NULLASVALUE */ NULLASNULL; /* объединение двух таблиц */ Join ([Qvd01]) LOAD * Resident [Qvd02] ; /* обработка флагов */ [Merged Table]: NoConcatenate LOAD *, Alt( [_Qvd01], 0 ) as [tmp._Qvd01], Alt( [_Qvd02], 0 ) as [tmp._Qvd02] Resident [Qvd01] ; DROP Tables [Qvd01], [Qvd02] ; DROP Fields [_Qvd01], [_Qvd02] ; RENAME Fields [tmp._Qvd01] to [_Qvd01], [tmp._Qvd02] to [_Qvd02] ; |
НА ЗАМЕТКУ!
Что интересного можно отметить:
- Загрузка данных из сверяемых QVD работает строго с применением квалификатора Distinct. В противном случае можно получить бессмысленное декартово произведение. Если же неуникальность строк в QVD — это данность, см. далее.
- Активно используются методики Null Values Handling из арсенала Qlik.
- Выполняется замена значений Null на текстовые строки ‘NULL’ с помощью команды NULLASVALUE. Это необходимо для корректной работы Join, который выполняет объединение только по реальным значениям в полях. Принудительно выключается режим QVD Optimized Load, чтобы NULLASVALUE корректно работала.
- После загрузки и объединения данных, значения Null заменяются на 0 с помощью удобной функции Alt().
- Сверяемые QVD файлы должны быть строго идентичны по структуре (одинаковый состав полей), иначе осмысленное сравнение будет невозможно.
Скрипт создает простую модель данных из одной таблицы со структурой из QVD и двумя добавленными флагами _Qvd01 и _Qvd02, в которых у каждой строки стоит 1, если она представлена в 1-м/2-м QVD, и 0 – если нет.
У одинаковых строк 1 будет стоять в обоих полях, а у неодинаковых – 0 в одном из полей. Применяя фильтры на эти поля, можно соответственно выбрать три множества строк данных:
- Одинаковые (1 и 1).
- Есть в первом QVD, но нет во втором (1 и 0).
- Есть во втором QVD, но нет в первом (0 и 1).
А вот как визуализация выглядит в интерфейсе пользователя:
Видно, что среди 7 млн. строк первого QVD нашлось 6,95 млн. аналогичных второму QVD и 50 тыс. отсутствующих во втором.
Чтобы найти конкретный пример отличий, применим фильтр 1 в первом флаге, и 0 во втором; выберем первые по счету значения ключей измерений в таблице и снимем фильтры с флагов.
Получили пример строки данных, которая различается между QVD и сразу видно, в чем именно:
Ну а для того, чтобы выгрузить различающиеся строки в отдельные QVD для их более детального анализа/агрегации в отдельном приложении QV, можно дополнить скрипт следующим опциональным кодом. Обратите внимание, что если в ваших данных присутствует неуникальность, то это способ ее обойти и выгрузить все нужные данные без обрезок.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 |
/* Код, размещенный далее, является опциональным и требуется в случаях, когда строки в таблице фактов неуникальны. Если неуникальность присутствует, то в рамках данной модели оценить количественные разницы было/стало НЕ получится. Чтобы обойти эту проблему, следующий код позволяет выгрузить различающиеся строки отдельно в QVD БЕЗ устранения дубликатов Distinct-ом. */ /* вычисление переменной со списком полей QVD */ LET vFilePath_QVD = vFilePath_Qvd01; /* определение количества полей в QVD файле */ LET v_QVDFile_NoOfFields = QvdNoOfFields('$(vFilePath_QVD)'); LET vStr_QVDFields = ; /* для каждого из полей QVD файла */ FOR vIx = 1 to v_QVDFile_NoOfFields LET v_QVDFile_Field = QvdFieldName('$(vFilePath_QVD)', vIx); LET vStr_QVDFields = vStr_QVDFields & ',' & '[' & v_QVDFile_Field & ']' ; NEXT /* обрезка первой запятой */ LET vStr_QVDFields = Mid( vStr_QVDFields, 2, Len( vStr_QVDFields ) - 1 ); TRACE vStr_QVDFields=$(vStr_QVDFields); LET vFilePath_QVD = ; LET v_QVDFile_NoOfFields = ; LET vIx = ; LET v_QVDFile_Field = ; [RowHashes._Qvd01 = 1 | _Qvd02 = 0]: NoConcatenate LOAD /* определение хеш-суммы для строки, параметр - перечень ВСЕХ полей источника */ Hash256( $(vStr_QVDFields) ) as [RowHash._Qvd01 = 1 | _Qvd02 = 0] Resident [Merged Table] Where Match( [_Qvd01], 1 ) and Match( [_Qvd02], 0 ) ; /* включение режима NULLASVALUE */ SET NullValue = 'NULL'; NULLASVALUE * ; [tmp.QVD]: NoConcatenate LOAD * From [$(vFilePath_Qvd01)] (qvd) Where /* отключение QVD Optimized загрузки для корректной работы NULLASVALUE */ ( 1 = 1 ) ; /* выключение NULLASVALUE */ NULLASNULL; [Qvd01.Rows _Qvd02 = 0]: NoConcatenate LOAD * Resident [tmp.QVD] Where /* все строки, хэша которых нет в Qvd02 */ Exists( [RowHash._Qvd01 = 1 | _Qvd02 = 0] , Hash256( $(vStr_QVDFields) ) ) ; DROP Table [tmp.QVD] ; STORE [Qvd01.Rows _Qvd02 = 0] into [Qvd01.Rows _Qvd02-0.csv] (txt, delimiter is ',') ; DROP Tables [RowHashes._Qvd01 = 1 | _Qvd02 = 0], [Qvd01.Rows _Qvd02 = 0] ; [RowHashes._Qvd01 = 0 | _Qvd02 = 1]: NoConcatenate LOAD /* определение хеш-суммы для строки, параметр - перечень ВСЕХ полей источника */ Hash256( $(vStr_QVDFields) ) as [RowHash._Qvd01 = 0 | _Qvd02 = 1] Resident [Merged Table] Where Match( [_Qvd01], 0 ) and Match( [_Qvd02], 1 ) ; /* включение режима NULLASVALUE */ SET NullValue = 'NULL'; NULLASVALUE * ; [tmp.QVD]: NoConcatenate LOAD * From [$(vFilePath_Qvd02)] (qvd) Where /* отключение QVD Optimized загрузки для корректной работы NULLASVALUE */ ( 1 = 1 ) ; /* выключение NULLASVALUE */ NULLASNULL; [Qvd02.Rows _Qvd01 = 0]: NoConcatenate LOAD * Resident [tmp.QVD] Where /* все строки, хэша которых нет в Qvd02 */ Exists( [RowHash._Qvd01 = 0 | _Qvd02 = 1] , Hash256( $(vStr_QVDFields) ) ) ; DROP Table [tmp.QVD] ; STORE [Qvd02.Rows _Qvd01 = 0] into [Qvd02.Rows _Qvd01-0.csv] (txt, delimiter is ',') ; DROP Tables [RowHashes._Qvd01 = 0 | _Qvd02 = 1], [Qvd02.Rows _Qvd01 = 0] ; |
Друзья, вот, мы и решили поставленную задачу сравнения QVD-файлов и нашли разные строки данных.
Какие у вас есть идеи использования данного алгоритма? Для каких задач он еще будет актуален?
Буду рад прочитать ваши комментарии.
До новых встреч!
Василий Петренко
B3
Максим
Stepan
Irina
Свежие комментарии