Minimizar problemas de SQL para migrações Oracle

Este artigo é a quinta parte de uma série de sete partes que fornece orientação sobre como migrar do Oracle para o Azure Synapse Analytics. O foco deste artigo são as práticas recomendadas para minimizar problemas de SQL.

Descrição geral

Características dos ambientes Oracle

O produto de banco de dados inicial da Oracle, lançado em 1979, era um banco de dados relacional SQL comercial para aplicativos OLTP (processamento de transações on-line) — com taxas de transação muito mais baixas do que as atuais. Desde essa versão inicial, o ambiente Oracle evoluiu para se tornar muito mais complexo e engloba vários recursos. Os recursos incluem arquiteturas cliente-servidor, bancos de dados distribuídos, processamento paralelo, análise de dados, alta disponibilidade, data warehousing, técnicas de dados na memória e suporte para instâncias baseadas em nuvem.

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

Muitos data warehouses que suportam consultas SQL analíticas complexas em grandes volumes de dados usam tecnologias Oracle. Esses armazéns de dados geralmente têm um modelo de dados dimensional, como esquemas de estrelas ou flocos de neve, e usam data marts para departamentos individuais.

A combinação de SQL e modelos de dados dimensionais no Oracle simplifica a migração para o Azure Synapse porque os conceitos de modelo de dados SQL e básico são transferíveis. A Microsoft recomenda mover seu 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 seu desempenho, escalabilidade elástica, recursos internos e benefícios de custo.

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

Usar os recursos do Azure para implementar uma migração orientada por metadados

Você pode automatizar e orquestrar o processo de migração usando os recursos do ambiente do Azure. Essa abordagem minimiza o impacto no desempenho no ambiente Oracle existente, que pode já estar sendo executado perto da capacidade.

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

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

Quando você estiver planejando usar recursos 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 seu local.

Diferenças de DDL do SQL entre Oracle e Azure Synapse

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

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 sobre tabela/exibição

Quando você migra tabelas entre ambientes diferentes, normalmente apenas os dados brutos e os metadados que os descrevem são migrados fisicamente. 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 TEMPORARY opção dentro da sintaxe do CREATE TABLE Oracle é equivalente ao prefixo de um nome de tabela com o # caractere na Sinapse do Azure.

As 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 forem usados com freqüência em consultas no ambiente Oracle de origem, isso sugere que um índice não clusterizado deve ser criado no Azure Synapse. Outras técnicas nativas de otimização de desempenho, como a replicação de tabelas, podem ser mais aplicáveis do que a criação direta de índices semelhantes. O SSMA for Oracle pode fornecer recomendações de migração para distribuição e indexação de tabelas.

As definições de exibição SQL contêm instruções DML (SQL Data Manipulation Language) que definem a exibição, geralmente com uma ou mais SELECT instruções. Ao migrar CREATE VIEW instruções, leve em consideração as diferenças de DML entre Oracle e Azure Synapse.

Tipos de objeto de banco de dados Oracle não suportados

Os recursos específicos do Oracle geralmente podem ser substituídos pelos 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 funcionalidade equivalente no Azure Synapse:

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

  Você pode descobrir quais colunas são indexadas e seu tipo de índice consultando tabelas e exibições do catálogo do sistema, como ALL_INDEXES, DBA_INDEXES, USER_INDEXESe DBA_IND_COL. Ou, você pode consultar os modos de exibição ou v$object_usage quando o dba_index_usage monitoramento estiver habilitado.

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

• Tabelas clusterizadas: as tabelas Oracle podem ser organizadas de modo que as linhas da tabela que são frequentemente acessadas juntas (com base em um valor comum) sejam fisicamente armazenadas juntas. Essa estratégia reduz a E/S de 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 juntas.

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

• Visualizações materializadas: a Oracle suporta visualizações materializadas e recomenda uma ou mais delas para tabelas grandes com muitas colunas onde apenas algumas colunas são usadas regularmente em consultas. As visualizações materializadas são atualizadas automaticamente pelo sistema quando os dados na tabela base são atualizados.

  Em 2019, a Microsoft anunciou que o Azure Synapse suportará visualizações materializadas com a mesma funcionalidade do Oracle. As vistas materializadas são agora uma funcionalidade de pré-visualização no Azure Synapse.

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

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

  • Uma instrução DDL, como CREATE ou ALTER, é executada. Esse evento de acionamento é frequentemente 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 login ou logout.

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

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

  Atualmente, o Azure Synapse não oferece suporte à definição de sinônimos, embora se um sinônimo no Oracle se referir a uma tabela ou exibição, você poderá definir um modo de exibição no Azure Synapse para corresponder ao nome alternativo. Se um sinônimo no Oracle se referir a uma função ou 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 suporta objetos definidos pelo usuário que podem conter uma série de campos individuais, cada um com sua própria definição e valores padrão. Esses objetos podem ser referenciados dentro de uma definição de tabela da mesma forma que os tipos de dados internos como NUMBER ou VARCHAR.

  Atualmente, o Azure Synapse não oferece suporte a tipos definidos pelo usuário. Se os dados que você precisa migrar incluírem 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 Oracle CREATE TABLE e CREATE VIEW scripts existentes para obter definições equivalentes no Azure Synapse. Para fazer isso, talvez seja necessário usar tipos de dados modificados e remover ou modificar cláusulas específicas do Oracle, como TABLESPACE.

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

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

