Migra de Teradata a BigQuery: guía de inicio rápido de traducción de consultas

Última actualización: septiembre de 2020
Notas de la versión

Este documento forma parte de una serie en la que se analiza la migración de datos y esquemas de Teradata a BigQuery en Google Cloud. Esta parte es una guía de inicio rápido (un instructivo de prueba de concepto) que te guía a través del proceso de conversión de varias instrucciones de SQL de Teradata no estándar en SQL estándar, que puedes usar en BigQuery.

La serie que analiza aspectos específicos de la transición de Teradata consta de las siguientes partes:

Para obtener una descripción general de la transición desde un almacén de datos local hacia BigQuery en Google Cloud, consulta la serie que comienza con Migra almacenes de datos a BigQuery: introducción y descripción general.

Objetivos

  • Traducir consultas de SQL de Teradata a SQL estándar
  • Comenzar con un enfoque manual para casos simples y proceder con un enfoque automatizado mediante el uso de plantillas
  • Explorar traducciones más complejas en las que se necesita refactorizar las consultas

Costos

En esta guía de inicio rápido, se usan los siguientes componentes facturables de Google Cloud:

  • BigQuery: en este instructivo, se almacena casi 1 GB de datos en BigQuery y se procesan menos de 2 GB cuando se ejecutan las consultas una vez. Como parte del nivel gratuito de Google Cloud, BigQuery ofrece algunos recursos de forma gratuita hasta alcanzar un límite específico. Estos límites de uso sin cargo están disponibles durante y luego el período de prueba gratuita. Si superas estos límites de uso y ya no te encuentras en el período de prueba gratuito, se te cobrará según los precios de la página de precios de BigQuery.

Puedes usar la calculadora de precios para realizar una estimación de costos según el uso previsto.

Antes de comenzar

  • Ejecuta primero la guía de inicio rápido de transferencia de datos y esquemas a fin de crear el esquema y los datos necesarios para esta guía de inicio rápido en tu base de datos de Teradata y en BigQuery. Se usa el mismo proyecto en ambas guías de inicio rápido.
  • Asegúrate de que tu computadora tenga instalado Teradata BTEQ y pueda conectarse a una base de datos de Teradata. Si necesitas instalar la herramienta BTEQ, puedes descargarla del sitio web de Teradata. Consulta al administrador del sistema para obtener detalles sobre la instalación, configuración y ejecución de BTEQ. Como alternativa, o de forma suplementaria a BTEQ, puedes hacer lo siguiente:
  • Instala Jinja2 en tu computadora si aún no está presente en tu entorno de Python. Jinja2 es un motor de plantillas para Python. Recomendamos usar un administrador de entornos, como virtualenvwrapper, para aislar los entornos de Python.
  • Asegúrate de tener acceso a la consola de BigQuery.

Introducción

En esta guía de inicio rápido, se detalla la traducción de algunas consultas de muestra de SQL de Teradata a SQL estándar, que se puede usar en BigQuery. Comienza con un método simple de búsqueda y reemplazo. Luego, pasa a una reestructuración automatizada con secuencias de comandos. Por último, analiza las traducciones más complejas en las que los expertos en la materia del dominio deben participar para asegurarse de que la consulta traducida conserve la semántica del original.

Esta guía de inicio rápido está destinada a los administradores, desarrolladores y profesionales de datos del almacén de datos que estén interesados en una experiencia práctica con la traducción de consultas de SQL de Teradata a SQL estándar ISO:2011.

Reemplaza operadores y funciones

Debido a que el SQL de Teradata se ajusta a ANSI/ISO SQL, muchas consultas se pueden migrar con facilidad con cambios mínimos. Sin embargo, Teradata también admite extensiones SQL no estándar. Para los casos simples en los que se usan operadores y funciones no estándar en Teradata, a menudo se puede usar el proceso de búsqueda y reemplazo a fin de traducir una consulta.

