AWS Glue: многораздельные базы данных SQL Server ETL в Redshift

Наша команда пытается создать ETL в Redshift, который будет нашим хранилищем данных для некоторых отчетов. Мы используем Microsoft SQL Server и разбили нашу базу данных на 40+ источников данных. Мы ищем способ передать данные из всех этих идентичных источников данных в 1 базу данных Redshift.

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

Мы также рассмотрели DMS, которая полезна для преобразования схемы и текущих данных в красное смещение, но не похоже, что это сработает и для проблемы с несколькими секционированными источниками данных.


sql-server etl aws-glue amazon-redshift database-partitioning
person jetset    schedule 17.01.2018    source источник
comment
У некоторых из моих коллег была аналогичная проблема, и, не будучи впечатленными Amazon Glue, они в конечном итоге использовали Matillion. Однако я думаю, что им пришлось прибегнуть к какой-то причудливой логике, чтобы объединить данные из нескольких баз данных.   -  person ColdSolstice    schedule 17.01.2018
comment
@ColdSolstice Спасибо за информацию, грустно слышать, что они не смогли использовать Amazon Glue, но я обязательно изучу Matillion. Знаете ли вы, использовали ли они это решение не только для миграции данных, но и использовали ли они его для постоянной передачи данных в качестве хранилища данных?   -  person jetset    schedule 18.01.2018
comment
SQL Server также поддерживает разделение хранилищ столбцов и. Вы уверены, что вам нужна новая база данных вместо лучшей схемы? Что означает 40 источников данных? Почему не единое хранилище данных? Почему бы не использовать звездообразные схемы и хранилища столбцов?   -  person Panagiotis Kanavos    schedule 18.01.2018
comment
Кроме того, Redshift не волшебство. Он предназначен для масштабирования для хранилищ данных с несколькими ТБ. Это означает, что он не будет работать так быстро, как ожидалось в однозначной шкале ГБ, но будет работать так же быстро в масштабе ТБ, если вы купите достаточно большой экземпляр. Это также не повлияет на плохой дизайн. Схема типа "звезда" будет работать быстрее, чем несвязанные таблицы   -  person Panagiotis Kanavos    schedule 18.01.2018
comment
И, наконец, Redshift хорош для агрегирования запросов и приема большого количества (несколько ГБ) данных из файлов, которые уже имеют соответствующую форму. Это ужасно в ETL, когда вы добавляете по одной строке за раз. Думайте однозначные строки в секунду. ужасно получать отдельные строки. В первом случае необходимо обновить сегменты столбцов и словари для каждого столбца в строке. И повторяем это для каждого ряда. При получении строк он должен распаковать все сегменты, чтобы получить значения, соответствующие одной строке.   -  person Panagiotis Kanavos    schedule 18.01.2018
comment
@LucasKlaassen, сколько у вас данных (ГБ и строк) и сколько строк добавляется в день?   -  person Panagiotis Kanavos    schedule 18.01.2018
comment
@PanagiotisKanavos, вы правы, что не должны использовать транзакцию ETL с красным смещением. Но вы ошибаетесь, выполняя преобразования внутри красного смещения. Redshift отлично подходит для этого! (не по одной строке, конечно, но целые таблицы обновляются новыми данными, которые были введены - например, случай, который я описываю ниже)   -  person Jon Scott    schedule 18.01.2018
comment
@JonScott ошибается, имея реальный жизненный опыт и сталкиваясь с последствиями плохих решений? Обновление данных в любой базе данных отчетов плохо. Вот почему используются таблицы фактов. Добавление новых данных с помощью COPY - это не updating with new data. Использование Redshift в качестве промежуточной вместо базы данных отчетов медленнее, чем использование другой базы данных для работы с каждой строкой, или, по крайней мере, стоит намного больше для той же производительности. Вы можете выполнить ETL, но это будет стоить вам намного дороже, чем предварительная подготовка данных и использование COPY.   -  person Panagiotis Kanavos    schedule 18.01.2018
comment
Я настраивал это несколько раз, и это хорошо. Если вам нужны доказательства, посмотрите, как Matillion ETL работает с красным смещением и насколько он популярен. В любом случае - если кто-то следит за этим и хочет получить для них правильный ответ, я предлагаю им попробовать быструю PoC, используя различные возможные методы, и выбрать тот, который лучше для них.   -  person Jon Scott    schedule 18.01.2018
comment
кстати, вообще матиллион не рекомендую. Вместо этого я предпочитаю вручную кодировать ETL. Я просто цитировал это, чтобы показать, что преобразование внутри Redshift не только хорошо работает, но и очень популярно. см. matillion.com/etl-for-redshift/faq/#performance   -  person Jon Scott    schedule 18.01.2018
comment
@PanagiotisKanavos отличный вопрос, наша цель - иметь возможность запускать некоторые внутренние отчеты (в основном для извлечения агрегированных данных) из наших баз данных SQL Server. На данный момент 40 источников данных означает, что у нас есть 1 схема базы данных, которую мы разделили на 40 различных баз данных для горизонтального масштабирования. Прямо сейчас, чтобы запустить отчет для всех наших клиентов, нам нужно пройтись по каждой базе данных и либо выполнить запрос запросов к этим данным, либо выполнить некоторые неприятные вычисления в памяти. Целью является возможность загружать определенные таблицы из каждой базы данных в одну основную внутреннюю базу данных отчетов.   -  person jetset    schedule 18.01.2018
comment
@LucaKlaassen 1-й) о каком количестве данных мы говорим? Почему 40 серверов вместо большего количества дисков в массиве RAID, кубах OLAP или хранилищах столбцов? 2) SQL Server поддерживает объединенные представления с 2000 года. У вас может быть одно представление, которое ссылается на другие таблицы, и, пока они имеют правильные ограничения, оптимизатор знает, что запрашивает данные у соответствующей связанной таблицы.   -  person Panagiotis Kanavos    schedule 18.01.2018
comment
@LucasKlaassen, с другой стороны, Lenovo опубликовала результаты TPC-H для одного экземпляра хранилища данных 10 ТБ почти 2 года назад. Сколько у вас данных? В этом диапазоне Redshift будет стоить от 10 до 55 тысяч долларов в год.   -  person Panagiotis Kanavos    schedule 18.01.2018
comment
@PanagiotisKanavos, спасибо за эту разбивку, я собираюсь взглянуть на федеративные представления, чтобы увидеть, сработает ли это для нас. Знаете ли вы, помимо их документации какие еще хорошие ресурсы можно посмотреть?   -  person jetset    schedule 18.01.2018

