Como migrar o Teradata para o BigQuery: visão geral de conversão de consultas

Última atualização: setembro de 2020
Notas da versão

Este documento faz parte de uma série que discute a migração de seu esquema e dados do Teradata para o BigQuery. Neste documento, fornecemos uma visão geral da conversão de instruções SQL gravadas para que o Teradata seja compatível com o BigQuery.

A série que discute as especificidades da transição do Teradata consiste nas partes a seguir:

Para uma visão geral da transição de um armazenamento de dados local para o BigQuery no Google Cloud, consulte a série que começa com Como migrar armazenamentos de dados para o BigQuery: introdução e visão geral

Introdução

O BigQuery e o Teradata Database (em inglês) estão em conformidade com o padrão ANSI/ISO SQL:2011. Além disso, a Teradata criou algumas extensões para o padrão SQL para ativar funcionalidades específicas da Teradata.

Por outro lado, o BigQuery não é compatível com essas extensões proprietárias. Portanto, algumas das suas consultas talvez precisem ser refatoradas durante a migração da Teradata para o BigQuery. Ter consultas que usam apenas o padrão ANSI/ISO SQL compatível com BigQuery ajuda a garantir a portabilidade e faz com que suas consultas sejam independentes do armazenamento de dados subjacente.

Este documento aborda alguns dos desafios que podem ser encontrados ao migrar consultas SQL da Teradata para o BigQuery. Ele explica quando essas conversões devem ser aplicadas no contexto de uma migração organizada de ponta a ponta.

Diferenças do SQL da Teradata

Nesta seção, discutiremos brevemente diferenças notáveis entre o SQL da Teradata e o SQL padrão do BigQuery e algumas estratégias para converter entre os dois dialetos. A lista de diferenças apresentadas neste documento não é exaustiva. Para mais informações, consulte a referência de conversão de SQL da Teradata para o BigQuery.

Linguagem de definição de dados

A Linguagem de definição de dados (DDL, na sigla em inglês) é usada para definir o esquema do banco de dados. Ela inclui um subconjunto de instruções SQL, como CREATE, ALTER e DROP.

Na maioria das vezes, essas instruções são equivalentes entre o Teradata SQL e o SQL padrão. Veja a seguir uma lista não conclusiva de exceções notáveis:

  • As opções de manipulação de índice não são compatíveis com o BigQuery, como CREATE INDEX e PRIMARY INDEX. O BigQuery não usa índices ao consultar seus dados. Ele produz resultados rápidos graças ao modelo subjacente que usa Dremel, as técnicas de armazenamento que usam o Capacitore a arquitetura massivamente paralela.
  • Restrições, que são verificações aplicadas a colunas individuais ou a uma tabela inteira. O BigQuery é compatível apenas com restrições NOT NULL.
  • MULTISET (em inglês), que é usado para permitir linhas duplicadas no Teradata.
  • CASESPECIFIC (em inglês), que especifica o caso para comparações e agrupamentos de dados de caracteres.

Tipos de dados

O BigQuery oferece suporte a um conjunto mais conciso de tipos de dados que a Teradata. Os grupos de tipos da Teradata são mapeados para um único tipo de dados SQL padrão. Por exemplo:

  • INTEGER, SMALLINT, BYTEINT e BIGINT são mapeados para INT64.
  • CLOB, JSON, XML, UDT e outros tipos que contêm campos de caracteres grandes são mapeados para STRING.
  • Tipos BLOB, BYTE e VARBYTE que contêm informações binárias são mapeados para BYTES.

Para datas, os principais tipos (DATE, TIME e TIMESTAMP) são equivalentes no Teradata e no BigQuery. No entanto, outros tipos de data especializados do Teradata precisam ser mapeados, como a seguir:

  • TIME_WITH_TIME_ZONE para TIME
  • TIMESTAMP_WITH_TIME_ZONE para TIMESTAMP
  • INTERVAL_HOUR, INTERVAL_MINUTE e outros INTERVAL_* tipos são mapeados para INT64 no BigQuery
  • PERIOD(DATE), PERIOD(TIME) e outros PERIOD(*) tipos são mapeados para STRING

