Поделиться через

Минимизация проблем с SQL для миграций Oracle

  • Статья

Эта статья представляет собой пятую из семи статей, посвященных рекомендациям по миграции данных из Oracle в Azure Synapse Analytics. Эта статья посвящена рекомендациям по минимизации проблем с SQL.

Обзор

Характеристики сред Oracle

Первоначальная база данных Oracle, выпущенная в 1979 году, представляла собой коммерческую реляционную базу данных SQL для OLTP-приложений с гораздо более низкой скоростью транзакций, чем сегодня. С тех пор среда Oracle превратилась в гораздо более сложную и включает в себя множество функций. К ним относятся клиент-серверные архитектуры, распределенные базы данных, параллельная обработка, аналитика данных, высокая доступность, хранилища данных, технологии обработки данных в памяти и поддержка облачных экземпляров.

Совет

В начале 2000-х годов Oracle впервые разработала концепцию "устройства хранилища данных".

Из-за затратности и сложности обслуживания и обновления устаревших локальных сред Oracle многие существующие пользователи Oracle хотят воспользоваться преимуществами инноваций, предоставляемых облачными средами. Современные облачные среды, такие как облако, IaaS и PaaS, позволяют делегировать такие задачи, как обслуживание инфраструктуры и разработка платформы, поставщику облачных служб.

Многие хранилища данных, которые поддерживают сложные аналитические запросы SQL, адресованные большим объемам данных, используют технологии Oracle. В таких хранилищах данных обычно используется многомерная модель данных, например схемы типа "звезда" или "снежинка", а также киоски данных для разных отделов.

Совет

Многие существующие установки Oracle — это хранилища данных, использующие модель мерных данных.

Такое сочетание SQL и многомерных моделей данных в Oracle упрощает переход на Azure Synapse, так как основные концепции модели данных SQL можно перенести. Microsoft рекомендует переносить существующую модель данных в Azure как есть, чтобы сократить риски, усилия и время миграции. Несмотря на то что план миграции может включать изменение базовой модели данных, например переход от модели Инмона к хранилищу данных, имеет смысл изначально выполнить перенос как есть. После первоначальной миграции можно внести изменения в облачную среду Azure, чтобы воспользоваться ее преимуществами: производительностью, эластичной масштабируемостью, встроенными функциями и экономичностью.

Хотя язык SQL стандартизирован, отдельные поставщики иногда реализуют собственные расширения. В результате во время миграции можно обнаружить различия, требующие поиска обходных путей в Azure Synapse.

Использование ресурсов Azure для выполнения миграции на основе метаданных

Можно использовать возможности среды Azure, чтобы автоматизировать и отладить процесс миграции. Такой подход позволяет снизить влияние на существующую среду Oracle, нагрузка на которую уже может быть максимальной.

Фабрика данных Azure — это облачная служба интеграции данных, которая позволяет создавать управляемые данными рабочие процессы в облаке для оркестрации и автоматизации перемещения и преобразования данных. С помощью Фабрики данных можно создавать и включать в расписание управляемые данными рабочие процессы (конвейеры), которые принимают данные из разнородных хранилищ. Фабрика данных может обрабатывать и преобразовывать эти данные с помощью таких служб вычислений, как Azure HDInsight Hadoop, Spark, Azure Data Lake Analytics и машинное обучение Azure.

Azure также включает Службы Azure Database Migration Services, которые помогут вам спланировать и выполнить миграцию из таких сред, как Oracle. Помощник по миграции SQL Server (SSMA) для Oracle может автоматизировать миграцию баз данных Oracle, в том числе в некоторых случаях функции и процедурный код.

Совет

Автоматизируйте процесс миграции с помощью возможностей Фабрики данных Azure.

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

Различия SQL DDL между Oracle и Azure Synapse

Стандарт ANSI SQL определяет базовый синтаксис для команд языка описания данных (DDL). Некоторые команды DDL, такие как CREATE TABLE и CREATE VIEW, являются общими для Oracle и Azure Synapse, но были расширены для предоставления характерных для своей реализации возможностей, таких как индексирование, распределение таблиц и параметры секционирования.

Совет

