Como migrar o Teradata para o BigQuery: Guia de início rápido 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 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:

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 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.

  1. Em um computador que tenha o BTEQ está instalado, abra o cliente Teradata BTEQ:

    bteq
    
  2. Faça login no Teradata. Substitua teradata-ip e teradata-user pelos valores correspondentes para seu ambiente.

    .LOGON teradata-ip/teradata-user
    
  3. 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:

  1. Acesse o console do BigQuery:

    Acessar o console do BigQuery

  2. 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:

    Erro "identificador inesperado - GT".

  3. Substitua GT pelo operador >.

  4. 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.

  1. No Console do Cloud, acesse Cloud Shell.

    Acessar o Cloud Shell

  2. 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
    
  3. Copie o script SQL da seção anterior para o arquivo.

  4. Salve e feche o arquivo.

  5. Substitua GT pelo operador >:

    sed -i 's/GT/>/' num-customers.sql
    
  6. 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.

  1. No Cloud Shell, use um editor de texto para abrir o arquivo denominado num-customers.sql:

    vi num-customers.sql
    
  2. 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 e EQ.

  3. Salve e feche o arquivo.

  4. 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.

  5. 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.

  6. 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 >.

  7. 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:

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.

  1. 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
    
  2. 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.

  1. No Cloud Shell, crie um novo arquivo denominado month-end.jinja2:

    vi month-end.jinja2
    
  2. 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 caso O_ORDERDATE.

  3. Salve e feche o arquivo.

  4. Crie um novo arquivo denominado translate-query.py:

    translate-query.py
    
  5. 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 arquivo month-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 procura TD_MONTH_END e captura três grupos:
      1. Qualquer caractere (.*) antes da função como group(1).
      2. O argumento ([A-Z_]+) enviado à função TD_MONTH_END como group(2).
      3. Qualquer caractere (.*) após a função como group(3).
    • 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:
      1. Os caracteres representados por group(1).
      2. O modelo, em que o marcador date foi substituído pelo argumento original encontrado no Teradata SQL, que é O_ORDERDATE.
      3. Os caracteres representados por group(3).
  6. Salve e feche o arquivo.

  7. Execute o script Python:

    python translate-query.py
    

    Um arquivo denominado month-end.sql é criado.

  8. 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 data O_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:

  1. Acesse o console do BigQuery:

    Acessar o console do BigQuery

  2. Copie a consulta que você usou anteriormente para o Editor de consultas.

  3. 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.

  1. 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
    
  2. 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áusula WHERE.
    • 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áusula SAMPLE (em inglês), porque SAMPLE é aleatória, o que não permitirá comparar resultados com o BigQuery.
  3. 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.

  1. Acesse o console do BigQuery:

    Acessar o console do BigQuery

  2. 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ítica ROW_NUMBER() é movida como uma coluna para a instrução SELECT 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ção MOD().
  3. 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.

  1. 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
    
  2. 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 alias num_orders.
    • Na segunda coluna, a expressão CASE gera ACTIVE quando o número de pedidos encontrados é diferente de zero. Caso contrário, gera INACTIVE. A expressão CASE usa internamente o alias da primeira coluna da mesma consulta (num_orders).
  3. 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.

  1. Acesse o console do BigQuery:

    Acessar o console do BigQuery

  2. 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 alias customer, mas ele não define uma coluna de saída, porque está em uma cláusula FROM, e não em uma cláusula SELECT.
    • 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
  3. 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.

  1. No Console do Google Cloud, acesse a página Gerenciar recursos.

    Acessar "Gerenciar recursos"

  2. Na lista de projetos, selecione o projeto que você quer excluir e clique em Excluir .
  3. Na caixa de diálogo, digite o ID do projeto e clique em Encerrar para excluí-lo.

A seguir