CREATE TABLE dans Databricks : Un guide complet pour construire et gérer des tableaux

Les tableaux Databricks sont un composant essentiel de la plateforme Databricks Lakehouse, conçue pour gérer le stockage et le traitement de données à grande échelle. Ces tableaux favorisent la collaboration entre les équipes en unifiant les données structurées et non structurées tout en prenant en charge les charges de travail analytiques.

Dans cet article, je vais vous guider à travers les différentes techniques de création de tableaux dans Databricks en utilisant la commande CREATE TABLE avec plusieurs variations de syntaxe pour s'adapter aux différents cas d'utilisation. Je vous donnerai également des conseils sur les meilleures pratiques en matière de construction et de gestion des tableaux dans Databricks pour plus d'efficacité et de fiabilité.

Pour commencer, je vous recommande vivement de consulter le site de DataCamp How to Learn Databricks : A Beginner's Guide to the Unified Data Platform blog post to help you understand the core features and applications of Databricks and provide a structured path to start your learning. Je vous recommande également de suivre notre cours d'introduction à Databricks. Même si vous avez de l'expérience en SQL, vous verrez que les choses peuvent être différentes dans la plateforme Databricks Lakehouse. Les exercices interactifs de notre cours vous guideront à travers les requêtes SQL ainsi que les fonctionnalités de la plateforme et les connexions aux systèmes externes.

Introduction aux tableaux Databricks

Pour commencer, nous devrions dire que les tableaux Databricks sont des ensembles de données structurés qui facilitent l'organisation et l'accès aux données dans l'environnement Databricks Lakehouse. Il en existe deux types :

• Tableaux gérés : Ces tableaux sont entièrement contrôlés par Databricks, qui gère à la fois le stockage et les métadonnées. Lorsqu'une table gérée est créée, Databricks s'occupe des fichiers de données sous-jacents, et si la table est supprimée, les données correspondantes sont également supprimées. 
• Tableaux externes : Les tableaux externes référencent des données qui résident en dehors de Databricks, généralement dans des systèmes de stockage dans le cloud comme AWS S3 ou Azure Blob Storage. Alors que Databricks gère les métadonnées de ces tableaux, les données réelles restent intactes même si le tableau est supprimé. Ces tableaux sont également appelés tableaux non gérés.

Les tableaux dans Databricks utilisent souvent par défaut Delta Lake en raison de ses fonctionnalités avancées et de ses optimisations. Delta Lake propose des transactions ACID, uneévolutivité, desaméliorations de performance telles que des optimisations comme la mise en cache et l'indexation, et untraitement unifié des données par lots et en continu.

À titre d'information, les premières versions d'Azure Databricks utilisaient les tableaux hérités du "format Hive", que les tableaux modernes Delta ont remplacés. Dans l'ensemble, les tableaux Delta modernes surpassent les tableaux Hive hérités en termes de fiabilité et de performances, ce qui en fait le choix par défaut pour les flux de travail Databricks.

Voici un résumé qui, je pense, pourrait vous aider à voir la différence entre les deux :

Variations de la syntaxe CREATE TABLE

La commande CREATE TABLE dans Databricks prend en charge plusieurs variantes pour s'adapter à différents cas. Chaque variante a sa raison d'être, avec des options ciblées telles que la localisation des fichiers, le clonage et la gestion des métadonnées sur . Si vous avez besoin de rafraîchir vos connaissances sur Databricks SQL, je vous recommande de lire notre tutoriel Databricks SQL pour apprendre, entre autres, comment utiliser un Notebook dans un entrepôt Databricks SQL.

 Vous trouverez ci-dessous les principaux styles de syntaxe, accompagnés d'exemples et d'explications.

CRÉER UN TABLEAU [EN UTILISANT]

Cette syntaxe crée un nouveau tableau avec un schéma et un format de données spécifiés. La requête suivante crée un tableau nommé sales_data en lisant les données des fichiers Parquet stockés à un emplacement spécifique.

-- Create a table from files in a specific format
CREATE TABLE sales_data
USING PARQUET
LOCATION '/mnt/data/sales';Propulsé par

CREATE TABLE (format Hive)

