Ingesta de datos con métodos SQL

7 minutos

Los comandos SQL proporcionan un enfoque declarativo para la ingesta de datos en Azure Databricks. Si ya está familiarizado con la sintaxis sql, estos métodos le permiten crear y rellenar tablas sin escribir código de procedimientos. Las tres técnicas principales de ingesta de SQL(CREATE TABLE AS SELECT (CTAS) , CREATE OR REPLACE TABLEy COPY INTO) abordan diferentes escenarios de ingesta, a la vez que se mantiene la compatibilidad completa con el catálogo de Unity.

Creación de tablas a partir de consultas con CTAS

La CREATE TABLE AS SELECT instrucción combina la creación de tablas y el rellenado de datos en una sola operación. Defina una nueva tabla basada en los resultados de una consulta SELECT, lo que la hace ideal para transformar los datos durante la ingesta de datos.

Considere un escenario en el que necesita ingerir datos de clientes de un origen externo y aplicar transformaciones. Con CTAS, escribe una consulta que lee desde el origen, aplica las transformaciones y guarda los resultados en una nueva tabla administrada:

CREATE TABLE catalog.schema.customers AS
SELECT 
    customer_id,
    UPPER(customer_name) AS customer_name,
    email,
    created_date
FROM external_staging.raw_customers
WHERE customer_status = 'active';

El esquema de tabla se deriva automáticamente de los resultados de la consulta. Azure Databricks crea la tabla con el formato Delta de forma predeterminada, lo que proporciona transacciones ACID, viajes en tiempo y rendimiento optimizado.

CTAS funciona bien para cargas de datos iniciales y migraciones únicas. Cuando necesite leer datos de archivos, combine CTAS con la read_files función con valores de tabla:

CREATE TABLE catalog.schema.sales_data AS
SELECT * FROM read_files(
    '/Volumes/catalog/schema/volume/sales/*.parquet',
    format => 'parquet'
);

Nota:

CTAS crea una nueva tabla cada vez que se ejecuta. Si la tabla ya existe, se produce un error en el comando a menos que use la IF NOT EXISTS cláusula , pero esa cláusula omite la ejecución por completo en lugar de actualizar la tabla.

Actualizar tablas con CREATE OR REPLACE TABLE

Cuando necesite actualizar completamente el contenido de una tabla, CREATE OR REPLACE TABLE proporciona una solución limpia. Este comando crea una nueva tabla o reemplaza completamente una existente, conservando el historial de tablas, los privilegios concedidos y los filtros de fila o máscaras de columna que haya configurado.

Este enfoque resulta útil para las actualizaciones periódicas de datos en las que desea reemplazar todos los datos existentes:

CREATE OR REPLACE TABLE catalog.schema.daily_metrics AS
SELECT 
    report_date,
    SUM(revenue) AS total_revenue,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM catalog.schema.transactions
WHERE report_date >= CURRENT_DATE - INTERVAL 30 DAYS
GROUP BY report_date;

A diferencia de quitar y volver a crear una tabla, CREATE OR REPLACE mantiene los metadatos y permisos de la tabla. Los usuarios y aplicaciones posteriores continúan accediendo a la tabla sin necesidad de reconfiguración.

También puede usar este comando para cargar datos directamente desde archivos. El esquema se deduce automáticamente de los resultados de la consulta:

CREATE OR REPLACE TABLE catalog.schema.products AS
SELECT * FROM read_files(
    '/Volumes/catalog/schema/volume/products.csv',
    format => 'csv',
    header => true
);

Importante

CREATE OR REPLACE realiza un reemplazo de una tabla completa. En el caso de las actualizaciones incrementales en las que solo desea agregar nuevos registros, use COPY INTO en su lugar.

Carga incremental de archivos con COPY INTO

COPY INTO aborda un desafío común de ingesta: cargar archivos desde el almacenamiento en la nube de forma confiable y repetible. A diferencia de CTAS, que se ejecuta una vez y crea una tabla, COPY INTO está diseñado para flujos de trabajo de ingesta en curso donde llegan periódicamente nuevos archivos.

