Compartilhar via

Minimizar problemas de SQL em migrações do Oracle

  • Artigo

Este artigo é a quinta parte de uma série de sete partes que oferece diretrizes para fazer a migração do Oracle para o Azure Synapse Analytics. O foco deste artigo são as melhores práticas para minimizar problemas de SQL.

Visão geral

Características de ambientes Oracle

O produto de banco de dados Oracle inicial, lançado em 1979, era um banco de dados relacional SQL comercial para aplicativos OLTP (processamento de transações online) – com taxas de transação muito menores do que hoje. Desde essa versão inicial, o ambiente Oracle evoluiu para se tornar muito mais complexo e abrange inúmeros recursos. Os recursos incluem arquiteturas de cliente-servidor, bancos de dados distribuídos, processamento paralelo, análise de dados, alta disponibilidade, armazenamento de dados, técnicas de dados na memória e suporte para instâncias baseadas em nuvem.

Dica

A Oracle foi pioneira do conceito de "dispositivo de data warehouse" no início dos anos 2000.

Devido ao custo e à complexidade de manter e atualizar ambientes Oracle locais herdados, muitos usuários existentes do Oracle desejam aproveitar as inovações fornecidas pelos ambientes de nuvem. Ambientes de nuvem modernos, como nuvem, IaaS e PaaS, permitem delegar tarefas como manutenção de infraestrutura e desenvolvimento de plataforma para o provedor de nuvem.

Muitos data warehouses que dão suporte a consultas SQL analíticas complexas em grandes volumes de dados usam tecnologias da Oracle. Esses data warehouses geralmente têm um modelo de dados dimensional, como esquemas estrela ou snowflake, e usam data marts para departamentos individuais.

Dica

Muitas instalações existentes da Oracle são data warehouses que usam um modelo de dados dimensional.

A combinação de modelos de dados SQL e dimensionais no Oracle simplifica a migração para o Azure Synapse, pois os conceitos de modelo de dados básicos e SQL são transferíveis. A Microsoft recomenda mover o modelo de dados existente no estado em que se encontra para o Azure para reduzir o risco, o esforço e o tempo de migração. Embora seu plano de migração possa incluir uma alteração no modelo de dados subjacente, como uma mudança de um modelo Inmon para um cofre de dados, faz sentido executar inicialmente uma migração no estado em que se encontra. Após a migração inicial, você pode fazer alterações no ambiente de nuvem do Azure para aproveitar o desempenho, a escalabilidade elástica, os recursos internos e os benefícios de custo dele.

Embora a linguagem SQL seja padronizada, fornecedores individuais às vezes implementam extensões proprietárias. Como resultado, você pode encontrar diferenças de SQL durante a migração que exigem soluções alternativas no Azure Synapse.

Usar as instalações do Azure para implementar uma migração controlada por metadados

Automatize e orquestre o processo de migração usando os recursos do ambiente do Azure. Essa abordagem minimiza o impacto no ambiente Oracle existente, que talvez já esteja funcionando próximo da capacidade total.

O Azure Data Factory é um serviço de integração de dados baseado em nuvem que permite criar na nuvem fluxos de trabalho controlados por dados para orquestrar e automatizar a movimentação e a transformação de dados. Você pode usar o Azure Data Factory para criar e agendar fluxos de trabalho controlados por dados (pipelines) que ingerem dados de diferentes armazenamentos de dados. O Data Factory é capaz de processar e transformar dados usando serviços de computação como o Azure HDInsight Hadoop, Spark, Azure Data Lake Analytics e o Azure Machine Learning.

O Azure também inclui os Serviços de Migração de Banco de Dados do Azure para ajudar você a planejar e executar uma migração de ambientes como o Oracle. O SSMA (Assistente de Migração do SQL Server) para Oracle pode automatizar a migração de bancos de dados Oracle, incluindo, em alguns casos, funções e código de procedimento.

Dica

Automatize o processo de migração usando recursos do Azure Data Factory.