Matrizes multidimensionais não são diretamente compatíveis com o BigQuery. Em vez disso, você cria uma matriz de estruturas. Cada estrutura contém um campo do tipo ARRAY.

A instrução SELECT

A sintaxe da instrução SELECT é geralmente compatível entre a Teradata e o BigQuery. Nesta seção, observaremos as diferenças que frequentemente precisam ser tratadas durante a migração.

Identificadores

O BigQuery permite usar como identificador: projetos; conjuntos de dados, tabelas ou visualizações e colunas.

Como um produto sem servidor, o BigQuery não tem um conceito de cluster ou ambiente ou endpoint fixo, por isso, o projeto especifica a hierarquia de recursos do conjunto de dados.

Em uma instrução SELECT na Teradata, é possível usar nomes de colunas totalmente qualificados. O BigQuery sempre faz referência a nomes de coluna em tabelas ou aliases, e nunca em projetos ou conjuntos de dados.

Por exemplo, veja algumas opções para lidar com identificadores no BigQuery.

Colunas inferidas implicitamente da tabela:

SELECT
 c
FROM
 project.dataset.table

Ou usando uma referência explícita da tabela:

SELECT
 table.c
FROM
 project.dataset.table

Ou usando um alias explícito da tabela:

SELECT
 t.c
FROM
 project.dataset.table t
Referências de alias

Em uma instrução SELECT no Teradata, aliases podem ser definidos e referidos dentro da mesma consulta. Por exemplo, no snippet a seguir, flag é definido como um alias de coluna e, em seguida, mencionado imediatamente na instrução CASE em anexo.

SELECT
 F AS flag,
 CASE WHEN flag = 1 THEN ...

No SQL padrão, não são permitidas referências entre colunas dentro da mesma consulta. Para converter, mova a lógica para uma consulta aninhada:

SELECT
 q.*,
 CASE WHEN q.flag = 1 THEN ...
FROM (
 SELECT
   F AS flag,
   ...
) AS q

O marcador de exemplo F poderia ser uma consulta aninhada que retorna uma única coluna.

Como filtrar com LIKE

No Teradata, o operador LIKE ANY é usado para filtrar os resultados para um determinado conjunto de opções possíveis. Exemplo:

SELECT*
FROM t1
WHERE a LIKE ANY ('string1', 'string2')

Para converter instruções que têm esse operador no SQL padrão, divida a lista após ANY em vários predicados OR:

SELECT*
FROM t1
WHERE a LIKE 'string1' OR a LIKE 'string2'
A cláusula QUALIFY

A cláusula QUALIFY (em inglês) do Teradata é uma cláusula condicional na instrução SELECT que filtra os resultados de uma função analítica ordenada, anteriormente calculada de acordo com as condições de pesquisa especificadas pelo usuário. Sua sintaxe consiste na cláusula QUALIFY seguida pela função analítica, como ROW_NUMBER ou RANK (links em inglês) e os valores que você quer encontrar:

SELECT a, b
FROM t1
QUALIFY ROW_NUMBER() OVER (PARTITION BY a ORDER BY b) = 1

Os clientes do Teradata geralmente usam essa função como atalho para classificar e retornar resultados sem a necessidade de outra subconsulta.

A cláusula QUALIFY é convertida no BigQuery adicionando uma condição WHERE a uma consulta anexa:

SELECT a, b
FROM (
 SELECT a, b,
 ROW_NUMBER() OVER (PARTITION BY A ORDER BY B) row_num
 FROM t1
) WHERE row_num = 1

Para ver um exemplo, consulte o guia de início rápido associado.

Linguagem de manipulação de dados

A linguagem de manipulação de dados (DML, na sigla em inglês) é usada para listar, adicionar, excluir e modificar dados em um banco de dados. Ela inclui SELECT, INSERT, DELETE, e instruções UPDATE.

