Última atualização: setembro de 2020
Notas da versão
Este documento faz parte de uma série que discute a migração do esquema e dos dados do Teradata para o BigQuery no Google Cloud. Esta parte é um guia de início rápido (um tutorial de prova de conceito) que orienta no processo de conversão de várias instruções SQL não padrão do Teradata em SQL padrão, que é possível usar no BigQuery.
A série que discute as especificidades da transição do Teradata consiste nas partes a seguir:
- Guia de início rápido de transferência de dados e esquemas
- Visão geral da conversão de consultas
- Guia de início rápido de conversão de consulta (este documento)
- Referência de conversão do SQL
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
Objetivos
- Converta consultas do Teradata SQL em SQL padrão.
- Começar com uma abordagem manual para casos simples e prosseguir para uma abordagem automatizada usando modelos.
- Explore conversões mais complexas, em que a refatoração de consultas é necessária.
Custos
Neste guia de início rápido, usamos o seguinte componente faturável do Google Cloud:
- BigQuery: este tutorial armazena aproximadamente 1 GB de dados no BigQuery e processa menos de 2 GB ao executar as consultas uma vez. Como parte do Nível gratuito do Google Cloud, o BigQuery oferece alguns recursos gratuitos até um limite específico. Esses limites de uso gratuito estão disponíveis durante e após o período de avaliação gratuita. Se você ultrapassar esses limites de uso e não estiver mais no período de avaliação gratuita, será cobrado de acordo com os preços na página de preços do BigQuery.
Use a calculadora de preços para gerar uma estimativa de custo com base no uso previsto.
Antes de começar
- Execute primeiro o guia de início rápido da transferência do esquema e de dados para criar o esquema e os dados necessários para esse início rápido no banco de dados Teradata e no BigQuery. Use o mesmo projeto para os dois guias de início rápido.
- Verifique se seu computador tem o Teradata BTEQ instalado e se tem conexão com um banco de dados Teradata. Se você precisar instalar a ferramenta BTEQ, faça o download no site do Teradata (em inglês).
Peça ao administrador do sistema detalhes sobre instalação, configuração e execução do BTEQ. Como alternativa, ou além do BTEQ, faça o seguinte:
- Instale uma ferramenta com uma interface gráfica como o DBeaver.
- Instale o módulo Python fornecido pelo Teradata para interações de script com seu banco de dados Teradata.
- Instale o Jinja2 no seu computador se ele ainda não estiver no seu ambiente Python. O Jinja2 (em inglês) é um mecanismo de modelos para Python. Recomendamos o uso de um gerenciador de ambiente como o virtualenvwrapper (em inglês) para isolar seus ambientes Python.
- Verifique se você tem acesso ao console do BigQuery.
Introdução
Este guia de início rápido orienta você na conversão de algumas consultas de exemplo do Teradata SQL para o SQL padrão que podem ser usadas no BigQuery. Começa com um método simples de pesquisar e substituir. Em seguida, ele passa para uma reestruturação automatizada usando scripts. Por fim, ele discute conversões mais complexas, que precisam do envolvimento de especialistas do domínio para garantir que a consulta convertida preserve a semântica da original.
Este guia de início rápido é destinado a administradores, desenvolvedores e profissionais de dados que estão interessados em uma experiência prática na conversão de consultas do Teradata SQL para o padrão ISO:2011 SQL.
Como substituir operadores e funções
Como o Teradata SQL está em conformidade com o ANSI/ISO SQL, muitas consultas podem ser facilmente migradas com alterações mínimas. No entanto, o Teradata também suporta extensões SQL não padrão. Para casos simples em que funções e operadores não padrão são usados no Teradata, é possível usar o processo de localização e substituição para converter uma consulta.
Por exemplo, comece trabalhando com uma consulta no Teradata para encontrar o número de clientes que fizeram compras acima de US $10.000 em 1994.
Em um computador que tenha o BTEQ está instalado, abra o cliente Teradata BTEQ:
bteq
Faça login no Teradata. Substitua teradata-ip e teradata-user pelos valores correspondentes para seu ambiente.
.LOGON teradata-ip/teradata-user
No prompt do BTEQ, execute a seguinte consulta do Teradata SQL:
SELECT COUNT(DISTINCT(O_CUSTKEY)) AS num_customers FROM tpch.orders WHERE O_TOTALPRICE GT 10000 AND EXTRACT(YEAR FROM O_ORDERDATE) = 1994;
O resultado é semelhante ao seguinte:
num_customers ------------- 86101
Agora execute a mesma consulta no BigQuery:
Acesse o console do BigQuery:
Copie a consulta no Editor de consultas.
Como o operador
GT
(maior que) não é do SQL padrão, o Editor de consultas mostra uma mensagem de erro de sintaxe:Substitua
GT
pelo operador>
.Clique em Executar.
O resultado numérico é o mesmo que o resultado do Teradata.
Como usar um script para procurar e substituir elementos SQL
A alteração que você acabou de fazer é trivial e fácil de fazer manualmente. No entanto, pesquisar e substituir manualmente se torna complicado e propenso a erros quando você precisa processar scripts SQL grandes ou processar um grande número de scripts SQL. Portanto, é melhor automatizar a tarefa, que é a tarefa desta seção.
No Console do Cloud, acesse Cloud Shell.
Use um editor de texto para criar um novo arquivo denominado
num-customers.sql
. Por exemplo, use "vi" para criar o arquivo:vi num-customers.sql
Copie o script SQL da seção anterior para o arquivo.
Salve e feche o arquivo.
Substitua
GT
pelo operador>
:sed -i 's/GT/>/' num-customers.sql
Verifique se
GT
foi substituído por>
no arquivo:cat num-customers.sql
É possível aplicar em massa o script sed
que você acabou de usar a um conjunto de arquivos. Ele também
pode manipular muitas substituições em cada arquivo.
No Cloud Shell, use um editor de texto para abrir o arquivo denominado
num-customers.sql
:vi num-customers.sql
Substitua o conteúdo do arquivo pelo script a seguir:
SELECT COUNT(DISTINCT(O_CUSTKEY)) AS num_customers FROM tpch.orders WHERE O_TOTALPRICE GT 10000 AND O_ORDERPRIORITY EQ '1-URGENT' AND EXTRACT(YEAR FROM O_ORDERDATE) = 1994;
O script é quase idêntico ao anterior, mas tem uma linha a mais para incluir apenas os pedidos urgentes. O script SQL agora tem dois operadores SQL não padrão:
GT
eEQ
.Salve e feche o arquivo.
Faça 99 cópias do arquivo:
for i in {1..99}; do cp num-customers.sql "num-customers$i.sql"; done
Quando o comando for concluído, você terá 100 versões do arquivo de script.
Substitua
GT
por>
em todos os arquivos em uma única operação:sed -i 's/GT/>/g;s/EQ/=/g' *.sql
Usar um script para alterar os 100 arquivos é muito mais eficiente do que alterar manualmente esses arquivos, um por um.
Liste os arquivos que incluem o operador
GT
:grep GT *.sql
O comando não retorna nenhum resultado porque todas as ocorrências do operador
GT
foram substituídas pelo operador>
.Escolha qualquer um dos arquivos e verifique se os operadores foram substituídos por seus equivalentes padrão:
cat num-customers33.sql
Os casos adequados para essa abordagem de pesquisa e substituição incluem:
- Algumas funções de data, como as seguintes alterações:
- de
DATE + k
paraDATE_ADD
- de
TD_DAY_OF_MONTH
paraEXTRACT
- de
MONTHS_BETWEEN
paraDATE_DIFF
- de
- Algumas
funções de string,
como as seguintes alterações:
- de
CHARACTER_LENGTH
paraCHAR_LENGTH
- de
INDEX
paraSTRPOS
- de
LEFT
paraSUBSTR
- de
- Algumas funções matemáticas, como as seguintes alterações:
- de
NULLIFZERO
paraNULLIF
- de
RANDOM
paraRAND
- de
ZEROIFNULL
paraIFNULL
- de
- Abreviações, como a alteração de
SEL
paraSELECT
.
Para uma lista mais abrangente de conversões comuns, consulte o documento de referência de conversão do Teradata para BigQuery SQL.
Como usar um script para reestruturar instruções e scripts SQL
Até o momento, você só automatizou a substituição de operadores e funções com mapeamento de um para um entre o TeradataSQL e o SQL padrão. No entanto, a complexidade da conversão de elementos SQL aumenta para funções não padrão. Além de substituir palavras-chave, o script de conversão também precisa adicionar ou mover outros elementos, como argumentos, parênteses ou outras chamadas de função.
Nesta seção, você trabalha uma consulta no Teradata para encontrar os valores mais altos que foram solicitados por um grupo de clientes no final de cada mês.
No computador em que o BTEQ está instalado, alterne para ou abra o prompt de comando do BTEQ. Se você fechou o BTEQ, execute o comando a seguir:
bteq
No prompt do BTEQ, execute a seguinte consulta do Teradata SQL:
SELECT O_CUSTKEY, SUM(O_TOTALPRICE) as total, TD_MONTH_END(O_ORDERDATE) as month_end FROM tpch.orders WHERE O_CUSTKEY < 5 GROUP BY O_CUSTKEY, month_end ORDER BY total DESC;
A consulta usa a função não padrão
TD_MONTH_END
(em inglês) do Teradata para conseguir a data do final do mês imediatamente após a data do pedido. Por exemplo, se o pedido foi realizado em 16/05/1996,TD_MONTH_END
retornará 1996/05/31. É necessário um argumento de data, isto é, a data do pedido. Os resultados são agrupados por essas datas de final de mês e pela chave do cliente para ver o valor total para um determinado mês e para um determinado cliente.O resultado será semelhante a este:
O_CUSTKEY total month_end ----------- ----------------- --------- 4 379593.37 96/06/30 4 323004.15 96/08/31 2 312692.22 97/02/28 4 311722.87 92/04/30
Para executar uma consulta que retorna os mesmos resultados no BigQuery, seria
necessário substituir a função não padrão TD_MONTH_END
pela
equivalente do SQL padrão. No entanto, essa função não tem mapeamento um para um. Crie uma função que usa um modelo Jinja2 para
executar a tarefa.
No Cloud Shell, crie um novo arquivo denominado
month-end.jinja2
:vi month-end.jinja2
Copie o seguinte snippet SQL no arquivo:
DATE_SUB( DATE_TRUNC( DATE_ADD( {{ date }}, INTERVAL 1 MONTH ), MONTH ), INTERVAL 1 DAY )
O arquivo é um modelo Jinja2 Representa o equivalente à função
TD_MONTH_END
no SQL padrão. Ele tem um marcador de posição denominado{{ date }}
que será substituído pelo argumento de data, neste casoO_ORDERDATE
.Salve e feche o arquivo.
Crie um novo arquivo denominado
translate-query.py
:translate-query.py
Copie o script Python a seguir no arquivo:
"""Translates a sample using a template.""" import re from jinja2 import Environment from jinja2 import PackageLoader env = Environment(loader=PackageLoader('translate-query', '.')) regex = re.compile(r'(.*)TD_MONTH_END\(([A-Z_]+)\)(.*)') with open('month-end.td.sql', 'r') as td_sql: with open('month-end.sql', 'w') as std_sql: for line in td_sql: match = regex.search(line) if match: argument = match.group(2) template = env.get_template('month-end.jinja2') std_sql.write(match.group(1) + template.render(date=argument) \ + match.group(3) + '\n') else: std_sql.write(line)
Esse script Python abre o arquivo criado anteriormente (
month-end.td.sql
), lê o Teradata SQL dele como entrada e grava um script SQL padrão convertido no arquivomonth-end.sql
.Observe os seguintes detalhes:
- O script corresponde à expressão regular
(.*)TD_MONTH_END\(([A-Z_]+)\)(.*)
em cada linha lida a partir do arquivo de entrada. A expressão procuraTD_MONTH_END
e captura três grupos:- Qualquer caractere
(.*)
antes da função comogroup(1)
. - O argumento
([A-Z_]+)
enviado à funçãoTD_MONTH_END
comogroup(2)
. - Qualquer caractere
(.*)
após a função comogroup(3)
.
- Qualquer caractere
- Se houver uma correspondência, o script recuperará o modelo
month-end.jinja2
do Jinja2 que você criou em uma etapa anterior. Em seguida, ele gravará o seguinte no arquivo de saída, nesta ordem:- Os caracteres representados por
group(1)
. - O modelo, em que o marcador
date
foi substituído pelo argumento original encontrado no Teradata SQL, que éO_ORDERDATE
. - Os caracteres representados por
group(3)
.
- Os caracteres representados por
- O script corresponde à expressão regular
Salve e feche o arquivo.
Execute o script Python:
python translate-query.py
Um arquivo denominado
month-end.sql
é criado.Exiba o conteúdo desse novo arquivo:
cat month-end.sql
Este comando mostra a consulta convertida em SQL padrão pelo script:
SELECT O_CUSTKEY, SUM(O_TOTALPRICE) as total, DATE_SUB( DATE_TRUNC( DATE_ADD( O_ORDERDATE, INTERVAL 1 MONTH ), MONTH ), INTERVAL 1 DAY ) as month_end FROM tpch.orders WHERE O_CUSTKEY < 5 GROUP BY O_CUSTKEY, month_end ORDER BY total DESC;
A função
TD_MONTH_END
não aparece mais. Ela foi substituída pelo modelo e pelo argumento de dataO_ORDERDATE
na posição apropriada no modelo.
O script Python já usa um modelo de um arquivo Jinja2 externo. A mesma abordagem pode ser aplicada à expressão regular, ou seja, a expressão pode ser carregada a partir de um arquivo ou de um armazenamento de chave-valor. Dessa forma, o script pode ser generalizado para manipular uma expressão arbitrária e seu modelo de conversão correspondente.
Por fim, execute o script gerado no BigQuery para verificar se seus resultados correspondem aos conseguidos no Teradata:
Acesse o console do BigQuery:
Copie a consulta que você usou anteriormente para o Editor de consultas.
Clique em Executar.
O resultado é o mesmo que o resultado do Teradata.
Como escalonar verticalmente o esforço de conversão de consultas
Durante uma migração, você precisa de um grupo de indivíduos qualificados para aplicar um conjunto de traduções usando ferramentas, como os scripts de exemplo que você viu anteriormente. Esses scripts evoluirão ao longo da migração. Portanto, é altamente recomendável que você coloque os scripts sob controle de origem. Você precisará testar meticulosamente os resultados da execução desses scripts.
Sugerimos que você entre em contato com nossa equipe de vendas, que pode colocar você em contato com nossa Professional Services Organization e nossos parceiros para ajudá-lo em sua migração.
Como refatorar suas consultas
Na seção anterior, você utilizou scripts para procurar e substituir operadores do Teradata SQL por seus equivalentes no SQL padrão. Você também executou uma reestruturação automatizada limitada de suas consultas com a ajuda de modelos.
Para converter algumas funcionalidades do Teradata SQL, é necessária uma refatoração mais profunda de suas consultas SQL. Esta seção explora dois exemplos: como converter a cláusula QUALIFY
e como converter referências entre colunas.
Os exemplos nesta seção são refatorados manualmente. Na prática, alguns casos de refatoração mais complexa podem ser candidatos à automação. No entanto, automatizá-los pode gerar retornos decrescentes devido à complexidade de analisar cada caso diferente. Além disso, é possível que um script automatizado perca as soluções mais ideais que preservam a semântica da consulta.
A cláusula QUALIFY
A cláusula QUALIFY
do Teradata é uma cláusula condicional usada em uma instrução SELECT
para filtrar os resultados de uma função analítica ordenada (links em inglês) previamente calculada.
As funções analíticas ordenadas funcionam em um intervalo de linhas e produzem um resultado para cada linha. Os clientes do Teradata geralmente usam essa função como atalho para classificar e retornar resultados sem a necessidade de outra subconsulta.
Para ilustrar isso, use a cláusula QUALIFY
para selecionar os pedidos de maior valor de cada cliente no ano de 1994.
No computador em que o BTEQ está instalado, alterne para ou abra o prompt de comando do BTEQ. Se você fechou o BTEQ, execute o comando a seguir:
bteq
Copie a seguinte consulta SQL do Teradata no prompt do BTEQ:
SELECT O_CUSTKEY, O_TOTALPRICE FROM tpch.orders QUALIFY ROW_NUMBER() OVER ( PARTITION BY O_CUSTKEY ORDER BY O_TOTALPRICE DESC ) = 1 WHERE EXTRACT(YEAR FROM O_ORDERDATE) = 1994 AND (O_CUSTKEY MOD 10000) = 0;
Observe o seguinte sobre a consulta:
- A consulta divide linhas em partições. Cada partição corresponde a uma chave de cliente (
PARTITION BY O_CUSTKEY
). - A cláusula
QUALIFY
filtra as linhas para apenas a primeira (ROW_NUMBER()=1
) de cada partição. - Como as linhas em cada partição são ordenadas pelo preço total do pedido em ordem decrescente (
ORDER BY O_TOTALPRICE DESC
), a primeira linha corresponde àquela com o maior valor do pedido. - A instrução
SELECT
busca a chave do cliente e o preço total do pedido (O_CUSTKEY
,O_TOTALPRICE
), filtrando ainda mais os resultados até 1994 usando uma cláusulaWHERE
. - O operador módulo (
MOD
) (em inglês) busca apenas um subconjunto de linhas, para fins de exibição. Esse método de amostragem é preferível à cláusulaSAMPLE
(em inglês), porqueSAMPLE
é aleatória, o que não permitirá comparar resultados com o BigQuery.
- A consulta divide linhas em partições. Cada partição corresponde a uma chave de cliente (
Execute a consulta.
O resultado será semelhante a este:
O_CUSTKEY O_TOTALPRICE ----------- ----------------- 10000 182742.02 20000 56470.00 40000 211502.51 50000 81584.54 70000 53131.09 80000 15902.64 100000 306639.29 130000 183113.29 140000 250958.13
A segunda coluna é o valor total mais alto para pedidos em 1994 para as chaves de cliente exemplificadas na primeira coluna.
Para executar a mesma consulta no BigQuery, você precisa transformar o script SQL para estar em conformidade com o ANSI/ISO SQL.
Acesse o console do BigQuery:
Copie a consulta a seguir convertida no Editor de consultas:
SELECT O_CUSTKEY, O_TOTALPRICE FROM ( SELECT O_CUSTKEY, O_TOTALPRICE, ROW_NUMBER() OVER ( PARTITION BY O_CUSTKEY ORDER BY O_TOTALPRICE DESC ) as row_num FROM tpch.orders WHERE EXTRACT(YEAR FROM O_ORDERDATE) = 1994 AND MOD(O_CUSTKEY, 10000) = 0 ) WHERE row_num = 1
Essa nova consulta apresenta algumas alterações, nenhuma delas pode ser feita usando uma pesquisa e substituição simples. Observações:
- A cláusula
QUALIFY
é removida e a função analíticaROW_NUMBER()
é movida como uma coluna para a instruçãoSELECT
e um alias específico (as row_num
). - Uma consulta de inclusão é criada, se não existir, e uma condição
WHERE
é adicionada a ela, com o filtro de valor analítico (row_num = 1
). - O operador
MOD
do Teradata também não é padrão e, portanto, é alterado pela funçãoMOD()
.
- A cláusula
Clique em Executar.
O resultado da coluna é o mesmo que o resultado do Teradata.
Referências entre colunas
O Teradata aceita referência cruzada entre colunas definidas na mesma consulta. Nesta seção, você usa uma consulta que atribui um alias a uma instrução SELECT
aninhada e, em seguida, o referencia em uma expressão CASE
(em inglês).
Para ilustrar isso, execute uma consulta que determine se um cliente estava ativo em um determinado ano. Um cliente está ativo se tiver feito pelo menos um pedido durante o ano.
No computador em que o BTEQ está instalado, alterne para ou abra o prompt de comando do BTEQ. Se você fechou o BTEQ, execute o comando a seguir:
bteq
Copie a seguinte consulta SQL do Teradata no prompt do BTEQ:
SELECT ( SELECT COUNT(O_CUSTKEY) FROM tpch.orders WHERE O_CUSTKEY = 2 AND EXTRACT(YEAR FROM O_ORDERDATE) = 1994 ) AS num_orders, CASE WHEN num_orders = 0 THEN 'INACTIVE' ELSE 'ACTIVE' END AS status;
Observe o seguinte sobre a consulta:
- Há uma consulta aninhada que conta o número de vezes que a chave do cliente
2
aparece no ano de 1994. O resultado dessa consulta é retornado na primeira coluna e recebe o aliasnum_orders
. - Na segunda coluna, a expressão
CASE
geraACTIVE
quando o número de pedidos encontrados é diferente de zero. Caso contrário, geraINACTIVE
. A expressãoCASE
usa internamente o alias da primeira coluna da mesma consulta (num_orders
).
- Há uma consulta aninhada que conta o número de vezes que a chave do cliente
Execute a consulta.
O resultado é semelhante ao seguinte:
num_orders status ----------- -------- 3 ACTIVE
Para executar a mesma consulta no BigQuery, você precisa eliminar a referência entre as colunas na mesma consulta.
Acesse o console do BigQuery:
Copie a seguinte consulta convertida no Query Editor:
SELECT customer.num_orders, CASE WHEN customer.num_orders = 0 THEN 'INACTIVE' ELSE 'ACTIVE' END AS status FROM ( SELECT COUNT(O_CUSTKEY) AS num_orders FROM tpch.orders WHERE O_CUSTKEY = 2 AND EXTRACT(YEAR FROM O_ORDERDATE) = 1994 ) customer;
Observe as alterações a seguir da consulta original:
- A consulta aninhada é movida para a cláusula
FROM
de uma consulta de inclusão. Ela recebe o aliascustomer
, mas ele não define uma coluna de saída, porque está em uma cláusulaFROM
, e não em uma cláusulaSELECT
. - A cláusula
SELECT
tem duas colunas:- A primeira coluna gera o número de pedidos (
num_orders
) definidos na consulta aninhada (customer
). - A segunda coluna inclui a instrução
CASE
que faz referência ao número de pedidos definidos na consulta aninhada
- A primeira coluna gera o número de pedidos (
- A consulta aninhada é movida para a cláusula
Clique em Executar.
O resultado da coluna é o mesmo que o resultado do Teradata.
Limpeza
Para evitar cobranças na sua conta do Google Cloud Platform pelos recursos usados neste tutorial, remova-os.
Exclua o projeto
A maneira mais simples de não ser cobrado é excluir o projeto criado neste tutorial.
- No Console do Google Cloud, acesse a página Gerenciar recursos.
- Na lista de projetos, selecione o projeto que você quer excluir e clique em Excluir .
- Na caixa de diálogo, digite o ID do projeto e clique em Encerrar para excluí-lo.
A seguir
- Consulte a referência de conversão do Teradata para o BigQuery SQL para uma visão detalhada das diferenças e mapeamentos entre o Teradata SQL e o SQL padrão usados no BigQuery.
- Vá para a próxima parte desta série: Governança de dados
- Confira as ofertas da Professional Services Organization do Google e em nosso extenso ecossistema de parceiros, composto por empresas que podem ajudar você no seu caminho de migração.
- Explore arquiteturas de referência, diagramas, tutoriais e práticas recomendadas sobre o Google Cloud. Confira o Centro de arquitetura do Cloud.