Quando você planeja usar as instalações do Azure, como o Data Factory, para gerenciar o processo de migração, primeiro crie metadados que listem todas as tabelas de dados que precisam ser migradas e a localização delas.

Diferenças de DDL SQL entre Oracle e Azure Synapse

O padrão ANSI SQL define a sintaxe básica para comandos DDL (Linguagem de Definição de Dados). Alguns comandos DDL, como CREATE TABLE e CREATE VIEW, são comuns ao Oracle e ao Azure Synapse, mas foram estendidas para fornecer recursos específicos de implementação, como indexação, distribuição de tabelas e opções de particionamento.

Dica

Os comandos de DDL de SQL CREATE TABLE e CREATE VIEW têm elementos principais padrão, mas também são usados para definir opções específicas de implementação.

As seções a seguir discutem as opções específicas do Oracle que precisam ser consideradas durante uma migração para o Azure Synapse.

Considerações de tabela/exibição

Ao migrar tabelas entre ambientes diferentes, normalmente só é possível migrar dados brutos e os metadados que os descrevem. Outros elementos de banco de dados do sistema de origem, como índices e arquivos de log, geralmente não são migrados porque podem ser desnecessários ou implementados de forma diferente no novo ambiente. Por exemplo, a opção TEMPORARY dentro da sintaxe CREATE TABLE do Oracle é equivalente à prefixação de um nome de tabela com um caractere # no Azure Synapse.

Otimizações de desempenho no ambiente de origem, como índices, indicam onde você pode adicionar otimização de desempenho no novo ambiente de destino. Por exemplo, se índices mapeados por bits são frequentemente usados em consultas dentro do ambiente Oracle de origem, isso sugere que um índice não clusterizado deve ser criado dentro do Azure Synapse. Outras técnicas nativas de otimização de desempenho, como a replicação de tabela, podem ser mais aplicáveis do que uma criação direta de índice por semelhança. O SSMA para Oracle pode fornecer recomendações de migração para distribuição e indexação de tabelas.

Dica

Índices existentes indicam candidatos à indexação no warehouse migrado.

As definições de exibição do SQL contêm instruções de DML (Linguagem de Manipulação de Dados) SQL que definem o modo de exibição, normalmente com uma ou mais instruções SELECT. Ao migrar instruções CREATE VIEW, leve em conta as diferenças de DML entre o Oracle e Azure Synapse.

Tipos de objeto de banco de dados Oracle sem suporte