Por ejemplo, puedes comenzar el trabajo con una consulta en Teradata para encontrar el número de clientes que realizaron compras superiores a $10,000 en 1994.

  1. En una computadora donde esté instalado BTEQ, abre el cliente de Teradata BTEQ:

    bteq

  2. Accede a Teradata. Reemplaza teradata-ip y teradata-user por los valores correspondientes de tu entorno.

    .LOGON teradata-ip/teradata-user

  3. En la ventana de BTEQ, ejecuta la siguiente consulta de SQL de Teradata:

    SELECT
 COUNT(DISTINCT(O_CUSTKEY)) AS num_customers
FROM
 tpch.orders
WHERE
 O_TOTALPRICE GT 10000
 AND EXTRACT(YEAR FROM O_ORDERDATE) = 1994;

    El resultado es similar al siguiente:

    num_customers
-------------
        86101

Ahora, ejecuta la misma consulta en BigQuery:

  1. Dirígete a la consola de BigQuery:

    Ir a la consola de BigQuery

  2. Copia la consulta en el editor de consultas.

    El operador GT (mayor que) no es SQL estándar, por lo que el editor de consultas muestra un mensaje de error de sintaxis:

    Error de “identificador inesperado 'GT'”.

  3. Reemplaza GT por el operador >.

  4. Haz clic en Ejecutar.

    El resultado numérico es el mismo que el de Teradata.

Usa una secuencia de comandos para buscar y reemplazar elementos SQL

El cambio que acabas de hacer es trivial y fácil de hacer a mano. Sin embargo, la búsqueda y el reemplazo de forma manual se vuelve un método engorroso y propenso a errores cuando se deben procesar secuencias de comandos SQL de gran tamaño o en grandes cantidades. Por lo tanto, es mejor automatizar esta tarea, que es lo que harás en esta sección.

  1. En la consola, ve a Cloud Shell:

    Ir a Cloud Shell

  2. Usa un editor de texto para crear un archivo nuevo llamado num-customers.sql. Por ejemplo, usa vi para crear el archivo:

    vi num-customers.sql

  3. Copia la secuencia de comandos SQL de la sección anterior en el archivo.

  4. Guarda y cierra el archivo.

  5. Reemplaza GT por el operador >:

    sed -i 's/GT/>/' num-customers.sql

  6. Verifica que GT se haya reemplazado por > en el archivo:

    cat num-customers.sql

Puedes aplicar la secuencia de comandos sed que acabas de usar en un conjunto de archivos masivo. También se pueden manejar muchos reemplazos en cada archivo.

  1. En Cloud Shell, usa un editor de texto para abrir el archivo llamado num-customers.sql:

    vi num-customers.sql

  2. Reemplaza el contenido del archivo por la secuencia de comandos siguiente:

    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;

    La secuencia de comandos es casi idéntica a la anterior, pero se le agregó una línea para incluir solo los pedidos urgentes. La secuencia de comandos SQL ahora tiene dos operadores SQL no estándar: GT y EQ.

  3. Guarda y cierra el archivo.

  4. Haz 99 copias del archivo:

    for i in {1..99}; do cp num-customers.sql "num-customers$i.sql"; done

    Cuando el comando esté listo, tendrás 100 versiones del archivo de secuencia de comandos.

  5. Reemplaza GT por > en todos los archivos en una sola operación:

    sed -i 's/GT/>/g;s/EQ/=/g' *.sql

    Usar una secuencia de comandos para modificar los 100 archivos es mucho más eficiente que modificarlos de forma manual uno por uno.

  6. Crea una lista de los archivos que incluyen el operador GT:

    grep GT *.sql

    El comando no devuelve ningún resultado, porque todas las apariciones del operador GT se reemplazaron por el operador >.

  7. Elige cualquiera de los archivos y verifica que los operadores se reemplazaron por sus homólogos estándar:

    cat num-customers33.sql

Los siguientes son casos en los que este método de búsqueda y reemplazo es adecuado:

Para obtener una lista más completa de traducciones comunes, consulta el documento de referencia de traducción de Teradata a BigQuery SQL.

Usa una secuencia de comandos para reestructurar instrucciones de SQL y secuencias de comandos

