Это третий пост в серии, состоящей из нескольких частей. Мы рады поделиться десятками новых функций для автоматизации преобразования схем; сохраните свои инвестиции в существующие сценарии, отчеты и приложения; ускорить выполнение запросов; и сократить общие затраты на миграцию на Амазонка Redshift.
Amazon Redshift — ведущее облачное хранилище данных. Ни одно другое хранилище данных не позволяет так легко получить новую информацию из ваших данных. С помощью Amazon Redshift вы можете запрашивать эксабайты данных в своем хранилище данных, операционных хранилищах и озере данных, используя стандартный SQL. Вы также можете интегрировать другие сервисы, такие как Амазонка ЭМИ, Амазонка Афинаи Создатель мудреца Амазонки использовать все аналитические возможности в облаке AWS.
Многие клиенты обращались за помощью в переходе от самоуправляемых механизмов хранения данных, таких как Teradata, на Amazon Redshift. В этих случаях у вас могут быть терабайты (или петабайты) исторических данных, сильная зависимость от собственных функций и тысячи процессов и отчетов извлечения, преобразования и загрузки (ETL), созданных за годы (или десятилетия) использования.
До сих пор миграция хранилища данных Teradata на AWS была сложной и требовала значительных усилий вручную.
Сегодня мы рады поделиться последними улучшениями в Amazon Redshift и Инструмент преобразования схемы AWS (AWS SCT), которые упрощают автоматизацию миграции Teradata в Amazon Redshift.
В этом посте мы представляем новую автоматизацию для операторов слияния, встроенную функцию для поддержки преобразования символов ASCII, улучшенную проверку ошибок при преобразовании строк в дату, расширенную поддержку курсоров Teradata и столбцов идентификаторов, автоматизацию для предикатов ANY и SOME, автоматизацию для RESET WHEN. предложений, автоматизация двух собственных функций Teradata (TD_NORMALIZE_OVERLAP и TD_UNPIVOT) и автоматизация поддержки аналитических функций (QUANTILE и QUALIFY).
Оператор слияния
Как следует из названия, оператор слияния принимает входной набор и объединяет его с целевой таблицей. Если входная строка уже существует в целевой таблице (строка в целевой таблице имеет то же значение первичного ключа), то целевая строка обновляется. Если соответствующей целевой строки нет, в таблицу вставляется входная строка.
До сих пор, если вы использовали операторы слияния в своей рабочей нагрузке, вам приходилось вручную переписывать оператор слияния для запуска в Amazon Redshift. Теперь мы рады сообщить, что AWS SCT автоматизирует это преобразование за вас. AWS SCT разлагает оператор слияния на обновление существующих записей с последующей вставкой новых записей.
Давайте посмотрим на пример. Создаем две таблицы в Teradata: целевую таблицу, employeeи дельта-таблица, employee_delta, где мы размещаем входные строки:
CREATE TABLE testschema.employee( id INTEGER
, name VARCHAR(20)
, manager INTEGER)
UNIQUE PRIMARY INDEX (id)
; CREATE TABLE testschema.employee_delta ( id INTEGER
, name VARCHAR(20)
, manager INTEGER)
UNIQUE PRIMARY INDEX(id)
;
Теперь мы создаем оператор слияния Teradata, который обновляет строку, если она существует в цели, в противном случае он вставляет новую строку. Мы встраиваем этот оператор слияния в макрос, чтобы позже показать вам процесс преобразования.
REPLACE MACRO testschema.merge_employees AS ( MERGE INTO testschema.employee tgt USING testschema.employee_delta delta ON delta.id = tgt.id WHEN MATCHED THEN UPDATE SET name = delta.name, manager = delta.manager WHEN NOT MATCHED THEN INSERT (delta.id, delta.name, delta.manager);
);
Теперь мы используем AWS SCT для преобразования макроса. (Видеть Ускорьте миграцию хранилища данных на Amazon Redshift - часть 1 подробнее о преобразовании макросов.) AWS SCT создает хранимую процедуру, содержащую обновление (для реализации условия WHEN MATCHED) и вставку (для реализации условия WHEN NOT MATCHED).
CREATE OR REPLACE PROCEDURE testschema.merge_employees()
AS $BODY$
BEGIN UPDATE testschema.employee SET name = "delta".name, manager = "delta".manager FROM testschema.employee_delta AS delta JOIN testschema.employee AS tgt ON "delta".id = tgt.id; INSERT INTO testschema.employee SELECT "delta".id , "delta".name , "delta".manager FROM testschema.employee_delta AS delta WHERE NOT EXISTS ( SELECT 1 FROM testschema.employee AS tgt WHERE "delta".id = tgt.id );
END;
$BODY$
LANGUAGE plpgsql;
В этом примере показано, как использовать автоматизацию слияния для макросов, но вы можете конвертировать операторы слияния в любой контекст приложения: хранимые процедуры, сценарии BTEQ, код Java и т. д. Загрузите Последняя версия AWS SCT и опробуйте его.
Функция ASCII()
Функция ASCII принимает на вход строку и возвращает код ASCII или, точнее, кодовую точку UNICODE первого символа строки. Ранее Amazon Redshift поддерживал ASCII как функцию только для ведущего узла, что не позволяло использовать его с пользовательскими таблицами.
Мы рады сообщить, что функция ASCII теперь доступна на вычислительных узлах Amazon Redshift и может использоваться с пользовательскими таблицами. В следующем коде мы создаем таблицу с некоторыми строковыми данными:
CREATE TABLE testschema.char_table ( id INTEGER
, char_col CHAR(10)
, varchar_col VARCHAR(10)
); INSERT INTO testschema.char_table VALUES (1, 'Hello', 'world');
Теперь вы можете использовать функцию ASCII для строковых столбцов:
# SELECT id, char_col, ascii(char_col), varchar_col, ascii(varchar_col) FROM testschema.char_table; id | char_col | ascii | varchar_col | ascii 1 | Hello | 72 | world | 119
Наконец, если код вашего приложения использует функцию ASCII, AWS SCT автоматически преобразует любые вызовы таких функций в Amazon Redshift.
Ассоциация Функция ASCII доступен уже сейчас — попробуйте его в своем кластере.
Функция ТО_ДАТА()
Функция TO_DATE преобразует строку символов в значение ДАТЫ. Особенностью этой функции является то, что она может принять строковое значение, которое не является допустимой датой, и преобразовать его в действительную дату.
Например, рассмотрим строку 2021-06-31. Это недействительная дата, поскольку в июне всего 30 дней. Однако функция TO_DATE принимает эту строку и возвращает «31-й» день июня (1 июля):
# SELECT to_date('2021-06-31', 'YYYY-MM-DD'); to_date 2021-07-01
(1 row)
Клиенты попросили провести строгую проверку ввода для TO_DATE, и мы рады поделиться этой новой возможностью. Теперь вы можете включить логическое значение в вызов функции, которая включает строгую проверку:
# SELECT to_date('2021-06-31', 'YYYY-MM-DD', TRUE);
ERROR: date/time field date value out of range: 2021-6-31
Вы также можете явно отключить строгую проверку:
# SELECT to_date('2021-06-31', 'YYYY-MM-DD', FALSE); to_date 2021-07-01
(1 row)
Кроме того, логическое значение является необязательным. Если вы не включите ее, строгая проверка отключится, и вы увидите то же поведение, что и до запуска этой функции.
Вы можете узнать больше о НА СЕГОДНЯШНИЙ ДЕНЬ и попробуйте строгую проверку даты в Amazon Redshift прямо сейчас.
Наборы результатов КУРСОР
Курсор — это конструкция языка программирования, которую приложения используют для управления результирующим набором по одной строке за раз. Курсоры больше подходят для OLTP-приложений, но некоторые устаревшие приложения, созданные на основе хранилищ данных, также используют их.
Teradata предоставляет разнообразный набор конфигураций курсоров. Amazon Redshift поддерживает более оптимизированный набор функций курсора.
Основываясь на отзывах клиентов, мы добавили автоматизацию для поддержки курсоров Teradata С ВОЗВРАТОМ. Курсоры этих типов открываются внутри хранимых процедур и возвращаются вызывающей стороне для обработки набора результатов. AWS SCT преобразует курсор с возвратом в курсор Amazon Redshift РЕКУРСОР.
Например, рассмотрим следующую процедуру, которая содержит курсор с возвратом. Процедура открывает курсор и возвращает результат вызывающей стороне в виде ДИНАМИЧЕСКОГО НАБОРА РЕЗУЛЬТАТОВ:
REPLACE PROCEDURE testschema.employee_cursor (IN p_mgrid INTEGER) DYNAMIC RESULT SETS 1
BEGIN DECLARE result_set CURSOR WITH RETURN ONLY FOR SELECT id, name, manager FROM testschema.employee WHERE manager = to_char(p_mgrid); OPEN result_set;
END;
AWS SCT преобразует процедуру следующим образом. В сигнатуру процедуры добавляется дополнительный параметр для передачи REFCURSOR:
CREATE OR REPLACE PROCEDURE testschema.employee_cursor(par_p_mgrid IN INTEGER, dynamic_return_cursor INOUT refcursor)
AS $BODY$
DECLARE
BEGIN OPEN dynamic_return_cursor FOR SELECT id, name, manager FROM testschema.employee WHERE manager = to_char(par_p_mgrid, '99999');
END;
$BODY$
LANGUAGE plpgsql;
Столбцы ИДЕНТИЧНОСТИ
Teradata поддерживает несколько функций, не соответствующих ANSI, для столбцов IDENTITY. Мы усовершенствовали AWS SCT, чтобы по возможности автоматически преобразовывать эти функции в Amazon Redshift.
В частности, AWS SCT теперь преобразует предложения Teradata START With и INCREMENT BY в Условия Amazon Redshift SEED и STEP, соответственно. Например, рассмотрим следующую таблицу Teradata:
CREATE TABLE testschema.identity_table ( a2 BIGINT GENERATED ALWAYS AS IDENTITY ( START WITH 1 INCREMENT BY 20 )
);
Предложение GENERATED ALWAYS указывает, что столбец всегда заполняется автоматически — значение не может быть явно вставлено или обновлено в столбец. Предложение START With определяет первое значение, которое будет вставлено в столбец, а предложение INCREMENT BY определяет следующее значение, которое будет вставлено в столбец.
При преобразовании этой таблицы с помощью AWS SCT создается следующий DDL Amazon Redshift. Обратите внимание, что значения START With и INCREMENT BY сохраняются в целевом синтаксисе:
CREATE TABLE IF NOT EXISTS testschema.identity_table ( a2 BIGINT IDENTITY(1, 20)
)
DISTSTYLE KEY
DISTKEY
(a2)
SORTKEY
(a2);
Кроме того, по умолчанию столбец IDENTITY в Amazon Redshift содержит только автоматически сгенерированные значения, поэтому свойство GENERATED ALWAYS в Teradata сохраняется:
# INSERT INTO testschema.identity_table VALUES (100);
ERROR: cannot set an identity column to a value
Столбцы IDENTITY в Teradata также могут быть указаны как GENERATED BY DEFAULT. В этом случае значение может быть явно определено в инструкции INSERT. Если значение не указано, столбец заполняется автоматически сгенерированным значением, как обычно. Раньше AWS SCT не поддерживал преобразование столбцов GENERATED BY DEFAULT. Теперь мы рады сообщить, что AWS SCT автоматически преобразует такие столбцы для вас.
Например, следующая таблица содержит столбец IDENTITY, СОЗДАННЫЙ ПО УМОЛЧАНИЮ:
CREATE TABLE testschema.identity_by_default ( a1 BIGINT GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 INCREMENT BY 20 )
PRIMARY INDEX (a1);
Столбец IDENTITY преобразуется AWS SCT следующим образом. В преобразованном столбце используется предложение Amazon Redshift GENERATED BY DEFAULT:
CREATE TABLE testschema.identity_by_default ( a1 BIGINT GENERATED BY DEFAULT AS IDENTITY(1,20) DISTKEY
) DISTSTYLE KEY SORTKEY (a1);
Существует еще одна проблема синтаксиса, требующая внимания. В Teradata автоматически сгенерированное значение вставляется, когда для значения столбца указано NULL:
INSERT INTO identity_by_default VALUES (null);
Amazon Redshift использует другой синтаксис для той же цели. Здесь вы включаете ключевое слово DEFAULT в список значений, чтобы указать, что столбец должен быть создан автоматически:
INSERT INTO testschema.identity_by_default VALUES (default);
Мы рады сообщить, что AWS SCT автоматически преобразует синтаксис Teradata для операторов INSERT, как в предыдущем примере. Например, рассмотрим следующий макрос Teradata:
REPLACE MACRO testschema.insert_identity_by_default AS ( INSERT INTO testschema.identity_by_default VALUES (NULL);
);
AWS SCT удаляет NULL и заменяет его DEFAULT:
CREATE OR REPLACE PROCEDURE testschema.insert_identity_by_default() LANGUAGE plpgsql
AS $$ BEGIN INSERT INTO testschema.identity_by_default VALUES (DEFAULT);
END; $$
Автоматизация столбцов IDENTITY теперь доступна в AWS SCT. Вы можете скачать Последняя версия . Потренеруйтесь применять его на практике!
Фильтры ANY и SOME с предикатами неравенства
Фильтры ANY и SOME определяют, применяется ли предикат к одному или нескольким значениям в списке. Например, в Teradata вы можете использовать <> ANY, чтобы найти всех сотрудников, которые не работают у определенного менеджера:
REPLACE MACRO testschema.not_in_103 AS ( SELECT * FROM testschema.employee WHERE manager <> ANY (103)
;
);
Конечно, вы можете переписать этот запрос, используя простой фильтр «не равно», но вы часто видите запросы от сторонних генераторов SQL, которые следуют этому шаблону.
Amazon Redshift не поддерживает этот синтаксис изначально. Раньше любые запросы, использующие этот синтаксис, приходилось конвертировать вручную. Теперь мы рады сообщить, что AWS SCT автоматически преобразует предложения ANY и SOME с предикатами неравенства. Приведенный выше макрос преобразуется в хранимую процедуру следующим образом.
CREATE OR REPLACE PROCEDURE testschema.not_in_103(macro_out INOUT refcursor)
AS $BODY$
BEGIN OPEN macro_out FOR SELECT * FROM testschema.employee WHERE ((manager <> 103));
END;
$BODY$
LANGUAGE plpgsql;
Если список значений, следующий за ANY, содержит еще два значения, AWS SCT преобразует их в серию условий ИЛИ, по одному для каждого элемента в списке.
Преобразование фильтров ANY/SOME теперь доступно в AWS SCT. Вы можете попробовать это в Последняя версия приложения.
Аналитические функции с RESET WHEN
RESET WHEN — это функция Teradata, используемая в аналитических оконных функциях SQL. Это расширение стандарта ANSI SQL. RESET WHEN определяет раздел, над которым работает оконная функция SQL, на основе заданного условия. Если условие оценивается как истинное, внутри существующего оконного раздела создается новый динамический подраздел.
Например, в следующем представлении используется RESET WHEN для вычисления промежуточной суммы по магазинам. Текущая сумма накапливается по мере увеличения продаж из месяца в месяц. Если продажи падают от одного месяца к другому, текущий итог обнуляется.
CREATE TABLE testschema.sales ( store_id INTEGER
, month_no INTEGER
, sales_amount DECIMAL(9,2)
)
; REPLACE VIEW testschema.running_total ( store_id
, month_no
, sales_amount
, cume_sales_amount
)
AS
SELECT store_id
, month_no
, sales_amount
, SUM(sales_amount) OVER ( PARTITION BY store_id ORDER BY month_no RESET WHEN sales_amount < SUM(sales_amount) OVER ( PARTITION BY store_id ORDER BY month_no ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ) ROWS UNBOUNDED PRECEDING )
FROM testschema.sales;
Для демонстрации вставим в таблицу некоторые тестовые данные:
INSERT INTO testschema.sales VALUES (1001, 1, 35000.00);
INSERT INTO testschema.sales VALUES (1001, 2, 40000.00);
INSERT INTO testschema.sales VALUES (1001, 3, 45000.00);
INSERT INTO testschema.sales VALUES (1001, 4, 25000.00);
INSERT INTO testschema.sales VALUES (1001, 5, 30000.00);
INSERT INTO testschema.sales VALUES (1001, 6, 30000.00);
INSERT INTO testschema.sales VALUES (1001, 7, 50000.00);
INSERT INTO testschema.sales VALUES (1001, 8, 35000.00);
INSERT INTO testschema.sales VALUES (1001, 9, 60000.00);
INSERT INTO testschema.sales VALUES (1001, 10, 80000.00);
INSERT INTO testschema.sales VALUES (1001, 11, 90000.00);
INSERT INTO testschema.sales VALUES (1001, 12, 100000.00);
Объемы продаж уменьшаются после 3-го и 7-го месяцев. Промежуточная сумма сбрасывается соответственно в 4-м и 8-м месяцах.
SELECT * FROM testschema.running_total; store_id month_no sales_amount cume_sales_amount
----------- ----------- ------------ ----------------- 1001 1 35000.00 35000.00 1001 2 40000.00 75000.00 1001 3 45000.00 120000.00 1001 4 25000.00 25000.00 1001 5 30000.00 55000.00 1001 6 30000.00 85000.00 1001 7 50000.00 135000.00 1001 8 35000.00 35000.00 1001 9 60000.00 95000.00 1001 10 80000.00 175000.00 1001 11 90000.00 265000.00 1001 12 100000.00 365000.00
AWS SCT преобразует представление следующим образом. Преобразованный код использует подзапрос для эмуляции RESET WHEN. По сути, к результату добавляется атрибут маркера, который отмечает ежемесячное падение продаж. Затем этот флаг используется для определения самого длительного предшествующего периода увеличения продаж для агрегирования.
CREATE OR REPLACE VIEW testschema.running_total ( store_id
, month_no
, sales_amount
, cume_sales_amount) AS
SELECT store_id
, month_no
, sales_amount
, sum(sales_amount) OVER (PARTITION BY k1, store_id ORDER BY month_no NULLS FIRST ROWS UNBOUNDED PRECEDING)
FROM ( SELECT store_id , month_no , sales_amount , SUM(CASE WHEN k = 1 THEN 0 ELSE 1 END) OVER (PARTITION BY store_id ORDER BY month_no NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS k1 FROM ( SELECT store_id , month_no , sales_amount , CASE WHEN sales_amount < SUM(sales_amount) OVER (PARTITION BY store_id ORDER BY month_no ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) OR sales_amount IS NULL THEN 0 ELSE 1 END AS k FROM testschema.sales )
);
Мы ожидаем, что преобразование RESET WHEN будет иметь большой успех среди клиентов. Вы можете попробовать это сейчас в АМС СЦТ.
Функция TD_NORMALIZE_OVERLAP()
Функция TD_NORMALIZE_OVERLAP объединяет строки, имеющие перекрывающиеся значения PERIOD. Результирующая нормализованная строка содержит самую раннюю начальную границу и самую позднюю конечную границу из значений PERIOD всех задействованных строк.
Например, мы создаем таблицу Teradata, в которой записываются зарплаты сотрудников, с помощью следующего кода. В каждой строке таблицы указан период, в течение которого сотруднику выплачивалась данная зарплата.
CREATE TABLE testschema.salaries ( emp_id INTEGER
, salary DECIMAL(8,2)
, from_to PERIOD(DATE)
);
Теперь добавляем данные по двум сотрудникам. Для emp_id = 1 и salary = 2000, есть две перекрывающиеся строки. Аналогично, две строки с emp_id = 2 и salary = 3000 перекрываются.
SELECT * FROM testschema.salaries ORDER BY emp_id, from_to; emp_id salary from_to
----------- ---------- ------------------------ 1 1000.00 ('20/01/01', '20/05/31') 1 2000.00 ('20/06/01', '21/02/28') 1 2000.00 ('21/01/01', '21/06/30') 2 3000.00 ('20/01/01', '20/03/31') 2 3000.00 ('20/02/01', '20/04/30')
Теперь мы создаем представление, которое использует функцию TD_NORMALIZE_OVERLAP для нормализации перекрывающихся данных:
REPLACE VIEW testschema.normalize_salaries AS WITH sub_table(emp_id, salary, from_to) AS ( SELECT emp_id , salary , from_to FROM testschema.salaries
)
SELECT *
FROM TABLE(TD_SYSFNLIB.TD_NORMALIZE_OVERLAP (NEW VARIANT_TYPE(sub_table.emp_id, sub_table.salary), sub_table.from_to) RETURNS (emp_id INTEGER, salary DECIMAL(8,2), from_to PERIOD(DATE)) HASH BY emp_id LOCAL ORDER BY emp_id, salary, from_to ) AS DT(emp_id, salary, duration)
;
Мы можем проверить, что данные представления действительно нормализованы:
select * from testschema.normalize_salaries order by emp_id, duration; emp_id salary duration
----------- ---------- ------------------------ 1 1000.00 ('20/01/01', '20/05/31') 1 2000.00 ('20/06/01', '21/06/30') 2 3000.00 ('20/01/01', '20/04/30')
Теперь вы можете использовать AWS SCT для преобразования любых операторов TD_NORMALIZE_OVERLAP. Сначала преобразуем таблицу зарплат в Amazon Redshift (см. Ускорьте миграцию хранилища данных на Amazon Redshift - часть 2 подробнее об автоматизации типов данных периода):
CREATE TABLE testschema.salaries ( emp_id integer distkey
, salary numeric(8,2) ENCODE az64
, from_to_begin date ENCODE az64
, from_to_end date ENCODE az64 ) DISTSTYLE KEY SORTKEY (emp_id); # SELECT * FROM testschema.salaries ORDER BY emp_id, from_to_begin; emp_id | salary | from_to_begin | from_to_end 1 | 1000.00 | 2020-01-01 | 2020-05-31 1 | 2000.00 | 2020-06-01 | 2021-02-28 1 | 2000.00 | 2021-01-01 | 2021-06-30 2 | 3000.00 | 2020-01-01 | 2020-03-31 2 | 3000.00 | 2020-02-01 | 2020-04-30
Теперь мы используем AWS SCT для преобразования представленияnormalize_salaries. AWS SCT добавляет столбец, который отмечает начало новой группы строк. Затем он создает одну строку для каждой группы с нормализованной меткой времени.
CREATE VIEW testschema.normalize_salaries (emp_id, salary, from_to_begin, from_to_end) AS
WITH sub_table AS ( SELECT emp_id , salary , from_to_begin AS start_date , from_to_end AS end_date , CASE WHEN start_date <= lag(end_date) OVER (PARTITION BY emp_id, salary ORDER BY start_date, end_date) THEN 0 ELSE 1 END AS GroupStartFlag FROM testschema.salaries )
SELECT t2.emp_id
, t2.salary
, min(t2.start_date) AS from_to_begin
, max(t2.end_date) AS from_to_end
FROM ( SELECT emp_id , salary , start_date , end_date , sum(GroupStartFlag) OVER (PARTITION BY emp_id, salary ORDER BY start_date ROWS UNBOUNDED PRECEDING) AS GroupID FROM sub_table
) AS t2
GROUP BY t2.emp_id
, t2.salary
, t2.GroupID;
Мы можем проверить, что преобразованное представление возвращает правильно нормализованные данные:
# SELECT * FROM testschema.normalize_salaries ORDER BY emp_id; emp_id | salary | from_to_begin | from_to_end 1 | 1000.00 | 2020-01-01 | 2020-05-31 1 | 2000.00 | 2020-06-01 | 2021-06-30 2 | 3000.00 | 2020-01-01 | 2020-04-30
Вы можете опробовать преобразование TD_NORMALIZE_OVERLAP в последней версии AWS SCT. Скачать это сейчас.
Функция TD_UNPIVOT()
Функция TD_UNPIVOT преобразует столбцы в строки. По сути, мы используем его, чтобы взять ряд похожих показателей за разные периоды времени и создать отдельную строку для каждого показателя.
Например, рассмотрим следующую таблицу Teradata. В таблице записаны посещения покупателей по годам и месяцам для небольших магазинов-киосков:
CREATE TABLE TESTSCHEMA.kiosk_monthly_visits ( kiosk_id INTEGER
, year_no INTEGER
, jan_visits INTEGER
, feb_visits INTEGER
, mar_visits INTEGER
, apr_visits INTEGER
, may_visits INTEGER
, jun_visits INTEGER
, jul_visits INTEGER
, aug_visits INTEGER
, sep_visits INTEGER
, oct_visits INTEGER
, nov_visits INTEGER
, dec_visits INTEGER)
PRIMARY INDEX (kiosk_id);
Вставляем в таблицу примерные данные:
INSERT INTO testschema.kiosk_monthly_visits VALUES (100, 2020, 1100, 1200, 1300, 1400, 1500, 1600, 1700, 1800, 1900, 2000, 2100, 2200);
Затем мы создаем представление, которое отменяет поворот таблицы, чтобы ежемесячные посещения отображались в отдельных строках. Одна строка в сводной таблице создает 12 строк в несведенной таблице, по одной строке в месяц.
REPLACE VIEW testschema.unpivot_kiosk_monthly_visits ( kiosk_id
, year_no
, month_name
, month_visits
)
AS
SELECT kiosk_id
, year_no
, month_name (FORMAT 'X(10)')
, month_visits
FROM TD_UNPIVOT ( ON (SELECT * FROM testschema.kiosk_monthly_visits) USING VALUE_COLUMNS ('month_visits') UNPIVOT_COLUMN('month_name') COLUMN_LIST( 'jan_visits' , 'feb_visits' , 'mar_visits' , 'apr_visits' , 'may_visits' , 'jun_visits' , 'jul_visits' , 'aug_visits' , 'sep_visits' , 'oct_visits' , 'nov_visits' , 'dec_visits' ) COLUMN_ALIAS_LIST ( 'jan' , 'feb' , 'mar' , 'apr' , 'may' , 'jun' , 'jul' , 'aug' , 'sep' , 'oct' , 'nov' , 'dec' )
) a;
Когда вы выбираете из представления, ежемесячные продажи разбиваются на 12 отдельных строк:
SELECT * FROM testschema.unpivot_monthly_sales; id yr mon mon_sales
----------- ----------- ---------- ----------
100 2021 jan 1100.00
100 2021 feb 1200.00
100 2021 mar 1300.00
100 2021 apr 1400.00
100 2021 may 1500.00
100 2021 jun 1600.00
100 2021 jul 1700.00
100 2021 aug 1800.00
100 2021 sep 1900.00
100 2021 oct 2000.00
100 2021 nov 2100.00
100 2021 dec 2200.00
Теперь мы используем AWS SCT для преобразования представления в ANSI SQL, который можно запустить на Amazon Redshift. В результате преобразования создается общее табличное выражение (CTE), в котором каждый месяц помещается в отдельную строку. Затем он объединяет CTE и остальные атрибуты исходной сводной таблицы.
REPLACE VIEW testschema.unpivot_kiosk_monthly_visits (kiosk_id, year_no, month_name, month_visits) AS
WITH cols
AS (SELECT 'jan' AS col
UNION ALL
SELECT 'feb' AS col
UNION ALL
SELECT 'mar' AS col
UNION ALL
SELECT 'apr' AS col
UNION ALL
SELECT 'may' AS col
UNION ALL
SELECT 'jun' AS col
UNION ALL
SELECT 'jul' AS col
UNION ALL
SELECT 'aug' AS col
UNION ALL
SELECT 'sep' AS col
UNION ALL
SELECT 'oct' AS col
UNION ALL
SELECT 'nov' AS col
UNION ALL
SELECT 'dec' AS col)
SELECT t1.kiosk_id, t1.year_no, col AS "month_name", CASE col WHEN 'jan' THEN "jan_visits" WHEN 'feb' THEN "feb_visits" WHEN 'mar' THEN "mar_visits" WHEN 'apr' THEN "apr_visits" WHEN 'may' THEN "may_visits" WHEN 'jun' THEN "jun_visits" WHEN 'jul' THEN "jul_visits" WHEN 'aug' THEN "aug_visits" WHEN 'sep' THEN "sep_visits" WHEN 'oct' THEN "oct_visits" WHEN 'nov' THEN "nov_visits" WHEN 'dec' THEN "dec_visits" ELSE NULL END AS "month_visits" FROM testschema.kiosk_monthly_visits AS t1 CROSS JOIN cols WHERE month_visits IS NOT NULL;
Вы можете проверить, что преобразованное представление дает тот же результат, что и версия Teradata:
# SELECT * FROM testschema.unpivot_kiosk_monthly_visits; kiosk_id | year_no | month_name | month_visits 100 | 2020 | oct | 2000 100 | 2020 | nov | 2100 100 | 2020 | jul | 1700 100 | 2020 | feb | 1200 100 | 2020 | apr | 1400 100 | 2020 | aug | 1800 100 | 2020 | sep | 1900 100 | 2020 | jan | 1100 100 | 2020 | mar | 1300 100 | 2020 | may | 1500 100 | 2020 | jun | 1600 100 | 2020 | dec | 2200
Вы можете опробовать поддержку преобразования для TD_UNPIVOT в Последняя версия AWS SCT.
Функция КВАНТИЛЬ
КВАНТИЛЬ — это функция ранжирования. Он делит входной набор на определенное количество групп, каждая из которых содержит равную часть общей совокупности. QUANTILE — это собственное расширение Teradata функции NTILE, найденной в ANSI SQL.
Например, мы можем вычислить квартили данных о ежемесячных посещениях, используя следующее представление Teradata:
REPLACE VIEW testschema.monthly_visit_rank AS
SELECT kiosk_id
, year_no
, month_name
, month_visits
, QUANTILE(4, month_visits) qtile
FROM testschema.unpivot_kiosk_monthly_visits
;
Когда вы выбираете из представления, функция КВАНТИЛЬ вычисляет квартиль и применяет его в качестве атрибута на выходе:
SELECT * FROM monthly_visit_rank; kiosk_id year_no month_name month_visits qtile
----------- ----------- ---------- ------------ ----------- 100 2020 jan 1100 0 100 2020 feb 1200 0 100 2020 mar 1300 0 100 2020 apr 1400 1 100 2020 may 1500 1 100 2020 jun 1600 1 100 2020 jul 1700 2 100 2020 aug 1800 2 100 2020 sep 1900 2 100 2020 oct 2000 3 100 2020 nov 2100 3 100 2020 dec 2200 3
Amazon Redshift поддерживает обобщенную функцию NTILE, которая может реализовывать QUANTILE и совместима с ANSI. Мы усовершенствовали AWS SCT, чтобы автоматически преобразовывать вызовы функций QUANTILE в эквивалентные вызовы функций NTILE.
Например, при преобразовании предыдущего представления Teradata AWS SCT создает следующий код Amazon Redshift:
SELECT unpivot_kiosk_monthly_visits.kiosk_id
, unpivot_kiosk_monthly_visits.year_no
, unpivot_kiosk_monthly_visits.month_name
, unpivot_kiosk_monthly_visits.month_visits
, ntile(4) OVER (ORDER BY unpivot_kiosk_monthly_visits.month_visits ASC NULLS FIRST) - 1) AS qtile FROM testschema.unpivot_kiosk_monthly_visits
;
Поддержка преобразования QUANTILE теперь доступна в АМС СЦТ.
КВАЛИФИКАЦИЯ фильтр
Предложение QUALIFY в Teradata фильтрует строки, созданные аналитической функцией. Давайте посмотрим на пример. Мы используем следующую таблицу, в которой указана выручка магазина по месяцам. Наша цель — найти пять лучших месяцев по выручке:
CREATE TABLE testschema.sales ( store_id INTEGER
, month_no INTEGER
, sales_amount DECIMAL(9,2))
PRIMARY INDEX (store_id); SELECT * FROM sales; store_id month_no sales_amount
----------- ----------- ------------ 1001 1 35000.00 1001 2 40000.00 1001 3 45000.00 1001 4 25000.00 1001 5 30000.00 1001 6 30000.00 1001 7 50000.00 1001 8 35000.00 1001 9 60000.00 1001 10 80000.00 1001 11 90000.00 1001 12 100000.00
Данные показывают, что июль, сентябрь, октябрь, ноябрь и декабрь были пятью месяцами с наибольшими продажами.
Мы создаем представление, которое использует функцию РАНГ для ранжирования каждого месяца по продажам, а затем используем функцию КВАЛИФИКАЦИЯ, чтобы выбрать пять лучших месяцев:
REPLACE VIEW testschema.top_five_months( store_id
, month_no
, sales_amount
, month_rank
) as
SELECT store_id
, month_no
, sales_amount
, RANK() OVER (PARTITION BY store_id ORDER BY sales_amount DESC) month_rank
FROM testschema.sales
QUALIFY RANK() OVER (PARTITION by store_id ORDER BY sales_amount DESC) <= 5
;
Раньше, если вы использовали предложение QUALIFY, вам приходилось вручную перекодировать операторы SQL. Теперь AWS SCT автоматически преобразует QUALIFY в совместимый с Amazon Redshift и ANSI SQL. Например, AWS SCT переписывает предыдущее представление следующим образом:
CREATE OR REPLACE VIEW testschema.top_five_months ( store_id
, month_no
, sales_amount
, month_rank) AS
SELECT qualify_subquery.store_id
, qualify_subquery.month_no
, qualify_subquery.sales_amount
, month_rank
FROM ( SELECT store_id , month_no , sales_amount , rank() OVER (PARTITION BY store_id ORDER BY sales_amount DESC NULLS FIRST) AS month_rank , rank() OVER (PARTITION BY store_id ORDER BY sales_amount DESC NULLS FIRST) AS qualify_expression_1 FROM testschema.sales) AS qualify_subquery WHERE qualify_expression_1 <= 5;
AWS SCT преобразует исходный запрос во подзапрос и применяет выражение QUALIFY в качестве фильтра к подзапросу. AWS SCT добавляет в подзапрос дополнительный столбец для фильтрации. Это не является строго необходимым, но упрощает код, когда псевдонимы столбцов не используются.
Вы можете попробовать конвертацию QUALIFY в Последняя версия AWS SCT.
Обзор
Мы рады поделиться с вами этими новыми функциями. Если вы планируете перейти на Amazon Redshift, эти возможности помогут автоматизировать преобразование схемы и сохранить инвестиции в существующие отчеты и приложения. Если вы хотите приступить к миграции хранилища данных, вы можете узнать больше об Amazon Redshift и AWS SCT из нашей общедоступной документации.
В этом посте описаны некоторые из десятков новых функций, которые мы представляем для автоматизации миграции Teradata в Amazon Redshift. В следующих публикациях мы расскажем больше об автоматизации собственных функций Teradata и других интересных новых возможностях.
Зайдите позже для получения дополнительной информации. А пока вы можете узнать больше о Амазонка Redshift и Инструмент преобразования схемы AWS. Удачной миграции!
Об авторах
Майкл Су — старший инженер баз данных в команде службы миграции баз данных AWS. Он создает продукты и услуги, которые помогают клиентам переносить рабочие нагрузки баз данных в облако AWS.
Раза Хафиз — архитектор данных в глобальной специализированной практике AWS Professional Services Lake House. Он имеет более чем 10-летний профессиональный опыт создания и оптимизации корпоративных хранилищ данных и стремится предоставить клиентам возможность реализовать потенциал своих данных. Он специализируется на миграции корпоративных хранилищ данных на AWS Lake House Architecture.
По Хонг, доктор философии, — главный архитектор данных глобальной специализированной практики Lake House, AWS Professional Services. Он с энтузиазмом помогает клиентам внедрять инновационные решения, позволяющие сократить время на получение информации. По специализируется на миграции крупномасштабных локальных хранилищ данных MPP на архитектуру AWS Lake House.
Энтонг Шен — менеджер по разработке программного обеспечения в Amazon Redshift. Он работает над базами данных MPP более 9 лет и сосредоточился на оптимизации запросов, статистике и функциях языка SQL, связанных с миграцией, таких как хранимые процедуры и типы данных.
Сумит Сингх — инженер баз данных в команде службы миграции баз данных в Amazon Web Services. Он тесно сотрудничает с клиентами и оказывает техническую помощь при переносе их локальных рабочих нагрузок в облако AWS. Он также помогает постоянно улучшать качество и функциональность продуктов миграции данных AWS.
Нелли Сусанто — старший специалист по миграции баз данных в AWS Database Migration Accelerator. У нее более 10 лет технического опыта, посвященного миграции и репликации баз данных, а также рабочим нагрузкам хранилищ данных. Она с энтузиазмом помогает клиентам в их облачном путешествии.
Источник: https://aws.amazon.com/blogs/big-data/part-3-accelerate-your-data-warehouse-migration-to-amazon-redshift/