Os recursos específicos do Oracle normalmente podem ser substituídos por recursos do Azure Synapse. No entanto, alguns objetos de banco de dados Oracle não têm suporte direto no Azure Synapse. A lista a seguir de objetos de banco de dados Oracle sem suporte descreve como você pode obter uma funcionalidade equivalente no Azure Synapse:

  • Opções de indexação: no Oracle, várias opções de indexação, como índices mapeados por bit, índices baseados em função e índices de domínio, não têm equivalente direto em Azure Synapse. Embora o Azure Synapse não dê suporte a esses tipos de índice, você pode obter uma redução semelhante na E/S do disco usando tipos de índice definidos pelo usuário e/ou particionando. A redução de E/S do disco aprimora o desempenho da consulta.

    Você pode descobrir quais colunas são indexadas e o tipo de índice delas consultando tabelas e exibições do catálogo do sistema, comoALL_INDEXES, DBA_INDEXES, USER_INDEXES e DBA_IND_COL. Você também poderá consultar as exibições dba_index_usage ou v$object_usage quando o monitoramento estiver habilitado.

    Os recursos do Azure Synapse, como processamento de consulta paralela e armazenamento em cache de dados e resultados na memória, tornam provável que menos índices sejam necessários para que os aplicativos de data warehouse alcancem excelentes metas de desempenho.

  • Tabelas clusterizados: as tabelas Oracle podem ser organizadas para que as linhas de tabela acessadas juntas com frequência (com base em um valor comum) sejam armazenadas fisicamente juntas. Essa estratégia reduz a E/S do disco quando os dados são recuperados. O Oracle também tem uma opção de cluster de hash para tabelas individuais, que aplica um valor de hash à chave de cluster e armazena fisicamente linhas com o mesmo valor de hash juntos.

    No Azure Synapse, você pode obter um resulatdo semelhante particionando e/ou usando outros índices.

  • Exibições materializadas: o Oracle dá suporte a exibições materializadas e recomenda uma ou mais delas para tabelas grandes com muitas colunas em que apenas algumas delas são usadas regularmente em consultas. Exibições materializadas serão atualizadas automaticamente pelo sistema quando os dados na tabela base forem atualizados.

    Em 2019, a Microsoft anunciou que o Azure Synapse dará suporte a exibições materializadas com a mesma funcionalidade que no Oracle. As exibições materializadas agora são uma versão prévia do recurso no Azure Synapse.

  • Gatilhos no banco de dados: no Oracle, um gatilho pode ser configurado para ser executado automaticamente quando ocorre um evento de gatilho. Os eventos de gatilho podem ser:

    • Uma instrução DML, como INSERT, UPDATE ou DELETE, é executada. Se você definiu um gatilho que é acionado antes de uma instrução INSERT em uma tabela do cliente, o gatilho será acionado uma vez antes de uma nova linha ser inserida na tabela do cliente.

    • Uma instrução DDL, como CREATE ou ALTER, é executada. Esse evento de gatilho geralmente é usado para registrar alterações de esquema para fins de auditoria.

    • Um evento do sistema, como inicialização ou desligamento do banco de dados Oracle.

    • Um evento de usuário, como logon ou logoff.

    O Azure Synapse não dá suporte a gatilhos de banco de dados Oracle. No entanto, você pode obter uma funcionalidade equivalente usando o Data Factory, embora isso exija que você refatore os processos que usam gatilhos.

  • Sinônimos: o Oracle dá suporte à definição de sinônimos como nomes alternativos para vários tipos de objeto de banco de dados. Esses tipos incluem tabelas, exibições, sequências, procedimentos, funções armazenadas, pacotes, exibições materializadas, objetos de esquema de classe Java, objetos definidos pelo usuário ou outros sinônimos.

    No momento, o Azure Synapse não dá suporte à definição de sinônimos, embora se um sinônimo no Oracle se referir a uma tabela ou exibição, você poderá definir uma exibição no Azure Synapse para corresponder ao nome alternativo. Se um sinônimo no Oracle se referir a uma função ou um procedimento armazenado, você poderá substituir o sinônimo no Azure Synapse por outra função ou procedimento armazenado que chame o destino.

  • Tipos definidos pelo usuário: o Oracle dá suporte a objetos definidos pelo usuário que podem conter uma série de campos individuais, cada um com a própria definição e valores padrão. Esses objetos podem ser referenciados dentro de uma definição de tabela da mesma forma que tipos de dados internos, como NUMBER ou VARCHAR.

    No momento, o Azure Synapse não dá suporte a tipos definidos pelo usuário. Se os dados que você precisa migrar incluirem tipos de dados definidos pelo usuário, "nivele-os" em uma definição de tabela convencional ou, se forem matrizes de dados, normalize-os em uma tabela separada.

Geração de DDL SQL

Você pode editar scripts Oracle CREATE TABLE e CREATE VIEW existentes para alcançar definições equivalentes no Azure Synapse. Para isso, talvez seja necessário usar tipos de dados modificados e remover ou modificar cláusulas específicas do Oracle, como TABLESPACE.

Dica

Use os metadados existentes do Oracle para automatizar a geração de DDL CREATE TABLE e CREATE VIEW para o Azure Synapse.

No ambiente Oracle, as tabelas do catálogo do sistema especificam a tabela e a definição de exibição atuais. Ao contrário da documentação mantida pelo usuário, as informações do catálogo do sistema estão sempre completas e em sincronia com as definições de tabela atuais. Você pode acessar informações do catálogo do sistema usando utilitários como o Oracle SQL Developer. O Oracle SQL Developer pode gerar instruções DDL CREATE TABLE que você pode editar para aplicar a tabelas equivalentes no Azure Synapse, conforme mostrado na próxima captura de tela.