Команды SQL DDL CREATE TABLE и CREATE VIEW содержат стандартные основные элементы, но также используются для определения вариантов, связанных с конкретной реализацией.

В следующих разделах рассматриваются параметры Oracle, которые необходимо учитывать при переходе на Azure Synapse.

Рекомендации по таблицам и представлениям

При переносе таблиц между разными средами обычно перемещаются только необработанные данные и метаданные, описывающие их физическую миграцию. Другие элементы базы данных из исходной системы, такие как индексы и файлы журнала, обычно не переносятся, так как они могут оказаться не нужны или реализованы по-другому в новой среде. Например, параметр TEMPORARY в синтаксисе Oracle CREATE TABLE эквивалентен префиксу имени таблицы с символом # в Azure Synapse.

Механизмы оптимизации производительности в исходной среде, например индексы, помогают понять, как именно можно оптимизировать производительность в новой целевой среде. Например, если индексы, сопоставленные с битами, часто используются в запросах в исходной среде Oracle, это предполагает необходимость создания некластеризованного индекса в Azure Synapse. Применение других нативных методов оптимизации производительности, например репликации таблиц, может быть более целесообразным, чем прямое создание аналогичных индексов. SSMA для Oracle может предоставлять рекомендации по миграции для распределения таблиц и индексирования.

Совет

Существующие индексы помогают найти кандидатов на индексирование в перенесенном хранилище.

Определения представлений SQL содержат инструкции языка обработки данных SQL (DML), которые определяют представление, как правило, с одной или несколькими инструкциями SELECT. При переносе инструкций CREATE VIEW учитывайте различия в DML между Oracle и Azure Synapse.

Неподдерживаемые типы объектов базы данных Oracle

Функции Oracle можно заменить функциями Azure Synapse. Однако Azure Synapse не поддерживает некоторые объекты базы данных Oracle напрямую. В следующем списке неподдерживаемых объектов базы данных Oracle описывается, как реализовать эквивалентные функциональные возможности в Azure Synapse:

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

    Вы можете узнать, какие столбцы индексируются и их тип индекса, запрашивая таблицы и представления системного каталога, например ALL_INDEXES, DBA_INDEXES, USER_INDEXESи DBA_IND_COL. Кроме того, если включен мониторинг, можно запрашивать представления dba_index_usage или v$object_usage.

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

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

    В Azure Synapse такого же результата можно добиться путем секционирования и(или) использования других типов индексов.

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

    В 2019 году Microsoft объявила, что Azure Synapse будет поддерживать материализованные представления с теми же функциями, что и Oracle. Материализованные представления уже доступны в Azure Synapse в качестве предварительной версии функции.

  • Триггеры в базе данных: в Oracle триггер можно настроить для автоматического запуска при возникновении события активации. Событиями активации могут быть следующие:

    • Выполняется инструкция DML, такая как INSERT, UPDATE или DELETE. Если вы определили триггер, который срабатывает перед инструкцией INSERT в таблице клиента, триггер сработает один раз, прежде чем новая строка будет вставлена в таблицу клиента.

    • Выполняется инструкция DML, такая как CREATE или ALTER. Это событие активации часто используется для записи изменений схемы в целях аудита.

    • Системное событие, например запуск или завершение работы базы данных Oracle.

    • Пользовательское событие, например вход или выход.

    Azure Synapse не поддерживает триггеры базы данных Oracle. Однако вы можете реализовать эквивалентные функциональные возможности с помощью фабрики данных, хотя для этого потребуется выполнить рефакторинг процессов, использующих триггеры.

  • Синонимы: Oracle поддерживает определение синонимов в качестве альтернативных названий нескольких типов объектов баз данных. К этим типам относятся таблицы, представления, последовательности, процедуры, хранимые функции, пакеты, материализованные представления, объекты схемы классов Java, определяемые пользователем объекты или другие синонимы.

    Azure Synapse в настоящее время не поддерживает определение синонимов, хотя если синоним в Oracle ссылается на таблицу или представление, можно определить представление в Azure Synapse, которое бы соответствовало альтернативному названию. Если синоним в Oracle ссылается на функцию или хранимую процедуру, можно заменить синоним в Azure Synapse другой функцией или хранимой процедурой, которая вызывает целевой объект.

  • Пользовательские типы: Oracle поддерживает пользовательские объекты, которые могут содержать ряд отдельных полей, каждое из которых содержит собственное определение и значения по умолчанию. Затем на эти объекты можно ссылаться в определении таблицы так же, как на встроенные типы данных, такие как NUMBER или VARCHAR.

    Azure Synapse в настоящее время не поддерживает пользовательские типы. Если данные, необходимые для переноса, включают пользовательские типы данных, преобразуйте их в плоскую структуру (обычное определение таблицы), а если они представляют собой массивы данных — в отдельную таблицу.