Cette syntaxe héritée est similaire à la commande SQL standard CREATE TABLE, utilisée pour définir un tableau avec des métadonnées gérées par Databricks sans qu'il soit nécessaire de faire référence à des fichiers externes. La requête ci-dessous crée un tableau de type Hive nommé hive_table avec un schéma prédéfini et des propriétés de métadonnées supplémentaires.

-- Hive-style table creation with schema definition
CREATE TABLE hive_table (
    id INT,
    name STRING
)
COMMENT 'Hive format example'
TBLPROPERTIES ('created_by' = 'databricks');Propulsé par

Dans la requête ci-dessus, la clause COMMENT ajoute des métadonnées descriptives tandis que TBLPROPERTIES stocke les propriétés personnalisées du tableau.

CRÉER UN TABLEAU COMME

Cette commande crée un nouveau tableau en utilisant le schéma et les propriétés d'un tableau existant. Vous devez utiliser cette syntaxe lorsque vous souhaitez reproduire la structure d'un tableau existant sans copier ses données. Ceci est utile pour les tests ou le développement.

-- Create a table with the same schema as another table
CREATE TABLE new_table LIKE existing_table;Propulsé par

CRÉER UN CLONE DE TABLEAU

Cette syntaxe vous permet de créer un nouveau tableau en clonant un tableau Delta Lake existant, soit en tant que clone profond, soit en tant que clone superficiel. Par conséquent, vous devez utiliser la syntaxe CREATE TABLE CLONE lorsque vous avez besoin d'une copie indépendante des données et des métadonnées d'un tableau (clone profond) ou uniquement des métadonnées tout en faisant référence aux données d'origine (clone superficiel). Pensez-y pour la gestion des versions ou la création de sauvegardes.

L'exemple suivant présente les requêtes permettant de créer des tableaux de clone profond et de clone superficiel dans Databricks.

 -- Deep clone by default
CREATE TABLE cloned_table CLONE original_table;

-- Shallow clone
CREATE TABLE shallow_clone_table SHALLOW CLONE original_table;  Propulsé par

CRÉER OU REMPLACER UN TABLEAU

Cette commande unique et combinée remplace un tableau existant par un nouveau, en conservant le même nom de tableau, en une seule opération. Vous devez utiliser cette syntaxe lorsque vous souhaitez écraser complètement un tableau existant tout en conservant son nom. Cette fonction est particulièrement utile pour mettre à jour la structure ou les données d'un tableau sans avoir à le supprimer au préalable.

L'exemple de requête ci-dessous crée ou remplace le tableau sales avec un nouveau schéma.

-- Create or replace a sales table with a new schema
CREATE OR REPLACE TABLE sales (
    sale_id INT,
    amount FLOAT
)
USING DELTA
LOCATION '/mnt/delta/sales';Propulsé par

Il existe d'autres clauses pertinentes que vous pouvez utiliser avec la syntaxe CREATE TABLE dans Databricks pour gérer vos tableaux selon des exigences spécifiques. Vous trouverez ci-dessous un résumé de l'utilisation de chacune de ces clauses. J'ai déjà utilisé certains d'entre eux, mais pas tous, à titre d'exemples.

• IF NOT EXISTS: Évite les erreurs en ignorant la création d'un tableau s'il existe déjà.

• USING: Spécifie le format du fichier (par exemple, Delta, Parquet, CSV).

• EXTERNAL: Déclare un tableau dont les données résident en dehors du stockage géré par Databricks.

• PARTITIONED BY: Définit les partitions pour optimiser les grands ensembles de données.

• LOCATION: Pointe vers le répertoire contenant les données du tableau.

• TBLPROPERTIES: Ajoute des métadonnées pour les configurations et optimisations de tableaux personnalisés.

Démonstrations pas à pas

Dans cette section, je vais vous présenter différentes façons de créer et de gérer des tableaux dans Databricks, avec des exemples concis et des explications sur les clauses clés. Je vous recommande de consulter notre tutoriel Databricks : 7 Concepts incontournables pour tout spécialiste des données tutoriel pour apprendre à exécuter des commandes SQL dans la plateforme Databricks.

Comment créer un tableau vide ?