Ответы (2)


arrow_upward
3
arrow_downward

Похоже, это отличный вариант использования Matillion ETL для Redshift.

(Полное раскрытие информации: я являюсь менеджером по продукту Matillion ETL для Redshift)

Matillion - это инструмент ELT - он будет извлекать данные из ваших (многочисленных) баз данных SQL-сервера и загружать их с помощью эффективного Redshift COPY в некоторые промежуточные таблицы (которые могут храниться внутри Redshift обычным образом или могут храниться на S3. и доступ из Redshift через Spectrum). Оттуда вы можете добавлять задания преобразования для очистки / фильтрации / присоединения (и многое другое!) К красивым запрашиваемым звездообразным схемам для ваших пользователей отчетов.

Если схемы таблиц в ваших 40+ базах данных очень похожи (ваш вопрос не проясняет, как вы разбиваете свои данные на эти серверы - горизонтальные или вертикальные), вы можете параметризовать детали подключения в своих заданиях и использовать итерацию для их выполнения. каждая исходная база данных, либо последовательно, либо с определенным уровнем параллелизма.

Передача преобразований в Redshift работает хорошо, потому что все эти запросы преобразования могут использовать мощь массивно параллельной масштабируемой вычислительной архитектуры. Конфигурация управления рабочей нагрузкой может использоваться для обеспечения одновременного выполнения запросов ETL и пользователей.

Кроме того, у вас могут быть другие источники данных, которые вы хотите объединить в кластере Redshift, и Matillion поддерживает многие другие - см. https://www.matillion.com/etl-for-redshift/integrations/.

person langton    schedule 19.01.2018

arrow_upward
0
arrow_downward

Для этого можно использовать AWS DMS.

Шаги:

  1. установить и настроить экземпляр DMS
  2. настроить целевую конечную точку для красного смещения
  3. настройте исходные конечные точки для каждого экземпляра сервера sql см. https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.SQLServer.html
  4. настроить задачу для каждого источника sql-сервера, вы можете указать таблицы для копирования / синхронизации, и вы можете использовать преобразование, чтобы указать, какие имена схем при красном смещении вы хотите писать.