Создание DDL SQL

Для получения эквивалентных определений в Azure Synapse можно отредактировать существующие скрипты Oracle CREATE TABLE и CREATE VIEW. Для этого может потребоваться задействовать модифицированные типы данных и удалить или изменить характерные для Oracle предложения, например TABLESPACE.

Совет

Используйте существующие метаданные Oracle для автоматизации создания DDL CREATE TABLE и CREATE VIEW для Azure Synapse.

В среде Oracle определение текущей таблицы и представления задают таблицы системного каталога. В отличие от пользовательской документации сведения из системного каталога всегда являются полными и в точности соответствуют текущим определениям таблиц. Доступ к сведениям о системных каталогах можно получить с помощью таких служебных программ, как Oracle SQL Developer. Oracle SQL Developer может создавать инструкции DDL CREATE TABLE, которые можно изменить для применения к эквивалентным таблицам в Azure Synapse, как показано на следующем снимке экрана.

Снимок: инструкция create table, сгенерированная Oracle SQL Developer.

Oracle SQL Developer выводит следующую инструкцию CREATE TABLE, которая содержит предложения Oracle, которые следует удалить. Прежде чем выполнять измененную инструкцию CREATE TABLE в Azure Synapse, сопоставьте все неподдерживаемые типы данных.

Снимок: параметр меню быстрого создания DDL в Oracle SQL Developer.

Кроме того, можно автоматически создавать инструкции CREATE TABLE из сведений в таблицах каталога Oracle с помощью SQL-запросов, SSMA или сторонних средств миграции. Этот подход является самым быстрым и согласованным способом создания инструкций CREATE TABLE для многих таблиц.

Совет

Сторонние средства и службы могут автоматизировать задачи сопоставления данных.

Сторонние поставщики предлагают инструменты и сервисы для автоматизации миграции, в том числе для сопоставления типов данных. Если в среде Oracle уже используется сторонний инструмент для извлечения, преобразования и загрузки (ETL) данных, воспользуйтесь им для выполнения всех необходимых преобразований с данными.

Различия SQL DML между Oracle и Azure Synapse

Стандарт ANSI SQL определяет базовый синтаксис для таких команд DML, как SELECT, INSERT, UPDATE и DELETE. Несмотря на то что Oracle и Azure Synapse поддерживают команды DDL, в некоторых случаях они реализуют одну и ту же команду по-разному.

Совет

Стандартные команды SQL DML SELECT, INSERT и UPDATE могут иметь дополнительные параметры синтаксиса в разных средах баз данных.

В следующих разделах рассматриваются характерные для Oracle команды DML, которые необходимо учитывать при переходе на Azure Synapse.

Различия в синтаксисе SQL DML

Между Oracle SQL и Azure Synapse T-SQL существуют некоторые различия в синтаксисе SQL DML:

  • Таблица DUAL: в Oracle есть системная таблица с именем DUAL, которая состоит ровно из одного столбца с именем dummy и одной записи со значением X. Системная таблица DUAL используется, если запрос требует названия таблицы по причине синтаксиса, однако содержимое таблицы не требуется.

    Пример запроса Oracle, использующего таблицу DUAL, — SELECT sysdate from dual;. Эквивалент в Azure Synapse — SELECT GETDATE();. Чтобы упростить миграцию DML, можно создать в Azure Synapse эквивалентную таблицу DUAL с помощью следующего DDL.

    CREATE TABLE DUAL