Captura de tela mostrando a instrução CREATE TABLE gerada pelo Oracle SQL Developer.

O Oracle SQL Developer gera a instrução CREATE TABLE a seguir, que contém cláusulas específicas do Oracle que você deve remover. Mapeie qualquer tipo de dados sem suporte antes de executar sua instrução CREATE TABLE modificada no Azure Synapse.

Captura de tela mostrando a opção de menu DLL Rápido no Oracle SQL Developer.

Como alternativa, você pode gerar instruções CREATE TABLE automaticamente com base nas informações nas tabelas de catálogo Oracle usando consultas SQL, SSMA ou ferramentas de migração de terceiros. Essa abordagem é a maneira mais rápida e consistente de gerar instruções CREATE TABLE para muitas tabelas.

Dica

Ferramentas e serviços de terceiros podem automatizar as tarefas de mapeamento de dados.

Fornecedores de terceiros oferecem ferramentas e serviços para automatizar a migração, incluindo o mapeamento de tipos de dados. Se uma ferramenta ETL de terceiros já estiver em uso no ambiente Oracle, use essa ferramenta para implementar transformações de dados necessárias.

Diferenças de DDL SQL entre Oracle e Azure Synapse

O padrão ANSI de SQL define a sintaxe básica para comandos DML, como SELECT, INSERT, UPDATE e DELETE. Embora o Oracle e o Azure Synapse deem suporte a comandos DDL, em alguns casos eles implementam o mesmo comando de maneira diferente.

Dica

Os comandos DML SQL SELECT, INSERT e UPDATE padrão podem ter opções de sintaxe adicionais em diferentes ambientes de banco de dados.

As seções a seguir discutem os comandos DML específicos do Oracle que precisam ser consideradas durante uma migração para o Azure Synapse.

Diferenças de sintaxe de DML SQL

Há algumas diferenças de sintaxe DML SQL entre o Oracle SQL e o Azure Synapse T-SQL:

  • A tabela DUAL: o Oracle tem uma tabela do sistema chamada DUAL que é composta exatamente por uma coluna chamada dummy e um registro com o valor X. A tabela do sistema DUAL é usada quando uma consulta requer um nome de tabela por motivos de sintaxe, mas o conteúdo da tabela não é necessário.

    Um exemplo de consulta Oracle que usa a tabela DUAL é SELECT sysdate from dual;. O equivalente no Azure Synapse é SELECT GETDATE();. Para simplificar a migração do DML, você pode criar uma tabela DUAL equivalente no Azure Synapse usando o DDL a seguir.

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

  • Valores NULL: um valor NULL no Oracle é uma cadeia de caracteres vazia, representada por um tipo de cadeia de caracteres CHAR ou VARCHAR de comprimento 0. No Azure Synapse e na maioria dos outros bancos de dados, NULL significa outra coisa. Tenha cuidado ao migrar dados ou ao migrar processos que manipulam ou armazenam dados para garantir que os valores NULL sejam tratados de maneira consistente.

  • Sintaxe de junção externa Oracle: embora versões mais recentes da Oracle deem suporte à sintaxe de junção externa ANSI, sistemas Oracle mais antigos usam uma sintaxe proprietária para junções externas que usa um sinal de adição (+) dentro da instrução SQL. Se você estiver migrando um ambiente Oracle mais antigo, poderá encontrar a sintaxe mais antiga. Por exemplo:

    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;

    A sintaxe ANSI padrão equivalente é:

    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;

  • Dados DATE: no Oracle, o tipo de dados DATE pode armazenar data e hora. O Azure Synapse armazena data e hora nos tipos de dados separados DATE, TIME e DATETIME. Quando você estiver migrando colunas DATE Oracle, verifique se elas armazenam data e hora ou apenas uma data. Se eles armazenarem apenas uma data, mapeie a coluna para DATE. Caso contrário, para DATETIME.

  • Aritmética DATE: o Oracle dá suporte à subtração de uma data de outra, por exemplo SELECT date '2018-12-31' - date '2018-1201' from dual;. No Azure Synapse, você pode subtrair datas usando a função DATEDIFF(), por exemplo SELECT DATEDIFF(day, '2018-12-01', '2018-12-31');.

    O Oracle pode subtrair inteiros de datas, por exemplo SELECT hire_date, (hire_date-1) FROM employees;. No Azure Synapse, você pode adicionar ou subtrair inteiros de datas usando a função DATEADD().

  • Atualizações por meio de exibições: no Oracle, você pode executar operações de inserção, atualização e exclusão em um modo de exibição para atualizar a tabela subjacente. No Azure Synapse, você executa essas operações em uma tabela base, não em um modo de exibição. Talvez seja necessário recriar o processamento de ETL se uma tabela Oracle for atualizada por meio de uma exibição.

  • Funções internas: a tabela a seguir mostra as diferenças na sintaxe e no uso de algumas funções internas.