Como alternativa, você pode gerar CREATE TABLE automaticamente instruções a partir das 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 CREATE TABLE instruções para muitas tabelas.

Fornecedores terceirizados 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 quaisquer transformações de dados necessárias.

Diferenças do SQL DML entre Oracle e Azure Synapse

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

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

Diferenças de sintaxe do SQL DML

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

• DUAL table: Oracle tem uma tabela do sistema nomeada DUAL que consiste exatamente em uma coluna nomeada dummy e um registro com o valor X. A DUAL tabela do sistema é 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 DUAL tabela é SELECT sysdate from dual;. O equivalente do Azure Synapse é SELECT GETDATE();. Para simplificar a migração de DML, você pode criar uma tabela equivalente DUAL no Azure Synapse usando a seguinte DDL.

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

• NULL valores: um NULL valor no Oracle é uma cadeia de caracteres vazia, representada por um CHAR tipo de comprimento ou VARCHAR cadeia de caracteres 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 NULL os valores sejam tratados de forma consistente.

• Sintaxe de junção externa do Oracle: embora as versões mais recentes do Oracle ofereçam suporte à sintaxe de junção externa ANSI, os 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 padrão ANSI 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;
  

• DATE data: no Oracle, o tipo de DATE dados pode armazenar data e hora. O Azure Synapse armazena data e hora em tipos de dados e TIME, separados.DATEDATETIME Ao migrar colunas Oracle DATE , 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.

• DATE aritmética: o Oracle suporta a 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 DATEDIFF() função, por exemplo SELECT DATEDIFF(day, '2018-12-01', '2018-12-31');.

  A 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 DATEADD() função.

• Atualizações via modos de exibição: no Oracle, você pode executar operações de inserção, atualização e exclusão em uma 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 reformular 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ções, procedimentos armazenados e sequências

Ao migrar um data warehouse de um ambiente maduro como o Oracle, você provavelmente precisará migrar elementos diferentes de tabelas e exibições simples. Para funções, procedimentos armazenados e sequências, verifique se as ferramentas no ambiente do Azure podem substituir suas funcionalidades, pois geralmente é mais eficiente usar ferramentas internas do Azure do que recodificar as funções do Oracle.

Como parte da fase de preparação, crie um inventário de objetos que precisam ser migrados, defina um método para manipulá-los e aloque recursos apropriados em seu plano de migração.

Ferramentas da Microsoft, como SSMA para Oracle e 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.

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

Funções

Como acontece com a maioria dos produtos de banco de dados, o Oracle suporta funções definidas pelo sistema e pelo usuário dentro de uma implementação SQL. Ao migrar 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 uma sintaxe ligeiramente diferente, mas você pode automatizar quaisquer alterações necessárias.

Para funções do sistema Oracle ou funções arbitrárias definidas pelo usuário que não têm equivalente no Azure Synapse, recodifice essas funções usando a linguagem do ambiente de destino. As funções definidas pelo usuário 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 suporta procedimentos de armazenamento dentro do banco de dados. A Oracle fornece a linguagem PL/SQL para este fim. Um procedimento armazenado normalmente contém instruções SQL e lógica de procedimento e retorna dados ou um status.

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

Sequências

Em Oracle, uma sequência é um objeto de banco de dados nomeado, criado usando CREATE SEQUENCE. Uma sequência fornece valores numéricos exclusivos através dos CURRVAL métodos e NEXTVAL . Você pode usar os 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 IDENTITY colunas ou código SQL que gera o próximo número de sequência em uma série.

Use EXPLAIN para validar SQL herdado

Supondo um modelo de dados migrado semelhante no Azure Synapse com os mesmos nomes de tabela e coluna, uma maneira de testar a compatibilidade do Oracle SQL herdado com o Azure Synapse é:

1. Capture algumas instruções SQL representativas dos logs de histórico de consultas do sistema herdado.
2. Prefira essas consultas com a EXPLAIN instrução.
3. Execute as EXPLAIN instruções 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 herdadas existentes do Oracle normalmente são implementadas de uma forma que torna a migração para o Azure Synapse relativamente simples. Ambos os ambientes usam SQL para consultas analíticas em grandes volumes de dados e, geralmente, usam alguma forma de modelo de dados dimensionais. Esses fatores tornam as instalações do Oracle um bom candidato para a migração para o Azure Synapse.

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

• Migre seu modelo de dados existente no estado em que se encontra para minimizar riscos, esforços e tempo de migração, mesmo que um modelo de dados diferente esteja 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 logs de consulta da implementação Oracle existente para avaliar o impacto da alteração do ambiente. Planeje uma abordagem para mitigar as diferenças.

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

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

Próximos passos

Para saber mais sobre a Microsoft e ferramentas de terceiros, consulte o próximo artigo desta série: Ferramentas para migração de data warehouse Oracle para o Azure Synapse Analytics.