(
  DUMMY VARCHAR(1)
)
GO
INSERT INTO DUAL (DUMMY)
VALUES ('X')
GO

  • Значения NULL: значение NULL в Oracle является пустой строкой, представленной CHAR, или типом строки VARCHAR с длиной 0. В Azure Synapse и большинстве других баз данных NULL означает что-то другое. Будьте осторожны при переносе данных или процессов, которые обрабатывают или хранят данные, чтобы обеспечить согласованность обработки значений NULL.

  • Синтаксис внешнего соединения Oracle: несмотря на то что более поздние версии Oracle поддерживают синтаксис внешнего соединения ANSI, старые системы Oracle используют собственный синтаксис для внешних соединений, использующих знак плюса (+) в инструкции SQL. Если вы переносите старую среду Oracle, вам может встретиться более старый синтаксис. Например:

    SELECT
    d.deptno, e.job
FROM
    dept d,
    emp e
WHERE
    d.deptno = e.deptno (+)
    AND e.job (+) = 'CLERK'
GROUP BY
    d.deptno, e.job;

    Эквивалентный стандартный синтаксис ANSI:

    SELECT
    d.deptno, e.job
FROM
    dept d
    LEFT OUTER JOIN emp e ON d.deptno = e.deptno
    and e.job = 'CLERK'
GROUP BY
    d.deptno,
    e.job
ORDER BY
    d.deptno, e.job;

  • Данные DATE: в Oracle тип данных DATE может хранить дату и время. Azure Synapse хранит дату и время в отдельных типах данных DATE, TIME и DATETIME. При переносе столбцов Oracle DATE проверьте, хранится ли в них дата и время или только дата. Если в столбцах хранится только дата, сопоставьте их DATE, в противном случае — DATETIME.

  • Арифметика DATE: Oracle поддерживает вычитание одной даты из другой, например SELECT date '2018-12-31' - date '2018-1201' from dual;. В Azure Synapse можно вычитать даты с помощью функции DATEDIFF(), например SELECT DATEDIFF(day, '2018-12-01', '2018-12-31');.

    Oracle может вычитать из дат целые числа, например SELECT hire_date, (hire_date-1) FROM employees;. В Azure Synapse можно добавлять или вычитать целые числа из дат с помощью функции DATEADD().

  • Обновления через представления: в Oracle можно выполнять операции вставки, обновления и удаления в представлении, чтобы обновить базовую таблицу. В Azure Synapse эти операции выполняются в базовой таблице, а не в представлении. Если таблица Oracle обновляется в представлении, возможно, потребуется перепроектировать ETL-обработку данных.

  • Встроенные функции: в следующей таблице показаны различия в синтаксисе и использовании некоторых встроенных функций.

Функция Oracle Description Эквивалент в Synapse
ADD_MONTHS Добавление указанного числа месяцев DATEADD
CAST Преобразование одного встроенного типа данных в другой CAST
DECODE Оценка списка условий CASE, выражение
EMPTY_BLOB Создание пустого значения BLOB-объекта Константа 0x (пустая двоичная строка)
EMPTY_CLOB Создание пустого значения CLOB или NCLOB '' (пустая строка)
INITCAP Перевод первой буквы каждого слова в верхний регистр Определяемая пользователем функция
INSTR Поиск позиции подстроки в строке CHARINDEX
LAST_DAY Получение последней даты месяца EOMONTH
LENGTH Получение длины строки в символах LEN
LPAD Строка левой панели до указанной длины Выражение с использованием параметров REPLICATE, RIGHT и LEFT
MOD Получение остатка в результате деления одного числа на другое Оператор %
MONTHS_BETWEEN Получение количества месяцев между двумя датами DATEDIFF
NVL Замена NULL выражением ISNULL
SUBSTR Возврат подстроки из строки SUBSTRING
TO_CHAR для даты и времени Преобразование даты и времени в строку CONVERT
TO_DATE Преобразование строки в дату и время CONVERT
TRANSLATE Замена одного символа по принципу один к одному Выражения с параметром REPLACE или пользовательской функцией
TRIM Обрезка начальных или конечных символов LTRIM и RTRIM
TRUNC для даты и времени Усечение даты и времени Выражения с параметром CONVERT
UNISTR Преобразование кодовых точек Юникода в символы Выражения с NCHAR