Ainda que as formas básicas dessas instruções sejam as mesmas entre o Teradata SQL e o SQL padrão, o Teradata inclui cláusulas extras não padrão e construções de instruções especiais que precisam ser convertidas na migração. As seções a seguir apresentam uma lista não exaustiva das instruções mais comuns, das principais diferenças e das conversões recomendadas.

A instrução INSERT

O BigQuery é um armazenamento de dados corporativo que se concentra no processamento analítico on-line (OLAP, na sigla em inglês). O uso de instruções DML específicas de pontos, como a execução de um script com muitas instruções INSERT, é uma tentativa de tratar o BigQuery como um processamento de transações on-line (OLTP, na sigla em inglês), o que não é uma abordagem correta.

As instruções DML do BigQuery destinam-se a atualizações em massa. Portanto, cada instrução DML que modifica os dados inicia uma transação implícita. Agrupe suas instruções DML sempre que possível para evitar sobrecarga desnecessária de transações.

Por exemplo, se você tiver o seguinte conjunto de instruções do Teradata, executá-las como estão no BigQuery é um antipadrão:

INSERT INTO t1 (...) VALUES (...);
INSERT INTO t1 (...) VALUES (...);

É possível converter o script anterior em uma única instrução INSERT, que executa uma operação em massa:

INSERT INTO t1 VALUES (...), (...)

Um cenário típico em que um grande número de instruções INSERT é usado é quando você cria uma nova tabela a partir de uma atual. No BigQuery, em vez de usar várias instruções INSERT, crie uma nova tabela e insira todas as linhas em uma única operação usando a instrução CREATE TABLE ... AS SELECT.

A instrução UPDATE

Instruções UPDATE no Teradata são semelhantes a instruções UPDATE no SQL padrão. As diferenças importantes são estas:

  • A ordem das cláusulas SET e FROM é revertida.
  • Qualquer nome de correlação do Teradata (em inglês) usado como alias da tabela no UPDATE precisa ser removido.
  • No SQL padrão, cada instrução UPDATE precisa incluir a palavra-chave WHERE, seguida por uma condição. Para atualizar todas as linhas da tabela, use WHERE true.

O exemplo a seguir mostra uma instrução UPDATE do Teradata que usa junções:

UPDATE t1
FROM t1, t2
SET
 b = t2.b
WHERE a = t2.a;

A instrução equivalente no SQL padrão é esta:

UPDATE t1
SET
 b = t2.b
FROM t2
WHERE a = t2.a;

As considerações da seção anterior sobre a execução de um grande número de instruções DML no BigQuery também se aplicam neste caso. Recomendamos o uso da instrução MERGE em vez de várias UPDATE.

A instrução DELETE

O SQL padrão requer que as instruções DELETE tenham uma cláusula WHERE. No Teradata, as cláusulas WHERE são opcionais nas instruções DELETE (em inglês) caso você esteja excluindo todas as linhas em uma tabela. Se linhas específicas estiverem sendo excluídas, o Teradata DML também exigirá uma cláusula WHERE. Durante a conversão, qualquer cláusula WHERE ausente precisa ser adicionada aos scripts. Essa alteração é necessária apenas antes da exclusão de todas as linhas de uma tabela.

Por exemplo, a instrução a seguir do Teradata SQL exclui todas as linhas de uma tabela. A cláusula ALL é opcional:

DELETE t1 ALL;

A conversão para o SQL padrão é esta:

DELETE FROM t1 WHERE TRUE;

Procedimentos armazenados

Os procedimentos armazenados na Teradata são uma combinação de instruções SQL e de controle. Os procedimentos armazenados podem assumir parâmetros que permitem criar uma interface personalizada para o Teradata Database.

Os procedimentos armazenados são aceitos como parte dos Scripts do BigQuery.