Pour créer un tableau Delta vide, définissez le schéma du tableau avec les noms des colonnes et les types de données. Utilisez la clause USING DELTA pour définir le format du tableau et COMMENT pour ajouter des métadonnées descriptives.

Cette requête crée un tableau Delta vide nommé employees avec un schéma prédéfini, comprenant des tableaux pour id, name, department, et hire_date. 

-- Create an empty Delta table with a defined schema
CREATE TABLE employees (
    id BIGINT,
    name STRING,
    department STRING,
    hire_date DATE
)
USING DELTA
COMMENT 'Table to store employee information';Propulsé par

Comment créer un tableau à partir de données existantes ?

Pour créer un tableau à partir de données existantes, reliez le tableau à des fichiers de données externes dans des formats tels que CSV, Parquet ou JSON. Vous pouvez utiliser la clause LOCATION pour lier le tableau au chemin d'accès au fichier et la clause USING pour spécifier le format des données.

Par exemple, la requête suivante crée un tableau nommé sales_data en référençant les fichiers Parquet stockés à l'adresse /mnt/data/sales_data/.

-- Create a table from Parquet files in a specified location
CREATE TABLE sales_data
USING PARQUET
LOCATION '/mnt/data/sales_data/';Propulsé par

Comment créer un tableau en tant que select (CTAS)

Vous pouvez également créer un tableau en tant que select en exécutant une requête et en stockant le résultat. Pour ce scénario, la clause AS SELECT remplit le tableau avec le résultat de la requête.

Par exemple, la requête ci-dessous crée un nouveau tableau nommé high_value_sales en sélectionnant et en transformant les données du tableau existant sales_data. Il filtre les lignes où total_amount est supérieur à 1000, en ne conservant que les colonnes sale_id, customer_id, et total_amount. 

-- Create a table by selecting and transforming data from another table
CREATE TABLE high_value_sales AS
SELECT
    sale_id,
    customer_id,
    total_amount
FROM sales_data
WHERE total_amount > 1000;Propulsé par

Comment créer un tableau externe ?

La création d'un tableau externe implique de référencer des données stockées en dehors du stockage géré par Databricks. Vous devez utiliser la clause LOCATION pour spécifier l'emplacement des données externes.

La requête ci-dessous crée un tableau Delta externe nommé external_table en se référant aux données stockées sur /mnt/external_storage/external_table/.

-- Create an external table referencing data outside Databricks storage
CREATE TABLE external_table
USING DELTA
LOCATION '/mnt/external_storage/external_table/';Propulsé par

Comment créer un tableau LIKE

Pour créer un tableau LIKE, dupliquez le schéma d'un tableau existant sans copier ses données. Dans l'exemple ci-dessous, la requête crée un nouveau tableau nommé sales_backup avec le même schéma qu'un tableau existant sales.

-- Create a new sales table with the same schema as an existing table
CREATE TABLE sales_backup LIKE sales;Propulsé par

Comment créer un tableau CLONE ?

Vous pouvez créer un clone de tableau en utilisant la clause CLONE pour créer une copie superficielle ou profonde d'un tableau Delta à des fins de sauvegarde ou de test.

Dans l'exemple suivant, la requête crée un nouveau tableau, sales_shallow_clone, en ne copiant que les métadonnées (schéma et propriétés du tableau).

-- Shallow clone: Copies only metadata (references original data)
CREATE TABLE sales_shallow_clone CLONE sales_data;Propulsé par

L'exemple ci-dessous crée un nouveau tableau, sales_deep_clone, mais copie à la fois les métadonnées et les données réelles.

-- Deep clone: Copies both metadata and actual data
CREATE TABLE sales_deep_clone CLONE sales_data DEEP;Propulsé par

Comment créer un tableau temporaire (temp table) ?

Utilisez la clause TEMPORARY lors de la création de tableaux temporaires pour vous assurer que le tableau est spécifique à la session.

La requête ci-dessous crée un tableau temporaire nommé temp_table qui n'existe que pour la session en cours. Il sélectionne id, name, et sales_amount à partir de sales_data, en filtrant les lignes où region = 'US'.

-- Create a temporary table that exists only in the session
CREATE TEMPORARY TABLE temp_table AS
SELECT
    id,
    name,
    sales_amount