Função Oracle Descrição Equivalente no Synapse
ADD_MONTHS Adicionar um número especificado de meses DATEADD
CAST Converter um tipo de dados interno em outro CAST
DECODE Avaliar uma lista de condições expressão CASE
EMPTY_BLOB Criar um valor blob vazio Constante 0x (cadeia de caracteres binários vazia)
EMPTY_CLOB Criar um valor CLOB ou NCLOB vazio '' (cadeia de caracteres vazia)
INITCAP Colocar a primeira letra de cada palavra em maiúscula Função definida pelo usuário
INSTR Localizar a posição de uma substring em uma cadeia de caracteres CHARINDEX
LAST_DAY Obter a última data do mês EOMONTH
LENGTH Obter o comprimento da cadeia de caracteres em caracteres LEN
LPAD Cadeia de caracteres do painel esquerdo para o comprimento especificado Expressão usando REPLICA, RIGHT e LEFT
MOD Obter o restante de uma divisão de um número por outro Operador %
MONTHS_BETWEEN Obter o número de meses entre duas datas DATEDIFF
NVL Substituir NULL por uma expressão ISNULL
SUBSTR Retornar uma substring de uma cadeia de caracteres SUBSTRING
TO_CHAR para datetime Converter datetime em cadeia de caracteres CONVERT
TO_DATE Converter uma cadeia de caracteres em um datetime CONVERT
TRANSLATE Substituição de caractere único um para um Expressões usando REPLACE ou uma função definida pelo usuário
TRIM Cortar caracteres à esquerda ou à direita LTRIM e RTRIM
TRUNC para datetime Truncar datetime Expressões usando CONVERT
UNISTR Converter pontos de código Unicode em caracteres Expressões usando NCHAR

Funções, procedimentos armazenados e sequências

Muitas vezes, ao migrar um data warehouse de um ambiente herdado maduro como o Oracle, provavelmente é preciso migrar elementos que não sejam tabelas e exibições simples. Para funções, procedimentos armazenados e sequências, verifique se as ferramentas do ambiente do Azure podem substituir a funcionalidade, pois geralmente é mais eficaz usar ferramentas internas do Azure do que recodificar as funções Oracle.

Como parte da fase de preparação, crie um inventário de objetos que precisem ser migrados, defina um método para tratá-los e aloque os recursos corretos no seu plano de migração.

Ferramentas da Microsoft, como o SSMA para Oracle e os Serviços de Migração de Banco de Dados do Azure ou produtos e serviços de migração de terceiros, podem automatizar a migração de funções, procedimentos armazenados e sequências.

Dica

Produtos e serviços de terceiros podem automatizar a migração de elementos que não são dados.

As seções a seguir discutem a migração de funções, procedimentos armazenados e sequências.

Funções

