Ingérer des données avec des méthodes SQL
Tip
Pour plus d’informations, consultez l’onglet Texte et images !
Les commandes SQL fournissent une approche déclarative de l’ingestion des données dans Azure Databricks. Si vous êtes déjà à l’aise avec la syntaxe SQL, ces méthodes vous permettent de créer et de remplir des tables sans écrire de code procédural. Les trois techniques d’ingestion SQL principales (CREATE TABLE AS SELECT (CTAS)CREATE OR REPLACE TABLE et COPY INTO– chacune d’elles traitent des scénarios d’ingestion différents tout en conservant une compatibilité totale avec le catalogue Unity.
Créer des tables à partir de requêtes avec CTAS
L’instruction CREATE TABLE AS SELECT combine la création de table et la population de données en une seule opération. Vous définissez une nouvelle table basée sur les résultats d’une SELECT requête, ce qui le rend idéal pour transformer des données pendant l’ingestion.
Envisagez un scénario dans lequel vous devez ingérer des données client à partir d’une source externe et appliquer des transformations. Avec CTAS, vous écrivez une requête qui lit à partir de la source, applique vos transformations et enregistre les résultats dans une nouvelle table managée :
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';
Le schéma de table est automatiquement dérivé des résultats de la requête. Azure Databricks crée la table au format Delta par défaut, qui fournit des transactions ACID, des déplacements temporels et des performances optimisées.
CTAS fonctionne bien pour les chargements de données initiaux et les migrations ponctuelles. Lorsque vous devez lire des données à partir de fichiers, combinez CTAS avec la fonction tabulaire read_files :
CREATE TABLE catalog.schema.sales_data AS
SELECT * FROM read_files(
'/Volumes/catalog/schema/volume/sales/*.parquet',
format => 'parquet'
);
Note
CTAS crée une table chaque fois qu’elle s’exécute. Si la table existe déjà, la commande échoue, sauf si vous utilisez la IF NOT EXISTS clause, mais cette clause ignore entièrement l’exécution au lieu de mettre à jour la table.
Actualiser les tables avec CREATE OR REPLACE TABLE
Lorsque vous devez actualiser entièrement le contenu d’une table, CREATE OR REPLACE TABLE fournit une solution propre. Cette commande crée une table ou remplace complètement une table existante, préservant l’historique des tables, les privilèges accordés et tous les filtres de lignes ou masques de colonne que vous avez configurés.
Cette approche est précieuse pour les actualisations périodiques des données dans lesquelles vous souhaitez remplacer toutes les données existantes :
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;
Contrairement à la suppression et à la recréation d’une table, CREATE OR REPLACE conserve les métadonnées et les autorisations de la table. Les utilisateurs et applications en aval continuent d’accéder à la table sans reconfiguration.
Vous pouvez également utiliser cette commande pour charger des données directement à partir de fichiers. Le schéma est déduit automatiquement des résultats de la requête :
CREATE OR REPLACE TABLE catalog.schema.products AS
SELECT * FROM read_files(
'/Volumes/catalog/schema/volume/products.csv',
format => 'csv',
header => true
);
Important
CREATE OR REPLACE effectue un remplacement complet de la table. Pour les mises à jour incrémentielles où vous souhaitez uniquement ajouter de nouveaux enregistrements, utilisez COPY INTO plutôt.
Charger des fichiers de manière incrémentielle avec COPY INTO
COPY INTO répond à un défi d’ingestion courant : le chargement de fichiers à partir du stockage cloud de manière fiable et reproductible. Contrairement à CTAS, qui s’exécute une fois et crée une table, COPY INTO est conçu pour les flux de travail d’ingestion en cours où de nouveaux fichiers arrivent régulièrement.
La commande lit les fichiers à partir d’un emplacement spécifié et les ajoute à une table Delta existante. Sa fonctionnalité clé est l'idempotence : les fichiers qui ont déjà été chargés sont automatiquement ignorés, et ce même lors de plusieurs exécutions.
COPY INTO catalog.schema.events
FROM '/Volumes/catalog/schema/volume/events/'
FILEFORMAT = JSON
FORMAT_OPTIONS ('multiline' = 'true');
Avant d’exécuter COPY INTO, la table cible doit déjà exister. Créez-le avec le schéma approprié :
CREATE TABLE IF NOT EXISTS catalog.schema.events (
event_id STRING,
event_type STRING,
event_timestamp TIMESTAMP,
payload STRING
);
Configurer la sélection de fichiers
Lorsque votre répertoire source contient des fichiers avec des modèles de dénomination différents ou que vous devez charger des fichiers spécifiques, utilisez les options PATTERN ou 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');
Gérer le schéma et la qualité des données
COPY INTO fournit des options de gestion des modifications de schéma et de validation des données avant le chargement :
COPY INTO catalog.schema.sensor_data
FROM '/Volumes/catalog/schema/volume/sensors/'
FILEFORMAT = CSV
FORMAT_OPTIONS (
'header' = 'true',
'inferSchema' = 'true'
)
COPY_OPTIONS ('mergeSchema' = 'true');
L’option mergeSchema permet au schéma de table d’évoluer à mesure que de nouvelles colonnes apparaissent dans les fichiers sources. Pour valider les données sans le charger, ajoutez la clause VALIDATE :
COPY INTO catalog.schema.sensor_data
FROM '/Volumes/catalog/schema/volume/sensors/'
FILEFORMAT = CSV
VALIDATE ALL;
Cette validation vérifie si les données peuvent être analysées, correspondent au schéma de table et répondent aux contraintes nullabilité et de vérification.
Choisir la méthode appropriée
Chaque méthode d’ingestion SQL sert des objectifs distincts dans vos workflows d’ingénierie des données :
| Méthode | Idéal pour | Comportement |
|---|---|---|
| CTAS | Chargement initial des données, migrations ponctuelles, création de tables à partir de requêtes | Crée une table ; échoue si la table existe |
| CRÉER OU REMPLACER | Actualisations complètes périodiques, remplacement des tables intermédiaires | Remplace l’intégralité de la table ; conserve les autorisations |
| INSÉRER DANS | Ingestion de fichiers en cours, chargements incrémentiels | Ajoute à la table existante ; ignore les fichiers chargés |
Pour l'ingestion basée sur des fichiers qui nécessite l'inférence automatique du schéma, des notifications de fichiers ou des garanties d'exactement une fois, envisagez d'utiliser Auto Loader comme approche complémentaire. Lorsque vos besoins d’ingestion sont simples et que vous préférez sql déclaratif par rapport au code procédural, ces trois méthodes fournissent un kit de ressources complet pour gérer le flux de données dans le catalogue Unity.
Note
COPY INTO fonctionne bien lors de l’ingestion de fichiers par milliers au fil du temps. Pour les sources qui vont augmenter jusqu’à des millions de fichiers ou plus, le chargeur automatique (couvert dans l’unité suivante) est l’approche recommandée : elle découvre les fichiers plus efficacement, prend en charge une évolution de schéma plus riche et des échelles sans surcharge de liste de répertoires. Databricks recommande également des tables de flux soutenues par Auto Loader comme alternative évolutive et à long terme pour l’ingestion de fichiers basée sur SQL.