QVD-файлы — что внутри
QlikView и QlikSense — замечательные BI инструменты, достаточно популярные у нас в стране и "за рубежом". Очень часто эти системы сохраняют "промежуточные" результаты своей работы — данные, которые визуализируют их "дашборды" — в так называемые "QVD файлы". Часто QVD файлы используются в качестве основного хранилища в многоэтапных ETL процессах, построенных на базе Qlik. И тогда возникает вопрос — можно ли и как воспользоваться этими данными без QlikView/QlikSense? Или другой — а что там и правильно ли "оно" посчиталось?
QVD — это формат файла, оптимизированный для работы QlikView/QlikSense (чтение и запись информации этими приложениями в файлы такого формата происходит существенно быстрее, чем в файлы любого другого формата). Структура этого файла недокументирована и покрыта "мраком проприетарности", практически не существует приложений, которые способны работать такими файлами (читать и тем более писать). В этой мы узнаем, как устроен QVD, как напрямую и быстро его читать и в него писать.
Преследуемая цель - необходимо было прочитать содержимое QVD файла, т.е. воссоздать на его основе реляционную таблицу. И наоборот — выгрузить данные реляционной таблицы в QVD так, чтобы QlikView смог ее корректно загрузить.
Структура файла
QVD файл создается скриптом QlikView/QlikSense в процессе загрузки данных в память приложения (результат работы команды STORE) и соответствует одной (реляционной) таблице QlikView/QlikSense. Он состоит из двух частей
- текстовой (метаданные) и
- бинарной (колонки и строки)
Метаданные представлены в виде XML (пример будет приведен ниже), бинарная часть начинается непосредственно после текстовой и состоит из двух блоков
- уникальные значения всех колонок (исходной таблицы)
- строки (исходной таблицы), ссылающиеся на уникальные значения колонок
Таким образом для таблицы из N колонок файл будет содержать N + 1 бинарный блок. Все части файла "плотно склеены" и идут друг за другом без каких-бы то ни было заполнителей и "хвостовиков".
Метаданные (XML)
QVD файл содержит достаточно много метаданных — "данных о данных". Он практически самодостаточен, вот краткий перечень того, что есть в метаданных (более подробно ниже):
- версия ПО, породившего файл
- дата и время создания файла
- файл QlikView/QlikSense, работа скрипта которого привела к созданию файла
- исходный код скрипта, породивший QVD файл
- имя таблицы
- информация о колонках (имена, типы, количества уникальных значений)
- количество строк
Метаданные хранятся в файле в текстовом виде и их можно увидеть в любой программе, которая может показать файл в текстовом виде (ну, почти в любой… в такой, которая не боится файлов больших размеров). Лично я смотрю метаинформацию при помощи more — достаточно удобно.
В дальнейшем изложении я буду использовать тестовую таблицу (использую синтаксис QlikView, но думаю, несложно будет домыслить):
SET NULLINTERPRET =<sym>;
tab1:
LOAD * INLINE [
ID, NAME
123.12,"Pete"
124,12/31/2018
-2,"Vasya"
1,"John"
<sym>,"None"
];
В качестве примера метаданные для этой таблички
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<QvdTableHeader>
<QvBuildNo>7314</QvBuildNo>
<CreatorDoc></CreatorDoc>
<CreateUtcTime>2019-04-03 06:24:33</CreateUtcTime>
<SourceCreateUtcTime></SourceCreateUtcTime>
<SourceFileUtcTime></SourceFileUtcTime>
<SourceFileSize>-1</SourceFileSize>
<StaleUtcTime></StaleUtcTime>
<TableName>tab1</TableName>
<Fields>
<QvdFieldHeader>
<FieldName>ID</FieldName>
<BitOffset>0</BitOffset>
<BitWidth>3</BitWidth>
<Bias>-2</Bias>
<NumberFormat>
<Type>0</Type>
<nDec>0</nDec>
<UseThou>0</UseThou>
<Fmt></Fmt>
<Dec></Dec>
<Thou></Thou>
</NumberFormat>
<NoOfSymbols>4</NoOfSymbols>
<Offset>0</Offset>
<Length>40</Length>
</QvdFieldHeader>
<QvdFieldHeader>
<FieldName>NAME</FieldName>
<BitOffset>3</BitOffset>
<BitWidth>5</BitWidth>
<Bias>0</Bias>
<NumberFormat>
<Type>0</Type>
<nDec>0</nDec>
<UseThou>0</UseThou>
<Fmt></Fmt>
<Dec></Dec>
<Thou></Thou>
</NumberFormat>
<NoOfSymbols>5</NoOfSymbols>
<Offset>40</Offset>
<Length>37</Length>
</QvdFieldHeader>
</Fields>
<Compression></Compression>
<RecordByteSize>1</RecordByteSize>
<NoOfRecords>5</NoOfRecords>
<Offset>77</Offset>
<Length>5</Length>
</QvdTableHeader>
Опыт работы с QVD показывает, что структура XML не меняется от файла к файлу.
Общая информация
QvBuildNo
Номер билда того приложения QlikView/QlikSense, которое породило QVD файл.
CreatorDoc
Как правило содержит имя того QVW файла, скрипт которого породил QVD файл. В данном примере не заполнено, возможно, потому что использовался Personal Edition.
CreateUtcTime
Время создания QVD файла.
SourceCreateUtcTime, SourceFileUtcTime, SourceFileSize, StaleUtcTime
Не встречаются файлы, в которых эти поля были бы заполнены, но может быть, каких-то настроек не хватает?
TableName
Имя таблицы в QlikView (см. пример выше).
Информация о полях (колонках)
Про каждое поле в QVD хранится информация о
FieldName
Имя поля (опять же в терминах QlikView, т.е. с учетом "AS")
BitOffset, BitWidth, Bias
Пока пропустим — это информация для "расшифровки строк", рассмотрим в третьей части, когда будет разбираться со строками.
Type, nDec, UseThou, Fmt, Dec, Thou
Хорошо задуманная (судя по названиям), но абсолютно бесполезная с точки зрения достижения цели информация. Почему бесполезная? — тэг "Type" не коррелирует с типом данных, которые хранятся в бинарной части. По нему нельзя восстановить тип колонки (казалось бы — что может быть проще, есть же тэг Type!). В 90% случаев значением этого тэга будет строка UNKNOWN...
В метаданных о колонках бывают еще такие данные (в метаданных примера его нет, видимо, по причине малого размера)
<Comment></Comment>
<Tags>
<String>$numeric</String>
<String>$integer</String>
</Tags>
Тэги — тоже бесполезная (с точки зрения восстановления структуры таблицы) информация. Но по ней можно примерно догадаться, какого типа информация хранится в колонке.
NoOfSymbols
Количество записей в бинарной части, относящейся к данной колонке. Как мы видим — в нашем примере это 5. Очень важная для расшифровки информация.
Offset
Смещение блока данных данной колонки в байтах относительно начала бинарной части файла. Также очень важно.
Length
Длина всего блока данных данной колонки в байтах. Отметим, что бинарное представление элемента колонки (ячейки таблицы) в общем случае имеет переменную длину (строка, например), поэтому длину нельзя вычислить, можно только взять из этого тэга.
Информация о строках
Compression
Никогда не заполнено. Возможно, мы не используем эту опцию...
RecordByteSize
Размер записи о строке в байтах. Все строки представлены в бинарном блоке строк в виде битового индекса, битовый индекс состоит из строк одинаковой длины.
NoOfRecords
Количество строк (в битовом индексе и в исходной таблице).
Offset
Смещение битового индекса (блока с информацией о строках) в байтах относительно начала бинарной части файла.
Length
Длина битового индекса в байтах.
В метаданных о строках бывают еще такие данные (опять же — короткий пример не позволяет увидеть все, но зато позволяет разобраться в сложном)
<Lineage>
<LineageInfo>
<Discriminator>Provider=OraOLEDB.Oracle.1;Persist Security Info=True;Data Source=XXXX;Extended Properties=""</Discriminator>
<Statement>LinkTable:
LOAD
SOURCE_NAME & '_' & SOURCE_ID as SYSKEY,
HID_PARTY;SQL
SELECT *
FROM
UNITED_VIEW</Statement>
</LineageInfo>
<LineageInfo>
<Discriminator>Provider=OraOLEDB.Oracle.1;Persist Security Info=True;Data Source=XXXX;Extended Properties=""</Discriminator>
<Statement>SQL
SELECT *
FROM
UNITED_VIEW</Statement>
</LineageInfo>
<LineageInfo>
<Discriminator>STORE - \\xxx.ru\mfs\SPECIAL\Qlikview\QVData\LinkTable.qvd (qvd)</Discriminator>
<Statement></Statement>
</LineageInfo>
</Lineage>
<Comment></Comment>
Подытожим
- QVD файл является самодостаточным (т.е. его можно анализировать в отрыве от других данных)
- QVD файл состоит из текстовой (метаданные) и бинарной (колонки и битовый индекс) частей
- метаданные — это XML с вполне понятной семантикой
В QVD файле таблица хранится в виде двух косвенно связанных частей:
Таблицы символов (термин мой) содержат уникальные значения каждой колонки исходной таблицы. Именно о них пойдет речь ниже.
Таблица строк содержит строки исходной таблицы, каждая строка хранит индексы значений колонки (поля) строки в соответствующей таблице символов.
На примере нашей таблички (помните — из первой части)
SET NULLINTERPRET =<sym>;
tab1:
LOAD * INLINE [
ID, NAME
123.12,"Pete"
124,12/31/2018
-2,"Vasya"
1,"John"
<sym>,"None"
];
В этой табличке:
- 5 строк
- поле “ID” имеет 4 уникальных значения (NULL не считается значением, более подробно о нем — в третьей части)
- поле “NAME” имеет 5 уникальных значений
- первая строка в таблице строк будет содержать индексы 0 и 0, соответствующие значениям 123.12 и “Pete” соответственно
Специальные случаи
Как правило, для всех полей таблицы в QVD файле создаются таблицы символов. Но есть нюансы.
Если поле имеет одно значение, то это значение, как правило, хранится в таблице символов (в этом случае таблица символов будет содержать одну запись). А в таблице строк поле будет отсутствовать (ибо итак понятно, каким должно быть значение этого поля в каждой строке...)
Если поле не имеет значений вовсе (всегда содержит NULL), на него не создается таблица символов.
Хранение таблиц символов в файле
Каждая таблица символов хранится в QVD файле в виде бинарного блока, его смещение (относительно начала бинарного блока) содержится в поле Offset раздела метаданных этого поля, длина (в байтах) — в поле Length метаданных.
Таким образом, первая таблица символов всегда будет имет смещение 0.
Таблицы символов следуют одна за другой и никак друг от друга не отделяются.
Структура таблицы символов
Таблица символов содержит значения поля, которые идут друг за другом без разделителей, каждое значение представлено следующим образом:
- один байт кодирует тип поля (опишу ниже)
- далее идет опциональное бинарное значение (зависит от типа поля)
- за ним идет опциональное строковое значение (зависит от типа поля)
Строки хранятся “как есть” (в кодировке, указанной в метаданных), строка оканчивается нулевым байтом. Строка может иметь нулевую длину, т.е. состоять только из нулевого байта.
Бинарные значения хранятся по правилам той архитектуры, где был сгенерирован QVD файла, их можно просто читать как бинарные значения с оглядкой на "endian-ность".
Типы полей
Все многообразие типов данных QVD свел с трем базовым
- (1) целое число (4 байта)
- (2) плавающее число (8 байт)
- (4) строка, заканчивающаяся нулем
Существуют еще комбинированные типы
- (5) целое число, после которого идет его строковое представление (4 байта плюс строка с нулевым байтом)
- (6) плавающее число, после которого идет его строковое представление (8 байт плюс строка с нулевым байтом)
В скобках числовые значения “типов” (первый байт значения поля в таблице символов). Где тройка – непонятно.
Два не очень приятных практических наблюдения
Одно и то же поле может иметь в таблице символов значения разных типов (целые, плавающие и строки). Единственное, что можно “гарантировать” — не может быть смеси “число” и “число со строкой” (либо то, либо другое). Значения не числовых полей (не типов 1 и 2 в нотации выше) приходится читать подряд — невозможно спозиционироваться на поле номер N… Объяснимо, но неэффективно (в плане обработки).
Рассмотрим опять нашу приведенную выше табличку, таблица символов поля ID будет выглядеть так (побайтно/посимвольно):
- число 6 (тип) + 8 байт (плавающее значение 123.12) + 7 байт (строка "123.12" с нулевым байтом)
- число 5 (тип) + 4 байта (целое значение 124) + 4 байта (строка "124" с нулевым байтом)
- число 5 (тип) + 4 байта (целое -2) + 3 байта (строка "-2" с нулевым байтом)
- число 5 (тип) + 4 байта (целое 1) + 2 байта (строка "1" с нулевым байтом)
Итого 40 байт (метаданные, значение атрибута Length для поля ID).
Из практики
Практической задачей было воссоздание таблицы по QVD файлу. Из изложенного выше следует, что из описания колонки (метаданные плюс данные) невозможно однозначно определить тип поля (тот, который, например, писать в "create table..."). 90% полей имеют в метаданных тип UNKNOWN, тэги также не позволяют однозначно установить тип поля.
Как быть?
Можно пойти по статистическому пути — анализировать определенный процент значений колонки и по результатам делать вывод — какой тип ей присвоить. Точность получается вполне удовлетворительной, неприятность в том, что анализировать (в общем случае) нужно все данные.
Но "create table" подразумевает куда как большее количество типов данных. В обработанных файлах не обнаружено данных типов, отличных от вышеперечисленных. Файлы соответствовали вполне реальным таблицам реальных баз данных и содержали весь спектр типов данных (к примеру, попались даже блобы… Зачем они в QVD??? Лучше бы комментарии писали).
Наверное, для полноты картины с типами данных надо пояснить про даты и таймстампы (остальные типы — вопрос длины).
Даты представлены в QVD виде целого числа — количество дней от начала эпохи (эпохи клика). Специалисты по QlikView/QlikSense легко скажут — когда она началась (хотя было это 30 декабря 1899 года, не спрашивайте — почему).
Таймстампы в QVD представлены плавающим числом, содержащим дату так, как это описано чуть выше, и время в дробной части (где .0 соответствует времени "00:00:00" и .999999 соответствует времени "23:59:59").
Таблицы, воссозданные из QVD, содержат целые и плавающие типы для полей типа "date" и "datetime". Как вариант можно воспользоваться строковым представлением — для полей этого типа всегда используется комбинированное представление (типы 5 и 6).
Последнее (про практику) — при чтении больших файлов логично создавать индексы для строковых полей. Это существенно сокращает время обработки в случаях, когда размер таблицы символов сильно меньше количества строк (т.е. одно значение встречается в поле исходной таблицы более одного раза).