Como a maioria dos produtos de banco de dados, o Oracle oferece suporte a funções do sistema e definidas pelo usuário em uma implementação SQL. Quando você migra uma plataforma de banco de dados herdada para o Azure Synapse, você geralmente pode migrar funções comuns do sistema sem alterações. Algumas funções do sistema podem ter sintaxes um pouco diferentes, mas todas as alterações necessárias podem ser automatizadas.

No caso de funções do sistema Oracle ou arbitrárias definidas pelo usuário sem equivalente no Azure Synapse, recodifique-as usando uma linguagem de ambiente de destino. As funções definidas pelo usuário do Oracle são codificadas em PL/SQL, Java ou C. O Azure Synapse usa a linguagem Transact-SQL para implementar funções definidas pelo usuário.

Procedimentos armazenados

A maioria dos produtos de banco de dados modernos permite que os procedimentos sejam armazenados no banco de dados. O Oracle fornece a linguagem PL/SQL para essa finalidade. Um procedimento armazenado normalmente contém instruções SQL e lógica de procedimento, retornando dados ou um status.

O Azure Synapse dá suporte a procedimentos armazenados usando T-SQL, portanto, você precisará recodificar todos os procedimentos armazenados migrados em T-SQL.

Sequências

No Oracle, uma sequência é um objeto de banco de dados nomeado criado usando CREATE SEQUENCE. Uma sequência fornece valores numéricos exclusivos por meio do método CURRVAL e NEXTVAL. Você pode usar números exclusivos gerados como valores de chave substituta para chaves primárias. O Azure Synapse não implementa CREATE SEQUENCE, mas você pode implementar sequências usando colunas IDENTITY ou o código SQL que gera o próximo número de sequência em uma série.

Use EXPLAIN para validar o SQL herdado

Dica

Use consultas reais dos logs de consulta do sistema existentes para encontrar possíveis problemas de migração.

Supondo que um modelo de dados migrados por semelhança no Azure Synapse com os mesmos nomes de tabela e coluna, uma forma de testar o Oracle SQL herdado para compatibilidade com o Azure Synapse é:

  1. Captura de algumas instruções SQL representativas dos logs de histórico de consultas do sistema herdados.
  2. Adicione a essas consultas um prefixo de instrução EXPLAIN.
  3. Execute as instruções EXPLAIN no Azure Synapse.

Qualquer SQL incompatível gerará um erro e as informações de erro podem ser usadas para determinar a escala da tarefa de recodificação. Essa abordagem não exige que você carregue nenhum dado no ambiente do Azure, você só precisa criar as tabelas e exibições relevantes.

Resumo

As instalações do Oracle herdadas existentes normalmente são implementadas de uma forma que torna a migração para Azure Synapse relativamente simples. Os ambientes usam SQL para consultas analíticas em grandes volumes de dados e geralmente usam alguma forma de modelo de dados dimensional. Esses fatores tornam as instalações Oracle um bom candidato para migração para o Azure Synapse.

Para resumir, nossas recomendações para minimizar a tarefa de migrar o código SQL do Oracle para Azure Synapse são:

  • Migrar o modelo de dados existente no estado em que se encontra para minimizar o risco, o esforço e o tempo de migração, mesmo que um modelo de dados diferente seja planejado, como um cofre de dados.

  • Entenda as diferenças entre a implementação do Oracle SQL e a implementação do Azure Synapse.

  • Use os metadados e consulte logs da implementação Oracle existente para avaliar a alteração do ambiente. Planeje uma abordagem para atenuar as diferenças.

  • Automatize o processo de migração para minimizar o risco, o esforço e o tempo de migração. Você pode usar ferramentas da Microsoft, como os Serviços de Migração de Banco de Dados do Azure e o SSMA.

  • Considere usar ferramentas e serviços de terceiros especializados para simplificar a migração.

Próximas etapas

Para saber mais sobre as ferramentas da Microsoft e de terceiros, confira o próximo artigo nesta série: Ferramentas para migração de data warehouse do Oracle para o Azure Synapse Analytics.