Тогда у вас будут все данные в идентичных схемах для красного смещения.

Если вы хотите запросить все это вместе, вы можете сделать это, запустив некоторый код преобразования внутри redsshift для объединения и создания новых таблиц. Или вы можете использовать представления.

person Jon Scott    schedule 18.01.2018
comment
Redshift хорош для агрегированных запросов, а не для ETL. Если вам нужно выполнять преобразования внутри Redshift, производительность будет очень плохой. - person Panagiotis Kanavos; 18.01.2018
comment
Неправильно, Redshift отлично справляется со многими задачами ETL. Вы должны разобраться с WLM, если у вас работает ETL, а также другое использование. - person Jon Scott; 18.01.2018
comment
Неправильно неправильно, если вы не хотите платить за больший кластер, чтобы рассматривать Redshift как промежуточную базу данных. Трансформация не означает specify which schema name(s) on redshift you want to write to. Это означает объединение данных, очистку, удаление плохих строк, разделение или объединение столбцов до тех пор, пока вы не приведете данные в форму, подходящую для вашего бизнес-сценария, обычно для отчетов. Если у вас есть даже умеренный объем данных, вы не можете сделать это по частям, это должно быть выполнено в потоковом режиме - вот почему T стоит перед L. В противном случае вы говорите о ELT - Extract Load Transform. - person Panagiotis Kanavos; 18.01.2018
comment
Очевидно, что использование ресурсов будет увеличиваться, но этот шаблон хорош! - см. мой комментарий выше - исследуйте успех matillion в этой области, если вам нужны доказательства. - person Jon Scott; 18.01.2018
comment
Я не думаю, что Матиллион делает то, что вы думаете. Он не выполняет преобразования внутри Redshift, то есть с помощью SQL-запросов. Это не первый продукт ETL в мире. Все выполняют одинаковую работу, все сталкиваются с одними и теми же ограничениями. Инструменты eTl загружают данные из источников в поток, преобразуют данные по мере их передачи, а затем отправляют их на выход. Преобразование Aggregate в всех из них, например, должно будет кэшировать данные. То же самое с Distinct, поэтому они дорогие - person Panagiotis Kanavos; 18.01.2018
comment
вы читали matillion.com/etl-for-redshift/faq/#performance?? Взгляните на 3 точки там, чтобы получить общий обзор - или используйте это на практике в полевых условиях, чтобы увидеть реальность. Проблема с этим инструментом в том, что иногда преобразование SQL может очень быстро стать очень большим, если вы начнете объединять вместе десятки шагов преобразования. Тем не менее, он все еще работает хорошо. Я согласен, что существует также возможность преобразования за пределами Redshift (конечно) с использованием традиционных инструментов, Apache Spark и т. Д. Или клея AWS. для них есть варианты использования. - person Jon Scott; 18.01.2018
comment
В конце концов, Matillion придется использовать любой доступный механизм для вставки новых данных в таблицу. Redshift предоставляет либо COPY, либо отдельные инструкции INSERT. Нет никакой магии - person Panagiotis Kanavos; 18.01.2018
comment
Позвольте нам продолжить это обсуждение в чате. - person Jon Scott; 18.01.2018
comment
Я не читал рекламную аннотацию. Я сразу перешел к техническим данным по трансформациям. Я не первый раз работаю с хранилищами данных или Redshift. Или нужно устранять неполадки каскадных CTE для запроса плохо спроектированных таблиц Redshift, потому что кто-то думал, что нам не нужны никакие вонючие звездные схемы с Redshift - person Panagiotis Kanavos; 18.01.2018
comment
Это не маркетинговая реклама, это просто несколько строк информации, объясняющих, как matillion подтолкнул все преобразования к красному смещению. Вот как это работает. - person Jon Scott; 18.01.2018
comment
as Matillion is an ELT tool. Как я уже сказал, он не делает того, что вы думаете. В таких случаях, да, ручное кодирование CTE может быть лучше и быстрее, чем создание инструмента для их создания. Пока запрос не использует тонны памяти и временного хранилища, потому что, например, агрегаты должны читать все данные перед выдачей вывода. Такие подходы делают хуже масштабируются, чем обработка потока данных, что и делают (более продвинутые) инструменты ETL. - person Panagiotis Kanavos; 18.01.2018