No entanto, há alguns casos em que outros recursos podem ser mais apropriados. Essas alternativas dependem de como seus procedimentos armazenados estão sendo usados. Exemplo:

  • Substitua os acionadores usados para executar consultas periódicas por consultas programadas.
  • Substitua os procedimentos armazenados que controlam a execução complexa de consultas e suas as respectivas interdependências pelos fluxos de trabalho definidos no Cloud Composer.
  • Refatore procedimentos armazenados que são usados como uma API no seu armazenamento de dados por consultas parametrizadas e usando a API BigQuery. Essa mudança implica na necessidade de recriar a lógica do procedimento armazenado em uma linguagem de programação diferente, como Java ou Go, e em chamar consultas SQL com parâmetros do código.

Refatorar e substituir a lógica de negócios presente em procedimentos armazenados é uma tarefa delicada que requer conhecimento tanto na camada de dados quanto nas práticas recomendadas de arquitetura da plataforma segmentada. Dependendo da complexidade da sua migração, é possível optar por contratar os serviços de nossos parceiros com especialização.

Como converter durante a migração

Como parte da sua migração, é preciso examinar as instruções e scripts SQL da Teradata e determinar se é necessário converter as instruções SQL da Teradata no SQL padrão usado no BigQuery. Como na recomendação geral para o uso da migração iterativa, recomendamos abordar essa tarefa de maneira sistemática.

Como escolher um caso de uso

Definimos anteriormente um caso de uso como todos os conjuntos de dados, processamento de dados e interações de sistema e usuário necessários para ter valor comercial. Um caso de uso inclui um grupo de tabelas no armazenamento de dados, os processos upstream que fornecem dados para essas tabelas e os processos downstream que consomem dados dessas tabelas, conforme mostrado no diagrama a seguir:

Fluxo de casos de uso de upstream (no local) para o armazenamento de dados legado para processos downstream.

Alguns exemplos de processos upstream, também chamados de pipelines de dados upstream, são feeds de data lakes, sistemas OLTP, um CRM e aplicativos de geração de registros. Alguns exemplos de processos downstream são painéis, relatórios, feeds para outros sistemas, aplicativos de negócios e consultas ad hoc usadas por analistas.

Ao escolher casos de uso para migração, escolha aqueles em que a parte dominante dos processos downstream são relatórios internos ou saídas de dados bem definidas, como feeds ou APIs. A escolha desses tipos nas iterações de migração inicial tem vários benefícios:

  • Eles ajudam a familiarizar sua equipe com as conversões necessárias, para que seja possível estimar o esforço necessário nas iterações subsequentes.
  • Eles facilitam a configuração de testes automatizados para a precisão dos dados, porque as saídas podem ser comparadas usando scripts.
  • Eles permitem comparações visuais dinâmicas, que são mais fáceis de apresentar a interessados não técnicos do que saídas de dados brutos.

Etapas de conversão

Para migrar um caso de uso da Teradata para o BigQuery, siga as recomendações no documento de visão geral de transferência de esquema e dados. Sempre que você move uma tabela para o BigQuery e quando processos de downstream ou upstream precisam ser modificados, também é necessário executar uma avaliação das consultas e procedimentos armazenados envolvidos na mudança para determinar a necessidade de converter essas consultas e procedimentos.

Se conversões forem necessárias, siga as orientações na seção Diferenças do Teradata SQL e na referência de conversão de SQL do Teradata para o BigQuery para criar novas consultas usando SQL padrão em conformidade com ISO SQL:2011.

Com as consultas convertidas, teste o processo downstream ou upstream em um ambiente controlado, seguindo as práticas recomendadas da sua empresa para teste e implantação contínua. Veja também ccomo criar pipelines de versão de software no Google Cloud.

Recomendamos que você crie recursos diferentes para ajudar na conversão, começando nas iterações iniciais da migração do seu armazenamento de dados. Por exemplo, considere o desenvolvimento de bibliotecas de software que implementam, sempre que possível, conversões de consultas comuns, adaptadas aos seus casos de uso. Essas bibliotecas facilitarão as iterações subsequentes. Também é possível usar essas bibliotecas com os guias de documentação para treinar sua equipe para se acostumar com o SQL padrão e para entender as melhores opções a serem usadas na sintaxe do BigQuery para Teradata que não é usada no BigQuery.

A seguir