FROM sales_data
WHERE region = 'US';Propulsé par

Comment remplacer un tableau

Vous pouvez utiliser la clause CREATE OR REPLACE pour remplacer la définition et le contenu du tableau. La requête ci-dessous remplace le tableau existant updated_table par un nouveau schéma et de nouvelles données. Il définit le tableau avec trois colonnes (id, name, total_sales).

-- Replace an existing table with a new schema or data
CREATE OR REPLACE TABLE updated_table (
    id BIGINT,
    name STRING,
    total_sales DOUBLE
)
USING DELTA
LOCATION '/mnt/delta/updated_table/';Propulsé par

Sujets avancés

Dans cette section, je vais explorer les caractéristiques avancées des tableaux Databricks qui améliorent les fonctionnalités, les performances et la sécurité.

Colonnes générées et colonnes d'identité

Databricks prend en charge les colonnes d'identité, qui génèrent automatiquement des valeurs uniques pour chaque nouvelle ligne. Cette fonction est utile pour créer des clés de substitution dans les entrepôts de données.

La requête ci-dessous crée un tableau employees avec trois colonnes : id, name, et department. La colonne id est une colonne d'identité qui s'incrémente automatiquement à chaque nouvelle ligne.

-- Identity column: auto-incremented value
CREATE TABLE employees (
    id BIGINT GENERATED ALWAYS AS IDENTITY, -- Auto-incremented column
    name STRING,
    department STRING
);Propulsé par

Cette autre requête crée un tableau sales avec trois colonnes : sale_id, sale_date, et sale_year. La colonne sale_year est générée et calculée automatiquement sur la base de la colonne sale_date à l'aide de la fonction YEAR(sale_date).

-- Generated column: derived from other columns
CREATE TABLE sales (
    sale_id BIGINT,
    sale_date DATE,
    sale_year INT GENERATED ALWAYS AS (YEAR(sale_date)) -- Derived column
);Propulsé par

Partitionnement et regroupement

Le partitionnement divise les données en plus petits morceaux, ce qui améliore les performances des requêtes, tandis que le regroupement organise les données au sein des partitions pour une lecture plus rapide.

Par exemple, la requête ci-dessous crée un tableau partitionné dans lequel les données sont divisées en partitions sur la base de la colonne region, ce qui améliore les performances de la requête lors du filtrage par région.

-- Partitioned table
CREATE TABLE partitioned_table (
    id BIGINT,
    region STRING,
    sales_amount DOUBLE
)
USING DELTA
PARTITIONED BY (region); -- Querying by region will be fasterPropulsé par

La requête ci-dessous optimise le tableau partitionné en regroupant les données dans chaque partition sur la base de la colonne sales_amount. Il accélère les requêtes sur sales_amount en améliorant la disposition des données pour une lecture efficace. La clause ZORDER BY fonctionne au sein des partitions pour améliorer les performances de lecture en regroupant les données au sein des partitions, et non entre les partitions.

-- Clustered table
OPTIMIZE partitioned_table
ZORDER BY (sales_amount); -- Clusters data within partitions for efficient readsPropulsé par

Masquage de colonnes et filtres de lignes

Le masquage des colonnes et les filtres de ligne limitent la visibilité des données en fonction du rôle de l'utilisateur ou des conditions, protégeant ainsi les informations sensibles.

Par exemple, la requête ci-dessous crée une vue masquée nommée masked_table basée sur le tableau employees. La colonne salary est expurgée (remplacée par REDACTED) pour tous les utilisateurs, à l'exception de ceux qui ont le rôle manager. La déclaration CASE garantit que seuls les gestionnaires peuvent voir le salaire réel, tandis que les autres voient les données masquées.

-- Column masking: Redact sensitive data
CREATE MASKED VIEW masked_table AS
SELECT
    id,
    name,
    CASE
        WHEN current_user() = 'manager' THEN salary
        ELSE 'REDACTED'
    END AS salary
FROM employees;Propulsé par

De même, la requête ci-dessous utilise des filtres de ligne pour créer ou remplacer une vue nommée filtered_table basée sur le tableau sales. La vue filtre les lignes pour n'inclure que les données où region = 'US', limitant l'accès aux seules données de vente des États-Unis.