Hasta ahora solo automatizaste el reemplazo de operadores y funciones que asignan uno a uno entre TeradataSQL y SQL estándar. Sin embargo, la complejidad de la traducción de elementos SQL es mayor para funciones no estándar. La secuencia de comandos de traducción necesita reemplazar palabras clave y, también, debe agregar o mover otros elementos como argumentos, paréntesis y otras llamadas a funciones.

En esta sección, trabajarás con una consulta en Teradata para encontrar los valores más altos que pidió un grupo de clientes al final de cada mes.

  1. En la computadora en la que tienes BTEQ instalado, cambia o abre el símbolo del sistema de BTEQ. Si cerraste BTEQ, ejecuta este comando:

    bteq

  2. En la ventana de BTEQ, ejecuta la siguiente consulta de SQL de Teradata:

    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;

    La consulta usa la función no estándar TD_MONTH_END de Teradata para obtener la fecha de finalización del mes que le sigue de inmediato a la fecha del pedido. Por ejemplo, si el pedido fue el día 16/05/1996, TD_MONTH_END muestra 1996-05-31. Se necesita un argumento de fecha, es decir, la fecha del pedido. Los resultados se agrupan por estas fechas de fin de mes y por la clave del cliente a fin de obtener el valor total para un mes determinado y un cliente determinado.

    El resultado es similar al siguiente:

    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 ejecutar una consulta que muestre los mismos resultados en BigQuery, tendrías que reemplazar la función TD_MONTH_END no estándar por su SQL estándar equivalente. Sin embargo, no existe una asignación uno a uno para esta función. Por lo tanto, debes crear una función que use una plantilla de Jinja2 para realizar esta tarea.

  1. En Cloud Shell, crea un archivo nuevo con el nombre month-end.jinja2:

    vi month-end.jinja2

  2. Copia el fragmento siguiente de SQL en el archivo:

    DATE_SUB(
   DATE_TRUNC(
     DATE_ADD(
       {{ date }},
       INTERVAL 1 MONTH
     ),
     MONTH
   ),
   INTERVAL 1 DAY
)

    Este archivo es una plantilla de Jinja2. Representa el equivalente a la función TD_MONTH_END en SQL estándar. Tiene un marcador de posición llamado {{ date }} que se reemplazará por el argumento de la fecha, en este caso O_ORDERDATE.

  3. Guarda y cierra el archivo.

  4. Crea un archivo nuevo llamado translate-query.py:

    translate-query.py

  5. Copia la secuencia de comandos siguiente de Python en el archivo:

    """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)

    Esta secuencia de comandos de Python abre el archivo que ya se creó (month-end.td.sql), lee el SQL de Teradata como entrada y escribe una secuencia de comandos SQL estándar traducida en el archivo month-end.sql.

    Ten en cuenta los detalles siguientes:

    • La secuencia de comandos hace coincidir la expresión regular (.*)TD_MONTH_END\(([A-Z_]+)\)(.*) con cada línea leída del archivo de entrada. La expresión busca TD_MONTH_END y captura tres grupos:
      1. Cualquier carácter (.*) antes de la función como group(1).
      2. El argumento ([A-Z_]+) enviado a la función TD_MONTH_END como group(2).
      3. Cualquier carácter (.*) después de la función como group(3).
    • Si hay una coincidencia, la secuencia de comandos recupera la plantilla de Jinja2 month-end.jinja2 que creaste en un paso anterior. Luego, escribe lo siguiente en el archivo de salida, en este orden:
      1. Los caracteres representados por group(1).
      2. La plantilla, en la que el marcador de posición date se reemplazó por el argumento original que se encontró en el SQL de Teradata, que es O_ORDERDATE.
      3. Los caracteres representados por group(3).

  6. Guarda y cierra el archivo.

  7. Ejecuta la secuencia de comandos de Python:

    python translate-query.py

    Se creará un archivo con el nombre month-end.sql.

  8. Haz que se muestre el contenido de este archivo nuevo:

    cat month-end.sql

    Con este comando, se muestra la consulta traducida a SQL estándar mediante la secuencia de comandos:

    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;

    La función TD_MONTH_END ya no aparece. Se reemplazó por la plantilla y el argumento de fecha O_ORDERDATE en la posición adecuada en la plantilla.

La secuencia de comandos de Python ya usa una plantilla de un archivo externo de Jinja2. Se puede aplicar el mismo método en la expresión regular, es decir, la expresión se puede cargar desde un archivo o un almacén de pares clave-valor. De esta manera, la secuencia de comandos se puede generalizar para manejar una expresión arbitraria y su plantilla de traducción correspondiente.

Por último, ejecuta la secuencia de comandos generada en BigQuery para verificar que sus resultados coincidan con los obtenidos de Teradata:

  1. Dirígete a la consola de BigQuery:

    Ir a la consola de BigQuery

  2. Copia la consulta que usaste antes en el editor de consultas.

  3. Haz clic en Ejecutar.

    El resultado es el mismo que el de Teradata.

Escala verticalmente el esfuerzo de traducción de consultas

Durante una migración, necesitas un grupo de personas capacitadas para aplicar un conjunto de traducciones con herramientas como las secuencias de comandos de ejemplo que viste antes. Estas secuencias de comandos evolucionarán a lo largo del esfuerzo de migración. Por lo tanto, te recomendamos que coloques las secuencias de comandos bajo un control de origen. Deberás probar de forma meticulosa los resultados de la ejecución de estas secuencias de comandos.

Te sugerimos que te comuniques con nuestro equipo de ventas, que te podrá comunicar con nuestra Professional Service Organization y nuestros socios para ayudarte durante la migración.

Refactoriza tus consultas

En la sección anterior, usaste secuencias de comandos para buscar y reemplazar operadores de SQL de Teradata por sus equivalentes en SQL estándar. También realizaste una reestructuración automatizada limitada de tus consultas con la ayuda de plantillas.

Para traducir algunas funcionalidades de SQL de Teradata, es necesario realizar una refactorización más profunda de tus consultas de SQL. En esta sección, se exploran dos ejemplos: traducir la cláusula QUALIFY y traducir referencias de columnas cruzadas.

Los ejemplos de esta sección se refactorizan a mano. En la práctica, algunos casos de refactorización más compleja podrían ser candidatos para la automatización. Sin embargo, automatizarlos podría generar rendimientos decrecientes debido a la complejidad de analizar cada caso diferente. Además, es posible que una secuencia de comandos automatizada no tome en cuenta soluciones más óptimas que preserven la semántica de la consulta.

Cláusula QUALIFY

La cláusula QUALIFY de Teradata es una cláusula condicional que se usa en una indicación SELECT para filtrar los resultados de una función analítica ordenada ya calculada. Las funciones analíticas ordenadas se aplican a un rango de filas y generan un resultado para cada fila. Los clientes de Teradata suelen usar esta función como una forma abreviada de clasificar y mostrar resultados sin la necesidad de realizar una subconsulta adicional.

Para ilustrar esto, puedes usar la cláusula QUALIFY a fin de seleccionar los pedidos de mayor valor de cada cliente en el año 1994.

  1. En la computadora en la que tienes BTEQ instalado, cambia o abre el símbolo del sistema de BTEQ. Si cerraste BTEQ, ejecuta este comando:

    bteq

  2. Copia la siguiente consulta de SQL de Teradata a la ventana de 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;

    Ten en cuenta lo siguiente sobre esta consulta:

    • La consulta divide las filas en particiones. Cada partición corresponde a una clave de cliente (PARTITION BY O_CUSTKEY).
    • La cláusula QUALIFY filtra las filas solo a la primera (ROW_NUMBER()=1) de cada partición.
    • Debido a que las filas de cada partición se ordenan según el precio total del pedido descendente (ORDER BY O_TOTALPRICE DESC), la primera fila corresponde a la que tiene el valor de orden más alto.
    • La instrucción SELECT obtiene la clave del cliente y el precio total del pedido (O_CUSTKEY, O_TOTALPRICE), lo que filtra aún más los resultados para incluir solo 1994 mediante una cláusula WHERE.
    • El operador de módulo (MOD) solo recupera un subconjunto de filas para fines de visualización. Se prefiere usar este método de muestreo a la cláusula SAMPLE, porque SAMPLE es aleatorio, lo que no permitirá comparar resultados con BigQuery.

  3. Ejecuta la consulta.

    El resultado es similar al siguiente:

     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

    La segunda columna es el valor total más alto de los pedidos de 1994 de las claves de cliente de muestra de la primera columna.

Para ejecutar la misma consulta en BigQuery, debes transformar la secuencia de comandos SQL a fin de que se ajuste a ANSI/ISO SQL.

  1. Dirígete a la consola de BigQuery:

    Ir a la consola de BigQuery

  2. Copia la siguiente consulta traducida al 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

    Esta nueva consulta presenta algunos cambios, ninguno de los cuales se puede hacer con una simple búsqueda y reemplazo. Ten en cuenta lo siguiente:

    • Se quita la cláusula QUALIFY y la función analítica ROW_NUMBER() se mueve como una columna a la instrucción SELECT y se le asigna un alias (as row_num).
    • Se crea una consulta de cierre si no existe, y se le agrega una condición WHERE, con el filtro de valor analítico (row_num = 1).
    • El operador MOD de Teradata tampoco es estándar y, por lo tanto, lo modifica la función MOD().

  3. Haz clic en Ejecutar.

    El resultado en columnas es el mismo que el de Teradata.

Referencias de columnas cruzadas

Teradata admite referencias cruzadas entre columnas definidas dentro de la misma consulta. En esta sección, se usa una consulta que asigna un alias a una instrucción SELECT anidada y, luego, se hace referencia a ese alias en una expresión CASE.

Para ilustrar esto, puedes ejecutar una consulta que determine si un cliente estuvo activo en un año determinado. Un cliente está activo si realizó al menos un pedido durante el año.

  1. En la computadora en la que tienes BTEQ instalado, cambia o abre el símbolo del sistema de BTEQ. Si cerraste BTEQ, ejecuta este comando:

    bteq

  2. Copia la siguiente consulta de SQL de Teradata a la ventana de 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;

    Ten en cuenta lo siguiente sobre la consulta:

    • Hay una consulta anidada que cuenta la cantidad de veces que aparece la clave de cliente 2 en el año 1994. El resultado de esta consulta se muestra en la primera columna y se le asigna el alias num_orders.
    • En la segunda columna, la expresión CASE genera ACTIVE si la cantidad de pedidos que se encontró no es cero; de lo contrario, genera INACTIVE. La expresión CASE usa de forma interna el alias de la primera columna de la misma consulta (num_orders).

  3. Ejecuta la consulta.

    El resultado es similar al siguiente:

     num_orders  status
-----------  --------
          3  ACTIVE

Para ejecutar la misma consulta en BigQuery, debes borrar la referencia entre columnas en la misma consulta.

  1. Dirígete a la consola de BigQuery:

    Ir a la consola de BigQuery

  2. Copia la siguiente consulta traducida en el editor de consultas:

    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;

    Ten en cuenta los cambios siguientes respecto a la consulta original:

    • La consulta anidada se traslada a la cláusula FROM de una consulta adjunta. Tiene el alias customer, pero este no define una columna de salida porque está en una cláusula FROM en contraposición a una cláusula SELECT.
    • La cláusula SELECT tiene dos columnas:
      • La primera columna muestra el número de pedidos (num_orders) definidos en la consulta anidada (customer).
      • La segunda columna incluye la instrucción CASE que hace referencia a la cantidad de pedidos definidos en la consulta anidada.

  3. Haz clic en Ejecutar.

    El resultado en columnas es el mismo que el de Teradata.

Limpia

Para evitar incurrir en cargos en tu cuenta de Google Cloud por los recursos que se usaron en este instructivo, bórralos.

Borra el proyecto

La forma más sencilla de detener los cargos de facturación es borrar el proyecto que creaste para este instructivo.

  1. En la consola de Google Cloud, ve a la página Administrar recursos.

    Ir a Administrar recursos

  2. En la lista de proyectos, elige el proyecto que quieres borrar y haz clic en Borrar.
  3. En el diálogo, escribe el ID del proyecto y, luego, haz clic en Cerrar para borrar el proyecto.

¿Qué sigue?