El comando lee los archivos de una ubicación especificada y los anexa a una tabla Delta existente. Su característica clave es idempotencia: los archivos que ya se han cargado se omiten automáticamente, incluso en varias ejecuciones.

COPY INTO catalog.schema.events
FROM '/Volumes/catalog/schema/volume/events/'
FILEFORMAT = JSON
FORMAT_OPTIONS ('multiline' = 'true');

Antes de ejecutar COPY INTO, la tabla de destino ya debe existir. Créelo con el esquema adecuado:

CREATE TABLE IF NOT EXISTS catalog.schema.events (
    event_id STRING,
    event_type STRING,
    event_timestamp TIMESTAMP,
    payload STRING
);

Configurar la selección de archivos

Cuando el directorio de origen contiene archivos con diferentes patrones de nomenclatura o necesita cargar archivos específicos, use las PATTERN opciones o FILES :

-- Load only files matching a pattern
COPY INTO catalog.schema.orders
FROM '/Volumes/catalog/schema/volume/orders/'
FILEFORMAT = PARQUET
PATTERN = 'orders_2024*.parquet';

-- Load specific files by name
COPY INTO catalog.schema.orders
FROM '/Volumes/catalog/schema/volume/orders/'
FILEFORMAT = PARQUET
FILES = ('orders_001.parquet', 'orders_002.parquet');

Gestión del esquema y la calidad de los datos

COPY INTO proporciona opciones para controlar los cambios de esquema y validar los datos antes de cargarlos:

COPY INTO catalog.schema.sensor_data
FROM '/Volumes/catalog/schema/volume/sensors/'
FILEFORMAT = CSV
FORMAT_OPTIONS (
    'header' = 'true',
    'inferSchema' = 'true'
)
COPY_OPTIONS ('mergeSchema' = 'true');

La mergeSchema opción permite que el esquema de tabla evolucione a medida que aparecen nuevas columnas en los archivos de origen. Para validar los datos sin cargarlos, agregue la cláusula VALIDATE:

COPY INTO catalog.schema.sensor_data
FROM '/Volumes/catalog/schema/volume/sensors/'
FILEFORMAT = CSV
VALIDATE ALL;

Esta validación comprueba si los datos se pueden analizar, coinciden con el esquema de la tabla y satisfacen las restricciones de nulabilidad y comprobación.

Elegir el método correcto

Cada método de ingesta de SQL sirve para propósitos distintos en los flujos de trabajo de ingeniería de datos:

Método	Más adecuado para	Comportamiento
CTAs	Cargas de datos iniciales, migraciones únicas, creación de tablas a partir de consultas	Crea una nueva tabla; produce un error si existe una tabla
CREAR O REEMPLAZAR	Actualizaciones completas periódicas, reemplazando las tablas de preparación	Reemplaza toda la tabla; conserva los permisos
COPIAR EN	Ingesta de archivos en curso, cargas incrementales	Anexa a la tabla existente; omite los archivos cargados

Para la ingesta basada en archivos que requiere inferencia automática de esquemas, notificaciones de archivos o garantías de exactamente una vez, considere usar Auto Loader como un enfoque complementario. Cuando las necesidades de ingesta son sencillas y prefiere SQL declarativo en lugar de código procedimental, estos tres métodos proporcionan un kit de herramientas completo para administrar el flujo de datos en el Catálogo de Unity.

Nota:

COPY INTO funciona bien al ingerir archivos en cantidades de miles a lo largo del tiempo. En el caso de los orígenes que aumentarán a millones de archivos o más, el cargador automático (cubierto en la siguiente unidad) es el enfoque recomendado: detecta archivos de forma más eficaz, admite una evolución de esquema más completa y se escala sin la sobrecarga de la lista de directorios. Databricks también recomienda la transmisión de tablas respaldadas por Auto Loader como alternativa escalable y a largo plazo para la ingesta de archivos basada en SQL.

Comentarios

¿Le ha resultado útil esta página?