-- Row filters: Fine-grained access control
CREATE OR REPLACE VIEW filtered_table AS
SELECT *
FROM sales
WHERE region = 'US'; -- Limit data visibility to a specific regionPropulsé par

Évaluation du schéma

Databricks prend en charge l'évolution du schéma par le biais de CREATE OR REPLACE TABLE et des modifications de tableaux. Vous devez utiliser l'instruction CREATE OR REPLACE TABLE pour écraser le schéma et les données et l'instruction ALTER TABLE pour modifier le schéma de manière incrémentielle sans affecter les données.

La requête ci-dessous remplace le tableau employees existant par un nouveau schéma, en ajoutant une nouvelle colonne hire_date tout en conservant les données stockées au format Delta.

-- Schema evolution with CREATE OR REPLACE TABLE
CREATE OR REPLACE TABLE employees (
    id BIGINT,
    name STRING,
    department STRING,
    hire_date DATE -- New column added
)
USING DELTA;Propulsé par

Cette autre requête modifie le tableau employees en ajoutant une nouvelle colonne salary au schéma existant.

-- Altering a table schema
ALTER TABLE employees ADD COLUMNS (salary DOUBLE);Propulsé par

Optimisation des performances

Pour optimiser les performances des tableaux à grande échelle, vous pouvez utiliser diverses propriétés et techniques de tableau telles que l'auto-optimisation et l'ordonnancement en Z.

Dans l'exemple suivant, la requête active l'auto-optimisation sur le site delta_table en définissant des propriétés permettant de compacter automatiquement les petits fichiers et de fusionner les fichiers lors des écritures, ce qui améliore l'efficacité du stockage.

-- Enable automatic optimizations
ALTER TABLE delta_table
SET TBLPROPERTIES (
    'delta.autoOptimize.optimizeWrite' = 'true', -- Automatically compact small files
    'delta.autoOptimize.autoCompact' = 'true'   -- Automatically merge files
);Propulsé par

De même, la requête ci-dessous optimise le site delta_table en regroupant les données basées sur region et sales_date afin d'améliorer les performances de la requête, en particulier pour les filtres sur ces colonnes.

-- Perform clustering for faster query performance
OPTIMIZE delta_table
ZORDER BY (region, sales_date); -- Improves query efficiency for frequent filtersPropulsé par

Meilleures pratiques et considérations

Voici quelques éléments à prendre en compte lors de la création de tableaux dans Databricks, afin de faire les choses correctement :

• Conventions d'appellation des tableaux : Utilisez toujours des noms clairs et cohérents pour améliorer la lisibilité et réduire les erreurs. Évitez les caractères spéciaux ou les noms trop longs pour éviter les problèmes de compatibilité.
• Sécurité et autorisations : Utilisez Unity Catalog pour un contrôle d'accès centralisé à toutes les données. Utilisez les listes de contrôle d'accès (ACL) pour limiter les personnes autorisées à consulter ou à modifier les données. Masquez également les données sensibles à l'aide de masques de colonnes ou de filtres de lignes.
• Sauvegarde et récupération : Comptez sur l'historique des versions de Delta Lake pour faciliter la récupération et le voyage dans le temps. Contrôlez et gérez régulièrement la conservation des versions historiques afin d'équilibrer les coûts de stockage et les besoins de récupération.
• Tableaux gérés et tableaux externes: Il est également important de distinguer les tableaux gérés des tableaux externes. Dans les tableaux gérés, Databricks gère à la fois les métadonnées et les données. Cette solution est idéale pour les scénarios dans lesquels vous souhaitez contrôler totalement la gestion du cycle de vie des données. Cependant, dans les tableaux externes, les données référencées sont stockées en dehors des Databricks. Ceci est utile pour maintenir le contrôle sur les données qui peuvent être partagées entre différentes plateformes ou systèmes.

Pièges courants et dépannage

Maintenant que nous avons discuté des meilleures pratiques pour gérer les tableaux dans Databricks, permettez-moi d'aborder les problèmes fréquents que vous pouvez rencontrer avec les tableaux Databricks, leurs messages d'erreur typiques, puis les solutions.

Erreurs d'emplacement non vide lors de l'utilisation de CREATE vs. CRÉER OU REMPLACER

