PostgreSQL: эффективная загрузка данных в схему Star

Представьте себе таблицу со следующей структурой в PostgreSQL 9.0:

create table raw_fact_table (text varchar(1000));

Для упрощения я упомяну только один текстовый столбец, в действительности он имеет дюжину. В этой таблице 10 миллиардов строк, и в каждом столбце много дубликатов. Таблица создается из плоского файла (CSV) с помощью COPY FROM.

Чтобы повысить производительность, я хочу преобразовать в следующую структуру звезды:

create table dimension_table (id int, text varchar(1000));

Затем таблица фактов будет заменена таблицей фактов, как показано ниже:

create table fact_table (dimension_table_id int);

Мой текущий метод состоит в том, чтобы по существу выполнить следующий запрос для создания таблицы измерений:

Create table dimension_table (id int, text varchar(1000), primary key(id));

Затем для создания заполните таблицу измерений, которую я использую:

insert into dimension_table (select null, text from raw_fact_table group by text);

После этого мне нужно выполнить следующий запрос:

select id into fact_table from dimension inner join raw_fact_table on (dimension.text = raw_fact_table.text);

Представьте себе ужасную производительность, которую я получаю, сравнивая все строки со всеми другими строками несколько раз.

На MySQL я мог запустить хранимую процедуру во время COPY FROM. Это может создать хеш строки, и все последующие сравнения строк будут выполняться для хеша вместо длинной необработанной строки. Это не представляется возможным в PostgreSQL, что мне тогда делать?

Примером данных будет файл CSV, содержащий что-то вроде этого (я использую кавычки также вокруг целых и двойных чисел):

"lots and lots of text";"3";"1";"2.4";"lots of text";"blabla"
"sometext";"30";"10";"1.0";"lots of text";"blabla"
"somemoretext";"30";"10";"1.0";"lots of text";"fooooooo"
Источник

5 ответов

Решение

Просто на вопросы: - Вам необходимо конвертировать ваши данные в 1 или 2 шага? - Можем ли мы изменить таблицу при конвертации?

Выполнение более простых запросов может улучшить вашу производительность (и нагрузку на сервер при этом)

Одним из подходов будет:

  1. генерировать размерность_таблицы (если я правильно понимаю, у вас нет проблем с производительностью) (возможно, с дополнительным временным логическим полем...)
  2. повторить: выберите одну ранее не выбранную запись из размерной таблицы, выберите все строки в raw_fact_table, содержащие ее, и вставьте их в fact_table. Пометить запись dimension_table как выполненную, а затем... Вы можете написать это как хранимую процедуру, и она может преобразовывать ваши данные в фоновом режиме, потребляя минимальные ресурсы...

Или другой (возможно, лучше):

  1. создать факт_таблицу как КАЖДУЮ запись из raw_fact_table И одного измерения_ид. (включая строки измерения_текст и размер_ид)
  2. создать размерную таблицу
  3. создайте триггер после вставки для fact_table, который:
    • ищет размерный текст в таблице фактов
    • если не найден, создает новую запись в измерении
    • обновляет размер_идентификатора до этого идентификатора
  4. в цикле simle вставьте каждую запись из raw_fact_table в fact_table
Источник

Представьте себе ужасную производительность, которую я получаю, сравнивая все строки со всеми другими строками несколько раз.

Когда вы делаете это некоторое время, вы перестаете воображать производительность и начинаете измерять ее. "Преждевременная оптимизация - корень всего зла".

Что значит для вас "миллиард"? Для меня в США это означает 1000 000 000 (или 1e9). Если это также верно для вас, вы, вероятно, просматриваете от 1 до 7 терабайт данных.

Мой текущий метод состоит в том, чтобы по существу выполнить следующий запрос для создания таблицы измерений:

Create table dimension_table (id int, text varchar(1000), primary key(id));

Как вы поместите 10 миллиардов строк в таблицу, которая использует целое число для первичного ключа? Давайте даже скажем, что половина строк является дубликатами. Как работает эта арифметика, когда вы это делаете?

Не представляю Читай сначала. Тогда проверь.

Читать хранилище данных с PostgreSQL. Я подозреваю, что эти слайды презентации дадут вам некоторые идеи.

Также прочитайте Заполнение базы данных и подумайте, какие предложения для реализации.

Протестируйте с миллионом (1e6) строк, следуя процессу "разделяй и властвуй". То есть не пытайтесь загрузить миллион за раз; написать процедуру, которая разбивает его на более мелкие куски. Бежать

EXPLAIN <sql statement>

Вы сказали, что оцениваете как минимум 99% дублирующихся строк. Вообще говоря, есть два способа избавиться от обманщиков

  1. Внутри базы данных не обязательно та же платформа, которую вы используете для производства.
  2. Вне базы данных, в файловой системе, не обязательно та же файловая система, которую вы используете для производства.

Если у вас все еще есть загруженные текстовые файлы, я бы посоветовал сначала попробовать за пределами базы данных. Этот awk one-liner будет выводить уникальные строки из каждого файла. Это относительно экономично, поскольку делает только один проход по данным.

awk '!arr[$0]++' file_with_dupes > file_without_dupes

Если у вас действительно есть 99% дупликов, к концу этого процесса вы должны были сократить свои от 1 до 7 терабайт до примерно 50 гигабайт. И, сделав это, вы также можете нумеровать каждую уникальную строку и создавать файл с разделителями табуляции, прежде чем копировать его в хранилище данных. Это еще одна строка:

awk '{printf("%d\t%s\n", NR, $0);}' file_without_dupes > tab_delimited_file

Если вам нужно сделать это под Windows, я бы использовал Cygwin.

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

Но я бы проверить

SELECT DISTINCT ...

перед использованием GROUP BY. Я мог бы провести некоторые тесты для большого набора данных для вас, но, вероятно, не на этой неделе. (Я обычно не работаю с файлами размером в терабайты. Это довольно интересно. Если вы можете подождать.)

Источник 
-- add unique index
CREATE UNIQUE INDEX uidx ON dimension_table USING hash(text);
-- for non case-sensitive hash(upper(text))

попробуйте хэш (текст); и btree(текст), чтобы увидеть, какой из них быстрее

Источник

Вы опускаете некоторые детали там в конце, но я не вижу, что обязательно есть проблема. Это не свидетельствует о том, что все строки на самом деле сравниваются со всеми другими строками. Если вы выполняете объединение, PostgreSQL вполне может выбрать более разумный алгоритм объединения, такой как объединение хешей, которое может дать вам то же хеширование, которое вы реализуете сами в своем решении MySQL. (Опять же, ваши данные неясны по этому поводу.)

Источник

Я вижу несколько способов решения вашей проблемы. В PostgreSql есть функция md5 md5(строка). Вычисляет хеш строки MD5, возвращая результат в шестнадцатеричном виде.

вставить в размерную таблицу (выберите ноль, md5(текст), текст из группы raw_fact_table по тексту)

добавьте поле md5 в raw_fact_table, а также выберите id в fact_table из внутреннего измерения измерения raw_fact_table on (dimension.md5 = raw_fact_table.md5);

Индексы в области MD5 также могут помочь

Или вы можете рассчитать MD5 на лету при загрузке данных. Например, наш ETL инструмент Расширенный процессор ETL может сделать это за вас. Кроме того, он может загружать данные в несколько таблиц одновременно.

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

http://www.dbsoftlab.com/online-tutorials/advanced-etl-processor/advanced-etl-processor-working-with-slow-changing-dimension-part-2.html

Источник
Другие вопросы по тегам