Wide records – wide table

К примеру, есть csv файл с записями. В сжатом виде 3 мегабайта, в распакованном 18 мегабайт. Заголовок определяет около 200 полей (естественно не все записи содержат). Нужно загрузить файл в Staging Area.

Первый вопрос, который возникает, - как хранить данные?

Правильный ответ – грузить в Staging Area не надо. Лучше хранить в памяти :) Ладно, предположим таких файлов штук 50. 50*18<1GB. Нет, все еще стоит подумать о просто загрузке в память. Ну скажем файлов у нас 200, нам еще и другие данные в памяти нужны, да и памяти у нас мало (бедные-бедные, но все равно потом придется выложить деньги на быстрые диски).

В случае с базами данных есть три варианта:

1. Создать таблицу с 200ми колонками 
2. Выделить в колонки только важные поля, остальные сериализовать в bulk column 
3. Таблица с колонками name-value

Три варианта обусловлены тем, что реляционные базы данных требуют строгой схемы. То есть 4й вариант – не использовать реляционную базу данных, читайте у Ayende - NoSQL.

Wide table

200 колонок в таблице - звучит непривычно, однако вариант вполне хороший.

Минусы:

- нужно поддерживать схему таблицы; действительно, при таком большом количестве колонок можно ожидать некоторых изменений и эти изменения могут быть проблематичными; вообще, это не такая большая проблема, потому что поставщику данных такие изменения делать тоже неудобно, поэтому и шанс изменений низок, обычно просто добавляют новые колонки

- если данные сильно разрежены (из 200 полей одна запись имеет только 5-10), то такие таблицы занимают больше, чем могли бы – значит большая нагрузка на диск, больше места на диске

- вообще говоря, в ряде случаев есть ограничения на размер одной записи; если ограничений нет, то будьте готовы, что если превышается определенный объем, то в целом работа с такой таблицей будет замедлятся

Плюсы:

- если данные разрежены не сильно, то это наиболее эффективный способ хранения данных (это станет понятно, когда мы рассмотрим остальные варианты)

- иметь типизированные колонки удобно

Применительно к SQL Server, приведенные выше минусы в чем-то решены новым механизмом wide tables, появившемся в 2008 версии. Wide tables используют sparse columns и column sets. Вы определяете набор sparce columns и объединяете их в column set.

Если записи сильно разрежены, то sparce columns требуют меньше места для полей, у которых нет значений. Для этого используются дополнительные биты, то есть в случае наличия всех значений, запись будет занимать больше места, чем обычно.

В целом, максимальный размер записи остается тот же, что и раньше. Надежда просто на то, что данные разрежены и упомянутая выше технология сэкономит нам место.

Column set добавляет новую специальную xml колонку и позволяет получить все sparce columns в xml виде, также позволяет напрямую изменять xml и транслирует эти изменения в таблицу. Удобно, но имеет свою цену.

Если вы работаете с такой таблицей из .NET, то будьте готовы к багу в SqlBulkCopy (Мы ведь говорим об ETL, правда? Значит, мы загружаем данные при помощи bulk insert).

SqlBulkCopy видит только колонки, не включенные в column set, и xml колонку, созданную column set-ом. Вкратце, чтобы определить набор колонок в таблице, он далает select *, и этот запрос не возвращает sparce columns (тоже надо иметь ввиду). По сути, чтобы добавить запись в такую таблицу при помощи SqlBulkCopy, вам придеться сериализовать запись в xml и надеятся на механизм column set-ов. Естественно, производительность падает.