Функции, хранимые процедуры и последовательности

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

На этапе подготовки составьте каталог объектов, которые необходимо перенести, определите метод их обработки и выделите соответствующие ресурсы в плане миграции.

Для автоматизации переноса функций, хранимых процедур и последовательностей можно использовать средства Microsoft, такие как SSMA для Oracle и Azure Database Migration Services, а также сторонние продукты и сервисы для миграции.

Совет

Сторонние продукты и службы могут автоматизировать миграцию элементов, отличных от данных.

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

Функции

Как и большинство СУБД, Oracle поддерживает системные и пользовательские функции в реализации SQL. При переносе устаревшей платформы базы данных в Azure Synapse, как правило, удается перенести общие системные функции без изменений. У некоторых системных функций может быть немного другой синтаксис, однако необходимые изменения можно автоматизировать.

Системные функций Oracle и произвольные пользовательские функции, у которых нет эквивалента в Azure Synapse, можно переписать на языке целевой среды. Определяемые пользователем функции Oracle кодируются на языках PL/SQL, Java или C, а Azure Synapse использует для реализации определяемых пользователем функций язык Transact-SQL.

Хранимые процедуры

Большинство современных СУБД поддерживают хранимые процедуры в базе данных. Oracle предоставляет для этой цели язык PL/SQL. Как правило, хранимая процедура содержит инструкции SQL и процедурную логику, а также может возвращать данные или состояние.

Azure Synapse реализует хранимые процедуры с помощью T-SQL, поэтому потребуется переписать все перенесенные хранимые процедуры на этом языке.

Последовательности

В Oracle последовательность — это именованный объект базы данных, созданный с помощью CREATE SEQUENCE. Последовательность предоставляет уникальные числовые значения с помощью методов CURRVAL и NEXTVAL. Созданные уникальные числа можно использовать как значения суррогатных ключей для первичных ключей. Azure Synapse не реализует CREATE SEQUENCE, однако последовательности можно реализовать с помощью столбцов IDENTITY или кода SQL, который генерирует следующий порядковый номер в ряде.

Использование EXPLAIN для проверки устаревших SQL

Совет

Найдите потенциальные проблемы миграции с помощью реальных запросов из существующих системных журналов запросов.

Если в Azure Synapse переносится аналогичная модель данных с такими же названиями таблиц и столбцов, для тестирования совместимости устаревшего Oracle SQL с Azure Synapse можно поступить следующим образом:

  1. Зафиксировать некоторые наиболее характерные инструкции SQL из журналов запросов устаревшей системы.
  2. Добавить к этим запросам инструкцию EXPLAIN в качестве префикса.
  3. Выполнить инструкции EXPLAIN в Azure Synapse.

Любая несовместимость SQL вызовет ошибку, а сведения об ошибке могут использоваться, чтобы определить масштаб задачи перекодировки. Этот подход не требует загрузки данных в среду Azure, необходимо создать только соответствующие таблицы и представления.

Итоги

Существующие устаревшие установки Oracle обычно реализованы таким образом, что миграция в Azure Synapse является относительно несложной задачей. Обе среды используют SQL для аналитических запросов к большим объемам данных и, как правило, имеют некоторую форму многомерной модели данных. Благодаря этим факторам установки Oracle — хороший кандидат на перенос в Azure Synapse.

Ниже приведены рекомендации по минимизации сложностей при переносе кода SQL из Oracle в Azure Synapse:

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

  • Изучите различия между реализацией Oracle SQL и Azure Synapse.

  • Используйте метаданные и журналы запросов из существующей реализации Oracle, чтобы оценить значение изменения среды. Спланируйте действия, которые позволят минимизировать различия.

  • Автоматизируйте процесс миграции, чтобы свести к минимуму риск, усилия и время миграции. Можно использовать такие средства Microsoft, как Azure Database Migration Services и SSMA.

  • Рассмотрите возможность использования специализированных сторонних инструментов и сервисов для оптимизации миграции.

Следующие шаги

Дополнительные сведения о средствах Microsoft и других поставщиков см. в следующей статье этой серии: Инструменты для переноса хранилища данных Oracle в Azure Synapse Analytics.