Lorsque vous utilisez CREATE TABLE avec un emplacement spécifié (clauseLOCATION ), si le répertoire n'est pas vide, vous pouvez rencontrer une erreur du type :

Error: Cannot create table because the location is not empty.Propulsé par

Cette erreur se produit parce que la commande CREATE TABLE s'attend à ce que l'emplacement spécifié soit un répertoire vide, mais que les fichiers ou les métadonnées existants ne répondent pas à cette exigence. 

Pour résoudre cette erreur, utilisez l'instruction CREATE OR REPLACE TABLE pour écraser les données et métadonnées existantes. Ou bien, nettoyez manuellement le répertoire avant d'utiliser l'instruction CREATE TABLE.

CREATE OR REPLACE TABLE delta_table
USING DELTA
LOCATION '/mnt/data/delta_table'; -- Overwrites table and filesPropulsé par

Inadéquation du schéma ou problèmes avec certains types de données

L'insertion ou l'interrogation de données entraîne des erreurs de concordance des schémas, telles que

Error: Schema mismatch: Expected column type STRING, but found INT.Propulsé par

L'erreur peut être due à une incompatibilité entre le schéma du tableau et les types de données entrants. En outre, les conversions de types de données non prises en charge ou ambiguës (par exemple, les types complexes imbriqués) peuvent entraîner une inadéquation des schémas. Pour résoudre cette erreur, appliquez la correspondance des schémas pour vérifier le schéma lors de la création des tableaux ou utilisez l'application des schémas dans les tableaux Delta.

-- Enforce schema matching during table creation
CREATE TABLE schema_table (
    id INT,
    name STRING
)
USING DELTA
TBLPROPERTIES ('delta.schema.enforce' = 'true'); -- Enforces strict schemaPropulsé par

Vous devez également permettre l'évolution du schéma lors de l'ajout de nouvelles colonnes ou de la modification des types de données.

ALTER TABLE schema_table ADD COLUMNS (new_column DOUBLE);Propulsé par

L'utilisation d'un moulage de type explicite lors de l'insertion de données devrait également permettre de résoudre ce type d'erreur.

INSERT INTO schema_table SELECT CAST(id AS INT), CAST(name AS STRING) FROM source_table;Propulsé par

Limitations des transactions simultanées avec les colonnes d'identité

Lorsque vous utilisez des colonnes d'identité, vous pouvez rencontrer des problèmes liés aux transactions simultanées, tels que

Error: Concurrent transactions detected on identity column tablePropulsé par

Les colonnes d'identité sont soumises à un état et peuvent rencontrer des conflits lorsque plusieurs transactions tentent d'insérer des données simultanément. Pour résoudre ce problème, utilisez des clés de substitution pour remplacer les colonnes d'identité par des clés de substitution déterministes générées à l'aide de fonctions telles que uuid() ou monotonically_increasing_id().

CREATE TABLE better_table AS
SELECT
    monotonically_increasing_id() AS id, -- Replaces identity column
    name, department
FROM source_data;Propulsé par

Vous pouvez également utiliser le site MERGE de Delta Lake pour des flux de travail d'insertion et de mise à jour complexes sans avoir recours à des colonnes d'identité.

MERGE INTO target_table AS t
USING source_table AS s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;Propulsé par

Conclusion

Dans Databricks, la commande CREATE TABLE est un outil polyvalent permettant de définir des structures de données, d'établir des liens avec des données externes et d'obtenir de nouveaux ensembles de données par le biais de requêtes. Il est important de comprendre les différentes syntaxes de création et de gestion des tableaux dans Databricks en fonction de vos besoins spécifiques. Comme vous l'avez vu, il existe plusieurs méthodes de création de tableaux.

Si vous souhaitez explorer les concepts fondamentaux de Databricks, je vous recommande vivement de suivre le cours Introduction à Databricks de DataCamp pour découvrir Databricks en tant que solution d'entreposage de données pour la Business Intelligence (BI). Je vous recommande également de consulter notre article de blog Databricks Certifications In 2025 pour savoir comment obtenir des certifications Databricks, explorer les avantages professionnels et apprendre à choisir la bonne certification en fonction de vos objectifs de carrière.